Hasan OzganHasan Ozgan

The Complete PostgreSQL Developer Guide

A deep, practical guide to PostgreSQL's most-used features, best practices, and patterns.

🌱 Seedling·created: ·category:Databases
tags:postgresqlsqldatabaseindexingperformanceacid

A deep, practical guide to PostgreSQL’s most-used features, best practices, and patterns — written for backend engineers who want to use Postgres correctly and efficiently, not just get queries to run.

Table of Contents

  1. Core Architecture Concepts
  2. Data Types
  3. Schema Design & Constraints
  4. Indexing
  5. Query Writing & Optimization
  6. EXPLAIN and the Query Planner
  7. Transactions & Isolation Levels
  8. Locking & Concurrency
  9. JSON / JSONB
  10. Full-Text Search
  11. Window Functions & Advanced SQL
  12. Common Table Expressions (CTEs)
  13. Partitioning
  14. Vacuuming, Autovacuum & Bloat
  15. Connection Management & Pooling
  16. Replication & High Availability
  17. Backup & Recovery
  18. Security Best Practices
  19. Extensions Worth Knowing
  20. Common Patterns
  21. Migrations
  22. Monitoring & Observability
  23. Anti-Patterns to Avoid
  24. Quick Reference Checklists

1. Core Architecture Concepts

Understanding why Postgres behaves the way it does makes every other section make sense.

  • Process model: Postgres uses one OS process per connection (not threads). This is why connection counts matter a lot — each connection has real memory overhead (a few MB at minimum, more with work_mem usage). This is the root reason connection pooling exists.
  • MVCC (Multi-Version Concurrency Control): Postgres never overwrites a row in place on UPDATE. It writes a new row version and marks the old one as dead. DELETE doesn’t immediately free space either — it just marks the row dead. This is what makes non-blocking reads possible, and it’s also why Postgres needs VACUUM.
  • WAL (Write-Ahead Log): Every change is written to the WAL before it’s applied to data files. WAL is the basis for crash recovery, replication, and point-in-time recovery (PITR).
  • Shared buffers: Postgres caches pages of data in memory (shared_buffers). It also relies heavily on the OS page cache — this dual-cache behavior is unusual compared to some other databases and affects how you size memory settings.
  • Catalogs: Metadata (tables, columns, indexes, types) lives in system catalogs (pg_class, pg_attribute, etc.), queryable like normal tables.

2. Data Types

Choosing correct types up front avoids painful migrations later.

Numbers

  • integer (4 bytes, ~±2.1B) — default choice for IDs/counts.
  • bigint (8 bytes) — use for anything that could exceed 2.1 billion (event tables, high-volume IDs). It’s much cheaper to start with bigint than to migrate later.
  • numeric(p,s) — exact precision, use for money and anything requiring exact decimal arithmetic. Never use float/double precision for money.
  • real / double precision — approximate, fast, fine for scientific/measurement data where exactness doesn’t matter.
  • smallint — rarely worth the savings unless you have billions of rows.

Text

  • text — unlimited length, no performance penalty vs varchar(n). Prefer text over varchar(n) in Postgres — unlike some databases, there’s no storage or performance advantage to varchar(n), and you avoid painful “value too long” migrations later. Use varchar(n) only when you have a genuine business rule requiring a hard length cap enforced at the DB layer.
  • char(n) — almost never what you want; it pads with spaces. Avoid.

Date/Time

  • timestamptz (timestamp with time zone) — almost always what you want. It stores UTC internally and converts on display based on the session’s timezone setting. Using plain timestamp (without time zone) is a classic mistake that causes silent bugs when your app or servers span time zones.
  • date — for pure calendar dates (birthdays, holidays) with no time component.
  • interval — for durations (“3 days”, “2 hours”).

Boolean

  • boolean — native true/false/null; don’t emulate with integer or char(1).

UUID

  • uuid — native 16-byte type, use gen_random_uuid() (built into Postgres 13+ via pgcrypto/core) instead of storing UUIDs as text. Note: random UUIDs (v4) as primary keys hurt index locality on high-insert tables — see Patterns for UUIDv7 alternatives.

Arrays

  • Native support: integer[], text[], etc. Useful for small, denormalized lists (e.g., tags) but don’t overuse — a proper join table is usually more flexible for anything that needs its own attributes or referential integrity.

JSON / JSONB

  • Covered in depth in Section 9. Short version: always prefer jsonb over json unless you specifically need to preserve exact input formatting/key order.

Enums

  • CREATE TYPE status AS ENUM ('pending','active','done'); — fast and self-documenting, but adding new values requires ALTER TYPE ... ADD VALUE (which historically couldn’t run inside a transaction with other DDL, though this is more flexible in modern versions). For values that change often, a lookup table with a foreign key is more flexible.

Network types

  • inet, cidr, macaddr — use these instead of storing IPs as text; they’re validated and support operators like containment (<<).

Ranges

  • int4range, tstzrange, daterange, etc. — excellent for “from-to” data (bookings, validity periods) and pair well with exclusion constraints (see below) to prevent overlaps.

3. Schema Design & Constraints

Primary keys

  • Prefer bigint generated always as identity (SQL-standard identity columns) over the legacy serial. Identity columns behave better with permissions and are the modern, standards-compliant choice:
CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

Foreign keys

  • Always index foreign key columns — Postgres does not automatically create an index on the referencing column (unlike the primary key side), and missing indexes here cause slow cascading deletes and slow joins.
  • Choose ON DELETE behavior deliberately: CASCADE, RESTRICT, SET NULL, or SET DEFAULT. Silent CASCADE on sensitive data is a common source of accidental data loss.

Constraints as documentation and safety net

  • NOT NULL — apply liberally; nullable-by-default is not a good default.
  • CHECK constraints — enforce business rules at the DB layer (CHECK (price >= 0)), catching bugs that would otherwise slip through app-layer validation.
  • UNIQUE constraints — prefer over app-level uniqueness checks, which are inherently race-condition prone.
  • EXCLUDE constraints — powerful and underused; e.g., prevent overlapping date ranges for the same resource:
CREATE TABLE bookings (
    room_id int,
    during tstzrange,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

This guarantees at the database level that no two bookings for the same room can overlap — no application code race condition possible.

Normalization vs. denormalization

  • Normalize by default (3NF is a fine baseline). Denormalize deliberately and only when you’ve measured a real read-performance problem, and document why the denormalized copy exists and how it stays in sync (trigger, batch job, application logic).

Naming conventions

  • Consistent, lower_snake_case names for tables/columns (Postgres folds unquoted identifiers to lowercase — fighting this by quoting "CamelCase" names everywhere is a common self-inflicted pain point).
  • Plural or singular table names — pick one convention and enforce it project-wide.

4. Indexing

Indexes are the single highest-leverage performance tool in Postgres — and the most commonly misused.

Index types

  • B-tree (default) — good for equality and range queries (=, <, >, BETWEEN, sorting). Use for the vast majority of cases.
  • GIN (Generalized Inverted Index) — for composite/multi-valued columns: jsonb, arrays, full-text search (tsvector).
  • GiST (Generalized Search Tree) — for geometric data, range types, exclusion constraints, nearest-neighbor searches.
  • BRIN (Block Range Index) — tiny, fast-to-build index good for very large tables where the column correlates with physical row order (e.g., an append-only created_at column). Dramatically smaller than B-tree but only useful under that correlation assumption.
  • Hash — rarely needed now; B-tree covers equality just as well and supports more operators.

Practical rules

  • Index foreign keys. Always. This is the single most common missing-index bug.
  • Composite index column order matters. An index on (a, b) supports queries filtering on a alone or a AND b, but not efficiently on b alone. Put the most selective / most commonly-filtered-alone column first, matching your actual query patterns.
  • Covering indexes — use INCLUDE to add columns to an index purely for “index-only scans” without making them part of the sort/search key:
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (order_date, total);
  • Partial indexes — index only the rows you actually query, dramatically shrinking index size:
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

Excellent for soft-delete patterns (WHERE deleted_at IS NULL) or status-filtered queries.

  • Expression indexes — index the result of a function/expression when you always query through it:
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- supports: WHERE lower(email) = 'x@y.com'
  • Don’t over-index. Every index slows down INSERT/UPDATE/DELETE and consumes storage/cache. Periodically audit for unused indexes via pg_stat_user_indexes.
  • Build indexes without blocking writes on production tables using CREATE INDEX CONCURRENTLY — slower to build, but doesn’t take a table-locking ACCESS EXCLUSIVE lock. Always use this in production migrations against live tables.
  • Watch for duplicate/redundant indexes — (a) is redundant if (a, b) already exists for most query patterns.

5. Query Writing & Optimization

  • Select only the columns you need. SELECT * prevents index-only scans and wastes bandwidth.
  • Avoid functions on indexed columns in WHERE unless you have a matching expression index — WHERE date_trunc('day', created_at) = ... can’t use a plain index on created_at.
  • Use EXISTS instead of IN with subqueries for large subquery result sets — the planner usually optimizes both similarly today, but EXISTS short-circuits and communicates intent more clearly, and historically outperformed IN on large sets.
  • Beware implicit type casts. Comparing a text column to an integer literal, or an int column to a bigint, can silently prevent index usage or cause unexpected results.
  • Batch writes. Bulk INSERT ... VALUES (...), (...), (...) is far faster than many single-row inserts due to reduced round-trips and WAL overhead. For very large loads, use COPY.
  • Use LIMIT with ORDER BY together — an ORDER BY without LIMIT on a huge result set forces a full sort.
  • Pagination: avoid OFFSET for deep pagination (it still scans and discards N rows). Use keyset pagination instead (see Patterns).
  • Avoid SELECT DISTINCT as a bug patch — if you need DISTINCT to remove duplicate rows from a join, it usually means the join is wrong (e.g., a one-to-many join fan-out). Fix the query logic instead.
  • Use RETURNING to get data back from INSERT/UPDATE/DELETE in a single round-trip instead of a separate SELECT.

6. EXPLAIN and the Query Planner

Query optimization without EXPLAIN is guesswork.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
  • EXPLAIN alone shows the planned execution (no actual run). EXPLAIN ANALYZE actually runs the query and shows real timings — be cautious running this with INSERT/UPDATE/DELETE on production (wrap in a transaction and ROLLBACK if you need to test a write query’s plan).
  • BUFFERS shows actual page hits/reads — essential for diagnosing whether a slow query is CPU-bound or I/O-bound.
  • Key things to look for in the output:
    • Seq Scan on a large table where you expected an Index Scan — usually means a missing index, a non-sargable predicate, or the planner deciding a seq scan is cheaper (which can be correct for small tables or when selecting a large fraction of rows).
    • Estimated rows vs. actual rows — a large discrepancy signals stale statistics; run ANALYZE on the table, or check default_statistics_target.
    • Nested Loop joins against large unindexed tables — can be extremely slow; usually fixed by adding an index on the join column.
    • Sort operations spilling to disk (visible in the plan as “external merge”) — indicates work_mem is too low for that operation.
  • pg_stat_statements extension — track the actual slowest/most-frequent queries in production over time rather than guessing. This should be enabled by default in almost every serious deployment.
  • Use ANALYZE (and ensure autovacuum’s analyze runs) to keep planner statistics fresh — critical after large bulk loads or schema changes.

7. Transactions & Isolation Levels

  • Postgres defaults to READ COMMITTED isolation — each statement in a transaction sees a snapshot as of when that statement started, not when the transaction started.
  • REPEATABLE READ — the whole transaction sees one consistent snapshot from its start; will raise a serialization error if a concurrent transaction modifies data your transaction depends on and both try to commit conflicting changes.
  • SERIALIZABLE — the strictest level; behaves as if transactions ran one at a time. Prevents subtle anomalies (like write skew) that REPEATABLE READ alone doesn’t. Costs more overhead and requires your application to retry on serialization failures (SQLSTATE 40001).
  • Keep transactions short. Long-running transactions hold back autovacuum’s ability to clean up dead rows (since Postgres must retain old row versions that might still be visible to that old transaction), which is a common root cause of table bloat.
  • Never leave transactions open while waiting on external I/O (an API call, user input) — this is one of the most common causes of production lock contention and bloat.
  • Use SET LOCAL inside a transaction to scope session settings (like statement_timeout) to just that transaction.

8. Locking & Concurrency

  • Row-level locks: SELECT ... FOR UPDATE locks selected rows against concurrent modification — essential for patterns like “read a balance, then update it” to prevent lost updates.
  • FOR UPDATE SKIP LOCKED — a killer pattern for building job queues: multiple workers can each grab different rows without blocking each other:
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
  • FOR UPDATE NOWAIT — fail immediately instead of waiting if a row is locked, useful when you want to fail fast rather than queue.
  • Table-level locks: DDL operations like ALTER TABLE ... ADD COLUMN (without a default, in modern Postgres) are fast metadata-only changes, but adding a column with a volatile default or changing a column type can rewrite the whole table and take an ACCESS EXCLUSIVE lock, blocking all reads/writes for the duration. Always check whether an operation is “fast path” before running it on a large production table.
  • Deadlocks: Postgres detects and automatically aborts one side of a deadlock. Avoid them by always acquiring locks (including row locks via UPDATE/SELECT FOR UPDATE) in a consistent order across your codebase.
  • Advisory locks (pg_advisory_lock) — application-level locks not tied to any row/table, useful for things like “ensure only one instance of this cron job runs at a time.”

9. JSON / JSONB

  • Use jsonb, not json, in virtually all cases. jsonb stores a parsed binary representation (faster to query, supports indexing) while json stores the exact input text (slightly faster to insert, preserves key order/whitespace/duplicate keys — rarely what you need).
  • Index jsonb with GIN for containment/existence queries:
CREATE INDEX idx_products_attrs ON products USING gin (attributes);
-- supports: WHERE attributes @> '{"color": "red"}'
  • Expression indexes on specific keys if you consistently query one field:
CREATE INDEX idx_products_sku ON products ((attributes->>'sku'));
  • Operators to know: -> (get JSON value as jsonb), ->> (get value as text), #> / #>> (get nested path), @> (contains), ? (key exists), jsonb_set(), jsonb_build_object(), || (concatenate/merge top-level keys).
  • Don’t use jsonb as a substitute for a real schema. It’s excellent for genuinely variable/sparse attributes (e.g., product attributes that differ by category) but a poor substitute for columns you regularly filter, join, or aggregate on — those belong in real typed columns.
  • Validate structure at the app layer or with CHECK constraints (e.g., CHECK (jsonb_typeof(data) = 'object')) — Postgres won’t otherwise enforce any particular shape inside a jsonb column.

10. Full-Text Search

Postgres has a genuinely capable built-in full-text search engine — often good enough to avoid standing up Elasticsearch for small-to-mid scale.

  • tsvector — a preprocessed, normalized document representation (stemmed, stripped of stop words). tsquery — a parsed search query.
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX idx_articles_search ON articles USING gin (search_vector);

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & performance');
  • Using a generated column (as above) keeps the tsvector automatically in sync with the source columns — no trigger needed, and it’s indexable.
  • ts_rank / ts_rank_cd — rank results by relevance for ordering.
  • websearch_to_tsquery — parses Google-style search syntax (quotes, -exclude, OR) from raw user input; usually the right entry point for user-facing search boxes rather than hand-building tsquery.
  • For fuzzy/typo-tolerant search, pair with the pg_trgm extension (trigram similarity) and a GIN or GiST index on similarity()/%.

11. Window Functions & Advanced SQL

Window functions compute across a set of rows related to the current row without collapsing them into groups (unlike GROUP BY).

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - LAG(salary) OVER (ORDER BY hire_date) AS diff_from_prev_hire
FROM employees;
  • ROW_NUMBER() — unique sequential number per partition; useful for “top N per group” queries and for deduplication.
  • RANK() / DENSE_RANK() — like ROW_NUMBER() but handles ties (RANK leaves gaps after ties, DENSE_RANK doesn’t).
  • LAG() / LEAD() — access a prior/following row’s value without a self-join — excellent for period-over-period comparisons.
  • SUM()/AVG()/COUNT() OVER (...) — running totals and moving averages via frame clauses (ROWS BETWEEN ... AND ...).
  • “Top N per group” pattern:
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) rn
  FROM employees
) t WHERE rn <= 3;

Other advanced constructs

  • LATERAL joins — allow a subquery on the right side of a join to reference columns from the left side row-by-row; essential for “top N related rows per row” queries that window functions alone can’t express efficiently.
  • GROUPING SETS / ROLLUP / CUBE — compute multiple levels of aggregation (e.g., subtotals and grand totals) in a single query pass instead of UNION-ing several GROUP BY queries.
  • FILTER clause — conditional aggregation cleanly: COUNT(*) FILTER (WHERE status = 'active') instead of SUM(CASE WHEN ... THEN 1 ELSE 0 END).

12. Common Table Expressions (CTEs)

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total
    FROM orders
    GROUP BY region
)
SELECT * FROM regional_sales WHERE total > 100000;
  • CTEs improve readability by breaking complex queries into named, sequential steps.
  • Modern Postgres (12+) inlines CTEs by default (unlike older versions, which always materialized them as an optimization fence). This means CTEs no longer carry an automatic performance penalty — but you can still force materialization explicitly with MATERIALIZED when you specifically want to prevent the planner from pushing predicates into the CTE (e.g., for a CTE with side effects, or to force a specific plan).
  • Recursive CTEs (WITH RECURSIVE) — the standard tool for hierarchical/graph data: org charts, category trees, bill-of-materials explosions:
WITH RECURSIVE subordinates AS (
    SELECT id, manager_id, name FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
  • Writable CTEs — you can chain INSERT/UPDATE/DELETE ... RETURNING inside a CTE and feed the result into the next step, useful for “move data from A to B” operations in one atomic statement:
WITH moved AS (
    DELETE FROM staging_orders WHERE processed = true RETURNING *
)
INSERT INTO orders SELECT * FROM moved;

13. Partitioning

For very large tables (tens of millions+ rows), native declarative partitioning splits one logical table into physical child tables.

  • Range partitioning — most common; typically by date (created_at), enabling easy “drop old data” (drop a partition instead of a slow DELETE) and query pruning (queries filtering on the partition key skip irrelevant partitions entirely).
CREATE TABLE events (
    id bigint,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
  • List partitioning — by discrete values (e.g., region, tenant_id).
  • Hash partitioning — for even distribution when there’s no natural range/list key.
  • When to partition: table is very large, has a clear partition key that most queries filter by, and/or you need efficient bulk deletion of old data (e.g., data retention policies). Don’t partition prematurely — it adds real complexity (constraints, indexes, and foreign keys all need extra care per-partition).
  • Automate partition creation (e.g., via pg_partman extension or a scheduled job) — forgetting to create the next period’s partition is a classic production incident.
  • Foreign keys referencing a partitioned table have historically had restrictions — verify behavior on your specific Postgres version.

14. Vacuuming, Autovacuum & Bloat

This is one of the most misunderstood parts of Postgres operations.

  • Because of MVCC, UPDATE/DELETE leave “dead tuples” behind. VACUUM reclaims that space for reuse (but usually doesn’t shrink the file on disk — see VACUUM FULL below).
  • Autovacuum runs this automatically based on thresholds (autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor, a percentage of table size). Default settings are conservative and often too infrequent for high-churn tables.
  • Symptoms of vacuum falling behind: growing table/index size without growing row count (bloat), slower sequential scans, EXPLAIN estimates diverging from reality, and in extreme cases, transaction ID wraparound warnings.
  • Tuning per-table for high-write tables:
ALTER TABLE hot_table SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005);
  • VACUUM FULL — actually reclaims disk space by rewriting the entire table, but takes an ACCESS EXCLUSIVE lock (blocks everything). Only run during maintenance windows, or use the pg_repack extension for an online alternative.
  • Transaction ID wraparound — Postgres transaction IDs are 32-bit and cyclic; if autovacuum can’t “freeze” old rows in time, you eventually risk data becoming inaccessible. Monitor age(datfrozenxid) in production. This is rare to hit but catastrophic if ignored — it’s the reason autovacuum can’t simply be disabled.
  • ANALYZE (statistics refresh) is a separate, cheaper operation from VACUUM (space reclaim) — autovacuum does both, but you can also run ANALYZE alone after bulk data changes to refresh planner statistics immediately.

15. Connection Management & Pooling

  • Because each Postgres connection is a full OS process, connection count is a real, finite resource — typically hundreds, not tens of thousands, even on beefy hardware.
  • Use a connection pooler — PgBouncer is the standard choice. Run it in transaction pooling mode for most web application workloads (a connection is only “checked out” for the duration of a transaction, not the whole client session).
  • Be aware transaction mode pooling breaks session-level features: prepared statements across transactions, session-level SET (use SET LOCAL instead), advisory locks held across transactions, and LISTEN/NOTIFY. Know your pooling mode’s limitations before relying on these features.
  • Application-side pools (e.g., in your ORM/driver) should be sized conservatively — more connections than your CPU core count is often counter-productive because of context-switching overhead; total connections across all app instances should be well within max_connections.
  • Set a reasonable statement_timeout and idle_in_transaction_session_timeout to prevent runaway queries and “forgotten” open transactions from holding locks indefinitely.

16. Replication & High Availability

  • Streaming replication (built-in) — a primary ships WAL to one or more standbys in near real-time. Standbys can serve read-only queries (hot standby), useful for scaling read traffic.
  • Synchronous vs. asynchronous replication — synchronous guarantees a standby has confirmed the write before the primary reports success (stronger durability, added write latency); asynchronous is the default (lower latency, small risk of losing the last few transactions on failover).
  • Logical replication — replicates at the row/statement level rather than the raw WAL/byte level; enables replicating specific tables, replicating between different major Postgres versions, and feeding data to external systems (e.g., search indexes, data warehouses, CDC pipelines).
  • Failover — Postgres core does not include automatic failover; that’s handled by tooling like Patroni, repmgr, or managed cloud services. Plan for this explicitly — “just enable replication” is not the same as “high availability.”
  • Read replica lag — always accessible via pg_stat_replication on the primary or by comparing WAL positions; design your application to tolerate eventual consistency on replicas (e.g., don’t read your own write from a replica immediately after writing to the primary without accounting for lag).

17. Backup & Recovery

  • pg_dump — logical backup of one database (schema + data), portable across versions/platforms, but doesn’t scale well to very large databases (single-threaded restore is slow; pg_dump --jobs=N and pg_restore --jobs=N for parallel dump/restore help).
  • pg_basebackup — physical backup of an entire cluster (all databases, faster for large datasets), used as the basis for WAL-based Point-in-Time Recovery (PITR).
  • PITR — combining a base backup with the continuous stream of WAL files lets you restore to any specific moment (crucial for recovering from accidental data deletion or a bad deploy). Tools like pgBackRest and WAL-G manage this well in production.
  • Test your restores. A backup you haven’t test-restored is a hypothesis, not a backup. This is the single most commonly skipped and most costly-to-skip operational practice.
  • Always back up before major version upgrades or risky schema migrations.

18. Security Best Practices

  • Principle of least privilege: application roles should not be superusers. Grant only the specific privileges (SELECT, INSERT, etc.) needed on the specific schemas/tables needed.
  • Use roles for grouping permissions, then grant roles to users, rather than granting individual privileges to every user directly.
  • Row-Level Security (RLS) — enforce per-row access rules at the database level, independent of application logic — valuable for multi-tenant systems:
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

This is a genuine defense-in-depth layer: even a SQL-injection bug or an application bug can’t leak cross-tenant data if RLS is correctly configured, because the database itself enforces the boundary.

  • Always use parameterized queries — never string-concatenate user input into SQL. This applies regardless of ORM use; raw queries or dynamic SQL (EXECUTE format(...)) need extra care with %I/%L quoting in PL/pgSQL.
  • Encrypt connections with sslmode=require (or stricter: verify-full) in production, especially over any network you don’t fully control.
  • Encrypt sensitive columns at the application layer or with pgcrypto when you need protection even from a database-level compromise — Postgres doesn’t encrypt individual column values by default.
  • Audit logging — use pgaudit extension or Postgres’s built-in logging (log_statement) for compliance-sensitive systems.
  • Keep Postgres patched — minor version updates often include security fixes and are designed to be safe/low-risk to apply.

19. Extensions Worth Knowing

Enable with CREATE EXTENSION extension_name;.

ExtensionWhat it does
pg_stat_statementsTracks query performance stats across all queries — should be on by default in production
pgcryptoCryptographic functions, gen_random_uuid() on older versions
pg_trgmTrigram-based fuzzy text matching/similarity search
postgisFull geographic/geospatial data types and functions — industry standard for spatial data
pg_partmanAutomates partition creation/maintenance
pg_repackOnline table/index bloat removal without long exclusive locks
pgauditDetailed session/object audit logging
hstoreSimple key-value store type (largely superseded by jsonb for new projects)
uuid-osspLegacy UUID generation functions (mostly unnecessary now that gen_random_uuid() is built in)
citextCase-insensitive text type — simpler than always wrapping comparisons in lower()
timescaledbTime-series optimizations (not bundled by default, but widely used for IoT/metrics workloads)

20. Common Patterns

Upsert (INSERT … ON CONFLICT)

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();

Atomic, race-condition-free — far better than “check if exists, then insert or update” from application code.

Keyset (cursor-based) pagination

Instead of:

SELECT * FROM posts ORDER BY created_at DESC OFFSET 10000 LIMIT 20; -- slow at depth

Use:

SELECT * FROM posts
WHERE created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

Constant performance regardless of page depth, as long as created_at (or a unique tie-breaker combined with it) is indexed.

Soft deletes

ALTER TABLE orders ADD COLUMN deleted_at timestamptz;
CREATE INDEX idx_orders_active ON orders (id) WHERE deleted_at IS NULL;

Pair every soft-delete column with a partial index for the “active rows” query pattern, or queries will slow down as deleted rows accumulate.

Optimistic locking (avoiding lost updates without DB-level locks)

UPDATE accounts SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = :expected_version;
-- check rows affected == 1; if 0, someone else updated it first — retry or reload

Job queue with SKIP LOCKED

(see Section 8) — the standard Postgres-native pattern for a simple job/task queue without a separate message broker.

UUIDv7 for primary keys

Random (v4) UUIDs as primary keys cause index fragmentation on insert-heavy tables because new values scatter randomly across the B-tree instead of appending at the end. UUIDv7 (time-ordered, supported via extensions or app-level generation on Postgres versions before native support) gives you UUID’s uniqueness/non-guessability benefits with much better insert locality — closer to the behavior of a sequential integer.

Audit trail with triggers

CREATE TABLE orders_audit (LIKE orders INCLUDING ALL, operation text, changed_at timestamptz DEFAULT now());

CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
    INSERT INTO orders_audit SELECT OLD.*, TG_OP, now();
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit_trigger
AFTER UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();

LISTEN/NOTIFY for lightweight pub-sub

LISTEN new_order;
NOTIFY new_order, '{"order_id": 123}';

Useful for triggering application-layer reactions to DB events without polling — but not a durable message queue (notifications are lost if no one is listening; use a real queue/CDC system for guaranteed delivery).

21. Migrations

  • Always use a migration tool (Flyway, Liquibase, Alembic, Sqitch, or your framework’s built-in migration system) — never hand-apply schema changes to production.
  • Understand which DDL operations are “fast” (metadata-only) vs. “slow” (table rewrite) on your Postgres version:
    • Fast: ADD COLUMN with no default (or a constant default on modern Postgres 11+), DROP COLUMN, adding a nullable column.
    • Slow / rewrites the table: adding a column with a volatile default, changing a column’s type (in most cases), adding a NOT NULL constraint without a pre-validated check.
  • Add constraints in two steps on large tables to avoid long locks: add as NOT VALID first (fast, doesn’t scan/lock for validation), then VALIDATE CONSTRAINT separately (scans but takes a lighter lock, doesn’t block reads/writes the whole time):
ALTER TABLE orders ADD CONSTRAINT chk_positive CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_positive;
  • Always use CREATE INDEX CONCURRENTLY on production tables (see Section 4).
  • Backward-compatible migrations for zero-downtime deploys: when renaming/removing a column, deploy in stages — (1) add new column, dual-write from the app, backfill, (2) switch reads to new column, (3) stop writing old column, (4) drop old column in a later deploy. Never do a rename that breaks the currently-running application version mid-deploy.
  • Test migrations against a production-sized dataset before running in production — a migration that’s instant on a dev database with 100 rows can lock a production table with 100 million rows for minutes.

22. Monitoring & Observability

Key things to track in production:

  • pg_stat_statements — slowest and most frequent queries.
  • pg_stat_activity — currently running queries, blocked queries, long-running transactions.
  • pg_stat_user_tables / pg_stat_user_indexes — sequential scan vs. index scan counts (spot tables that should have an index but don’t), unused indexes (idx_scan = 0 on a long-lived index is a removal candidate).
  • pg_locks joined with pg_stat_activity** — diagnosing lock contention/blocking chains in real time.
  • Replication lag — pg_stat_replication on primary.
  • Cache hit ratio — a low shared_buffers hit ratio (via pg_statio_user_tables) suggests memory pressure or under-sized shared_buffers.
  • Table/index bloat estimates — via community queries against pg_stat_user_tables/pgstattuple extension.
  • Standard tools: pgAdmin, pganalyze, Datadog/Grafana with postgres_exporter, and most managed cloud providers’ built-in dashboards (RDS Performance Insights, Cloud SQL Insights, etc.).

23. Anti-Patterns to Avoid

  • ❌ Using SERIAL/int primary keys on tables expected to grow large — migrate to bigint from day one.
  • ❌ timestamp without time zone for anything user-facing or cross-region.
  • ❌ Missing indexes on foreign key columns.
  • ❌ SELECT * in application code and views used in hot paths.
  • ❌ Deep OFFSET-based pagination on large tables.
  • ❌ Storing money as float/double precision.
  • ❌ Using jsonb to avoid schema design entirely (“schemaless” tables that should have real columns).
  • ❌ Long-running transactions holding row locks or blocking vacuum.
  • ❌ Running VACUUM FULL or heavy DDL on a large table during peak traffic without understanding the lock implications.
  • ❌ Application-level uniqueness checks (“check if exists, then insert”) instead of DB-level UNIQUE constraints + ON CONFLICT.
  • ❌ Ignoring EXPLAIN ANALYZE output and guessing at performance fixes.
  • ❌ Disabling or ignoring autovacuum instead of tuning it.
  • ❌ Never testing backup restores.
  • ❌ Granting superuser/broad privileges to application database roles.
  • ❌ String-concatenating user input into SQL queries.

24. Quick Reference Checklists

New table checklist

  • bigint generated always as identity primary key (not serial, not plain int unless truly small/bounded)
  • timestamptz for all timestamp columns
  • NOT NULL on columns that should never be null
  • CHECK constraints for business rules
  • Foreign keys indexed
  • Naming convention followed (snake_case)
  • Consider partial index for common filtered queries (e.g., soft-delete pattern)

Before deploying a migration to production

  • Tested against production-sized data
  • Uses CREATE INDEX CONCURRENTLY for new indexes
  • Uses NOT VALID + VALIDATE CONSTRAINT pattern for new constraints on large tables
  • Confirmed whether the DDL takes an ACCESS EXCLUSIVE lock, and scheduled accordingly
  • Backward compatible with the currently-deployed application version

Query performance checklist

  • Ran EXPLAIN (ANALYZE, BUFFERS) on the actual query
  • Checked for Seq Scan where an Index Scan was expected
  • Verified planner row estimates roughly match reality (statistics are fresh)
  • Confirmed relevant indexes exist and column order matches filter patterns
  • Checked pg_stat_statements for this query’s real-world frequency/cost

Production readiness checklist

  • Connection pooler (PgBouncer) in front of the database
  • pg_stat_statements enabled
  • Automated backups with tested restore procedure
  • Replication / HA strategy defined (not just “replication is on”)
  • Monitoring dashboards for locks, replication lag, cache hit ratio, bloat
  • statement_timeout and idle_in_transaction_session_timeout configured
  • Autovacuum tuned for high-churn tables specifically

This guide covers the vast majority of what a working backend/application developer needs day-to-day. For deep internals (storage format details, WAL internals, planner cost model internals), consult the official PostgreSQL documentation, which is genuinely excellent and worth reading directly for authoritative detail.

This note is part of the Digital Garden — a collection of connected, evolving thoughts.