PostgreSQL

Area: Relational database
Related: graphile-worker, Microsoft Graph, IndexedDB

What is it?

PostgreSQL (“Postgres”) is an open-source relational database. You store data in tables of rows and columns, connect tables with keys, and ask questions in SQL. It’s been developed since the 1980s, and today it’s the default choice for most new web apps.

A spreadsheet that’s strict about types, enforces your rules, never loses a committed write, lets thousands of people use it at once, and can answer any question you can write in SQL.

Why people pick it over other databases:

• Correctness. Real transactions, constraints that are actually enforced, and careful handling of concurrent writes.
• Breadth. Relational data, JSON documents (jsonb), full-text search, geospatial data (PostGIS), vectors for AI (pgvector), all in one database.
• Extensions. New index types and data types can be added without forking it.
• Everywhere. Every cloud offers it managed: Azure Database for PostgreSQL, AWS RDS and Aurora, Google Cloud SQL, Supabase, Neon.

Tables, types and keys

CREATE TABLE tenants (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE email_queue (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  dedupe_key  text NOT NULL,
  status      text NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'sent', 'failed')),
  payload     jsonb NOT NULL DEFAULT '{}',
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, dedupe_key)
);

Types worth getting right from day one

• uuid with gen_random_uuid() for ids you show outside the database. They’re hard to guess and can be generated anywhere. bigint identity columns (GENERATED ALWAYS AS IDENTITY) are smaller and faster for internal ids.
• text, not varchar(255). In Postgres they perform the same. If you need a limit, add a CHECK (length(x) <= 200).
• timestamptz, never timestamp. timestamptz stores a real moment in time (internally in UTC) and converts on the way in and out. Plain timestamp stores a wall-clock reading with no zone, and you’ll eventually mix up zones.
• numeric for money, or integer cents in a bigint. Never float or real.
• jsonb (binary JSON), not json, for JSON data. It can be indexed and compared. json just stores text.
• boolean, arrays (text[]), inet, ranges, and enums are all built in.

Constraints: let the database enforce the rules

• PRIMARY KEY: unique and not null, one per table.
• REFERENCES (foreign keys): a row can’t point at something that doesn’t exist. ON DELETE CASCADE deletes children with the parent. ON DELETE SET NULL or RESTRICT are the other common choices.
• UNIQUE: no duplicates, across one or several columns.
• CHECK: any rule you can write as an expression.
• NOT NULL: make it the default habit. Allow NULL only when “no value” is a real state.

Application code has bugs and runs in many processes. A constraint is checked on every write, by every client, forever.

Foreign keys don’t create an index on the referencing column. email_queue.tenant_id has no index unless you add one. Without it, deleting a tenant scans the whole email_queue table, and joins on tenant_id are slow.

Upserts and idempotency

A UNIQUE constraint plus ON CONFLICT makes writes safe to repeat:

-- Insert once; a repeat with the same (tenant_id, dedupe_key) does nothing
INSERT INTO email_queue (tenant_id, dedupe_key, payload)
VALUES ($1, $2, $3)
ON CONFLICT (tenant_id, dedupe_key) DO NOTHING
RETURNING id;   -- returns no row when it was a duplicate
 
-- Insert or update ("upsert")
INSERT INTO settings (user_id, theme) VALUES ($1, $2)
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;

That’s the cleanest way to handle double-clicks, retried HTTP requests, and webhooks that arrive twice. Give each logical action a dedupe key, put a unique constraint on it, and the database guarantees “exactly once” for the insert.

Indexes

Without an index, WHERE tenant_id = $1 reads every row (a sequential scan). An index is a sorted structure that jumps straight to matching rows.

B-tree (the default)

CREATE INDEX email_queue_tenant_idx ON email_queue (tenant_id);
CREATE INDEX notes_ws_updated_idx ON notes (workspace_id, updated_at DESC);

Handles =, <, >, BETWEEN, ORDER BY, and prefix LIKE 'abc%'.

Composite index rule: equality columns first, then the range or sort column. (workspace_id, updated_at) serves “notes in this workspace, newest first”. It also serves “notes in this workspace”. It does not help “all notes updated today across every workspace”. It’s the same rule as compound indexes in IndexedDB, for the same reason: the index is sorted by the first column, then the second.

Partial index

Index only the rows you query:

CREATE INDEX email_queue_pending_idx ON email_queue (created_at) WHERE status = 'pending';

If 99% of rows are sent, this index is tiny and always hot.

Expression index

CREATE INDEX users_email_lower_idx ON users (lower(email));
-- used by: WHERE lower(email) = lower($1)

GIN: for “contains” questions

CREATE INDEX notes_tags_idx ON notes USING gin (tags);           -- text[]: WHERE tags @> ARRAY['work']
CREATE INDEX events_payload_idx ON events USING gin (payload);   -- jsonb: WHERE payload @> '{"type":"x"}'

Fuzzy and full-text search

• pg_trgm (trigram extension) + a GIN index makes ILIKE '%plan%' and similarity search fast. Good for title search with typos.
• Full-text search: tsvector (a document broken into normalised words) and tsquery (a search), with a GIN index. It handles stemming (“planning” matches “plan”) and ranking. Fine for a lot of apps before you need Elasticsearch.

SELECT id, title FROM notes
WHERE search_vector @@ websearch_to_tsquery('english', 'quarterly plan')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'quarterly plan')) DESC;

Indexes aren’t free

Every index is updated on every INSERT, UPDATE and DELETE, and takes disk space. Add indexes for queries you actually run. Remove unused ones (pg_stat_user_indexes shows idx_scan = 0).

Reading query plans

EXPLAIN ANALYZE runs the query and shows what Postgres actually did:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM email_queue WHERE tenant_id = '...' AND status = 'pending';

What to look for:

• Seq Scan on a big table where you expected an index → missing or unusable index.
• Estimated rows vs actual rows wildly different → stale statistics. Run ANALYZE table.
• Rows Removed by Filter in the hundreds of thousands → the index finds too much, and the filter does the real work.
• Nested Loop with a big outer side → often a missing index on the join column.

Turn on pg_stat_statements. It records every query’s total time and call count, and shows which queries cost you the most in practice. Fix the top of that list, not the query you happen to be worried about.

How Postgres handles concurrent users: MVCC

Postgres uses Multi-Version Concurrency Control. An UPDATE doesn’t overwrite a row in place. It writes a new version of the row and marks the old one as dead from that point on. Each transaction sees a consistent snapshot: the versions that were committed when its snapshot was taken.

What that gives you:

• Readers never block writers, and writers never block readers. A long report doesn’t stop people saving.
• Two writers to the same row do wait for each other.

What it costs:

• Dead row versions pile up. VACUUM cleans them, and autovacuum runs it for you in the background. Tables with very heavy updates can grow (“bloat”) if autovacuum can’t keep up.
• A transaction left open for hours stops VACUUM from cleaning anything newer than its snapshot. Never leave transactions open while waiting on a network call or a user.

Transactions and isolation levels

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both happen, or (on error / ROLLBACK) neither

Postgres has three isolation levels in practice:

Level	What you see	Risk
Read Committed (default)	each statement sees data committed before it started	two transactions can read the same value and both write based on it (“lost update”)
Repeatable Read	the whole transaction sees one snapshot	conflicting writes fail with a serialization error; you retry
Serializable	as if transactions ran one at a time	more serialization errors; you must retry

The classic bug at Read Committed:

-- two requests at the same time, both read seats_used = 9 of 10
SELECT seats_used FROM tenants WHERE id = $1;
-- both decide "there's room", both increment → 11 of 10
UPDATE tenants SET seats_used = seats_used + 1 WHERE id = $1;

Fixes:

1. Do it in one statement, with the check inside it:

   UPDATE tenants SET seats_used = seats_used + 1
   WHERE id = $1 AND seats_used < seat_limit
   RETURNING seats_used;  -- no row back = no room

2. Lock the row while you decide: SELECT ... FOR UPDATE inside a transaction. Other transactions wanting that row wait.
3. Use a constraint that makes the bad state impossible.
4. Use Serializable and retry on error code 40001.

Locks you’ll meet

• FOR UPDATE: lock the selected rows until commit.
• FOR UPDATE SKIP LOCKED: take rows nobody else has locked and skip the rest instead of waiting. This one clause is how Postgres works as a job queue: many workers each grab different jobs without stepping on each other. See graphile-worker.
• Advisory locks (pg_advisory_lock(key), pg_advisory_xact_lock(key)): locks on a number you choose, not on a row. Good for “only one process runs this migration or job at a time”.

Migrations without downtime

Changing the schema of a live database is where outages come from.

The lock problem

Most ALTER TABLE statements take an ACCESS EXCLUSIVE lock, which blocks all reads and writes on that table. Even a change that takes 1 ms can cause an outage: it waits in line behind a long-running query, and every query that arrives after it waits behind it.

Protection:

SET lock_timeout = '3s';  -- give up instead of freezing the table
ALTER TABLE ...;

Then retry later.

Which changes are cheap

Change	Cost
Add a nullable column, no default	instant
Add a column with a constant default (Postgres 11+)	instant
Add a column with a volatile default like now() per row, or gen_random_uuid()	rewrites the table
Add NOT NULL to an existing column	scans the whole table under lock
Change a column’s type	usually a full rewrite
CREATE INDEX	blocks writes for the whole build
CREATE INDEX CONCURRENTLY	doesn’t block writes; slower; can’t run inside a transaction
Add a foreign key	scans; use NOT VALID then VALIDATE CONSTRAINT separately

Expand and contract

When old and new app versions run at the same time during a deploy, the schema must work for both:

1. Expand. Add the new column or table. The old code ignores it.
2. Deploy code that writes to both, and reads the new one when it’s there.
3. Backfill old rows in small batches.
4. Deploy code that only uses the new shape.
5. Contract. Drop the old column, in a later release.

Never rename or drop something the currently running code still uses.

Idempotent migration files

Write migrations so running them twice is harmless:

ALTER TABLE documents ADD COLUMN IF NOT EXISTS cover_url text;
CREATE INDEX IF NOT EXISTS ...;

This makes hand-applied migrations, retries after a partial failure, and the same migration on several environments much less stressful.

Connections and pooling

Each Postgres connection is a separate operating-system process on the server, using several MB of memory. Postgres has a hard max_connections limit, often a few hundred on managed plans.

• Every app process has a pool of connections it reuses. Pool size × number of processes × replicas must stay under the limit, with room left for admin tools, migrations and local development.
• Serverless platforms can open hundreds of short connections. They need an external pooler like PgBouncer (Azure flexible server has it built in), usually in transaction mode: a client gets a real connection only for the length of each transaction.
• Transaction pooling breaks features that depend on a connection staying the same across transactions: prepared statements, SET session settings, LISTEN, and session-level advisory locks. Client libraries usually have an option to turn prepared statements off.

JSONB: documents inside a relational database

SELECT payload->>'templateKey' AS template   -- ->> returns text
FROM email_queue
WHERE payload @> '{"kind": "mention"}';      -- containment, can use a GIN index
 
UPDATE settings SET prefs = prefs || '{"theme": "dark"}';  -- merge keys

Good for: flexible payloads, settings, data from external APIs, things you read whole and rarely query inside.

Not good for: fields you filter, join or sort on all the time, or that need foreign keys and constraints. Those should be real columns. A useful rule: if you’re writing payload->>'x' in more than a couple of WHERE clauses, x wants to be a column.

Other features worth knowing

• LISTEN / NOTIFY: lightweight pub/sub between database clients. NOTIFY jobs, 'new' wakes every client that ran LISTEN jobs. Job queues use it to start work instantly instead of polling.
• CTEs (WITH x AS (...)) and window functions (row_number() OVER (PARTITION BY ... ORDER BY ...)): reporting queries that are painful in application code.
• RETURNING: get the inserted or updated rows back without a second query.
• Row-Level Security: per-row access rules enforced by the database. Supabase is built around it.
• Generated columns: search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', title)) STORED.

Backups and recovery

• WAL (write-ahead log): every change is written to a log before the data files. That’s how Postgres survives a crash, and how replication works.
• Point-in-time recovery (PITR): a base backup plus the saved WAL lets you restore to any second within the retention period. Managed services do this for you, usually with 7 to 35 days of retention.
• Logical dumps (pg_dump): portable, human-readable, good for moving data and for long-term copies, but slow to restore for large databases.
• A backup you’ve never restored isn’t a backup. Practise the restore and time it.
• Take a snapshot before any risky migration.

ORMs and query builders

Most TypeScript apps talk to Postgres through a library:

• Drizzle: schema defined in TypeScript, SQL-like query builder, thin, and close to real SQL. You can always drop to raw sql\…“ with safe parameters.
• Prisma: its own schema language, a generated client, more magic.
• Kysely: a typed query builder, no schema of its own.
• Raw drivers: postgres.js, node-postgres (pg).

Whatever you use, learn the SQL it generates. Turn on query logging in development, and check the plans of anything that runs often.

A real-world example

A SaaS app that keeps customer documents in another system and uses Azure Database for PostgreSQL flexible server for metadata only: tenants, users, licences, shares, invites, audit and telemetry, an email queue, and an index of document titles and paths.

• Drizzle on postgres.js in one shared package, used by the web app, a real-time server and a background worker.
• Connection budget. Each process opens at most 10 connections by default, so three production processes use about 30, well under the server’s limit. prepare: false keeps it safe behind transaction pooling.
• Hand-written migrations. Numbered SQL files, mostly using IF NOT EXISTS so they can be applied by hand and re-run safely. They’re additive, so old and new app versions both work during a deploy. A snapshot is taken before each hand-applied migration.
• Features in use:
  • gen_random_uuid() ids and timestamptz everywhere
  • jsonb columns and unique indexes in many tables
  • GIN indexes, with pg_trgm for title search
  • a tsvector search column
  • an advisory lock inside a migration
• Idempotent inserts. The email queue has a unique (tenant_id, dedupe_key) and inserts with ON CONFLICT DO NOTHING, so retried or double-fired notifications become one email.
• It’s also the job queue. graphile-worker keeps its jobs in a graphile_worker schema in the same database.
• Backups. 35-day point-in-time recovery plus weekly vault backups kept for about a year. The staging environment has its own server and must never point at the production database.

Gotchas

• Use timestamptz, not timestamp.
• Foreign keys don’t index the referencing column. Add the index.
• Read Committed allows lost updates. Do check-and-write in one statement, or lock the row.
• Long-open transactions block VACUUM and hold locks. Keep transactions short.
• ALTER TABLE can freeze a table while it waits. Set lock_timeout.
• CREATE INDEX CONCURRENTLY can’t run inside a transaction block.
• Transaction pooling breaks prepared statements, SET and LISTEN.
• Pool size × processes must fit max_connections.
• If a JSONB key shows up in many WHERE clauses, make it a column.
• Practise restoring backups.

Practice

1. Run Postgres locally in Docker. Create the two tables above. Insert the same email twice with ON CONFLICT DO NOTHING and check what RETURNING gives you each time.
2. Fill a table with a million rows (generate_series). Compare EXPLAIN ANALYZE before and after adding an index. Then try a partial index.
3. Open two psql sessions and reproduce the lost-update bug. Fix it with a single conditional UPDATE, then with FOR UPDATE.
4. Build a tiny job queue: a jobs table, and two sessions each running SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1. Watch them take different rows.
5. In one session, start a transaction and select from a table without committing. In another, run ALTER TABLE ... ADD COLUMN. Watch it wait, and watch a third session’s plain SELECT queue behind it. Then retry with lock_timeout.
6. Use LISTEN in one session and NOTIFY in another.

Sources worth reading

• The PostgreSQL documentation, especially Concurrency Control, Indexes and Using EXPLAIN. Unusually well written.
• Use The Index, Luke (use-the-index-luke.com), on how indexes really work
• Brandur Leach’s blog, on Postgres job queues, idempotency keys and migrations
• PostgreSQL at Scale posts from GitLab, Notion and Figma engineering blogs
• Drizzle ORM docs