PostgreSQL

01Why Postgres: features that matter

PostgreSQL follows the SQL standard closely and adds a lot on top. Constraints are strong: CHECK, foreign keys that can be deferred to commit time, and EXCLUDE constraints that can forbid overlapping time ranges, such as double bookings. DDL is transactional, so a failed migration rolls back completely. Common table expressions (WITH queries), including recursive ones for trees, keep complex queries readable. Window functions, upserts (INSERT ... ON CONFLICT), RETURNING, arrays, ranges, enums and generated columns are all built in. Extensions add whole capabilities: pg_trgm for fuzzy search, PostGIS for maps, pgvector for embeddings, pg_stat_statements for query stats. PostgreSQL 18 (September 2025) added asynchronous I/O and a native uuidv7() function.

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 done right

JSONB stores JSON in a parsed binary format that can be indexed and queried. Operators like -> and ->> read fields, @> tests containment, and jsonb_path_query supports JSONPath. It is ideal for truly variable data: product attributes that differ per category, integration payloads, settings or CMS blocks. It is a poor fit for data you filter, join or constrain often; that belongs in real columns with types and foreign keys. A practical pattern is to keep core fields relational and put the long tail in a JSONB column, indexed with GIN. If one JSON field becomes important, promote it to a generated column or a real column. Remember that updating any part of a JSONB value rewrites the whole value.

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"}';

03Indexes: B-tree, GIN and friends

B-tree is the default and handles equality, ranges and sorting; use it for ids, foreign keys, dates and most columns. GIN (generalised inverted index) indexes the elements inside a value, which makes it right for JSONB containment, arrays and full-text search; it is larger and slower to update than B-tree. GiST supports ranges, geometry and exclusion constraints. BRIN is tiny and works for huge, naturally ordered tables like logs. Postgres also has partial indexes (only rows matching a WHERE clause, such as active rows), expression indexes (on lower(email)) and INCLUDE columns for covering indexes. Build indexes on live tables with CREATE INDEX CONCURRENTLY, and check plans with EXPLAIN (ANALYZE, BUFFERS).

• B-tree: equality, ranges, ORDER BY.
• GIN: JSONB, arrays, full-text, trigram search.
• GiST: ranges, geometry, exclusion constraints.
• Partial and expression indexes: smaller and more precise.

04MVCC, VACUUM and connections

PostgreSQL implements MVCC (multi-version concurrency control) by never updating rows in place. An UPDATE writes a new row version and marks the old one as dead; each transaction sees the versions that were visible when its snapshot was taken. Readers never block writers. The cost is cleanup: dead rows stay until VACUUM removes them, and autovacuum does this in the background. If autovacuum falls behind, tables bloat and queries slow down; long-running transactions make it worse because they prevent cleanup. VACUUM also prevents transaction ID wraparound, so never disable it. The default isolation level is READ COMMITTED. Each connection is a separate server process, so use a pooler such as PgBouncer in transaction mode when many app instances or serverless functions connect.

• Monitor dead tuples and autovacuum in pg_stat_user_tables.
• Avoid long idle-in-transaction sessions; set idle_in_transaction_session_timeout.
• Pool connections; hundreds of direct connections waste memory.

05Row-level security for multi-tenancy

Row-level security (RLS) lets the database filter rows per request. You enable it on a table and write a policy, for example 'a row is visible only if its company_id equals the current tenant setting'. The application sets that setting at the start of each transaction, and then even a query that forgets its WHERE clause cannot see another tenant's data. Important details: table owners and superusers bypass RLS unless you use FORCE ROW LEVEL SECURITY and connect as a dedicated non-owner role; with connection pooling, set the tenant with set_config(..., true) so it is local to the transaction; and index the tenant column because every query now filters by it. RLS works best as a second layer on top of explicit tenant filters in the application.

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 with PostgreSQL

Prisma is a TypeScript ORM: you describe models in schema.prisma, and it generates a fully typed client and SQL migrations. In development, prisma migrate dev compares the schema with the database, writes a migration file and applies it. In CI and production, run prisma migrate deploy, which only applies committed migrations and never resets data. Use --create-only to generate a migration and edit the SQL before applying it, which is how you add things Prisma's schema cannot fully express, such as RLS policies, CHECK and exclusion constraints, or CREATE INDEX CONCURRENTLY. Client extensions can add cross-cutting rules, such as automatically scoping every query to a tenant. Prisma 7 moved to a Rust-free client with driver adapters and a prisma.config.ts file, so check the upgrade guide when moving from version 6.

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

07Backups, and Postgres vs MySQL

pg_dump makes a logical backup of one database; the custom format (-Fc) is compressed and lets pg_restore restore selectively and in parallel. For larger systems and point-in-time recovery, combine a physical base backup (pg_basebackup) with continuous WAL archiving, ideally through a tool like pgBackRest or WAL-G that handles retention, compression and off-site storage. Test restores regularly. Choosing between Postgres and MySQL: both are excellent for standard web apps. I lean to Postgres for complex domains, strict data integrity, JSONB, advanced SQL, multi-tenancy with RLS, or when I need extensions such as PostGIS or pgvector. I stay with MySQL or MariaDB when the hosting, the team or the framework ecosystem is built around it and the workload is simple reads and writes.

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

08Reading plans: EXPLAIN, statistics and estimates

The planner chooses a plan from estimated row counts, and most bad plans come from bad estimates. EXPLAIN (ANALYZE, BUFFERS) runs the query and shows, for each node, estimated rows versus actual rows, time, and how many pages came from shared buffers (hit) versus disk (read); since PostgreSQL 18, BUFFERS is included automatically with ANALYZE. When estimates are off by ten times or more, the planner may pick a nested loop where a hash join was needed. Estimates come from statistics gathered by ANALYZE, which autovacuum runs, but they assume columns are independent. For correlated columns, such as city and postal code or tenant and status, create extended statistics so the planner knows the relationship. For skewed columns, raise the statistics target on that column. Prepared statements add a twist: after five executions Postgres may switch to a generic plan that ignores parameter values, which is bad when one tenant has millions of rows and others have ten; plan_cache_mode lets you control that.

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;

09Locks and zero-downtime DDL

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECTs. The lock itself may only be held for milliseconds, but the queue is the real danger: if a long transaction holds any lock on the table, the ALTER waits, and every query that arrives after it queues behind the ALTER. The application freezes, though nothing is technically slow. The fix is to set lock_timeout to a few seconds in migrations and retry, so a blocked ALTER gives up instead of stopping traffic. Know which operations are cheap. Adding a column with a constant default is metadata-only since PostgreSQL 11. Adding a foreign key or CHECK constraint can be split: add it NOT VALID, which is quick, then VALIDATE CONSTRAINT, which scans the table with a weaker lock that allows reads and writes. SET NOT NULL can skip its table scan if a validated CHECK (col IS NOT NULL) already exists. Changing a column type usually rewrites the table, so prefer a new column and a batched 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 modes and their side effects

Every PostgreSQL connection is a server process with its own memory, and memory for sorts and hashes (work_mem) is allocated per operation, so a single complex query can use several multiples of it. Hundreds of direct connections therefore waste memory and add contention; a few dozen active connections are usually optimal. PgBouncer in transaction mode lets thousands of clients share a small pool by assigning a server connection only for the duration of a transaction. That breaks anything that relies on session state between transactions: SET without LOCAL, session-level advisory locks, LISTEN and NOTIFY, temporary tables and, in older PgBouncer versions, server-side prepared statements (version 1.21 added support for protocol-level prepared statements through max_prepared_statements). This is why tenant settings for row-level security must be transaction-local. Run schema migrations over a direct connection, not through the transaction pooler, because migration tools, including Prisma Migrate, rely on session-level advisory locks. Serverless platforms make pooling mandatory, since every function instance opens its own connections.

• Size the pool from cores and storage speed, not from the number of app instances.
• Use set_config(name, value, true) or SET LOCAL inside transactions with a transaction pooler.
• Keep a separate direct connection string for migrations and maintenance.

11Replication, failover and major upgrades

Streaming replication ships the write-ahead log (WAL) to standbys that replay it, giving a byte-identical copy usable for reads and failover. With synchronous_standby_names and synchronous_commit you can require that a standby confirms each commit, trading latency for zero data loss on failover. Postgres itself does not elect a new primary; tools such as Patroni, or a managed service, handle health checks, promotion and fencing. Logical replication sends row changes per table instead of WAL bytes, so it works across major versions and can copy selected tables, which makes it the standard path for near-zero-downtime upgrades and migrations; it does not replicate DDL or sequence values, so you handle those yourself. Replication slots make the primary keep WAL until a consumer has received it; an abandoned slot makes the disk fill up, so set max_slot_wal_keep_size and alert on inactive slots. For in-place upgrades, pg_upgrade --link takes minutes even for large databases, and since PostgreSQL 18 it keeps planner statistics, so performance is normal right after the 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;