A set of PostgreSQL interview questions ranging from fundamentals to more advanced topics, with concise, correct answers.

MVCC (Multi-Version Concurrency Control) lets readers and writers work on the same data without blocking each other. Instead of locking rows for reads, PostgreSQL keeps multiple versions of a row and gives each transaction a consistent snapshot of the data as of when it started. Old row versions are cleaned up later by VACUUM.

JSON stores an exact copy of the input text, including whitespace and key order, and re-parses it on every read. JSONB stores a decomposed binary representation: it discards insignificant whitespace and duplicate keys, doesn't preserve key order, but supports indexing (via GIN) and is generally faster to query. JSONB is the recommended default unless exact text preservation matters.

TEXT stores variable-length text with no length limit. VARCHAR(n) is the same underlying storage but enforces a maximum length of n. CHAR(n) is fixed-length and pads shorter values with trailing spaces. In PostgreSQL, all three use the same internal storage and have essentially identical performance — VARCHAR(n) and CHAR(n) exist mainly for standards compliance and explicit length constraints, not for a performance advantage.

A regular view is a stored query — it runs the underlying query fresh every time it's selected from. A materialized view stores the query's result set on disk at the time it was last refreshed, so reading from it is fast but the data can go stale until you run REFRESH MATERIALIZED VIEW.

B-tree (the default) handles equality and range comparisons on sortable data. GIN suits composite values with many elements to search inside, such as JSONB, arrays, and full-text search. GiST supports geometric and other data types needing nearest-neighbor or overlap searches. BRIN is a lightweight index good for very large tables where column values correlate with physical row order, such as an ever-increasing timestamp.

A function always returns a value and can be called inside a query (e.g. SELECT my_func(col)); it cannot manage transactions itself. A stored procedure (introduced in PostgreSQL 11, via CALL) doesn't have to return a value and can run its own COMMIT/ROLLBACK statements, making it suitable for multi-step operations that need transaction control.

With INSERT ... ON CONFLICT. You specify a conflict target (a unique or primary key constraint) and what to do when a conflicting row already exists: DO NOTHING to skip it, or DO UPDATE SET ... to update the existing row instead.

Because of MVCC, UPDATE and DELETE don't immediately remove old row versions — they mark them as dead and leave them in place so other in-flight transactions can still see them if needed. VACUUM reclaims that dead space for reuse. Without regular vacuuming, tables and indexes bloat over time and query performance degrades. autovacuum handles this automatically in the background for most workloads.

PostgreSQL supports Read Committed (the default — each statement sees data committed before it started), Repeatable Read (the whole transaction sees one consistent snapshot from its start), and Serializable (the strictest — behaves as if transactions ran one at a time, detecting and rejecting conflicting concurrent transactions with a serialization error). Note that PostgreSQL doesn't implement a separate Read Uncommitted level — it behaves like Read Committed.

PostgreSQL offers richer native data types (JSONB, arrays, ranges, custom types), more advanced indexing (GIN, GiST, BRIN), full support for window functions and recursive CTEs, table inheritance and declarative partitioning, and standards-compliant transactional DDL. MySQL, by contrast, is often simpler to operate at small scale and has historically had a slight edge in raw read-heavy throughput with certain storage engines — the right choice depends on the workload.

Both enforce uniqueness, but a table can have only one primary key (which also implies NOT NULL), while it can have many unique constraints. Unique constraints allow multiple NULL values (since NULL is never considered equal to another NULL), whereas a primary key column cannot be NULL at all.

A Common Table Expression (WITH name AS (...)) is a named, temporary result set you can reference within a single query, useful for breaking complex queries into readable steps. A recursive CTE references itself and is the standard way to query hierarchical or graph-like data, such as an employee-to-manager reporting chain or a category tree.

WHERE filters individual rows before grouping and aggregation happen. HAVING filters after GROUP BY has produced aggregated results, so it can reference aggregate functions like COUNT(*) or SUM(...), which WHERE cannot.

An index built over only the rows that satisfy a WHERE condition, rather than the whole table. It's smaller and faster to maintain than a full index, and is a good fit when queries consistently filter on the same condition — for example, indexing only WHERE status = 'active' when most rows are eventually archived and rarely queried.

EXPLAIN shows the planner's intended strategy without running the query. EXPLAIN ANALYZE actually executes the query and augments the plan with real timing and row counts, which is essential for diagnosing whether the planner's estimates match reality.

DELETE removes rows one at a time (optionally filtered by WHERE), can be rolled back, and fires triggers. TRUNCATE removes all rows at once by deallocating the table's storage, is much faster on large tables, but cannot be filtered and typically doesn't fire per-row triggers. DROP TABLE removes the table definition itself along with all its data.

PostgreSQL spawns a dedicated OS process per connection, which carries real memory and scheduling overhead at high connection counts. A connection pooler such as PgBouncer sits between the application and PostgreSQL, reusing a small set of real database connections across many client connections, which keeps the server responsive under high concurrency.

A PostgreSQL feature that lets you attach policies to a table restricting which rows a given role can see or modify, evaluated transparently on every query. It is commonly used in multi-tenant applications so that a shared table can enforce tenant isolation at the database level rather than relying solely on application code.

Always use parameterized queries or prepared statements provided by your database driver or ORM, so user input is sent as data rather than interpolated into the SQL text. Never build queries by string concatenation with untrusted input.

ANALYZE collects statistics about the distribution of values in each table's columns and stores them for the query planner to use when estimating row counts and choosing a plan. VACUUM ANALYZE does both jobs — reclaiming dead space and refreshing statistics — in one pass, which is why it's the more commonly run command.