PostgreSQL Performance Tuning for Application Developers (2026 Edition)¶

Slow queries are not a database problem. They are a design problem.

A query that takes 4 seconds on a table with 100 million rows almost always has a fixable root cause: missing index, wrong index type, table bloat, exhausted connection pool, or configuration defaults that were set for a 1GB dataset and never updated. Most performance problems are found — and solved — at the query and schema level before any hardware needs to be touched.

The Ground Rules¶

Before tuning anything, state what you are optimizing for. The two primary goals are usually in tension:

• Read throughput: Indexes help. More indexes = faster reads, slower writes.
• Write throughput: Indexes hurt. Every INSERT/UPDATE/DELETE must maintain all indexes.

For OLTP applications (web apps, APIs, microservices), reads dominate 80–95% of traffic. Index aggressively, monitor write latency separately. For data pipelines and batch jobs, consider bulk-loading with indexes dropped and rebuilt after.

Part 1: Profile First, Tune Second¶

The most common tuning mistake is guessing. Always measure before changing anything.

Enable pg_stat_statements¶

This extension tracks every query executed, how often, and how long. It is the single most useful tool for finding slow queries.

-- Add to postgresql.conf, then restart
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Create the extension once per database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Find your top 10 slowest queries by total time:

SELECT
    round(total_exec_time::numeric, 2)  AS total_ms,
    round(mean_exec_time::numeric, 2)   AS mean_ms,
    calls,
    round(rows::numeric / calls, 1)     AS avg_rows,
    left(query, 120)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Find high-frequency queries with mediocre latency (often the biggest real-world impact):

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY mean_exec_time DESC
LIMIT 20;

Reset stats after tuning to measure improvements cleanly:

SELECT pg_stat_statements_reset();

Find Unused Indexes¶

Indexes cost writes and storage. Unused ones are pure overhead:

SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND indisunique IS FALSE
ORDER BY pg_relation_size(indexrelid) DESC;

Any index with zero scans since the last stats reset is a candidate for removal. Confirm with a longer observation window (one week minimum) before dropping.

Find Missing Indexes (Sequential Scans on Large Tables)¶

SELECT
    schemaname,
    relname AS table,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND n_live_tup > 100000
ORDER BY seq_tup_read DESC
LIMIT 20;

High seq_tup_read on a large table means a full table scan is happening repeatedly. That table needs an index on the columns in the WHERE clause.

Part 2: Reading EXPLAIN Like a Senior Engineer¶

EXPLAIN shows the query plan. EXPLAIN (ANALYZE, BUFFERS) shows what actually happened, including cache usage.

The Modern EXPLAIN Invocation¶

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM transactions
WHERE acc_from = 'ACC-001'
  AND created_at > now() - interval '30 days';

What each output line means:

Bitmap Heap Scan on transactions  (cost=297.23..8421.44 rows=10037 width=89)
                                   ^cost estimate      ^rows estimate
  Recheck Cond: (acc_from = 'ACC-001')
  ->  Bitmap Index Scan on idx_txn_acc_from  (cost=0.00..294.72 rows=10037)
        Index Cond: (acc_from = 'ACC-001')
  Buffers: shared hit=4821 read=4153
           ^cache hits        ^disk reads
Planning Time: 1.2 ms
Execution Time: 297.5 ms

Key things to look for:

Force Plans for Testing¶

-- Test without index to measure impact
SET enable_indexscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_indexscan = on;

Part 3: Index Strategy¶

The Baseline: B-Tree Indexes¶

B-tree is the default and handles =, <, >, BETWEEN, LIKE 'prefix%', and ORDER BY.

-- Single column
CREATE INDEX CONCURRENTLY idx_txn_acc_from
    ON transactions (acc_from);

-- With timing: sequential scan vs index scan
-- Before: 3,757 ms (seq scan, 100M rows)
-- After:    297 ms (index scan) — 12× faster

Always use CONCURRENTLY in production — it builds the index without locking writes.

Compound Indexes: Column Order Matters¶

A compound index (a, b) is useful for: - WHERE a = ? AND b = ? — uses both columns - WHERE a = ? — uses leading column only - ORDER BY a, b — supports sort without a separate sort step

It is not useful for WHERE b = ? alone — the leading column must be present.

-- For: WHERE acc_from = ? AND acc_to = ?
CREATE INDEX CONCURRENTLY idx_txn_from_to
    ON transactions (acc_from, acc_to);

-- Timing comparison for acc_from + acc_to filter:
-- Separate indexes:   34.6 ms
-- Compound index:      1.2 ms — 28× faster

Rule: Put the highest-selectivity column (fewest duplicate values) first in a compound index when both columns appear in WHERE. Put equality columns before range columns.

Covering Indexes: INCLUDE Columns¶

A covering index contains all columns the query needs, so PostgreSQL never has to visit the heap table. This produces an Index Only Scan — the fastest possible read path.

-- Query: SELECT acc_from, acc_to, value FROM transactions WHERE acc_from = ?
CREATE INDEX CONCURRENTLY idx_txn_covering
    ON transactions (acc_from)
    INCLUDE (acc_to, value);

The INCLUDE columns are stored in the index leaf but not in the B-tree structure, so they don't affect sort order and don't increase index depth.

When to use: High-frequency read queries that always select the same small set of columns.

Partial Indexes: Index Only What You Query¶

If 95% of queries filter on status = 'active', there's no value indexing the 95% of rows that are 'archived'.

-- Index only active records
CREATE INDEX CONCURRENTLY idx_orders_active
    ON orders (created_at)
    WHERE status = 'active';

-- This query will use it:
SELECT * FROM orders WHERE status = 'active' AND created_at > now() - interval '7 days';

-- This query will NOT (different status value):
SELECT * FROM orders WHERE status = 'archived' AND created_at > ...;

Partial indexes are smaller, faster to build, and faster to scan.

Expression Indexes¶

Index the result of a function, not the raw column value:

-- Query uses lower() for case-insensitive match
SELECT * FROM users WHERE lower(email) = lower('User@Example.com');

-- Create expression index
CREATE INDEX CONCURRENTLY idx_users_email_lower
    ON users (lower(email));

-- Index on JSONB field extraction
CREATE INDEX CONCURRENTLY idx_events_user_id
    ON events ((payload->>'user_id'));

The query must use the exact same expression as the index for PostgreSQL to use it.

GIN Indexes: Full-Text Search and JSONB¶

B-tree cannot index array membership or JSONB key existence. GIN (Generalized Inverted Index) can.

-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX CONCURRENTLY idx_articles_search
    ON articles USING GIN (search_vector);

-- Query
SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'cloud native kubernetes');

-- JSONB containment
CREATE INDEX CONCURRENTLY idx_events_payload
    ON events USING GIN (payload jsonb_path_ops);

-- Query: find events where payload contains {"type": "login"}
SELECT * FROM events WHERE payload @> '{"type": "login"}';

BRIN Indexes: Massive Tables with Natural Order¶

BRIN (Block Range Index) stores min/max values per block range instead of individual row pointers. It is tiny (often 100× smaller than B-tree) but only works when data has physical ordering — like a created_at column on an insert-only table.

-- Effective for time-series / append-only tables
CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING BRIN (created_at) WITH (pages_per_range = 128);

-- A 100M row table's BRIN index: ~500 KB vs ~3 GB for B-tree

Use BRIN when: The column has high cardinality, data is inserted roughly in order (like timestamps), and queries use range filters. BRIN is not useful for random lookup by arbitrary values.

Part 4: Server-Side Configuration¶

Defaults ship for compatibility, not performance. These three parameters have the highest impact.

shared_buffers¶

PostgreSQL's in-memory page cache. Default is 128MB — far too low for any production workload.

# postgresql.conf
shared_buffers = 4GB           # 25% of total RAM, up to 8GB

PostgreSQL also uses the OS page cache on top of this. Setting shared_buffers above 8GB rarely helps because the OS cache covers the gap.

effective_cache_size¶

Tells the query planner how much total cache (PostgreSQL + OS) is available. Does not allocate memory — it's a hint that affects plan choices. Set it high.

effective_cache_size = 12GB    # 75% of total RAM

With a high effective_cache_size, the planner prefers index scans over sequential scans because it assumes more data is cached.

work_mem¶

Memory per sort or hash operation. Default is 4MB. If a sort can't fit in work_mem, it spills to disk (Sort Method: external merge Disk).

work_mem = 64MB

Warning: work_mem is per-operation, and a complex query can have many sorts and hashes simultaneously. With 200 connections and 5 operations each at 64MB, peak memory is 64GB. Set conservatively for OLTP; raise per session for analytics:

-- For a single heavy query session
SET work_mem = '512MB';
SELECT ...complex analytics query...;

Full Recommended Configuration for 16GB OLTP Server¶

# postgresql.conf
shared_buffers          = 4GB
effective_cache_size    = 12GB
work_mem                = 32MB
maintenance_work_mem    = 1GB        # For VACUUM, CREATE INDEX
max_connections         = 100        # Use PgBouncer for more
wal_buffers             = 64MB
checkpoint_completion_target = 0.9
random_page_cost        = 1.1        # SSD: 1.1; spinning disk: 4.0
effective_io_concurrency = 200       # SSD: 200; spinning disk: 2
max_worker_processes    = 8
max_parallel_workers    = 4
max_parallel_workers_per_gather = 2

After changing postgresql.conf:

# Parameters marked as requiring restart
sudo systemctl restart postgresql-17

# Parameters that apply on reload
sudo systemctl reload postgresql-17

# Check which changed without restart
SELECT name, pending_restart FROM pg_settings WHERE pending_restart = true;

Part 5: Connection Pooling with PgBouncer¶

PostgreSQL creates a new OS process (not a thread) per connection. At 500 connections, you have 500 processes competing for memory and CPU even when most are idle. Connection overhead alone can consume gigabytes of RAM and add latency.

PgBouncer sits between your application and PostgreSQL, maintaining a small pool of real database connections and multiplexing thousands of application connections across them.

Install PgBouncer¶

# RHEL/Rocky Linux 9
sudo dnf install -y pgbouncer

# Debian/Ubuntu
sudo apt install -y pgbouncer

Configure /etc/pgbouncer/pgbouncer.ini¶

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Transaction pooling: connection returned to pool after each transaction
; Best for OLTP — works with most ORMs
pool_mode = transaction

server_pool_size = 25          ; Real DB connections per database/user pair
max_client_conn = 1000         ; Max application connections PgBouncer accepts
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Keep connections alive
server_idle_timeout = 600
client_idle_timeout = 0

log_connections = 0
log_disconnections = 0

Create the Auth File¶

# Generate scram-sha-256 hash from PostgreSQL
psql -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename = 'myappuser';" -t

# /etc/pgbouncer/userlist.txt
"myappuser" "SCRAM-SHA-256$..."

sudo systemctl enable --now pgbouncer

Application connects to port 6432 instead of 5432. PostgreSQL max_connections can now be set to 100 instead of 1000 — PgBouncer absorbs the application concurrency.

Pool Mode Trade-offs¶

Use transaction mode for most OLTP applications. Use session mode if your application uses advisory locks, SET LOCAL, or prepared statements that persist across transactions.

Part 6: Table Partitioning¶

When a table grows beyond ~50–100M rows and queries always filter by a known dimension (time, tenant, region), partitioning lets PostgreSQL skip entire partitions without scanning them.

Range Partitioning by Date¶

-- Create partitioned parent table
CREATE TABLE transactions (
    id          bigserial,
    acc_from    varchar(20)  NOT NULL,
    acc_to      varchar(20)  NOT NULL,
    value       numeric(18,2) NOT NULL,
    created_at  timestamptz  NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE transactions_2026_01 PARTITION OF transactions
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE transactions_2026_02 PARTITION OF transactions
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Indexes are created per partition
CREATE INDEX ON transactions_2026_01 (acc_from);
CREATE INDEX ON transactions_2026_02 (acc_from);

Partition pruning happens automatically when the WHERE clause includes the partition key:

-- PostgreSQL scans only transactions_2026_01 — all other partitions skipped
EXPLAIN SELECT * FROM transactions
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
  AND acc_from = 'ACC-001';
-- → Index Scan on transactions_2026_01 (not a full table scan)

Automate Partition Creation¶

Use pg_partman to create and drop partitions on a schedule:

sudo dnf install -y pg_partman_17

CREATE EXTENSION pg_partman SCHEMA partman;

SELECT partman.create_parent(
    p_parent_table := 'public.transactions',
    p_control      := 'created_at',
    p_interval     := 'monthly',
    p_premake      := 3           -- Create 3 future partitions ahead
);

-- Run via cron or pg_agent to maintain partitions
SELECT partman.run_maintenance(p_analyze := false);

When Not to Partition¶

• Table < 10M rows: partitioning adds overhead without benefit
• No clear partition key: hash partitioning rarely improves query performance
• Random access patterns: partition pruning only helps range/list queries

Part 7: Autovacuum Tuning¶

PostgreSQL uses MVCC (Multi-Version Concurrency Control): updates don't overwrite rows, they create new versions and mark old ones as "dead." Autovacuum reclaims dead tuple space. If it falls behind, tables bloat, queries slow down, and in severe cases the transaction ID wraparound emergency kicks in.

Check Current Bloat¶

SELECT
    schemaname,
    relname AS table,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

A dead_pct above 20% on a frequently-queried table is a problem. High dead tuples mean queries scan rows they will then discard.

Tune Autovacuum Globally¶

Default autovacuum triggers when dead tuples exceed 50 + 0.2 × live_rows (20% of the table). On a 100M row table, that's 20 million dead tuples before vacuum runs — far too many.

# postgresql.conf

# Worker resources
autovacuum_max_workers         = 5       # Default: 3
autovacuum_vacuum_cost_delay   = 2ms     # Default: 2ms (SSD); set 20ms for spinning disk
autovacuum_vacuum_cost_limit   = 400     # Default: 200; raise for SSD

# Trigger thresholds (lower = vacuum runs sooner)
autovacuum_vacuum_scale_factor    = 0.05  # Default: 0.20 (5% dead triggers vacuum)
autovacuum_analyze_scale_factor   = 0.02  # Default: 0.10
autovacuum_vacuum_threshold       = 500   # Default: 50

Per-Table Autovacuum Overrides¶

Hot tables need more aggressive settings than cold tables:

-- High-write OLTP table: vacuum aggressively
ALTER TABLE transactions SET (
    autovacuum_vacuum_scale_factor    = 0.01,
    autovacuum_vacuum_threshold       = 100,
    autovacuum_analyze_scale_factor   = 0.005,
    autovacuum_vacuum_cost_delay      = 1
);

-- Large archive table: vacuum rarely (minimal writes)
ALTER TABLE transactions_archive SET (
    autovacuum_vacuum_scale_factor    = 0.10,
    autovacuum_vacuum_threshold       = 10000
);

Emergency Manual Vacuum¶

When bloat is already severe and autovacuum can't keep up:

# Run VACUUM ANALYZE without blocking reads/writes (but blocks other VACUUMs)
psql -d myapp -c "VACUUM (ANALYZE, VERBOSE) transactions;"

# For extreme bloat: reclaims space to OS (requires brief exclusive lock at end)
psql -d myapp -c "VACUUM (FULL, ANALYZE) transactions_archive;"

VACUUM FULL rewrites the entire table and should only be used during maintenance windows. For zero-downtime space reclamation, use pg_squeeze or pg_repack extensions.

Part 8: Data Modeling for Performance¶

The schema design decisions made at the start are harder to change than any index or configuration. A few rules that compound over time:

Use the Right Data Types¶

-- Bad: storing integers as text
account_id  varchar(20)   -- requires text comparison, larger storage
-- Better
account_id  bigint        -- fixed 8 bytes, integer comparison

-- Bad: status as freeform text
status  varchar(20)       -- unbounded, no constraint
-- Better
status  text CHECK (status IN ('active', 'inactive', 'suspended'))
-- Or use a native ENUM (faster comparison, less storage):
CREATE TYPE account_status AS ENUM ('active', 'inactive', 'suspended');

Use BIGSERIAL or UUIDs Intentionally¶

-- For internal IDs (contiguous, fast joins)
id  bigserial PRIMARY KEY

-- For external/distributed IDs (random order — use UUIDv7 for monotonic inserts)
id  uuid DEFAULT gen_random_uuid() PRIMARY KEY

UUIDv4 inserts into a B-tree primary key cause page splits. UUIDv7 (time-ordered) avoids this. PostgreSQL 17 includes gen_ulid() as a time-ordered alternative.

Separate Hot and Cold Data¶

Tables with a mix of frequently-updated and rarely-touched columns slow down vacuum and bloat the heap. Vertical partitioning keeps hot data small:

-- Hot table: core financial data, updated often
CREATE TABLE transactions (
    id          bigserial PRIMARY KEY,
    acc_from    varchar(20) NOT NULL,
    acc_to      varchar(20) NOT NULL,
    value       numeric(18,2) NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL
);

-- Cold table: metadata appended once, never updated
CREATE TABLE transaction_details (
    transaction_id  bigint PRIMARY KEY REFERENCES transactions(id),
    channel         text,
    ip_address      inet,
    user_agent      text,
    raw_payload     jsonb
);

Part 9: Query-Level Optimization Patterns¶

Avoid Functions on Indexed Columns in WHERE¶

-- Bad: function prevents index use
SELECT * FROM users WHERE date_trunc('day', created_at) = '2026-05-01';

-- Good: range query uses index
SELECT * FROM users
WHERE created_at >= '2026-05-01'
  AND created_at <  '2026-05-02';

Use CTEs Carefully (PostgreSQL 12+)¶

Before PostgreSQL 12, CTEs were optimization fences — the planner treated them as black boxes. Since 12, the planner can inline CTEs. If you need the old fence behavior (rare), use WITH ... AS MATERIALIZED.

-- PostgreSQL 12+: this CTE is inlined and the planner optimizes it end-to-end
WITH recent_txn AS (
    SELECT * FROM transactions WHERE created_at > now() - interval '1 day'
)
SELECT acc_from, sum(value) FROM recent_txn GROUP BY acc_from;

-- Force materialization (old behavior) — rarely needed
WITH recent_txn AS MATERIALIZED (
    SELECT * FROM transactions WHERE created_at > now() - interval '1 day'
)
SELECT acc_from, sum(value) FROM recent_txn GROUP BY acc_from;

LIMIT + ORDER BY Requires the Right Index¶

-- Query: get latest 20 transactions for account ACC-001
SELECT * FROM transactions
WHERE acc_from = 'ACC-001'
ORDER BY created_at DESC
LIMIT 20;

-- Correct index: equality first, then ORDER BY column
CREATE INDEX CONCURRENTLY idx_txn_acc_created
    ON transactions (acc_from, created_at DESC);

Without this index, PostgreSQL fetches all rows matching acc_from = 'ACC-001', sorts them, then takes the top 20 — regardless of LIMIT.

Batch Large Writes¶

-- Bad: 100,000 individual INSERTs (100,000 round trips)
for row in data:
    execute("INSERT INTO events VALUES (?)", row)

-- Good: single multi-row INSERT
INSERT INTO events (user_id, event_type, created_at)
VALUES
    (1, 'login', now()),
    (2, 'purchase', now()),
    ...;   -- Up to ~1000 rows per statement

For very large bulk loads, use COPY:

psql -d myapp -c "\COPY events FROM '/tmp/events.csv' CSV HEADER;"

COPY bypasses row-by-row overhead and is 10–100× faster than INSERT for bulk data.

Part 10: Monitoring Queries to Run Regularly¶

Make these part of your weekly operational review:

Largest Tables and Indexes¶

SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total,
    pg_size_pretty(pg_relation_size(oid))        AS table,
    pg_size_pretty(pg_indexes_size(oid))         AS indexes
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 15;

Long-Running Queries¶

SELECT
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '30 seconds'
ORDER BY duration DESC;

Lock Waits¶

SELECT
    blocked.pid          AS blocked_pid,
    blocked.query        AS blocked_query,
    blocking.pid         AS blocking_pid,
    blocking.query       AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.cardinality(pg_blocking_pids(blocked.pid)) > 0;

Index Hit Rate (Target: > 99%)¶

SELECT
    relname AS table,
    round(idx_blks_hit * 100.0 / nullif(idx_blks_hit + idx_blks_read, 0), 2) AS idx_hit_rate,
    round(heap_blks_hit * 100.0 / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS heap_hit_rate
FROM pg_statio_user_tables
ORDER BY idx_blks_read + heap_blks_read DESC
LIMIT 15;

If index or heap hit rates are below 95%, increase shared_buffers or the server's RAM.

Summary¶

PostgreSQL performance tuning in 2026 is a layered discipline. The layers, from highest to lowest impact, are:

The Tuning Hierarchy¶

The 10 Rules That Compound¶

1. Measure with pg_stat_statements first. Never guess which query is slow.
2. EXPLAIN (ANALYZE, BUFFERS) — not just EXPLAIN. Estimated vs actual rows diverge constantly.
3. Column order in compound indexes matters: equality columns first, range columns last.
4. INCLUDE columns turn B-tree indexes into covering indexes — the fastest read path.
5. Partial indexes on filtered subsets are smaller, faster, and cheaper to maintain.
6. PgBouncer in transaction mode is not optional above 200 connections. Set PostgreSQL max_connections = 100.
7. shared_buffers = 25% of RAM is a starting point, not a ceiling. Monitor cache hit rates.
8. Autovacuum defaults are wrong for large tables. Lower scale factors and raise cost limits.
9. Partitioning is a schema decision, not a query fix. It helps only when queries filter on the partition key.
10. Functions on indexed columns in WHERE kill indexes. Rewrite to range queries or use expression indexes.

Every technique here starts with measurement, makes one change, and measures again. That discipline — not any single setting — is what separates databases that stay fast from ones that require emergency tuning at 3 AM.

Based on PostgreSQL 17, PgBouncer 1.23, and pg_partman 5.x on Rocky Linux 9. Techniques apply to any PostgreSQL 14+ installation.

Questions or discussion? Connect on LinkedIn, X or reach out via email.

Discussion

Have thoughts on this post? Share them below — questions, corrections, or your own experience are all welcome.