PostgreSQL Performance Tuning: Indexing, Vacuum, Partitioning, and pgBadger

PostgreSQL's defaults are safe on any hardware, which also means they leave a lot of a modern server unused. This guide walks through the changes that make the biggest difference, in the order you should try them, with the commands to check each one.

When to tune

Default PostgreSQL config is conservative — safe for any hardware but underuses modern VPS resources. Tune when:

• Queries suddenly slow after table grew past 1M rows
• pg_stat_activity shows many sessions with a wait_event of type Lock
• CPU spikes during business hours
• Disk usage grows much faster than your data (often: bloat from a lagging autovacuum)
• Long-running maintenance tasks blocking writes

If your site is small (<100K rows) and fast, don't tune. Default PG works.

Level 1 — Right-size memory settings

Edit postgresql.conf. On AlmaLinux or Rocky with the PostgreSQL (PGDG) packages it is /var/lib/pgsql/17/data/postgresql.conf; on Ubuntu or Debian it is /etc/postgresql/17/main/postgresql.conf (both examples are for PostgreSQL 17; use your version number). If in doubt, run SHOW config_file; in psql.

Apply and restart:

sudo systemctl restart postgresql

A reload (SELECT pg_reload_conf();) is enough for most settings, but shared_buffers and max_connections need a restart. The pgtune online tool gives a starting config for your RAM and workload.

Level 2 — Indexing strategies

b-tree (default) — equality + range

-- Fast for:
--   WHERE email = ...
--   WHERE created_at > '2026-01-01'
--   ORDER BY price
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created ON orders(created_at);

Composite — multi-column

-- Fast for WHERE status='paid' AND user_id=42 ORDER BY created_at
CREATE INDEX idx_orders_status_user_created
    ON orders(status, user_id, created_at DESC);

Column order matters: columns compared with = first, then the column you filter by range or sort by. An index on (a, b, c) also serves queries on a alone and on a, b, but not on b alone.

Partial — index only relevant rows

Huge win for sparse columns:

-- 95% of orders are 'paid' — only index unusual ones
CREATE INDEX idx_orders_pending
    ON orders(id)
    WHERE status != 'paid';

Index is smaller → faster writes, smaller reads, better cache use.

GIN — full-text search, JSONB, arrays

-- Search JSON metadata
CREATE INDEX idx_products_meta
    ON products USING gin(metadata);

SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';

-- Full-text search
ALTER TABLE articles ADD COLUMN tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_tsv ON articles USING gin(tsv);

SELECT * FROM articles WHERE tsv @@ plainto_tsquery('english', 'hosting tips');

BRIN — huge append-only tables

-- Log table with 100M rows by time
CREATE INDEX idx_logs_created_brin
    ON logs USING brin(created_at);

BRIN index is tiny (KB) vs GB for b-tree. Best when data is physically clustered by the indexed column (append-only).

Covering — index-only scans

-- Query: SELECT id, email FROM users WHERE status = 'active'
CREATE INDEX idx_users_active_covering
    ON users(status) INCLUDE(id, email)
    WHERE status = 'active';

PostgreSQL returns results without touching the table — huge speedup.

Level 3 — EXPLAIN ANALYZE

Before tuning, measure. Always.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.name;

Look for:

• Seq Scan on big tables = missing index
• Nested Loop over many rows = often a bad row estimate; run ANALYZE, and for diagnosis only, test with SET enable_nestloop = off; in your session
• Actual rows >> estimated rows = outdated statistics (run ANALYZE)
• Buffers: shared read=10000 = data not cached; large shared_buffers helps

Visualise plans at explain.depesz.com or pgmustard.com.

Level 4 — Vacuum strategy

PostgreSQL's MVCC keeps "dead tuples" after UPDATE/DELETE. Autovacuum removes them. Without it: bloat grows, queries slow down.

Check bloat:

SELECT schemaname, relname,
       pg_size_pretty(pg_relation_size(relid)) AS size,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If n_dead_tup > 20% of rowcount, autovacuum is behind. Tune:

# postgresql.conf
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05    # vacuum at 5% dead (default 20%)
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000      # aggressive

For high-write tables, set per-table:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

Manual vacuum for recovery:

VACUUM (VERBOSE, ANALYZE) orders;

-- Last resort: rewrites the table and returns disk space,
-- but holds an exclusive lock for the whole run
VACUUM FULL orders;

Don't VACUUM FULL large tables on a live system: the lock blocks reads and writes, possibly for hours. Use pg_repack instead (see the FAQ).

Level 5 — Partitioning huge tables

When a single table > 50M rows, partitioning by time often helps.

-- Range partition by month
CREATE TABLE events (
    id bigserial,
    created_at timestamptz NOT NULL,
    user_id uuid,
    data jsonb
) PARTITION BY RANGE (created_at);

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

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

-- An index on the parent is created on every partition,
-- including partitions added later
CREATE INDEX ON events (user_id);

Use pg_partman extension to auto-create future partitions + drop old ones.

Benefits:

• Query for "last 30 days" hits 1 partition, not all
• DROP TABLE events_2023_01 instantly reclaims old data
• Individual partition vacuum/index maintenance

Level 6 — Connection pooling

Each PostgreSQL connection is a separate server process that costs memory and startup time. Use PgBouncer for pooling:

sudo dnf install pgbouncer      # AlmaLinux/Rocky (PGDG repository)
sudo apt install pgbouncer      # Ubuntu/Debian
# then edit /etc/pgbouncer/pgbouncer.ini

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

The app connects to localhost:6432 instead of :5432: up to 1,000 client connections share 20 real PostgreSQL connections. In transaction mode, session-level features (session SET, advisory locks held across transactions, LISTEN) don't behave as they do on a direct connection, so test your app.

Level 7 — Slow query log + pgBadger

Log slow queries:

# postgresql.conf
log_min_duration_statement = 500  # ms — log queries slower than this
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_statement = 'ddl'
log_duration = off

Analyse logs with pgBadger (HTML reports):

sudo dnf install pgbadger      # or: sudo apt install pgbadger
# Point it at your log directory (Ubuntu: /var/log/postgresql/,
# PGDG on AlmaLinux: /var/lib/pgsql/<version>/data/log/)
pgbadger /var/log/postgresql/postgresql-*.log -o /root/reports/pgbadger.html

Keep the report out of your public web root, because it contains your SQL. Copy it to your computer, or serve it behind authentication. It shows the slowest queries, the most-called ones, peak hours and errors.

Level 8 — Replication + read replicas

For read-heavy apps, add a streaming replica:

# On primary
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# On the primary, also create a replication role and allow it in pg_hba.conf
# On the replica, take a base backup and start:
pg_basebackup -h primary-ip -D /var/lib/pgsql/17/data -U replicator -P -R
sudo systemctl start postgresql

Route read queries to replica in your app:

write_engine = create_engine(PRIMARY_URL)
read_engine = create_engine(REPLICA_URL)
# Use read_engine for SELECTs, write_engine for writes

Common pitfalls

Running this on Domain India

• Query-level work (indexes, EXPLAIN ANALYZE, ANALYZE, rewriting slow queries) needs only a normal database login, so it applies wherever your PostgreSQL database runs.
• Server-level settings (postgresql.conf, autovacuum workers, PgBouncer, pgBadger, replicas) need root on the server. A Domain India VPS is self-managed with full root access, so all of this guide applies there. VPS plans include no backups or snapshots, so schedule your own pg_dump or pg_basebackup copies to storage outside the server.
• The App Platform includes a PostgreSQL database on every plan. Treat it as managed: the query-level advice applies, but don't assume you can change server settings or install extensions; ask support what is possible.

Prices and plans are on the VPS page (from ₹553 a month, excluding 18% GST) and the App Platform page.

FAQ

Ready to tune your own server? Compare VPS plans, or open a support ticket with questions about a plan; tickets get a first response within 15 minutes.