PostgreSQL Interview Questions for AI Training Work

A B-tree index handles equality and range queries on ordered scalar data efficiently, the default for most columns. A GIN index is built for values with multiple component elements, like array containment, full-text search, or querying inside a `jsonb` column, where a B-tree can't efficiently express "does this value contain X." I'd reach for GIN specifically when queries filter on `jsonb` fields or array membership, not for typical scalar columns.

Instead of locking rows for reads, PostgreSQL keeps multiple versions of a row, and each transaction sees a consistent snapshot as of when it started, so readers never block writers and writers never block readers. The tradeoff is that old row versions accumulate as dead tuples until vacuumed, which is why regular autovacuum activity matters, a table that isn't vacuumed properly bloats and query performance degrades over time.

`json` stores an exact text copy of the input, preserving whitespace and key order but re-parsing it on every access, and it's not indexable with GIN. `jsonb` stores a decomposed binary format, losing whitespace and key ordering but making it faster to query and indexable, so `jsonb` is the practical default for most applications unless you specifically need to preserve the original text formatting.

`READ COMMITTED`, the default, only guarantees each individual statement sees committed data as of that statement's start, which allows anomalies like two transactions both reading a value, both deciding to insert based on it, and ending up with an inconsistent result. `SERIALIZABLE` guarantees the outcome is equivalent to running transactions one at a time, at the cost of PostgreSQL sometimes aborting a transaction with a serialization failure that the application then has to retry.

A covering index includes all the columns a query needs, either as index keys or via the `INCLUDE` clause, so PostgreSQL can answer the query entirely from the index without visiting the actual table heap, called an index-only scan. This helps most on tables where a query repeatedly selects a small, fixed set of columns, turning what would be an index scan plus heap lookups into a single, cheaper pass over the index.

I'd check whether table statistics are stale, since the planner's decision depends on `ANALYZE` having run recently enough to reflect the current data distribution; a large recent data change without an updated ANALYZE can make the planner misjudge selectivity. I'd also check if the table has grown enough that a sequential scan genuinely became cheaper, or if a function applied to the indexed column in the query is preventing index use entirely.

I'd query `pg_stat_activity` joined against `pg_locks` to see which process is holding the lock the waiting transaction needs, and how long it's been waiting. If it's a long-running transaction holding a lock unnecessarily, like one left open by a bug that forgot to commit, I'd address that root cause rather than just terminating the blocked query, since the same blocking transaction would just cause the next query to stall too.

This points to table bloat: `UPDATE` and `DELETE` in PostgreSQL don't remove the old row version immediately, they mark it dead, and autovacuum is responsible for reclaiming that space. If autovacuum isn't keeping up, often on a high-churn table with default vacuum settings tuned for a smaller table, dead tuples accumulate faster than they're cleaned. I'd check autovacuum settings for that specific table and consider tuning its thresholds more aggressively.

A login query without an index on the lookup column was doing a sequential scan on a growing users table, and as the table crossed a size threshold, query time grew enough to exhaust the connection pool under normal traffic, taking down login for everyone rather than just being slow for one request. Adding the index fixed it in minutes once identified, but it changed how I review schema changes now, checking indexes on any column used in a WHERE clause before it ships, not after.

A stakeholder wanted a new report field indexed immediately for speed, but adding an index isn't free, it slows down every write to that table and adds storage overhead. I explained it as a tradeoff between read speed and write speed rather than a simple yes/no request, and we agreed to index it since the table was read far more than written, but the framing made it clear the decision wasn't automatic and depended on how the table was actually used.