PostgreSQL Interview Questions

Q: What is the difference between CHAR, VARCHAR, and TEXT?

CHAR(n) is fixed-length, padded with spaces. VARCHAR(n) is variable-length with a max. TEXT is unlimited variable-length. In PostgreSQL, all three are stored the same way internally — there is no performance difference. Prefer TEXT unless you have a specific reason to enforce a length constraint.

Q: What are ACID properties?

• Atomicity — all operations in a transaction succeed or all are rolled back

• Consistency — database moves from one valid state to another; constraints are never violated

• Isolation — concurrent transactions don't see each other's uncommitted changes

• Durability — committed transactions survive crashes (WAL ensures this)

Q: INNER JOIN vs LEFT JOIN?

INNER JOIN returns only rows where the condition matches in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right — NULLs fill unmatched right-side columns. Use LEFT JOIN when you need to keep all records from one side regardless of whether a match exists.

Q: What is an index and when should you add one?

An index is a separate data structure (usually a B-tree) that speeds up lookups at the cost of extra storage and slower writes. Add indexes on columns used in WHERE, JOIN ON, and ORDER BY clauses. Avoid over-indexing — each index slows INSERT/UPDATE/DELETE and wastes space. Use EXPLAIN ANALYZE to confirm an index is being used.

Q: What is MVCC?

Multi-Version Concurrency Control. PostgreSQL never overwrites rows in place — instead each UPDATE creates a new row version (tuple) and marks the old one as dead. Readers see a consistent snapshot without locking writers. Dead tuples are reclaimed by VACUUM. This enables high concurrency but requires regular vacuuming to prevent bloat.

Q: What is a covering index?

A covering index includes all columns needed by a query, enabling an Index Only Scan — PostgreSQL never touches the heap (main table). Use the INCLUDE clause:

CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total, status);

Q: What is the N+1 query problem?

When you fetch N records then make one extra query per record (e.g., fetch 100 users, then query each user's orders individually = 101 queries). Fix by using a JOIN or eager loading to fetch all data in one query. In ORMs use .include() / .with() or DataLoader batching.

Q: What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation. HAVING filters groups after GROUP BY aggregation. You cannot use aggregate functions in WHERE, but you can in HAVING.

Q: How does PostgreSQL handle NULL?

NULL represents unknown/missing data. NULL != NULL — comparisons with NULL always return NULL (unknown), not true/false. Use IS NULL / IS NOT NULL to check for NULL. Aggregate functions like COUNT(*) vs COUNT(col) differ: COUNT(col) ignores NULLs. Use COALESCE(val, default) to substitute a fallback.

Q: Sequence vs SERIAL vs IDENTITY?

SERIAL is a shorthand that creates a sequence + sets a DEFAULT. IDENTITY (SQL standard, PostgreSQL 10+) is more explicit and portable. Both auto-increment. Use GENERATED ALWAYS AS IDENTITY for new tables. Use uuid_generate_v4() or gen_random_uuid() for UUID primary keys in distributed systems.

Q: What is connection pooling and why is it needed?

PostgreSQL spawns a new process per connection — connections are expensive (~5MB RAM each). Connection pooling (PgBouncer, Supabase Pooler) maintains a pool of persistent backend connections and multiplexes many app connections over them. Essential for serverless/edge environments where hundreds of short-lived connections would overwhelm the DB.