Top 50 PostgreSQL Interview Questions and Answers

PostgreSQL is a powerful, open-source object-relational database system.

• Known for strict standards compliance, extensibility, and advanced features (JSONB, arrays, full-text search, custom types).
• Free and open-source under a permissive license, with a strong reputation for reliability and data integrity.

Both are popular open-source RDBMSs, but differ in philosophy:

• PostgreSQL: emphasizes strict SQL standards compliance, advanced data types (JSONB, arrays), and extensibility.
• MySQL: historically prioritized simplicity and read speed, popular for web apps via the LAMP stack.
• PostgreSQL generally offers richer features out of the box (e.g., native full outer joins, window functions historically earlier, CTEs).

A Sequence is a database object that generates a series of unique numeric values, typically used for auto-incrementing IDs.

CREATE SEQUENCE order_id_seq;
SELECT nextval('order_id_seq');

• Underlies the SERIAL and GENERATED ALWAYS AS IDENTITY column types.

SERIAL is shorthand for creating an auto-incrementing integer column, backed by a sequence.

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

• Under the hood, PostgreSQL creates an associated sequence and sets the column's default to nextval() on that sequence.

Both auto-generate incrementing values, but GENERATED ... AS IDENTITY (SQL-standard, added in PostgreSQL 10) is preferred:

• SERIAL: a PostgreSQL-specific convenience that creates a separate sequence object with looser ownership semantics.
• GENERATED ALWAYS AS IDENTITY: SQL-standard syntax, more tightly bound to the table, and prevents accidental manual inserts overriding the sequence (unless OVERRIDING SYSTEM VALUE is used).

CREATE TABLE users (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

A Schema is a namespace within a database that groups tables, views, and other objects.

• A single PostgreSQL database can contain multiple schemas (e.g., public, sales, hr).
• Useful for organizing objects and managing permissions without needing separate databases, which cannot be joined across in a single query.

Both store JSON data, but differently:

• JSON: stores an exact textual copy of the input, preserving whitespace/key order, but slower to query.
• JSONB: stores data in a binary, decomposed format — faster to query and supports indexing (e.g., GIN indexes), but doesn't preserve original formatting or key order.
• JSONB is almost always preferred unless exact text preservation is required.

SELECT data->'name' FROM users WHERE data @> '{"active": true}';

PostgreSQL natively supports array columns, letting you store multiple values of the same type in a single column.

CREATE TABLE posts (id SERIAL, tags TEXT[]);
INSERT INTO posts (tags) VALUES ('{"sql","database"}');
SELECT * FROM posts WHERE 'sql' = ANY(tags);

• Useful for simple multi-value fields without needing a separate join table.

A CTE defines a named temporary result set using WITH, usable within the main query.

WITH RECURSIVE subordinates AS (
  SELECT id, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

• Recursive CTEs are ideal for hierarchical data like org charts or category trees.

Window functions compute values across a set of related rows without collapsing them into one row (unlike GROUP BY).

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

• Common functions: ROW_NUMBER(), RANK(), LAG(), LEAD(), and aggregate functions used with OVER().

Both are based on a stored query, but differ in when they're computed:

• View: recomputed every time it's queried — always reflects live data.
• Materialized View: computed once and stored physically, must be manually refreshed to see new data, but reads are much faster.

CREATE MATERIALIZED VIEW sales_summary AS SELECT ...;

Use the REFRESH MATERIALIZED VIEW command:

REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

• CONCURRENTLY allows the view to remain queryable during the refresh (requires a unique index on the view), avoiding a full lock.

VACUUM reclaims storage occupied by "dead" rows left behind by PostgreSQL's MVCC model after updates/deletes.

• Without regular vacuuming, tables can bloat significantly in size over time.
• Also updates statistics used by the query planner for better execution plans.

VACUUM ANALYZE orders;

Both reclaim space, but with different trade-offs:

• VACUUM: reclaims space for reuse within the table, without actually shrinking the file size on disk; runs without locking the table for reads/writes.
• VACUUM FULL: rewrites the entire table to reclaim disk space and shrink the file, but requires an exclusive lock, blocking all access during the operation.

Autovacuum is a background process that automatically runs VACUUM and ANALYZE on tables as they accumulate dead rows, based on configurable thresholds.

• Removes the need for manual vacuum scheduling in most cases.
• Can be tuned per-table for high-churn tables that need more aggressive vacuuming.

MVCC allows multiple transactions to read and write data concurrently without blocking each other, by keeping multiple versions of a row.

• Readers see a consistent snapshot of the data as of when their transaction started, unaffected by concurrent writes.
• Old row versions become "dead tuples" once no transaction needs them, later cleaned up by VACUUM.

Partitioning splits a large table into smaller physical tables (partitions), while queries still target the parent table transparently.

CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

• Improves query performance (partition pruning) and simplifies maintenance for very large, time-series-like tables.

Both split data across child tables, but declarative partitioning (added in PostgreSQL 10) is the modern approach:

• Declarative partitioning: built-in syntax (PARTITION BY), automatic routing of inserts to the correct partition, better planner integration.
• Inheritance-based partitioning (legacy): uses table inheritance with manual CHECK constraints and triggers to route data — more flexible but far more manual and error-prone.

PostgreSQL supports several index types for different use cases:

• B-tree (default): general-purpose, good for equality and range queries.
• Hash: optimized for simple equality lookups.
• GIN (Generalized Inverted Index): ideal for JSONB, arrays, and full-text search.
• GiST: supports geometric data and nearest-neighbor searches.
• BRIN: extremely compact indexes for very large, naturally-ordered tables (like time-series data).

Choice depends on the data type and query pattern:

• B-tree: best for scalar values with equality/range comparisons (=, <, >, BETWEEN).
• GIN: best for composite/multi-valued data like JSONB, arrays, or full-text search vectors, where you're checking for containment (@>) or membership.

EXPLAIN ANALYZE shows both the planned and actual execution details of a query, including real timing and row counts.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

• Unlike plain EXPLAIN (which only estimates), this actually runs the query, so use with caution on write statements or expensive queries in production.

Extensions add extra functionality to PostgreSQL beyond the core engine.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis;

• pgcrypto: cryptographic functions (hashing, encryption).
• PostGIS: adds geographic/spatial data types and queries.
• uuid-ossp: functions for generating UUIDs.

pg_stat_activity is a system view showing information about all currently active connections/queries.

SELECT pid, state, query FROM pg_stat_activity WHERE state = 'active';

• Useful for identifying long-running queries, blocked sessions, or terminating a stuck connection with pg_terminate_backend(pid).

FDWs allow PostgreSQL to query external data sources as if they were local tables.

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw ...;

• Can connect to other PostgreSQL instances, MySQL, CSV files, and more — enabling federated queries across systems.

RLS restricts which rows a user can see or modify in a table, enforced automatically by the database rather than the application.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_orders ON orders
  USING (user_id = current_setting('app.current_user_id')::int);

• Useful for multi-tenant applications where different users must only see their own data.

Both remove rows, but behave differently:

• DELETE: fires row-level triggers, can be filtered with WHERE, and is transactional (rollback-able).
• TRUNCATE: much faster since it doesn't scan rows, fires only statement-level triggers (not row-level ones), but is still transactional in PostgreSQL (unlike some other databases).

A Trigger runs a function automatically in response to table events.

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

• In PostgreSQL, triggers always call a separate trigger function (often written in PL/pgSQL), unlike some databases where trigger logic is inline.

PL/pgSQL is PostgreSQL's built-in procedural language for writing functions with control-flow logic (loops, conditionals, exception handling).

CREATE FUNCTION add(a INT, b INT) RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Both are stored, reusable logic, but differ in transaction control (since PostgreSQL 11 introduced true procedures):

• Function: must return a value, can be used inline in queries, and cannot manage transactions internally (COMMIT/ROLLBACK).
• Procedure (CREATE PROCEDURE, called via CALL): may or may not return a value, and can control transactions within its own body.

PostgreSQL supports four standard SQL isolation levels:

• Read Uncommitted: treated the same as Read Committed (PostgreSQL never actually allows dirty reads).
• Read Committed (default): sees only data committed before each statement begins.
• Repeatable Read: sees a consistent snapshot for the whole transaction.
• Serializable: strictest — behaves as if transactions ran one at a time, detecting and rejecting conflicting concurrent transactions.

PostgreSQL defaults to Read Committed.

• Each statement within a transaction sees a fresh snapshot of committed data as of when that statement began — differs from Repeatable Read, where the entire transaction sees one snapshot.

A deadlock occurs when transactions form a cycle of waiting on each other's locks.

• PostgreSQL automatically runs periodic deadlock detection; when found, it aborts one of the transactions (raising an error) to break the cycle.
• Applications should catch the deadlock error and retry the aborted transaction.

Both enforce uniqueness, but with subtle differences:

• Primary Key: implicitly NOT NULL, and a table can have only one.
• Unique Constraint: allows multiple NULL values (since NULLs aren't considered equal to each other), and a table can have several unique constraints.

A CHECK constraint ensures values in a column satisfy a specific boolean condition.

CREATE TABLE products (
  price NUMERIC CHECK (price > 0)
);

• Enforced automatically on every insert/update — attempts to violate it are rejected with an error.

LISTEN/NOTIFY provides a simple publish-subscribe messaging system built into PostgreSQL.

LISTEN new_order;
NOTIFY new_order, 'order_id:123';

• Allows applications to receive real-time notifications of events without polling the database.

Logical Replication replicates data changes at the row/statement level rather than copying raw disk blocks.

• Allows replicating specific tables (not the whole database), between different PostgreSQL versions, or even to non-PostgreSQL systems.
• Uses a publish/subscribe model: a publication on the source, a subscription on the target.

Streaming Replication continuously ships the Write-Ahead Log (WAL) from a primary server to one or more standby servers in near real-time.

• Standbys can serve read-only queries (hot standby), providing both high availability and read scaling.
• Can be synchronous (waits for standby confirmation) or asynchronous (faster, slight replication lag).

The WAL records every change to the database before it's applied to the actual data files.

• Ensures durability and crash recovery — if the server crashes, changes can be replayed from the WAL to restore a consistent state.
• Also the foundation for streaming replication and point-in-time recovery (PITR).

A Tablespace defines a location on disk where PostgreSQL stores data files for specific tables/indexes.

CREATE TABLESPACE fast_storage LOCATION '/mnt/ssd/pgdata';
CREATE TABLE big_table (...) TABLESPACE fast_storage;

• Useful for placing frequently accessed tables on faster storage (SSDs) while archiving cold data elsewhere.

All three combine results from two queries, but with different set logic:

• UNION: combines rows from both queries, removing duplicates (UNION ALL keeps duplicates).
• INTERSECT: returns only rows present in both result sets.
• EXCEPT: returns rows from the first query that are not present in the second.

ILIKE performs a case-insensitive pattern match, unlike the standard LIKE which is case-sensitive.

SELECT * FROM users WHERE name ILIKE 'john%';

• Matches "John", "JOHN", "john", etc. — a PostgreSQL-specific convenience not part of the SQL standard.

A Composite Type groups multiple fields into a single structured type, similar to a struct.

CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);
CREATE TABLE users (id SERIAL, home_address address);

• Useful for logically grouping related fields without creating a separate table.

Functionally, they're nearly identical in PostgreSQL:

• TEXT: unlimited length, no length checking.
• VARCHAR(n): same storage as TEXT internally, but enforces a maximum length of n characters.
• Unlike some databases, PostgreSQL doesn't penalize TEXT performance-wise — many PostgreSQL developers prefer TEXT unless a length limit is a real business rule.

A LATERAL join allows a subquery on the right side to reference columns from tables on the left side of the join — not possible with a regular join/subquery.

SELECT u.name, recent.*
FROM users u,
LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) recent;

• Useful for "top N per group" queries.

UPSERT (insert-or-update) is implemented via INSERT ... ON CONFLICT.

INSERT INTO users (id, email) VALUES (1, 'a@x.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

• If a row with a conflicting key already exists, it updates instead of throwing a duplicate-key error.

RETURNING returns values from rows affected by an INSERT, UPDATE, or DELETE, without a separate SELECT.

INSERT INTO users (name) VALUES ('Alice') RETURNING id;

• Saves a round-trip when you need the generated ID or updated values immediately after a write.

They're optimized for different query types:

• B-tree: supports equality and range queries (<, >, BETWEEN), and is the default for most use cases.
• Hash: supports only equality (=) comparisons, but can be marginally faster/smaller for that specific case. Historically less reliable before PostgreSQL 10 added WAL-logging for hash indexes.

Connection pooling reuses a small set of database connections across many client requests, instead of opening a new connection per request.

• PostgreSQL connections are relatively expensive (each spawns a backend process), so a high number of short-lived connections can exhaust resources.
• PgBouncer sits between the application and PostgreSQL, pooling and reusing connections, dramatically reducing overhead under high concurrency.

pg_dump creates a logical backup of a PostgreSQL database.

pg_dump -U postgres mydb > backup.sql
psql -U postgres mydb < backup.sql

• Can output plain SQL or a custom compressed format usable with pg_restore, which also allows selective/parallel restores.

They differ in how strictly the primary waits for replicas:

• Synchronous: the primary waits for at least one standby to confirm it received the WAL data before acknowledging a commit — guarantees no data loss on failover, at the cost of higher write latency.
• Asynchronous (default): the primary commits immediately without waiting for standbys — faster writes, but a small risk of data loss if the primary fails before replication catches up.