PostgreSQL Performance Tuning: The Complete Guide

PostgreSQL performance tuning is the process of configuring your database, optimizing queries, managing indexes, and adjusting system resources to reduce query latency, increase throughput, and lower infrastructure costs. The encouraging news is that most PostgreSQL performance problems are solved through better indexing, query rewrites, and configuration changes — not more hardware. This is a knowledge problem, and knowledge problems are the very best kind.

This guide applies to PostgreSQL 14 and later, though most techniques work across recent major versions. For the official reference on server configuration, see the PostgreSQL runtime configuration documentation.

This guide covers every major lever available for PostgreSQL performance tuning: reading execution plans, indexing strategies, query optimization, connection management, materialized views, autovacuum tuning, server configuration, lock contention, and monitoring. Each section provides enough context to act on immediately, with links to dedicated deep-dive guides where the topic warrants further attention.

Five things worth checking first

Before reading the full guide, allow me to direct your attention to five items. They resolve the majority of PostgreSQL performance issues, and any one of them may be all you need today.

1. Enable pg_stat_statements and sort by total_exec_time to find your most expensive queries. This single step tells you where to focus — and the answer is often not what you would expect.
2. Run EXPLAIN (ANALYZE, BUFFERS) on your slowest queries and look for sequential scans on large tables, row estimate mismatches greater than 10x, and nested loop joins over unindexed columns.
3. Check for missing indexes by querying pg_stat_user_tables — tables with high seq_scan counts relative to idx_scan are candidates for indexing.
4. Review shared_buffers and work_mem. PostgreSQL ships with shared_buffers = 128MB and work_mem = 4MB. Both are far too conservative for production workloads, and adjusting them is straightforward.
5. Verify autovacuum is keeping up. Query pg_stat_user_tables for tables where n_dead_tup is high or last_autovacuum is stale. Dead tuple buildup degrades scan performance and wastes disk space.

Why PostgreSQL performance tuning matters

Most PostgreSQL performance problems are knowledge problems, not hardware problems. A single missing index can make a query 1,000 times slower. A poorly sized work_mem can force every sort and hash operation to spill to disk. A misconfigured connection pool can exhaust backend processes while the actual database sits idle waiting for something useful to do.

PostgreSQL ships with conservative defaults designed for broad compatibility — entirely reasonable for a project that must run everywhere from a Raspberry Pi to a 128-core production server. shared_buffers defaults to 128MB. random_page_cost defaults to 4.0, a value calibrated for spinning disks, not SSDs. These defaults mean that an untuned PostgreSQL instance is leaving performance on the table.

The cost of a slow query compounds across three dimensions. User experience degrades — a 200ms API call becomes a 2-second page load. Infrastructure spend increases — teams add read replicas and bigger instances to compensate for queries that a single index would have resolved. Engineering time is consumed building caching layers that address the symptom rather than the cause.

Reading your queries — EXPLAIN ANALYZE

Every performance investigation starts with EXPLAIN. It is the single most important diagnostic tool in PostgreSQL, and becoming fluent in reading its output will serve you in every tuning effort that follows.

EXPLAIN vs EXPLAIN ANALYZE vs EXPLAIN (ANALYZE, BUFFERS, TIMING)

EXPLAIN shows the planner's estimated execution plan without running the query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1542.00 rows=15 width=96)
  Filter: (customer_id = 42)

EXPLAIN ANALYZE actually executes the query and shows real timing alongside the estimates:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1542.00 rows=15 width=96)
                     (actual time=0.021..12.345 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 49988
Planning Time: 0.085 ms
Execution Time: 12.401 ms

The most informative form includes BUFFERS, which shows how much data was read from cache versus disk:

EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1542.00 rows=15 width=96)
                     (actual time=0.021..12.345 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 49988
  Buffers: shared hit=542 read=200
Planning Time: 0.085 ms
Execution Time: 12.401 ms

shared hit=542 means 542 pages were served from PostgreSQL's buffer cache. read=200 means 200 pages required disk I/O. A high read count relative to hit suggests the working set exceeds shared_buffers — a situation worth attending to.

How to read an execution plan

Execution plans are trees read from inside out, bottom to top. Each node represents an operation: a scan, join, sort, or aggregate. The key fields to watch:

• cost=startup..total: The planner's estimated cost in abstract units. The startup cost is work done before the first row is returned.
• rows: The planner's estimated number of rows this node will produce.
• actual time=startup..total: Real milliseconds (only with ANALYZE). This is the time per loop — multiply by loops for the true total.
• rows (actual): The actual number of rows produced. Compare this to the estimated rows — a large discrepancy indicates stale statistics.
• loops: How many times this node was executed. Common in nested loop joins.

What the plan is telling you

Sequential scans on large tables. A Seq Scan on a table with millions of rows usually means a missing index. Not all sequential scans are cause for concern — on small tables or queries that truly need most rows, a sequential scan is the right choice.

Row estimate mismatches. When the planner estimates rows=1 but the actual is rows=50000, the resulting plan may be quite far from optimal. Run ANALYZE on the table to refresh statistics.

Nested loop joins over unindexed columns. A nested loop performs the inner scan once per outer row. If the inner scan is a sequential scan, the cost multiplies quickly.

Sort or Hash operations with disk spills. If you see Sort Method: external merge Disk: in the plan, the sort exceeded work_mem and spilled to disk. Increasing work_mem or reducing the sort input can eliminate this.

For the complete guide to reading execution plans, see the EXPLAIN ANALYZE guide.

Indexing strategies

Indexes are the single most impactful tool for PostgreSQL performance. A well-chosen index can reduce a query from seconds to microseconds. A missing index is the most common cause of slow queries in production — and, if I may say so, the most satisfying to resolve.

B-tree indexes — the default workhorse

B-tree is the default index type in PostgreSQL and the correct choice for the vast majority of use cases. B-tree indexes support equality (=), range (<, >, BETWEEN), sorting (ORDER BY), and prefix pattern matching (LIKE 'abc%').

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

One line. This single index transforms a sequential scan of 50,000 rows into an index scan that reads a handful of pages.

Multi-column index ordering matters. In a composite B-tree, the leftmost column is the primary sort key. The index is useful for queries that filter on the leftmost column, or the leftmost N columns.

-- This index supports WHERE status = 'active' AND created_at > '2026-01-01'
-- It also supports WHERE status = 'active' (alone)
-- It does NOT efficiently support WHERE created_at > '2026-01-01' (alone)
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

Covering indexes use the INCLUDE clause to store additional columns in the index leaf pages, enabling index-only scans that never touch the heap table:

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (order_total, created_at);

See: B-tree index | Covering index | Index-only scan

Composite indexes

The key rule for composite index column ordering: equality columns first, range columns last.

-- Query: WHERE status = 'shipped' AND created_at BETWEEN '2026-01-01' AND '2026-03-01'
-- Good: equality (status) first, range (created_at) second
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- Less effective: range column first narrows the index scan less
CREATE INDEX idx_orders_date_status ON orders (created_at, status);

The leftmost prefix rule means a composite index on (a, b, c) supports queries on (a), (a, b), and (a, b, c), but not (b) or (c) alone.

For the full guide on composite index design, see Composite Indexes.

Partial indexes

Partial indexes index only a subset of rows, defined by a WHERE clause. They are smaller, faster to scan, and cheaper to maintain than full indexes.

-- Only index active orders (80% of queries, 10% of rows)
CREATE INDEX idx_orders_active ON orders (customer_id, created_at)
  WHERE status = 'active';

-- Only index rows where a nullable column is not null
CREATE INDEX idx_users_email_verified ON users (email)
  WHERE email_verified_at IS NOT NULL;

A partial index on active records that comprise 10% of the table is 90% smaller than a full index. It also reduces write overhead because PostgreSQL only updates the index when the filtered condition applies.

See the Partial Indexes guide for advanced patterns and common pitfalls.

Specialized index types

GIN (Generalized Inverted Index) is designed for values that contain multiple elements: JSONB documents, arrays, and full-text search vectors.

-- Index JSONB fields for containment queries (@>)
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- Full-text search index
CREATE INDEX idx_articles_search ON articles
  USING gin (to_tsvector('english', title || ' ' || body));

GiST (Generalized Search Tree) supports geometric data, range types, and PostGIS spatial queries.

-- Range type index for overlapping date ranges
CREATE INDEX idx_bookings_dates ON bookings USING gist (date_range);

Hash indexes support only equality checks (=) but do so with less storage than B-tree for equality-only workloads.

CREATE INDEX idx_sessions_token ON sessions USING hash (session_token);

BRIN (Block Range Index) is extremely compact and effective for large tables where data is naturally ordered. A BRIN index on a 100-million-row table might be 100KB where a B-tree would be 2GB.

-- Tiny index for a 100M-row time-series table
CREATE INDEX idx_events_created ON events USING brin (created_at);

See: GIN index | GiST index | Hash index

Finding missing indexes

The most reliable way to find missing indexes is to examine PostgreSQL's own statistics. The database has been keeping notes, and they are worth reading. For the complete three-step workflow — from pg_stat_user_tables to pg_qualstats to HypoPG validation — see the missing index detection guide.

SELECT
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  n_live_tup,
  seq_scan - idx_scan AS scan_diff
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY seq_scan - idx_scan DESC
LIMIT 20;

Tables with high seq_scan counts, many live tuples, and low idx_scan counts are strong candidates for indexing.

HypoPG lets you create hypothetical indexes that exist only in the planner — you can test whether an index would improve a query without building it:

-- Create a hypothetical index
SELECT hypopg_create_index('CREATE INDEX ON orders (customer_id, status)');

-- Now EXPLAIN will consider it
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'active';

-- Clean up
SELECT hypopg_reset();

See: HypoPG guide | pg_qualstats guide

Query optimization techniques

Once you have the right indexes in place, the next lever is the queries themselves. A well-indexed query with a poor structure still underperforms — the index provides the path, but the query must walk it sensibly.

Rewriting slow queries

Correlated subqueries to JOINs. A correlated subquery executes once per outer row. Rewriting it as a JOIN allows the planner to choose more efficient join strategies:

-- Slow: correlated subquery (executes once per customer)
SELECT c.name,
       (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id)
FROM customers c;

-- Faster: JOIN with aggregate
SELECT c.name, MAX(o.created_at)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Alternative: LATERAL join (useful when you need more than one column)
SELECT c.name, latest.created_at, latest.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT created_at, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 1
) latest ON true;

EXISTS vs IN vs JOIN for existence checks. EXISTS short-circuits — it stops as soon as it finds one matching row:

-- Prefer EXISTS for existence checks
SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.created_at > '2026-01-01'
);

Common Table Expressions (CTEs)

Since PostgreSQL 12, CTEs are inlined by default when they are non-recursive and referenced only once:

-- PostgreSQL 12+: this CTE is inlined — the WHERE clause pushes down
WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > '2026-01-01'
)
SELECT * FROM recent_orders WHERE customer_id = 42;

You can force materialization with AS MATERIALIZED when the CTE result is used multiple times:

WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE lifetime_value > 10000
UNION ALL
SELECT * FROM expensive_calc WHERE customer_id IN (SELECT id FROM vip_customers);

See: CTE | Lateral join

Pagination done right

OFFSET/LIMIT pagination degrades linearly with depth. OFFSET 100000 forces PostgreSQL to scan and discard 100,000 rows before returning the next page. Keyset (cursor) pagination eliminates this problem:

-- First page
SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page (using the last row's values as cursor)
SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ('2026-03-20 14:30:00', 8842)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (created_at DESC, id DESC), every page is equally fast regardless of depth.

See: Keyset pagination guide

The N+1 query problem

The N+1 problem occurs when application code executes one query to fetch a list of N records, then executes N additional queries to fetch related data for each record:

-- 1 query to get 100 customers
SELECT * FROM customers LIMIT 100;

-- Then 100 individual queries (the N in N+1)
SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 2;
...
SELECT * FROM orders WHERE customer_id = 100;

The fix is to eager-load the related data in a single query. Every major ORM provides a mechanism: select_related/prefetch_related in Django, includes/preload in Rails, JOIN FETCH in Spring Boot JPA, with() in Laravel Eloquent.

To detect N+1 queries in production, enable auto_explain:

-- In postgresql.conf or via ALTER SYSTEM
shared_preload_libraries = 'auto_explain';
auto_explain.log_min_duration = '50ms';
auto_explain.log_nested_statements = on;

See: N+1 query problem | N+1 queries guide

Connection management

PostgreSQL uses a process-per-connection model. Each client connection spawns a dedicated backend process via fork(). Each process consumes approximately 5-10MB of memory. At high connection counts, performance degrades due to CPU context switching, lock contention on shared memory structures, and snapshot management overhead.

A common response is to raise max_connections to 500 or 1,000. This rarely improves throughput and often reduces it.

Connection pooling

A connection pooler multiplexes many application connections over a smaller number of database connections. Transaction-mode pooling assigns a database connection for the duration of a transaction, then returns it to the pool.

The three most widely deployed external poolers:

• PgBouncer: Single-threaded, minimal overhead, battle-tested. The default choice for most teams.
• Pgpool-II: Multi-process, includes load balancing, read/write splitting, and high availability.
• PgCat: Multi-threaded (Rust/Tokio), supports sharding and read/write splitting.

pool_size = (2 × CPU_cores) + disk_spindles

-- For SSD-backed systems, this simplifies to roughly 2 × CPU_cores.
-- A 4-core database server performs optimally with 8–12 active connections.

See: Connection pooling: the complete guide | PostgreSQL poolers compared

Materialized views

A materialized view stores the result of a query as a physical table. Unlike a regular view, a materialized view is computed once and read from disk until explicitly refreshed. This trades data freshness for read performance — a trade-off that is often well worth making.

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
  date_trunc('month', created_at) AS month,
  product_category,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1, 2;

-- Add an index for fast lookups
CREATE UNIQUE INDEX idx_mv_monthly_revenue
  ON mv_monthly_revenue (month, product_category);

This materialized view replaces a costly aggregation query with a simple table read. A dashboard that hits this view instead of the base table can go from 3 seconds to 3 milliseconds.

Refreshing materialized views

-- Blocks reads during refresh
REFRESH MATERIALIZED VIEW mv_monthly_revenue;

-- Allows reads during refresh (requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

The CONCURRENTLY option builds the new data in a temporary location and swaps it in atomically. It is slower but avoids downtime for read-heavy views.

-- Refresh every 15 minutes using pg_cron
SELECT cron.schedule('refresh_monthly_revenue', '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue');

When materialized views are the wrong tool

Materialized views are a poor fit when the underlying data changes rapidly and users expect real-time results, when the base query is already fast (under 100ms), or when the view is over a table with extremely high write churn.

See: Materialized views guide | Materialized view | pg_ivm

VACUUM and autovacuum tuning

PostgreSQL implements MVCC by keeping old versions of rows visible to transactions that started before the update. These dead tuples accumulate over time, wasting disk space and slowing down sequential scans. VACUUM reclaims space occupied by dead tuples.

Autovacuum configuration

The default autovacuum_vacuum_scale_factor = 0.2 triggers autovacuum when 20% of rows are dead. On a table with 100 million rows, that is 20 million dead tuples before autovacuum acts. For large, high-churn tables, set a lower scale factor per table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

Monitoring autovacuum:

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

See: Autovacuum tuning guide

Table bloat

Even with well-tuned autovacuum, tables can accumulate bloat. Detecting bloat with pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  pg_size_pretty(table_len) AS table_size,
  dead_tuple_count,
  dead_tuple_len,
  round(dead_tuple_percent::numeric, 1) AS dead_pct,
  free_space,
  round(free_percent::numeric, 1) AS free_pct
FROM pgstattuple('orders');

A dead_pct above 20% or a free_pct above 30% indicates significant bloat. pg_repack performs online table reorganization without locking:

-- From the command line (not SQL)
pg_repack --table orders --no-superuser-check -d mydb

See: Table bloat guide | Bloat | pg_repack

PostgreSQL configuration tuning

PostgreSQL's default configuration prioritizes safety and compatibility. Production deployments should adjust memory, WAL, and planner settings based on the workload and hardware.

Memory settings

-- postgresql.conf
shared_buffers = '8GB'          -- 25% of 32GB RAM
work_mem = '64MB'               -- adjust based on query complexity
effective_cache_size = '24GB'   -- 75% of 32GB RAM
maintenance_work_mem = '1GB'    -- for VACUUM, CREATE INDEX

See: shared_buffers | work_mem | Buffer cache

WAL and checkpoint settings

For write-heavy workloads, the default max_wal_size = 1GB causes frequent checkpoints, each producing a burst of I/O. Increasing it smooths I/O patterns:

-- postgresql.conf
max_wal_size = '8GB'            -- reduce checkpoint frequency
min_wal_size = '1GB'            -- WAL space for recycling
checkpoint_completion_target = 0.9

See: WAL | Checkpoint

Planner settings

The most impactful change is random_page_cost. The default of 4.0 was calibrated for spinning disks. On SSDs, setting it to 1.1 makes the planner more willing to choose index scans:

-- postgresql.conf
random_page_cost = 1.1          -- SSD storage (default 4.0 assumes spinning disks)
effective_io_concurrency = 200  -- SSD (default 1)
default_statistics_target = 200 -- better planner estimates

For columns with skewed distributions, increase the statistics target:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

See: Planner | Row estimation

Lock contention and concurrency

PostgreSQL uses locks to protect data integrity during concurrent access. Most locking is handled transparently, but certain workload patterns create contention that degrades throughput.

Identifying lock contention

-- Find blocked queries and what's blocking them
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocked.wait_event_type,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state,
  now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype
  AND gl.database IS NOT DISTINCT FROM bl.database
  AND gl.relation IS NOT DISTINCT FROM bl.relation
  AND gl.page IS NOT DISTINCT FROM bl.page
  AND gl.tuple IS NOT DISTINCT FROM bl.tuple
  AND gl.virtualxid IS NOT DISTINCT FROM bl.virtualxid
  AND gl.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND gl.classid IS NOT DISTINCT FROM bl.classid
  AND gl.objid IS NOT DISTINCT FROM bl.objid
  AND gl.objsubid IS NOT DISTINCT FROM bl.objsubid
  AND gl.pid != bl.pid
  AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid
ORDER BY blocked_duration DESC;

Common lock scenarios

Long-running transactions hold locks for their entire duration, blocking DDL and autovacuum. A forgotten BEGIN in a psql session can hold a lock for hours.

Queue processing with SELECT ... FOR UPDATE can create hot-row contention. Use SKIP LOCKED to allow workers to process different rows concurrently:

-- Each worker grabs a different available task
UPDATE tasks SET status = 'processing', worker_id = $1
WHERE id = (
  SELECT id FROM tasks
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

See: Lock contention guide | Lock contention

Monitoring and observability

You cannot tune what you cannot measure. Before making configuration changes, establish a monitoring baseline.

pg_stat_statements

The pg_stat_statements extension tracks execution statistics for every distinct query. It is the single most important monitoring tool for PostgreSQL performance.

-- Enable (requires server restart the first time)
-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 queries by total execution time
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  round(stddev_exec_time::numeric, 1) AS stddev_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time to find queries that consume the most aggregate time. Sort by mean_exec_time to find the slowest individual queries.

Unused indexes

Unused indexes consume disk space and slow down writes. Removing them is a free performance improvement:

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;

pg_stat_activity

-- Active queries running longer than 5 seconds
SELECT
  pid,
  now() - query_start AS duration,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 seconds'
ORDER BY query_start;

See: PostgreSQL monitoring stack guide | Best PostgreSQL extensions for performance | pg_stat_statements | auto_explain

When tuning is not enough — scaling PostgreSQL

Performance tuning has limits. When a single PostgreSQL instance is well-tuned and still cannot meet your requirements, the next steps follow a natural progression:

1. Tune — Apply the techniques in this guide. Most workloads never need to go beyond this step.
2. Pool — Add connection pooling to handle more concurrent connections.
3. Materialize — Pre-compute expensive aggregations with materialized views.
4. Replicate — Add read replicas for read-heavy workloads.
5. Partition — Split large tables into smaller physical partitions.
6. Shard — Distribute data across multiple independent PostgreSQL instances. This is a last resort.

Each step adds operational complexity. Pursue them in order: do not shard a database that has not been properly indexed and tuned.

CREATE TABLE events (
  id bigserial,
  created_at timestamptz NOT NULL,
  event_type text,
  payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_q1 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

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

Gold Lapel is a PostgreSQL proxy that handles connection pooling alongside automated query optimization. For a detailed scaling decision framework, see the PostgreSQL scaling guide.