postgres_basic

login

optional

میشه ابتدا پستگرس رو استارت کرد(البته من تا حالا استفاده نکردم)

pg_ctlcluster 14 main start

برای استفاده از cli ابتدا باید با یوزر postgres لاگین کنیم ، سپس دستور psql رو بزنیم

sudo -i -u postgres

psql

دقت کنیم که خط اول لاگین در سیستم عامل هست و هنوز ربطی به پستگرس نداره ، میتونیم این رول رو توی سیستم عامل ببینیم :

less /etc/passwd

اگر مستقیم psql بزنیم با یوزر سیستم عامل تلاش می کنه به دیتابیس هم نام وسط شه ، برای راحتی یه کلک رشتی داره و اون اینه که هم نام یوزر سیستم عامل یه دیتابیس بسازیم و اوونر اون کنیم

sudo -i -u postgres psql -c "CREATE ROLE hp WITH LOGIN PASSWORD 'your_password_here';"

psql -c "CREATE DATABASE hp OWNER hp;"

ALTER ROLE hp WITH SUPERUSER;

سوال چرا از لوکال و یا ssh نیازی نیست پسورد بزنیم؟

چون از UDS Unix domain socket استفاده می کنه

pg_hba.conf

با استفاده از این فایل میشه احراز هوییت ها رو مدیریت کرد ، چند تا ستون داره :

• TYPE :

تایپ می گه لوکال وصل میشه و یا از طریق نتورک خارجی و ۲ مقدار میگیره

• • local (Unix domain socket)
• • host (TCP/IP)

• DATABASE : all

نام دیتابس ها میشه همه رو داد و برای محیط دولیوپ ثابته

• USER : all

• ADDRESS : فقط برای زمانی که host هست کاربرد داره و یه رنج آی پی هست

• METHOD :

روش رمزنگاری و هش پسورد هست ، برای local از peer استفاده می کنیم اما از host میشه یکی از این ها رو استفاده کرد md5, scram-sha-256, trust

یه نمونه خیلی ساده برای تست :

local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

اما برای رپلیکیشن هم رول نیازه و میشه اینا رو اضافه کرد

local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            scram-sha-256
host    replication     postgres        ::1/128                 scram-sha-256

postgresql.conf

برای پیکر بندی بعد از نصب باید با دستور زیر ون رو استارت کرد

/etc/postgresql/14/main/postgresql.conf

listen_addresses = '*'

listen_addresses = 'localhost,192.168.13.14'

یکی از راه های ساختن تیبل در اینیت کردن دیتا بیس اینه که DDL ها رو توی فایل ریخته و با دستور زیر فایل اجرا شود :

psql -U postgres -d news_fetcher -h localhost -p 5432 -a -f init-sql.sql

در فایل init-sql.sql می توان مشخصات تیبل ها رو ریخت

و در نهایت با دستور زیر ریموت بزنیم

psql -h 192.168.13.14 -p 5432 -d postgres -U postgres

secure

https://www.atlantic.net/vps-hosting/how-to-secure-postgresql-server/

برای ریموت باید ابتدا یوزر پسورد رو توی pgpass گذاشت برای راحتی و استفاده از کرون تب

role vs user

توی پست گرس رول داریم که به اون پرمیشن می دیم به صورت پیش فرض رولی که پرمیشن لاگین رو داره ، بهش یوزر میگیم

ورود به پستگرس اگر دستور psql رو در ترمینال بزنیم . پستگرس به صورت پیشفرض یوزر رو یوزر os و دیتا بیس رو پیشفرض میگیره و خطا میده چون ایجاد نکردیم

VIEW

با استفاده از ویو ، می تونیم به جای سلکت های تکراری و پیچیده از این فیچر استفاده کنیم و ب ای هر ویو اسمی انتخاب کنیم و بجای سلکت کردن ، ویو رو سلکت کنیم

MATETIALIZED VIEW

با مفهوم بالا می تونیم ویو رو تعمیم بدیم، به این گونه که اگر سلکت هایی که نیاز به پردازش زیاد داره و می خوایم تو روز تعداد محدودی بروز بشوند از این استفاده کنیم بعد از هر بار رفرش کردن متریالازر داده ها ثابت می مونن( مانند تیبل های موقت )

sequence

مانند آتو اینکیریمنت هست یعنی با یک سیکونس داده تولید می کنه

aggregate function

می تونیم اگریگیت های کایتوم تولید کنیم

cte with

یه جورایی شبیه تیبل تمپراری هست می شه یه سلکت یا آپدیت رو بهش اسم داد و بعدا از اون استفاده کرد مثلا دو کوییری رو با with اسم گزاری می کنیم و در کوییری سوم آن هارا استفاده می کنیم

with recursive

می توان به یک کوییری اسم داد و با روش باز گشتی ، داده را فچ کرد بهترین روش برای پیمایش درخت

tablespace

با استفاده از این ، مشخص میکنیم مکان ذخیره در حافظه کجا باشد. زمانی استفاده میشود که یا پارتیشن پر شده و بخواهیم بقیه داده را در مکانی دیگر ذخیره کنیم ، یا این که داده های کم اهمییت تر را در جایی دیگر ذخیره کنیم

upsert - on conflict در شرایطی که اینسرت با مشکل مواجه می شود ، از تکنیک هایی استفاده می کنیم که راه حل جایگزین استفاده شود

cursor

گاهی داده ای که نیاز است فچ کنیم ، تعداد سطر های زیادی دارد و در صورت دریافت همه ی آنها ، مموری الکی اشغال می شه ، می توانیم یک ترنزاکشن باز کنیم و یک شی از داده ی سلکت شده در آن بسازیم و تکه تکه ، از کرسر داده را بخوانیم ، در حقیقت پیمایش یا ایتریت کردن داده سنگین از دیتا بیس را این مفهوم هندل می کند . نکته : در جوانی بعد از ایجاد کرسر ، آن رو فچ آل می کردیم و خب از این قابلیت استفاده نمی کردیم .

lookup table

اگر بخواهیم ستونی با تنوع داده های محدود داشته باشیم مثلا ستون زبان و داده هایی مانند انگیسی و فارسی و…

حال اگر تنوع داده ثابت باشد می توان از ENUM or smallint استفاده کرد اما اگر امکان افزایش باشد ۲ روش مجود دارد .

• راه direct varible (varchar ) ـــ در این روش به صورت تکراری مقدار را می نویسیم ایرا اینه readandancy یا تکراری بودن پیش میاد همچنین aggregate مشکل هست

• راه lookup table (reference table) ــــ این همون جدول فارن کی هست خوبیش اینه از تکراری بودن داده جلوگیری می کنه همچنین اجازه ورود هر داده را نداره بدیش اینه جویین نیازه ولی اگر بخوهیم از groupby aggregate استفاده کنیم بهتر است

lookup table vs varchar index

گاهی یه کالمن زیاد داده هاش متمایز نیست cardinality (number of distinct values) و تعدادشون کمه ، در این صورت بهتره تنها ایندکس کنیم

اما اگر تمایز زیاد بود یا احتمال می دادیم که در آینده پییدگی قراره اضافه بشه یا طول ورچر خیلی قراره طولانی بشه بهتر از لوکاپ تیبل استفاده کنیم

همچنین لوکاپ می تواند راحت تر کنترل کند داده ی جدید تکراری است یا جدید

در کل نتیجه ای که گرفتم ، اگر قراره کاربر بیاد و دستی اضافه کنه ، لوکاء بهتره ولی اگر مانند enum تعداد مشخصه ، ایندکس گذاری هم در پرفورمنس هم در راحتی و خوانایی بهتره

index

هر موقع ما یه ستون را ایندکس می کنیم ، در حقیقت یه تیبل جدید می سازیم که با توجه به نوع ایندکس ، ترتیب آن چیده میشود

مثل اگر یک جدول ، ۳ ایندکس دارد ، ۳ تیبل جدا وجود دارد که با توجه به سیاست ما ، ان مرتب شده

مثلن اگر سرچ کنیم دانشجو های مرد و متاهل و 20 نفر ، ابتدا پستگرس با استفاده از planner تصمیم می گیرد از ایندکس مرد ها چند دانشجو بردارد و آن ها را در ایندکس متاهل ها تا ۲۰ عدد بردارد

نکته منفی این است که هر تیبل به صورت موازی چند داد می آورد و در نهایت اشتراک گیری می شود که زمان بر است ، راه حل پایین :

می توان از کامپوزیت ایندکس استفاده کرد ، یعنی چند ستون را یک ایندکس کنیم

با توجه به این که میدانیم کوییری بالا خیلی استفاده می شود ، هر دو را در یک ایندکس می گذاریم .

توجه داشته باشید ترتیب کامپوزیت ایندکس در تایپ B_TREE خیلی مهم است و شرط های اصلی باید سمت چپ باشند

همچنین می توان ابتدای کوییری explain analize گذاشت و دید که زمان و ترتیب جست و جو در ایندکس ها چقدر است

همچنین توجه شود اگر ستونی که تنها ۳ داده مانند “good”, “normal”, and “bad” راذخیره می کند ، ایندکس کنیم ، تکرار در ذخیره ایجاد نمی شود

types of index

B-Tree

• مقایسه ترتیبی و محدوده (=, <, >, ⇐, >=, BETWEEN)

• ترتیب‌سازی و ORDER BY

• ساپورت LIKE ‘prefix%’ (فقط اگر الگو با wildcard شروع نشود)

• یونیک - uniqueness (شاخص‌های یکتا معمولاً بوسیله btree ساخته می‌شوند)

• برای ستون‌های با cardinality خیلی پایین (مثلاً boolean) معمولاً بی‌فایده است.

• معیب : multicolumn index ترتیب ستون‌ها اهمیت دارد: CREATE INDEX idx ON t (a,b) سریع‌تر برای WHERE a = ? AND b = ? ولی نه لزوماً برای WHERE b = ? تنها.

Hash

• فقط برای تطابق دقیق (=) بسیار سریع است.

• کاربرد محدود: فقط equality؛ نه range، نه ordering.

GIN (Generalized Inverted Index)

یک ورودی دارد که نشان می‌دهد آن term در کدام ردیف‌ها وجود دارد.

• برای جستجوی full-text بسیار کارا.

• ایندکس سنگین (حجم زیاد)؛ نوشتن (INSERT/UPDATE) کندتر می‌شود.

GiST

• همچنین full-text می‌تواند از GiST استفاده کند (هرچند GIN معمول‌تر است).
• مناسب برای داده‌های چندبعدی، بازه‌ای، جغرافیایی و عملیات overlap / nearest. برای جغرافیایی / هندسی

tips

دلیل این نام گذاری ، عمق( depth ) این درخت ها ثابت است مثلا استفاده از اینکس با تایپ B_Tree (Balanced tree) ، پیچیدگی زمانی در این ایندکس برابر O(log n) است

مثال

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10, 2)
);
 
CREATE INDEX idx_customer_orderdate ON Orders(CustomerID, OrderDate);
 
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount) VALUES
(1, 101, '2024-01-15', 250.00),
(2, 102, '2024-01-16', 150.00),
(3, 101, '2024-01-17', 200.00),
(4, 103, '2024-01-18', 300.00),
(5, 102, '2024-01-19', 350.00),
(6, 101, '2024-01-20', 400.00);
 
 
-- Query using the composite index
SELECT * FROM Orders 
WHERE CustomerID = 101 AND OrderDate >= '2024-01-15';
 
 
              [101, 102, 103]
             /        |        \
          /           |         \
   [101, 2024-01-15] [102, 2024-01-16] [103, 2024-01-18]
      /      |       \                \
     /       |        \                \
[101, 2024-01-17]  [102, 2024-01-19]  [NULL]
      |
  [101, 2024-01-20]
 
 

tips

• تعداد کانکشن به صورت دیفالت ۱۰۰ تا هست و می توان با این دستور لیست و تعداد سرویس هایی که کانت شده اند را دید .

  SELECT * FROM pg_stat_activity;
  

  • باید بعد از هر کانکشن در سرویس، کلوز کرد .
  • همیشه در سینگلتون کانکشن ، می بایست ریکانکشن هم نوشت .

Preper statement

به صورت دیفالت هر بار که درخواست ثبت در دیتا بیس میزنیم تمامی اطلاعات مانند insert into ارسال میشه ، میتونیم با ننظیم این مفهوم ، دیگه این رو ارسال نکنیم و سرعت رو بیشتر کنیم

Parameterized Queries

به جای این که کوییری استرینگ رو بسازیم ، مقادیر و کلید ها رو جدا میفرستیم و باعث جلوگیری از sql injection میشه

transaction

tips

Skip default transaction

میتونیم موقع کانکشن بگیم نیاز نیست برای هر کوییری ، ترنساکشن بسازی و سرعت بیشتر میشه

sharding

قبل از شروع باید به چند نکته دقت کرد

• در شاردینگ برای پرفورمنس ، باید نود ها ایزوله باشند به این معنی که مستقل از هم بتوان کار کرد مثلن کوییری نباشه که رو همه اعمال شه یا جویینی وسطشون باشه

• هدف توضیع داده است ، نباید در بعضی نود ها داده بیشتر باشد و درست تقسیم نشن

• باید در همون ابتدا شارد کی رو خیلی دقیق و با دانش انتخاب کنیم

• احتمال ابنم بدیم که شاید ادمین بخواد کوییری هایی بزنه که جویین داشته باشه بین چند شارد ، اونجا جویین بین چند نود جدا خیلی خیلی پیچیده میشه

• توجه شود به صورت معمول باید تنها کوییری بر اساس shard key باشد ، و اگر بر اساس دیگر ستون ها باشند ، باید کوییری پارالر زد

راه های شارد کرد _Methods for Implementing Sharding in PostgreSQL

• Manual Sharding:

در این حالت در لایه اپلیکیشن و لاجیک کد این رو هندل میکنیم ، پستگرس نمیداند شارد شده ، همچنین خودمون باید دیتابیس های جدا با کانفیگ های دستی بیاریم بالا

برای مثال چند تا پستگرس رو نود های مختلف بیاریم بالا و شارد کی رو یوزر آی دی بزاریم ، در نهایت در لایه ی اپلیکیشن بدانیم یوزر های فرد در دیتابیس اول هستن و زوج در دومی

• partition

خود پستگرس قابلیتی داره که میتونه تیبل ها بزرگ رو شارد کنه ، اما به صورت دیفالت روی یک کانکشن و یک سینگل اینستنس انجام میده

Example:CREATE TABLE users ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, name TEXT, data JSONB ) PARTITION BY HASH (user_id);

CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 2, REMAINDER 1);

• citus

یه افزونه به پستگرس کهشاردینگ رو هندل میکنه

توجه کنید کوییری ها پارالر هستند

CREATE TABLE users ( id SERIAL PRIMARY KEY, user_id INT NOT NULL, name TEXT ) DISTRIBUTED BY (user_id);

Partitioning

• یعنی تقسیم یک جدول بزرگ به چند جدول کوچک‌تر (partitions) بر اساس یک کلید (مثل تاریخ، id range، list یا hash). از PostgreSQL 10 به بعد declarative partitioning بهتر و رسمی شده.

• Range partitioning:

محدوده‌های عددی/تاریخی (مثلاً بر اساس created_at ماهیانه).

• List partitioning:

مقادیر گسسته (مثلاً country یا region).

• Hash partitioning:

بر اساس هش، برای توزیع یکنواخت داده.

adv/disadv

• سرعت بالا - فقط پارتیشن‌های مرتبط را اسکن می‌کند

CREATE TABLE events (
  id bigserial,
  created_at timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2025_11 PARTITION OF events FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

varchar vs char vs nvarchar

ابتدا باید دانست که postgre به صورت دیفالت ، utf8 encoding string رو فرض میگیره

• چون varchar و nvarchar هر دو variable-length در نتیجه اگر طول رو 255 هم در نظر بگیریم ، باز هم به اندازه مورد نیاز مموری اختصاص میده

varchar

ایده آل برای ذخیره ascii یا utf8

nvarchar (nature varchar)

هر دو مقدار_طول ذخیره میکنند اما طراحی شده برای ذخیره unicode data

ایده آل برای متنی که شاید چند زبانه باشد

منطق و شرط ها در کد یا دیتا بیس؟

اگر منطق در دیتا بیس باشه ، خوبیش اینه که حتی سوپر یوزر هم نمیتونه در دیتابیس به صورت دستی ، داده رو دست کاری کنه

اما هزینه نگهداری و توسعه بیشتره از مزایاش هست ،

ولی اگر منطق در کد باشه ، سوپر یوزر میتونه بدون توجه به منطق کد ، crud کنه ، چند راه برای جلو گیری از داستان هست :

• گاهی یونیک کردن سطر ها سخت تر از شرط های پیش فرض در کانستریت تیبل هست ، مانند چک کردن استتوس بی جای استفاده از getFirst از گرفتن لیستی و در نهایت شرط بزاریم اگر تعداد لیست مخالف یک بود ، بگه

partial unique index

یکی از خوبی های دیگه ی پستگرس به نسبت mysql داشتن این مورد هست

گاهی ما می خوایم هر یوزر ، یه ایمیل داشته باشه ، پس این دو رو یونیک تو گدر میکنیم ، اما گاهی نیاز داریم هر یوزر تنها یه ایمیل فعال داشته باشه ، در این حالت میتونه چندین ایمیل غیر فعال داشته باشه ، در این صورت از دستور زیر استفاده میکنیم

CREATE UNIQUE INDEX unique_active_phone_number ON user_phone_numbers (user_id)
WHERE is_active = TRUE;

بر خلاف mysql که چندین engine داره مثل innoDb , myisam ، پستگرس نداره

ابزار های distribiuted transaction

• پستگرس به صورت پیش فرض 2pc رو ساپورت میکنه
• میشه از Postgres-XL/Postgres-XC استفاده کرد ، ابزار هایی با اینترفیس پستگرس هستند ، توجه شود اکستنشن نیست و قابلیت های توضیع پذیری را فراهم میکند مانند sharding , distrib transaction , cross_node query توجه شود از 2pc درون خودش استفاده میکنه

مکانیزم های ذخیره پستگرس

• Heap Storage: به صورت دیفالت با این مکانیزم ، استور میکنه ، هر جا که پیدا کنه ذخیره میکنه

• TOAST (The Oversized-Attribute Storage Technique): سطر های خیلی بزرگ مانند باینری یا تکست لارج رو یاکامپرس میکنه بعد ذخیره میکنه ، یا در مکان دیگه ذخیره میکنه

• Unlogged Tables:

داده لاگ میشه ولی ذخیره در همان لحظه نمیشه ، برای زمانی استفاده میشه که سرعت بالا میخواییم ولی داده اهمییت نداره

• Foreign Data Wrappers (FDW):

اجازه میده دیتابیس های دیگه مثل مای اسکیو ال یا دیگر پستگرس ها دسترسی به دیتا ها ما داشته باشند ، با فرض شناخت تیبل ها

tips

ETL (Extract, Transform, Load)

گاهی بعد از آپدیت یا تغییر در جدول که موجود است ، نیازه داده های قدیمی رو توی جدول قدیمی بودن رو در جدول با ساختار یا نام جدید ذخیره کنیم ، شاید نیاز باشد در حین انتقال تغییری هم به داده بدهیم ، حال باید از این روش استفاده کنیم

code-first

گاهی اوقات یه بخشی از پروژه خارج از سرویس ما هست مانند دستورات دیتابیس که زبون گولنگ یا پایتون باید زبان sql بزنیم ، مثلن ORM یا cache ، در این حالت تصمیم میگیریم لاجیک و کار رو از کد خودمون خارج نکنیم و از طریق کد خودمون توی پروژه هایی که داریم این کار رو انجام بدیم ، به عبارت دیگه اول کد میزنیم ، سپس اسکیمای دیتابیس رو ایجاد میکنیم

دیگه دولوپر تنها باید روی کد های خودشان کار کند و کد های خارج از پروژه رو نگاه نکند ، نیازی نیست چندین ابزار مدیریت ، مستند و … شود

در برابر این راه Database-First هست که ابتدا توی دیتابیس اسکیما رو ایجاد می کنیم و بعد با استفاده از اون اسکیما ، کد های خودمون رو ایجاد می کنیم یکی دیگش Model-first است که اول کلاس ها ، انتیتی ها رو در بیزینس در نظر میگیریم و بعد کد و اسکیما رو میسازیم کانند

میشه گفت gorm بیشتر Model-first هست و ent بیشتر code-first هست