PostgreSQL

01Ինչու Postgres. կարևոր հնարավորություններ

PostgreSQL-ը սերտորեն հետևում է SQL ստանդարտին և դրա վրա շատ բան է ավելացնում։ Constraint-ները ուժեղ են՝ CHECK, foreign key-եր, որոնք կարելի է հետաձգել մինչև commit-ի պահը, և EXCLUDE constraint-ներ, որոնք կարող են արգելել համընկնող ժամանակային միջակայքեր, օրինակ՝ կրկնակի ամրագրումներ։ DDL-ը transactional է, ուստի ձախողված migration-ը ամբողջությամբ rollback է լինում։ Common table expression-ները (WITH հարցումներ), ներառյալ ռեկուրսիվները՝ ծառերի համար, բարդ հարցումները ընթեռնելի են պահում։ Window function-ները, upsert-ները (INSERT ... ON CONFLICT), RETURNING-ը, array-ները, range-երը, enum-ները և generated սյունակները բոլորը ներկառուցված են։ Extension-ները ամբողջական հնարավորություններ են ավելացնում՝ pg_trgm՝ fuzzy search-ի համար, PostGIS՝ քարտեզների համար, pgvector՝ embedding-ների համար, pg_stat_statements՝ հարցումների վիճակագրության համար։ PostgreSQL 18-ը (սեպտեմբեր 2025) ավելացրեց ասինխրոն I/O և native uuidv7() ֆունկցիա։

1-- recursive CTE: all subcategories of category 1
2WITH RECURSIVE tree AS (
3  SELECT id, parent_id, name FROM categories WHERE id = 1
4  UNION ALL
5  SELECT c.id, c.parent_id, c.name
6  FROM categories c JOIN tree t ON c.parent_id = t.id
7)
8SELECT * FROM tree;
9 
10-- forbid overlapping bookings for the same room
11CREATE EXTENSION IF NOT EXISTS btree_gist;
12ALTER TABLE bookings ADD CONSTRAINT no_overlap
13  EXCLUDE USING gist (room_id WITH =, period WITH &&);

02JSONB՝ ճիշտ ձևով

JSONB-ն JSON-ը պահում է parse արված բինար ֆորմատով, որը կարելի է ինդեքսավորել և հարցումներ անել։ -> և ->> օպերատորները կարդում են դաշտերը, @>-ը ստուգում է պարունակումը, իսկ jsonb_path_query-ն աջակցում է JSONPath։ Այն իդեալական է իսկապես փոփոխական տվյալների համար՝ ապրանքի ատրիբուտներ, որոնք տարբերվում են ըստ կատեգորիայի, ինտեգրացիաների payload-ներ, կարգավորումներ կամ CMS բլոկներ։ Այն վատ ընտրություն է այն տվյալների համար, որոնցով հաճախ filter, join կամ constraint եք անում. դրանց տեղը իրական սյունակներում է՝ տիպերով և foreign key-երով։ Գործնական pattern է հիմնական դաշտերը պահել ռելյացիոն, իսկ «երկար պոչը» դնել JSONB սյունակում՝ GIN-ով ինդեքսավորված։ Եթե JSON-ի որևէ դաշտ կարևոր է դառնում, բարձրացրեք այն generated սյունակի կամ իրական սյունակի։ Հիշեք, որ JSONB արժեքի ցանկացած մասի թարմացումը վերագրում է ամբողջ արժեքը։

1ALTER TABLE products ADD COLUMN attrs jsonb NOT NULL DEFAULT '{}';
2CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
3 
4-- all products with colour "black" and size "M"
5SELECT id, name
6FROM products
7WHERE attrs @> '{"colour": "black", "size": "M"}';

03Ինդեքսներ. B-tree, GIN և մյուսները

B-tree-ն լռելյայն է և մշակում է հավասարություն, միջակայքեր և դասավորում. օգտագործեք այն id-ների, foreign key-երի, ամսաթվերի և սյունակների մեծ մասի համար։ GIN-ը (generalised inverted index) ինդեքսավորում է արժեքի ներսում գտնվող տարրերը, ինչը այն ճիշտ ընտրություն է դարձնում JSONB containment-ի, array-ների և full-text search-ի համար. այն ավելի մեծ է և ավելի դանդաղ է թարմացվում, քան B-tree-ն։ GiST-ը աջակցում է range-եր, երկրաչափություն և exclusion constraint-ներ։ BRIN-ը փոքրիկ է և աշխատում է հսկայական, բնականորեն դասավորված աղյուսակների համար, ինչպես log-երը։ Postgres-ն ունի նաև partial ինդեքսներ (միայն WHERE պայմանին համապատասխանող տողեր, օրինակ՝ ակտիվ տողերը), expression ինդեքսներ (lower(email)-ի վրա) և INCLUDE սյունակներ՝ covering ինդեքսների համար։ Live աղյուսակների վրա ինդեքսները ստեղծեք CREATE INDEX CONCURRENTLY-ով, իսկ plan-երը ստուգեք EXPLAIN (ANALYZE, BUFFERS)-ով։

• B-tree. հավասարություն, միջակայքեր, ORDER BY։
• GIN. JSONB, array-ներ, full-text, trigram search։
• GiST. range-եր, երկրաչափություն, exclusion constraint-ներ։
• Partial և expression ինդեքսներ. ավելի փոքր և ավելի ճշգրիտ։

04MVCC, VACUUM և միացումներ

PostgreSQL-ը MVCC-ն (multi-version concurrency control) իրականացնում է՝ երբեք տողերը տեղում չթարմացնելով։ UPDATE-ը գրում է տողի նոր տարբերակ և հինը նշում որպես dead. յուրաքանչյուր transaction տեսնում է այն տարբերակները, որոնք տեսանելի էին իր snapshot-ը վերցնելու պահին։ Կարդացողները երբեք չեն արգելափակում գրողներին։ Գինը մաքրումն է. dead տողերը մնում են, մինչև VACUUM-ը հեռացնի դրանք, և autovacuum-ը դա անում է background-ում։ Եթե autovacuum-ը հետ է մնում, աղյուսակները ուռչում են, և հարցումները դանդաղում. երկար աշխատող transaction-ները վիճակն ավելի են վատացնում, որովհետև խանգարում են մաքրմանը։ VACUUM-ը նաև կանխում է transaction ID wraparound-ը, ուստի երբեք մի անջատեք այն։ Լռելյայն isolation level-ը READ COMMITTED-ն է։ Յուրաքանչյուր միացում առանձին սերվերային պրոցես է, ուստի օգտագործեք pooler, օրինակ՝ PgBouncer transaction mode-ում, երբ միանում են հավելվածի բազմաթիվ instance-ներ կամ serverless function-ներ։

• Հետևեք dead tuple-ներին և autovacuum-ին pg_stat_user_tables-ում։
• Խուսափեք երկար idle-in-transaction session-ներից. սահմանեք idle_in_transaction_session_timeout։
• Միացումները pool արեք. հարյուրավոր ուղիղ միացումներ իզուր հիշողություն են վատնում։

05Row-level security multi-tenancy-ի համար

Row-level security-ն (RLS) թույլ է տալիս, որ տվյալների բազան տողերը filter անի յուրաքանչյուր հարցման համար։ Միացնում եք այն աղյուսակի վրա և գրում policy, օրինակ՝ «տողը տեսանելի է միայն եթե նրա company_id-ն հավասար է ընթացիկ tenant կարգավորմանը»։ Հավելվածը այդ կարգավորումը սահմանում է յուրաքանչյուր transaction-ի սկզբում, և հետո նույնիսկ այն հարցումը, որը մոռացել է իր WHERE պայմանը, չի կարող տեսնել մեկ այլ tenant-ի տվյալները։ Կարևոր մանրամասներ. աղյուսակի owner-ները և superuser-ները շրջանցում են RLS-ը, եթե չեք օգտագործում FORCE ROW LEVEL SECURITY և չեք միանում որպես առանձին non-owner role. connection pooling-ի դեպքում tenant-ը սահմանեք set_config(..., true)-ով, որպեսզի այն լոկալ լինի transaction-ի համար. և ինդեքսավորեք tenant սյունակը, որովհետև այժմ յուրաքանչյուր հարցում filter է անում ըստ դրա։ RLS-ը լավագույնս աշխատում է որպես երկրորդ շերտ՝ հավելվածում բացահայտ tenant filter-ների վրա։

1ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
2ALTER TABLE orders FORCE ROW LEVEL SECURITY;
3 
4CREATE POLICY tenant_isolation ON orders
5  USING (company_id = current_setting('app.company_id')::uuid)
6  WITH CHECK (company_id = current_setting('app.company_id')::uuid);
7 
8-- per request, inside a transaction:
9BEGIN;
10SELECT set_config('app.company_id', '0193a1b2-0000-7000-8000-000000000001', true);
11SELECT id, status FROM orders;   -- only this tenant's rows
12COMMIT;

06Prisma PostgreSQL-ի հետ

Prisma-ն TypeScript ORM է. մոդելները նկարագրում եք schema.prisma-ում, և այն գեներացնում է ամբողջությամբ տիպավորված client և SQL migration-ներ։ Development-ում prisma migrate dev-ը համեմատում է սխեման տվյալների բազայի հետ, գրում migration ֆայլ և կիրառում այն։ CI-ում և production-ում աշխատեցրեք prisma migrate deploy, որը կիրառում է միայն commit արված migration-ները և երբեք չի reset անում տվյալները։ Օգտագործեք --create-only՝ migration գեներացնելու և SQL-ը նախքան կիրառելը խմբագրելու համար. այսպես եք ավելացնում այն, ինչ Prisma-ի սխեման չի կարող լիովին արտահայտել, օրինակ՝ RLS policy-ներ, CHECK և exclusion constraint-ներ կամ CREATE INDEX CONCURRENTLY։ Client extension-ները կարող են ավելացնել cross-cutting կանոններ, օրինակ՝ յուրաքանչյուր հարցման ավտոմատ սահմանափակում tenant-ով։ Prisma 7-ը անցավ առանց Rust-ի client-ի՝ driver adapter-ներով և prisma.config.ts ֆայլով, ուստի 6-րդ տարբերակից անցնելիս ստուգեք upgrade guide-ը։

1model Order {
2  id         String   @id @default(uuid(7)) @db.Uuid
3  companyId  String   @db.Uuid
4  status     String
5  totalMinor BigInt
6  createdAt  DateTime @default(now()) @db.Timestamptz
7  items      OrderItem[]
8 
9  @@index([companyId, createdAt(sort: Desc)])
10}
11 
12// npx prisma migrate dev --name add_orders --create-only
13// review/edit SQL, then: npx prisma migrate dev
14// production:            npx prisma migrate deploy

07Backup-ներ, և Postgres ընդդեմ MySQL

pg_dump-ը մեկ տվյալների բազայի logical backup է անում. custom ֆորմատը (-Fc) սեղմված է և թույլ է տալիս pg_restore-ին վերականգնել ընտրովի և զուգահեռ։ Ավելի մեծ համակարգերի և point-in-time recovery-ի համար համադրեք physical base backup-ը (pg_basebackup) անընդհատ WAL archiving-ի հետ, իդեալում՝ pgBackRest-ի կամ WAL-G-ի նման գործիքով, որը կառավարում է retention-ը, սեղմումը և off-site պահպանումը։ Պարբերաբար ստուգեք վերականգնումը։ Postgres-ի և MySQL-ի միջև ընտրություն. երկուսն էլ հիանալի են ստանդարտ վեբ հավելվածների համար։ Ես թեքվում եմ դեպի Postgres բարդ domain-ների, տվյալների խիստ ամբողջականության, JSONB-ի, առաջադեմ SQL-ի, RLS-ով multi-tenancy-ի դեպքում, կամ երբ պետք են extension-ներ, ինչպես PostGIS-ը կամ pgvector-ը։ Մնում եմ MySQL-ի կամ MariaDB-ի հետ, երբ hosting-ը, թիմը կամ framework-ի էկոհամակարգը կառուցված են դրա շուրջ, և ծանրաբեռնվածությունը պարզ read-եր ու write-եր են։

1# logical backup, compressed custom format
2pg_dump -Fc -U backup_user -h localhost -d shop -f shop-$(date +%F).dump
3 
4# restore into a fresh database with 4 parallel jobs
5createdb -U postgres shop_restore_test
6pg_restore -U postgres -d shop_restore_test -j 4 shop-2026-11-05.dump

08Պլանների ընթերցում՝ EXPLAIN, վիճակագրություն և գնահատականներ

Planner-ը պլանն ընտրում է տողերի գնահատված քանակից, և վատ պլանների մեծ մասը գալիս է վատ գնահատականներից։ EXPLAIN (ANALYZE, BUFFERS)-ը գործարկում է հարցումը և յուրաքանչյուր հանգույցի համար ցույց է տալիս գնահատված և իրական տողերը, ժամանակը, ինչպես նաև քանի էջ է եկել shared buffers-ից (hit) և քանիսը՝ սկավառակից (read)․ PostgreSQL 18-ից սկսած BUFFERS-ը ավտոմատ ներառվում է ANALYZE-ի հետ։ Երբ գնահատականները տասն անգամ կամ ավելի են սխալ, planner-ը կարող է ընտրել nested loop այնտեղ, որտեղ hash join էր պետք։ Գնահատականները գալիս են ANALYZE-ի հավաքած վիճակագրությունից, որը գործարկում է autovacuum-ը, բայց դրանք ենթադրում են, որ սյունակներն անկախ են։ Կոռելացված սյունակների համար, ինչպես քաղաքը և փոստային ինդեքսը կամ tenant-ը և status-ը, ստեղծեք extended statistics, որպեսզի planner-ը իմանա նրանց կապը։ Անհավասարաչափ բաշխված սյունակների համար բարձրացրեք այդ սյունակի statistics target-ը։ Prepared statement-ները լրացուցիչ նրբություն ունեն․ հինգ կատարումից հետո Postgres-ը կարող է անցնել generic պլանի, որն անտեսում է պարամետրերի արժեքները, և դա վատ է, երբ մեկ tenant ունի միլիոնավոր տողեր, իսկ մյուսները՝ տասը․ plan_cache_mode-ը թույլ է տալիս դա կառավարել։

1EXPLAIN (ANALYZE, BUFFERS)
2SELECT * FROM orders WHERE company_id = $1 AND status = 'open';
3 
4-- planner thinks company and status are independent; teach it otherwise
5CREATE STATISTICS st_orders_company_status (dependencies, mcv)
6  ON company_id, status FROM orders;
7ALTER TABLE orders ALTER COLUMN company_id SET STATISTICS 1000;
8ANALYZE orders;
9 
10-- top queries by total time (needs pg_stat_statements)
11SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
12FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

09Lock-եր և zero-downtime DDL

ALTER TABLE-ի ձևերի մեծ մասը վերցնում է ACCESS EXCLUSIVE lock, որը կոնֆլիկտում է ամեն ինչի հետ, այդ թվում՝ սովորական SELECT-ների։ Lock-ը կարող է պահվել ընդամենը միլիվայրկյաններ, բայց իրական վտանգը հերթն է․ եթե երկար transaction-ը աղյուսակի վրա որևէ lock է պահում, ALTER-ը սպասում է, և դրանից հետո եկող ամեն հարցում հերթ է կանգնում ALTER-ի հետևում։ Հավելվածը սառչում է, թեև տեխնիկապես ոչինչ դանդաղ չէ։ Լուծումը migration-ներում lock_timeout-ը մի քանի վայրկյան սահմանելն ու կրկնելն է, որպեսզի արգելափակված ALTER-ը հրաժարվի, այլ ոչ թե կանգնեցնի տրաֆիկը։ Իմացեք, թե որ գործողություններն են էժան։ Հաստատուն default-ով սյունակ ավելացնելը PostgreSQL 11-ից սկսած միայն metadata է փոխում։ Foreign key կամ CHECK constraint ավելացնելը կարելի է բաժանել․ ավելացրեք այն NOT VALID-ով, ինչն արագ է, հետո VALIDATE CONSTRAINT, որը սկանավորում է աղյուսակը ավելի թույլ lock-ով, որը թույլ է տալիս ընթերցումներ և գրառումներ։ SET NOT NULL-ը կարող է բաց թողնել աղյուսակի սկանավորումը, եթե արդեն կա validated CHECK (col IS NOT NULL)։ Սյունակի տիպը փոխելը սովորաբար վերագրում է ամբողջ աղյուսակը, ուստի նախընտրեք նոր սյունակ և batch-երով backfill։

1SET lock_timeout = '5s';
2 
3-- foreign key without blocking writes for the whole scan
4ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
5  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
6ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
7 
8-- NOT NULL without a long exclusive scan
9ALTER TABLE orders ADD CONSTRAINT orders_currency_nn CHECK (currency IS NOT NULL) NOT VALID;
10ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;
11ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;   -- uses the check, no scan
12ALTER TABLE orders DROP CONSTRAINT orders_currency_nn;

10Connection pooling-ի ռեժիմները և դրանց կողմնակի ազդեցությունները

PostgreSQL-ի յուրաքանչյուր կապ սերվերային պրոցես է իր սեփական հիշողությամբ, իսկ sort-երի և hash-երի հիշողությունը (work_mem) հատկացվում է ըստ գործողության, ուստի մեկ բարդ հարցումը կարող է օգտագործել դրա մի քանի բազմապատիկը։ Հետևաբար հարյուրավոր ուղիղ կապերը վատնում են հիշողություն և ավելացնում մրցակցություն․ սովորաբար օպտիմալ են մի քանի տասնյակ ակտիվ կապեր։ PgBouncer-ը transaction ռեժիմում թույլ է տալիս հազարավոր կլիենտների կիսել փոքր pool՝ սերվերային կապը տրամադրելով միայն transaction-ի տևողությամբ։ Դա կոտրում է այն ամենը, ինչը հենվում է transaction-ների միջև session-ի վիճակի վրա՝ SET առանց LOCAL-ի, session-level advisory lock-եր, LISTEN և NOTIFY, ժամանակավոր աղյուսակներ և PgBouncer-ի հին տարբերակներում՝ server-side prepared statement-ներ (1.21 տարբերակը ավելացրեց պրոտոկոլի մակարդակի prepared statement-ների աջակցություն max_prepared_statements-ի միջոցով)։ Ահա թե ինչու row-level security-ի tenant կարգավորումները պետք է լինեն transaction-local։ Սխեմայի migration-ները գործարկեք ուղիղ կապով, ոչ թե transaction pooler-ի միջոցով, քանի որ migration գործիքները, այդ թվում Prisma Migrate-ը, հենվում են session-level advisory lock-երի վրա։ Serverless հարթակները pooling-ը դարձնում են պարտադիր, քանի որ յուրաքանչյուր function instance բացում է իր սեփական կապերը։

• Pool-ի չափը որոշեք ըստ միջուկների և պահոցի արագության, ոչ թե ըստ հավելվածի instance-ների քանակի։
• Transaction pooler-ի դեպքում transaction-ների ներսում օգտագործեք set_config(name, value, true) կամ SET LOCAL։
• Migration-ների և սպասարկման համար պահեք առանձին ուղիղ connection string։

11Replication, failover և major upgrade-ներ

Streaming replication-ը write-ahead log-ը (WAL) ուղարկում է standby-ներին, որոնք այն վերարտադրում են՝ տալով բայթ առ բայթ նույնական պատճեն, որը կարելի է օգտագործել ընթերցումների և failover-ի համար։ synchronous_standby_names-ով և synchronous_commit-ով կարող եք պահանջել, որ standby-ը հաստատի յուրաքանչյուր commit՝ latency-ն փոխանակելով failover-ի ժամանակ տվյալների զրո կորստի հետ։ Postgres-ն ինքը նոր primary չի ընտրում․ Patroni-ի նման գործիքները կամ managed ծառայությունը կատարում են health check-երը, promotion-ը և fencing-ը։ Logical replication-ը WAL բայթերի փոխարեն ուղարկում է տողերի փոփոխությունները ըստ աղյուսակների, ուստի աշխատում է major տարբերակների միջև և կարող է պատճենել ընտրված աղյուսակներ, ինչը այն դարձնում է գրեթե զրո downtime-ով upgrade-ների և migration-ների ստանդարտ ուղի․ այն չի replicate անում DDL-ը և sequence-ների արժեքները, ուստի դրանք պետք է ինքներդ կարգավորեք։ Replication slot-երը ստիպում են primary-ին պահել WAL-ը, մինչև consumer-ը այն ստանա․ լքված slot-ը լցնում է սկավառակը, ուստի սահմանեք max_slot_wal_keep_size և alert դրեք անգործ slot-երի վրա։ Տեղում upgrade-ի համար pg_upgrade --link-ը նույնիսկ մեծ բազաների համար րոպեներ է տևում, և PostgreSQL 18-ից սկսած այն պահպանում է planner-ի վիճակագրությունը, ուստի upgrade-ից անմիջապես հետո արտադրողականությունը նորմալ է։

1-- is any slot holding back WAL?
2SELECT slot_name, slot_type, active,
3       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
4FROM pg_replication_slots;
5 
6-- replication lag per standby (run on the primary)
7SELECT application_name, state, replay_lag FROM pg_stat_replication;