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