Top 60 PostgreSQL Interview Questions (2026)

PostgreSQL is a free, open-source object-relational database that speaks standard SQL and adds features like rich data types, extensions, and full ACID transactions. It's known for correctness and for handling both relational and JSON data in one engine.

What sets it apart is depth in the core: MVCC concurrency, window functions, CTEs, JSONB, full-text search, and an extension system (like PostGIS). Teams reach for it when they want one dependable database rather than several specialized ones.

A relational database stores data in tables made of rows and columns, where each table represents one kind of thing and relationships between tables are expressed with keys. A row is a single record, a column is one attribute, and every column has a defined data type.

The 'relational' part is the point: instead of duplicating data everywhere, you link tables with foreign keys and join them when you query. That keeps data consistent and storage lean.

A primary key uniquely identifies every row in a table. It's both UNIQUE and NOT NULL, there's at most one per table, and PostgreSQL automatically builds an index on it so lookups are fast.

A primary key can be a single column or several columns together (a composite key). Many teams use a generated identity column or a UUID as a surrogate key rather than relying on natural data.

A foreign key is a column (or set of columns) whose values must match a key in another table. It enforces referential integrity: you can't insert an order for a customer that doesn't exist, and you control what happens on delete or update.

The ON DELETE and ON UPDATE clauses set that behavior: CASCADE removes dependent rows, RESTRICT blocks the delete, and SET NULL clears the reference.

SQL is the standard query language for relational databases: SELECT, INSERT, UPDATE, DELETE, and the rest. PostgreSQL is a database system that implements SQL and extends it with its own features.

So you write SQL to talk to PostgreSQL. Most SQL you learn is portable, but each database adds extensions: PostgreSQL has JSONB operators, array types, and its own procedural language, PL/pgSQL, that aren't part of the standard.

They group SQL by purpose. DDL defines structure (CREATE, ALTER, DROP). DML changes data (SELECT, INSERT, UPDATE, DELETE). DCL controls access (GRANT, REVOKE). TCL manages transactions (BEGIN, COMMIT, ROLLBACK, SAVEPOINT).

DELETE removes rows and accepts a WHERE filter, logging each row so it's fully transactional. TRUNCATE empties an entire table quickly by deallocating data pages instead of scanning rows. DROP removes the table definition itself, structure and all.

In PostgreSQL, TRUNCATE is transactional and can be rolled back, but it takes a stronger lock and doesn't fire per-row triggers. Reach for DELETE when you need a filter, TRUNCATE to clear a whole table fast, DROP to get rid of the table.

INNER JOIN returns only rows that match in both tables. LEFT JOIN keeps all left rows plus matches. RIGHT JOIN keeps all right rows plus matches. FULL OUTER JOIN keeps everything from both sides. CROSS JOIN pairs every left row with every right row.

The one people forget is the NULL behavior: an outer join fills the non-matching side with NULLs, which is exactly how you find rows that have no match (a WHERE ... IS NULL after a LEFT JOIN).

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY has aggregated them. So you use WHERE for row conditions and HAVING for conditions on aggregates like COUNT or SUM.

You can't put an aggregate in a WHERE clause; that's what HAVING is for. A query can use both: WHERE trims rows first, then HAVING trims the resulting groups.

GROUP BY collapses rows that share the same values into one row per group, so you can summarize. Aggregate functions like COUNT, SUM, AVG, MIN, and MAX compute one value per group.

Every column in the SELECT list either appears in GROUP BY or is wrapped in an aggregate. Miss that and Postgres raises an error telling you the column must appear in the GROUP BY.

NULL means 'unknown', not zero or empty string. Any comparison with NULL using = or <> returns NULL (treated as not true), which is why you test it with IS NULL and IS NOT NULL.

NULL also spreads: NULL + 5 is NULL, and most aggregates skip NULLs (COUNT(column) ignores them while COUNT(*) doesn't). Use COALESCE to substitute a fallback value.

DISTINCT removes duplicate rows from a result set. DISTINCT ON is a PostgreSQL feature that keeps the first row for each group of the listed columns, which is handy for 'latest row per key' queries.

DISTINCT ON pairs with ORDER BY: the first row per group is decided by the sort order, so you order by the key then by whatever picks the winner.

ORDER BY sorts the result, ascending by default or descending with DESC. LIMIT caps how many rows come back, and OFFSET skips a number of rows first, which together give basic pagination.

One caution: OFFSET on large tables gets slow because the database still scans and discards the skipped rows. Keyset pagination (WHERE id > last_seen_id) scales better.

Numbers: INTEGER, BIGINT, NUMERIC (exact decimals for money), and REAL/DOUBLE PRECISION (floating point). Text: TEXT and VARCHAR(n). Dates and times: DATE, TIMESTAMP, TIMESTAMPTZ. Plus BOOLEAN, UUID, JSONB, arrays, and more.

A practical tip that shows judgment: use NUMERIC for money to avoid floating-point rounding, and prefer TIMESTAMPTZ over TIMESTAMP so time zones are handled correctly.

TEXT stores strings of any length. VARCHAR(n) is TEXT with a length limit. CHAR(n) is fixed-length and pads short values with spaces, which is rarely what you want.

In PostgreSQL there's no performance penalty for TEXT over VARCHAR; the only difference is the length check. Many teams just use TEXT and enforce limits with a CHECK constraint when they need one.

INSERT adds rows, UPDATE changes existing rows, and DELETE removes them. All three accept a WHERE clause where it makes sense, and all three support RETURNING to hand back the affected rows in the same statement.

Forgetting WHERE on an UPDATE or DELETE changes or removes every row. Running inside a transaction (BEGIN ... COMMIT) gives you a safety net to roll back a mistake.

Use INSERT ... ON CONFLICT. When a row would violate a unique or primary key constraint, you tell Postgres what to do instead: DO NOTHING to skip it, or DO UPDATE to merge in the new values.

This is atomic and avoids the race condition of 'check then insert' from application code. EXCLUDED refers to the row you tried to insert.

Constraints are rules the database enforces on your data: NOT NULL (no missing values), UNIQUE (no duplicates), PRIMARY KEY (unique and not null), FOREIGN KEY (must reference a valid row), CHECK (a custom condition), and DEFAULT (a fallback value).

Putting rules in constraints instead of application code means the database guarantees them for every write, no matter which app or script does the inserting.

An index is a separate data structure that lets PostgreSQL find rows without scanning the whole table, the way a book's index sends you straight to a page. The default is a B-tree, which handles equality and range lookups on ordered columns.

Indexes speed up reads but slow down writes, because every INSERT, UPDATE, or DELETE must also update the index, and they take disk space. So you index columns you filter and join on, not every column.

A scalar function returns one value per row: UPPER(name), LENGTH(text), ROUND(price). An aggregate function returns one value per group of rows: COUNT, SUM, AVG, MIN, MAX.

You mix them, but only aggregates trigger grouping. If a query has an aggregate and other columns, those columns must go in GROUP BY.

psql is PostgreSQL's interactive terminal client. Beyond running SQL, it has backslash meta-commands for exploring the database: \dt lists tables, \d tablename describes a table, \l lists databases, \du lists roles, and \timing shows how long each query takes.

Knowing these signals hands-on experience. \d+ gives extra detail like size and storage, and \e opens your editor for a longer query.

A transaction groups one or more statements so they succeed or fail together. You BEGIN, make changes, then COMMIT to save them or ROLLBACK to undo everything since BEGIN comes first.

Transactions give you the 'A' in ACID, atomicity: a transfer that debits one account and credits another either fully happens or fully doesn't, so you never leave money in limbo.

COUNT(*) counts every row, including rows where columns are NULL. COUNT(column) counts only rows where that column is not NULL, so it can return a smaller number. COUNT(DISTINCT column) counts how many different non-NULL values that column has.

The NULL behavior is the point being tested. If you want a true row count use COUNT(*); if you want how many rows have a value in a column use COUNT(column).

Both auto-generate increasing integer values for a column. SERIAL is the older shorthand that creates a sequence behind the scenes. GENERATED AS IDENTITY is the SQL-standard modern replacement and is the recommended choice today.

IDENTITY handles ownership and permissions of the underlying sequence more cleanly, and GENERATED ALWAYS AS IDENTITY blocks manual inserts into the column unless you override, which prevents accidental id collisions.

MVCC is Multi-Version Concurrency Control. Instead of locking rows for reads, PostgreSQL keeps multiple versions of each row, so every transaction sees a consistent snapshot of the data as of when it started. Readers don't block writers and writers don't block readers.

Under the hood, an UPDATE writes a new row version and marks the old one dead rather than overwriting in place. Each row carries hidden xmin and xmax columns that record which transactions created and expired it, and that's how visibility is decided.

Because MVCC leaves dead row versions behind after UPDATE and DELETE, tables would grow forever without cleanup. VACUUM reclaims that space for reuse, updates the visibility map, and refreshes planner statistics. Autovacuum runs it automatically in the background.

VACUUM (plain) makes space reusable but doesn't shrink the file; VACUUM FULL rewrites the table to return space to the OS but takes an exclusive lock. ANALYZE, which autovacuum also runs, keeps the statistics the planner relies on current.

B-tree is the default and fits most equality and range queries on ordered data. GIN suits multi-value columns like JSONB, arrays, and full-text search. GiST handles geometric and nearest-neighbor searches. BRIN is tiny and works on huge, naturally ordered tables. Hash is for equality only.

The judgment being tested is matching the index to the query shape. Indexing a JSONB column with B-tree won't help containment queries; that's what GIN is for.

EXPLAIN shows the plan the query planner intends to use, with estimated costs and row counts. EXPLAIN ANALYZE actually runs the query and reports real timings and real row counts, so you can compare the planner's estimate to reality.

Read it bottom-up: leaf nodes (scans) feed into joins and sorts. Watch for Seq Scan on large tables, a big gap between estimated and actual rows (stale statistics), and expensive sorts that spill to disk.

PostgreSQL supports Read Committed (the default), Repeatable Read, and Serializable. Higher levels prevent more anomalies. Read Uncommitted is accepted as syntax but behaves like Read Committed, since Postgres never shows uncommitted data.

Read Committed sees each statement's fresh snapshot. Repeatable Read locks in one snapshot for the whole transaction, preventing non-repeatable and phantom reads. Serializable adds detection of write conflicts and may abort a transaction you must retry.

Atomicity: a transaction's statements all commit or all roll back. Consistency: constraints keep the database valid. Isolation: concurrent transactions don't step on each other, controlled by isolation levels. Durability: committed data survives a crash.

PostgreSQL delivers atomicity and durability through the write-ahead log (WAL): changes are logged before they hit data files, so a crash can replay committed work. Isolation comes from MVCC and locks, consistency from constraints and triggers.

Use a JOIN when you need columns from both tables in the result. Use a subquery when you only need one table's rows filtered by a condition that involves another table, like existence or membership checks.

For existence, prefer EXISTS over IN with a subquery: EXISTS stops at the first match and handles NULLs cleanly, while IN with a NULL in the list can silently return no rows.

A common table expression is a named temporary result you define with WITH and reference in the main query. It makes complex queries readable by breaking them into named steps instead of nesting subqueries.

CTEs also enable recursion (WITH RECURSIVE) for hierarchical data like org charts or category trees. Note that since PostgreSQL 12, CTEs can be inlined by the planner, so they're no longer an optimization fence by default.

Window functions compute a value across a set of rows related to the current row, without collapsing them. GROUP BY returns one row per group; a window function keeps every row and adds the computed column alongside.

They're the right tool for running totals, rankings, and 'compare each row to its group'. ROW_NUMBER, RANK, LAG, LEAD, and SUM(...) OVER (...) are the common ones. The OVER clause defines the window with PARTITION BY and ORDER BY.

PostgreSQL stores JSON in two types. JSON keeps the exact text including whitespace and key order and reparses on each access. JSONB stores a decomposed binary form that's faster to query, supports indexing with GIN, and removes duplicate keys.

For almost anything you query or index, use JSONB. Operators like -> (get field), ->> (get field as text), and @> (contains) work on it, and GIN indexes make containment queries fast.

Normalization organizes tables to reduce redundancy and avoid update anomalies. First normal form: atomic columns, no repeating groups. Second: no partial dependency on part of a composite key. Third: no non-key column depends on another non-key column.

The practical goal is one fact in one place. You normalize to keep writes consistent, then selectively denormalize for read performance when profiling proves it's needed, not before.

Denormalize when a fully normalized schema forces expensive joins on a hot read path and you've measured that the joins are the bottleneck. Storing a computed total or a duplicated label trades write complexity for read speed.

The cost is consistency: duplicated data can drift, so you need triggers, application logic, or scheduled jobs to keep copies in sync. Normalize by default, denormalize deliberately with a reason you can defend.

A view is a saved query that runs fresh every time you select from it, so it always shows current data but costs the full query each read. A materialized view stores the query's results physically, so reads are fast but the data is a snapshot until you refresh it.

Use a plain view to simplify or secure access to complex queries. Use a materialized view for expensive aggregations that can tolerate slightly stale data, and refresh it on a schedule, ideally CONCURRENTLY so reads aren't blocked.

A function returns a value and runs inside the calling transaction, so it can't COMMIT or ROLLBACK on its own. A procedure (added in PostgreSQL 11) is called with CALL, doesn't have to return anything, and can manage transactions inside its body.

Both can be written in PL/pgSQL, PostgreSQL's procedural language, or in SQL. Reach for a procedure when you need transaction control in the routine itself, like batch jobs that commit in chunks.

A trigger runs a function automatically in response to INSERT, UPDATE, DELETE, or TRUNCATE on a table, either before or after the event. Common uses are auditing changes, maintaining a denormalized value, or validating complex rules.

The counterpoint worth stating: triggers hide logic from application code, so overusing them makes behavior hard to trace. Keep them for data-integrity concerns that must hold no matter which client writes.

A sequence is a database object that generates unique increasing numbers, which is what powers auto-incrementing ids. nextval advances it and returns the new value, currval returns the last value this session got, and setval resets it.

Sequences are not transactional for gap-freeness: a rolled-back transaction still consumes the number it drew, so ids can have gaps. That's expected; don't rely on sequences for consecutive, gap-free numbering.

CASE is SQL's conditional expression. It evaluates conditions in order and returns the first match's result, with an optional ELSE fallback. You use it to bucket values, pivot data, or apply conditional logic inside SELECT, ORDER BY, or aggregates.

A clean trick is conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows meeting a condition per group, effectively pivoting rows into columns.

Every PostgreSQL connection is a separate OS process with real memory overhead, so opening a fresh connection per request is expensive and the server has a hard connection limit. A pooler keeps a set of connections open and hands them out, so apps reuse them.

PgBouncer is the common external pooler, running in transaction or session mode. Application frameworks also pool. Without pooling, a traffic spike exhausts connections and new requests fail with 'too many clients'.

NUMERIC stores exact decimal values, so 0.1 plus 0.2 is exactly 0.3, while FLOAT and DOUBLE PRECISION are binary floating point and introduce tiny rounding errors that corrupt financial totals. For money and any value where exactness matters, use NUMERIC with a defined precision and scale.

For cleaning text, PostgreSQL ships functions like TRIM, LOWER, UPPER, LEFT, SUBSTRING, REPLACE, and SPLIT_PART, plus regexp_replace for pattern-based edits. Combining them in a query normalizes messy input without touching the application.

pg_dump produces a logical backup of one database as a script or archive, and pg_restore loads an archive-format dump. For a whole cluster, pg_dumpall includes roles and tablespaces. For large or always-on systems, physical base backups with WAL archiving support point-in-time recovery.

The distinction the key signal is: logical dumps are portable and good for smaller databases, while physical backups plus WAL are how you restore a big production system to a specific moment.

The WAL is an append-only log where PostgreSQL records every change before applying it to the data files. On commit, the relevant WAL is flushed to disk, which is what makes durability cheap: a crash replays committed WAL records to recover.

WAL is also the backbone of replication and point-in-time recovery. Streaming replication ships WAL to standbys, and archiving WAL lets you restore a base backup and replay forward to any moment.

PostgreSQL takes table-level and row-level locks automatically, and MVCC means plain reads take no row locks at all. When you need to lock rows you're about to modify, SELECT ... FOR UPDATE locks them so no other transaction can change them until you commit.

FOR UPDATE is the tool for read-modify-write patterns like reserving inventory. Add SKIP LOCKED to build a work queue where each worker grabs different rows, or NOWAIT to fail fast instead of waiting.

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL detects the cycle and aborts one transaction with a deadlock error, freeing the other. Your code catches that error and retries.

The prevention that experienced engineers cite: acquire locks in a consistent order across your application, keep transactions short, and touch rows in the same sequence everywhere. Consistent lock ordering removes most deadlocks entirely.

The planner is cost-based. It estimates the cost of alternative plans using table statistics (row counts, value distributions, most-common values) that ANALYZE collects, then picks the cheapest. That's why it might choose a sequential scan over an index when it expects to return most of a table.

When plans go wrong, the usual cause is stale or missing statistics, so estimated rows diverge from actual. Running ANALYZE, raising the statistics target on skewed columns, or adding an index the planner will actually use are the fixes.

Partitioning splits one logical table into smaller physical child tables by range, list, or hash on a partition key. Queries that filter on the key touch only the relevant partitions (partition pruning), and you can drop old data by dropping a whole partition instantly.

It earns its keep on very large tables, especially time-series data partitioned by date. The cost is management overhead and that the partition key should appear in most queries; partitioning a table that queries ignore the key on adds complexity for little gain.

Physical streaming replication ships WAL records from a primary to one or more standbys that replay them, keeping byte-for-byte copies for read scaling and failover. It's asynchronous by default; synchronous mode makes the primary wait for a standby to confirm, trading latency for zero data loss on failover.

Logical replication (publications and subscriptions) replicates selected tables at the row level, which supports version upgrades, selective sync, and replicating into a differently-structured target. Physical is for whole-cluster high availability; logical is for flexible, table-level flows.

Find the offenders first with pg_stat_statements, which ranks queries by total and mean time. Take the worst one and run EXPLAIN ANALYZE (BUFFERS) to see the real plan: is it a Seq Scan that should use an index, a bad row estimate from stale statistics, a sort spilling to disk, or an N+1 pattern from the app?

Then fix at the right layer and confirm with another measurement: add or adjust an index, rewrite the query, run ANALYZE, or tune work_mem for that workload. The structure, observe, hypothesize, verify, fix, confirm, matters more than any single tool.

An index-only scan answers a query entirely from the index without visiting the table heap, which is much faster because it reads less. It's possible when every column the query needs is in the index and the visibility map says the pages are all-visible.

A covering index deliberately includes extra columns with INCLUDE so more queries qualify for index-only scans. You put filter and join columns in the key and the returned columns in INCLUDE.

A partial index covers only rows matching a WHERE condition, so it's smaller and faster when queries always filter on that condition, like indexing only active rows. An expression index indexes the result of a function, so a query using the same expression can use it.

Both target the exact queries you run. Indexing LOWER(email) lets case-insensitive lookups use an index; a partial index on WHERE status = 'open' keeps a hot-path index tiny.

A plain CREATE INDEX takes a lock that blocks writes to the table while it builds, which is unacceptable on a busy production table. CREATE INDEX CONCURRENTLY builds it without that lock by scanning the table twice, at the cost of taking longer and not running inside a transaction block.

The gotcha to mention: if a concurrent build fails, it leaves an invalid index you must drop and recreate. Checking for and cleaning up invalid indexes is part of doing this safely.

PostgreSQL numbers transactions with a 32-bit counter that eventually wraps around. MVCC visibility depends on comparing these ids, so without maintenance old rows could suddenly look like they're in the future and become invisible. To prevent that, VACUUM freezes old rows, stamping them as permanently visible.

If autovacuum falls far behind on a high-write database, Postgres warns and ultimately stops accepting writes to force a freeze. Monitoring age(datfrozenxid) and keeping autovacuum healthy is how you avoid the emergency.

TOAST (The Oversized-Attribute Storage Technique) is how PostgreSQL stores values too big for a single 8 KB page. Large text, JSONB, or bytea values are compressed and, if still large, sliced into chunks stored in a separate TOAST table, transparently to your queries.

It matters for performance because reading a row doesn't fetch its TOASTed columns unless you select them, and the storage strategy (PLAIN, EXTENDED, EXTERNAL) affects compression. Wide JSONB columns you rarely read are cheap precisely because of TOAST.

A Heap-Only Tuple (HOT) update is an optimization where an UPDATE that doesn't change any indexed column can store the new row version on the same page and skip updating the indexes, which cuts write amplification and index bloat.

For a HOT update to happen there must be free space on the page, so lowering fillfactor (say to 90) leaves room on each page for in-place updates. On update-heavy tables that don't change indexed columns, this reduces bloat noticeably.

Extensions add packaged functionality to PostgreSQL with CREATE EXTENSION. pg_stat_statements tracks query performance, PostGIS adds geospatial types and functions, pgcrypto adds cryptographic functions, pg_trgm speeds up fuzzy text matching, and uuid-ossp generates UUIDs.

The extension system is a big reason teams choose Postgres: you get specialized capabilities without leaving the database. Naming pg_stat_statements and PostGIS shows you've worked with real deployments.

BIGINT identity keys are small, sequential, and index-friendly, so they pack tightly in B-trees and keep inserts local. UUIDs are globally unique without coordination, which suits distributed systems and client-generated ids, but random v4 UUIDs scatter inserts across the index and bloat it.

The nuance worth stating: if you need UUIDs, time-ordered variants (UUIDv7) restore insert locality, and their larger size still costs more space and memory than BIGINT. Pick BIGINT unless you specifically need decentralized id generation.

one primary and one or more streaming standbys, then add automated failover so a standby is promoted when the primary dies comes first. Tools like Patroni or repmgr handle leader election and promotion, and a connection router or virtual IP points clients at the current primary.

Round it out with the operational pieces: synchronous replication if zero data loss is required, regular base backups plus WAL archiving for point-in-time recovery, connection pooling in front, and monitoring on replication lag and disk. High availability is the whole stack, not a single flag.