PostgreSQL Best Practices

Production-oriented PostgreSQL guidance covering specialized index types (GIN, GiST, BRIN, hash), JSONB modeling, table partitioning, connection pooling, autovacuum tuning, and logical replication.

Introduction / Overview

PostgreSQL is the leading open-source relational database in 2026, with a feature set that rivals or exceeds commercial engines: rich indexing strategies, native JSON document support, declarative partitioning, logical replication, and - as of PostgreSQL 18 - a rewritten asynchronous I/O subsystem, planner "skip scan" support for multicolumn B-tree indexes, uuidv7() for timestamp-ordered identifiers, and OAuth-based authentication. This document builds on General SQL Patterns with Postgres-specific mechanisms for extracting maximum performance and reliability.

The recurring theme: Postgres gives you far more precise tools (index types, partitioning strategies, replication modes) than "just add an index and hope." Choosing the right tool for the access pattern, rather than defaulting to a plain B-tree everywhere, is what separates a Postgres deployment that scales gracefully from one that requires constant firefighting.

When to use this guidance: You are running PostgreSQL in production (self-hosted or managed) and need to choose index types for non-trivial data (JSON, geometric, full-text, time-series), partition large tables, size a connection pool correctly, or diagnose bloat and replication lag.

Core Concepts

• Index access methods: Postgres exposes B-tree, GIN, GiST, SP-GiST, BRIN, and hash as pluggable index types, each optimized for a different data shape and query pattern - there is no single "best" index type independent of the workload.
• JSONB: A binary, decomposed storage format for JSON that supports indexing and efficient containment queries, distinct from the text-based json type which merely validates and stores JSON verbatim.
• Declarative partitioning: Splitting one logical table into physically separate child tables by range, list, or hash, transparent to most queries but requiring partition-aware maintenance.
• MVCC and vacuum: Postgres never overwrites rows in place; updates and deletes leave "dead tuples" that VACUUM must reclaim. Autovacuum is the background process responsible for this and for keeping planner statistics current.
• Logical replication: Row-level, publish/subscribe replication that can replicate a subset of tables, tolerate different major versions, and enable zero-downtime major upgrades or selective data distribution - distinct from physical (WAL-shipping) streaming replication.

Best Practices

1. Match the Index Type to the Data Shape and Query Pattern

Rationale: A B-tree cannot efficiently answer "does this JSONB document contain this key" or "which points fall within this bounding box." Using the wrong index type silently degrades to a sequential scan or bloats an index far beyond necessity.

-- GIN index for JSONB containment queries
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);

-- BRIN index: tiny footprint on a huge append-only table ordered by time
CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at);

-- GiST for a booking system's exclusion constraint (no overlapping reservations)
ALTER TABLE reservations ADD CONSTRAINT no_overlap
EXCLUDE USING GIST (room_id WITH =, during WITH &&);

Why this works well in production: BRIN indexes on a billion-row append-only table can be orders of magnitude smaller than an equivalent B-tree because they store only min/max summaries per block range, at the cost of being effective only when physical row order correlates with the indexed column.

2. Model JSONB Deliberately - It Complements the Relational Model, It Does Not Replace It

Rationale: JSONB is excellent for semi-structured, sparse, or genuinely variable attributes (event payloads, third-party API responses, feature flags). It is a poor substitute for columns you will filter, join, or constrain frequently - those belong as real columns with real indexes and constraints.

CREATE TABLE events (
    event_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type  TEXT NOT NULL,       -- frequently filtered: a real column
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload     JSONB NOT NULL       -- variable per event_type: JSONB is appropriate here
);

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);

-- Containment query using the GIN index
SELECT * FROM events
WHERE payload @> '{"user_tier": "enterprise"}'
AND event_type = 'checkout_completed';

-- Extracting a nested value with the ->> operator (returns text)
SELECT payload -> 'cart' ->> 'total' AS cart_total FROM events;

Guideline: if a JSONB field is queried, filtered, or joined on in more than one or two places, promote it to a real column (with GENERATED ALWAYS AS (payload ->> 'field') STORED as a migration bridge if needed) rather than continuing to query into the document.

3. Partition Large Tables by Access Pattern, Not by Table Size Alone

Rationale: Partitioning improves query performance through "partition pruning" (the planner skips partitions that cannot match the query) and simplifies maintenance (dropping an old partition is near-instant versus a slow DELETE). It also adds operational complexity - indexes, constraints, and foreign keys must be managed per partition or via inheritance. Partition only when a specific pattern (typically time-based retention) justifies it.

CREATE TABLE events (
    event_id    BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type  TEXT NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload     JSONB NOT NULL,
    PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE events_2026_07 PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Dropping a partition reclaims space instantly, unlike a bulk DELETE
DROP TABLE events_2026_01;

Production tip: automate partition creation and retirement (via pg_partman or a scheduled job) - manually created partitions are a common source of "insert fails because no partition matches" incidents.

4. Size and Choose Your Connection Pooler Deliberately

Rationale: Each Postgres connection is a full OS process with non-trivial memory overhead; a naive "one connection per web request" architecture collapses well before the database itself is CPU- or I/O-bound. As of PostgreSQL 18, there is still no built-in server-side connection pool comparable to a dedicated pooler, so an external pooler remains standard practice for any application with more than a handful of concurrent clients.

; pgbouncer.ini - transaction pooling for a typical web application
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25

Why transaction-mode pooling matters: it multiplexes many client connections onto a small number of real server connections, releasing the server connection back to the pool as soon as a transaction commits - but it is incompatible with session-level state (prepared statements across transactions, advisory locks held across commits, SET at session scope). Confirm your ORM/driver is configured for transaction-pooling compatibility (e.g., disable server-side prepared statement caching, or use PREPARE-free query modes) before switching modes.

5. Tune Autovacuum Rather Than Disabling It

Rationale: Autovacuum reclaims dead tuples and refreshes planner statistics. Disabling it (a tempting "fix" when it appears to cause I/O spikes) guarantees table and index bloat and stale statistics, both of which degrade performance far more than a well-tuned autovacuum ever would.

-- Per-table tuning for a high-churn table (default thresholds are too conservative
-- for tables with heavy UPDATE/DELETE traffic)
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- vacuum after 2% of rows change, not the 20% default
    autovacuum_vacuum_cost_limit   = 2000,   -- allow more I/O budget per vacuum cycle
    autovacuum_analyze_scale_factor = 0.01
);

-- Monitor bloat and vacuum activity
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Watch for: long-running transactions (including idle-in-transaction sessions) holding back the vacuum horizon - autovacuum cannot reclaim tuples that are still visible to any open transaction's snapshot, regardless of how aggressively it is tuned.

6. Use Logical Replication for Selective Sync, Zero-Downtime Upgrades, and Event Streaming

Rationale: Physical (streaming) replication copies the entire cluster byte-for-byte and requires matching major versions. Logical replication operates at the row/statement level, enabling cross-version replication, replicating a subset of tables, and feeding downstream systems (search indexes, data warehouses, change-data-capture pipelines) without impacting the primary's read/write path beyond WAL decoding overhead.

-- On the publisher
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- On the subscriber (can be a different Postgres major version)
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=primary.internal dbname=appdb user=replicator'
PUBLICATION orders_pub;

Common use case: replicating a narrow slice of the transactional schema into an analytics or search-indexing service in a microservices architecture, avoiding both tight coupling to the primary schema and batch ETL latency.

Common Pitfalls & Anti-Patterns

Pitfall: Adding a GIN Index to Every JSONB Column Reflexively

Problem: GIN indexes are large and, without fastupdate tuning, can slow down writes noticeably on high-throughput insert workloads. Not every JSONB column is queried by containment - some are only ever fetched by primary key and returned wholesale.

Recommended approach: index JSONB only when containment/path queries against it exist in real code, and prefer jsonbpathops over the default jsonb_ops operator class when only @> containment is needed - it produces a smaller, faster index.

Pitfall: Session-Mode Assumptions Under Transaction-Pooling PgBouncer

Problem: Code that opens a transaction, sets a session-level SET variable, and expects it to persist into a later, separate transaction on "the same connection" breaks unpredictably under transaction pooling, because the underlying server connection may be handed to a different client between transactions.

Recommended approach: scope all session state to within a single transaction, or use PgBouncer's session pooling mode for the specific workloads that require it (accepting the higher connection overhead).

Pitfall: Partitioning Prematurely on Tables That Don't Need It

Problem: Partitioning a 2-million-row table "for future scale" adds query planning overhead, complicates every schema migration, and provides no measurable benefit below tens of millions of rows for most access patterns.

Recommended approach: partition when a specific, measured pain point exists - typically retention (bulk-dropping old data) or a working set so large that partition pruning measurably reduces I/O.

Testing Strategies

• Index usage verification: after adding a specialized index (GIN/GiST/BRIN), confirm with EXPLAIN (ANALYZE, BUFFERS) that the planner actually chooses it - a common surprise is the planner preferring a sequential scan on small test datasets, which is correct behavior, not a bug.
• Partition pruning tests: assert via EXPLAIN that queries filtering on the partition key only scan the expected partitions (Partitions removed in the plan output).
• Pooling mode integration tests: run the full application test suite against a PgBouncer instance in transaction-pooling mode, not only against a direct Postgres connection, to catch session-state assumptions before production.
• Replication lag tests: in staging, simulate load and assert pgstatreplication.replay_lag (physical) or subscriber apply lag (logical) stays within an agreed SLA under representative write throughput.

Performance, Security & Scalability Considerations

• PostgreSQL 18's asynchronous I/O subsystem can meaningfully improve read throughput (vendor benchmarks report up to 3x for storage-bound workloads) with no application changes required - prioritize testing this on upgrade.
• uuidv7() (new in PostgreSQL 18) produces timestamp-ordered UUIDs, which insert into B-tree indexes far more efficiently than random uuidv4() values (avoiding index page fragmentation from random insertion order) while retaining the distributed-generation benefits of UUIDs.
• OAuth authentication (PostgreSQL 18) removes the need for password- or certificate-based authentication schemes in environments already standardized on an identity provider - evaluate for new deployments before defaulting to scram-sha-256 plus an external secrets manager.
• Security: use role-based grants scoped per schema/table, enable row-level security (RLS) for multi-tenant tables where the database itself should enforce tenant isolation rather than relying solely on application-layer WHERE tenant_id = ? discipline, and always require sslmode=verify-full for connections crossing an untrusted network.
• Scalability: read replicas (physical streaming replication) scale read throughput horizontally; write throughput scales primarily via vertical scaling or sharding at the application layer - evaluate Redis as a read-through cache before reaching for sharding.

Edge Cases & Advanced Usage

• Temporal constraints (PostgreSQL 18): native support for PRIMARY KEY/UNIQUE/FOREIGN KEY constraints over ranges (e.g., ensuring no two active price records overlap for the same product) removes a class of exclusion-constraint boilerplate previously required via GiST.
• OLD/NEW in RETURNING (PostgreSQL 18): UPDATE ... RETURNING OLD., NEW. allows capturing before/after state in a single statement, useful for audit logging without a separate trigger.
• Virtual generated columns: computed at read time rather than stored, now the default for GENERATED ALWAYS AS (...) columns without STORED - useful for derived values that change semantics if recomputed lazily versus persisted.
• Skip scan on multicolumn B-tree indexes (PostgreSQL 18): queries that omit an equality condition on a leading composite-index column can, in some cases, still use the index - verify with EXPLAIN rather than assuming; do not rely on this as a substitute for correct column ordering in new indexes.
• Zero-downtime major version upgrades: logical replication enables running the new major version as a subscriber, validating it against production traffic, then cutting over with a brief write pause instead of a multi-hour pg_upgrade maintenance window.

When Not to Use / Alternatives

Choose a distributed SQL engine (CockroachDB, YugabyteDB, Spanner) when write throughput must scale horizontally across regions with strong consistency and the operational complexity is justified by genuine global scale.

Choose MongoDB (best practices) when the domain model is naturally document-shaped with deeply nested, per-document-variable structure and cross-document joins are rare.

Related Documentation

• General SQL Design Patterns - normalization, isolation levels, and query optimization fundamentals this document builds on.
• Redis Best Practices - caching layer to reduce load on Postgres for hot read paths.
• MongoDB Best Practices - comparison for document-shaped domains.
• FastAPI Best Practices - async database session and connection-pool integration.
• Django Best Practices - CONNMAX_AGE and ORM-level pooling considerations.
• Microservices Architecture - logical replication as a data-distribution mechanism between services.

Glossary

• GIN: Generalized Inverted Index, optimal for composite/multi-valued data like JSONB and arrays.
• GiST: Generalized Search Tree, optimal for geometric data, ranges, and exclusion constraints.
• BRIN: Block Range Index, a minimal-footprint index for large, naturally ordered append-only tables.
• JSONB: PostgreSQL's binary, indexable JSON storage format.
• Partition pruning: The planner's ability to skip partitions that cannot contain matching rows.
• Autovacuum: The background process that reclaims dead tuples and refreshes planner statistics.
• Logical replication: Row-level, publish/subscribe replication independent of physical storage format or major version.
• Transaction pooling: A PgBouncer mode that returns a server connection to the pool as soon as a transaction ends, maximizing connection reuse at the cost of session-state persistence.