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. 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
3. 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
4. What does EXPLAIN (ANALYZE, BUFFERS) add and what do shared hit and read mean?

   BUFFERS reports how many 8 kB pages each plan node touched. shared hit means the page was already in PostgreSQL's cache, shared read means it came from the operating system or disk. It shows I/O cost that timing alone hides.

   IntermediateQuick answer1 min read
5. What is a HOT update in PostgreSQL and how does fillfactor help?

   A HOT (heap-only tuple) update happens when no indexed column changes and the new row version fits on the same page, so no new index entries are needed. Lowering fillfactor leaves free space on each page to make that more likely.

   AdvancedQuick answer1 min read