PostgreSQL is the most advanced open-source relational database and a favourite of modern product teams. These PostgreSQL interview questions cover data types, constraints and basic SQL for freshers, then MVCC, VACUUM, index types, JSONB, query plans, partitioning and replication for experienced developers.
30 questions · Updated
Fundamentals, definitions and simple scenarios. Good for freshers and warm-ups.
PostgreSQL is an open-source, object-relational database known for standards compliance, reliability and extensibility. It supports advanced data types such as JSONB, arrays and ranges, full-text search, powerful indexing, window functions and CTEs, and extensions such as PostGIS for geospatial data and pgvector for AI embeddings.
Both create auto-incrementing integers.
SERIAL is older PostgreSQL-specific shorthand that creates a sequence and a default value.
GENERATED ALWAYS AS IDENTITY is the SQL-standard approach introduced in PostgreSQL 10, with clearer ownership and permissions, and it is recommended for new tables.
UUIDs are another option for distributed systems.
JSON stores the exact text, keeping whitespace, key order and duplicate keys, and must be parsed each time it is read.
JSONB stores a decomposed binary format, which is slightly slower to write but much faster to query, and supports GIN indexes and operators such as @> for containment.
JSONB is the usual choice.
\l lists databases, \c dbname connects to one, \dt lists tables, \d table describes a table with its columns and indexes, \dn lists schemas, \du lists roles, \x toggles expanded output, \timing shows query time, \i file runs a SQL file and \q quits. \? shows all meta-commands.
A schema is a namespace inside a database that contains tables, views and functions.
Every database has a public schema by default.
Schemas organise objects (for example separate schemas for app data and reporting), control access with privileges, and allow the same table name in different schemas. search_path decides which schemas are searched when a name is not qualified.
integer and bigint, numeric for exact money values, text (with no performance difference from varchar), boolean, timestamptz for points in time (stored in UTC), date, uuid, jsonb, arrays such as text[], and enums. timestamptz should be preferred over timestamp without time zone for events, to avoid time zone bugs.
RETURNING returns values from rows affected by INSERT, UPDATE or DELETE in the same statement, such as INSERT INTO orders (...) VALUES (...) RETURNING id, created_at. It saves a second query to read generated IDs, defaults or updated values, and works well with upserts and bulk operations.
INSERT ... ON CONFLICT (column) DO UPDATE SET ... updates the existing row when a unique constraint would be violated, using EXCLUDED to refer to the new values, for example ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name.
ON CONFLICT DO NOTHING skips duplicates.
PostgreSQL 15 also added the SQL-standard MERGE statement.
Users and groups are both roles.
CREATE ROLE app LOGIN PASSWORD '...' creates a login role, and GRANT gives privileges such as SELECT, INSERT and UPDATE on tables, or USAGE on schemas.
Roles can be members of other roles to share privileges, ALTER DEFAULT PRIVILEGES applies grants to future tables, and row-level security policies can restrict which rows each role sees.
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY with ON DELETE actions, CHECK for conditions such as price >= 0, and EXCLUDE constraints, which prevent overlapping values, for example two bookings for the same room in overlapping time ranges using a GiST index. Constraints can be deferred until commit, which helps with circular references.
OFFSET pagination (LIMIT 20 OFFSET 1000) is simple but the database still reads and discards all skipped rows, so later pages get slower, and rows can be missed or repeated when data changes. Keyset pagination filters on the last seen value, such as WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20, which uses an index and stays fast on any page.
Applied problems, trade-offs and questions about your own projects.
Multi-Version Concurrency Control means updates create new row versions instead of overwriting rows, and each transaction sees a consistent snapshot of data.
Readers never block writers and writers never block readers.
The old row versions (dead tuples) remain until VACUUM cleans them up.
VACUUM removes dead tuples left by updates and deletes so the space can be reused, updates the visibility map, and prevents transaction ID wraparound.
VACUUM FULL rewrites the table to return space to the operating system but locks it.
Autovacuum runs automatically; on busy tables its settings may need tuning to avoid table bloat.
B-tree (the default, for equality and range queries), Hash (equality only), GIN (for JSONB, arrays and full-text search), GiST and SP-GiST (for geometric data, ranges and nearest-neighbour search) and BRIN (very small indexes for huge tables with naturally ordered data, such as timestamps). Partial indexes and expression indexes add flexibility.
It shows the plan tree with each node, such as Seq Scan, Index Scan, Bitmap Heap Scan, Hash Join or Nested Loop, with estimated and actual rows, time and loops.
I look for the slowest nodes, sequential scans on large tables, and big differences between estimated and actual row counts, which suggest outdated statistics.
Adding BUFFERS shows I/O.
A materialized view stores the result of a query physically, so expensive aggregations for dashboards and reports are read instantly.
The data does not update automatically; REFRESH MATERIALIZED VIEW recalculates it, and adding CONCURRENTLY (which requires a unique index) refreshes without blocking readers.
Refreshes are usually scheduled or triggered after data loads.
Text is converted into a tsvector of normalised words (lexemes) using a language configuration, and searches use a tsquery, matched with the @@ operator, such as to_tsvector('english', body) @@ websearch_to_tsquery('english', 'fast cars'). A GIN index on the tsvector, often in a generated column, makes it fast, and ts_rank orders results by relevance. pg_trgm helps with fuzzy and partial matching.
The default is Read Committed, where each statement sees data committed before it began.
Repeatable Read gives the whole transaction one snapshot.
Serializable uses Serializable Snapshot Isolation, which detects conflicts and aborts one transaction with a serialization failure, so the application must retry.
Read Uncommitted behaves like Read Committed in PostgreSQL.
SELECT ... FOR UPDATE locks selected rows until the transaction ends, so other transactions cannot change them; FOR UPDATE SKIP LOCKED lets several workers take different rows from a jobs table without blocking each other. Advisory locks are application-defined locks on a number, such as pg_advisory_lock(42), used to make sure only one process runs a job or migration at a time.
pg_stat_statements to find the most expensive queries, PostGIS for geospatial data, pgvector for storing and searching AI embeddings, pg_trgm for fuzzy text search, pgcrypto for hashing and encryption functions, uuid-ossp (now mostly replaced by the built-in gen_random_uuid()), hstore, and TimescaleDB or pg_partman for time-series data and partitions.
Functions can be written in SQL or PL/pgSQL, which adds variables, conditions, loops and error handling, and are called in queries.
Procedures (CALL) can manage transactions.
A trigger calls a trigger function before or after INSERT, UPDATE or DELETE, for example to update an updated_at column or write an audit record.
Logic in triggers should stay small and well documented.
A LATERAL subquery can refer to columns from tables earlier in the FROM clause, so it runs for each row, like a for-each loop.
A classic use is fetching the latest three orders for every customer: FROM customers c CROSS JOIN LATERAL (SELECT * FROM orders o WHERE o.customer_id = c.id ORDER BY created_at DESC LIMIT 3) recent.
It is also used with set-returning functions.
System design, deep internals, leadership and tough follow-ups.
Declarative partitioning splits a large table into smaller partitions by range (for example by month), list (by region) or hash.
Queries that filter on the partition key only scan the relevant partitions (partition pruning), and old data can be dropped instantly by detaching a partition.
It suits very large time-series or log tables.
Streaming replication sends the write-ahead log (WAL) from the primary to standby servers, which can serve read queries (hot standby).
It can be asynchronous or synchronous.
Logical replication replicates selected tables and allows different versions, useful for upgrades.
Tools such as Patroni manage automatic failover, and PgBouncer handles connection pooling.
Missing or unused indexes, table bloat from poorly tuned autovacuum, outdated statistics, too many connections without a pooler like PgBouncer, long-running transactions that block vacuum and hold locks, N+1 queries from the application, and settings such as shared_buffers, work_mem and effective_cache_size left at defaults. pg_stat_statements helps find the most expensive queries.
Each PostgreSQL connection is a separate process using memory, so thousands of application connections overload the server.
PgBouncer keeps a small pool of real connections and shares them.
Session mode assigns a server connection for the whole client session, transaction mode (the most common) assigns one only for each transaction, and statement mode per statement.
Transaction mode limits session-level features such as session variables.
Every change is first written to the write-ahead log (WAL), which makes crash recovery possible and powers replication. pg_dump creates logical backups of a database, good for smaller databases and migrations. Physical backups with pg_basebackup or tools such as pgBackRest, combined with archived WAL files, allow point-in-time recovery to any moment, for example just before an accidental DELETE.
Major versions change the storage format, so you cannot simply replace the binaries. pg_upgrade upgrades in place quickly, especially with --link, but needs downtime. pg_dump and restore is simple but slow for large databases.
Logical replication from the old to the new version allows near-zero downtime: replicate, test, then switch applications over.
Always test extensions and queries on the new version first.
The pgvector extension adds a vector column type for embeddings created by AI models, and distance operators such as <=> for cosine distance to find the most similar items, for example for semantic search or retrieval-augmented generation.
HNSW or IVFFlat indexes make approximate nearest-neighbour search fast.
Combining it with normal SQL filters and full-text search in one database is a major advantage.
Create indexes with CREATE INDEX CONCURRENTLY so writes are not blocked.
Adding a column with a constant default is instant in modern versions, but adding NOT NULL or changing types can rewrite or lock the table, so add constraints as NOT VALID and validate them separately.
Set lock_timeout so a migration fails fast instead of queueing behind long transactions and blocking everything, and keep changes backward compatible.
See all Database Engineer interview questions, covering SQL, MySQL, PostgreSQL, Oracle, SQL Server and more.