Getting PostgreSQL ready for Black Friday: a SQL-first checklist

A traffic peak means more connections, more sessions after the same rows and more cleanup work for PostgreSQL. Here is which limits break first, when throwing a bigger server at the problem does not help, and how to check every one of them with plain SQL - before Black Friday, not during it.

TL;DR

• Peaks rarely invent new PostgreSQL problems. They surface limits that were already there: a connection budget, a long-running transaction, a cleanup threshold.
• A bigger box helps when you are short on CPU, memory or disk speed. It helps little when sessions wait for each other, and not at all when they wait on a limit that is a setting. For some workloads a bigger machine really is enough - the checks below tell you if yours is one of them.
• Connections are usually the first thing to break. Bumping max_connections is rarely the fix; a calculated pool size plus a properly configured connection pooler usually is.
• Locks come right after. Under load, one hot row, one schema change or one forgotten open transaction can stall hundreds of sessions.
• shared_buffers, max_connections, max_locks_per_transaction and minor version upgrades all need a restart. Get them done before your change freeze.
• Every check is a psql query. On the day, four queries tell you most of what is happening.

The short version

More hardware fixes hardware shortages, and sometimes that is all you need. To know whether it is enough for you, review five areas before the peak: connections, locks, autovacuum, the WAL and memory headroom. Ship every restart-only change before the change freeze and test the app with it. In a quiet hour before the freeze, refresh planner statistics and vacuum the tables that need it. No schema changes during the peak. On the day, keep four queries at hand: connections by state, what active sessions wait for, who blocks whom, and the oldest open transactions.

Weeks before the peak

• Configure the pooler and load-test the app through it
• Drop the invalid and duplicate indexes you have verified
• Measure how fast the transaction ID age of your tables grows

Before your change freeze

• Every change that needs a restart
• Timeouts for the application role, and log_lock_waits on
• In a quiet hour: ANALYZE, VACUUM where needed, VACUUM (FREEZE) on the oldest tables

On the day

• The four queries, run on demand
• SHOW POOLS, if you run PgBouncer
• No schema changes and no statistics reset

A change freeze takes restarts off the table, which is why restart-only changes come first.

What a peak does to your database

Every customer request ends up as work in some database session. On a regular day most sessions finish before anyone else needs the same row, lock or CPU core. During a peak they overlap, and three things grow together:

• connection count - the platform runs more copies of the app (servers, containers, pods), and each copy opens its own connections;
• contention for the same data - think of the stock count of the product on your front page;
• update and delete rate - which means more dead rows for autovacuum and more WAL for checkpoints.

So readiness is not just a settings review. Settings matter, but so does how long your transactions stay open, and what else runs on the box during the peak: migrations, reports, backups, bulk imports.

"Let's just get a bigger server"

Moving the database to a larger machine - more cores, more RAM, faster disks - is a classic pre-Black-Friday move. It is reasonable, and for some workloads it is enough. It works when the database is genuinely short of hardware: queries saturating every core, a working set that no longer fits in memory, disks that cannot keep up.

It does little when sessions wait for each other, and nothing when they wait on a setting rather than hardware. Four examples you will meet again below:

• Hot row. When many orders update the same product's stock count, each update waits "for the first updating transaction to commit or roll back". More cores do not shorten that queue. Faster hardware only shortens the in-database part of each transaction, not the time the app keeps it open.
• Forgotten open transaction. A session that is idle in transaction keeps its locks until the app ends the transaction, and if it modified data, it also blocks cleanup. The server is doing nothing for that session meanwhile, so hardware is irrelevant.
• Connection limit. max_connections is a setting. On a self-managed server, a box with twice the RAM refuses the next connection at the exact same number.
• Cleanup speed. Autovacuum sleeps after a set amount of work, and both the amount and the sleep length are settings. Once disks are fast enough, those settings - not the disks - cap cleanup speed.

Three orders, one product, one stock-count row Today Order 1 [UPDATE][ app work, tx open ][COMMIT] Order 2 [....... waiting for the row .......][UPDATE][ app work ][COMMIT] Order 3 [.................. waiting for the row ...................][UPDATE]... Faster CPUs Order 1 [UPD][ app work, tx open ][C] Order 2 [..... waiting for the row ....][UPD][ app work ][C] Order 3 [............. waiting for the row ..............][UPD]... time → 

Each order waits for the previous one to commit. More cores do not change the queue; faster CPUs only shrink the database part, not the time the app holds the transaction. Illustration, not a benchmark.

Also note: on a self-managed server, PostgreSQL does not retune itself on bigger hardware. The OS will use the extra RAM to cache data files, but shared_buffers, work_mem and max_connections stay where they were. Some managed services differ - Amazon RDS for PostgreSQL, for instance, computes its default max_connections from the memory of the instance class. Verify with SHOW after the move. The move itself usually means a restart or failover, so it belongs before the change freeze with the other restart-only changes.

How do you know which case you are in? Look at what active sessions wait for during your busiest hour today, or during a load test - query #2 in the "on the day" section at the end. An empty wait_event_type usually means on-CPU or waiting for a CPU; IO means waiting on disk. A bigger server helps with both. Lock means waiting for another transaction to finish, and a bigger server only helps as much as it speeds up that other transaction. LWLock waits deserve a closer look - see the locks section. If you would rather know before the peak than on the day, read on.

All SQL below can be run from psql or any SQL client you like. The checks are read-only and work with the built-in pg_monitor role. Anything that changes state is called out and needs a superuser or the object owner.

Connections break first

PostgreSQL forks a separate server process for every connection. Three consequences for a peak:

1. The ceiling is fixed. max_connections changes only with a restart. Once all slots are used, the next connection gets "sorry, too many clients already" and that customer's request fails. Connecting is not free either - a new process plus authentication before the first query - so connect-per-request apps pay that cost every time.

2. Growth comes from the app side. Each running app instance - server, container, pod - has its own pool. When the platform scales out for the peak, every new instance brings a new pool. Do the worst-case math up front:

largest number of application copies running at once x connections in each copy's pool + background workers, scheduled jobs and other services that connect directly + your own monitoring and administration tools = the number that must stay below max_connections, minus the slots reserved for administrators 

When the database slows down, requests queue for a pool connection. If the app retries failures instantly, retries add load at exactly the wrong time.

3. More connections ≠ more throughput. A running query needs a core. With far more active sessions than cores, they wait for each other - partly inside PostgreSQL, on internal locks protecting shared memory. In a pgsql-admin thread about a load test where active sessions climbed while CPUs stayed mostly idle, Michael Vitale summed it up as "a bunch of concurrent active connections contending over the same resources". Even idle connections cost something: PostgreSQL committer Andres Freund measured that "a large number of connections clearly reduce the efficiency of other connections, even when idle". That was 2020; his improvements landed in PostgreSQL 14.

Fix: a pooler, configured for real

Put a pooler like PgBouncer between the app and PostgreSQL - and configure it. Out of the box it will not do what you need. PgBouncer's default pool_mode is session, where a server connection "is released back to pool after client disconnects". App-side pools keep connections open, so in session mode each client connection pins a server connection for its whole lifetime, and nothing gets shared.

Transaction mode is where sharing happens: "a server connection is assigned to a client only during a transaction". Many client connections share far fewer server connections, and excess requests wait in the pooler, not inside the database.

Only transaction mode actually shares server connections.

Check these pgbouncer.ini settings against your numbers before the peak (defaults from PgBouncer's configuration reference):

• pool_mode - transaction, as explained above.
• default_pool_size (default 20) - per database+user pair, not per pooler. Multiply by the number of pairs, and sum across all PgBouncer instances. That total, plus every connection that bypasses the pooler, must stay under max_connections minus admin-reserved slots. max_db_connections and max_user_connections cap per-database and per-user totals; both are unlimited by default.
• max_client_conn (default 100) - how many client connections PgBouncer accepts. Size it from your worst case. Raising it may also require raising the OS open-files limit.
• reserve_pool_size (default 0, i.e. off) - extra server connections for a pool once a client has waited longer than reserve_pool_timeout (default 5 s). Count these in the total.
• query_wait_timeout (default 120 s) - how long a query may wait for a server connection before PgBouncer drops the client. Decide whether your app should wait that long or fail fast and retry.
• max_prepared_statements - must be above 0 for protocol-level prepared statements to work in transaction mode. Some drivers use them automatically, e.g. the PostgreSQL JDBC driver after the app runs the same prepared statement five times. Default is 200 since PgBouncer 1.24.0 (January 2025), 0 before - so check what version your package ships.

PgBouncer is "single-threaded and uses one CPU core per instance". At high request rates, watch the CPU of the PgBouncer process itself; the docs explain running several instances on one port with so_reuseport.

Transaction mode does not keep session state. PgBouncer lists SET and RESET, LISTEN, PREPARE and DEALLOCATE, session-level advisory locks and temp tables that outlive a transaction among features it does not support there. If your app relies on any of these, change it, or route that part through a database entry with its own pool_mode set to session. Load-test the app with the pooler and final settings before the freeze - not in peak week.

Set each app instance's pool size on purpose and cap how many instances the platform may spin up. Without a pooler, the sum above must stay under max_connections minus admin slots; with one, under max_client_conn. If you do raise max_connections, remember every extra session can allocate memory for sorts and hashes (see the memory section) - check your memory headroom first.

Leave yourself a way in. When the app grabs every slot, PostgreSQL refuses you too - unless a slot is reserved. superuser_reserved_connections (default 3) keeps slots that only superusers can use. If you debug incidents as a superuser, you already have them - as long as the app itself does not connect as a superuser.

Debugging with a non-superuser account? PostgreSQL 16+ can reserve slots for it via reserved_connections (default 0), for roles with the privileges of pg_use_reserved_connections. As a superuser:

ALTER SYSTEM SET reserved_connections = 2; -- takes effect after a restart GRANT pg_use_reserved_connections TO oncall; 

2 and oncall are examples. This needs a restart, and each reserved slot comes out of max_connections - one fewer for the app.

Check it

SHOW max_connections; SHOW superuser_reserved_connections; SHOW reserved_connections; SELECT count(*) AS connections, count(*) FILTER (WHERE state = 'active') AS active, count(*) FILTER (WHERE state LIKE 'idle in transaction%') AS idle_in_transaction FROM pg_stat_activity WHERE backend_type = 'client backend'; 

Run the SELECT at today's busiest hour. Compare connections to max_connections minus reserved slots, and active to the server's core count. Then ask: how much does each number grow when traffic multiplies?

On PgBouncer, check the pooler too. Its admin console is a virtual database called pgbouncer, open to users in admin_users or stats_users. From a shell, e.g. psql -p 6432 pgbouncer (6432 is the default port):

SHOW CONFIG; SHOW POOLS; 

SHOW CONFIG shows the values in effect. In SHOW POOLS, cl_waiting counts clients that sent a query but have no server connection yet, and maxwait is how long the oldest has waited. The docs on maxwait: "If this starts increasing, then the current pool of servers does not handle requests quickly enough."

Then come the locks

Locks stop two sessions from making conflicting changes to the same data. Most are held for milliseconds and nobody notices - until a peak requests the same locks many times per second and every long-held lock grows a queue. Five patterns account for most of it.

Hot row. An UPDATE locks its row until the transaction ends. If every order touches the same row - the stock count of the sale product - orders run in series, not in parallel. In pg_stat_activity the waiters show wait_event_type Lock with wait_event transactionid, "Waiting for a transaction to finish", or tuple. No knob fixes this; the transaction design does. Touch the hot row as late as possible and commit right after. Never wait on anything external - a payment provider, say - while holding that row.

Schema change behind a long query. Many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, and the docs are precise: "Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement." Lock requests queue. While an ALTER TABLE waits for a long transaction, every new query on the table - plain SELECTs too - queues behind it. As Laurenz Albe put it on pgsql-admin, the ACCESS EXCLUSIVE lock is "waiting in line and blocking everything else (locks have to queue)". Keep migrations out of the peak. When you do run one, set a short lock_timeout first so it fails rather than blocks everyone, and retry later:

SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN gift_note text; 

The timeout and column are examples. Create indexes with CREATE INDEX CONCURRENTLY, which does not block writes. If it fails, it leaves an invalid index that "will still consume update overhead" until dropped.

Lock queue on one table Without lock_timeout SELECT (long report, holds lock) ← ALTER TABLE waits for ACCESS EXCLUSIVE ← SELECT waits ← UPDATE waits ← SELECT waits With SET lock_timeout = '2s' before ALTER TABLE SELECT (long report, holds lock) ← ALTER TABLE gives up after 2 s ← SELECT runs ← UPDATE runs ← SELECT runs 

With lock_timeout, the schema change bails out after 2 seconds, the queue behind it drains, and you try again later.

Forgotten open transaction. An idle-in-transaction session keeps all its locks for as long as the app leaves the transaction open. The docs pitch idle_in_transaction_session_timeout as a way "to ensure that idle sessions do not hold locks for an unreasonable amount of time". Set it, and statement_timeout, on your app's role rather than server-wide - the docs advise against statement_timeout in postgresql.conf "because it would affect all sessions". As a superuser:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s'; ALTER ROLE app_user SET statement_timeout = '30s'; 

Role name and values are examples. Choose a statement_timeout above your slowest legitimate request, and make sure the app copes with a cancelled statement and a closed session. Role settings only reach new sessions, so pooled connections keep the old values until they reconnect.

Inconsistent lock order. PostgreSQL detects deadlocks and resolves them "by aborting one of the transactions involved"; the log says deadlock detected. An ordering that rarely collides on a normal day collides much more under peak load. The fix lives in the app: the docs recommend that "all applications using a database acquire locks on multiple objects in a consistent order".

Waiting inside PostgreSQL. Not every wait is on your data. wait_event_type LWLock is a lightweight lock guarding a shared-memory structure. Lots of these at peak mean too many active sessions competing for the same internals - the fix is fewer active sessions (hello, pooler), not more. One has its own cause: LockManager. Each session can record a small number of regular table/index locks in a fast private area; beyond that it falls back to the shared lock table, which the commit enlarging the fast area calls "considerably more expensive, and may be subject to contention (especially on many-core systems)". Queries over many partitions or indexes get there quickly. In PostgreSQL 18 the fast area size follows max_locks_per_transaction (default 64), which needs a restart. If load tests show many LockManager waits, schedule that restart before the freeze.

Check it

Enable lock-wait logging before the peak. log_lock_waits is off by default. With it on, every lock wait longer than deadlock_timeout (default 1 s) gets logged, with the holder and the queue. After the peak, that is your audit trail of who blocked whom. As a superuser:

ALTER SYSTEM SET log_lock_waits = on; SELECT pg_reload_conf(); 

The live who-blocks-whom query is in the "on the day" section.

Can autovacuum keep pace?

Every UPDATE and DELETE leaves a dead row version behind; autovacuum reclaims that space. At peak, dead rows accumulate faster. Three factors decide whether cleanup keeps up.

When a table qualifies. By default, after 50 rows + 20% of the table have been updated or deleted. PostgreSQL 18 adds a cap, autovacuum_vacuum_max_threshold, at 100 million rows. For big, busy tables both are too late. Tune just that table, not the whole server:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01); 

Table and value are examples. This takes a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes, and autovacuum uses the new value on its next check.

How fast it runs. Autovacuum pauses regularly to avoid hogging the disks; autovacuum_vacuum_cost_limit sets how much work happens between pauses. The default -1 means "use vacuum_cost_limit", i.e. 200. And that budget is shared - per the docs, it "is distributed proportionally among the running autovacuum workers, if there is more than one, so that the sum of the limits for each worker does not exceed the value of this variable". More workers without a higher cost limit = more tables in parallel, each slower. If several tables lag, raise the cost limit; raising only the worker count will not help. Extra cleanup competes with customer traffic for disk, so change it before the peak and watch disk afterwards. Both settings reload without restart in PostgreSQL 18. autovacuum_max_workers can go up to autovacuum_worker_slots (default 16), which needs a restart.

One shared budget: more workers, more tables at once, each one slower.

Whether it can remove anything. Dead rows that an open transaction might still see cannot be removed. The docs: "an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction". An hour-long report during the peak blocks cleanup on every table in its database for that hour, no matter how fast autovacuum is. Same for a data-modifying transaction left idle in transaction. A replication slot, or a replica reporting its queries back to the primary (hot_standby_feedback), can block cleanup server-wide.

Check it

Dead rows and last cleanup per table (pg_stat_user_tables is per database, so run it in each one):

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20; 

Dead row counts are estimates. Start with large tables near the top whose last_autovacuum is old or empty. Then the server-wide open transactions holding cleanup back, oldest first:

SELECT pid, datname, usename, application_name, state, now() - xact_start AS transaction_age, left(query, 60) AS query FROM pg_stat_activity WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC LIMIT 10; 

And replication slots:

SELECT slot_name, active, age(xmin) AS xmin_age FROM pg_replication_slots; 

Transactions open for hours hold back cleanup, and so do slots with a large xmin_age. Inactive slots are the usual culprit - nothing advances them.

Wraparound vacuum at the worst possible moment

Every writing transaction consumes a transaction ID, and PostgreSQL must periodically freeze old rows so IDs can be reused. Once a table's oldest unfrozen XID is older than autovacuum_freeze_max_age (default 200 million), autovacuum runs a "to prevent wraparound" vacuum on it - even if autovacuum is otherwise disabled. Unlike regular autovacuum, the docs say, this one "is not automatically interrupted" when another command wants a conflicting lock. On a big table it can read most of the table and generate lots of WAL, and any schema change on that table waits for it - with the lock queue piling up behind.

Peaks consume XIDs faster than normal days. If a big table will hit that age mid-peak, do the work yourself beforehand in a quiet hour. Oldest tables per database:

SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age, mxid_age(c.relminmxid) AS multixact_age, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c WHERE c.relkind IN ('r', 'm') ORDER BY age(c.relfrozenxid) DESC LIMIT 20; 

Run it twice, a day apart, to get the growth rate, and compare it with the headroom to autovacuum_freeze_max_age. multixact_age works the same way with its own limit (below). For tables that will cross either limit during the peak, run VACUUM (FREEZE, VERBOSE) before the change freeze, as owner or superuser. It is I/O-heavy - pick a quiet hour.

Server-wide distance to the hard limit, for both XIDs and multixact IDs (used when multiple transactions lock the same row):

SELECT datname, age(datfrozenxid) AS xid_age, mxid_age(datminmxid) AS multixact_age FROM pg_database ORDER BY xid_age DESC; 

autovacuum_multixact_freeze_max_age (default 400 million) is the multixact equivalent. Ignore these long enough and the server "will refuse to assign new XIDs once there are fewer than three million transactions left until wraparound" - read-only transactions only. Not a number you want to meet during a peak.

Should you VACUUM and ANALYZE by hand first?

Yes - in a quiet hour a few days before the freeze - but treat them differently. A manual run clears accumulated dead rows, refreshes planner stats and updates the visibility map, which lets PostgreSQL "answer some queries using only the index, without reference to the underlying table". Since a table qualifies for autovacuum when its "tuples obsoleted since the last VACUUM" cross the threshold, a freshly vacuumed table enters the peak with a low count.

Database-wide ANALYZE is cheap. "For large tables, ANALYZE takes a random sample of the table contents, rather than examining every row", and it "can run in parallel with other non-DDL activity on the table". New stats can change plans, so run it early enough to see the effect before the freeze - not the night before.

Database-wide VACUUM is expensive. Three things to weigh:

• It runs flat out. Autovacuum throttles; a manual VACUUM does not - vacuum_cost_delay is 0 by default, "which disables the cost-based vacuum delay feature". Great in a quiet hour, bad if it spills into busy hours.
• It takes as long as your biggest tables, and changed pages go to the WAL too. Never run it on this database? Then you do not know how long it takes. Watch the pg_wal disk and pg_stat_progress_vacuum while it runs.
• It cannot remove rows an open transaction still holds back. Check the oldest transactions and replication slots first (autovacuum section).

So: refresh stats everywhere, vacuum where it matters - the top of the dead-row list and the tables closest to autovacuum_freeze_max_age. vacuumdb, shipped with PostgreSQL, handles both from a shell. As a superuser:

vacuumdb --all --analyze-only vacuumdb --all --freeze --min-xid-age=100000000 vacuumdb --dbname=shop --table=orders --analyze 

Line one refreshes stats in every database. Line two vacuums and freezes only tables with XID age ≥ 100 million. Line three vacuums and analyzes one table. Database, table and age are examples. If a previous quiet hour proved the whole database fits the window, vacuumdb --all --analyze does it all at once.

Do not run VACUUM FULL before the peak. It "rewrites the entire contents of the table into a new disk file" and "requires an ACCESS EXCLUSIVE lock on each table while it is being processed" - every query on the table waits, and it needs space for a full second copy. Plain VACUUM runs alongside reads and writes, locking only briefly at the end, and only if it can hand empty trailing pages back to the OS.

WAL sizing for peak write volume

Every change hits the WAL first. Checkpoints periodically flush changed data to data files so older WAL is no longer needed for crash recovery. Per the docs, "A checkpoint is begun every checkpoint_timeout seconds, or if max_wal_size is about to be exceeded, whichever comes first." At peak the WAL grows faster, so a max_wal_size that was fine yesterday can force early checkpoints. Each one costs extra writes, since with the default full_page_writes "the first modification of a data page after each checkpoint results in logging the entire page content".

Check it

SELECT num_timed, num_requested, num_done, stats_reset FROM pg_stat_checkpointer; 

num_timed = timer-triggered checkpoints, num_requested = requested for other reasons. In PostgreSQL 18 both also count skipped checkpoints; num_done counts those actually performed. Counters are server-wide and cumulative since stats_reset, so snapshot right before and right after your busiest hour and diff them. PostgreSQL 17 has no num_done; 16 and older use checkpoints_timed and checkpoints_req in pg_stat_bgwriter.

Requested checkpoints are not only about WAL size - a manual CHECKPOINT or a backup start counts too. Confirm in the server log: log_checkpoints is on by default since PostgreSQL 15, and checkpoint starting: wal means WAL size. If they come too close together you will also see checkpoints are occurring too frequently with a hint to raise max_wal_size.

Fix

max_wal_size reloads without a restart - no maintenance window needed. Check free space on the pg_wal disk first, because the limit is soft: "max_wal_size is never a hard limit anyway, so you should always leave plenty of headroom to avoid running out of disk space." A higher value also raises worst-case disk usage and crash recovery time. We walk through the measurement and the change step by step in max_wal_size, explained.

Memory headroom for all those sessions

shared_buffers (PostgreSQL's own cache) only changes with a restart. work_mem is different - it limits each sort or hash operation, not each session. The docs explain the catch: "a complex query might perform several sort and hash operations at the same time", and "several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem". Hashes can use work_mem × hash_mem_multiplier (default 2). Parallel queries add more, as each worker gets its own work_mem. A peak multiplies how many sessions do this concurrently.

If a Linux box runs out of memory, the kernel may kill a PostgreSQL process. If it kills the postmaster, the docs describe the aftermath: "Although existing database connections will continue to function normally, no new connections will be accepted. To recover, PostgreSQL will need to be restarted."

Fix

Do not bump work_mem server-wide in the week before the peak. If a known report or job needs more, set it per session or per role. To see whether sorts and hashes spill to temp files today:

SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size, stats_reset FROM pg_stat_database WHERE datname IS NOT NULL; 

These counters are cumulative too, so diff two readings. log_temp_files logs every temp file above a size you pick; it is off by default and a superuser enables it with a reload.

Indexes and planner stats

Indexes rarely fail on the day, but each adds work to every INSERT and to every UPDATE that cannot be a HOT update (one that touches no indexed column and fits on the same page). Do this a few weeks out - dropping an index is a change you want to test too.

Invalid indexes - usually leftovers from a failed CREATE INDEX CONCURRENTLY - help no query and may still cost work on every write:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name FROM pg_index WHERE NOT indisvalid; 

An index currently being built with CREATE INDEX CONCURRENTLY is also invalid until done, so check pg_stat_progress_create_index before dropping anything.

Duplicate indexes - same table, same columns in the same order, same type, same expressions, same WHERE clause - do the same work twice. The PostgreSQL wiki has a query that finds candidates. Compare the full indexdef in pg_indexes before dropping: lookalikes can differ in, say, the operator class, and one may back a unique constraint.

Unused indexes show low idx_scan; last_idx_scan (PostgreSQL 16+) shows the last use:

SELECT schemaname, relname, indexrelname, idx_scan, last_idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20; 

Four caveats before dropping: counters start at the last stats reset; an index used only during peaks or by a monthly report can look unused today; an index used only by replica queries shows zero scans on the primary; and a primary key or unique constraint index enforces the constraint regardless of scans. Use DROP INDEX CONCURRENTLY, weeks ahead.

Planner statistics drive every plan. Loading a new catalog or price list before the peak? ANALYZE those tables afterwards so plans match the data. Changes since the last ANALYZE: