30 Advanced PostgreSQL Interview Questions for Senior Backend Developers

Both are used for indexing complex data types that a standard B-Tree cannot handle efficiently.

• GIN (Generalized Inverted Index): Is optimized for queries that search for many matching items within a single composite value. It’s ideal for indexing arrays, JSONB documents, or full-text search vectors where you’re asking “what documents contain this specific key/word?”. GIN lookups are faster, but index builds are slower.
• GiST (Generalized Search Tree): Is a more flexible, balanced tree structure. It’s optimized for finding “nearby” or overlapping data. It’s ideal for geometric data types (finding intersecting shapes) and can also be used for full-text search, though it may be slower than GIN for that specific task. GiST has a faster build time.

An **index-only scan** is a highly efficient query plan where PostgreSQL can answer the entire query just by looking at the index, without needing to visit the table itself (the “heap”).

The conditions required are:

1. All columns needed by the query (in the `SELECT` list and `WHERE` clause) must be present in the index.
2. The visibility of the tuple must be verifiable from the visibility map. If a page is marked as “all-visible,” PostgreSQL knows it doesn’t need to check the heap for old row versions. A `VACUUM` operation is crucial for keeping the visibility map up-to-date.

`EXPLAIN ANALYZE` executes the query and provides the planner’s estimates along with the *actual* execution statistics.

• `cost`: An arbitrary unit representing the planner’s estimated cost. The first value is the startup cost (before returning the first row), and the second is the total cost.
• `rows`: The planner’s *estimate* of the number of rows the plan node will output.
• `actual time`: The *actual* time taken to execute the node, in milliseconds. The first value is the time to return the first row, and the second is the total time spent in that node.
• `loops`: The number of times the node was executed. For nested loop joins, the inner node will have a loop count equal to the number of rows from the outer node.

A large discrepancy between estimated `rows` and actual rows is a key indicator of poor planner statistics, which can lead to suboptimal query plans.

A partial index is an index built on a subset of a table’s rows, defined by a `WHERE` clause in the `CREATE INDEX` statement. The index only contains entries for rows that satisfy the condition.

A great use case is indexing a boolean “status” column where you frequently query for a small subset of values. For example, on a large `orders` table, creating an index like `CREATE INDEX ON orders (status) WHERE status = ‘pending’` would be very small and efficient for finding all pending orders, instead of indexing the entire table.

A **BRIN (Block Range Index)** index stores summary information (min and max values) for “block ranges”—groups of consecutive physical disk pages. It is extremely small compared to a B-Tree index.

It is most effective on very large tables where the data has a strong natural correlation with its physical order on disk. A perfect example is a table with a `created_at` timestamp column where rows are inserted in chronological order. A query for a specific date range can use the BRIN index to quickly rule out large block ranges that don’t contain relevant data.

MVCC is PostgreSQL’s core mechanism for handling concurrency. Instead of using locks for reading, it ensures that each transaction sees a consistent “snapshot” of the database. When a row is updated or deleted, PostgreSQL does not overwrite the old data. Instead, it creates a new version of the row (a new “tuple”) and marks the old one as expired for future transactions. Each tuple has hidden columns (`xmin`, `xmax`) that track which transaction created it and which expired it. This allows readers and writers to not block each other, leading to high concurrency.

• `SELECT … FOR UPDATE`: Acquires an exclusive lock on the selected rows. This prevents any other transaction from either modifying *or* acquiring a `FOR UPDATE` or `FOR SHARE` lock on those rows until the current transaction commits or rolls back. This is used when you intend to update the selected rows later in the transaction.
• `SELECT … FOR SHARE`: Acquires a shared lock on the selected rows. Other transactions can also acquire shared locks on the same rows (they can also read them), but they are prevented from modifying or deleting them until the current transaction ends.

In many databases, `REPEATABLE READ` can still suffer from phantom reads. However, PostgreSQL’s implementation is stricter. When a transaction begins in `REPEATABLE READ` mode, it takes a “snapshot” of the database. Any query within that transaction will *only* see data that was committed before that snapshot was taken. It will not see new rows inserted and committed by other transactions, nor will it see updates to existing rows. This behavior effectively prevents phantom reads, making PostgreSQL’s `REPEATABLE READ` behave very similarly to the `SERIALIZABLE` level in other systems.

Advisory locks are application-defined, cooperative locks. Unlike regular locks, they are not tied to a specific table or row but are managed using an arbitrary integer key. An application can acquire a lock on a number, and other sessions can check for the existence of that lock. They are useful for implementing locking strategies that don’t map cleanly to database rows, such as controlling access to a background job or ensuring only one process is performing a specific maintenance task at a time.

• Physical Replication (or Streaming Replication): Replicates the database on a physical level by streaming the Write-Ahead Log (WAL) records from the primary to the replica. The replica is a byte-for-byte copy of the primary. It’s simpler to set up and is used for creating hot standbys and read replicas.
• Logical Replication: Replicates data on a logical level based on a publication/subscription model. It allows for more flexibility, such as replicating only specific tables, replicating between different major PostgreSQL versions, or sending changes to a non-PostgreSQL system.

This determines when the primary server considers a transaction to be committed.

• Asynchronous (default): The primary commits the transaction as soon as it has written the change to its own WAL, without waiting for confirmation from the replica. This offers the best performance but risks data loss if the primary crashes before the change has been sent to the replica.
• Synchronous: The primary waits for at least one replica to confirm that it has received and written the transaction to its own logs before returning a success to the client. This guarantees zero data loss in case of a primary failure but introduces latency on write operations.

Opening a new database connection in PostgreSQL is an expensive process as it involves forking a new backend process. A connection pooler like **PgBouncer** maintains a pool of active connections to the database server. Application clients connect to PgBouncer instead of directly to PostgreSQL. PgBouncer then hands out an existing connection from its pool for the duration of a transaction (`transaction` pooling mode) or a session (`session` pooling mode). This dramatically reduces connection overhead and allows the database to support a much larger number of concurrent clients than it could handle directly.

• `JSON`: Stores an exact, literal copy of the input text. It preserves whitespace, duplicate keys, and key order. It is faster to write but slower to query as it must be parsed on every access.
• `JSONB`: Stores the data in a decomposed binary format. It does *not* preserve whitespace or duplicate keys (it keeps the last one). It is slightly slower to write due to the conversion, but it is significantly faster to query because the data is already parsed. It also supports indexing (using GIN indexes).

You should almost always use **`JSONB`** because its query performance and indexing capabilities are far superior for any real application.

Declarative partitioning allows you to divide a large table into smaller, more manageable pieces called partitions, based on a partition key. The parent table remains as a logical entity, but the data is physically stored in the child partitions.

Benefits:

• Query Performance: The query planner can use “partition pruning” to scan only the relevant partitions, drastically speeding up queries that filter by the partition key.
• Maintenance: Operations like dropping old data can be done by detaching or dropping a partition, which is nearly instantaneous compared to a massive `DELETE` operation.

An exclusion constraint guarantees that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. It’s a generalization of a `UNIQUE` constraint.

A classic use case is preventing overlapping time ranges. For example, in a meeting room booking table, you could create an exclusion constraint to ensure that no two bookings for the same room have overlapping time periods: `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)`.

A composite type (or row type) is a data type that represents the structure of a row or record. It is essentially a list of field names and their data types. You can use them to define the type of a column in a table or as the return type of a function, allowing you to return multiple values from a function in a structured way without needing to create a temporary table.

There are three main approaches:

1. Shared Database, Shared Schema: All tenants share the same tables, with a `tenant_id` column on every table to discriminate data. Row-Level Security (RLS) can be used to enforce data isolation automatically. This is the simplest to manage but can have “noisy neighbor” performance issues.
2. Shared Database, Separate Schemas: Each tenant gets their own schema within a single PostgreSQL database. This provides strong data isolation but is more complex to manage, especially with migrations.
3. Separate Databases: Each tenant gets their own dedicated database instance. This offers the best isolation and security but is the most expensive and complex to manage.

Due to PostgreSQL’s MVCC architecture, old row versions (“dead tuples”) are not immediately removed from disk. **`VACUUM`** is a maintenance process that reclaims the storage occupied by these dead tuples, making the space available for reuse. It also updates the visibility map (for index-only scans) and freezes transaction IDs to prevent transaction ID wraparound.

**`VACUUM FULL`** is a more aggressive version that reclaims more space by rewriting the entire table to a new file on disk, but it requires an exclusive lock on the table and is much slower. It should be used sparingly. Regular `VACUUM` (managed by `autovacuum`) is sufficient for most cases.

TOAST (The Oversized-Attribute Storage Technique) is how PostgreSQL handles values that are too large to fit in a standard 8KB data page. When a large value is stored, PostgreSQL will first try to compress it. If it’s still too large, it will break the value into smaller chunks and store them “out of line” in a separate TOAST table. The main table row then just stores a pointer to the data in the TOAST table.

The WAL is a log of all changes made to the database’s data files. Before any change is written to the actual data pages on disk, a record of that change is first written and flushed to the WAL. This ensures durability (the “D” in ACID). In the event of a crash, PostgreSQL can replay the WAL from the last checkpoint to restore the database to a consistent state, guaranteeing that no committed transactions are lost.

A checkpoint is a point in the WAL sequence at which all data files have been updated to reflect the information in the log. It’s a “safe point” for recovery. During a checkpoint, all “dirty” data pages from shared buffers are flushed to disk. This process limits the amount of WAL that needs to be replayed after a crash. Checkpoints are triggered automatically based on time (`checkpoint_timeout`) or the amount of WAL generated (`max_wal_size`).

An **aggregate function** (like `SUM()` or `COUNT()`) collapses a group of rows into a single result row. A **window function** performs a calculation across a set of rows that are related to the current row (the “window frame”) but does not collapse them. It returns a value for each row. They are defined using the `OVER()` clause, which can specify partitioning and ordering. They are perfect for calculating running totals, moving averages, or ranking results.

Foreign Data Wrappers allow you to access data from remote data sources as if they were local tables within your PostgreSQL database. You can query a remote PostgreSQL server, a MySQL database, a CSV file, or even a web service using standard SQL.

A great use case is integrating a legacy database into a new system. You can create a foreign table that points to a table in the old database, allowing you to join data between the new and old systems directly in a single query without complex ETL processes.

A **CTE**, defined with the `WITH` clause, creates a temporary, named result set that exists only for the duration of a single query. It helps break down complex queries into logical, readable steps.

A **recursive CTE** (`WITH RECURSIVE`) is a CTE that references itself. It’s used to query hierarchical or graph-like data. It consists of a non-recursive “anchor” member, a `UNION`, and a recursive member that joins with the CTE itself. This is ideal for traversing an organizational chart or a parts hierarchy.

`LISTEN` and `NOTIFY` provide a simple inter-process communication mechanism. A session can register to `LISTEN` on a specific channel name. Any session can then send a notification to that channel using `NOTIFY`, along with an optional payload string. All sessions listening on that channel will receive the notification asynchronously. This can be used to signal application backends that data has changed, avoiding the need for constant polling of the database.

Row-Level Security is a feature that allows you to control which rows a user is allowed to access or modify in a table. You define RLS policies on a table that filter rows based on the current user or other session characteristics. For example, in a multi-tenant application, you could create a policy that automatically adds a `WHERE tenant_id = current_setting(‘app.tenant_id’)` condition to every query, ensuring users can only see data belonging to their own organization.

PostgreSQL has sophisticated built-in full-text search capabilities. The process involves:

1. Converting text into a `tsvector`, which is a sorted list of distinct words (lexemes) that have been normalized (e.g., by stemming and removing stop words).
2. Creating a `tsquery` from user input, which also converts search terms into lexemes and can include operators like `&` (AND), `|` (OR), and `!` (NOT).
3. Matching the `tsquery` against the `tsvector` using the `@@` operator.

For performance, you would create a GIN or GiST index on the `tsvector` column.

A `LATERAL` join is a type of `JOIN` that allows a subquery in the `FROM` clause to reference columns from preceding items in the `FROM` list. It effectively lets you run a correlated subquery for each row of the outer table. This is very powerful for “top-N-per-group” queries, such as finding the 3 most recent posts for each user, a task that is difficult to express with a standard `JOIN`.

Both can execute actions in response to database events, but they work very differently.

• A **Trigger** is a function that executes *for each affected row* either before or after an `INSERT`, `UPDATE`, or `DELETE` operation. It is the more common and intuitive choice.
• A **Rule** is a mechanism that rewrites the incoming query. It modifies the query tree before the query is executed. Rules are more powerful but also more complex and can have surprising behavior, especially with `UPDATE` or `DELETE` statements. They are rarely used in modern applications.

`work_mem` specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. This memory is allocated on a per-operation basis within a query. Increasing `work_mem` can significantly improve the performance of complex sorts (`ORDER BY`), window functions, and hash joins by allowing these operations to be performed entirely in memory. However, setting it too high can lead to memory exhaustion, as a single complex query could have multiple operations that each request this amount of memory.