Interview Q&A / PostgreSQL

1. What is the difference between EXPLAIN and EXPLAIN ANALYZE, and how do you read a query plan?

   EXPLAIN shows the plan PostgreSQL intends to use, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE actually runs the query and adds real timings and real row counts, so you can compare estimates with reality.

   BeginnerIn-depth4 min read
2. What is the difference between JSON and JSONB in PostgreSQL?

   JSON stores the exact text you inserted, including spaces, key order and duplicate keys, and re-parses it on every read. JSONB stores a parsed binary form, which is slightly slower to write but much faster to query and can be indexed with GIN. Use JSONB unless you must keep the original text exactly.

   BeginnerIn-depth3 min read
3. What is the difference between SERIAL and IDENTITY columns, and when would you use a UUID primary key?

   SERIAL is an older PostgreSQL shorthand that creates a sequence and sets it as the column default. IDENTITY (GENERATED ALWAYS or BY DEFAULT AS IDENTITY) is the SQL-standard replacement and is preferred for new tables. Use a UUID when IDs must be generated outside the database, merged across systems, or must not be guessable.

   BeginnerIn-depth3 min read
4. Why do too many connections hurt PostgreSQL, and how does a connection pooler like PgBouncer help?

   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.

   BeginnerIn-depth3 min read
5. What do VACUUM and autovacuum do in PostgreSQL?

   Because of MVCC, updates and deletes leave dead row versions behind. VACUUM marks that space as reusable, updates the visibility map and freezes old transaction IDs so they cannot wrap around. Autovacuum is a background process that runs VACUUM and ANALYZE automatically when enough rows have changed.

   IntermediateIn-depth3 min read
6. What index types does PostgreSQL support, and why is my index sometimes not used?

   B-tree is the default and handles equality, ranges and sorting. GIN indexes values that contain many items, like JSONB, arrays and full-text search. BRIN is a tiny index for huge tables whose rows are stored in a natural order, like time-series data. The planner skips an index when a full scan looks cheaper, or when the query is written in a way the index cannot match.

   IntermediateIn-depth4 min read
7. What is MVCC in PostgreSQL, and why don't readers block writers?

   MVCC (Multi-Version Concurrency Control) means PostgreSQL keeps several versions of a row. An UPDATE writes a new version instead of overwriting the old one, so a reader keeps seeing the version that was valid when its snapshot was taken while the writer works on the new one. Readers never wait for writers, and writers never wait for readers.

   IntermediateIn-depth4 min read
8. How does PostgreSQL implement transaction isolation levels?

   PostgreSQL offers Read Committed (the default), Repeatable Read and Serializable. Dirty reads never happen, even if you ask for Read Uncommitted. Read Committed takes a new snapshot per statement, Repeatable Read keeps one snapshot for the whole transaction, and Serializable adds checks that abort a transaction if the result could not have come from running transactions one at a time.

   AdvancedIn-depth4 min read