PostgreSQL and SQL - Interview Preparation
1. Basic Questions
Q1: What is PostgreSQL and why is it popular?
PostgreSQL is a powerful, open-source object-relational database system (ORDBMS). It has more than 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
- Relational: It organizes data into tables with predefined relationships.
- Object-Oriented: It supports features like table inheritance and function overloading.
- ACID Compliant: Ensures data integrity through Atomicity, Consistency, Isolation, and Durability.
- Extensible: Allows users to define their own data types, index types, and functional languages.
- Conforms to SQL Standards: Highly compliant with the SQL:2023 standard.
It is popular because it handles complex queries well, supports advanced data types like JSONB and Arrays, and has a vibrant community that continuously adds features like logical replication and window functions.
-- Simple query to show version
SELECT version();
Q2: What are the primary data types in PostgreSQL?
PostgreSQL provides a rich set of data types:
- Numeric:
integer,bigint,decimal,numeric,real,double precision. - Character:
char(n),varchar(n),text. - Binary:
bytea. - Date/Time:
timestamp,date,time,interval. - Boolean:
boolean. - Geometric:
point,line,lseg,box,path,polygon,circle. - Network Address:
inet,cidr,macaddr. - JSON:
json,jsonb. - Array: Variable-length multidimensional arrays of any base type.
- UUID: Universally Unique Identifier.
Using the correct data type is crucial for performance and storage efficiency. For example, jsonb is usually preferred over json because it's stored in a binary format and supports indexing.
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username TEXT NOT NULL,
metadata JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
Q3: Explain the ACID properties in a database.
ACID stands for:
- Atomicity: The "all or nothing" principle. A transaction consists of multiple steps, and either all of them succeed, or none of them do. If a failure occurs midway, the database is rolled back to its previous state.
- Consistency: Ensures that a transaction brings the database from one valid state to another valid state, maintaining all predefined rules (constraints, cascades, triggers).
- Isolation: Concurrent transactions must not interfere with each other. The result of running multiple transactions simultaneously should be the same as if they were run sequentially.
- Durability: Once a transaction is committed, its changes are permanent and survive system failures (like a power outage) by being written to non-volatile storage (the WAL - Write Ahead Log).
PostgreSQL implements ACID using MVCC (Multi-Version Concurrency Control) and a robust WAL system.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- If this fails, neither update is applied.
Q4: What is a Primary Key?
A primary key is a column or a group of columns that uniquely identifies each row in a table.
- Uniqueness: No two rows can have the same primary key value.
- Non-null: A primary key column cannot contain NULL values.
- One per table: A table can have only one primary key.
- Indexing: PostgreSQL automatically creates a unique B-tree index on the primary key column to ensure fast lookups.
Primary keys are essential for relational integrity, acting as the target for foreign key references in other tables.
ALTER TABLE products ADD PRIMARY KEY (id);
Q5: What is a Foreign Key?
A foreign key is a column or group of columns used to establish a link between data in two tables. It points to the primary key (or a unique key) of another table.
- Referential Integrity: Ensures that the value in the foreign key column exists in the parent table.
- Constraints: You can define actions for when the parent row is deleted or updated (e.g.,
ON DELETE CASCADE,ON DELETE SET NULL).
Foreign keys prevent "orphaned" data and maintain the logical structure of the database.
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE
);
Q6: What is the difference between DELETE and TRUNCATE?
DELETE:- DML (Data Manipulation Language) command.
- Can have a
WHEREclause to delete specific rows. - Generates transaction logs for every row deleted.
- Triggers are fired.
- Slower for large tables.
TRUNCATE:- DDL (Data Definition Language) command.
- Removes all rows from a table.
- Minimally logged (faster than delete).
- Does not fire row-level
DELETEtriggers (Postgres has separate statement-levelTRUNCATEtriggers). - Keeps identity/SERIAL counters by default; add
RESTART IDENTITYto reset them. - Transactional in Postgres: it can be rolled back if run inside a transaction.
- Requires an exclusive lock on the table.
DELETE FROM logs WHERE created_at < '2023-01-01';
TRUNCATE TABLE temp_data; -- sequences keep counting
TRUNCATE TABLE temp_data RESTART IDENTITY; -- sequences reset to 1
Q7: What is a "Constraint" and name some common ones.
Constraints are rules enforced on data columns to ensure the accuracy and reliability of the data.
- NOT NULL: Prevents NULL values.
- UNIQUE: Ensures all values in a column are different.
- PRIMARY KEY: Combination of NOT NULL and UNIQUE.
- FOREIGN KEY: Ensures referential integrity.
- CHECK: Ensures that the value satisfies a specific condition.
- EXCLUSION: Ensures 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 be null.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
age INT CHECK (age >= 18),
email TEXT UNIQUE NOT NULL
);
Q8: Explain the SELECT statement and its basic components.
The SELECT statement is used to retrieve data from one or more tables.
- SELECT: Specifies the columns to return.
- FROM: Specifies the table(s).
- WHERE: Filters the rows based on a condition.
- GROUP BY: Groups rows that have the same values in specified columns into summary rows.
- HAVING: Filters groups created by
GROUP BY. - ORDER BY: Sorts the results.
- LIMIT/OFFSET: Limits the number of rows returned and skips a specific number of rows.
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC;
Q9: What are the different types of Joins?
Joins are used to combine rows from two or more tables based on a related column.
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. If no match, results are NULL on the right side.
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.
- CROSS JOIN: Returns the Cartesian product of the two tables (every row from left combined with every row from right).
SELECT users.name, orders.id
FROM users
INNER JOIN orders ON users.id = orders.user_id;
Q10: What is a "Schema" in PostgreSQL?
A schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators.
- Organization: Helps group objects into logical sets for easier management.
- Multi-tenancy: Different schemas can be used for different users or applications within the same database.
- Search Path: The
search_pathconfiguration parameter determines the order in which schemas are searched when an object is referenced without a schema prefix.
By default, every database has a schema named public.
CREATE SCHEMA sales;
CREATE TABLE sales.revenue (id INT, amount DECIMAL);
Q11: How do you handle "NULL" values in SQL?
NULL represents a missing or unknown value.
- Comparison: You cannot use
=or<>with NULL. You must useIS NULLorIS NOT NULL. - COALESCE: Returns the first non-null argument in a list. Great for setting defaults.
- NULLIF: Returns NULL if two arguments are equal.
- Aggregate functions: Most aggregate functions (like
SUM,AVG) ignore NULL values, exceptCOUNT(*).
SELECT COALESCE(phone, 'N/A') FROM contacts;
SELECT * FROM users WHERE email IS NULL;
Q12: What is the purpose of the WHERE clause vs the HAVING clause?
WHERE: Filters rows before any grouping or aggregation is performed. It works on individual column values.HAVING: Filters groups after theGROUP BYclause has been applied. It is typically used with aggregate functions (likeSUM,COUNT).
-- Valid
SELECT dept, SUM(salary) FROM emp WHERE salary > 1000 GROUP BY dept;
-- Invalid (salary is not an aggregate)
SELECT dept FROM emp GROUP BY dept HAVING salary > 1000;
-- Valid
SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 10;
Q13: What is an "Index" and why use it?
An index is a separate data structure (usually a B-tree) that allows the database to find rows much faster than scanning the entire table (sequential scan).
- Speed: Drastically improves
SELECTperformance for filtered or joined queries. - Overhead: Slows down
INSERT,UPDATE, andDELETEoperations because the index must also be updated. - Unique index: Ensures that all values in a column are distinct, while also providing fast lookups.
CREATE INDEX idx_user_email ON users(email);
Q14: What is a "View"?
A view is a virtual table based on the result-set of an SQL statement. It does not store data itself (unless it's a Materialized View).
- Security: Can restrict access to specific columns or rows of a base table.
- Simplicity: Can hide complex join logic behind a simple table name.
- Consistency: Provides a stable interface even if the underlying table structure changes.
CREATE VIEW active_users AS
SELECT id, username FROM users WHERE is_active = TRUE;
Q15: What is a "Transaction"?
A transaction is a single unit of work that contains one or more SQL statements. Transactions are started with BEGIN and ended with either COMMIT (to save changes) or ROLLBACK (to discard them).
Transactions ensure data integrity by upholding the ACID properties, especially in multi-user environments where concurrent access is common.
BEGIN;
INSERT INTO logs (msg) VALUES ('started');
UPDATE inventory SET stock = stock - 1 WHERE id = 123;
COMMIT;
Q16: Explain the GROUP BY clause.
The GROUP BY clause groups rows that have the same values in specified columns. It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to perform calculations on each group.
Every column in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause.
SELECT category, AVG(price)
FROM products
GROUP BY category;
Q17: What is a "Sequence" in PostgreSQL?
A sequence is a special kind of database object that generates a sequence of integers. It is typically used to create unique identifiers for primary keys. The SERIAL and BIGSERIAL types in Postgres are abstractions that automatically create a sequence and set the column's default value to nextval().
CREATE SEQUENCE user_id_seq;
SELECT nextval('user_id_seq'); -- Returns next number
Q18: What is "Normalization"?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a large table into smaller tables and defining relationships between them.
- 1NF: Atomic values, no repeating groups.
- 2NF: In 1NF and all non-key attributes are fully functional dependent on the primary key.
- 3NF: In 2NF and no transitive dependencies (non-key columns shouldn't depend on other non-key columns).
Normalization prevents anomalies during insert, update, and delete operations.
Q19: What is a "Subquery"?
A subquery is a query nested inside another query. It can be used in SELECT, FROM, WHERE, or HAVING clauses.
- Scalar subquery: Returns a single value.
- Correlated subquery: A subquery that refers to columns of the outer query. It is executed once for every row processed by the outer query.
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Q20: What is the difference between UNION and UNION ALL?
UNION: Combines the result sets of two or moreSELECTstatements and removes duplicate rows. It requires a sort operation to find duplicates, so it's slower.UNION ALL: Combines result sets but includes duplicates. It is much faster because no sorting is required.
SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b;
Q21: What are "Aggregate Functions"?
Aggregate functions perform a calculation on a set of values and return a single value.
COUNT(): Number of rows.SUM(): Total sum of a numeric column.AVG(): Average value.MIN(): Smallest value.MAX(): Largest value.
SELECT department, COUNT(*) FROM employees GROUP BY department;
Q22: What is a "Trigger"?
A trigger is a function that is automatically invoked by the database in response to a specific event (INSERT, UPDATE, DELETE, or TRUNCATE) on a table or view.
- Uses: Auditing, enforcing complex business rules, data validation, and automatically updating related tables.
- Timing: Can be
BEFOREorAFTERthe event. - Level: Can be
FOR EACH ROWorFOR EACH STATEMENT.
CREATE TRIGGER log_update
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION log_user_changes();
Q23: What is the purpose of EXPLAIN?
The EXPLAIN statement shows the execution plan that the PostgreSQL planner generates for a given SQL statement. It shows how the database will retrieve the data: whether it will use an index scan, a sequential scan, what joins it will use, and the estimated cost/time.
EXPLAIN ANALYZE actually executes the query and provides the real timings and row counts.
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
Q24: How do you define a JSONB column and query it?
JSONB stores JSON data in a decomposed binary format. It is slower to insert but much faster to query and supports indexing.
- Operators:
->(get by key as JSON),->>(get by key as text),@>(contains).
SELECT metadata->>'theme' FROM users WHERE metadata @> '{"role": "admin"}';
Q25: What is a "Common Table Expression" (CTE)?
A CTE is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause.
- Readability: Makes complex queries easier to read by breaking them into logical steps.
- Recursion:
WITH RECURSIVEallows querying hierarchical data (like org charts or folder structures).
WITH regional_sales AS (
SELECT region, SUM(amount) AS total FROM sales GROUP BY region
)
SELECT * FROM regional_sales WHERE total > 10000;
Q26: What is a "Unique Key"?
Similar to a primary key, a unique key ensures that all values in a column are distinct. However:
- A table can have multiple unique keys.
- Unique key columns can contain NULL values (though multiple NULLs are usually allowed depending on configuration, as NULL != NULL).
ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username);
Q27: What is the CASE expression?
The CASE expression is SQL's way of handling if-then-else logic. It returns a value based on which condition is met first.
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM students;
Q28: What is the LIKE operator?
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
%: Matches zero or more characters._: Matches a single character.ILIKE: PostgreSQL specific case-insensitive version.
SELECT * FROM users WHERE username LIKE 'admin%';
Q29: What is the purpose of ORDER BY?
ORDER BY is used to sort the result set in ascending (ASC, default) or descending (DESC) order. You can sort by multiple columns.
SELECT * FROM products ORDER BY category ASC, price DESC;
Q30: What is a "Data Dictionary" in PostgreSQL?
The data dictionary is a set of system tables and views that store metadata about the database itself (tables, columns, permissions, etc.). In PostgreSQL, these are found in the information_schema and the pg_catalog schemas.
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Q31: What is the DISTINCT keyword?
DISTINCT is used in the SELECT clause to remove duplicate rows from the result set, ensuring that only unique values are returned.
SELECT DISTINCT category FROM products;
Q32: Explain the BETWEEN operator.
The BETWEEN operator selects values within a given range (inclusive). It can be used with numbers, text, and dates.
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
Q33: What is an "Alias"?
An alias is a temporary name given to a table or a column for the duration of a query. It makes names more readable or shorter.
SELECT u.username AS user_name FROM users AS u;
Q34: What is the difference between CHAR and VARCHAR?
CHAR(n): Fixed-length string. If the value is shorter thann, it is padded with spaces.VARCHAR(n): Variable-length string up toncharacters. No padding. In PostgreSQL,TEXTis often preferred over both as it has no performance penalty and handles any length.
Q35: How do you add a column to an existing table?
You use the ALTER TABLE statement with the ADD COLUMN clause.
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
Q36: What is the IN operator?
The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');
Q37: What is a "Self-Join"?
A self-join is a regular join, but the table is joined with itself. This is useful for querying hierarchical data stored in a single table (e.g., an employee table where each row has a manager_id pointing to another employee).
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;
Q38: Explain LIMIT and OFFSET.
LIMIT: Specifies the maximum number of rows to return.OFFSET: Specifies how many rows to skip before starting to return rows. This is commonly used for pagination.
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;
Q39: What is a "Stored Procedure" vs a "Function"?
- Function: Returns a value. Can be used in SQL expressions. In Postgres, functions can also perform data modification.
- Procedure: (Postgres 11+) Does not return a value. The key difference is that a procedure can manage its own transactions (it can
COMMITorROLLBACKinside), while a function runs inside the transaction of the caller.
CREATE PROCEDURE update_stock(p_id INT, p_qty INT)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE inventory SET stock = stock + p_qty WHERE id = p_id;
COMMIT;
END;
$$;
Q40: What is the ANY or SOME operator?
The ANY operator returns true if any of the subquery values meet the condition. It is used with comparison operators like =, >, <.
SELECT * FROM products WHERE price > ANY (SELECT price FROM sales);
Q41: What is the ALL operator?
The ALL operator returns true only if all of the subquery values meet the condition.
SELECT * FROM products WHERE price > ALL (SELECT price FROM competitor_prices);
Q42: Explain the EXISTS operator.
EXISTS returns TRUE if the subquery returns at least one row. It is often faster than IN for checking existence because it stops as soon as it finds a match.
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
Q43: How do you rename a table?
Use ALTER TABLE ... RENAME TO ....
ALTER TABLE old_name RENAME TO new_name;
Q44: What is "Data Integrity"?
Data integrity refers to the overall accuracy, completeness, and consistency of data. In databases, it is maintained through constraints (primary keys, foreign keys), types, and triggers.
Q45: What is a "Composite Key"?
A composite key is a primary key or unique key that consists of more than one column. This is common in many-to-many relationship mapping tables.
CREATE TABLE project_members (
project_id INT,
user_id INT,
PRIMARY KEY (project_id, user_id)
);
Q46: What is the psql tool?
psql is the command-line interface for interacting with PostgreSQL. It allows you to type in queries, issue them to PostgreSQL, and see the query results. It also has many "meta-commands" starting with \ (e.g., \d to list tables).
Q47: How do you delete a table?
Use the DROP TABLE statement. Adding IF EXISTS prevents an error if the table doesn't exist.
DROP TABLE IF EXISTS temp_data;
Q48: What is a "UUID" and when should you use it as a Primary Key?
A UUID is a 128-bit number used to uniquely identify information.
- Pros: Can be generated offline, unique across different systems (great for distributed systems/microservices).
- Cons: Larger storage (16 bytes vs 4 or 8), slower B-tree performance due to non-sequential nature (though
uuid_generate_v7orv4with prefixing helps).
Q49: What is gen_random_uuid()?
It is a built-in PostgreSQL function (available by default in newer versions or via pgcrypto extension) that generates a random version 4 UUID.
Q50: How do you comment in SQL?
- Single line: Start with
--. - Multi-line: Wrap in
/* ... */.
-- This is a comment
SELECT 1; /* This is also
a comment */
2. Intermediate Questions
Q51: Describe "MVCC" (Multi-Version Concurrency Control).
MVCC is the mechanism PostgreSQL uses to handle concurrent access to data without using heavy locking.
- Concept: Instead of locking a row when it's being updated, Postgres creates a new version (a "tuple") of that row.
- Snapshots: Each transaction sees a "snapshot" of the data as it existed at a specific point in time (based on transaction IDs).
- Benefits: Readers never block writers, and writers never block readers. Only writers modifying the same row block each other.
- Cleanup: Old row versions that are no longer visible to any transaction must be cleaned up by the
VACUUMprocess.
Q52: What is the "VACUUM" process and why is it necessary?
Because of MVCC, updating or deleting a row doesn't immediately remove the old data from disk; it just marks it as invisible. This results in "bloat".
VACUUM: Reclaims storage occupied by dead tuples so it can be reused by new data in the same table.ANALYZE: Updates statistics used by the query planner.AUTOVACUUM: A background daemon that automatically runs vacuum and analyze based on the amount of changes in a table.VACUUM FULL: Rewrites the entire table to a new file to reclaim all space and return it to the OS, but it requires an exclusive lock and is slow.
Q53: Explain the different "Transaction Isolation Levels".
PostgreSQL supports four isolation levels defined by the SQL standard:
- Read Uncommitted: (In Postgres, behaves like Read Committed).
- Read Committed: Default. A transaction only sees data committed before the query (not the transaction) began. Prevents "dirty reads".
- Repeatable Read: A transaction sees data as it was when the transaction began. Prevents "non-repeatable reads".
- Serializable: The strictest level. It emulates serial transaction execution. If a concurrent modification could cause a "serialization anomaly", the transaction is rolled back.
Q54: What are "Window Functions" and provide an example.
Window functions perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not cause rows to become grouped into a single output row; the rows retain their separate identities.
- Syntax:
FUNCTION(...) OVER (PARTITION BY ... ORDER BY ...).
-- Get each employee's salary and the average salary of their department
SELECT name, salary, department,
AVG(salary) OVER(PARTITION BY department) as dept_avg
FROM employees;
Q55: Explain "Index Types" in PostgreSQL.
Postgres supports several index types:
- B-Tree: Default. Good for equality and range queries.
- Hash: Good for equality queries (rarely used, B-tree is usually better).
- GiST (Generalized Search Tree): Used for geometric data, full-text search, and ranges.
- GIN (Generalized Inverted Index): Excellent for indexing multi-value data like arrays and JSONB keys.
- BRIN (Block Range Index): Very small and efficient for very large tables where data has a natural correlation with its physical location (like timestamps).
- SP-GiST: For non-balanced disk-based data structures (trees).
Q56: What is a "Partial Index"?
A partial index is built over a subset of a table, defined by a conditional expression (a WHERE clause).
- Benefits: Smaller index size, faster updates, and can enforce uniqueness on a subset of data (e.g., only one "active" record per user).
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id)
WHERE active = TRUE;
Q57: What is a "Covering Index" (Index-Only Scan)?
A covering index includes all the columns required by a query, allowing Postgres to satisfy the query entirely from the index without visiting the heap (the table file). This is achieved using the INCLUDE clause.
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);
-- A query selecting name WHERE email = ? can now be an Index-Only Scan.
Q58: Explain "Write-Ahead Logging" (WAL).
WAL is a standard method for ensuring data integrity and durability.
- Mechanism: All changes to data files are first recorded in a sequential log (the WAL) before being applied to the actual data pages.
- Crash Recovery: If the system crashes, Postgres can "replay" the WAL to restore the database to a consistent state.
- Replication: The WAL stream is also used for physical streaming replication to standby servers.
Q59: What is "Logical Replication"?
Unlike physical replication (which copies the entire cluster bit-for-bit), logical replication allows you to replicate specific tables or even specific rows/columns.
- Uses: Replicating between different Postgres versions, consolidating multiple databases into one, and feeding data to external systems in real-time.
- Publish/Subscribe: Based on a "Publication" on the source and a "Subscription" on the target.
Q60: Describe "Table Partitioning".
Partitioning refers to splitting one logically large table into smaller physical pieces. PostgreSQL supports:
- Range Partitioning: e.g., by date ranges.
- List Partitioning: e.g., by country or status codes.
- Hash Partitioning: e.g., by a hash of a user ID to distribute load.
- Benefits: Faster queries (partition pruning), easier maintenance (dropping an old month of data is a fast
DROP TABLE), and better performance for very large datasets.
Q61: What are "Upserts" and how do you implement them?
An "Upsert" is an operation that inserts a row if it doesn't exist, or updates it if it does. In Postgres, this is done using the INSERT ... ON CONFLICT clause.
INSERT INTO stats (id, count) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE SET count = stats.count + 1;
Q62: Explain "Foreign Data Wrappers" (FDW).
FDW allows PostgreSQL to access data stored in external systems (like another Postgres DB, MySQL, MongoDB, or even a CSV file) as if they were local tables.
- Standard: Follows the SQL/MED (Management of External Data) standard.
- Use case: Federated queries and data integration without complex ETL processes.
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '...', dbname '...');
Q63: How do you handle Full-Text Search in PostgreSQL?
Postgres has built-in robust full-text search capabilities using:
tsvector: A sorted list of distinct "lexemes" (normalized words).tsquery: Represents the search terms.@@: The match operator.to_tsvector()andto_tsquery(): Functions to convert text and queries.- GIN indexes: Used to make these searches extremely fast.
SELECT title FROM posts
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & performance');
Q64: What is a "Recursive CTE"?
A recursive CTE allows you to perform complex hierarchical queries. It consists of an "anchor" query and a "recursive" query joined by UNION or UNION ALL.
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1 -- Anchor
UNION
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- Recursive
)
SELECT * FROM subordinates;
Q65: Explain "Lateral Joins".
A LATERAL join allows a subquery in the FROM clause to refer to columns of preceding tables in the join. It's like a "for each" loop for joins.
SELECT u.name, recent_order.amount
FROM users u,
LATERAL (
SELECT amount FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 1
) AS recent_order;
Q66: What are "Savepoints" in transactions?
Savepoints allow you to roll back part of a transaction without discarding the entire thing. This is useful for handling expected errors in a complex sequence of operations.
BEGIN;
INSERT INTO accounts ...;
SAVEPOINT my_savepoint;
INSERT INTO risky_table ...; -- If this fails...
ROLLBACK TO my_savepoint; -- ...we go back here and keep the first insert.
COMMIT;
Q67: What is the "Search Path"?
The search_path is a configuration setting that determines which schemas PostgreSQL looks in for objects (like tables) when they are referenced without a schema prefix. By default, it's "$user", public.
SET search_path TO myschema, public;
Q68: Explain "Prepared Statements".
Prepared statements allow you to parse and plan a query once, and then execute it multiple times with different parameters.
- Benefits: Improved performance (saves planning time) and security (prevents SQL injection).
PREPARE my_plan (int) AS SELECT * FROM users WHERE id = $1;
EXECUTE my_plan (10);
Q69: What is the difference between NULLS FIRST and NULLS LAST in sorting?
In PostgreSQL, NULLs are considered "larger" than all other values. By default, ASC sorts them at the end and DESC sorts them at the beginning. You can explicitly override this behavior.
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
Q70: What is "Select For Update"?
SELECT ... FOR UPDATE locks the rows returned by the query as if they were being updated. This prevents other transactions from modifying or locking them until the current transaction finishes. It is crucial for preventing race conditions in "read-then-write" logic.
-- Prevents two users from withdrawing money at the same time
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- perform logic and update
Q71: How do you detect and resolve "Deadlocks"?
A deadlock happens when transaction A is waiting for a lock held by B, and B is waiting for a lock held by A.
- Detection: PostgreSQL has an automatic deadlock detector that wakes up after a configurable timeout (
deadlock_timeout, default 1s). - Resolution: It identifies the circular dependency and cancels one of the transactions, allowing the others to proceed.
- Prevention: Always acquire locks in the same order across different transactions.
Q72: What are "Extensions" in PostgreSQL?
Extensions are packages that add new functionality to a Postgres database (data types, functions, operators, index types).
- Common ones:
pg_stat_statements(query profiling),postgis(spatial data),pgcrypto(hashing/encryption).
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Q73: Explain the pg_stat_statements extension.
This is one of the most important tools for performance tuning. It records statistics (number of calls, total time, rows returned, block I/O) for every unique SQL statement executed on the server. It allows you to find the "slowest" or most frequent queries.
SELECT query, total_exec_time / calls AS avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
Q74: What is a "Materialized View"?
Unlike a regular view, a materialized view saves the results of the query to disk.
- Pros: Extremely fast to query complex aggregations or joins.
- Cons: Data becomes stale. You must manually run
REFRESH MATERIALIZED VIEWto update it. - CONCURRENTLY: (Postgres 9.4+) Allows refreshing without locking the view for readers.
CREATE MATERIALIZED VIEW sales_summary AS SELECT ...;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
Q75: Describe "Physical Streaming Replication".
This is the process of sending the WAL stream from a "Primary" server to one or more "Standby" servers.
- Asynchronous: Primary doesn't wait for standby. Minimal impact on primary performance, but risk of data loss on failover.
- Synchronous: Primary waits for standby to confirm receipt. Zero data loss, but primary stops if standby is down.
- Hot Standby: Allows running read-only queries on the standby server.
Q76: What is the "Shared Buffer" cache?
This is a chunk of memory dedicated to caching data blocks from the disk. When Postgres needs data, it first looks in the shared buffers. If it's not there, it requests it from the OS (which might have it in its own filesystem cache).
- Tuning: Typically set to 25% of total system RAM.
Q77: How do you handle "Time Zones" in PostgreSQL?
TIMESTAMP: Stores date and time, but ignores time zones.TIMESTAMP WITH TIME ZONE(timestamptz): The recommended type. It converts incoming times to UTC and stores them. On output, it converts them to the time zone of the current session.
SET timezone TO 'UTC';
SELECT now();
Q78: What is the "TOAST" mechanism?
TOAST (The Oversized-Attribute Storage Technique) is used to store large column values (like long text or large binaries) that exceed the page size (8KB).
- Compression: Postgres first tries to compress the value.
- Out-of-line storage: If it's still too big, it moves the value to a separate "TOAST table" and stores a pointer in the main table. This keeps the main table rows small and efficient to scan.
Q79: Explain "Fillfactor".
Fillfactor is a parameter for tables and indexes (0-100) that determines how much space to leave empty in each data page during initial loading.
- Tables: Default 100. Lowering it (e.g., to 90) leaves room for
UPDATEsto place the new row version on the same page, which is much faster (HOT updates). - Indexes: Default 90.
Q80: What are "HOT" (Heap-Only Tuple) updates?
A HOT update occurs when a new row version can be placed on the same data page as the old one AND no indexed columns are modified.
- Benefit: It avoids the need to update indexes, significantly reducing I/O and vacuum pressure. It's the "holy grail" of Postgres performance for write-heavy loads.
Q81: What is the pg_dump utility?
pg_dump is used to export a PostgreSQL database into a script file or archive file. It creates a consistent backup even if the database is being used concurrently. For large databases, pg_dump -Fd (directory format) allows for parallel backups.
Q82: Describe "Point-in-Time Recovery" (PITR).
PITR allows you to restore a database to its state at any specific moment in the past.
- Requirements: A base backup and all WAL files generated since that backup (archived using
archive_mode). - Process: Restore the base backup and "replay" the WAL logs until the target timestamp.
Q83: What is the "Query Planner" (Optimizer)?
The query planner analyzes an SQL statement and decides on the most efficient way to execute it. It uses statistics about table sizes, column value distributions, and index availability to calculate the "cost" of different execution paths and picks the one with the lowest cost.
Q84: Explain "Statistics" in the context of the planner.
Postgres maintains statistics about the data in each table (e.g., number of distinct values, frequency of values, null count). The ANALYZE command updates these. If statistics are outdated, the planner might choose a terrible execution plan (like a sequential scan instead of an index scan).
Q85: What is "Index-Only Scan"?
This happens when the index contains all the information needed to satisfy the query, and the index entry's visibility can be determined without checking the table (using the "Visibility Map"). It is much faster than a standard index scan.
Q86: What is the "Visibility Map"?
The visibility map is a bitmask associated with each table that tracks which data pages contain only rows visible to all active transactions. It helps the VACUUM process skip clean pages and enables "Index-Only Scans".
Q87: Explain "Set-Returning Functions".
These are functions that return more than one row. The most common is generate_series().
SELECT * FROM generate_series(1, 5);
Q88: What is array_agg()?
An aggregate function that collects values into a PostgreSQL array.
SELECT category, array_agg(name) FROM products GROUP BY category;
Q89: Explain string_agg().
Similar to array_agg, but concatenates values into a single string with a specified delimiter.
SELECT category, string_agg(name, ', ') FROM products GROUP BY category;
Q90: What is the purpose of LISTEN and NOTIFY?
This is a simple asynchronous messaging system within PostgreSQL. A client can LISTEN on a channel, and another process can NOTIFY that channel. This is often used to trigger actions in an application when database data changes.
LISTEN my_channel;
NOTIFY my_channel, 'data changed';
Q91: Describe "Row-Level Security" (RLS).
RLS allows you to define policies that restrict which rows a user can see or modify based on their identity or other session variables. It is crucial for building multi-tenant applications securely at the database level.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY doc_access ON documents
USING (owner = current_user);
Q92: How do you handle "Bulk Inserts"?
COPY: The fastest way. It reads data from a file or stdin directly into a table.- Unlogged Tables: If you don't need durability for temporary data, using an
UNLOGGEDtable skips WAL logging and is much faster. - Drop Indexes: Drop indexes before a massive import and recreate them after.
Q93: What is an "Advisory Lock"?
Advisory locks are application-defined locks that have no meaning to the database's internal concurrency control. They are useful for implementing mutexes or distributed locks across multiple application servers using the database.
SELECT pg_advisory_lock(42); -- Acquire lock
-- critical section
SELECT pg_advisory_unlock(42); -- Release lock
Q94: What is the pg_stat_activity view?
This view shows one row per server process, showing information about the current state of that process: the user, the database, the client's IP, and the current SQL query being executed.
SELECT * FROM pg_stat_activity WHERE state = 'active';
Q95: Explain FILTER in aggregate functions.
Postgres allows you to provide a condition for an aggregate function using the FILTER clause. This is cleaner than using CASE inside the aggregate.
SELECT count(*) AS total,
count(*) FILTER (WHERE score > 90) AS top_tier
FROM students;
Q96: What is the EXCLUDE constraint?
An exclusion constraint ensures that if any two rows are compared on the specified columns using specified operators, at least one of those operator comparisons will return false or null. It is commonly used to prevent overlapping time ranges or overlapping circles.
ALTER TABLE room_reservations ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);
Q97: How do you convert a string to a date?
Use the to_date() function or a cast (::date).
SELECT to_date('2023-01-01', 'YYYY-MM-DD');
SELECT '2023-01-01'::date;
Q98: What is the pg_settings view?
This view provides access to all the configuration parameters of the PostgreSQL server. You can use it to inspect current settings and their sources (e.g., config file, environment variable).
Q99: Explain "Parallel Query".
PostgreSQL can use multiple CPU cores to execute a single query. The planner can create a plan that splits a large table scan or join among multiple worker processes, gathering the results at the end. This is enabled by parameters like max_parallel_workers_per_gather.
Q100: What is DISTINCT ON?
DISTINCT ON (expression) is a PostgreSQL extension that keeps only the first row of each set of rows where the given expressions evaluate to equal. It is often combined with ORDER BY to find the "first" or "most recent" record per group.
-- Find the most recent order for every user
SELECT DISTINCT ON (user_id) user_id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;
3. Advanced Questions
Q101: Deep dive: The PostgreSQL "Page" structure.
PostgreSQL stores data in "Pages" (typically 8KB).
- Header: Contains metadata (checksums, free space pointers).
- Line Pointers: An array of small pointers to the actual data items (tuples) in the page.
- Free Space: Space between line pointers and data items.
- Tuples: The actual row data. They are inserted from the end of the page towards the beginning.
- Special Space: Used by specific index types (like B-trees). Understanding this is key to understanding Fillfactor and TOAST.
Q102: Explain "B-Tree Index Corruption" and how to fix it.
While rare, B-tree indexes can become corrupt due to OS bugs, power failures, or hardware issues.
- Symptoms: Queries returning incorrect results, or
REINDEXfailing. - Fix: The
amcheckextension can be used to verify index integrity. If corruption is found, the index should be dropped and recreated or useREINDEX CONCURRENTLY.
Q103: Describe the "Postmaster" process and the connection lifecycle.
The Postmaster is the main server process.
- Listen: Postmaster listens on the configured port.
- Fork: When a client connects, Postmaster
forksa new "Backend" process to handle that specific connection. - Authentication: The backend process handles user authentication.
- Processing: The backend process remains active for the duration of the connection, executing queries and managing its own memory.
- Note: Because forking is expensive, use a connection pooler like PgBouncer in high-concurrency environments.
Q104: What is "Streaming Replication Lag" and how do you monitor it?
Lag occurs when the standby server cannot keep up with the primary's WAL generation.
- Monitoring: Check
pg_stat_replicationon the primary (shows the difference between the primary's current LSN and the standby's received/applied LSN). - LSN: Log Sequence Number - a pointer into the WAL stream.
Q105: How does the "Cost Model" work in the planner?
The planner assigns costs to different operations:
seq_page_cost: Cost of reading one disk page sequentially (default 1.0).random_page_cost: Cost of reading one disk page randomly (default 4.0 - reflects HDD latency, usually set to 1.1 for SSDs).cpu_tuple_cost: Cost of processing one row (default 0.01).cpu_index_tuple_cost: Cost of processing one index entry. The planner sums these up to estimate the total "cost" of a plan.
Q106: Explain "Join Strategies": Nested Loop, Merge Join, and Hash Join.
- Nested Loop: For each row in the outer table, scan the inner table for a match. Best for small tables or when an index is available on the inner join column.
- Merge Join: Both tables are sorted on the join column and then merged. Efficient for large joins if the data is already sorted (e.g., from an index).
- Hash Join: A hash table is built from the smaller table, and the larger table is scanned to find matches. Fast for large joins where no indexes exist.
Q107: What is "Partition Pruning"?
This is a planner optimization where it realizes that some partitions can't possibly contain data matching a query's WHERE clause, so it skips scanning them entirely. This is what makes partitioning so fast for time-series data.
Q108: Describe "Custom Statistics" (Extended Statistics).
Sometimes the planner assumes columns are independent when they are not (e.g., make and model of a car). CREATE STATISTICS allows you to tell Postgres to track the correlation between these columns, leading to much better estimates and plans.
CREATE STATISTICS s_car_make_model (dependencies) ON make, model FROM cars;
Q109: Explain "Bitmap Index Scan".
This is a hybrid between an index scan and a sequential scan.
- Scan Index: Postgres scans the index and builds a bitmap of pages that might contain matching rows.
- Sort: The bitmap is sorted by physical disk location.
- Scan Heap: Postgres reads the data pages sequentially according to the bitmap.
- Why: It reduces random I/O and efficiently handles multiple indexes for the same query.
Q110: Deep dive: The "Transaction Log" (WAL) architecture.
The WAL consists of multiple files (typically 16MB) in the pg_wal directory.
- Checkpointing: The process of flushing all dirty data pages from the shared buffers to disk and writing a "checkpoint" record to the WAL.
- WAL Archiving: Moving old WAL files to a safe location for PITR.
- WAL Segments: Postgres cycles through a fixed number of segments to keep disk usage stable.
Q111: How would you scale PostgreSQL for "Read" vs "Write" heavy workloads?
- Read Scaling: Add "Read Replicas" (Standby servers) and use a load balancer (like HAProxy or a smart driver) to distribute
SELECTqueries among them. - Write Scaling: Much harder.
- Vertical scaling (more CPU/RAM/SSD).
- Table partitioning.
- "Sharding": Splitting data across multiple independent database clusters (often using
Citusextension).
Q112: Explain "Inter-Process Communication" (IPC) in Postgres.
Postgres uses Shared Memory and Semaphores to coordinate between its multiple backend processes. This allows processes to share the shared buffer cache and the lock manager.
Q113: What are "Prepared Transactions" (Two-Phase Commit)?
Used in distributed databases. A transaction is "prepared" on multiple nodes, ensuring it can be committed. Once all nodes confirm, a second "commit" message is sent. If any node fails, all are rolled back. Use PREPARE TRANSACTION.
Q114: Describe "Logical Decoding".
Logical decoding is the process of extracting the changes recorded in the WAL and converting them into a logical format (like JSON or SQL). This is the underlying technology for logical replication and CDC (Change Data Capture) tools like Debezium.
Q115: What is the "Free Space Map" (FSM)?
The FSM is a data structure associated with each table that tracks the amount of available space in each data page. When Postgres needs to insert a new row, it uses the FSM to quickly find a page that has enough room.
Q116: Explain "BRIN Index" internals.
BRIN indexes store summary information (min/max values) for blocks of pages. They are tiny because they don't store individual row pointers. They are incredibly fast for range queries on columns that are physically sorted (like primary keys or timestamps).
Q117: What is "Lock Contention"?
This occurs when many transactions are waiting for the same lock (e.g., updating the same "hot" row or an exclusive lock on a table). This kills performance. Monitor it using the pg_locks view and by looking for wait_event in pg_stat_activity.
Q118: Describe the architecture of "PgBouncer".
PgBouncer is a lightweight connection pooler. It sits between the application and the database.
- Session Pooling: Connection is held by the client for their entire session.
- Transaction Pooling: Connection is returned to the pool as soon as a transaction ends. This allows hundreds of clients to share a very small number of actual database connections.
Q119: What is "Snapshot Isolation"?
It is the guarantee that all queries within a transaction see a consistent snapshot of the database. PostgreSQL achieves this through MVCC. It avoids most concurrency anomalies, though "Write Skew" can still happen in Repeatable Read (but not Serializable).
Q120: How do you handle "Database Sharding" with Citus?
Citus is an extension that transforms Postgres into a distributed database. You define a "distribution column" (sharding key), and Citus distributes table rows across multiple "worker" nodes. The "coordinator" node handles query planning and execution across the workers.
Q121: Explain "JIT (Just-In-Time) Compilation".
PostgreSQL can use LLVM to compile parts of complex SQL queries into machine code at runtime. This speeds up CPU-bound tasks like complex expressions and aggregations on large datasets.
Q122: Describe the "Visibility Map" role in Index-Only Scans.
An Index-Only Scan can only return data from the index if the visibility map indicates that all tuples on the corresponding data page are visible to everyone. If not, the database must visit the heap to check visibility, losing the benefit of the index-only scan.
Q123: How do you tune work_mem?
work_mem is the amount of memory allowed for internal sort and hash operations before switching to disk.
- Warning: This is per operation, not per connection. A complex query could use several times this amount.
- Tuning: Start small and increase based on queries that
EXPLAIN ANALYZEshows are spilling to disk ("external sort").
Q124: Explain "Autovacuum Tuning".
If autovacuum can't keep up, bloat grows.
autovacuum_vacuum_scale_factor: Percent of dead rows to trigger a vacuum.autovacuum_vacuum_cost_limit: Controls how much I/O the autovacuum can perform before sleeping.autovacuum_max_workers: Number of concurrent workers.
Q125: What is the "Catalog Version"?
Each PostgreSQL cluster has a version ID for its system catalogs. This is why you usually can't simply move data files between different major versions of Postgres; the underlying structure of the metadata has changed.
Q126: Explain "Operator Classes" and "Operator Families".
These are the definitions that tell PostgreSQL how to use a specific data type with a specific index type (e.g., how to compare two geometric shapes for a GiST index).
Q127: How do you implement "Audit Logging" with pgaudit?
The pgaudit extension provides detailed session and/or object audit logging via the standard logging facility. It can be configured to log specific classes of statements (READ, WRITE, DDL) for compliance and security auditing.
Q128: What is the "Hint Bit" mechanism?
To avoid repeated visibility checks, Postgres uses "hint bits" to mark a tuple as known-visible or known-dead directly in the data page once its status is determined. This is a crucial micro-optimization for MVCC performance.
Q129: Describe "Table Inheritance".
Postgres allows a table to inherit from one or more other tables. A query on the parent table returns its own rows plus all rows of its children. This was the precursor to modern partitioning.
Q130: Explain "External Sorting".
If a sort operation exceeds work_mem, Postgres performs an "external sort" on disk using temporary files. This is significantly slower than in-memory sorting. Use EXPLAIN ANALYZE to detect this.
Q131: How do you handle "Connection Leaks" in your application?
- Use a connection pooler (client-side or server-side).
- Use
idle_in_transaction_session_timeoutto automatically kill connections that have been hanging in an open transaction for too long.
Q132: Describe "Partial index" vs "Expression Index".
- Partial Index: Indexes a subset of rows (e.g.,
WHERE active = true). - Expression Index: Indexes the result of a function or expression (e.g.,
lower(email)).
Q133: Explain "Nulls Distinct" (Postgres 15+).
In unique constraints, Postgres traditionally treated each NULL as a distinct value (so you could have multiple NULLs). In Postgres 15, you can specify UNIQUE NULLS NOT DISTINCT to allow only one NULL in a unique column.
Q134: What is the "Buffer Cache Hit Ratio"?
It's the percentage of requests for data pages that were satisfied by the shared buffers without going to the OS/disk. A healthy ratio for active data is usually >95% or 99%.
Q135: How do you use pg_prewarm?
The pg_prewarm extension allows you to manually load specific tables or indexes into the shared buffers (or the OS cache). This is useful after a server restart to "warm up" the database before high traffic arrives.
Q136: Explain "Transaction ID (XID) Wraparound" and how to prevent it.
Postgres uses a 32-bit transaction ID. After ~4 billion transactions, the ID wraps around. If not handled, the database starts thinking old rows are in the future and they "disappear".
- Prevention:
VACUUM"freezes" old rows, replacing their XID with a specialFrozenTransactionId. Autovacuum is aggressive about this when a table gets old.
Q137: What is "Incremental Backup"?
Incremental backup (available in Postgres 17+ or via tools like pgBackRest) only copies the data blocks that have changed since the last backup, drastically reducing backup time and storage for large databases.
Q138: Explain "Security Barrier" views.
By default, some optimizations can allow a user to see data that should be filtered by a view (e.g., by providing a slow, leakable function in the WHERE clause). SECURITY_BARRIER ensures that the view's filters are applied before any other user-provided conditions.
Q139: Describe "Collation".
Collation defines the sorting order and character classification for text. In Postgres, you can specify collations at the column, table, or database level, and even use different ones in the same query.
Q140: What is the "Lock Manager"?
The lock manager is an internal subsystem that keeps track of all locks held and requested by all processes. It uses a shared memory structure to quickly detect conflicts and handle queueing.
Q141: Explain "Schema-only" vs "Data-only" backups.
Using pg_dump, you can use -s for schema-only (DDL) or -a for data-only. This is useful for replicating table structures to a dev environment or moving data between systems with compatible schemas.
Q142: What is the "Background Writer" (bgwriter) process?
The bgwriter process periodically flushes "dirty" pages (modified pages) from shared buffers to disk. This offloads the work from the checkpointer and ensures there are always free pages available in the buffer for new data.
Q143: Explain "Unlogged Tables" use cases and risks.
- Use case: Temp data, caches, or intermediate ETL steps.
- Risk: Data is lost on a crash or unclean shutdown. Replicas also do not receive data from unlogged tables.
Q144: How do you optimize "Range Queries"?
- Use B-tree indexes.
- For time ranges, ensure statistics are up to date.
- Consider partitioning by the range column.
Q145: What is "Index Bloom" Filter?
Bloom filters (available via the bloom extension) are space-efficient probabilistic data structures used to test whether an element is a member of a set. They are useful for multi-column queries on large tables where you don't want a massive B-tree.
Q146: Explain "Identity Columns" vs "Serial".
GENERATED ALWAYS AS IDENTITY is the SQL-standard way to define auto-incrementing columns. It is preferred over SERIAL because it's more robust and has clearer ownership of the underlying sequence.
Q147: Describe "Logical Decoding Plugins" (e.g., pgoutput, wal2json).
These are the modules that decide how to format the logical changes extracted from the WAL. pgoutput is the built-in plugin used by standard logical replication.
Q148: What is the "Stats Collector" process?
A background process that collects information about server activity (e.g., row counts, blocks read). It sends this data to the backend processes via UDP to avoid overhead.
Q149: How do you implement "Blue-Green" database deployment?
Usually involves setting up a logical replica of the "Blue" database as the "Green" database. You upgrade Green, test it, and then switch the application traffic. Logical replication makes this possible between different Postgres versions.
Q150: Explain the DEFAULT PRIVILEGES statement.
This allows you to specify the permissions that will be automatically granted to new objects (tables, functions, etc.) created in the future by a specific user. It's a key part of managing permissions in a growing team.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
4. More Detailed Performance Tuning
Q151: How to handle "Row Bloat" in high-update tables?
Bloat happens when dead tuples aren't cleaned up fast enough.
- Increase
autovacuumfrequency and intensity. - Adjust
fillfactorto 80-90 to allow for HOT updates. - If bloat is already massive, use
pg_repackto rebuild the table without an exclusive lock.
Q152: Optimizing "Aggregation" on millions of rows.
- Use
Materialized Viewsto pre-calculate results. - Use
Table Partitioningto allow parallel aggregation. - Ensure
work_memis large enough to avoid disk spills. - Consider a column-store extension like
Hydraif the workload is pure OLAP.
Q153: Tuning "Parallel Workers".
max_parallel_workers: Total workers across the cluster.max_parallel_workers_per_gather: Maximum workers for a single query. Setting these too high can overwhelm the CPU and lead to context switching overhead.
Q154: Managing "Long-Running Transactions".
Long transactions prevent VACUUM from cleaning up dead tuples cluster-wide (XID wraparound risk).
- Set
idle_in_transaction_session_timeout. - Monitor
pg_stat_activityfor transactions older than a few minutes. - Use
statement_timeoutto prevent accidental "queries from hell".
Q155: The role of "OS Tuning" for PostgreSQL.
- Huge Pages: Can reduce memory overhead and improve performance by using larger memory pages.
- Dirty Background Ratio: Controls when the OS starts flushing data to disk.
- Overcommit Memory: Should usually be disabled (
2) to prevent the OOM killer from killing the Postgres master process.
Q156: How to analyze "Lock Waits".
Join pg_locks with pg_stat_activity. This shows which process is holding a lock and which process is waiting for it, along with the SQL they are running.
Q157: Optimizing "Large IN clauses".
Large IN (...) lists can be slow. It's often better to:
- Upload the values into a temporary table and use a
JOIN. - Use an array parameter:
WHERE id = ANY(?::int[]).
Q158: Dealing with "Slow Index Creation" on large tables.
- Increase
maintenance_work_mem(the memory used byCREATE INDEXandVACUUM). - Use
CREATE INDEX CONCURRENTLYto avoid blocking writers (though it's slower).
Q159: The importance of "CPU affinity" in high-end systems.
On NUMA systems, memory access time depends on which CPU is accessing which RAM. Postgres itself doesn't handle NUMA, but pinning processes to specific cores or using numactl can sometimes improve performance on massive multi-socket servers.
Q160: How to use "Query Plan Guides" (Hints)?
PostgreSQL core does not support hints (like /*+ INDEX(u) */) because the community believes the planner should be improved instead. However, the pg_hint_plan extension allows you to add them if you absolutely must override the planner's decision.
5. Advanced PostgreSQL and SQL Mastery
Q161: Explain the difference between ANALYZE and VACUUM ANALYZE.
ANALYZE only updates the planner statistics by sampling rows from the table. It is fast and does not affect storage. VACUUM ANALYZE performs a vacuum operation first (reclaiming space from dead tuples) and then updates the statistics. You should use VACUUM ANALYZE for regular maintenance, while a standalone ANALYZE is useful after a large bulk insert but before you start querying.
Q162: Deep dive: How does the "FSM" (Free Space Map) and "VM" (Visibility Map) impact Performance?
- FSM: A tree-based structure that tracks the approximate free space in every data page. Without it, every insert would require scanning the heap from the beginning to find space, leading to $O(N)$ insertion time. With FSM, it's $O(1)$.
- VM: A bitmask where 1 bit represents 1 page. If the bit is set, it means all tuples on that page are visible to all active transactions. This allows
VACUUMto skip those pages and is the fundamental requirement forIndex-Only Scans.
Q163: Describe the "Logical Decoding" workflow for Change Data Capture (CDC).
- A client creates a "Logical Replication Slot" on the primary.
- The primary starts preserving WAL logs (won't recycle them).
- A decoding plugin (like
pgoutput) reads the WAL records. - The plugin filters and converts the physical changes into logical ones (e.g., "Insert into table X values Y").
- The changes are sent to the client as a stream.
- The client acknowledges the LSN, allowing the primary to recycle the WAL.
Q164: What are "Operator Classes" and how do they relate to B-Tree indexes?
An operator class defines how a B-tree should handle a specific data type. It specifies the strategy functions (like less than, equal, greater than) and support functions (like compare). This extensibility is why you can create B-tree indexes on custom data types.
Q165: How do you implement "Queue-like behavior" efficiently in Postgres?
Using a table as a queue is common but can be slow. To optimize:
- Use
SKIP LOCKED:SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1. This prevents workers from blocking each other. - Ensure you have an index on the
statusorprioritycolumn. - Keep the table small by deleting processed items immediately or moving them to an archive.
Q166: Explain "Predicate Locks" in the Serializable isolation level.
Unlike standard row locks, predicate locks don't lock specific data. Instead, they "lock" the condition of a query. If transaction A reads rows where x > 100, and transaction B tries to insert a row where x = 150, the database detects a conflict even if they aren't touching the same physical rows. This is how Postgres provides true serializability.
Q167: What is the impact of "Large Object" (LOB) storage on database size?
Large objects (using the oid type and lo_import) are stored in a separate system table pg_largeobject. Unlike TOAST, they aren't tied to a specific row in your table.
- Risk: They are not automatically deleted when your table row is deleted. You must use a trigger or a tool like
vacuumloto clean them up, otherwise the database will grow indefinitely.
Q168: How does "WAL Compression" work and when should you use it?
The wal_compression setting (pioneered in Postgres 9.5) compresses the full-page images written to the WAL.
- Pros: Reduces WAL volume, which speeds up replication and reduces storage requirements for WAL archives.
- Cons: Adds a small amount of CPU overhead for compression/decompression.
- Recommendation: Always enable it on modern hardware with fast CPUs and many cores.
Q169: Explain "Physical Standby" vs "Logical Standby".
- Physical: A bit-for-bit copy. Must be the same version and OS. It is "Read-Only" (Hot Standby). Best for DR and high availability.
- Logical: Can have different versions, extra indexes, or different table structures. It is "Read-Write" on other tables. Best for upgrades and data distribution.
Q170: How do you handle "High Connection Counts" (> 5000) in Postgres?
Postgres follows a process-per-connection model, which is heavy.
- Use
PgBouncerto pool connections. - Increase
max_connections(but be careful of memory/context switching). - Use
huge_pagesto reduce the memory overhead of the process page tables. - Tuning
shared_buffersis critical to ensure data is shared effectively across all these processes.
Q171: What is "Parallel Index Scan" and how does it work?
In a parallel index scan, multiple worker processes scan different parts of an index concurrently. The results are combined to find the relevant heap pages. This is particularly effective for large range scans or when the index itself is massive.
Q172: Explain "Interleaved Execution" in distributed databases.
This refers to how a distributed query (like in Citus) executes across many nodes. The coordinator breaks the query into fragments, sends them to the workers, and then interleaves the results as they arrive, ensuring that the total query time is determined by the speed of the workers, not the overhead of gathering data.
Q173: Describe "Heap-Only Tuple" (HOT) chains.
When a row is updated and stays on the same page, a "HOT chain" is formed. The index still points to the original tuple location (the "head"). If a query finds that tuple, it follows a small internal pointer to the new version. This "indirection" allows us to avoid updating the index entries, which is a massive performance win.
Q174: What is the "Commit Log" (clog)?
The clog is a small shared memory area (and disk files) that stores the status of every transaction (In Progress, Committed, Aborted). This is the absolute source of truth for MVCC visibility checks.
Q175: How do you use pg_archive_cleanup?
This is a utility tool used to remove old WAL files from a WAL archive directory. It is typically called as part of a standby's recovery_command to ensure the archive doesn't grow indefinitely after the standby has successfully "replayed" the logs.
Q176: Explain "Operator Overloading" in PostgreSQL.
Postgres allows you to define multiple functions with the same name as long as they take different argument types. You can also define your own operators (like ###) and link them to these functions. This is how Postgres supports diverse types like geometry, network addresses, and JSON natively.
Q177: What is the purpose of pg_buffercache extension?
This extension allows you to see exactly what is currently stored in the shared buffer cache: which tables are taking up space, how many "dirty" pages are there, and how many times each page has been accessed. It's a "MRI" for your database's memory usage.
Q178: How do you handle "Subtransaction" performance issues?
Using too many nested SAVEPOINTs or large EXCEPTION blocks in PL/pgSQL creates subtransactions. Each one takes up a slot in a small internal array. If you exceed this limit (~64), every visibility check in the entire database becomes much slower because it has to look up subtransaction statuses on disk.
- Fix: Avoid deeply nested loops with try/catch logic.
Q179: Describe "Bitmap Heap Scan" cost calculation.
The planner calculates the cost of a bitmap scan based on:
index_scan_cost: Cost to find matching entries in the index.bitmap_creation_cost: Cost to build the bitmap in memory.heap_scan_cost: Cost to read the pages identified by the bitmap (treating them as sequential access rather than random).
Q180: How do you implement "Data Sharding" with pure SQL?
You can use Inheritance and CHECK constraints combined with Triggers (the old way) or Declarative Partitioning (the new way). However, for true multi-node sharding, you need an extension like Citus or a specialized proxy.
Q181: Explain the "Double Buffering" problem.
Postgres has its own cache (shared_buffers) and the OS has its own cache (the filesystem cache). Data is often stored in both, which is redundant.
- Mitigation: Don't set
shared_bufferstoo high (usually 25%). Let the OS handle the rest. This allows for faster crashes (less to flush) and better use of memory for other OS tasks.
Q182: What is "External Row Storage" (TOAST)?
When a row is too large, parts of it are moved to a side table. The main table row only contains a 20-byte pointer. This side table has its own OIDs and indexes, ensuring that scanning the main table remains fast even if it contains "bloated" rows.
Q183: Describe "Index Rebuild" strategies.
REINDEX TABLE: Locks the table. Very fast but causes downtime.REINDEX CONCURRENTLY: (Postgres 12+) Doesn't lock the table. Slower and requires more resources, but allows for zero-downtime maintenance.pg_repack: An external tool that rebuilds both the table and indexes concurrently.
Q184: Explain "Functional Dependencies" in query optimization.
If column A determines column B (e.g., zip_code determines city), the planner might over-count rows in a WHERE zip_code=X AND city=Y query. Extended statistics (CREATE STATISTICS ... ON (A, B)) allow the planner to understand this dependency and produce more accurate estimates.
Q185: How do you monitor "Disk I/O" specifically for Postgres?
Use pg_stat_bgwriter to see how much data the checkpointer and bgwriter are flushing. Use pg_statio_user_tables to see how many blocks are being read from the buffer vs. the disk for each table.
Q186: What is "Predicate Pushdown"?
In complex queries or when using FDWs, the database tries to "push" the WHERE filters as close to the data source as possible. For example, if joining two remote tables via FDW, Postgres will try to send the filters to the remote server so that only the matching rows are sent over the network.
Q187: Explain "Tuple Locking".
Postgres doesn't just lock rows; it locks "tuples" (versions of rows). This allows one transaction to lock a row while another transaction is creating a new version of that same row (as long as they don't both try to commit changes to the same version).
Q188: Describe the "Catalog Cache".
Postgres has an internal cache for its system metadata (the catalogs). If this cache is too small or if there are too many objects (e.g., millions of partitions), the overhead of looking up table definitions can become a significant bottleneck.
Q189: How do you handle "Large Object" security?
Permissions for large objects are handled separately from table permissions. You use GRANT and REVOKE on specific OIDs or use the pg_largeobject_metadata table to manage access.
Q190: Explain "BRIN" index "Summarization".
A BRIN index is only useful if it's "summarized". If you insert many new rows, they aren't indexed until the summarization process runs (manually or by autovacuum). Until then, those new pages must be scanned sequentially.
Q191: What is the pg_stat_replication_slots view?
This view shows all active and inactive replication slots. It's critical for monitoring: an inactive slot will prevent the primary from recycling WAL files, which can eventually fill up your entire disk.
Q192: Explain "Transaction Snapshots".
A snapshot is a data structure containing:
xmin: The earliest active transaction ID.xmax: The first as-yet-unassigned transaction ID.xip: A list of all transaction IDs currently in progress. Any row with an ID >=xmaxis invisible. Any row with an ID <xminis visible (unless it was deleted).
Q193: How do you use pg_test_fsync?
This is a diagnostic tool that tests different fsync methods on your hardware to find the fastest way to safely commit data to disk. This is a crucial step when tuning a new server for maximum performance.
Q194: Describe "Custom Background Workers".
Postgres allows extensions to register their own background processes. These workers can connect to databases, execute queries, and perform tasks like scheduled maintenance or data ingestion completely within the database engine.
Q195: Explain "Sort Support" functions.
These are specialized C functions that provide a highly optimized way to compare data types for sorting. They are used by B-trees and merge joins to achieve maximum throughput.
Q196: How do you implement "Search-as-you-type"?
- Use
trigramindexes (pg_trgmextension). - Create a GIN index on your text column using
gin_trgm_ops. - Use the
%operator for similarity matching.
Q197: Describe "Logical Decoding Out-of-Order" processing.
Logical decoding always processes changes in commit order, not in the order they were written to the WAL. This ensures that a logical stream always represents a consistent state of the database, even if multiple large transactions were running concurrently.
Q198: What is the pg_class system table?
pg_class is the most important catalog table. It contains one row for every table, index, sequence, view, and materialized view in the entire cluster. It stores metadata like the name, the schema, the owner, and the estimated number of rows (reltuples).
Q199: Explain "Index Fast Scan".
This is a feature of GIN indexes. When searching for multiple terms, it sorts the matches by heap page ID so that it can read the required data pages in a single sequential pass, minimizing random I/O.
Q200: How do you handle "Temporary Table" bloat?
Temporary tables are not cleaned up by the standard autovacuum. If you create many large temp tables in a single session, you must manually run ANALYZE on them for performance, and ideally DROP them as soon as possible to free up resources in the pg_temp schema.
Q201: Describe "Shared Memory Stats".
Newer versions of Postgres store some statistics in shared memory to avoid the UDP overhead of the old stats collector. This leads to more reliable and lower-latency statistics reporting.
Q202: Explain "Range Types" (e.g., int4range, tstzrange).
Range types allow you to represent a continuous interval of values in a single column. Postgres provides operators for intersection (&&), containment (@>), and adjacency (-|-). They are essential for scheduling and logistics applications.
Q203: How do you use pg_lsclusters?
This is a Debian/Ubuntu-specific tool for managing multiple PostgreSQL clusters on the same machine. It shows the status, version, and data directory for every cluster.
Q204: Describe "Logical Replication Failover" issues.
The main issue is that replication slots are not replicated to standby servers. If your primary fails, your logical consumers must re-create their slots on the new primary, which can lead to duplicate data if not handled carefully. (Postgres 17+ has built-in features to mitigate this).
Q205: Explain "Index Build Memory" (maintenance_work_mem).
Building an index requires sorting all the values in the column. maintenance_work_mem determines how much of this sort happens in memory. Setting this to 10% of system RAM during index-heavy maintenance can significantly speed up the process.
Q206: What is the pg_attribute table?
It contains one row for every column in every table or index in the database. It stores the data type, the collation, and the position of the column within the row.
Q207: How do you implement "Full-Text Search across multiple languages"?
You should have a separate tsvector column for each language, or store the language ID in a column and use it in your to_tsvector calls. You can then use a GIN index on an expression that combines these vectors.
Q208: Describe "Write Amplification" in Postgres.
Because of MVCC, every UPDATE is actually a DELETE followed by an INSERT. This means that even a small change to a row results in writing a whole new version of the row to the WAL and the data files. Tuning fillfactor and ensuring HOT updates is the primary way to minimize this.
Q209: Explain "Recursive Joins" vs "Connect By".
While Oracle uses CONNECT BY, PostgreSQL uses the SQL-standard WITH RECURSIVE. CTEs are much more flexible as they allow you to perform joins, filters, and aggregations at each level of the recursion.
Q210: What is the purpose of pg_filedump?
It is an external utility for inspecting the raw binary data files of a PostgreSQL database. It's a "forensics" tool used for debugging internal storage issues or recovering data from corrupted files.
Q211: Explain the "Deadlock Detector" algorithm.
Postgres uses a graph-based algorithm. When a process waits for a lock, it adds an edge to the "wait-for" graph. The detector periodically scans this graph for cycles. If a cycle is found, it "breaks" it by aborting the transaction that joined the cycle last.
Q212: How do you use pg_stat_user_indexes?
This view shows how many times each index has been used in a scan. If an index has 0 scans after months of production use, it is a candidate for deletion, which will speed up your INSERT and UPDATE operations.
Q213: Describe "Large Object" Invocation.
Large objects are accessed using the Server-Side Large Object API. This allows you to open a "file descriptor" to an object and read/write chunks of data, which is more efficient than loading a 1GB bytea into memory all at once.
Q214: Explain "Join Removals" optimization.
The planner can sometimes detect that a LEFT JOIN is unnecessary (e.g., if you are joining a table but not selecting any columns from it and the join is on a unique key). In such cases, it will remove the join entirely, saving significant processing time.
Q215: How do you implement "Database Level Encryption" (TDE)?
PostgreSQL core does not support TDE (Transparent Data Encryption). You must either:
- Use filesystem-level encryption (LUKS).
- Use a specialized fork like
CYBERTEC PostgreSQLorFujitsu Enterprise Postgres. - Encrypt sensitive columns in the application before sending them to the database.
Q216: Explain "JIT Cache" and its benefits.
Once a query is JIT-compiled, the resulting machine code can be cached and reused for subsequent executions of the same query plan. This avoids the overhead of compilation for frequently run complex queries.
Q217: Describe "Logical Decoding" snapshots.
When a replication slot is created, Postgres takes a "snapshot" of all currently running transactions. The logical stream will only include changes from transactions that started after this snapshot was created, ensuring a clean starting point.
Q218: What is the pg_description table?
It stores the "comments" you add to database objects using the COMMENT ON statement. This is a great way to include self-documenting metadata directly in your database schema.
Q219: Explain "Index De-duplication" (Postgres 13+).
For B-tree indexes with many duplicate values, Postgres can store the value once followed by a list of row identifiers. This can drastically reduce the size of indexes on columns with low cardinality (like status or gender).
Q220: How do you handle "High Availability" with Patroni?
Patroni is a Python-based cluster manager. It uses a distributed configuration store (like Etcd or Consul) to manage Postgres leaders and followers. If a leader fails, Patroni automatically performs failover and reconfigures the rest of the cluster.
Q221: Describe "Foreign Data Wrapper" Batching.
Postgres 14+ allows FDWs to insert rows in batches rather than one by one. This significantly reduces the network round-trips when moving large amounts of data between servers.
Q222: Explain "Operator Families" and Cross-type Comparisons.
An operator family groups multiple operator classes together. This allows the database to perform efficient index searches even when comparing different types (e.g., comparing an integer column to a bigint parameter).
Q223: What is the pg_shdescription table?
Similar to pg_description, but for "shared" objects that exist across the entire cluster, such as databases and roles.
Q224: How do you implement "Zero-Downtime Schema Migrations"?
- Add columns with defaults: In Postgres 11+, this is a metadata-only change and is instant.
- Create indexes: Use
CREATE INDEX CONCURRENTLY. - Rename tables: Use a
VIEWas a proxy to allow applications to use both names during the transition.
Q225: Describe "Internal" vs "External" Functions.
- Internal: Written in C and compiled into the Postgres binary (e.g.,
now()). - External: Written in languages like PL/pgSQL, Python, or Javascript. They are interpreted or compiled at runtime.
Q226: Explain "Sort Performance" tuning.
Sorting is one of the most expensive database operations.
- Increase
work_mem. - Use an index to provide the data in the requested order (Index Scan).
- Select only the necessary columns to reduce the size of the tuples being sorted.
Q227: What is the pg_trigger table?
It stores the definitions of all triggers in the database, including the event they fire on, the function they call, and whether they are row-level or statement-level.
Q228: How do you handle "Database Sharding" for multi-tenancy?
The "Silo" pattern: Each tenant has their own database or schema. This provides the best isolation but is hardest to manage at scale. The "Pool" pattern: All tenants share the same tables, distinguished by a tenant_id. This is easiest to manage but requires careful RLS implementation.
Q229: Describe "Plan Invalidation".
If you ANALYZE a table or change its schema, the database automatically invalidates any cached query plans for that table. The next time the query is run, the planner will generate a fresh, up-to-date plan.
Q230: Explain "GiST" index "Penalty" functions.
When inserting a new item into a GiST tree, the index must decide which branch to follow. The penalty function calculates the "cost" of adding the item to a specific branch. Minimizing this penalty ensures the tree remains balanced and efficient.
Q231: What is the pg_index table?
It contains metadata specifically for indexes: which table they belong to, which columns they cover, whether they are unique or primary keys, and whether they are "ready" for use (important during concurrent index builds).
Q232: How do you use pg_recvlogical?
This is a command-line tool for consuming a logical replication stream. It allows you to "preview" the changes being sent by a logical replication slot or even save them to a file for later processing.
Q233: Describe "Heap Scan" optimization.
Postgres uses a "Sequential Scan" by default. To speed it up, it uses a "Synchronized Scan" feature: if multiple processes are scanning the same large table, they share the same data blocks in the buffer, reducing disk I/O.
Q234: Explain "Constraint Exclusion" for old-style partitioning.
Before declarative partitioning, the planner used "Constraint Exclusion". If a query had WHERE date = '2023-01-01', the planner would check the CHECK constraints on all child tables and skip those that couldn't possibly contain that date.
Q235: What is the pg_proc table?
It stores information about every function and procedure in the database, including its arguments, return type, language, and the actual source code.
Q236: How do you implement "Data Masking" for PII?
Use a VIEW or RLS. For example, you can define a policy that only allows users in the hr group to see the social_security_number column, while everyone else sees a masked string like XXX-XX-XXXX.
Q237: Describe "Index-Only Scan" with non-key columns.
Using the INCLUDE clause, you can add "payload" columns to a unique index. These columns aren't used for uniqueness checks, but they are stored in the index, enabling index-only scans for queries that need those values.
Q238: Explain "Recursive CTE" cycle detection.
Hierarchical data can sometimes contain cycles (A -> B -> A). In a recursive CTE, you must manually implement cycle detection by keeping track of visited IDs in an array and stopping if the current ID is already in the list.
Q239: What is the pg_type table?
It contains one row for every data type in the database (built-in and user-defined). It stores information like the size of the type, how it is passed (by value or reference), and which functions are used for input and output.
Q240: How do you handle "Slow Query" diagnosis in real-time?
- Check
pg_stat_activityto see currently running queries. - Use
pg_stat_activity.wait_eventto see if they are waiting for I/O or locks. - Use
EXPLAIN (ANALYZE, BUFFERS)on the query to see where it's spending time and if it's hitting the cache.
Q241: Describe "Logical Replication" Column Mapping.
In logical replication, column names must match between the source and target tables, but the order doesn't have to. You can also replicate to a target table that has extra columns as long as they have default values.
Q242: Explain "Gin Index" "Pending List".
To speed up inserts into a GIN index, Postgres first places new entries into a flat "pending list". When this list gets too large, it is merged into the main B-tree structure. This "buffered" approach is what makes GIN indexes practical for high-update workloads.
Q243: What is the pg_rewrite table?
It stores the "Rules" for the query rewrite system. This is the underlying mechanism for views: when you query a view, a "rule" tells Postgres to replace that query with the view's definition.
Q244: How do you implement "Query Logging" based on duration?
Set log_min_duration_statement to a value like 500ms. Every query that takes longer than this will be logged to the Postgres log file along with its execution time. This is the best way to find performance outliers in production.
Q245: Describe "Parallel Hash Join" internals.
In a parallel hash join, all worker processes cooperate to build a single shared hash table in memory. They then divide the large table among themselves and perform the join concurrently, leading to nearly linear speedup on large datasets.
Q246: Explain "Table Rewrite" operations.
Some ALTER TABLE operations (like changing a column's data type) require Postgres to rewrite every single row in the table. This takes a long time and requires an exclusive lock. Always test these on a large dataset before running them in production.
Q247: What is the pg_namespace table?
It stores the definitions of all schemas in the database. Every object in Postgres has a relnamespace ID that points to a row in this table.
Q248: How do you handle "Full-Text Search" ranking?
Use ts_rank() or ts_rank_cd(). These functions calculate a score based on how frequently the search terms appear and how close they are to each other. You can use these scores to sort your search results by relevance.
Q249: Describe "Autovacuum" "Naptime".
autovacuum_naptime determines how long the autovacuum launcher sleeps between checking for tables that need vacuuming. Reducing this can make the database more responsive to sudden bursts of updates.
Q250: Explain "Shared Buffer" "Clock Sweep" algorithm.
Postgres uses a variation of the LRU algorithm called "Clock Sweep". Each page in the shared buffers has a "usage count". A "hand" moves through the buffers, decrementing the count. Pages with a count of 0 are evicted to make room for new data. This is extremely efficient for multi-threaded access.
Q251: What is the pg_authid table?
It stores the definitions of all roles (users and groups) in the cluster, including their passwords and global permissions. Because this table is shared across all databases, it is only accessible to superusers.
Q252: How do you implement "Database Level Auditing" for DDL changes?
Use an Event Trigger. You can define a trigger that fires on the ddl_command_end event. The trigger function can then log the user, the time, and the specific command that was run to an audit table.
Q253: Describe "Logical Replication" row filtering.
Postgres 15+ allows you to provide a WHERE clause when defining a publication. This allows you to replicate only a subset of rows to a specific consumer (e.g., replicating only "active" orders to a reporting database).
Q254: Explain "Index Selection" for multi-column queries.
If you have WHERE a=1 AND b=2, Postgres can:
- Use a multi-column index on
(a, b). - Use two separate indexes and perform a
Bitmap And. - Use an index on just
aand filter bybin the heap. The planner uses statistics to decide which is most efficient.
Q255: What is the pg_stat_database view?
It provides high-level statistics for the entire database: total number of commits and rollbacks, total blocks read, and the total number of deadlocks detected. It's the first place to look for general database health issues.
Q256: How do you handle "Large Result Sets" in applications?
- Use
Cursors: Allows you to fetch rows in small batches over the same connection. - Use
LIMIT/OFFSET: The standard for web pagination. - Use
Streaming: Some drivers (likepsycopg2) allow you to stream rows directly into a Python generator.
Q257: Describe "Physical Replication" "Slot" behavior.
A physical replication slot ensures that the primary doesn't delete WAL files until all standbys have received them. This is safer than wal_keep_segments but carries the risk of the primary's disk filling up if a standby goes offline.
Q258: Explain "Custom Aggregates".
Postgres allows you to define your own aggregate functions. You provide a "state transition function" (runs for every row) and a "final function" (runs at the end). This is how you can implement complex statistics or data processing directly in SQL.
Q259: What is the pg_database table?
It contains one row for every database in the cluster, including its name, its default encoding, and its default collation.
Q260: How do you handle "Database corruption" recovery?
- Restore from the last good backup and replay WAL logs (PITR).
- Use
pg_checksumsto verify data integrity on disk. - In extreme cases, use
pg_resetwalto force the database to start even if logs are missing, though this will result in data loss.
6. Expert Level PostgreSQL Internals and SQL Patterns
Q261: What is "Inter-Process Communication" (IPC) using POSIX Semaphores in PostgreSQL?
PostgreSQL uses POSIX semaphores to coordinate access to shared memory structures among its many backend processes. This is critical for the Lock Manager and the Shared Buffer Cache. On some operating systems, the default limit for semaphores is too low for high-connection Postgres clusters, requiring OS-level tuning (kernel.sem in Linux).
Q262: Deep dive: The "Checkpointer" vs "Background Writer".
- Background Writer (bgwriter): Aimed at keeping the shared buffer cache "clean". it constantly writes a small number of dirty pages to disk so that incoming backend processes can always find a free page without having to perform a slow disk write themselves.
- Checkpointer: Aimed at ensuring database consistency and reducing recovery time. It periodically flushes all dirty pages to disk and writes a "checkpoint" record. This defines the starting point for WAL replay after a crash.
Q263: Explain "Predicate Pushdown" in the context of Foreign Data Wrappers (FDW).
When you query a foreign table, Postgres tries to "push" as much of the query as possible to the remote server. This includes WHERE clauses, JOINs, and sometimes even AGGREGATEs. This is critical for performance because it minimizes the amount of data transferred over the network. If a filter can't be pushed down (e.g., it uses a local custom function), Postgres must fetch all rows and filter them locally.
Q264: How do you implement "Distributed Locking" using Postgres Advisory Locks?
Postgres advisory locks are global cluster-wide locks. They are faster than row-level locks because they don't involve the heap or MVCC.
- Session-level: Lock is held until the connection is closed or manually released.
- Transaction-level: Lock is automatically released when the current transaction ends. Use cases include ensuring only one worker processes a specific task or managing access to external non-database resources.
Q265: Describe the internal structure of a "GiST" (Generalized Search Tree) Index.
A GiST index is a balanced, tree-structured access method. Unlike B-trees which store values, GiST nodes store "predicates" (statements that are true for all items below that node). For example, a node in a geometric GiST index stores a "Bounding Box" that contains all shapes in its subtree. This allows GiST to handle complex types like ranges, geometry, and full-text.
Q266: What is the "Commit Log" (CLOG) and how does it relate to Row Visibility?
The CLOG (stored in pg_xact) is a bit array that stores 2 bits per transaction ID, representing its status: IN_PROGRESS, COMMITTED, ABORTED, or SUB_COMMITTED. Every time a process reads a row, it checks the row's xmin and xmax against the CLOG to decide if that version of the row is visible to the current transaction.
Q267: Explain "Operator Families" and how they enable cross-type indexing.
An operator family groups multiple operator classes. For example, a family for "integer-like" types includes classes for int2, int4, and int8. This allows a B-tree index on a bigint column to be used efficiently even if the search parameter is a regular integer, as the family defines how to compare across these related types.
Q268: How do you handle "Subtransaction Aliasing" and its performance pitfalls?
Each time you use a SAVEPOINT or a BEGIN...EXCEPTION block in PL/pgSQL, a subtransaction is created. Postgres tracks these in a fixed-size shared memory area. If a transaction creates too many subtransactions, this area overflows, and every visibility check in the entire database must perform a slow disk lookup to resolve sub-transaction statuses. This can cause a sudden, massive performance drop.
Q269: Describe "HOT" (Heap-Only Tuple) chain pruning.
When a page becomes full, or during a vacuum, Postgres tries to "prune" HOT chains. It removes intermediate row versions that are no longer visible to anyone and "defragments" the page. This keeps the page small and ensures that the index pointers (which only point to the head of the chain) remain valid.
Q270: What is "Write Amplification" in the context of SSDs and Postgres WAL?
Write amplification occurs when a small logical change results in a much larger physical write. In Postgres, this is caused by "Full Page Writes": the first time a data page is modified after a checkpoint, the entire 8KB page is written to the WAL to prevent data corruption from "torn writes". Tuning the checkpoint frequency and using wal_compression are key to minimizing this on high-write systems.
Q271: How do you use pg_stat_statements to find "Query Jitter"?
Query jitter refers to high variance in query execution time. You can find this by comparing mean_exec_time to stddev_exec_time in pg_stat_statements. High jitter often indicates lock contention, intermittent I/O bottlenecks, or plans that are sensitive to specific parameter values.
Q272: Explain "Index Fast Build" (Parallel Index Creation).
Introduced in Postgres 11, this allows multiple worker processes to cooperate in sorting and building a B-tree index. It is controlled by max_parallel_maintenance_workers. This can reduce the time required to create an index on a massive table from hours to minutes.
Q273: Describe "Logical Replication" Conflict Detection and Resolution.
In logical replication, conflicts can occur if a row being inserted/updated on the subscriber already exists or has been modified. By default, replication stops until the user manually resolves the conflict. Postgres 15+ introduced features to automatically "skip" conflicting transactions or resolve them based on timestamps (Last Writer Wins).
Q274: What is the "Hint Bit" mechanism and how does it prevent CLOG lookups?
The first process to determine a row's visibility (by checking the CLOG) sets a "hint bit" directly on the row in the data page (e.g., XMIN_COMMITTED). Subsequent processes can then determine visibility just by looking at the data page, avoiding the overhead of a CLOG lookup. This is a crucial micro-optimization for high-concurrency read performance.
Q275: How do you implement "Time-Travel" or "Auditing" using Triggers?
You create an "audit" table that mirrors your main table but adds action, changed_by, and changed_at columns. A BEFORE UPDATE OR DELETE trigger then copies the old version of the row into the audit table before the change is applied. This provides a complete history of every change ever made to the data.
Q276: Explain "Predicate Pushdown" in Partitioned Tables.
When you query a partitioned table, the planner tries to "push" the WHERE filters down to the individual partitions. This allows "Partition Pruning": the database only scans the specific partitions that could possibly contain matching data, which is the primary reason why partitioning improves performance for massive datasets.
Q277: Describe the "TOAST" pointer structure.
A TOAST pointer (stored in the main table row) consists of:
va_toastrelid: The OID of the TOAST table.va_valueid: The unique ID of the value within the TOAST table.va_extsize: The original (uncompressed) size of the data.va_rawsize: The actual size of the data on disk (after compression).
Q278: What is "JIT (Just-In-Time) Inlining"?
When JIT is enabled, Postgres can "inline" small SQL functions directly into the generated machine code of a query. This removes the overhead of a function call for every row, which can provide a significant speedup for queries that perform complex calculations on millions of rows.
Q279: How do you handle "Catalog Bloat"?
If you create and drop many thousands of tables or partitions, the system catalogs themselves can become bloated, making object lookups slow. Standard VACUUM works on catalogs, but in extreme cases, you might need to run VACUUM FULL on tables like pg_attribute or pg_class during a maintenance window.
Q280: Explain "Physical Replication" with "Cascading Standbys".
In this architecture, a standby server replicates from another standby server rather than directly from the primary. This reduces the network and CPU load on the primary server, allowing you to scale out to many read replicas across different geographic regions or data centers.
Q281: What is the "Operator Precedence" in PostgreSQL and how can it be overridden?
Postgres follows standard SQL precedence rules (e.g., * before +). You can use parentheses to override this. Note that Postgres allows defining custom operators, and their precedence is determined by the characters they contain (e.g., operators starting with @ have a specific precedence level).
Q282: Describe "Logical Decoding" filtering at the server level.
Postgres allows logical decoding plugins to decide which changes to include in the stream. This allows creating streams that only include data for specific tables, specific columns, or even rows that match a specific condition, minimizing the amount of data sent over the network to the consumer.
Q283: Explain "Bitmap Index Scan" memory usage (work_mem).
A bitmap index scan builds a bitmap of matching pages in memory. If the number of matching rows is very large, the bitmap can exceed work_mem. In this case, Postgres switches to a "lossy" bitmap: it only tracks which pages contain matching rows, not the specific tuples. This saves memory but requires scanning every tuple on those pages during the subsequent heap scan.
Q284: How do you implement "Read-Your-Own-Writes" consistency with replicas?
Replicas are usually asynchronous, leading to "stale" data. To ensure a user sees their own changes:
- Perform all writes and the subsequent read on the Primary.
- Use "Synchronous Replication" (slow).
- Record the LSN of the write and ensure the replica has caught up to that LSN before performing the read (using
pg_last_wal_replay_lsn()).
Q285: Describe "Partial Index" uniqueness enforcement.
A partial unique index only enforces uniqueness on the rows that match the index's WHERE clause. This is a powerful way to implement business rules like "a user can only have one active subscription" while allowing them to have many expired ones.
Q286: Explain "External Row Storage" (TOAST) and "Out-of-Line" compression.
TOAST automatically handles large values. It first tries to compress the value using the LZ4 or pglz algorithm. If it's still >2KB, it breaks it into chunks and stores them in a separate TOAST table. The main table row only stores a small pointer, ensuring that a SELECT * without the large column remains extremely fast.
Q287: What is the "Search Path" and how can it lead to security vulnerabilities?
The search_path determines the order of schema searching. An attacker who can create objects in a schema that appears earlier in the path (like public) could "shadow" built-in functions or tables with their own malicious versions. This is why security-critical functions should always use SET search_path = pg_catalog, temp and use fully qualified names for everything else.
Q288: Describe "Parallel Sequential Scan" internals.
In a parallel sequential scan, the table is divided into small chunks of pages. Worker processes take these chunks one by one and scan them. A shared "Current Page" pointer ensures that no two workers scan the same data. The results are sent back to a "Gather" node for merging.
Q289: Explain "GiST" index "Split" strategies.
When a GiST page becomes full, it must be split into two. The "split strategy" determines how to distribute the existing items between the two new pages to minimize the "overlap" between their bounding boxes. A good split strategy is critical for keeping the index efficient and preventing "false positive" branch traversals.
Q290: How do you use pg_test_timing?
This tool measures the overhead of getting the current time from the operating system. If this overhead is high, EXPLAIN ANALYZE results will be significantly distorted. This helps determine if you can trust the fine-grained timing data provided by Postgres performance tools.
Q291: Describe "Logical Replication" Sequence support.
Standard logical replication does not replicate sequence values. This is a major challenge for failover: if you switch to a subscriber, its primary key sequences might be far behind the source, leading to "duplicate key" errors. You must manually sync sequences as part of your failover procedure.
Q292: Explain "Index Re-duplication" and its impact on B-tree bloat.
Introduced in Postgres 13, this feature identifies duplicate keys in a B-tree and stores them as a single key followed by a list of TIDs (row pointers). This can reduce the size of indexes on low-cardinality columns by up to 40%, which in turn reduces I/O and improves the cache hit ratio.
Q293: What is the "Hint Bit" and "Page Checksum" interaction?
Setting a hint bit is a modification of the data page. To avoid corrupting page checksums, Postgres must recalculate the checksum and potentially write a "Full Page Image" to the WAL when a hint bit is set for the first time after a checkpoint. This is why "Read-Only" workloads can still generate significant WAL volume.
Q294: Describe "Foreign Data Wrapper" (FDW) "Foreign Join" pushdown.
If you join two tables that both reside on the same remote server, a smart FDW (like postgres_fdw) will send the entire JOIN query to the remote server, rather than fetching both tables and joining them locally. This drastically reduces network traffic and leverages the remote server's indexes.
Q295: Explain "Operator Families" and how they differ from "Operator Classes".
An operator class is for a specific data type and a specific index type. An operator family is a container for multiple operator classes that are compatible with each other. For example, a family for "integers" would contain classes for int2, int4, and int8, allowing an index on an int8 column to be used for a query with an int4 parameter.
Q296: How do you implement "Global ID" generation in a sharded Postgres cluster?
- UUIDs: The easiest and most robust way.
- Snowflake IDs: 64-bit integers composed of a timestamp, worker ID, and sequence.
- Offset Sequences: Each shard uses a different starting point and increment (e.g., Shard 1 uses 1, 3, 5... Shard 2 uses 2, 4, 6...).
Q297: Describe "Logical Decoding" and "Transaction Reordering".
The logical decoding engine reads WAL records in the order they were written (physical order). However, it must output them in the order they were committed (logical order). It uses a "Reorder Buffer" in memory to hold changes until it sees a COMMIT record, at which point it flushes all changes for that transaction to the stream.
Q298: Explain "Expression Indexes" and their impact on the Query Planner.
An expression index allows you to index the result of a function (e.g., LOWER(email)). When the planner sees a query with a matching expression in the WHERE clause, it knows it can use the index. Additionally, Postgres tracks statistics on the result of the expression, leading to better row count estimates than if it were just guessing.
Q299: What is the "Lock Manager" "Fast Path"?
To reduce overhead, Postgres can acquire certain types of locks (like relation-level AccessShareLock) without using the full, complex lock manager logic. This "fast path" uses a small array in each process's local memory and is significantly faster for the most common lock types.
Q300: Describe "Parallel Hash Join" "Shared Hash Table".
In a parallel hash join, all worker processes cooperate to build a single hash table in shared memory. One worker is designated as the "leader" and coordinates the partitioning and sizing. This allows the join to use much more memory than a single-process join and scale linearly with the number of CPU cores.
Q301: Explain "Full Page Writes" and "Torn Page" protection.
A torn page happens if the OS crashes while writing an 8KB block, leaving it half-new and half-old. To recover, Postgres writes the entire page to the WAL the first time it's modified after a checkpoint. During crash recovery, if a page is found to be corrupt, Postgres replaces it with the clean "Full Page Image" from the WAL.
Q302: How do you use pg_filedump for data recovery?
If a table file is corrupted and Postgres can't read it, pg_filedump can be used to extract the raw data bytes from the remaining valid blocks. You can then pipe this data into a new table. This is a last-resort "forensics" technique.
Q303: Describe "Logical Replication" and "REPLICA IDENTITY".
To perform updates or deletes on a subscriber, Postgres needs a way to find the correct row. REPLICA IDENTITY defines which columns are used for this.
DEFAULT: Uses the primary key.FULL: Stores the old values of all columns in the WAL (slow, but works if there is no PK).NOTHING: Updates and deletes are not allowed.
Q304: Explain "GIN Index" "Entry" vs "Posting" lists.
A GIN index stores a tree of "entries" (unique values, like words in a document). Each entry points to a "posting list" (a list of row IDs containing that value). For very common values, the posting list is transformed into a "posting tree" (a mini B-tree) to ensure that searching for those values remains efficient.
Q305: What is the "Background Writer" "LruClock" algorithm?
The bgwriter uses a "Clock Sweep" algorithm to identify "dirty" pages that haven't been accessed recently. It prioritizes writing these pages to disk because they are the most likely candidates to be evicted from the shared buffers soon, ensuring there's always "clean" space available.
Q306: Describe "Table Inheritance" and "Constraint Exclusion" caveats.
While powerful, inheritance-based partitioning has major drawbacks:
- Indexes and constraints must be created manually on every child table.
- The planner must check every child table, which becomes slow if you have thousands of partitions.
- It doesn't support modern features like unique constraints across partitions.
Q307: Explain "GiST" index "PickSplit" function.
The picksplit function is the heart of GiST indexing. When a page overflows, it decides how to distribute the entries between two new pages. The goal is to minimize the "volume" and "overlap" of the resulting bounding boxes, which directly determines the search performance of the index.
Q308: How do you implement "Application-level Failover" for Postgres?
Use a driver that supports multiple hostnames (like libpq or psycopg3). You provide a list of all cluster nodes. The driver tries them in order until it finds one that is a "primary" (writable). This avoids the need for a separate load balancer for simple setups.
Q309: Describe "Logical Decoding" and "Memory Overflow".
If a transaction is massive, its changes might exceed logical_decoding_work_mem. In this case, the decoding engine "spills" the changes to disk in the pg_replslot directory. This prevents the primary from running out of memory but makes the replication stream significantly slower.
Q310: Explain "B-Tree Index" "High Key" and "Suffix Truncation".
- High Key: Each internal node in a B-tree stores the maximum value found in its rightmost child. This allows the search to "know" if a value is beyond the range of that subtree.
- Suffix Truncation: Postgres tries to store the shortest possible prefix of a key that still uniquely distinguishes it from its neighbors, significantly reducing the size of internal index nodes.
7. PostgreSQL and SQL Final Mastery Pack
Q311: What is "Interleaved Execution" and why is it problematic for traditional query optimizers?
Interleaved execution occurs when the database must alternate between executing fragments of a query on different nodes or in different threads. For traditional optimizers, this is hard because the cost of "gathering" or "redistributing" data is difficult to estimate without knowing the actual runtime performance of the network or other nodes.
Q312: Explain the "Tuple Header" bitmask.
Each row in a Postgres data page has a 23-byte header. This header includes a t_infomask and t_infomask2. These bitmasks store flags about the row, such as whether it has NULLs, whether it has been compressed via TOAST, and its visibility status (hint bits). Understanding this is key to low-level data recovery.
Q313: How do you use pg_stat_kcache extension?
This extension supplements pg_stat_statements by adding information about operating system level resource usage for each query: CPU time (user/system), and physical disk reads/writes. This helps identify if a query's slowness is due to Postgres logic or if the OS is struggling with I/O.
Q314: Describe the "Multi-Version Concurrency Control" (MVCC) "Snapshot" format.
A snapshot is represented as xmin:xmax:xip_list.
xmin: All transactions before this ID are known to be finished (visible).xmax: All transactions after this ID are known to be in the future (invisible).xip_list: A list of transaction IDs betweenxminandxmaxthat were still active when the snapshot was taken.
Q315: Explain "Predicate Locks" vs "SIReadLocks".
Serializable Snapshot Isolation (SSI) uses "SIReadLocks" to track which transactions have read which data. If transaction A reads a row and transaction B later modifies that same row, a "conflict" is recorded. If a cycle of such conflicts is detected, one transaction is aborted to maintain serializability.
Q316: What is the purpose of pg_class.relpages and pg_class.reltuples?
relpages: The number of 8KB disk pages occupied by the table or index.reltuples: The estimated number of rows in the table. These are the two most important numbers used by the planner to calculate the cost of a query. They are updated byVACUUMandANALYZE.
Q317: How do you handle "Large Binary Data" (BLOBs) efficiently in SQL?
bytea: Stored in-line (or TOASTed). Good for smaller blobs (up to a few MB).- Large Objects (OIDs): Stored in a separate system table. Supports streaming read/write. Best for multi-GB files.
- Filesystem: Store the file on S3/Disk and store the URI in Postgres. Best for very large files or when the database shouldn't be the primary storage for media.
Q318: Describe "Logical Decoding" "Context" and its use in plugins.
A decoding plugin receives a "context" object for every transaction. This context includes the user OID, the timestamp, and the transaction ID. Plugins can use this to enrich the change stream with metadata that isn't stored in the WAL itself.
Q319: Explain "Heap-Only Tuple" (HOT) "Redirect" pointers.
When a HOT update happens, a "Redirect" pointer is placed in the line pointer array of the page. It points from the old tuple ID to the new tuple ID. This ensures that any index entries pointing to the old location are still valid and can find the new data version without being updated themselves.
Q320: What is "Write Amplification" in Cloud environments (e.g., AWS EBS)?
Cloud storage often has its own internal block size (e.g., 4KB or 16KB). If Postgres writes an 8KB page, but the underlying storage is optimized for 16KB, it can lead to extra physical I/O operations and latency spikes. Always align your Postgres page size with your storage architecture.
Q321: How do you implement "API rate limiting" using Postgres and WINDOW functions?
You can use COUNT(*) OVER (PARTITION BY user_id ORDER BY request_time RANGE BETWEEN INTERVAL '1 minute' PRECEDING AND CURRENT ROW) to see how many requests a user has made in the last minute directly in a logging query.
Q322: Describe "Physical Replication" "Keep-Alive" messages.
Standby servers periodically send a "feedback" message to the primary. This includes the standby's current LSN and a timestamp. If the primary doesn't receive this message for a certain time, it considers the standby "stale" and can potentially disconnect it to save resources.
Q323: Explain "Index De-duplication" "Posting List" format.
When de-duplicating, Postgres stores the key followed by a Posting List of row IDs. If the list is large, it is split into multiple Posting Items. This allows the index to grow incrementally without ever having to rewrite the entire list for a popular key.
Q324: What is the "Hint Bit" "Clean Page" optimization?
When a vacuum process finds a page where all rows have their hint bits set (meaning their visibility is known), it marks the page as "clean" in the Visibility Map. This is what allows future vacuums to skip that page entirely, saving massive amounts of I/O.
Q325: How do you implement "Row-Level Auditing" without performance overhead?
Use Logical Decoding. Instead of using triggers (which add overhead to every transaction), you consume the logical change stream in a separate background process and write the changes to your audit database asynchronously.
Q326: Explain "Predicate Pushdown" in "Recursive CTEs".
Optimizing recursive queries is difficult. In some cases, Postgres can "push" filters into the recursive part of the query, preventing it from generating millions of rows that will eventually be discarded. This is crucial for querying large graphs.
Q327: Describe the "TOAST" "External" vs "Extended" storage strategies.
EXTENDED: Try to compress first, then store out-of-line.EXTERNAL: Do not compress, just store out-of-line. This is faster for already-compressed data like JPEG or ZIP files.
Q328: What is "JIT (Just-In-Time) Costing"?
The planner decides whether to use JIT based on the estimated cost of the query. If the cost is above jit_above_cost, it enables JIT. If it's even higher (above jit_optimize_above_cost), it performs more aggressive optimizations. This ensures JIT is only used for queries where the compilation overhead is justified.
Q329: How do you handle "Catalog Synchronization" in a multi-primary cluster?
In clusters like BDR (Bi-Directional Replication), DDL changes must be synchronized globally. This usually involves a "Global Lock" or a consensus algorithm (Raft/Paxos) to ensure that all nodes apply the same schema changes in the exact same order.
Q330: Explain "Physical Replication" "Sync Rep" performance bottleneck.
In synchronous replication, the primary must wait for the standby to acknowledge every commit. This adds the network round-trip time to every single write transaction. If the network is slow or the standby is busy, the entire application will crawl.
Q331: What is the "Operator Precedence" for custom operators?
The precedence of a custom operator is determined by its first character. For example, any operator starting with * has the same precedence as the standard multiplication operator. This consistency is what makes Postgres's extensible operator system work.
Q332: Describe "Logical Decoding" "Origin" tracking.
To prevent infinite loops in multi-way replication (A -> B -> A), Postgres can attach an "Origin" ID to every change in the logical stream. A subscriber can then be configured to ignore any changes that originated from itself.
Q333: Explain "Bitmap Index Scan" "Recheck" condition.
If a bitmap becomes "lossy" (due to work_mem limits), it only knows that a page has a matching row. During the heap scan, Postgres must "recheck" the query conditions for every single row on that page. This information is shown in EXPLAIN ANALYZE and is a sign you should increase work_mem.
Q334: How do you implement "Global Transactions" (XA) with Postgres?
Use PREPARE TRANSACTION 'id'. This puts the transaction into a "prepared" state where it survives crashes and can be finished by any other connection later using COMMIT PREPARED or ROLLBACK PREPARED. This is the standard for 2-phase commit.
Q335: Describe "Partial Index" "Optimization" for range queries.
If you frequently query WHERE status = 'active' AND created_at > ..., creating a partial index ON (created_at) WHERE status = 'active' is much smaller and more efficient than a full index on (status, created_at).
Q336: Explain "External Row Storage" (TOAST) and "In-Line" compression.
Postgres always tries to keep a row below 2KB (one-fourth of a page). It will try to compress compressible columns in-place before giving up and moving them to the TOAST table. This "aggressive inlining" is why Postgres handles medium-sized text fields so well.
Q337: What is the "Search Path" "User" variable?
$user in the search path is a placeholder that refers to the name of the currently logged-in user. This allows creating a personal schema for every user where they can store temporary tables or custom functions without affecting anyone else.
Q338: Describe "Parallel Append" internals.
Used for partitioned tables or UNION ALL queries. Multiple workers can scan different child tables or partitions simultaneously. This allows a massive query over many years of partitioned data to utilize all available CPU cores efficiently.
Q339: Explain "GiST" index "Compress" and "Decompress" functions.
These functions allow a GiST index to store data in a different format than the heap. For example, a full-text GiST index "compresses" a document into a small bit-signature (Bloom filter) for efficient tree searching.
Q340: How do you use pg_test_fsync to choose a filesystem?
Different filesystems (Ext4, XFS, ZFS) handle data flushing differently. pg_test_fsync will show you the latency of different flush methods (fdatasync, fsync_writethrough, etc.) allowing you to pick the one that offers the best balance of safety and speed for your hardware.
Q341: Describe "Logical Replication" "Publication" types.
FOR TABLE x, y: Replicates specific tables.FOR ALL TABLES: Replicates the entire database.FOR TABLES IN SCHEMA: (Postgres 15+) Replicates everything in specific schemas.
Q342: Explain "Index Re-duplication" "Threshold".
Postgres doesn't de-duplicate every single key. It uses a threshold (usually 4 row IDs) before it triggers de-duplication logic. This avoids the overhead of managing posting lists for keys that are almost unique.
Q343: What is the "Hint Bit" "Torn Page" risk?
Technically, setting a hint bit could cause a torn page if the OS crashes at that exact microsecond. To prevent this, Postgres treats the setting of the first hint bit on a page after a checkpoint as a "Partial Page Write", ensuring that a full image is saved to the WAL.
Q344: Describe "Foreign Data Wrapper" (FDW) "Foreign Aggregate" pushdown.
If you run SELECT COUNT(*) FROM foreign_table, a smart FDW will send the COUNT(*) to the remote server and just return the single integer result. Without this, Postgres would have to download the entire table just to count the rows.
Q345: Explain "Operator Families" and "Index Support" functions.
A B-tree index requires a "comparison" support function that returns -1, 0, or 1. A GiST index requires "union", "penalty", and "picksplit" functions. The operator family links these internal support functions to the SQL operators used in your queries.
Q346: How do you implement "Sharding" for Time-Series data?
Use "Declarative Partitioning" by month or day. For very high write loads, use Citus to distribute these partitions across multiple physical servers based on the time range, allowing you to scale ingestion linearly.
Q347: Describe "Logical Decoding" "Streaming" mode.
Postgres 14+ allows logical changes to be streamed to the consumer before the transaction has committed. This is critical for reducing the lag of massive, multi-hour transactions in the replication stream.
Q348: Explain "Expression Indexes" and "Null" values.
An expression index on (COALESCE(email, '')) allows you to perform fast, indexed searches that include the rows where email was NULL, which a standard index on email would struggle with (depending on the comparison).
Q349: What is the "Lock Manager" "Wait Queue"?
When a lock is requested but not available, the process is added to a "Wait Queue". The queue is prioritized: certain lock types or processes might be moved to the front to prevent starvation. The deadlock detector periodically re-shuffles the queue to see if it can resolve a circular dependency.
Q350: Describe "Parallel Hash Join" "Batching".
If the shared hash table for a join is too large to fit in memory, Postgres splits the join into "batches". It processes one batch of data in memory and spills the others to disk, ensuring that even massive joins can be completed without crashing the server.
Q351: Explain "Full Page Writes" and "Storage Alignment".
If your hardware (e.g., a high-end RAID controller) provides its own torn-page protection, you can sometimes disable full_page_writes in Postgres to drastically reduce WAL volume. However, this is dangerous and should only be done with expert knowledge of your storage stack.
Q352: How do you use pg_filedump to recover "Deleted" rows?
Because of MVCC, "deleted" rows stay on disk until vacuumed. pg_filedump can find these rows (by looking for tuples with xmax set but not yet vacuumed) and extract their data, allowing for "undelete" operations if you act fast enough.
Q353: Describe "Logical Replication" and "Failover Slots".
By default, replication slots are lost on failover. "Failover Slots" (introduced in Postgres 17) synchronize slot metadata to standby servers, allowing logical consumers to seamlessly switch to the new primary without losing their place in the stream.
Q354: Explain "GIN Index" "Entry Tree" structure.
The entries in a GIN index (the unique keys) are themselves stored in a standard B-tree. This allows for fast equality and range searches on the keys themselves (e.g., finding all documents that contain a word starting with "database").
Q355: What is the "Background Writer" "Density" calculation?
The bgwriter calculates how "dense" the shared buffers are with dirty pages. If the density is high, it writes more aggressively. If the density is low, it slows down to save I/O for the actual application processes.
Q356: Describe "Table Inheritance" and "Schema evolution" challenges.
If you add a column to a parent table, it is automatically added to all children. However, if you add a column to a child table, it is NOT added to the parent. Managing these inconsistencies in a complex inheritance hierarchy is a common source of bugs.
Q357: Explain "GiST" index "Consistent" function.
The consistent function is what the index calls for every node it visits during a search. It takes the search query and the node's predicate and returns "maybe" or "no". This "fuzzy" matching is what allows GiST to handle complex range and geometric queries.
Q358: How do you implement "Zero-Downtime" version upgrades?
- Set up a logical replica using the new version.
- Wait for the new version to catch up to the old one.
- Switch the application traffic to the new database.
- Shut down the old version.
Q359: Describe "Logical Decoding" and "WAL recycling".
A logical replication slot prevents the primary from recycling WAL files until the consumer has acknowledged them. If a consumer goes offline for days, your pg_wal directory can grow to fill the entire disk, causing a system-wide crash. Always set a max_replication_slots_size.
Q360: Explain "B-Tree Index" "Internal" vs "Leaf" pages.
- Leaf Pages: Store the actual data keys and pointers to the rows in the heap.
- Internal Pages: Store keys and pointers to other index pages. The root page is the entry point. A search traverses internal pages based on key ranges until it hits a leaf page.
8. PostgreSQL Advanced Interview Pack
Q361: How does SQL logical query processing order differ from written syntax?
Technical answer: SQL is parsed in one order but logically evaluated as FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then LIMIT.
Example: SELECT dept, COUNT(*) FROM employees WHERE active = true GROUP BY dept HAVING COUNT(*) > 5 ORDER BY dept;
Interviewer notes: Strong candidates explain why aliases usually are not available in WHERE but are in ORDER BY.
Red flags: Treating SQL as if it executes top to bottom.
Q362: When should you use INNER JOIN versus LEFT JOIN?
Technical answer: Use INNER JOIN when unmatched rows should be dropped and LEFT JOIN when the left side must be preserved even if no match exists.
Example: SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON c.id = o.customer_id;
Interviewer notes: Look for clarity on null-extended rows and accidental row loss.
Red flags: Using LEFT JOIN by habit without explaining the data shape.
Q363: What is the practical difference between EXISTS and IN?
Technical answer: EXISTS checks for the presence of at least one matching row and is usually better for correlated subqueries; IN compares against a value set and can be less predictable with NULLs.
Example: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
Interviewer notes: Good answers mention NULL semantics and planner behavior.
Red flags: Saying they are always interchangeable.
Q364: Why can GROUP BY queries fail even when the selected columns look valid?
Technical answer: Every selected non-aggregated column must be functionally dependent on the grouping columns, or explicitly included in GROUP BY.
Example: SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id;
Interviewer notes: Candidates should explain functional dependency and the SQL standard.
Red flags: Claiming PostgreSQL “just picks one row”.
Q365: When is HAVING necessary instead of WHERE?
Technical answer: WHERE filters rows before aggregation, while HAVING filters grouped results after aggregation.
Example: SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 10;
Interviewer notes: Ask for a concrete example with aggregate filtering.
Red flags: Using HAVING for non-aggregate predicates without reason.
Q366: What makes a constraint deferrable?
Technical answer: A deferrable constraint can be checked at commit time instead of immediately, which is useful for complex multi-row updates.
Example: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED;
Interviewer notes: Useful for swap operations and cyclic dependencies.
Red flags: Not knowing the difference between DEFERRABLE and NOT DEFERRABLE.
Q367: How should foreign key columns be indexed?
Technical answer: Index foreign key columns on the child table to speed up joins and parent deletes or updates that must validate references.
Example: CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Interviewer notes: Strong candidates mention the parent key is already indexed by the primary key or unique constraint.
Red flags: Forgetting child-side indexing in write-heavy systems.
Q368: What problem does SAVEPOINT solve?
Technical answer: SAVEPOINT lets you partially roll back a transaction without aborting the entire unit of work.
Example: SAVEPOINT s1; UPDATE accounts SET balance = balance - 10 WHERE id = 1; ROLLBACK TO s1;
Interviewer notes: Good for batch imports and multi-step workflows.
Red flags: Using it as a replacement for application-level error handling.
Q369: What is the main limitation of READ COMMITTED?
Technical answer: Each statement sees a fresh snapshot, so a transaction can observe different committed data across statements.
Example: Two SELECT statements in the same transaction can return different counts if concurrent writes commit between them.
Interviewer notes: Mention non-repeatable reads and why this is the default.
Red flags: Claiming a transaction sees a single stable snapshot at this level.
Q370: How does PostgreSQL implement MVCC visibility?
Technical answer: Each row version stores transaction metadata, and readers decide visibility based on snapshot information without blocking writers.
Example: An UPDATE creates a new tuple version instead of overwriting the old one in place.
Interviewer notes: Strong answers explain xmin, xmax, and snapshot semantics at a high level.
Red flags: Describing MVCC as a lock manager.
Q371: Why can long-running transactions hurt vacuum?
Technical answer: They keep old snapshots alive, so dead tuples remain potentially visible and cannot be reclaimed safely. Example: A reporting transaction left open for hours can prevent table bloat cleanup. Interviewer notes: Look for discussion of xid horizon and bloat. Red flags: Ignoring open idle transactions.
Q372: What is the purpose of VACUUM versus VACUUM FULL?
Technical answer: VACUUM reclaims space for reuse inside the table; VACUUM FULL rewrites the table and compacts disk usage, but it takes stronger locks.
Example: VACUUM (ANALYZE) orders;
Interviewer notes: Candidates should know VACUUM FULL is usually an offline maintenance tool.
Red flags: Recommending VACUUM FULL as routine maintenance.
Q373: Why do HOT updates matter?
Technical answer: Heap-only tuple updates avoid changing indexes when indexed columns do not change, reducing write amplification.
Example: Updating a status column not covered by an index can often be HOT if page space is available.
Interviewer notes: Mention fillfactor and page space on the same table page.
Red flags: Saying all updates are equally expensive.
Q374: What does fillfactor control?
Technical answer: It reserves free space on table or index pages to reduce page splits and improve HOT update chances.
Example: ALTER TABLE events SET (fillfactor = 80);
Interviewer notes: Best answers tie it to update-heavy tables and index maintenance.
Red flags: Treating it as a performance win for every table.
Q375: When is a B-tree index the right choice?
Technical answer: B-tree is the default for equality, range, ordering, and prefix-friendly comparisons on scalar values.
Example: CREATE INDEX ON invoices(created_at);
Interviewer notes: Good candidates can explain why B-tree is usually first choice before specialized indexes.
Red flags: Jumping to GIN or GiST without a query pattern.
Q376: What query patterns fit a GIN index?
Technical answer: GIN works well for many-to-one lookups such as JSONB containment, arrays, and full-text search.
Example: CREATE INDEX ON docs USING GIN (tags);
Interviewer notes: Mention that GIN is usually better for read-heavy search workloads.
Red flags: Using GIN for highly volatile columns without discussing update cost.
Q377: What is a BRIN index good for?
Technical answer: BRIN summarizes value ranges per block and is effective for huge tables where values correlate with physical order.
Example: CREATE INDEX ON measurements USING BRIN (created_at);
Interviewer notes: Ideal for append-only time-series data.
Red flags: Recommending BRIN for small, random-access tables.
Q378: Why use a partial index?
Technical answer: A partial index covers only rows matching a predicate, shrinking index size and improving maintenance for selective queries.
Example: CREATE INDEX ON tickets(created_at) WHERE status = 'open';
Interviewer notes: Ask whether the query predicate exactly matches the index predicate.
Red flags: Proposing partial indexes without stable query patterns.
Q379: What is a covering index in PostgreSQL terms?
Technical answer: A covering index includes all needed columns so the planner may use an index-only scan and avoid heap access.
Example: CREATE INDEX ON orders(customer_id) INCLUDE (total, created_at);
Interviewer notes: Good candidates mention the visibility map requirement.
Red flags: Assuming INCLUDE always guarantees index-only scans.
Q380: How do you interpret EXPLAIN ANALYZE?
Technical answer: It shows estimated versus actual costs, row counts, timing, and the chosen plan nodes for a query.
Example: EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
Interviewer notes: Strong candidates look for row estimate mismatches and expensive node types.
Red flags: Reading only the total execution time and ignoring cardinality errors.
Q381: Why do stale statistics cause bad plans?
Technical answer: The planner depends on table and column statistics to estimate selectivity, join size, and costs.
Example: ANALYZE orders;
Interviewer notes: Mention autovacuum analyze thresholds and skewed distributions.
Red flags: Blaming the planner without checking stats freshness.
Q382: What role does work_mem play in performance?
Technical answer: It caps memory for sorts, hash joins, and similar per-operation work, so too little causes spills and too much can exhaust RAM.
Example: SET work_mem = '64MB';
Interviewer notes: Good answers explain that the setting applies per operation, not per session total.
Red flags: Tuning it high globally without considering concurrency.
Q383: What is the WAL used for?
Technical answer: Write-ahead logging records changes before they are applied so PostgreSQL can recover after crashes and support replication. Example: Committing a transaction writes its WAL records before data pages are guaranteed durable. Interviewer notes: Ask for durability and recovery reasoning, not just replication. Red flags: Describing WAL as a simple audit log.
Q384: Why are checkpoints important?
Technical answer: Checkpoints limit recovery time by flushing dirty buffers and establishing a recovery restart point in WAL.
Example: CHECKPOINT;
Interviewer notes: Candidates should connect checkpoint frequency to write spikes and crash recovery time.
Red flags: Treating checkpoints as optional housekeeping.
Q385: How does logical replication differ from physical replication?
Technical answer: Physical replication ships page-level changes for an entire cluster, while logical replication streams row-level changes for selected tables.
Example: CREATE PUBLICATION pub1 FOR TABLE orders;
Interviewer notes: Strong answers mention selective replication and schema flexibility.
Red flags: Saying logical replication copies the whole cluster.
Q386: What is a replication slot and why can it be dangerous?
Technical answer: A slot prevents WAL from being recycled until a consumer confirms receipt, which protects replication but can fill disk if the consumer lags.
Example: SELECT * FROM pg_replication_slots;
Interviewer notes: Mention monitoring slot lag and disk usage.
Red flags: Ignoring slot retention risk in production.
Q387: What is the benefit of partitioning a large table?
Technical answer: Partitioning improves manageability, pruning, retention, and sometimes write or read performance by splitting data into smaller chunks.
Example: CREATE TABLE events (...) PARTITION BY RANGE (created_at);
Interviewer notes: Good answers note that partitioning is not a universal performance fix.
Red flags: Partitioning without a clear pruning or retention use case.
Q388: What is partition pruning?
Technical answer: The planner skips partitions that cannot satisfy the query predicate, reducing scans dramatically.
Example: SELECT * FROM events WHERE created_at >= DATE '2026-01-01';
Interviewer notes: Ask whether pruning happens at plan time or execution time.
Red flags: Believing all partitions are scanned by default.
Q389: How do you choose a partition key?
Technical answer: Pick a column used in most filters, with a distribution that keeps partitions balanced and maintenance practical.
Example: Time-series data often uses created_at as the range partition key.
Interviewer notes: Good candidates discuss cardinality, skew, and lifecycle operations.
Red flags: Choosing a key just because it is popular elsewhere.
Q390: Why are CTEs sometimes slower than inline subqueries?
Technical answer: A CTE can act as an optimization fence in some versions or query shapes, forcing materialization and blocking pushdown.
Example: WITH active AS (...) SELECT * FROM active WHERE ...;
Interviewer notes: Strong answers know newer PostgreSQL versions can inline many CTEs.
Red flags: Saying CTEs are always faster because they are readable.
Q391: When is a recursive CTE appropriate?
Technical answer: Use it for hierarchical or graph-like data such as org charts, dependencies, or path traversal.
Example: WITH RECURSIVE tree AS (...) SELECT * FROM tree;
Interviewer notes: Look for awareness of termination conditions and cycle handling.
Red flags: Using recursion when a simple self-join is enough.
Q392: What problem do window functions solve?
Technical answer: They let you compute analytics over a partition of rows without collapsing the result set.
Example: SELECT user_id, row_number() OVER (PARTITION BY user_id ORDER BY created_at) FROM events;
Interviewer notes: Good candidates distinguish them from aggregates.
Red flags: Treating window functions like grouped aggregates.
Q393: How do ROW_NUMBER, RANK, and DENSE_RANK differ?
Technical answer: ROW_NUMBER gives unique sequence numbers, RANK leaves gaps after ties, and DENSE_RANK does not.
Example: SELECT score, rank() OVER (ORDER BY score DESC) FROM leaderboard;
Interviewer notes: Ask for leaderboard or pagination examples.
Red flags: Mixing up gap behavior.
Q394: What is LAG used for?
Technical answer: LAG reads a prior row in the same window, which is useful for deltas, trend checks, and session gap analysis.
Example: SELECT value - LAG(value) OVER (ORDER BY day) AS delta FROM metrics;
Interviewer notes: Good candidates mention LEAD as the mirror operation.
Red flags: Rewriting this with self-joins without recognizing the simpler tool.
Q395: Why is JSONB often preferred over JSON?
Technical answer: JSONB stores parsed binary data, supports indexing and containment operations, and generally performs better for querying.
Example: CREATE INDEX ON profiles USING GIN (metadata jsonb_path_ops);
Interviewer notes: Mention that JSON preserves formatting but is less query-friendly.
Red flags: Claiming JSONB is always smaller.
Q396: What is a common pitfall with JSONB indexes?
Technical answer: Poorly chosen index types or overly broad GIN indexes can become large and slow to maintain.
Example: Index the actual access path, such as metadata -> 'tags', not the entire document if only one field is queried.
Interviewer notes: Ask about write amplification and selectivity.
Red flags: Indexing every JSONB column by default.
Q397: When should you use a generated column?
Technical answer: Use it when a derived value is queried frequently and should be stored consistently without application duplication.
Example: ALTER TABLE users ADD COLUMN email_domain text GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED;
Interviewer notes: Good answers distinguish stored versus virtual behavior where supported.
Red flags: Using generated columns for expensive, unstable computations without need.
Q398: What is the difference between SERIAL and identity columns?
Technical answer: Identity columns are the modern SQL-standard approach, while SERIAL is PostgreSQL-specific shorthand built on sequences.
Example: id bigint GENERATED ALWAYS AS IDENTITY
Interviewer notes: Mention migration and standardization benefits.
Red flags: Treating them as different storage mechanisms.
Q399: Why can UUID primary keys hurt locality?
Technical answer: Random UUIDs scatter inserts across the B-tree, increasing page splits and reducing cache locality compared with monotonic keys.
Example: id UUID PRIMARY KEY DEFAULT gen_random_uuid()
Interviewer notes: Strong answers discuss v7/ordered UUIDs as a mitigation.
Red flags: Saying UUIDs are always superior because they are globally unique.
Q400: What is a schema-safe way to implement soft deletes?
Technical answer: Add a tombstone column and consistently filter on it, but avoid relying on it for strict retention or privacy needs.
Example: ALTER TABLE customers ADD COLUMN deleted_at timestamptz;
Interviewer notes: Good candidates mention partial unique indexes to preserve uniqueness among active rows.
Red flags: Ignoring query filters across the application.
Q401: How do you model many-to-many relationships?
Technical answer: Use a join table with foreign keys to both sides, and usually a composite unique constraint on the pair.
Example: CREATE TABLE user_roles (user_id bigint REFERENCES users(id), role_id bigint REFERENCES roles(id), UNIQUE (user_id, role_id));
Interviewer notes: Look for normalized design and cascade behavior.
Red flags: Packing arrays of IDs into one column.
Q402: What are common pitfalls in multi-tenant schema design?
Technical answer: The main tradeoff is isolation versus operational simplicity, especially around indexing, backups, and query scoping.
Example: A shared table with tenant_id often needs tenant-scoped indexes and RLS.
Interviewer notes: Strong answers compare shared-schema, schema-per-tenant, and database-per-tenant patterns.
Red flags: Ignoring tenant isolation in queries and migrations.
Q403: What does row-level security protect?
Technical answer: RLS enforces row filters inside the database so users only see rows allowed by policy.
Example: ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
Interviewer notes: Ask how policies interact with roles and application users.
Red flags: Assuming RLS alone replaces all application authorization.
Q404: What is SELECT ... FOR UPDATE SKIP LOCKED good for?
Technical answer: It is useful for queue consumers that need to claim work items without blocking on rows already taken by another worker.
Example: SELECT id FROM jobs WHERE status = 'ready' FOR UPDATE SKIP LOCKED LIMIT 1;
Interviewer notes: Candidates should mention fairness and retry semantics.
Red flags: Using it without a clear worker-claim protocol.
Q405: How do you prevent lost updates?
Technical answer: Use row locks, optimistic version checks, or serializable transactions depending on contention and correctness needs.
Example: UPDATE accounts SET balance = balance - 10, version = version + 1 WHERE id = 1 AND version = 7;
Interviewer notes: Good answers compare pessimistic and optimistic approaches.
Red flags: Relying on application read-modify-write loops alone.
Q406: Why can DDL block production traffic?
Technical answer: Some schema changes require strong locks that conflict with reads or writes, especially on large busy tables.
Example: ALTER TABLE big_table ADD COLUMN note text;
Interviewer notes: Look for awareness of CREATE INDEX CONCURRENTLY and phased migrations.
Red flags: Assuming all DDL is instantaneous.
Q407: What is CREATE INDEX CONCURRENTLY for?
Technical answer: It builds an index without blocking normal reads and writes, though it takes longer and has more operational constraints.
Example: CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);
Interviewer notes: Mention that it cannot run inside a transaction block.
Red flags: Forgetting the operational tradeoff.
Q408: How should you think about COUNT(*) performance?
Technical answer: COUNT(*) may still need to scan many rows unless a narrow index or approximate strategy is available, because PostgreSQL cannot always use metadata alone.
Example: SELECT COUNT(*) FROM events WHERE created_at >= now() - interval '1 day';
Interviewer notes: Strong answers distinguish exact counts from approximate metrics.
Red flags: Assuming counts are always O(1).
Q409: What is the risk of overusing DISTINCT?
Technical answer: DISTINCT can hide bad joins and force extra sorting or hashing, so it should solve a real duplication problem rather than mask query bugs.
Example: SELECT DISTINCT user_id FROM sessions;
Interviewer notes: Ask whether the duplicates should be removed in the join logic instead.
Red flags: Adding DISTINCT as a first debugging step.
Q410: What is the safest approach to backup and restore validation?
Technical answer: Backups are only useful if restores are tested, so practice both logical and physical recovery before an incident.
Example: pg_dump dbname | psql restore_db
Interviewer notes: Good candidates mention PITR drills and restore time objectives.
Red flags: Saying backups are complete without proving recovery.
Q411: What is a common join pitfall with duplicated rows?
Technical answer: Joining on a non-unique key can multiply rows unexpectedly, so you need to verify cardinality on both sides before blaming the query.
Example: SELECT * FROM orders o JOIN order_items i ON i.order_id = o.id;
Interviewer notes: Ask whether the result explosion is intended or a modeling bug.
Red flags: Assuming every join is one-to-one.
Q412: How do you safely deduplicate rows in PostgreSQL?
Technical answer: Identify the business key, choose a survivor row with ROW_NUMBER(), and delete the rest in a controlled transaction.
Example: DELETE FROM t USING (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users) d WHERE t.id = d.id AND d.rn > 1;
Interviewer notes: Good answers mention taking backups first.
Red flags: Deleting duplicates without confirming the retention rule.
Q413: Why can ORDER BY be expensive?
Technical answer: Sorting large result sets can spill to disk if memory is insufficient, and even indexed ordering can still be costly with wide rows.
Example: SELECT * FROM events ORDER BY created_at DESC LIMIT 100;
Interviewer notes: Strong candidates mention indexes, top-N sort, and work_mem.
Red flags: Ignoring the cost of wide sorts.
Q414: What is a materialized view best used for?
Technical answer: A materialized view stores precomputed query results for expensive aggregations or reporting workloads.
Example: CREATE MATERIALIZED VIEW daily_sales AS SELECT date_trunc('day', created_at), SUM(total) FROM orders GROUP BY 1;
Interviewer notes: Ask how it is refreshed and how stale data is acceptable.
Red flags: Treating it like a live view.
Q415: What is the downside of too many indexes?
Technical answer: Every write must update every affected index, so excessive indexing slows inserts, updates, and vacuum while increasing storage. Example: A table with ten indexes can be much slower to ingest than one with two well-chosen indexes. Interviewer notes: Good candidates discuss read/write tradeoffs. Red flags: Recommending an index for every filter column by default.
Q416: How do you decide between jsonb and normalized tables?
Technical answer: Use normalized tables when relationships and queries are stable; use JSONB when schema is flexible and queries target document-like attributes. Example: Store audit metadata in JSONB, but keep account relationships relational. Interviewer notes: Look for explicit discussion of indexing and integrity. Red flags: Choosing JSONB to avoid schema design.
Q417: What is a common mistake with CTEs and pagination?
Technical answer: Materializing a large CTE before applying LIMIT can make pagination much slower than expected.
Example: WITH base AS (...) SELECT * FROM base ORDER BY id LIMIT 50;
Interviewer notes: Candidates should know when to inline the query.
Red flags: Using CTEs as a blanket style rule.
Q418: How can OFFSET hurt large-page pagination?
Technical answer: The database still has to walk past the skipped rows, so large offsets get slower as page numbers grow.
Example: Prefer keyset pagination such as WHERE id > last_seen_id ORDER BY id LIMIT 50.
Interviewer notes: Ask for keyset pagination as the better pattern.
Red flags: Using high offsets in user-facing infinite scroll.
Q419: What is keyset pagination?
Technical answer: It pages using a stable sort key and a cursor value instead of a numeric offset.
Example: SELECT * FROM events WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 50;
Interviewer notes: Good answers mention deterministic ordering and tie breakers.
Red flags: Using non-unique ordering columns without a secondary key.
Q420: Why is COUNT(DISTINCT ...) often expensive?
Technical answer: It requires deduplication work, usually through sorting or hashing, which can be much more expensive than a plain count.
Example: SELECT COUNT(DISTINCT user_id) FROM events;
Interviewer notes: Strong candidates discuss approximate alternatives if exactness is not required.
Red flags: Treating distinct counts as cheap at scale.
Q421: What does ANALYZE actually do?
Technical answer: It samples table data to build statistics about value distribution, null fraction, and distinct counts for the planner.
Example: ANALYZE orders;
Interviewer notes: Mention manual ANALYZE after bulk loads.
Red flags: Confusing ANALYZE with data cleanup.
Q422: What is the main purpose of autovacuum tuning?
Technical answer: Autovacuum keeps bloat and statistics under control, so tuning balances cleanup frequency with write overhead. Example: Adjust table-level vacuum thresholds for a hot table. Interviewer notes: Good answers mention per-table storage parameters. Red flags: Disabling autovacuum globally.
Q423: What happens during a B-tree page split?
Technical answer: When a page fills, PostgreSQL splits it into two pages, which increases write cost and can fragment the index. Example: Random UUID inserts often trigger frequent splits. Interviewer notes: Ask how fillfactor and insert order affect splits. Red flags: Assuming index growth is always linear and cheap.
Q424: Why does CREATE INDEX CONCURRENTLY take longer?
Technical answer: It avoids blocking writes by building in phases and validating later, which adds extra work and coordination. Example: The operation may scan the table more than once. Interviewer notes: Good candidates know it can fail and leave an invalid index. Red flags: Using it blindly in migration windows without monitoring.
Q425: What is the relationship between WAL and replicas?
Technical answer: Physical replicas replay WAL to stay in sync with the primary, so WAL retention and shipping determine replica freshness.
Example: pg_stat_replication shows replay lag.
Interviewer notes: Strong answers connect WAL volume to replication bandwidth.
Red flags: Treating replicas as independent read-only copies.
Q426: What is a common restore pitfall with large databases?
Technical answer: Restoring schema objects, indexes, and constraints can dominate recovery time, so the backup format and restore order matter.
Example: Restoring a custom-format pg_dump with parallel jobs can be much faster than plain SQL.
Interviewer notes: Ask about restore testing and dependency ordering.
Red flags: Assuming restore time equals dump time.
Q427: Why is EXPLAIN output not enough by itself?
Technical answer: Estimates can be wrong, so EXPLAIN ANALYZE with real execution statistics is needed to see actual behavior.
Example: A plan with low estimated cost may still be slow because of bad row estimates.
Interviewer notes: Good candidates compare estimated and actual rows.
Red flags: Optimizing based only on the planner’s guesses.
Q428: What is a common mistake when using aggregates with joins?
Technical answer: Joining before aggregating can multiply rows and inflate sums or counts unless you aggregate at the right grain. Example: Pre-aggregate order items before joining to orders if the metrics are per order. Interviewer notes: Strong answers reason about grain and grouping keys. Red flags: Summing after a many-to-many join without checking duplication.
Q429: How do you avoid full-table scans on common filters?
Technical answer: Add the right selective index, keep predicates sargable, and avoid wrapping indexed columns in functions when possible.
Example: Prefer created_at >= now() - interval '7 days' over date(created_at) = current_date.
Interviewer notes: Ask for sargable predicates and functional indexes when needed.
Red flags: Function-wrapping columns and expecting normal indexes to help.
Q430: What is the safest way to delete large amounts of old data?
Technical answer: Delete in batches, monitor locks and replication lag, and prefer partition drops when retention boundaries are natural.
Example: DELETE FROM logs WHERE created_at < now() - interval '90 days';
Interviewer notes: Good answers mention batch size and maintenance windows.
Red flags: Running one giant delete on a hot table.
Q431: Why should you avoid SELECT * in production code?
Technical answer: It couples callers to schema changes, transfers unnecessary columns, and can make queries harder to optimize.
Example: SELECT id, name FROM users;
Interviewer notes: Strong candidates mention smaller I/O and clearer intent.
Red flags: Defending SELECT * as harmless convenience.
Q432: What does COALESCE do?
Technical answer: It returns the first non-null value, which is useful for defaults, display logic, and null-safe expressions.
Example: SELECT COALESCE(nickname, username) FROM users;
Interviewer notes: Good answers note it can be used in expression indexes.
Red flags: Using it to hide bad data modeling.
Q433: How should you handle serialization failures?
Technical answer: Serializable transactions can abort, so the application must retry the whole transaction safely. Example: Retry the unit of work when PostgreSQL raises a serialization error. Interviewer notes: Ask for idempotency and backoff strategy. Red flags: Treating serialization failures as impossible bugs.
Q434: What is a dead tuple?
Technical answer: It is an old row version that is no longer visible to any active snapshot and can eventually be reclaimed. Example: Frequent updates generate dead tuples until vacuum processes them. Interviewer notes: Strong candidates tie this to MVCC bloat. Red flags: Confusing dead tuples with deleted rows at the logical level.
Q435: Why can wide rows hurt performance?
Technical answer: Wide rows reduce cache density, increase I/O, and can push columns into TOAST storage or create larger tuples. Example: Keep blobs out of frequently joined tables when possible. Interviewer notes: Good candidates focus on workload impact, not type fear. Red flags: Calling all wide columns bad without context.
Q436: What is a common anti-pattern with NOT IN?
Technical answer: NOT IN can produce surprising results when the subquery yields NULLs, so NOT EXISTS is usually safer.
Example: SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.id);
Interviewer notes: Good answers explain three-valued logic.
Red flags: Using NOT IN without considering NULL semantics.
Q437: When should you use an expression index?
Technical answer: Use it when queries repeatedly filter or sort on a deterministic expression.
Example: CREATE INDEX ON users (lower(email));
Interviewer notes: Mention exact expression matching and collation concerns.
Red flags: Indexing arbitrary expressions without a query pattern.
Q438: What is LISTEN/NOTIFY good for?
Technical answer: It provides lightweight async signaling between sessions, often for cache invalidation or job wakeups.
Example: NOTIFY cache_invalidate, 'users:42';
Interviewer notes: Good candidates mention it is not a durable message queue.
Red flags: Using it for critical delivery guarantees.
Q439: Why do sequence values have gaps?
Technical answer: Sequence values are allocated outside transaction rollback semantics, so aborted transactions and caching can create gaps.
Example: A row can roll back after consuming nextval().
Interviewer notes: Candidates should know gaps are expected behavior.
Red flags: Treating sequence gaps as corruption.
Q440: What is a good baseline for query tuning order?
Technical answer: Start with schema and access patterns, then stats, indexes, query shape, and only then memory or config tuning.
Example: Fix a bad join before raising work_mem.
Interviewer notes: Strong answers prioritize root cause over knob turning.
Red flags: Jumping straight to server configuration.
Q441: How does FOR SHARE differ from FOR UPDATE?
Technical answer: FOR SHARE takes a weaker lock suitable for read coordination, while FOR UPDATE reserves rows for modification.
Example: SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
Interviewer notes: Ask about concurrent writers and lock compatibility.
Red flags: Not knowing lock strength differences.
Q442: What does statement_timeout protect you from?
Technical answer: It cancels queries that run too long, helping prevent runaway workloads from consuming resources indefinitely.
Example: SET statement_timeout = '5s';
Interviewer notes: Good candidates distinguish it from lock timeout and idle timeout.
Red flags: Using it as the only reliability safeguard.
Q443: Why is UNION ALL usually faster than UNION?
Technical answer: UNION removes duplicates, which requires extra sort or hash work, while UNION ALL simply concatenates results.
Example: SELECT id FROM a UNION ALL SELECT id FROM b;
Interviewer notes: Ask whether deduplication is actually required.
Red flags: Using UNION out of habit.
Q444: How do you handle time zones correctly?
Technical answer: Store timestamps in UTC with timestamptz and convert for presentation at the edges.
Example: SELECT created_at AT TIME ZONE 'America/New_York' FROM orders;
Interviewer notes: Good answers explain why timestamp without time zone is easy to misuse.
Red flags: Storing local time as if it were universal.
Q445: What is a safe pattern for bulk imports?
Technical answer: Load data in batches, disable nonessential work where appropriate, and validate with constraints after ingestion if needed.
Example: Use COPY for large loads, then analyze and index afterward.
Interviewer notes: Mention WAL volume and lock duration.
Red flags: Ignoring validation and recovery strategy.
Q446: What should you monitor for PostgreSQL health?
Technical answer: Track active connections, lock waits, replication lag, autovacuum activity, bloat, cache hit rate, and query latency.
Example: SELECT * FROM pg_stat_activity;
Interviewer notes: Good answers include both database and OS symptoms.
Red flags: Monitoring only CPU and disk.
Q447: Why can too many connections hurt performance?
Technical answer: Each connection consumes memory and increases context switching, so a connection pool is usually better than unlimited concurrency. Example: Use PgBouncer or an application-side pool. Interviewer notes: Ask about queueing and backpressure. Red flags: Setting max connections very high to mask application issues.
Q448: What is the benefit of prepared statements?
Technical answer: They reduce parse/plan overhead and can stabilize repeated query execution, especially for hot paths. Example: Prepare a parameterized query for repeated user lookups. Interviewer notes: Strong candidates mention plan caching tradeoffs. Red flags: Assuming prepared statements always improve performance.
Q449: Why can parameterized queries still produce bad plans?
Technical answer: Generic plans may be chosen when parameter values vary widely, which can hide selectivity differences from the planner. Example: A query on a skewed status column may need a custom plan. Interviewer notes: Good answers know the generic-versus-custom plan tradeoff. Red flags: Assuming binding parameters guarantees optimal plans.
Q450: What is a good use case for advisory locks?
Technical answer: Use them for application-level coordination where you need mutex-like behavior without locking a specific row.
Example: SELECT pg_advisory_lock(42);
Interviewer notes: Candidates should know they are cooperative and not tied to data integrity.
Red flags: Using advisory locks to replace transactional constraints.
Q451: How do you design around write hotspots?
Technical answer: Reduce contention by sharding the hotspot, batching writes, or redesigning the access pattern to avoid a single contended row. Example: Instead of one counter row, use per-shard counters and aggregate later. Interviewer notes: Ask about lock contention and sequence-heavy workloads. Red flags: Adding more indexes to fix a write hotspot.
Q452: What is the cost of an unbounded VARCHAR or TEXT column?
Technical answer: The type itself is fine, but huge values increase storage, I/O, and memory usage during sorting or hashing.
Example: Keep blobs out of frequently joined tables when possible.
Interviewer notes: Good candidates focus on workload impact, not type fear.
Red flags: Saying TEXT is always bad.
Q453: What is the difference between a unique constraint and a unique index?
Technical answer: Both enforce uniqueness, but a unique constraint is schema-level intent while the unique index is the underlying mechanism.
Example: ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
Interviewer notes: Strong answers know constraints express business rules.
Red flags: Treating them as unrelated features.
Q454: What is a common problem with ON DELETE CASCADE?
Technical answer: Cascades can delete large graphs of data unexpectedly if relationships are not carefully modeled and reviewed. Example: Deleting a parent row can wipe child records automatically. Interviewer notes: Ask about auditing and soft-delete alternatives. Red flags: Turning on cascade on every foreign key by default.
Q455: How do you detect bloat?
Technical answer: Look at dead tuple ratios, table size growth, and vacuum behavior, then confirm whether old row versions are accumulating.
Example: pg_stat_user_tables can help identify bloat-prone tables.
Interviewer notes: Candidates should connect symptoms to MVCC churn.
Red flags: Equating table size with useful data size.
Q456: What is the advantage of INSERT ... ON CONFLICT?
Technical answer: It provides atomic upsert semantics without a race between a separate existence check and insert.
Example: INSERT INTO users(email) VALUES ('a@b.com') ON CONFLICT (email) DO UPDATE SET updated_at = now();
Interviewer notes: Good answers mention unique constraints as the arbiter.
Red flags: Replacing all business logic with upserts.
Q457: Why do prepared transactions need care?
Technical answer: Two-phase commit can leave transactions in a prepared state if the coordinator fails, so operational handling is crucial.
Example: PREPARE TRANSACTION 'tx1';
Interviewer notes: Ask about recovery and manual resolution.
Red flags: Using prepared transactions casually without ops maturity.
Q458: What is a schema migration anti-pattern?
Technical answer: Shipping destructive schema changes before application code is compatible can cause outages. Example: Add nullable columns first, deploy code, backfill, then enforce constraints. Interviewer notes: Look for expand-and-contract thinking. Red flags: Doing breaking DDL in a single step.
Q459: Why is random() not suitable for primary keys?
Technical answer: It creates poor locality and high collision risk unless carefully managed, so it is not a good default identity strategy. Example: Prefer sequences or ordered UUIDs for most keys. Interviewer notes: Strong candidates discuss index behavior. Red flags: Using random keys because they look distributed.
Q460: What is the final thing to check before blaming PostgreSQL?
Technical answer: Verify the query, data model, statistics, indexes, and application behavior before assuming the database engine is the issue. Example: A slow query may simply be missing a predicate or returning too many rows. Interviewer notes: Good candidates are methodical and evidence-driven. Red flags: Jumping to engine-level conclusions without measurements.
Q461: How do you ensure deterministic results with window functions?
Technical answer: Include a complete ORDER BY with tie-breakers so ranking and paging do not depend on incidental row order.
Example: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at, id)
Interviewer notes: Ask about stable pagination and reproducibility.
Red flags: Omitting tie-breakers in production queries.
Q462: What is a common JSONB modeling pitfall?
Technical answer: Storing relational data in JSONB can make referential integrity, querying, and evolution harder than necessary. Example: Keep foreign-key-like relationships as actual foreign keys. Interviewer notes: Good answers separate flexible attributes from core relations. Red flags: Putting everything into one JSON document.
Q463: How can you reduce lock contention in batch jobs?
Technical answer: Break work into smaller commits, avoid touching the same rows repeatedly, and use SKIP LOCKED where appropriate. Example: Process 1,000 rows at a time rather than one giant transaction. Interviewer notes: Ask how retries affect correctness. Red flags: Holding long transactions open for the entire batch.
Q464: Why is NOT NULL valuable beyond correctness?
Technical answer: It improves data quality, simplifies query logic, and can help the planner and indexes by removing null semantics.
Example: ALTER TABLE users ALTER COLUMN email SET NOT NULL;
Interviewer notes: Good candidates tie constraints to simpler code and safer queries.
Red flags: Treating constraints as optional documentation.
Q465: What is a common pitfall with timestamp comparisons?
Technical answer: Comparing local timestamps across time zones can produce off-by-one errors or missing rows around DST changes. Example: Store UTC and compare in UTC. Interviewer notes: Ask how DST transitions are handled. Red flags: Assuming wall-clock time is enough for ordering.
Q466: How does pg_dump differ from physical backup tools?
Technical answer: pg_dump is logical, schema-aware, and portable; physical backups copy the data files and are better for large-scale recovery and PITR.
Example: Use pg_dump for migrations and physical backups for disaster recovery.
Interviewer notes: Good candidates compare speed, portability, and restore scope.
Red flags: Using the wrong backup type for the recovery goal.
Q467: Why is ANALYZE after bulk load important?
Technical answer: Bulk loads can leave the planner with stale or missing stats, leading to poor plans until analysis runs.
Example: Load data, then ANALYZE the affected tables.
Interviewer notes: Strong answers connect this to ETL pipelines.
Red flags: Forgetting post-load statistics refreshes.
Q468: What is the benefit of EXCLUDE constraints?
Technical answer: They enforce rules that go beyond simple equality, such as preventing overlapping ranges.
Example: EXCLUDE USING gist (room WITH =, period WITH &&);
Interviewer notes: Ask for scheduling or reservation examples.
Red flags: Using triggers when a declarative constraint is better.
Q469: How do you handle overlapping bookings?
Technical answer: Model the interval with a range type and use an exclusion constraint to prevent conflicts.
Example: EXCLUDE USING gist (resource_id WITH =, booking_window WITH &&);
Interviewer notes: Good candidates know this is a classic PostgreSQL strength.
Red flags: Solving overlap only in application code.
Q470: What is a practical use of pg_stat_statements?
Technical answer: It aggregates query statistics so you can find the most expensive and frequent queries in production. Example: Review top queries by total time and mean latency. Interviewer notes: Strong answers mention normalization of query texts. Red flags: Tuning blind without workload data.
Q471: Why can temp tables be both useful and risky?
Technical answer: They isolate intermediate data for a session, but heavy temp table use can add planning, maintenance, and storage overhead. Example: Use temp tables for multi-step ETL only when they simplify the workflow. Interviewer notes: Ask about session scope and cleanup. Red flags: Treating temp tables as free scratch space.
Q472: What is a safe pattern for schema evolution?
Technical answer: Add new columns, backfill in batches, dual-write if needed, then cut over and remove old paths later.
Example: Introduce new_status, populate it, switch reads, then retire status.
Interviewer notes: Good answers show phased deployment discipline.
Red flags: Changing semantics in place without rollout planning.
Q473: How do you think about replication lag?
Technical answer: Lag is the delay between primary commit and replica replay, which affects read freshness and failover confidence.
Example: Monitor pg_stat_replication and replica apply delay.
Interviewer notes: Candidates should mention workload spikes and network issues.
Red flags: Assuming replicas are instantly consistent.
Q474: What is the purpose of ON CONFLICT DO NOTHING?
Technical answer: It makes idempotent inserts easy when duplicates should be ignored rather than erroring.
Example: INSERT INTO email_subscribers(email) VALUES ('a@b.com') ON CONFLICT DO NOTHING;
Interviewer notes: Ask whether silent drops are acceptable.
Red flags: Using it where duplicate detection should be surfaced.
Q475: What is the main tradeoff of denormalization?
Technical answer: It can speed reads by avoiding joins, but increases update complexity and the risk of inconsistent copies. Example: Store a customer name snapshot on orders for historical reporting. Interviewer notes: Good answers tie denormalization to a specific query pattern. Red flags: Denormalizing everything by default.
Q476: Why is OFFSET 0 sometimes seen in query patterns?
Technical answer: It can force a subquery or view boundary in some code paths, but it should not be used as a magical optimization.
Example: Avoid depending on OFFSET 0 for semantics.
Interviewer notes: Strong candidates know it is usually a smell.
Red flags: Claiming it improves every query.
Q477: What is a common mistake with sequence ownership?
Technical answer: Detached sequences can survive table changes and become harder to manage, so ownership should match the table lifecycle.
Example: Use OWNED BY for table-backed identity sequences.
Interviewer notes: Ask how schema cleanup is handled.
Red flags: Leaving orphaned sequences around indefinitely.
Q478: How do you prevent duplicate work in background workers?
Technical answer: Use atomic claim-and-process patterns with row locks, status transitions, or advisory locks.
Example: UPDATE jobs SET status='running' WHERE id = ... AND status='ready' RETURNING *;
Interviewer notes: Good answers stress idempotence and retries.
Red flags: Polling without a claim mechanism.
Q479: What is the purpose of CLUSTER in PostgreSQL?
Technical answer: It rewrites a table in index order to improve locality for certain access patterns, though the benefit decays as new rows arrive.
Example: CLUSTER orders USING idx_orders_created_at;
Interviewer notes: Candidates should know it is an offline physical reordering operation.
Red flags: Expecting it to be a permanent optimization.
Q480: What is the best sign that a PostgreSQL tuning effort succeeded?
Technical answer: The workload meets its latency, throughput, and correctness goals with simpler or more stable resource usage, not just lower one metric. Example: Fewer slow queries, lower bloat growth, and predictable replication lag. Interviewer notes: Strong answers optimize outcomes, not vanity metrics. Red flags: Declaring success from a single benchmark.
Q481: What is the difference between ALTER TABLE ... ADD COLUMN and rewriting a table?
Technical answer: Adding a nullable column with no default is usually metadata-only, but some schema changes force a full table rewrite.
Example: ALTER TABLE users ADD COLUMN middle_name text;
Interviewer notes: Ask which changes are fast versus disruptive.
Red flags: Assuming every ALTER TABLE is cheap.
Q482: Why can default expressions make DDL expensive?
Technical answer: If a new column default must be materialized for existing rows, PostgreSQL may need to touch all rows depending on version and expression semantics. Example: Adding a constant default is often cheap, while volatile expressions are more expensive. Interviewer notes: Good candidates know not all defaults behave the same. Red flags: Thinking defaults are always metadata-only.
Q483: What is the purpose of RETURNING?
Technical answer: RETURNING lets you retrieve affected rows directly from INSERT, UPDATE, or DELETE without a separate query.
Example: INSERT INTO users(name) VALUES ('A') RETURNING id;
Interviewer notes: Strong answers mention fewer round trips and safer workflows.
Red flags: Ignoring atomic read-back needs.
Q484: Why are cross joins dangerous if used accidentally?
Technical answer: A missing join predicate can create a Cartesian product, exploding row counts and query cost.
Example: SELECT * FROM a, b;
Interviewer notes: Ask how to detect accidental cross joins in explain plans.
Red flags: Not checking join conditions carefully.
Q485: How do you make analytics queries safer on large tables?
Technical answer: Push filters early, project only needed columns, and pre-aggregate or partition data where possible. Example: Summarize daily facts before joining to dimensions. Interviewer notes: Good answers think about scan volume and memory pressure. Red flags: Running raw warehouse-style scans on transactional tables.
Q486: What is the purpose of LIMIT 1 in existence checks?
Technical answer: It can short-circuit work when only presence matters, but EXISTS is usually clearer and often better optimized.
Example: SELECT 1 FROM orders WHERE user_id = 42 LIMIT 1;
Interviewer notes: Ask why EXISTS is usually preferred.
Red flags: Using COUNT(*) to test existence.
Q487: What is a practical use for UNNEST?
Technical answer: UNNEST turns arrays into rows, which is useful for joining or filtering array data relationally.
Example: SELECT * FROM unnest(ARRAY[1,2,3]) AS x(id);
Interviewer notes: Good candidates mention set-based processing.
Red flags: Storing everything as arrays to avoid modeling.
Q488: Why can arrays be awkward in relational design?
Technical answer: Arrays can make indexing, constraints, and joins less straightforward than normalized tables. Example: A many-to-many relationship is often better as a join table. Interviewer notes: Strong answers compare convenience to queryability. Red flags: Using arrays just because PostgreSQL supports them.
Q489: What does jsonb_set enable?
Technical answer: It updates a nested JSONB path without rewriting application logic around the full document.
Example: UPDATE profiles SET data = jsonb_set(data, '{prefs,theme}', '"dark"');
Interviewer notes: Ask about path correctness and write amplification.
Red flags: Rebuilding whole JSON objects in application code every time.
Q490: What is a common pitfall with nullable foreign keys?
Technical answer: Nullable foreign keys can represent optional relationships, but they require careful handling in joins and constraints.
Example: LEFT JOIN is needed if the relationship may be absent.
Interviewer notes: Candidates should explain business meaning of NULL.
Red flags: Treating null as a valid referenced value.
Q491: Why can ORDER BY random() be a bad idea?
Technical answer: It usually forces a full scan and sort, which is expensive on large tables.
Example: SELECT * FROM items ORDER BY random() LIMIT 10;
Interviewer notes: Ask for alternative sampling strategies.
Red flags: Using it in production for large datasets.
Q492: What is the significance of primary key clustering?
Technical answer: Physical row order is not guaranteed by primary key, so you cannot assume insertion or clustering order unless you explicitly manage it. Example: A primary key index does not reorder table storage by itself. Interviewer notes: Good answers distinguish logical and physical order. Red flags: Assuming primary key implies sorted table data.
Q493: How do you identify a lock wait problem?
Technical answer: Check blocked sessions, blockers, and wait events to see which transactions are holding conflicting locks.
Example: Query pg_stat_activity for wait_event information.
Interviewer notes: Strong candidates correlate lock waits with specific statements.
Red flags: Blaming the database without identifying blockers.
Q494: What is the value of comment metadata in PostgreSQL?
Technical answer: Comments document schema intent directly in the database, helping future maintainers understand tables and columns.
Example: COMMENT ON COLUMN orders.status IS 'Lifecycle state of the order';
Interviewer notes: Ask whether schema metadata is part of engineering hygiene.
Red flags: Treating comments as optional and never updating them.
Q495: Why can GROUPING SETS be useful?
Technical answer: They let you compute multiple aggregate rollups in one pass instead of separate queries.
Example: GROUP BY GROUPING SETS ((dept), (region), ())
Interviewer notes: Good answers mention cube and rollup-style reporting.
Red flags: Running many separate aggregate scans when one would do.
Q496: What is the purpose of to_tsvector and to_tsquery?
Technical answer: They build and query PostgreSQL full-text search structures over tokenized text.
Example: SELECT * FROM docs WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgres');
Interviewer notes: Strong candidates mention dictionaries and stemming.
Red flags: Using LIKE for real text search needs.
Q497: What is a practical pg_restore advantage?
Technical answer: pg_restore can restore custom-format dumps selectively and in parallel, which helps with large databases and recovery flexibility.
Example: pg_restore -j 4 -d target backup.dump
Interviewer notes: Ask about partial restores and dependency ordering.
Red flags: Assuming plain SQL dumps are always best.
Q498: Why can ANALYZE be table-specific?
Technical answer: Different tables change at different rates, so it is often enough to refresh stats only where data churn is significant.
Example: ANALYZE hot_events;
Interviewer notes: Good answers mention targeted maintenance.
Red flags: Running blanket maintenance with no reason.
Q499: What is the risk of too-small shared_buffers?
Technical answer: It can reduce cache effectiveness and increase reliance on the OS page cache and disk I/O. Example: Monitor cache hit ratios before changing memory settings. Interviewer notes: Strong answers know this is only one part of memory tuning. Red flags: Thinking bigger is always better without measurement.
Q500: What is the best reason to revisit schema design?
Technical answer: If queries are consistently fighting the model, a schema redesign is often more effective than endless query micro-optimization. Example: Split an overloaded table into normalized entities. Interviewer notes: Good candidates know when the model, not the SQL, is the root issue. Red flags: Tuning around a bad schema forever.
PostGIS — Geospatial Extension
Q501: What is PostGIS?
Technical answer: PostGIS is an open-source spatial database extension for PostgreSQL. It adds support for storing, querying, and manipulating geospatial data using SQL.
Example: SELECT ST_Distance(geom1, geom2) FROM places;
Interviewer notes: Good answers mention spatial indexes and coordinate systems.
Red flags: Treating lat/long as plain floats without spatial types.
Q502: What is the difference between geometry and geography types?
Technical answer: geometry uses a planar coordinate system and is faster for most operations; geography uses a spheroidal model and gives accurate distances on Earth’s surface.
Example: Use geometry for maps, geography for real-world distances.
Interviewer notes: Strong candidates know which to pick by use case.
Red flags: Using geography everywhere and complaining about performance.
Q503: What spatial index types does PostGIS use?
Technical answer: PostGIS primarily uses GiST indexes for spatial data. SP-GIST and GIN can also be used depending on workload.
Example: CREATE INDEX idx_geom ON places USING GIST (geom);
Interviewer notes: Ask whether the candidate has tuned spatial indexes.
Red flags: No spatial index on large geospatial tables.
Q504: How do you find all points within a radius?
Technical answer: Use ST_DWithin with a geography or geometry cast. It uses spatial indexes efficiently.
Example: SELECT * FROM cafes WHERE ST_DWithin(location, ST_MakePoint(77.6, 12.97)::geography, 1000);
Interviewer notes: Good answers mention meter-based units with geography.
Red flags: Computing distances in application code or using raw math.
Q505: What does ST_Intersects do?
Technical answer: It returns true if two geometries share any portion of space. Commonly used for point-in-polygon and polygon overlap checks.
Example: SELECT * FROM zones WHERE ST_Intersects(zone_poly, user_point);
Interviewer notes: Good answers know it is index-supported.
Red flags: Using bounding boxes manually instead of spatial predicates.
Q506: What is a bounding box and why does it matter?
Technical answer: PostGIS indexes on the minimum bounding rectangle (MBR) of geometries. A quick box filter narrows candidates before exact shape checks.
Example: && operator tests whether bounding boxes overlap.
Interviewer notes: Strong candidates explain index selectivity.
Red flags: Skipping index-use analysis for spatial queries.
Q507: How do you compute distance between two points?
Technical answer: Use ST_Distance for geometry or geography. For geography, distance is in meters on the spheroid.
Example: SELECT ST_Distance(a::geography, b::geography) FROM ...;
Interviewer notes: Ask about units and accuracy trade-offs.
Red flags: Returning degrees as if they were meters.
Q508: What is ST_Transform?
Technical answer: It converts coordinates from one SRID to another. You need a defined projection for accurate results.
Example: ST_Transform(geom, 3857) for web mercator.
Interviewer notes: Good answers mention SRID 4326 as common input.
Red flags: Transforming without checking source SRID.
Q509: How do you create a spatial table?
Technical answer: Add a geometry or geography column and register it with PostGIS using AddGeometryColumn or direct column definition.
Example:
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
name TEXT,
geom GEOMETRY(LineString, 4326)
);
Interviewer notes: Strong candidates mention SRID at column level. Red flags: Storing WKT strings without a spatial column.
Q510: What is a common performance trap with PostGIS?
Technical answer: Casting between geometry and geography inside queries breaks index usage. Keep types consistent and cast at write time when possible. Example: Store as geography if distance filtering is the main query. Interviewer notes: Good answers mention plan output inspection. Red flags: Blaming PostGIS without checking query plans.
Q511: How do you check if a point is inside a polygon?
Technical answer: Use ST_Contains(polygon, point) or ST_Within(point, polygon). Both are index-supported with bounding box filtering.
Example: SELECT * FROM parks WHERE ST_Contains(park_boundary, user_location);
Interviewer notes: Good answers mention ST_Within as the inverse.
Red flags: Using manual ray casting logic.
Q512: What is ST_Area used for?
Technical answer: It returns the area of a polygon in the units of its coordinate system. For geography, it is computed on the spheroid.
Example: SELECT ST_Area(geom) FROM cities;
Interviewer notes: Ask whether candidate knows projected vs geographic area differences.
Red flags: Trusting degree-based area as meaningful.
Q513: How do you load GeoJSON into PostGIS?
Technical answer: Use ST_GeomFromGeoJSON to insert GeoJSON directly, or load via tools like ogr2ogr and QGIS.
Example: INSERT INTO places(geom) VALUES (ST_GeomFromGeoJSON('{"type":"Point","coordinates":[77.6,12.97]}'));
Interviewer notes: Good answers mention SRID handling.
Red flags: Parsing GeoJSON in application code.
Q514: What is tile serving and how does PostGIS help?
Technical answer: PostGIS can generate map tiles by querying vector data in tile bounding boxes. Libraries like pg_tileserv expose a REST tile endpoint directly from PostGIS.
Example: SELECT * FROM roads WHERE geom && ST_TileEnvelope(z, x, y);
Interviewer notes: Strong candidates know vector tile formats.
Red flags: Serving geospatial data without bounding-box filtering.
Q515: What is ST_SnapToGrid?
Technical answer: It rounds coordinates to a grid, which can reduce precision and improve index performance when exact precision is not required.
Example: ST_SnapToGrid(geom, 0.0001)
Interviewer notes: Good answers mention precision vs performance trade-offs.
Red flags: Applying it blindly to all spatial columns.
Q516: How do spatial joins work in PostGIS?
Technical answer: A spatial join uses a spatial predicate like ST_Intersects or ST_DWithin in the ON clause to relate rows from two tables by geometry.
Example:
SELECT a.id, b.name
FROM parcels a
JOIN zoning b
ON ST_Intersects(a.geom, b.geom);
Interviewer notes: Ask about join order and index usage. Red flags: Joining large spatial tables without spatial indexes.
Q517: What is ST_Union?
Technical answer: It combines multiple geometries into one. ST_Union in aggregates merges overlapping polygons.
Example: SELECT ST_Union(geom) FROM blocks GROUP BY district_id;
Interviewer notes: Strong candidates know it can be expensive.
Red flags: Using it on unbounded datasets without filtering.
Q518: Why should you avoid ST_AsText in production queries?
Technical answer: ST_AsText converts geometry to WKT, which is expensive and unnecessary for server-side spatial processing. Keep data in binary geometry for performance.
Example: Filter with ST_Intersects, return WKT only to clients.
Interviewer notes: Good answers separate API format from storage format.
Red flags: Converting early and losing index benefits.
Q519: What is coordinate reference system (CRS) or SRID?
Technical answer: SRID identifies the coordinate system of spatial data. Accurate spatial operations require matching or transformed SRIDs.
Example: SRID 4326 is GPS lat/long; 3857 is Web Mercator.
Interviewer notes: Ask whether candidate checks SRID before analysis.
Red flags: Mixing SRIDs without ST_Transform.
Q520: How do you enforce spatial constraints?
Technical answer: Use CHECK constraints with spatial predicates, or triggers, to validate geometry validity and SRID at insert time.
Example: ALTER TABLE places ADD CHECK (ST_SRID(geom) = 4326);
Interviewer notes: Good answers mention invalid geometry handling.
Red flags: Accepting bad geometries silently.
Q521: What is ST_IsValid?
Technical answer: It tests whether a geometry is well-formed according to its rules. Invalid polygons are a common data quality issue.
Example: SELECT * FROM parcels WHERE NOT ST_IsValid(geom);
Interviewer notes: Strong candidates mention ST_MakeValid.
Red flags: Ignoring invalid geometry until queries break.
Q522: How do you optimize a slow spatial query?
Technical answer: Ensure a GiST index exists, avoid unnecessary casts, use bounding-box operators early, and check the query plan for index scans.
Example: EXPLAIN ANALYZE SELECT ... WHERE ST_DWithin(...);
Interviewer notes: Good answers mention selectivity and plan analysis.
Red flags: Scaling hardware before indexing.
Q523: What is a use case for ST_Buffer?
Technical answer: It creates a polygon around a geometry at a given distance. Common for radius searches, service areas, and map visualizations.
Example: SELECT ST_Buffer(location, 500) FROM fire_stations;
Interviewer notes: Strong candidates know units depend on CRS.
Red flags: Buffering in degrees and calling it meters.
Q524: When would you use geography instead of geometry?
Technical answer: When you need true-meter distances and area on Earth’s surface across large distances, especially with lat/long data.
Example: WHERE ST_DWithin(location::geography, target::geography, 2000)
Interviewer notes: Good answers accept the performance cost.
Red flags: Using geography for small-scale planar maps.
Q525: How do you back up and restore a PostGIS-enabled database?
Technical answer: Use pg_dump and pg_restore as with normal PostgreSQL. PostGIS types and indexes are preserved as long as the extension is installed on restore.
Example: pg_dump -Fc dbname > db.dump && pg_restore -d newdb db.dump
Interviewer notes: Strong candidates mention CREATE EXTENSION postgis; on target.
Red flags: Restoring without the PostGIS extension and wondering why geometry breaks.
Q532: Connection pool exhaustion under async load
Level: Senior
Scenario: A FastAPI app using SQLAlchemy async works fine normally. During a traffic spike, all requests hang with "could not obtain connection from pool within 30 seconds." Database CPU is low.
Impact: All API calls hang or fail. Database is healthy but unreachable.
Root Cause: Async pool size too small. Each request holds a connection for its entire duration including slow queries. Pool empties and new requests queue.
Simple explanation: Five checkout counters. Hundreds of customers. Each counter stays occupied because customers pay slowly. New customers can't check out.
Technical Answer:
- Size pool based on concurrent workers:
pool_size = 2 * CPU cores * workers. - Set
pool_timeoutto fail fast instead of hanging forever. - Set
pool_recycleandpool_pre_pingfor stale connections. - Keep DB transactions short — do not call external services inside a transaction.
- Monitor
pool.checkedout()and alert at 80% utilization.
Code/Syntax Justification:
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
engine = create_async_engine(
DATABASE_URL,
pool_size=20,
max_overflow=10,
pool_timeout=30,
pool_recycle=1800,
pool_pre_ping=True,
)
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
Workflow Diagram:
Request arrives
|
v
[Check connection pool]
|
+-- available --> checkout -> run query -> return to pool
|
+-- none available --> wait up to pool_timeout
|
+-- freed --> proceed
|
+-- timeout --> 503 Service Unavailable
|
v
Return response
🔍 Behind the Question: Async pool sizing, fail-fast vs hang, short transactions, pool monitoring.
❌ The Red Flags: Default pool_size=5 under high load; external API calls inside DB transactions; no pool timeout.
Q533: Deadlock under concurrent updates
Level: Senior
Scenario: Two workers process orders at the same time. Worker A updates order 1 then order 2. Worker B updates order 2 then order 1. Both wait for each other. Database aborts one with deadlock error.
Impact: One worker fails. Orders need reprocessing. Retry logic must handle it.
Root Cause: Transactions acquire row locks in different orders, forming a cycle.
Simple explanation: Two people each hold one end of a rope and want the other end. Neither lets go. Stuck forever.
Technical Answer:
- Always acquire locks in consistent global order (e.g., by ID ascending).
- Use
SELECT ... FOR UPDATEto lock rows explicitly before updating. - Keep transactions short to reduce lock hold time.
- Catch deadlock errors and retry with backoff.
Code/Syntax Justification:
from sqlalchemy import text
ids = sorted([order1_id, order2_id])
with db.begin() as transaction:
db.execute(text("SELECT * FROM orders WHERE id = :id FOR UPDATE"), {"id": ids[0]})
db.execute(text("SELECT * FROM orders WHERE id = :id FOR UPDATE"), {"id": ids[1]})
Workflow Diagram:
Without fix: With fix (consistent order):
A locks 1 A locks 1 (lower id first)
B locks 2 B waits for 1
A waits for 2 ---> B gets 1 after A commits
B waits for 1 B locks 2
DEADLOCK No deadlock
🔍 Behind the Question: Lock ordering, deadlock detection, retry handling, SELECT FOR UPDATE.
❌ The Red Flags: No consistent lock order; no retry on deadlock; long-running transactions holding locks.
Q534: Missing index — collection scan on high-traffic query
Level: Senior
Scenario: A query SELECT * FROM orders WHERE created_at > '2026-01-01' ran in 50 ms when the table had 10k rows. At 10 million rows, it takes 8 seconds and spikes CPU.
Impact: Dashboard times out. Application queries slow. Database CPU saturates.
Root Cause: No index on created_at. PostgreSQL performs a sequential scan (reads every row) as the table grows.
Simple explanation: Searching for a page in a book without an index means reading every page from the start.
Technical Answer:
- Add an index on
created_at:CREATE INDEX idx_orders_created_at ON orders(created_at); - Use
EXPLAIN ANALYZEto confirm index usage (Index Scan vs Seq Scan). - Consider covering indexes or partial indexes for common WHERE + SELECT patterns.
- Monitor index bloat and reindex periodically.
Code/Syntax Justification:
-- Before: sequential scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > '2026-01-01';
-- After: add index
CREATE INDEX idx_orders_created_at ON orders(created_at);
-- Verify uses Index Scan not Seq Scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > '2026-01-01';
Workflow Diagram:
Query: WHERE created_at > X
|
v
No Index Index on created_at
| |
v v
Seq Scan (read all rows) Index Range Scan (read matching only)
| |
v v
Slow at scale Fast regardless of table size
🔍 Behind the Question: Index selection, EXPLAIN ANALYZE usage, sequential vs index scans.
❌ The Red Flags: Adding indexes on every column (write penalty); not verifying with EXPLAIN; indexing low-cardinality columns.
Q535: Replication lag — read replica returns stale data
Level: Principal
Scenario: A user updates their profile and immediately refreshes the page. The update is not visible because the read replica lags behind the primary by 5–10 seconds.
Impact: Users see stale data. Confusing experience. Support tickets.
Root Cause: PostgreSQL streaming replication is asynchronous. WAL records ship to replicas after commit on primary. Under heavy write load, replicas fall behind.
Simple explanation: The primary notebook is updated instantly. The photocopy (replica) is updated minutes later. Readers of the photocopy see old content.
Technical Answer:
- Read-after-write consistency: route the user's own reads to the primary for a short window after their write.
- Synchronous replication for critical tables (
synchronous_standby_names). - Accept eventual consistency for non-critical reads; display "updated" timestamps.
- Monitor replication lag with
pg_stat_replication.
Code/Syntax Justification:
class DatabaseRouter:
def __init__(self):
self.primary_until = {} # user_id -> timestamp
def get_connection(self, user_id, just_wrote=False):
if just_wrote:
self.primary_until[user_id] = time.time() + 5
return primary_conn
if user_id in self.primary_until and time.time() < self.primary_until[user_id]:
return primary_conn
return replica_conn
Workflow Diagram:
User UPDATE profile (Primary)
|
v
Primary commits
|
v
[Async WAL shipping to Replica]
|
+-- lag < 5s --> replica catches up
|
+-- lag > 5s --> replica stale
User READ profile
|
+-- within 5s of write --> route to Primary (read-your-writes)
|
+-- otherwise --> route to Replica (eventual consistency)
🔍 Behind the Question: Asynchronous replication, read-your-writes consistency, replication lag monitoring.
❌ The Red Flags: Assuming replicas always in sync; no lag monitoring; routing all reads to replicas regardless of freshness needs.
Q536: Vacuum and bloat — autovacuum not keeping up
Level: Principal
Scenario: A high-write table grows to 3× expected size. Queries slow down. pg_total_relation_size() shows heavy bloat. Autovacuum runs but cannot keep up with UPDATE/DELETE volume.
Impact: Disk waste. Slower queries. Worse cache hit ratio.
Root Cause: Each UPDATE in PostgreSQL is DELETE + INSERT (MVCC). Dead tuples accumulate until vacuum removes them. If update rate exceeds vacuum rate, bloat grows.
Simple explanation: Every edit leaves a scrap of paper on the desk. The cleaner removes scraps, but if you write faster than the cleaner cleans, the desk gets buried.
Technical Answer:
- Tune autovacuum for high-write tables: lower
autovacuum_vacuum_scale_factor. - Increase
autovacuum_max_workersandmaintenance_work_mem. - Use
pg_repackorVACUUM FULL(requires lock) for severe bloat. - Use fillfactor to leave space for HOT updates on frequently updated tables.
Code/Syntax Justification:
ALTER TABLE high_write_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_cost_limit = 2000
);
SELECT schemaname, tablename, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000;
Workflow Diagram:
UPDATE row
|
v
Old row version marked dead
|
v
Autovacuum runs periodically
|
+-- keeps up --> dead tuples removed, size stable
|
+-- cannot keep up --> dead tuples accumulate = bloat
|
v
queries slower, disk wasted
🔍 Behind the Question: MVCC, autovacuum tuning, bloat management, fillfactor, HOT updates.
❌ The Red Flags: VACUUM FULL during peak (long exclusive lock); ignoring n_dead_tup; not tuning per high-write table.
Q537: Connection storm — too many app instances connecting directly
Level: Principal
Scenario: After autoscaling, 100 FastAPI instances each open 20 DB connections = 2000 connections. PostgreSQL max_connections is 200. New connections fail.
Impact: Application cannot connect to DB. Widespread errors.
Root Cause: Each app process maintains its own pool. Total connections exceed PostgreSQL max_connections.
Simple explanation: 100 people each calling one receptionist at once. The line is busy. Nobody gets through.
Technical Answer:
- Use a connection pooler (PgBouncer) between app and PostgreSQL. PgBouncer multiplexes many app connections into fewer DB connections.
- Set PgBouncer
default_pool_sizeto match DB capacity (e.g., 150–200). - Reduce per-instance pool size.
- Monitor
pg_stat_activityfor connection count by state.
Code/Syntax Justification:
[pgbouncer]
pool_mode = transaction
default_pool_size = 150
max_client_conn = 1000
reserve_pool_size = 20
Workflow Diagram:
100 App Instances PgBouncer PostgreSQL
| | |
|--- 20 conns each ->| |
| | multiplexes into |
| | 150 DB conns |
| |--------------------->|
🔍 Behind the Question: Connection pooling at scale, PgBouncer, max_connections planning.
❌ The Red Flags: Each app instance with large pool directly to DB; max_connections too low; no pooler.
Senior / Principal — Real World Scenarios
Q526: Database connection pool exhaustion under load
Level: Senior
Scenario: A FastAPI application using SQLAlchemy works fine normally. During a traffic spike, all requests hang with "could not obtain connection from pool within 30 seconds" errors. The database CPU is low.
Impact: All API calls hang or fail. Users see timeouts. The database itself is healthy but unreachable.
Root Cause: The connection pool size is too small (default 5), while traffic creates many concurrent requests. Each request holds a connection for its entire duration, including slow queries and external API calls within the same request. The pool empties and new requests queue.
Simple explanation: Five checkout counters at a store. Hundreds of customers arrive. Each counter stays occupied because customers are slow to pay. New customers cannot check out even though the store has space.
Fix:
- Size the pool based on concurrent worker count, not a fixed default (e.g., pool_size = 2 × CPU cores per worker × workers).
- Set
pool_recycleandpool_pre_pingto handle stale/dropped connections. - Keep DB transactions short — do not call external services inside a transaction.
- Add monitoring on pool utilization (
pool.checkedout()).
Code:
from sqlalchemy import create_engine
engine = create_engine(
DATABASE_URL,
pool_size=20, # match expected concurrency
max_overflow=10, # temporary burst above pool_size
pool_timeout=30, # fail fast instead of hanging forever
pool_recycle=1800, # recycle connections every 30 min
pool_pre_ping=True, # verify connection before use
)
Workflow diagram:
Request arrives
|
v
[Check connection pool]
|
+-- available --> checkout connection -> run query -> return to pool
|
+-- none available --> wait up to pool_timeout
|
+-- connection freed --> proceed
|
+-- timeout --> 503 Service Unavailable
|
v
Return response
Interviewer notes: Tests connection pool sizing, fail-fast vs hang behavior, and keeping transactions short.
Red flags: Using default pool_size=5 under high load; calling external APIs inside DB transactions; no pool timeout.
Q527: Deadlock under concurrent updates
Level: Senior
Scenario: Two workers process orders at the same time. Worker A updates order 1 then tries to update order 2. Worker B updates order 2 then tries to update order 1. Both wait for each other. PostgreSQL raises a deadlock error and aborts one transaction.
Impact: One worker fails with "deadlock detected". Orders may need reprocessing. Retry logic must handle it gracefully.
Root Cause: Transactions acquire row locks in different orders. When two transactions lock resources in opposite order, they form a cycle.
Simple explanation: Two people each hold one end of a rope and want the other end. Neither lets go. Stuck forever.
Fix:
- Always acquire locks in a consistent global order (e.g., by ID ascending).
- Use
SELECT ... FOR UPDATEto lock rows explicitly before updating. - Keep transactions short to reduce lock hold time.
- Catch
psycopg2.errors.DeadlockDetectedand retry with backoff.
Code:
# Consistent lock order: always lock lower id first
ids = sorted([order1_id, order2_id])
with db.begin() as transaction:
db.execute("SELECT * FROM orders WHERE id = :id FOR UPDATE", {"id": ids[0]})
db.execute("SELECT * FROM orders WHERE id = :id FOR UPDATE", {"id": ids[1]})
# safe to update both now
Workflow diagram:
Transaction A: lock order 1, then order 2
Transaction B: lock order 2, then order 1
Without fix: With fix (consistent order):
A locks 1 A locks 1 (lower id first)
B locks 2 B waits for 1 (lower id first)
A waits for 2 ---> B gets 1 after A commits
B waits for 1 B locks 2
DEADLOCK No deadlock
Interviewer notes: Tests lock ordering, deadlock detection, and retry handling. Principal candidates discuss isolation levels and SELECT FOR UPDATE vs optimistic locking.
Red flags: No consistent lock order; no retry on deadlock; long-running transactions holding locks.
Q528: Slow query after table growth — missing index
Level: Senior
Scenario: A report query SELECT * FROM orders WHERE created_at > '2026-01-01' ran in 50 ms when the table had 10k rows. At 10 million rows, it takes 8 seconds and spikes CPU.
Impact: Dashboard times out. Application queries slow. Database CPU saturates.
Root Cause: No index on created_at. PostgreSQL performs a sequential scan (reads every row) as the table grows.
Simple explanation: Searching for a page in a book without an index means reading every page from the start.
Fix:
- Add an index on
created_at:CREATE INDEX idx_orders_created_at ON orders(created_at); - Use
EXPLAIN ANALYZEto confirm index usage (Index Scan vs Seq Scan). - Consider covering indexes or partial indexes for common WHERE + SELECT patterns.
- Monitor index bloat and reindex periodically.
Code:
-- Before: sequential scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > '2026-01-01';
-- -> Seq Scan on orders (cost=0.00..####..#### rows=####)
-- After: add index
CREATE INDEX idx_orders_created_at ON orders(created_at);
-- Verify
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > '2026-01-01';
-- -> Index Scan using idx_orders_created_at
Workflow diagram:
Query: WHERE created_at > X
|
v
No Index Index on created_at
| |
v v
Seq Scan (read all rows) Index Range Scan (read matching only)
| |
v v
Slow at scale Fast regardless of table size
Interviewer notes: Tests index selection, EXPLAIN ANALYZE usage, and understanding sequential vs index scans.
Red flags: Adding indexes on every column (write penalty); not verifying with EXPLAIN; indexing low-cardinality columns.
Q529: Replication lag — read replica returns stale data
Level: Principal
Scenario: A user updates their profile and immediately refreshes the page. The update is not visible because the read replica lags behind the primary by 5–10 seconds.
Impact: Users see stale data. Confusing experience. Support tickets.
Root Cause: PostgreSQL streaming replication is asynchronous. WAL records are shipped to replicas after commit on primary. Under heavy write load, replicas fall behind.
Simple explanation: The primary notebook is updated instantly. The photocopy (replica) is updated a few minutes later. Readers of the photocopy see old content.
Fix:
- Read-after-write consistency: route the user's own reads to the primary for a short window after their write (session stickiness).
- Use synchronous replication for critical tables (
synchronous_standby_names). - Accept eventual consistency for non-critical reads and display "updated" timestamps.
- Monitor replication lag with
pg_stat_replicationandpg_last_xact_replay_timestamp().
Code:
# Route own writes to primary for N seconds
class DatabaseRouter:
def __init__(self):
self.primary_until = {} # user_id -> timestamp
def get_connection(self, user_id, just_wrote=False):
if just_wrote:
self.primary_until[user_id] = time.time() + 5
return primary_conn
if user_id in self.primary_until and time.time() < self.primary_until[user_id]:
return primary_conn
return replica_conn
Workflow diagram:
User UPDATE profile (Primary)
|
v
Primary commits
|
v
[Asynchronous WAL shipping to Replica]
|
+-- lag < 5s --> replica catches up quickly
|
+-- lag > 5s --> replica stale
User READ profile (Replica)
|
+-- within 5s of write --> route to Primary (read-your-writes)
|
+-- otherwise --> route to Replica (eventual consistency)
Interviewer notes: Tests understanding of asynchronous replication, read-your-writes consistency, and monitoring replication lag. Principal candidates discuss synchronous replication trade-offs (performance vs durability).
Red flags: Assuming replicas are always in sync; no replication lag monitoring; routing all reads to replicas without considering freshness requirements.
Q530: Vacuum and bloat — autovacuum not keeping up
Level: Principal
Scenario: A high-write table grows to 3× its expected size. Queries slow down. pg_total_relation_size() shows heavy bloat. Autovacuum is running but cannot keep up with UPDATE/DELETE volume.
Impact: Disk space waste. Slower queries due to more pages to scan. Worse cache hit ratio.
Root Cause: Each UPDATE in PostgreSQL is effectively a DELETE + INSERT (MVCC). Dead tuples accumulate until vacuum removes them. If update rate exceeds vacuum rate, bloat grows.
Simple explanation: Every edit leaves a scrap of paper on the desk. The cleaner (vacuum) removes scraps, but if you write faster than the cleaner cleans, the desk gets buried.
Fix:
- Tune autovacuum for high-write tables: lower
autovacuum_vacuum_scale_factorandautovacuum_vacuum_insert_scale_factor. - Increase
autovacuum_max_workersandmaintenance_work_mem. - Consider
pg_repackorVACUUM FULL(requires lock) for severe bloat. - Use fillfactor to leave space for HOT updates on frequently updated tables.
Code:
-- Per-table aggressive autovacuum
ALTER TABLE high_write_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_insert_scale_factor = 0.05,
autovacuum_vacuum_cost_limit = 2000
);
-- Check bloat
SELECT schemaname, tablename, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000;
Workflow diagram:
UPDATE row
|
v
Old row version marked dead (not removed immediately)
|
v
Autovacuum runs periodically
|
+-- keeps up --> dead tuples removed, table size stable
|
+-- cannot keep up --> dead tuples accumulate = bloat
|
v
queries slower, disk wasted
|
v
Fix: tune autovacuum / pg_repack
Interviewer notes: Tests MVCC understanding, autovacuum tuning, and bloat management. Principal candidates discuss fillfactor, HOT updates, and pg_repack.
Red flags: Running VACUUM FULL during peak (long exclusive lock); ignoring n_dead_tup; not tuning per high-write table.
Q531: Connection storm — too many application instances connecting directly
Level: Principal
Scenario: After autoscaling, 100 FastAPI instances each open 20 database connections = 2000 connections. PostgreSQL max_connections is 200. New connections fail. Existing queries slow.
Impact: Application cannot connect to DB. Widespread errors. Database struggles with context switching.
Root Cause: Each application process maintains its own connection pool. With many instances, total connections exceed PostgreSQL's max_connections.
Simple explanation: 100 people each calling one receptionist at the same time. The phone line is busy. Nobody gets through.
Fix:
- Use a connection pooler (PgBouncer) between application and PostgreSQL. PgBouncer multiplexes many app connections into fewer DB connections.
- Set PgBouncer
default_pool_sizeto match DB capacity (e.g., 150–200). - Reduce per-instance pool size.
- Monitor
pg_stat_activityfor connection count by state.
Code:
# pgbouncer.ini
[databases]
appdb = host=db.internal port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction # transaction-level pooling (efficient)
default_pool_size = 150 # <= max_connections - reserve
max_client_conn = 1000 # app connections allowed
reserve_pool_size = 20
reserve_pool_timeout = 3
Workflow diagram:
100 App Instances PgBouncer PostgreSQL
| | |
|--- 20 conns each ----> | |
| | multiplexes into |
| | 150 DB conns |
| |-------------------->|
| | |
|<-- results ------------| |
Interviewer notes: Tests connection pooling at scale, PgBouncer, transaction vs session pooling, and max_connections planning.
Red flags: Each app instance with large pool directly to DB; max_connections too low for instance count; no pooler.