FAQ

Questions operators type at three in the morning, answered in the first paragraph

One question per page, in the words it was asked. The answer comes before the explanation, because that is the order you need it in.

Most pages that answer a database question make you earn it. There is a definition, then some history, then a paragraph about why the subject is important, and the sentence you came for arrives two screens down if it arrives at all. That ordering exists because it is easier to write, not because anybody reads that way.

So every page in this section opens with the answer and nothing else, in about sixty words, and only then explains what is behind it. Some of these have a short answer and a long one that disagree in an interesting way. Where a question really needs a query, a threshold or a procedure, the page says so and sends you to the guide that carries it rather than compressing it into something you would have to double-check anyway. Words with an exact meaning link to their definition, and where the answer differs by major version, to the page for that version.

Vacuum, bloat and wraparound

How do I know if autovacuum is keeping up?

Compare how long ago each busy table was last vacuumed against how fast it earns new dead rows, and check whether the vacuums that did run were permitted to remove anything.
Does VACUUM FULL lock the table?

Yes. VACUUM FULL holds an access exclusive lock on the table for its entire duration, so nothing can read it, write it or plan against it until the command finishes.
What is the difference between VACUUM and ANALYZE?

VACUUM is housekeeping on the storage: it makes the space held by no-longer-visible row versions reusable, advances the freezing bookkeeping that keeps transaction ids from wrapping, and records which pages are entirely visible.
What does "database is not accepting commands to avoid wraparound data loss" mean?

It means freezing fell so far behind that the server has refused to issue any more transaction ids in that database, to protect rows that would otherwise become unreadable.
Can I turn autovacuum off?

You can, and it is almost always the wrong lever.
How do I make VACUUM run faster?

Give it more memory, remove the pacing that is deliberately slowing it down, and let the index phase use more than one core.
Does VACUUM block other queries?

No. A plain vacuum runs alongside ordinary reading and writing, and the documentation says so directly: it takes no exclusive lock on the table.
Why didn't deleting rows free any disk space?

Because a delete does not take anything out of the file.

Connections and sessions

How do I stop idle in transaction sessions?

Set idle_in_transaction_session_timeout to a duration no legitimate transaction would ever reach, and the server ends those sessions itself.
Why do I get "sorry, too many clients already"?

Every connection slot the server was configured with is occupied, so it refuses the next login with that fatal error rather than accepting a session it cannot serve.
How many connections can Postgres handle?

There is no single number, and the useful question is different from the one asked.
Is it safe to kill a Postgres query?

Yes, when you do it through the server. pg_cancel_backend stops the running statement and leaves the session connected; pg_terminate_backend ends the session and rolls its transaction back.
Why do I get "server closed the connection unexpectedly"?

That message comes from the client library, not from the database.
Why is opening a Postgres connection so slow?

Because the server forks a new process for every client.
Do idle Postgres connections use memory?

Yes, and the amount is neither small nor fixed.
How do I limit connections per user or database?

With a connection limit set on the role, on the database, or on both.

Locks and schema changes

How do I find what is blocking a query?

Ask the server directly: there is a function that takes a process id and returns the sessions blocking it, which is far more reliable than joining the lock view to itself.
Does adding a column lock the table?

It takes the strongest table lock, but on a modern version it holds it for a moment rather than for a rewrite.
Does TRUNCATE lock the table?

Yes, an access exclusive lock, on the named table and on every table the command cascades into.
Does changing a column type lock the table?

Yes, and this is the expensive member of the family.
ERROR: out of shared memory. HINT: You might need to increase max_locks_per_transaction.

The lock table ran out of room. It is a fixed block of shared memory, sized when the server starts from the per-transaction setting multiplied by the number of backends it was configured for, and it holds one entry for each distinct object a transaction has locked.
What is SELECT FOR UPDATE SKIP LOCKED for?

Dealing work out of a table to several workers at once without any of them waiting.

Replication and slots

Why is my replication slot growing?

Because the consumer behind it has stopped confirming how far it has read, and the primary is doing exactly what a slot asks: keeping every write-ahead log segment from that point onward so the consumer can resume.
Can I drop a replication slot safely?

Safely, yes, provided you have established what was consuming it.
How do I check replication lag in seconds?

Two sources, and you want both. On the standby, the timestamp of the last replayed transaction compared with the current time gives a figure in seconds.
ERROR: canceling statement due to conflict with recovery

The query was running on a standby, and applying the primary's changes would have destroyed something the query still needed.
What happens if a synchronous standby goes down?

Commits stop. The documentation puts it bluntly: transaction commits may never complete if a required synchronous standby crashes.
Why did my logical replication subscription stop?

Almost always because the apply worker met a change it could not apply and raised an error.
ERROR: cannot execute UPDATE in a read-only transaction

Something has made this session read-only, and there are only four candidates.

Write-ahead log

Why is pg_wal filling up?

Because segments are being created faster than they are being released, and there are only four reasons a segment is not released: a replication slot still needs it, the archive command has not succeeded on it, a retention setting says to keep it, or checkpoints are not completing so nothing has become releasable yet.
Can I delete files from pg_wal?

No. Every file in that directory is either required to bring the cluster back after a crash or about to be recycled by the server itself, and there is no way to tell those apart from names, sizes or timestamps.
How do I know if WAL archiving is working?

Read the archiver statistics view. It carries the number of files archived, the name and time of the last success, and the matching pair for failures.
Why does Postgres write so much WAL?

Because the log is not a record of your row changes.
Can I lose data with synchronous_commit off?

Yes, and only in one way. A crash can lose transactions that were already reported to the client as committed, within a window the documentation bounds at three times the log writer's interval, which on the defaults is well under a second.

Plans and the planner

Does EXPLAIN ANALYZE actually run the query?

Yes. EXPLAIN on its own only plans, but adding ANALYZE executes the statement in full and reports what happened, which means an insert inserts, an update updates and a delete deletes.
Why is Postgres not using my index?

There are only two answers and they need separating before anything else.
Why did my query suddenly get slow?

The word "suddenly" is the most useful part of the report.
Why is count(*) so slow in Postgres?

Because the number is not stored anywhere.
Why is my query fast in psql but slow in the app?

Most often because the two are not running the same plan.
Why is my query not using parallel workers?

Two entirely different failures wearing the same symptom.
Why is OFFSET pagination slow?

Because skipped rows are not free. The documentation states it directly: the rows an offset skips still have to be computed inside the server, and then discarded.

Indexes

Is CREATE INDEX CONCURRENTLY safe?

For availability, yes: it is the supported way to add an index to a table that is being written to, and it does not block reads or writes for the duration.
Do I need an index on a foreign key?

The referenced side is already indexed, because the constraint requires it.
Why is my index bigger than my table?

Usually because it is holding entries for row versions the table no longer counts.
ERROR: index row size exceeds btree version 4 maximum

A single value is too large to fit in a B-tree entry.
How do I index a JSONB column?

Three options, and the right one depends on whether you know in advance which keys you query.

Upgrades

Do I need to REINDEX after a major upgrade?

Not because of the major version itself.
Do I need to run ANALYZE after pg_upgrade?

This changed recently, so check which version you are upgrading to before copying anybody's runbook.
Can I skip major versions when upgrading Postgres?

Yes. The upgrade tool goes from any release back to 9.2 straight to the current major in a single pass, and doing it in one hop is both faster and less risky than a chain of intermediate upgrades, each of which is another chance to stop halfway.
Is pg_upgrade --link safe?

Safe in the sense that it does not corrupt anything, and unsafe in the sense that it takes away the rollback.
Does a minor version upgrade need pg_upgrade?

No. The documentation says the tool is not required for minor upgrades, and it is not required because nothing in the data directory changes.

Configuration

Do I need to restart Postgres after changing a setting?

Usually not. Every setting carries a context that says how it can be changed, and only one of those values requires a restart: the settings fixed when the server process starts, such as the connection limit and the shared buffer size.
Should I turn off fsync in Postgres?

No, unless the entire contents can be recreated from somewhere else.
What should random_page_cost be on SSD?

Lower than the default of four, and the exact figure matters less than people expect.
How do I log slow queries in Postgres?

Set a minimum duration, and every statement that runs longer is written to the log with its text and its elapsed time.
How do I set statement_timeout safely?

Attach it to the role or to the database, not to the configuration file.

Monitoring

Is pg_stat_statements safe to enable in production?

Yes. It is the closest thing PostgreSQL has to a standard, it runs on an enormous number of production clusters, and the overhead is small enough that the question is usually the wrong way round: the risk of not having it is higher.
Why does pg_stat_activity show a query on an idle session?

Because that column is documented as the backend's most recent statement, not its current one.
Why doesn't pg_stat_statements show all my queries?

Four reasons, and the common one is eviction.
How do I find what is using all my disk space?

Check four places, in this order, because they fail for different reasons and on different timescales: the write-ahead log directory, the temporary file area, the server log directory, and only then the tables.

Put every Postgres you run on autopilot.

We onboard teams in small batches. Tell us about your fleet and we will reach out when a seat opens. One email, no drip campaign.

Request early access Book a demo