Why should you care?

A bank PostgresVerse cluster must hit SLA during month-end batch and daytime OLTP — optimization is capacity planning plus SQL discipline.

See it live — copy this example

Run in pgAdmin or psql.

-- Hot table tuning snapshot
ALTER TABLE ledger_entries SET (
  autovacuum_vacuum_scale_factor = 0.02,
  fillfactor = 90
);
CREATE INDEX CONCURRENTLY idx_ledger_acct_date
ON ledger_entries (account_id, posted_at DESC);

What happened?

• Lower scale_factor vacuums ledger more often.
• fillfactor 90 leaves page room for HOT updates.
• CONCURRENTLY builds index without blocking writes — standard for prod.

Practice next

1. Identify top queries from pg_stat_statements.
2. Map each to index or partition change.
3. Apply CONCURRENTLY index on staging, measure.
4. Create monthly partition on ledger_entries for partition pruning.
5. Materialize nightly summary with REFRESH MATERIALIZED VIEW CONCURRENTLY.

Remember

Measure statements, locks, and I/O first. Tune autovacuum per table not only globally. Separate OLTP, batch, and analytics paths.

PostgresVerse month-end SLA

Finance batch moved to replica; primary indexes tuned for daytime transfers.

Outcome: Both workloads meet SLA without doubling cluster size.