PostgreSQL

PostgreSQL interviews go beyond portable SQL into MVCC, rich index types, JSONB, CTEs, window functions, full-text search, extensions, and vacuum operations. Candidates should connect features to access paths and day-two operations.

Subtopics: index types (B-tree, GIN, GiST, BRIN, partial), JSONB, CTE, window functions, full-text search, extensions, vacuum basics.

On interviews: explain when GIN beats B-tree, how JSONB indexing works, why long transactions hurt vacuum, and when an extension is worth portability cost.

Common pitfalls: JSONB for everything; recursive CTE without plan check; ignoring bloat and autovacuum; extension sprawl without upgrade path.

The trade-off is balancing PostgreSQL power against operational surface area — extensions and custom types increase capability and migration risk.

Checklist:

• Match index type to operator class and query shape.
• Keep JSONB query paths indexed and schema-owned.
• Monitor bloat, autovacuum, and long-running transactions.
• Evaluate extensions for deploy, backup, and portability.