---
title: "PostgreSQL Performance Tuning"
description: "Reading EXPLAIN ANALYZE output, choosing the right index type, using pg_stat_statements, configuring PgBouncer, and tuning autovacuum for sustained PostgreSQL performance."
url: https://agent-zone.ai/knowledge/databases/postgres-performance-tuning/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["postgresql","performance","indexing","explain-analyze","pgbouncer","vacuum","autovacuum"]
skills: ["postgres-performance","query-optimization","index-selection","connection-pooling"]
tools: ["postgresql","psql","pgbouncer","pg_stat_statements"]
levels: ["intermediate"]
word_count: 573
formats:
  json: https://agent-zone.ai/knowledge/databases/postgres-performance-tuning/index.json
  html: https://agent-zone.ai/knowledge/databases/postgres-performance-tuning/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=PostgreSQL+Performance+Tuning
---


# PostgreSQL Performance Tuning

Most PostgreSQL performance problems come from missing indexes, bad query plans, connection overhead, or table bloat. This covers how to diagnose each one.

## Reading EXPLAIN ANALYZE

`EXPLAIN` shows the query plan. `EXPLAIN ANALYZE` actually executes the query and shows real timings.

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
```

```
Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=1 width=120) (actual time=0.023..0.025 rows=3 loops=1)
  Index Cond: (customer_id = 42)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 12
Planning Time: 0.152 ms
Execution Time: 0.048 ms
```

What to look for: **Seq Scan on large tables** means a missing index. **Rows Removed by Filter** means the index fetched extra rows that a composite index would eliminate. **actual rows far from estimated rows** means stale statistics -- run `ANALYZE tablename;`. **Nested Loop with high loops count** usually wants a hash join; check the inner table's indexes.

For destructive statements, wrap in a transaction:

```sql
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM orders WHERE created_at < '2020-01-01';
ROLLBACK;
```

## Common Slow Query Patterns

**Functions on indexed columns:** `WHERE LOWER(email) = 'user@example.com'` cannot use a B-tree index on `email`. Fix: `CREATE INDEX idx_email_lower ON users (LOWER(email));`

**Large IN lists:** `WHERE id IN (1, 2, ..., 10000)` generates huge plan trees. Use `WHERE id = ANY(ARRAY[...])` or a temp table join.

**SELECT * when you need 2 columns:** Forces full row reads. A covering index can serve the query from the index alone.

## Index Types

**B-tree** (default) -- Equality and range queries. Covers `=`, `<`, `>`, `BETWEEN`, `IN`. Right choice 90% of the time.

```sql
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);
```

**GIN** -- Full-text search, JSONB containment, array membership.

```sql
CREATE INDEX idx_data_jsonb ON events USING gin (metadata jsonb_path_ops);
```

**GiST** -- Geometric data, range types, nearest-neighbor queries.

**BRIN** -- Very large append-only tables where the indexed column correlates with physical order (like timestamps). Tiny index size, useless if values are randomly distributed.

```sql
CREATE INDEX idx_events_created ON events USING brin (created_at);
```

## pg_stat_statements

The most important tool for finding slow queries in production. Enable it:

```
shared_preload_libraries = 'pg_stat_statements'
```

After restart: `CREATE EXTENSION pg_stat_statements;`

Find top queries by total time:

```sql
SELECT calls, round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  substr(query, 1, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
```

A 5 ms query called 2 million times consumes more than a 500 ms query called 100 times. Optimize by total_exec_time, not mean.

## Connection Pooling with PgBouncer

Each PostgreSQL connection is a process using 5-10 MB. PgBouncer multiplexes many client connections onto a small server pool.

```ini
# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
```

Use `pool_mode = transaction` for best multiplexing -- the server connection returns to the pool after each transaction. Applications connect on port 6432 instead of 5432.

## Vacuum and Autovacuum Tuning

MVCC means updates and deletes leave dead tuples. VACUUM reclaims this space. Default autovacuum settings are conservative.

Check bloat:

```sql
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
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
```

If `dead_pct` exceeds 20% on large tables, tune autovacuum:

```
autovacuum_vacuum_scale_factor = 0.02    # trigger at 2% dead instead of 20%
autovacuum_max_workers = 5               # default 3
autovacuum_naptime = 15s                 # default 1min
```

Per-table override for high-churn tables:

```sql
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01);
```

Never run `VACUUM FULL` during business hours -- it takes an `ACCESS EXCLUSIVE` lock, blocking all reads and writes.