PostgreSQL interview questions and quick answers

Advisory locks are application-defined locks on an arbitrary number, managed by PostgreSQL but not tied to any table or row. Use them to make sure only one process runs a task, like a nightly report job, at a time.

In most cases PostgreSQL rewrites the whole table while holding an ACCESS EXCLUSIVE lock, so reads and writes wait. Only some changes, such as widening varchar or dropping a length limit, skip the rewrite.

BRIN stores only the minimum and maximum value for each block range of the table, so it is tiny. It works well on huge tables where the column's value follows the physical row order, like an append-only timestamp.

Yes. A composite index is sorted by the first column, then the second within it, and so on. It helps queries that filter on a leading prefix of its columns, and it is much less useful if only a later column is filtered.

CHECK rejects a row when a boolean expression on its columns is false. It passes when the result is true or NULL, so pair it with NOT NULL when the column must always be present. It cannot see other rows.

Each PostgreSQL connection is a separate server process with its own memory, so thousands of mostly idle connections waste RAM and add CPU overhead. A pooler like PgBouncer lets many clients share a small number of real database connections, handing a connection to a client only while it runs a transaction.

Using session-level advisory locks through a transaction-mode pooler.

"Advisory locks let an application lock an abstract key, for example to ensure one cron instance runs at a time. I use the transaction-scoped versions, especially behind PgBouncer, so locks are never leaked."