PostgreSQL Performance Optimization: 15 Essential Techniques for 2025

A slow database is rarely a database problem. It's usually a query problem, an index problem, or a configuration problem — and all three are fixable without upgrading hardware.

This guide covers the techniques that consistently matter: indexing strategy, query rewriting, connection pooling, partitioning, vacuum tuning, materialized views, and configuration. Each section explains what to measure, what the fix looks like, and what it costs.

1. Indexing: The Highest-Leverage Change

Reading a Query Plan

Before adding an index, look at what PostgreSQL is actually doing. Consider a query like this against a large orders table:

SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'completed'
  AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 ...;

An unindexed plan looks something like this:

Seq Scan on orders  (cost=0.00..387429.50 rows=1234 width=245)
  Filter: (user_id = 12345 AND status = 'completed' AND created_at > '2024-01-01')
  Rows Removed by Filter: 8234567

Two things to notice, and they're the two things worth learning to spot:

• Seq Scan — PostgreSQL is reading the whole table
• Rows Removed by Filter — the number of rows read and thrown away

A large "Rows Removed by Filter" relative to rows returned is the clearest possible signal that an index is missing. The fix:

CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at DESC);

The plan should then show an Index Scan using that index, with the filtering happening in the index rather than by reading every row.

Index Types and What They're For

| Index type | Use case | Notes | |------------|----------|-------| | B-Tree | Equality and range on scalar columns | The default; right for the vast majority of cases | | B-Tree composite | Multi-column predicates | Column order matters — see below | | Partial | Queries that always filter on a condition | Much smaller; only indexes matching rows | | GIN | JSONB containment, full-text search, arrays | Fast reads, slower writes, larger index | | GiST | Geometric and range types, nearest-neighbour | Smaller and faster to write than GIN | | BRIN | Very large tables with natural physical ordering | Tiny; excellent for append-only time-series |

BRIN is the most under-used of these. On an append-only table where rows arrive in timestamp order, a BRIN index on the timestamp can be a tiny fraction of an equivalent B-Tree's size while still eliminating most of the table from a range scan.

Partial Indexes

If a query always filters on a condition, the index only needs to contain matching rows:

-- Indexes every row, including ones you never query
CREATE INDEX idx_users_email ON users(email);

-- Indexes only the rows the query actually touches
CREATE INDEX idx_active_users_email
ON users(email)
WHERE is_active = true;

When the filtered subset is a small fraction of the table, the index is correspondingly smaller — which means faster writes, less memory to cache it, and a better chance it stays resident.

The catch: the planner only uses a partial index when it can prove the query's WHERE clause implies the index predicate. A query without is_active = true won't use it.

Composite Index Column Order

This is the most common indexing mistake:

-- Wrong order for a `user_id = ? AND created_at > ?` workload
CREATE INDEX idx_orders_bad ON orders(created_at, user_id);

-- Correct order
CREATE INDEX idx_orders_good ON orders(user_id, created_at DESC);

The rule: put equality conditions before range conditions. An index is sorted by its first column, then by the second within each first-column value. Leading with a range condition means the second column isn't usefully sorted for your filter.

A useful corollary: an index on (user_id, created_at) also serves queries filtering on user_id alone — a leading subset of the columns works. The reverse isn't true. That means you often need fewer indexes than you'd think.

Expression Indexes

A function call on a column defeats a plain index:

-- Can't use an index on email
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Now it can
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

The expression in the index must match the query's expression exactly. Alternatively, use the citext type and avoid the problem.

JSONB Indexing

-- Index a JSONB column for containment queries
CREATE INDEX idx_metadata_gin
ON products USING GIN (metadata jsonb_path_ops);

-- The kind of query it accelerates
SELECT * FROM products
WHERE metadata @> '{"tags": ["urgent"]}';

jsonb_path_ops produces a smaller index than the default jsonb_ops and is faster for containment (@>) queries — at the cost of not supporting key-existence operators (?, ?|, ?&). Pick based on which operators you actually use.

GIN vs GiST for JSONB: GIN gives faster lookups with slower writes and a larger index; GiST is the reverse. Read-heavy workloads generally favour GIN.

Unused Indexes Are Not Free

Adding indexes speculatively is a reliable way to make a database slower. Every index must be updated on every INSERT, UPDATE, and DELETE, and it consumes memory that could be caching data.

Find indexes nothing is using:

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Drop them without locking the table:

DROP INDEX CONCURRENTLY idx_unused_name;

Always use CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY in production. The non-concurrent forms take locks that block writes for the duration.

2. Query Optimization

The N+1 Pattern

The most common application-level database problem, and it's invisible in the database logs — each individual query is fast:

// N+1: one query for the list, then one per row
const users = await db.query('SELECT * FROM users LIMIT 100');

for (const user of users.rows) {
    user.orders = await db.query(
        'SELECT * FROM orders WHERE user_id = $1',
        [user.id]
    );
}
// 101 round trips

// One query, one round trip
const result = await db.query(`
    SELECT
        u.id, u.name, u.email,
        json_agg(
            json_build_object(
                'id', o.id,
                'total', o.total,
                'status', o.status
            )
        ) as orders
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = ANY($1)
    GROUP BY u.id, u.name, u.email
`, [userIds]);

The cost isn't query execution — it's network round trips. A hundred 2ms queries with 3ms of latency each is far worse than one 20ms query. This is why N+1 problems get dramatically worse when you move the database to a different host.

EXISTS vs COUNT

If you only need to know whether a row exists, don't count them all:

-- Counts every matching row before comparing
SELECT u.id, u.name
FROM users u
WHERE (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 0;

-- Stops at the first match
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders WHERE user_id = u.id
);

EXISTS short-circuits. COUNT(*) > 0 cannot — it must scan every matching row to produce the count, then compare. The difference grows with the number of matches per user.

Window Functions Over Correlated Subqueries

Getting each user's most recent orders with a correlated subquery runs the subquery once per row:

-- One subquery per user row
SELECT
    u.id,
    u.name,
    (
        SELECT json_agg(o.*)
        FROM (
            SELECT * FROM orders
            WHERE user_id = u.id
            ORDER BY created_at DESC
            LIMIT 3
        ) o
    ) as recent_orders
FROM users u;

A window function computes the ranking in a single pass:

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY created_at DESC
        ) as rn
    FROM orders o
)
SELECT
    u.id,
    u.name,
    json_agg(ro.*) FILTER (WHERE ro.rn <= 3) as recent_orders
FROM users u
LEFT JOIN ranked_orders ro ON u.id = ro.user_id AND ro.rn <= 3
GROUP BY u.id, u.name;

Look for "loops=N" with a large N in your EXPLAIN ANALYZE output — that's the signature of a correlated subquery running per row.

3. Connection Pooling

Why Connections Are Expensive

PostgreSQL forks a process per connection. Each one carries memory overhead, and the connection count is capped by max_connections. It's easy to exceed it without noticing:

ERROR: remaining connection slots are reserved for non-replication superuser connections

The arithmetic that causes this: 20 application instances, each with a pool of 20 connections, is 400 connections demanded from a database configured for 100.

PgBouncer

PgBouncer sits between your application and PostgreSQL, accepting many client connections and multiplexing them onto few database connections:

[databases]
myapp_prod = host=db.internal port=5432 dbname=myapp user=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000   # Accept many clients
default_pool_size = 25   # Multiplex onto few backends
min_pool_size = 10
reserve_pool_size = 5

pool_mode = transaction is the setting that matters. A connection returns to the pool at the end of each transaction rather than being held for the client's whole session, which is what makes 2000 clients over 25 backends possible.

Pool Modes

| Pool mode | Reuse | Constraint | |-----------|-------|------------| | Session | Connection held for the client's whole session | Little multiplexing benefit; safest | | Transaction | Returned after each transaction | No session state: prepared statements, SET, advisory locks, LISTEN/NOTIFY | | Statement | Returned after each statement | Multi-statement transactions are impossible |

Transaction pooling is the usual production choice, but the constraint is real — session-level features break silently. Many client libraries use prepared statements by default and need explicit configuration to work behind transaction pooling.

4. Table Partitioning

When a table grows to the point where its indexes no longer fit comfortably in memory, range partitioning splits it into physically separate child tables:

CREATE TABLE events_new (
    id BIGSERIAL,
    user_id INTEGER NOT NULL,
    event_type VARCHAR(50),
    created_at TIMESTAMP NOT NULL,
    data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_01 PARTITION OF events_new
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events_new
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE events_2025_03 PARTITION OF events_new
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Each partition gets its own index
CREATE INDEX idx_events_2025_01_user_date ON events_2025_01(user_id, created_at);
CREATE INDEX idx_events_2025_02_user_date ON events_2025_02(user_id, created_at);
CREATE INDEX idx_events_2025_03_user_date ON events_2025_03(user_id, created_at);

How it helps: a query filtering on created_at triggers partition pruning — PostgreSQL skips partitions that can't contain matching rows. A query for last week touches one partition rather than the whole table, and that partition's index is small enough to stay cached.

Verify pruning is happening. Run EXPLAIN and confirm only the expected partitions appear in the plan. If your query doesn't filter on the partition key, every partition gets scanned — which is slower than the unpartitioned table, since you've added planning overhead for nothing.

Cheap Archival

Partitioning makes retention almost free:

-- Instant metadata operation — no row-by-row delete
ALTER TABLE events_new DETACH PARTITION events_2024_01;

-- Archive
pg_dump events_2024_01 > events_2024_01.sql

-- Drop
DROP TABLE events_2024_01;

DROP TABLE on an old partition is instant and creates no dead tuples. Deleting the equivalent rows from an unpartitioned table would generate millions of dead tuples for autovacuum to clean up. For time-series data with a retention policy, this alone can justify partitioning.

5. VACUUM and Table Bloat

Why Bloat Happens

PostgreSQL's MVCC model doesn't delete rows in place. An UPDATE writes a new row version and marks the old one dead; a DELETE just marks it dead. Dead tuples accumulate until vacuumed, causing slower scans, wasted disk, and poor cache efficiency.

Finding Bloat

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

High-churn tables — sessions, queues, job records — bloat fastest, because every update creates another dead tuple.

Tuning Autovacuum

The real fix is making autovacuum keep up, not periodically cleaning up after it:

ALTER TABLE sessions SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- Vacuum at 5% dead (default: 20%)
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = 10
);

The default scale_factor of 0.20 means a table waits until 20% of it is dead tuples before vacuuming. On a large, high-churn table that's an enormous amount of garbage. Lower it per-table for your worst offenders.

6. Materialized Views

When a dashboard runs the same expensive aggregations on every page load, precompute them:

CREATE MATERIALIZED VIEW dashboard_stats AS
SELECT
    DATE(created_at) as date,
    COUNT(*) as order_count,
    SUM(total) as revenue,
    AVG(total) as avg_order_value,
    COUNT(DISTINCT product_id) as unique_products,
    COUNT(DISTINCT user_id) as unique_users
FROM orders
WHERE created_at >= CURRENT_DATE - 90
GROUP BY DATE(created_at);

-- A unique index is REQUIRED for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_dashboard_stats_date ON dashboard_stats(date);

-- Refresh without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats;

The trade-off is staleness. A materialized view refreshed every 10 minutes serves data up to 10 minutes old. For analytics dashboards that's almost always acceptable; for anything transactional it isn't. Make that call explicitly rather than discovering it in a bug report.

7. Configuration Tuning

PostgreSQL's defaults are conservative — designed to start on almost anything, not to perform on your server. Two of them actively mislead the planner.

The Settings That Matter

# Memory (example: a 16GB server)
shared_buffers = 4GB              # ~25% of RAM
effective_cache_size = 12GB       # ~75% of RAM — planner hint, not an allocation
maintenance_work_mem = 1GB        # For VACUUM, CREATE INDEX
work_mem = 64MB                   # PER OPERATION — see the warning below

# Query planner — critical on SSDs
random_page_cost = 1.1            # Default 4 assumes spinning disks
effective_io_concurrency = 200    # Default 1 assumes a single spindle

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB
max_wal_size = 4GB

# Connections
max_connections = 200             # Keep modest; use PgBouncer

# Observability
log_min_duration_statement = 1000  # Log queries slower than 1s

random_page_cost is the highest-impact one. The default of 4 tells the planner that random reads cost four times a sequential read — true for spinning disks, wildly wrong for SSDs. An over-estimated random cost makes index scans look expensive, so the planner chooses sequential scans instead. You can have perfect indexes and watch PostgreSQL ignore them.

effective_cache_size doesn't allocate anything. It tells the planner how much memory it can assume is available for caching between PostgreSQL and the OS. Set too low, the planner assumes indexes won't be cached and avoids them.

8. Monitoring

You can't optimize what you don't measure. pg_stat_statements is the single most valuable extension for this:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    round(total_exec_time::numeric, 2) as total_time_ms,
    round(mean_exec_time::numeric, 2) as mean_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) as percent_total
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not mean_exec_time. A 5-second query that runs twice a day matters less than a 50ms query that runs a million times. Total time is where your database's capacity actually goes, and it's routinely a query nobody suspected.

Cache Hit Ratio

SELECT
    sum(blks_hit) * 100.0 / (sum(blks_hit) + sum(blks_read)) as cache_hit_ratio
FROM pg_stat_database;

A healthy OLTP database generally sits above 99%. A materially lower ratio suggests shared_buffers is too small for the working set, or that queries are reading far more data than they need.

Connection States

SELECT
    count(*) as total_connections,
    count(*) FILTER (WHERE state = 'active') as active,
    count(*) FILTER (WHERE state = 'idle') as idle,
    count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity;

Watch idle in transaction. A connection sitting in an open transaction holds locks and prevents vacuum from cleaning up dead tuples anywhere in the database — one leaked transaction can bloat tables it never touched. It almost always means application code that opened a transaction and forgot to commit.

9. Batch Operations

Row-by-row inserts are dominated by round-trip latency:

// One round trip per record
for (const record of records) {
    await db.query(
        'INSERT INTO events (user_id, type, data) VALUES ($1, $2, $3)',
        [record.userId, record.type, record.data]
    );
}

Multi-row INSERT collapses that into one:

const values = records.map((r, i) =>
    `($${i*3+1}, $${i*3+2}, $${i*3+3})`
).join(',');

const params = records.flatMap(r => [r.userId, r.type, r.data]);

await db.query(
    `INSERT INTO events (user_id, type, data) VALUES ${values}`,
    params
);

For bulk loading, COPY is faster still — it bypasses much of the per-row overhead:

const { from } = require('pg-copy-streams');
const copyStream = db.query(from('COPY events (user_id, type, data) FROM STDIN CSV'));

const csvData = records.map(r =>
    `${r.userId},${r.type},"${r.data}"`
).join('\n');

copyStream.write(csvData);
copyStream.end();

Note the parameter limit: PostgreSQL caps a single statement at 65535 parameters, so batch inserts need chunking. COPY has no such limit, which is one more reason it's the right tool for genuinely large loads.

10. Read Replicas

When reads dominate and you've exhausted query-level optimization, route them to a replica:

const { Pool } = require('pg');

const primary = new Pool({
    host: 'primary.db.internal',
    database: 'myapp',
    max: 20
});

const replica = new Pool({
    host: 'replica.db.internal',
    database: 'myapp',
    max: 50  // Read-heavy workload
});

async function query(sql, params, { write = false } = {}) {
    const pool = write ? primary : replica;
    return pool.query(sql, params);
}

await query('SELECT * FROM users', [], { write: false });        // Replica
await query('INSERT INTO users ...', [...], { write: true });    // Primary

Replicas also don't help write throughput at all. If writes are your bottleneck, replicas are the wrong tool — look at partitioning, batching, or sharding.

Common Mistakes

Adding Indexes Without Analysis

Adding a dozen indexes based on which columns "look important" reliably makes things worse. Every write must update every index. Use EXPLAIN ANALYZE to find what's actually slow, add the index that fixes it, and verify the plan changed.

Running VACUUM FULL During Business Hours

VACUUM FULL locks the table for the duration of a full rewrite. On a large table that's a self-inflicted outage. Use pg_repack, or schedule a maintenance window.

Setting work_mem Too High

work_mem is per operation. Multiply it by concurrent operations, not connections, and confirm the result fits in RAM.

Trusting Development Performance

A query against 10,000 development rows tells you almost nothing about its behaviour against 10 million production rows. Sequential scans are fast on small tables — the plan will change under real data volume, and usually not in your favour. Test against production-scale data.

When to Reach for What

Rough guidance by table size — treat these as prompts, not rules:

Under ~100k rows per table

• Focus on application-level caching and avoiding N+1 queries
• Skip partitioning and elaborate indexing; sequential scans are genuinely fine

~100k to ~10M rows

• Proper indexes and query optimization become essential
• Consider connection pooling and materialized views

Over ~10M rows

• Everything above, plus partitioning for time-series data
• Consider read replicas and aggressive caching

Over ~100M rows

• All of the above becomes mandatory rather than optional
• Partitioning strategy needs designing up front, not retrofitting
• Dedicated database expertise starts paying for itself

An Ongoing Routine

Optimization isn't a project you finish. A workable cadence:

Weekly — review the slow query log, check table bloat, monitor cache hit ratio

Monthly — analyze query patterns, review index usage, look for missing indexes

Quarterly — capacity planning, configuration review, disaster recovery testing

Resources

Tools

• pg_stat_statements — query performance tracking
• explain.dalibo.com — visualize query plans
• PgBouncer — connection pooling
• pg_repack — bloat removal without long locks
• pgBadger — log analysis

Learning

• Use The Index, Luke — the best free resource on indexing
• PostgreSQL Documentation — the official performance tips
• Postgres Weekly
• Citus Data Blog

Monitoring

• pgwatch2
• pg_stat_monitor

Books

• PostgreSQL: Up and Running by Regina Obe
• The Art of PostgreSQL by Dimitri Fontaine
• PostgreSQL Query Optimization by Henrietta Dombrovskaya

Our Take

The techniques here are ordered roughly by leverage, and that ordering is deliberate: indexing and query rewriting solve the overwhelming majority of PostgreSQL performance problems. Partitioning, replicas, and configuration tuning matter, but they're rarely where the biggest win is hiding.

Start by measuring. Install pg_stat_statements, sort by total execution time, and look at the top entry. It's frequently something nobody suspected — a cheap query called a million times, not the expensive report everyone complains about.

Then fix one thing and measure again. The discipline of changing one variable at a time is what separates optimization from superstition. And check random_page_cost before anything else: if you're running on SSDs with the default of 4, PostgreSQL is quietly ignoring indexes you already have.

For choosing a database in the first place, see our PostgreSQL vs MongoDB vs Redis guide.