Blog
PostgreSQL operations

What your PostgreSQL needs before Black Friday

·30 min read

On Black Friday, many more people use your application at the same moment. For PostgreSQL that means more connections, more sessions that want the same rows, and more cleanup work and transaction log in the same hour. This article shows which limits a peak usually reaches first, which of them a bigger server does not remove, how to check each one on your own server with plain SQL, and which changes need a restart and therefore belong before your change freeze.

Key takeaways

The short answer

A larger machine helps when the database is short of hardware, and for some workloads that is enough. To know whether it is enough for yours, check five areas before the peak: connections, locks, automatic cleanup (autovacuum), the transaction log, and the memory headroom. Make every change that needs a restart before your change freeze, and test the application with it. In a quiet hour before the freeze, refresh the planner statistics and vacuum the tables that need it. Do not run schema changes during the peak. On the day, keep four queries ready: connections by state, what active sessions wait for, which session blocks which, and the oldest open transactions.

When each step of this article belongs. A change freeze takes the restart changes away, so they come first.

What changes when thousands of customers arrive at once?

Every request from a customer becomes work in a database session. On a normal day, most sessions finish before another session needs the same row, the same lock or the same processor core. During a peak they overlap. Three things grow at the same time:

That is why a readiness check is more than a review of settings. Settings matter, but so do the length of your transactions and everything else that runs on the server during the peak: a schema migration, a report, a backup, a bulk import.

Is a bigger server enough?

Many teams prepare for Black Friday by moving the database to a larger machine, with more processor cores, more memory and faster disks. That is a reasonable step, and for some workloads it is enough. A bigger server helps when the database is short of hardware: queries that keep every processor core busy, data that no longer fits in memory, disks that cannot keep up with reads and writes.

It helps little when sessions wait for each other, and not at all when they wait for a limit that is a setting and not a piece of hardware. Four examples from the sections below:

Three orders change the stock count of the same product, and each waits until the order before it commits. More processor cores leave this queue as it is. Faster processors shorten only the database parts, not the time the application keeps each transaction open. An illustration, not a measurement.

On a server you manage yourself, PostgreSQL also does not change its settings when it starts on a larger machine. The extra memory still helps, because the operating system uses it to cache data files, but shared_buffers, work_mem and max_connections keep the values they had before. Some managed services work differently: Amazon RDS for PostgreSQL, for example, computes its default max_connections from the memory of the instance class. Check the values with SHOW after the move. The move itself usually means a restart or a failover, so it belongs before your change freeze, together with the other changes that need a restart.

To know which case you are in, look at what active sessions wait for at your busiest hour today, or during a load test. The second query in the section on what to watch on the day, near the end of this article, shows it. Sessions with an empty wait_event_type are usually running on a processor or waiting for a free one, and sessions with wait_event_type IO are waiting for the disk; a bigger server can help both. Sessions with wait_event_type Lock are waiting for another transaction to finish, and a bigger server shortens that wait only as much as it shortens the other transaction. LWLock waits need a closer look, which the section on locks explains. If you want to know before the peak whether a larger machine is enough, rather than find out on the day, the rest of this article shows how to check each limit.

For every SQL example below, connect to your database with psql or any other SQL client you normally use, and run the query. The checks only read, and they work with the permissions of the built-in pg_monitor role. The commands that change something say so, and they need a superuser or the owner of the object they change.

Why are connections the first limit you hit?

PostgreSQL starts a separate server process for every connection. That has three consequences for a peak.

First, the limit is fixed. max_connections changes only with a restart. When all slots are taken, a new connection from the application is refused with the message "sorry, too many clients already", and the request of that customer fails. Opening a connection is also work in itself - a new process and authentication before the first query runs - so an application that opens a new connection for every request pays that cost on every request.

Second, the number of connections usually grows because of the application, not because of the database. Each copy of your application that runs at the same time - each application server, container or pod - has its own connection pool. When your platform starts more copies to handle the peak, every new copy opens its own pool. Calculate the worst case before the peak: 

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 wait for a free connection in the pool. If the application then retries failed requests at once, the retries add load at the worst possible moment.

Third, more connections do not mean more work done. A running query needs a processor core. When many more sessions are active than the server has cores, they wait for each other, and part of that waiting happens inside PostgreSQL, on the internal locks that protect its shared memory. On the pgsql-admin mailing list, in a thread about a load test in which active sessions climbed while the processors stayed mostly idle, Michael Vitale described this as "a bunch of concurrent active connections contending over the same resources". Idle connections are not free either. Andres Freund, a PostgreSQL committer, measured that "a large number of connections clearly reduce the efficiency of other connections, even when idle". He wrote this in 2020; his changes that reduce this cost went into PostgreSQL 14.

What to do

Put a connection pooler such as PgBouncer between the application and PostgreSQL, and configure it for your traffic. A pooler installed with its default settings does not do what this section needs. PgBouncer's default pool_mode is session, in which a server connection "is released back to pool after client disconnects". Application pools keep their connections open, so in session mode every client connection holds a server connection for as long as it lives, and PgBouncer shares nothing.

The sharing comes from transaction mode. There, "a server connection is assigned to a client only during a transaction", so many client connections share a much smaller number of server connections, and extra requests wait in the pooler instead of inside the database.

The same twelve application connections without a pooler, through PgBouncer with its default pool_mode, and through PgBouncer in transaction mode. Of these three, only transaction mode shares server connections.

Before the peak, check these settings in pgbouncer.ini against your own numbers. The defaults below are from PgBouncer's configuration reference:

PgBouncer is "single-threaded and uses one CPU core per instance". At high request rates, watch the processor usage of the PgBouncer process itself. Its documentation describes how to run several instances on the same port with so_reuseport.

Transaction mode does not keep the state of a session. PgBouncer lists SET and RESET, LISTEN, PREPARE and DEALLOCATE, session-level advisory locks and temporary tables that outlive a transaction among the features it does not support in that mode. An application that relies on one of them has to change, or that part of it has to connect through a database entry with its own pool_mode set to session. Test the application with the pooler and its final settings, under load, before the freeze - not in the week of the peak.

Set the pool size of each copy of the application on purpose, and limit how many copies the platform may start. Without a pooler, the sum above has to stay below max_connections, minus the slots reserved for administrators; with one, it has to stay below max_client_conn. If you do raise max_connections, remember that every extra session can use memory for its own sorts and hashes (see the memory section below), so check the server's memory reserve first.

Keep a way in for yourself. When the application has taken every slot, PostgreSQL refuses your connection too, unless a slot is reserved for you. superuser_reserved_connections (3 by default) keeps slots that only superusers can use. If you investigate incidents as a superuser, you already have these slots and need to change nothing. They stay free only when the application itself does not connect as a superuser.

If you investigate with an account that is not a superuser, PostgreSQL 16 and newer can keep slots for it too: reserved_connections (0 by default) keeps slots 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;

The 2 and the role name oncall are examples. The setting needs a restart, and every reserved slot comes out of max_connections, so it is a slot the application can no longer use.

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 your busiest hour today. Compare connections with max_connections minus the reserved slots, and active with the number of processor cores of the server. Then ask how much each number grows when traffic grows several times.

If you run PgBouncer, check it as well. Its admin console is a virtual database named pgbouncer, open to the users listed in admin_users or stats_users. From a shell, for example psql -p 6432 pgbouncer (6432 is PgBouncer's default port), run: 

SHOW CONFIG;
SHOW POOLS;

SHOW CONFIG lists the values PgBouncer is running with. In SHOW POOLS, cl_waiting counts client connections that have sent a query and have no server connection yet, and maxwait shows how long the oldest of them has waited. The documentation's advice on maxwait: "If this starts increasing, then the current pool of servers does not handle requests quickly enough."

Why do sessions start waiting for each other?

PostgreSQL uses locks so that two sessions cannot change the same data in conflicting ways. On a normal day, most locks are held for milliseconds and nobody notices them. During a peak the same locks are requested many times per second, and any lock that is held for long builds a queue behind it. Five patterns cause most of this waiting.

A popular row. An UPDATE locks the row it changes until its transaction ends. If every order updates the same row, for example the stock count of the product on sale, those orders run one after another, not in parallel. In pg_stat_activity, the waiting sessions show wait_event_type Lock with wait_event transactionid, "Waiting for a transaction to finish", or tuple. A setting does not fix this; the transaction does. Update the popular row as late as possible in the transaction and commit right after it. Never wait for something outside the database, such as a payment provider, while a transaction holds that row.

A schema change behind a long query. Many forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, and the documentation is precise about what that means: "Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement." Lock requests also wait in a queue. While an ALTER TABLE waits for a long transaction to finish, every new query on that table waits behind the ALTER TABLE, plain SELECT included. Laurenz Albe explained a case like this on the pgsql-admin list: the ACCESS EXCLUSIVE lock is "waiting in line and blocking everything else (locks have to queue)". Do not run schema migrations during the peak. When you do run one, set a short lock_timeout first, so that the statement fails instead of blocking everyone, and try again later: 

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

The 2 seconds and the column are examples. Create indexes with CREATE INDEX CONCURRENTLY, which does not block writes. If it fails, it leaves an invalid index behind, which "will still consume update overhead" until you drop it.

The lock queue on one table. Without lock_timeout, a schema change that waits for a long report makes every later query wait too, plain SELECT included. With lock_timeout, the schema change fails after 2 seconds, the queries behind it run, and you retry the change later.

A transaction left open. A session in the state idle in transaction keeps every lock it took, for as long as the application leaves the transaction open. The documentation describes 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, for the role your application uses rather than for the whole server. The documentation advises against setting 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';

The role name and both values are examples. Choose a statement_timeout longer than your slowest legitimate request, and check that the application handles a cancelled statement and a closed session. A role setting applies to new sessions, so connections that a pool already holds keep the old values until they reconnect.

Two transactions that lock rows in a different order. PostgreSQL detects such a deadlock and resolves it "by aborting one of the transactions involved", and the server log shows deadlock detected. An order of operations that rarely overlaps on a normal day overlaps more often under peak load. The fix is in the application: the documentation recommends that "all applications using a database acquire locks on multiple objects in a consistent order".

Waiting inside PostgreSQL itself. Not every wait is a lock on your data. wait_event_type LWLock means that a session waits for a lightweight lock, which protects a structure in PostgreSQL's shared memory. Many such waits during a peak point to too many active sessions competing for the same internal structures, and the answer is fewer active sessions - the pooler above - not more. One of these waits has its own cause: LockManager. Each session can record a small number of ordinary table and index locks in a fast private area. Above that number it uses the shared lock table, which the change that enlarged the fast area describes as "considerably more expensive, and may be subject to contention (especially on many-core systems)". A query over many partitions or many indexes reaches that number quickly. In PostgreSQL 18 the size of the fast area follows max_locks_per_transaction (64 by default), which changes only with a restart. If a load test shows many LockManager waits, that restart belongs before the freeze.

Check it

Turn on logging of lock waits before the peak. log_lock_waits is off by default. With it on, the server log records every session that waits for a lock longer than deadlock_timeout (1 second by default), and names the process holding the lock and the wait queue. After the peak, that is your record of who blocked whom. As a superuser: 

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

The query for who blocks whom is in the section on what to watch on the day, near the end of this article.

Will autovacuum keep up?

Every UPDATE and DELETE leaves the old version of the row behind as a dead row. Autovacuum, PostgreSQL's automatic cleanup, removes dead rows so that the space can be used again. During a peak, dead rows appear faster than on a normal day. Three things decide whether cleanup keeps up.

When a table is due for cleanup. By default, a table becomes due for autovacuum after 50 rows plus 20 percent of the table have been updated or deleted. PostgreSQL 18 adds a cap, autovacuum_vacuum_max_threshold, at 100 million rows. For a large and busy table, both are late. Change the threshold for that table only, instead of for the whole server: 

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

The table name and the value are examples. The command takes a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes, and autovacuum uses the new value the next time it checks the table.

How fast it works. Autovacuum pauses regularly so that it does not take over the disks, and autovacuum_vacuum_cost_limit sets how much work it does between pauses. Its default, -1, means that it uses vacuum_cost_limit, which is 200. That limit is shared. In the words of the documentation, 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 clean more tables at the same time, each one more slowly. If cleanup falls behind on several tables at once, raising the cost limit allows more work per second; raising only the number of workers does not. The extra cleanup reads and writes the disk while your customers do, so make that change before the peak and watch the disks after it. Both settings change with a reload in PostgreSQL 18. autovacuum_max_workers can go up to autovacuum_worker_slots (16 by default), and changing that one needs a restart.

autovacuum_vacuum_cost_limit is one budget for all running workers. With the default limit of 200, three workers get about 67 each: they clean more tables at the same time, each more slowly.

Whether it can remove anything at all. Cleanup cannot remove a dead row that an open transaction might still need to see. The documentation says it directly: "an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction". One report that runs for an hour during the peak holds back cleanup for every table in its database for that hour, however fast autovacuum is. So does a transaction that changed data and was then left idle in transaction, for as long as the application leaves it open. A replication slot, or a replica that reports its own queries back to the primary (hot_standby_feedback), can hold back cleanup for the whole server.

Check it

Dead rows and the last cleanup, per table. pg_stat_user_tables covers only the database you are connected to, so run this in each database: 

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;

The dead row count is an estimate. A large table near the top of this list, with an old or empty last_autovacuum, is the one to look at first. Then the open transactions that hold back cleanup, for the whole server, 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 whether a replication slot holds cleanup back: 

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

A transaction that has been open for hours holds back cleanup, and so does a slot with a large xmin_age. A slot that is not active is the usual cause, because nothing moves it forward.

Will a wraparound vacuum start in the middle of the peak?

Every transaction that writes uses a transaction ID, and PostgreSQL must regularly mark old rows as frozen so that these IDs can be used again. When the oldest unfrozen transaction ID in a table is older than autovacuum_freeze_max_age (200 million transactions by default), autovacuum runs a vacuum "to prevent wraparound" on it, even when autovacuum is otherwise turned off. The documentation adds that, unlike a normal autovacuum, this one "is not automatically interrupted" when another command needs a conflicting lock. On a large table it can read most of the table and write a lot of transaction log, and a schema change on that table waits for it to finish, with the lock queue behind it.

A peak uses transaction IDs faster than a normal day. If a large table will reach that age during the peak, do the work yourself before it, in a quiet hour. Find the oldest tables in each 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 see how fast the age grows, and compare that growth with the distance left to autovacuum_freeze_max_age. multixact_age works the same way, with its own limit, described below. For a table that will reach either limit during the peak, run VACUUM (FREEZE, VERBOSE) on it before your change freeze, as the table owner or a superuser. It reads and writes a lot, so choose a quiet hour.

For the whole server, check how far each database is from the hard limit, both for transaction IDs and for multixact IDs, which PostgreSQL uses when several 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 (400 million by default) plays the same role for multixact IDs. The documentation describes what happens if these numbers are ignored for too long: the server "will refuse to assign new XIDs once there are fewer than three million transactions left until wraparound", and only read-only transactions can start. This is the number you never want to discover during a peak.

Should you run VACUUM and ANALYZE yourself before the peak?

Yes, in a quiet hour some days before your change freeze, but the two commands deserve different treatment. A manual run removes the dead rows that have piled up, refreshes the planner statistics, and updates the visibility map, which lets PostgreSQL "answer some queries using only the index, without reference to the underlying table". A table becomes due for autovacuum when the number of "tuples obsoleted since the last VACUUM" crosses its threshold, so on a table you have just vacuumed, that count starts the peak low.

ANALYZE of the whole database is the cheap part. "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 statistics can change query plans, so run it early enough to see the effect before the freeze, not the night before the peak.

VACUUM of the whole database is the expensive part, and three things decide how much of it to run:

So refresh statistics everywhere, and vacuum where it matters: the tables near the top of the dead-row list in the autovacuum section, and the tables whose transaction ID age is closest to autovacuum_freeze_max_age. vacuumdb, which ships with PostgreSQL, does both from a shell. As a superuser: 

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

The first command refreshes statistics in every database. The second vacuums and freezes only the tables whose transaction ID age is at least 100 million. The third vacuums and analyzes one table. The database, the table and the age are examples. If an earlier quiet hour showed that the whole database fits into such a window, vacuumdb --all --analyze does all of it 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", so every query on that table waits for it, and it needs disk space for a second copy of the table. Plain VACUUM runs alongside reads and writes. It locks the table only for a moment at the end, and only when it can give empty pages at the end of the table back to the operating system.

Is the transaction log sized for the peak?

PostgreSQL writes every change to the transaction log (WAL) first. A checkpoint regularly saves the changed data to the data files, so that older log is no longer needed for recovery after a crash. In the documentation's words, "A checkpoint is begun every checkpoint_timeout seconds, or if max_wal_size is about to be exceeded, whichever comes first." During a peak the log grows faster, so a max_wal_size that was enough on a normal day can start checkpoints early. Each extra checkpoint costs extra writes, because 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 counts checkpoints started by the timer and num_requested those requested for other reasons. In PostgreSQL 18 both also count checkpoints that were skipped, and num_done counts the ones performed. The counters cover the whole server and add up from the time in stats_reset, so take one reading right before your busiest hour and one right after it, and compare the differences. On PostgreSQL 17 there is no num_done, and on PostgreSQL 16 and older the counters are checkpoints_timed and checkpoints_req in pg_stat_bgwriter.

Requested checkpoints are not caused only by log size: a manual CHECKPOINT and the start of a backup count too. Confirm the cause in the server log. log_checkpoints is on by default since PostgreSQL 15, and a line with checkpoint starting: wal means log size. When such checkpoints come too close together, the log also says checkpoints are occurring too frequently, with a hint to increase max_wal_size.

What to do

max_wal_size changes with a reload, so it needs no maintenance window. Before you raise it, check the free space on the disk that holds pg_wal. 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 the worst case for disk use and for recovery time after a crash. Our article max_wal_size, explained walks through the measurement and the change step by step.

Is there enough memory for the extra sessions?

shared_buffers, PostgreSQL's own data cache, changes only with a restart. work_mem is different: it limits each sort or hash operation, not each session. The documentation explains why that matters: "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". A hash operation can use work_mem times hash_mem_multiplier, which is 2 by default. A query that runs in parallel adds more, because each of its worker processes has its own work_mem. A peak multiplies the number of sessions that do this at the same time.

If a Linux server runs out of memory, the kernel can stop a PostgreSQL process. When it stops the postmaster, the main PostgreSQL process, the documentation describes the result: "Although existing database connections will continue to function normally, no new connections will be accepted. To recover, PostgreSQL will need to be restarted."

What to do

Do not raise work_mem for the whole server in the week before the peak. If a known report or job needs more, give it more for that session or for that role. To see whether queries write sorts and hashes to temporary 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;

Like the checkpoint counters, these add up over time, so compare two readings. The setting log_temp_files makes the server log every temporary file above a size you choose; it is off by default and a superuser changes it with a reload.

Are your indexes and planner statistics ready?

Indexes rarely fail on the day, but each one adds work to every INSERT, and to every UPDATE that PostgreSQL cannot do as a HOT update - an update that changes no indexed column and fits on the same data page. Do this part a few weeks before the peak, because removing an index is a change you want to test as well.

Invalid indexes, usually left behind by a failed CREATE INDEX CONCURRENTLY, help no query and can 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 that CREATE INDEX CONCURRENTLY is building right now is also invalid until the build finishes, so check pg_stat_progress_create_index before you drop anything.

Duplicate indexes - two indexes on the same table with the same columns in the same order, the same index type, the same expressions and the same WHERE condition - do the same work twice. The PostgreSQL wiki has a query that finds candidates. Before you drop one, compare the full definitions in the indexdef column of pg_indexes: two indexes that look alike can still differ in a detail such as the operator class, and one of them can be the index behind a unique constraint.

Unused indexes show up with few scans in idx_scan, and last_idx_scan (PostgreSQL 16 and newer) shows when an index was last used: 

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

Before you drop one, check four things: the counters start at the last statistics reset; an index that only the peak itself or a monthly report uses can show few scans today; an index used only by queries on a replica shows no scans on the primary; and an index behind a primary key or a unique constraint enforces that constraint even with no scans. Drop it with DROP INDEX CONCURRENTLY, weeks before the peak.

Planner statistics decide how each query runs. If you load a new product catalog or price list before the peak, run ANALYZE on those tables after the load, so that the plans fit the new data. This shows how much each table changed since its last ANALYZE: 

SELECT schemaname, relname, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 20;

Which changes need a restart?

Everything in the first part of this table needs a restart, and that is why this review belongs in the calendar rather than in the backlog.

How the changes in this article apply in PostgreSQL 18, and when you can make them.
ChangeHow it appliesWhen you can make it
shared_buffersRestartBefore the change freeze
max_connectionsRestartBefore the change freeze
reserved_connections, superuser_reserved_connectionsRestartBefore the change freeze
max_locks_per_transactionRestartBefore the change freeze
autovacuum_worker_slotsRestartBefore the change freeze
A minor version updateRestartBefore the change freeze
max_wal_sizeReloadAny time, once you know the disk reserve
autovacuum_max_workers (PostgreSQL 18)ReloadAny time, up to autovacuum_worker_slots
autovacuum_vacuum_cost_limitReloadAny time
log_lock_waits, log_temp_filesReload, as a superuserAny time
work_mem, statement_timeout, idle_in_transaction_session_timeout, lock_timeoutFor a session or a roleAny time; a role setting reaches new sessions
Autovacuum settings of one tableALTER TABLEAny time; autovacuum uses them at its next check

Check your own server: SELECT version(); shows the version you run, and PostgreSQL's versioning page lists the latest minor release of each major version. The context column of pg_settings says how each setting applies - postmaster means a restart, sighup a reload - and pending_restart shows changes that are already in the configuration but wait for a restart: 

SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'max_locks_per_transaction',
               'autovacuum_max_workers', 'max_wal_size', 'work_mem');

SELECT name, setting FROM pg_settings WHERE pending_restart;

Any row in the second query is a change that will take effect at the next restart, planned or not. Know about it before the peak.

If your team freezes changes before Black Friday, make the changes that need a restart before the freeze. The other changes in this table need no restart, so you could make them later, but a change you make for the first time in the middle of the peak is a change you have not tested.

What should you watch on the day?

Four queries cover most of what happens during the peak. Run them when you need them, not in a loop every second. Connections by state: 

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

What active sessions wait for. An empty wait_event_type means that the session is not waiting for anything PostgreSQL tracks: usually it is running on a processor, or waiting for a free one, so compare these numbers with the processor usage of the server: 

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

Which session blocks which: 

SELECT pid, usename, wait_event, now() - query_start AS query_running_for,
       blocked_by, left(query, 60) AS query
FROM (SELECT a.*, pg_blocking_pids(a.pid) AS blocked_by
      FROM pg_stat_activity a
      WHERE a.wait_event_type = 'Lock') AS w
ORDER BY query_running_for DESC;

blocked_by lists the sessions that hold a conflicting lock and the sessions ahead in the queue. The documentation warns that "frequent calls to this function could have some impact on database performance", which is one more reason to run it on demand. The fourth query is the one for the oldest open transactions in the autovacuum section above.

If you run PgBouncer, keep SHOW POOLS from the connections section ready too. A request that waits in the pooler has no database session yet, so it does not appear in pg_stat_activity; cl_waiting and maxwait show it.

One more thing not to do: do not reset the statistics to get a clean start for the day. "Using pg_stat_reset() also resets counters that autovacuum uses to determine when to trigger a vacuum or an analyze." Take two readings and compare them instead.

What does Guardioz check from this list?

Guardioz checks part of this list for you, about once an hour, from the same statistics. You install its agent on the database server with one command, and Guardioz analyzes the first snapshot as soon as it arrives. Checks that follow your server's activity over time, such as the transaction log volume, also use the snapshots that come after it, so their results get more certain as readings add up. From this article, Guardioz checks:

Every finding shows the measurements from your server that it is based on, and a settings recommendation says whether it needs a restart. Where there is a statement to run, the finding gives it to you, and running it is your decision. The agent only reads, connects to PostgreSQL locally, and sends its data out over HTTPS.

Guardioz does not check locks and deadlocks, the number of connections or the connection limits, timeouts, how close a database is to transaction ID wraparound, unused indexes, the worker and cost settings of autovacuum, or query performance. Query analysis is planned. Guardioz collects about once an hour, so it is not a tool for watching the peak as it happens. For the day itself, use the four queries above.

FAQ

Does Guardioz work with managed PostgreSQL such as Amazon RDS, Aurora, Cloud SQL or Azure Database for PostgreSQL?

No. Guardioz supports PostgreSQL 18 on your own servers, on Linux with systemd and on FreeBSD. The agent runs on the database server itself: it connects over the local socket and measures the machine that PostgreSQL runs on, and managed services do not give you a shell on that machine. Support for Azure Database for PostgreSQL flexible server is planned. The queries in this article that only read statistics work on managed services too; there, you change settings in the provider's console, not with ALTER SYSTEM.

Which PostgreSQL versions are supported?

Guardioz supports PostgreSQL 18. The queries in this article are written for PostgreSQL 18, and where an older version differs, the article says so next to the query.

Does Guardioz change anything on my server?

Guardioz does not change your settings or your data. When a finding has a statement to run, it gives you the statement, and running it is your decision. The installer creates a monitoring role and a system account for the agent, and the documentation lists every change it makes.

What leaves my network?

The agent sends its snapshots to Guardioz over HTTPS, on a connection that it opens itself. It needs no inbound port. The documentation lists what a snapshot contains.

Do I need a database administrator on the team to use it?

No. Guardioz is built for teams that run PostgreSQL in production without a dedicated database administrator. You do need someone who reads SQL and knows what a restart means for your service, because the findings are written for that person.

What does it cost?

Guardioz is in beta. You can sign up, and the product is early - we say so up front. Always free for one user. No credit card required to sign up. The Team plan costs $49 per user per month.

How often should this review be repeated?

Before every planned traffic increase, and after every large change in table sizes or write volume. Settings that fit a database at one size do not keep fitting as it grows.

Further reading

Get started

Want a real say in what we build?

Guardioz is in beta. You can sign up, and the product is early - we say so up front. Always free for one user. No credit card required. Every customer gets a real say in what we build next: which problems matter most, which recommendations earn trust, where the guardian goes from here.

Start free See pricing

Questions first? Write to .