100% Free
🎯 3200+ Questions. One Goal: Your Success Sign In Sign Up

Amazon Interview PostgreSQL Interview Questions

Curated Amazon Interview-level PostgreSQL interview questions for developers targeting amazon interview positions. 115 questions available.

Last updated:

PostgreSQL Interview Questions & Answers

Skip to Questions

Welcome to our comprehensive collection of PostgreSQL interview questions and answers. This page contains expertly curated interview questions covering all aspects of PostgreSQL, from fundamental concepts to advanced topics. Whether you're preparing for an entry-level position or a senior role, you'll find questions tailored to your experience level.

Our PostgreSQL interview questions are designed to help you:

  • Understand core concepts and best practices in PostgreSQL
  • Prepare for technical interviews at all experience levels
  • Master both theoretical knowledge and practical application
  • Build confidence for your next PostgreSQL interview

Each question includes detailed answers and explanations to help you understand not just what the answer is, but why it's correct. We cover topics ranging from basic PostgreSQL concepts to advanced scenarios that you might encounter in senior-level interviews.

Use the filters below to find questions by difficulty level (Entry, Junior, Mid, Senior, Expert) or focus specifically on code challenges. Each question is carefully crafted to reflect real-world interview scenarios you'll encounter at top tech companies, startups, and MNCs.

Questions

115 questions
Q1:

What is PostgreSQL?

Entry

Answer

PostgreSQL is an open-source relational database known for reliability, SQL compliance, JSON support, advanced indexing, and ACID transactions.
Quick Summary: PostgreSQL is an open-source, enterprise-grade relational database known for reliability, standards compliance, and advanced features. It supports ACID transactions, complex queries, JSON, full-text search, extensions, and custom data types. Used widely in production for web apps, analytics, and geospatial data (PostGIS). Considered the most feature-rich open-source RDBMS.
Permalink
Q2:

What is a database cluster in PostgreSQL?

Entry

Answer

A PostgreSQL cluster is a collection of databases managed by a single server instance, sharing one data directory.
Quick Summary: A database cluster in PostgreSQL is a collection of databases managed by a single PostgreSQL server instance, sharing the same data directory (PGDATA), configuration files, and WAL log. Not a "cluster" in the distributed sense - just one server managing multiple databases. Each cluster has its own port, processes, and system catalogs.
Permalink
Q3:

What is a schema in PostgreSQL?

Entry

Answer

A schema is a logical container used to group tables, views, and functions. It helps organize objects and avoid name conflicts.
Quick Summary: A schema is a namespace inside a database that groups related tables, views, functions, and other objects. Default schema is "public". Multiple schemas in one database allow logical separation (like departments or tenants). Access with schema_name.table_name. search_path controls which schemas are searched without prefix. Useful for multi-tenant apps and organizing large databases.
Permalink
Q4:

What is the difference between a table and a view?

Entry

Answer

A table stores data physically, while a view is a virtual table created from a SELECT query and stores no actual data.
Quick Summary: Table stores actual data rows on disk with persistent storage. View is a saved SELECT query - it looks like a table but computes results at query time (no stored data). Materialized view stores the query result on disk (like a cached table). Use views for simplifying complex queries and security (expose subset of columns). Use materialized views for performance when query is expensive and data can be slightly stale.
Permalink
Q5:

What is a primary key in PostgreSQL?

Entry

Answer

A primary key uniquely identifies each row and automatically creates a unique index for fast lookups.
Quick Summary: Primary key uniquely identifies each row in a table - must be unique and NOT NULL. PostgreSQL automatically creates a unique index on the primary key columns. You can define it inline (id SERIAL PRIMARY KEY) or as a table constraint. Composite primary keys use multiple columns together. The primary key is the main way to reference a row from foreign keys in other tables.
Permalink
Q6:

What is a foreign key?

Entry

Answer

A foreign key links a column to another table’s primary key to enforce referential integrity.
Quick Summary: Foreign key creates a referential integrity constraint - a column in one table must match a value in the primary key column of another table (or be NULL). Prevents orphaned records. ON DELETE/UPDATE: CASCADE (propagate change), SET NULL, SET DEFAULT, or RESTRICT (block the operation). FK lookups benefit from an index on the FK column in the child table.
Permalink
Q7:

What data types does PostgreSQL support?

Entry

Answer

PostgreSQL supports numeric, text, date/time, Boolean, arrays, JSON/JSONB, UUID, geometric types, and custom types.
Quick Summary: PostgreSQL data types: Numeric (INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION), Text (TEXT, VARCHAR(n), CHAR(n)), Boolean, Date/Time (DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL), JSON/JSONB (binary JSON), Arrays (any_type[]), UUID, ENUM (custom set of values), Geometric types, Network types (INET, CIDR), BYTEA (binary data), and custom composite types.
Permalink
Q8:

What is the purpose of the SELECT statement?

Entry

Answer

SELECT retrieves data from tables or views and supports filtering, sorting, grouping, and formatting.
Quick Summary: SELECT retrieves data from one or more tables. Basic: SELECT col1, col2 FROM table WHERE condition ORDER BY col LIMIT 10. SELECT * fetches all columns (avoid in production - fetches unnecessary data). Use column aliases: SELECT name AS full_name. Combine with JOIN for multi-table queries, GROUP BY for aggregation, HAVING to filter groups. The most used SQL statement.
Permalink
Q9:

What is the WHERE clause used for?

Entry

Answer

WHERE filters rows that meet specified conditions, returning only relevant records.
Quick Summary: WHERE filters rows returned by SELECT, UPDATE, or DELETE - only rows matching the condition are included. Conditions use comparison operators (=, >, <, >=, <=, !=), logical operators (AND, OR, NOT), pattern matching (LIKE, ILIKE), NULL checks (IS NULL, IS NOT NULL), ranges (BETWEEN), and set membership (IN, NOT IN). WHERE is evaluated before GROUP BY.
Permalink
Q10:

What is ORDER BY in PostgreSQL?

Entry

Answer

ORDER BY sorts query results in ascending or descending order.
Quick Summary: ORDER BY sorts query results. ASC (ascending, default) or DESC (descending). ORDER BY multiple columns: ORDER BY last_name ASC, first_name ASC. NULL values sort last in ASC (NULLS LAST) by default. Use NULLS FIRST or NULLS LAST to control. Without ORDER BY, PostgreSQL makes no guarantee about result order - never assume rows come back in insert order.
Permalink
Q11:

What is the LIMIT clause?

Entry

Answer

LIMIT restricts the number of returned rows and is often used for pagination.
Quick Summary: LIMIT restricts the number of rows returned: SELECT * FROM users LIMIT 10. OFFSET skips rows: LIMIT 10 OFFSET 20 (page 3 of 10 results). Common for pagination. Caution: OFFSET-based pagination gets slower as offset grows (PostgreSQL must scan and discard rows). For large datasets, use keyset pagination (WHERE id > last_seen_id LIMIT 10) instead.
Permalink
Q12:

What is a UNIQUE constraint?

Entry

Answer

A UNIQUE constraint ensures all values in a column or column group are distinct.
Quick Summary: UNIQUE constraint ensures all values in a column (or combination of columns) are distinct. Unlike PRIMARY KEY, UNIQUE allows one NULL value. PostgreSQL automatically creates a unique index to enforce it. Add to single column: email TEXT UNIQUE. Composite unique: UNIQUE(user_id, product_id). Use to enforce natural business keys (email addresses, usernames, product codes).
Permalink
Q13:

What is the purpose of the INSERT statement?

Entry

Answer

INSERT adds new rows to a table, either individually or using INSERT SELECT.
Quick Summary: INSERT adds new rows to a table. Single row: INSERT INTO users (name, email) VALUES ("Alice", "alice@example.com"). Multiple rows: INSERT INTO users VALUES (...), (...), (...). INSERT ... SELECT copies from another query. INSERT ... ON CONFLICT (upsert): handle duplicate key violations gracefully - DO NOTHING or DO UPDATE SET. Use RETURNING to get the inserted row back.
Permalink
Q14:

What is the difference between DELETE and DROP?

Entry

Answer

DELETE removes rows but keeps the table structure. DROP removes the entire table.
Quick Summary: DELETE removes rows from a table but preserves table structure, can use WHERE, fires triggers, is logged in WAL, and is transactional (can be rolled back). DROP TABLE removes the entire table and its structure permanently - can't be rolled back after commit. TRUNCATE removes all rows fast (like DROP + recreate) but keeps the table structure - also transactional in PostgreSQL.
Permalink
Q15:

What is a default value in PostgreSQL?

Entry

Answer

A default value is automatically applied when no explicit value is provided during INSERT.
Quick Summary: A default value is used when no value is provided for a column during INSERT. Define with: age INTEGER DEFAULT 18. Common defaults: DEFAULT NOW() for timestamps, DEFAULT TRUE for boolean flags, DEFAULT gen_random_uuid() for UUID PKs, DEFAULT CURRENT_USER for audit fields. Defaults can be expressions or function calls. DEFAULT is applied column by column during INSERT.
Permalink
Q16:

What is the PostgreSQL psql tool used for?

Junior

Answer

psql is the interactive command-line tool for connecting to PostgreSQL, executing SQL queries, running scripts, and viewing metadata.
Quick Summary: psql is PostgreSQL's interactive command-line client. Connect: psql -h host -U user -d dbname. Run SQL interactively or from files (-f file.sql). Meta-commands: \l (list databases), \dt (list tables), \d tablename (describe table), \i file.sql (run file), \timing (show query time), \copy for CSV import/export. Essential tool for DBA work and debugging.
Permalink
Q17:

What is the purpose of the SERIAL data type?

Junior

Answer

SERIAL creates an auto-incrementing integer column using sequences that automatically generate unique values.
Quick Summary: SERIAL is a shorthand for creating an auto-incrementing integer column. SERIAL creates an INTEGER with a sequence. BIGSERIAL for BIGINT. Modern PostgreSQL prefers: id BIGINT GENERATED ALWAYS AS IDENTITY which is SQL-standard. Both create a sequence that provides the next value on each insert. Use BIGINT/BIGSERIAL for tables that might grow large to avoid integer overflow.
Permalink
Q18:

What is the difference between TEXT and VARCHAR?

Junior

Answer

TEXT stores unlimited variable-length text, whereas VARCHAR can enforce a maximum length. Performance difference is minimal.
Quick Summary: TEXT and VARCHAR are functionally identical in PostgreSQL - both store unlimited length strings with the same performance. VARCHAR(n) enforces a maximum length (inserts longer strings fail). TEXT has no length limit. Unlike other databases, PostgreSQL TEXT is not slower than VARCHAR. Use TEXT unless you have a specific business reason to enforce a maximum length.
Permalink
Q19:

What are ENUM types used for?

Junior

Answer

ENUM restricts a column to predefined string values, useful for static categories such as status fields.
Quick Summary: ENUM types define a static set of allowed string values: CREATE TYPE mood AS ENUM ("happy", "sad", "neutral"). Use as column type: mood mood NOT NULL. Storage is efficient (1-4 bytes). Values have a defined order (useful for BETWEEN, ORDER BY). Downside: adding new values requires ALTER TYPE - needs exclusive lock in older PostgreSQL (non-blocking in newer versions).
Permalink
Q20:

What is a CHECK constraint?

Junior

Answer

CHECK ensures column values follow specific rules, enforcing data integrity at the database level.
Quick Summary: CHECK constraint enforces a condition on column values: age INTEGER CHECK (age >= 0 AND age <= 150). Table-level CHECK for multi-column conditions: CHECK (end_date > start_date). Fails silently for NULL values (NULL is not checked - it passes). PostgreSQL validates CHECK on INSERT and UPDATE. NOT NULL is a special CHECK that prevents NULL. Use for business rule enforcement.
Permalink
Q21:

What is the purpose of the RETURNING clause in PostgreSQL?

Junior

Answer

RETURNING returns columns from rows affected by INSERT, UPDATE, or DELETE, useful for retrieving generated IDs.
Quick Summary: RETURNING clause returns values from rows affected by INSERT, UPDATE, or DELETE - like a SELECT on modified rows. INSERT ... RETURNING id gets the auto-generated ID. UPDATE ... RETURNING new_value gets updated values. DELETE ... RETURNING * shows what was deleted. Eliminates a second SELECT query. More efficient and atomic than insert-then-select.
Permalink
Q22:

What is the difference between INNER JOIN and FULL OUTER JOIN?

Junior

Answer

INNER JOIN returns only matching rows, while FULL OUTER JOIN returns all rows from both tables with NULLs for non-matching data.
Quick Summary: INNER JOIN returns only rows with matching values in both tables (intersection). LEFT JOIN returns all rows from the left table plus matched rows from the right (unmatched right rows are NULL). RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both tables - matched where possible, NULL where not. Most commonly used: INNER JOIN for related data, LEFT JOIN to include optional relations.
Permalink
Q23:

What is a sequence in PostgreSQL?

Junior

Answer

A sequence is an independent object that generates incremental numeric values, commonly used for primary keys.
Quick Summary: A sequence is an independent database object that generates unique integer values in order. Used to auto-increment primary keys. CREATE SEQUENCE seq_name. Get next value: nextval("seq_name"). Current value: currval(). SERIAL/IDENTITY columns use sequences internally. Sequences are not transaction-aware - a rolled-back transaction still advances the sequence, creating gaps (this is expected and fine).
Permalink
Q24:

What is the purpose of the COALESCE function?

Junior

Answer

COALESCE returns the first non-NULL value from a list, useful for handling missing data.
Quick Summary: COALESCE returns the first non-NULL value from a list of arguments. COALESCE(col, "default") returns col if not NULL, else "default". Useful for: providing fallback values in queries, replacing NULLs for display. COALESCE(a, b, c) returns first non-NULL of a, b, c. Related: NULLIF(a, b) returns NULL if a equals b. Both are null-handling utilities used in SELECT columns and WHERE conditions.
Permalink
Q25:

What are indexes in PostgreSQL and why are they important?

Junior

Answer

Indexes speed up reads by creating fast lookup structures. PostgreSQL supports B-tree, Hash, GIN, GiST, SP-GiST, and BRIN.
Quick Summary: Indexes speed up data retrieval by creating a sorted data structure (B-tree by default) on one or more columns. Without an index, PostgreSQL scans every row (seq scan). With an index, it jumps directly to matching rows (index scan). Tradeoff: faster reads but slower writes (index updated on every INSERT/UPDATE/DELETE). Index columns frequently used in WHERE, JOIN, and ORDER BY.
Permalink
Q26:

What is a partial index?

Junior

Answer

A partial index applies only to rows meeting a condition, reducing storage and improving performance.
Quick Summary: Partial index indexes only rows matching a WHERE condition. Example: CREATE INDEX ON orders (user_id) WHERE status = "active". Smaller than a full index (only active orders indexed), faster to maintain, and PostgreSQL can only use it when the query matches the WHERE condition. Use when most queries target a subset of rows (active records, recent data, non-null values).
Permalink
Q27:

What is a composite index?

Junior

Answer

A composite index includes multiple columns and is used left to right, making column order important for optimization.
Quick Summary: Composite index covers multiple columns: CREATE INDEX ON orders (user_id, created_at). Useful when queries filter on multiple columns together. Index prefix rule: a composite index on (a, b, c) can be used for queries on (a), (a, b), or (a, b, c) but NOT (b) alone. Put the most selective and most frequently filtered column first.
Permalink
Q28:

What is a materialized view?

Junior

Answer

A materialized view stores query results physically and must be refreshed manually, improving performance for expensive queries.
Quick Summary: Materialized view stores the result of a query on disk - like a cached table. Faster to query than a regular view (no re-execution of the underlying query). Requires manual refresh: REFRESH MATERIALIZED VIEW viewname. REFRESH CONCURRENTLY allows reads during refresh (requires unique index). Use for expensive aggregate queries or reports that are expensive to recompute on every access.
Permalink
Q29:

What is the difference between DELETE and TRUNCATE in PostgreSQL?

Junior

Answer

DELETE removes rows with full logging. TRUNCATE removes all rows instantly, resets identities, and is minimally logged.
Quick Summary: DELETE: removes matching rows, logged in WAL (slower for large deletions), fires triggers, can use WHERE, transactional. TRUNCATE: removes all rows very fast (doesn't scan rows), minimal WAL logging, fires truncate triggers, can't use WHERE. Both are transactional in PostgreSQL (unlike MySQL MyISAM). TRUNCATE is the right choice when clearing entire tables for ETL or test data cleanup.
Permalink
Q30:

What is the VACUUM operation in PostgreSQL?

Junior

Answer

VACUUM removes dead tuples, reclaims storage, improves performance, and prevents transaction ID wraparound.
Quick Summary: VACUUM reclaims storage from dead tuples created by PostgreSQL's MVCC - rows that were deleted or updated but not yet physically removed. Dead tuples occupy space and slow down queries. VACUUM marks space as reusable (doesn't shrink file). VACUUM ANALYZE also updates query planner statistics. Autovacuum runs automatically. Run manually if autovacuum is falling behind on high-traffic tables.
Permalink
Q31:

What is the ANALYZE command used for?

Junior

Answer

ANALYZE collects table statistics used by the query optimizer to choose efficient execution plans.
Quick Summary: ANALYZE collects statistics about table column value distribution and stores them in pg_statistic. The query planner uses these statistics to estimate row counts and choose the best execution plan. Stale statistics lead to bad plan choices (wrong join order, wrong index). Run ANALYZE after bulk data loads or large changes. Autovacuum runs ANALYZE automatically when tables change significantly.
Permalink
Q32:

What is the difference between a function and a stored procedure in PostgreSQL?

Junior

Answer

Functions return values and can be used in queries, while procedures do not return values and can manage transactions.
Quick Summary: Functions (CREATE FUNCTION) return a value and can be used in SELECT, WHERE, etc. They can't manage transactions. Stored procedures (CREATE PROCEDURE, PostgreSQL 11+) don't need to return a value and can COMMIT/ROLLBACK within their body - enabling transaction control inside procedures. Call procedures with CALL procedure_name(). Functions are more versatile for query use; procedures for transaction control.
Permalink
Q33:

What is PostgreSQL’s JSONB data type?

Junior

Answer

JSONB stores JSON in a binary format for fast searching and indexing, supporting advanced JSON operations.
Quick Summary: JSONB stores JSON as decomposed binary - faster to query than JSON (which stores as text). JSONB supports: indexing with GIN indexes, operators for key lookup (->), path extraction (#>), containment (@>), and key existence (?). Use JSONB when you need to query JSON fields. Use JSON only when you need to preserve key order or exact formatting. Most use cases should use JSONB.
Permalink
Q34:

What are foreign data wrappers (FDW)?

Junior

Answer

FDWs allow PostgreSQL to query external data sources like MySQL, MongoDB, APIs, or files as if they were local.
Quick Summary: Foreign Data Wrappers (FDW) allow PostgreSQL to query external data sources as if they were local tables. Connect to: other PostgreSQL databases (postgres_fdw), MySQL, CSV files, Redis, S3, REST APIs. Install extension, create a server, define user mapping, then create foreign tables. Useful for data federation and migrating data across systems without ETL pipelines.
Permalink
Q35:

What is the purpose of the EXPLAIN command?

Junior

Answer

EXPLAIN shows how a query will execute, including index usage, join types, and cost estimates.
Quick Summary: EXPLAIN shows the query execution plan PostgreSQL will use without actually running the query. EXPLAIN ANALYZE runs the query and shows actual vs estimated row counts and execution times. Look for: sequential scans on large tables (may need an index), hash joins vs nested loops, rows estimates vs actual rows (big gap = stale statistics). Essential for performance debugging.
Permalink
Q36:

What is PostgreSQL’s MVCC and how does it enable concurrent reads and writes?

Mid

Answer

MVCC gives each transaction a snapshot of the database. PostgreSQL stores multiple row versions with transaction IDs, allowing readers and writers to work without blocking each other.
Quick Summary: MVCC (Multi-Version Concurrency Control) lets readers and writers proceed concurrently without blocking each other. When a row is updated, PostgreSQL creates a new version (tuple) with a new transaction ID, keeping the old version for concurrent readers. Each transaction sees a consistent snapshot of the data as of when it started. No read locks needed - readers never block writers and vice versa.
Permalink
Q37:

What is a tuple in PostgreSQL?

Mid

Answer

A tuple is a row stored in a PostgreSQL table. Updates create new tuple versions while old ones become dead tuples until vacuumed.
Quick Summary: In PostgreSQL, a tuple is a row version in a table. Every UPDATE creates a new tuple (new row version) rather than modifying in place. Old tuples stay visible to concurrent transactions and are later cleaned up by VACUUM. Each tuple has system columns: xmin (transaction that created it), xmax (transaction that deleted it), ctid (physical location). Multiple tuple versions enable MVCC.
Permalink
Q38:

What is a transaction ID and why is wraparound dangerous?

Mid

Answer

Each transaction gets a unique XID. Because XIDs are 32-bit, they eventually wrap around, risking marking live rows as expired. VACUUM prevents wraparound by freezing tuples.
Quick Summary: Transaction ID (XID) is a 32-bit integer assigned to every transaction. PostgreSQL uses XIDs to determine tuple visibility in MVCC. Wraparound danger: after ~2 billion transactions, XID wraps around to 0. Old rows could suddenly appear to be "in the future" and become invisible - catastrophic data loss. Autovacuum prevents this by freezing old tuples (replacing real XIDs with FrozenXID that's always visible).
Permalink
Q39:

What is VACUUM FULL and how does it differ from regular VACUUM?

Mid

Answer

VACUUM FULL rewrites the table, reclaiming space and compacting rows but requires an exclusive lock. Regular VACUUM only removes dead tuples and updates visibility maps without heavy locking.
Quick Summary: Regular VACUUM marks dead tuples as reusable space without compacting the file or returning space to the OS. VACUUM FULL rewrites the entire table to a new file, reclaiming disk space (table shrinks). VACUUM FULL requires an exclusive lock (all reads and writes block during operation) - not safe on high-traffic tables. Use VACUUM FULL rarely, or use pg_repack as a non-blocking alternative.
Permalink
Q40:

What is the visibility map in PostgreSQL?

Mid

Answer

The visibility map tracks pages containing only visible tuples, enabling index-only scans without reading table pages.
Quick Summary: The visibility map is a per-table file with one bit per page tracking if all tuples on that page are visible to all transactions. Index-only scans use the visibility map to avoid fetching the heap page to verify tuple visibility. VACUUM updates the visibility map. Pages with all-visible tuples allow index-only scans, dramatically improving read performance for covering indexes.
Permalink
Q41:

What is an index-only scan?

Mid

Answer

An index-only scan satisfies a query using only index data when all required columns are in the index, avoiding table reads.
Quick Summary: Index-only scan reads all needed columns directly from the index without accessing the heap (table). Possible when: all SELECT columns are in the index, and the visibility map shows the page as all-visible (so PostgreSQL doesn't need to check heap for tuple visibility). Much faster than regular index scan (no heap fetch). Create covering indexes: include all frequently queried columns.
Permalink
Q42:

What is the difference between B-Tree, GIN, and GiST indexes?

Mid

Answer

B-Tree handles equality and range queries. GIN is optimized for arrays, JSONB, and full-text search. GiST supports ranges, geometric types, and custom index logic.
Quick Summary: B-Tree: default, balanced tree, handles equality and range queries, sorted order - use for most queries. GIN (Generalized Inverted Index): multi-value items - use for JSONB, arrays, full-text search (indexes all keys/elements). GiST (Generalized Search Tree): geometric data, range types, full-text search - use for PostGIS, EXCLUDE constraints. Each handles different query patterns.
Permalink
Q43:

When would you use a BRIN index?

Mid

Answer

BRIN is ideal for very large tables where data is naturally ordered, storing summaries of value ranges to minimize storage.
Quick Summary: BRIN (Block Range INdex) stores min/max values per range of disk pages - very small index, very fast to create, low maintenance overhead. Only useful when column values correlate with physical storage order (sequential inserts of timestamps, monotonic IDs). Not selective for random data. Ideal for time-series tables where you INSERT rows in timestamp order - BRIN can eliminate large chunks of the table.
Permalink
Q44:

What is the purpose of pg_stat_activity?

Mid

Answer

pg_stat_activity displays running queries, wait events, and session details for debugging locks and performance issues.
Quick Summary: pg_stat_activity is a system view showing one row per database connection with: pid (process ID), query (SQL being run), state (active, idle, idle in transaction), wait_event (what it's waiting on), application_name, client_addr. Essential for diagnosing: blocking queries (find locks), long-running transactions, idle-in-transaction connections holding locks, and overall connection usage.
Permalink
Q45:

What is a CTE and why use it?

Mid

Answer

A CTE organizes complex queries into readable blocks and supports recursion and reusable query logic.
Quick Summary: CTE (Common Table Expression) is a named temporary result set defined with WITH, usable in the main query. WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users WHERE age > 18. Improves readability for complex queries. Recursive CTEs traverse hierarchical data. In PostgreSQL 12+, CTEs are inlined by default (the optimizer can push conditions inside).
Permalink
Q46:

What is the difference between a CTE and a subquery?

Mid

Answer

CTEs improve readability and reusability, while subqueries are nested directly. CTEs may materialize results depending on PostgreSQL version.
Quick Summary: CTE: named, reusable within a query, can be recursive, executes once and result is referenced multiple times (in some cases). Subquery: embedded in the parent query, potentially re-executed for each row (correlated subquery). Pre-PostgreSQL 12, CTEs were optimization fences (result materialized). Now both can be optimized similarly. Use CTEs for readability; subqueries when optimizer needs more flexibility.
Permalink
Q47:

How do recursive CTEs work?

Mid

Answer

Recursive CTEs contain an anchor part and a recursive part that repeats until no new rows are returned.
Quick Summary: Recursive CTEs traverse hierarchical or graph-like data. Structure: WITH RECURSIVE cte AS (base_case UNION ALL recursive_case WHERE termination_condition). Base case: initial rows (root nodes). Recursive case: references the CTE to get next level. Use for: org charts (employee hierarchy), category trees, graph traversal, bill of materials. Add a depth counter or LIMIT to prevent infinite recursion.
Permalink
Q48:

What are dead tuples and why do they accumulate?

Mid

Answer

Dead tuples are obsolete row versions created by updates and deletes. They accumulate because PostgreSQL never overwrites rows.
Quick Summary: Dead tuples are old row versions left behind by UPDATE and DELETE operations in MVCC. When you update a row, PostgreSQL creates a new tuple version but keeps the old one visible for concurrent transactions. Once no active transaction needs the old version, it becomes a "dead tuple". Dead tuples accumulate, bloating tables and slowing queries. VACUUM removes dead tuples and marks space reusable.
Permalink
Q49:

How does PostgreSQL handle deadlocks?

Mid

Answer

PostgreSQL detects cycles in lock waits and cancels one transaction, usually the one with lowest cost.
Quick Summary: PostgreSQL detects deadlocks by running a deadlock detection process after lock_timeout expires. It builds a wait-for graph and detects cycles. When a deadlock is found, PostgreSQL aborts one of the transactions (lowest cost or most recent) with "deadlock detected" error. Prevent deadlocks: always acquire locks in consistent order, keep transactions short, use NOWAIT or FOR UPDATE SKIP LOCKED.
Permalink
Q50:

What is a tablespace?

Mid

Answer

A tablespace maps database storage to filesystem locations, enabling distribution of data across disks.
Quick Summary: Tablespace defines the physical disk location where database objects are stored. CREATE TABLESPACE fast_ssd LOCATION "/mnt/ssd". Create tables in it: CREATE TABLE big_table (...) TABLESPACE fast_ssd. Use to: place hot tables on fast SSDs, put large archive tables on slower cheap storage, separate indexes from their tables for parallel I/O, and spread load across multiple disks.
Permalink
Q51:

What are PostgreSQL extensions?

Mid

Answer

Extensions add new features such as PostGIS, pg_stat_statements, and hstore without modifying core PostgreSQL.
Quick Summary: PostgreSQL extensions add functionality without modifying the core. Install: CREATE EXTENSION extension_name. Popular extensions: pg_stat_statements (query statistics), PostGIS (geospatial), pgcrypto (encryption), uuid-ossp (UUID generation), pg_trgm (trigram similarity for LIKE queries), hstore (key-value in a column), timescaledb (time-series). Extensions ship with PostgreSQL or via PGXN.
Permalink
Q52:

What is pg_stat_statements used for?

Mid

Answer

pg_stat_statements collects query execution statistics like total time and frequency, useful for performance tuning.
Quick Summary: pg_stat_statements extension tracks execution statistics for every SQL query: total execution time, number of calls, rows returned, buffer hits/misses. Query: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC. Essential for finding which queries are consuming the most database time. Reset with pg_stat_statements_reset().
Permalink
Q53:

What is WAL in PostgreSQL?

Mid

Answer

WAL logs all changes before writing data files to ensure durability and enable crash recovery.
Quick Summary: WAL (Write-Ahead Log) records every database change before applying it to data files. On crash, PostgreSQL replays WAL to recover to a consistent state. WAL also enables: replication (WAL is streamed to standbys), point-in-time recovery (restore to any past moment), and logical decoding. WAL files are in pg_wal directory. wal_level setting controls detail level.
Permalink
Q54:

What is a WAL checkpoint?

Mid

Answer

A checkpoint flushes dirty pages to disk, reducing recovery time but increasing I/O load if too frequent.
Quick Summary: WAL checkpoint writes all dirty buffers (modified data pages in shared_buffers) to disk files. This ensures recovery can start from a recent point rather than replaying all WAL from the beginning. Checkpoints happen every checkpoint_timeout (5 min default) or when WAL reaches max_wal_size. Frequent checkpoints: faster recovery but more I/O. Checkpoint duration visible in pg_stat_bgwriter.
Permalink
Q55:

What is WAL archiving?

Mid

Answer

WAL archiving stores old WAL segments for point-in-time recovery and disaster recovery.
Quick Summary: WAL archiving copies WAL segment files to a separate location (another disk, S3, network share) using the archive_command parameter. Used for: Point-In-Time Recovery (PITR) - restore to any moment by replaying archived WAL, and building standbys from a base backup + WAL archives. Essential for DR strategy when you need recovery beyond the current WAL on disk.
Permalink
Q56:

What are HOT updates?

Mid

Answer

HOT updates modify rows without updating indexes when non-indexed columns change, reducing index bloat.
Quick Summary: HOT (Heap Only Tuple) update: when an updated row fits in the same table page AND no indexed columns changed, PostgreSQL creates a chain: old tuple points to new tuple in the same page. Index entries still point to the old tuple which redirects to new. Benefit: indexes don't need to be updated (major write amplification reduction). HOT updates only work when fillfactor leaves room in the page.
Permalink
Q57:

What is index bloat and how is it fixed?

Mid

Answer

Index bloat occurs when indexes accumulate dead entries. Fix with REINDEX or VACUUM FULL.
Quick Summary: Index bloat: dead index entries accumulate after many updates/deletes - index grows without being reclaimed by regular VACUUM. Identify with pgstattuple extension or VACUUM VERBOSE. Fix without downtime: REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds the index while allowing reads and writes. Or pg_repack. Regular autovacuum helps prevent bloat but can lag on very high-write tables.
Permalink
Q58:

What is logical replication?

Mid

Answer

Logical replication sends row-level changes to subscribers and supports selective table replication.
Quick Summary: Logical replication replicates data changes at the row level using logical decoding of the WAL. Subscribers receive INSERT, UPDATE, DELETE events for specific tables. Supports: replicating to different PostgreSQL versions, selective table replication, cross-version upgrades, and CDC (Change Data Capture) for downstream systems. More flexible than physical replication but can't replicate DDL automatically.
Permalink
Q59:

Difference between physical and logical replication?

Mid

Answer

Physical replicates data files, while logical replicates row changes. Physical is faster; logical is more flexible.
Quick Summary: Physical replication: copies the exact binary data files (byte-for-byte replica of the primary). Fast, all objects replicated, standby must be same PostgreSQL version. Logical replication: replicates row-level changes for selected tables/publications. More flexible (different schema versions, selective tables, cross-version). Physical for HA standby; logical for migrations, selective replication, and CDC.
Permalink
Q60:

What is synchronous replication?

Mid

Answer

Synchronous replication waits for confirmation from at least one replica before committing.
Quick Summary: Synchronous replication: primary waits for the standby to confirm it received and wrote the WAL before acknowledging the commit to the client (synchronous_standby_names). Guarantees zero data loss on failover (RPO=0). Higher latency per transaction. Use for financial data where data loss is unacceptable. Configure with synchronous_commit = on and a synchronous standby.
Permalink
Q61:

What is asynchronous replication?

Mid

Answer

Asynchronous replication commits without waiting for replicas, improving performance at the cost of durability.
Quick Summary: Asynchronous replication: primary commits without waiting for standby acknowledgment. Lower write latency but standby may lag behind. If primary fails, recently committed transactions may not be on standby (RPO > 0). Default replication mode in PostgreSQL. Acceptable for most apps where a small amount of data loss is tolerable in exchange for lower latency.
Permalink
Q62:

What is the role of autovacuum?

Mid

Answer

Autovacuum removes dead tuples, prevents wraparound, and updates visibility maps.
Quick Summary: Autovacuum is a background daemon that automatically runs VACUUM and ANALYZE when tables have enough dead tuples or changed data. Prevents XID wraparound, reclaims dead tuple space, and keeps statistics fresh. Tune with: autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, autovacuum_vacuum_cost_delay. On high-write tables, tune per-table with ALTER TABLE SET (autovacuum_*).
Permalink
Q63:

What are prepared statements?

Mid

Answer

Prepared statements compile SQL once and reuse execution plans, improving speed and preventing SQL injection.
Quick Summary: Prepared statements parse and plan a SQL query once, then execute multiple times with different parameters. PREPARE stmt AS SELECT * FROM users WHERE id = $1; EXECUTE stmt(42). Benefits: plan reuse (faster for repeated queries), protection against SQL injection (parameters are separate from query). In application code, most drivers use prepared statements automatically (parameterized queries).
Permalink
Q64:

What is query planning and cost estimation?

Mid

Answer

The planner evaluates possible execution paths and selects the lowest-cost plan using statistics.
Quick Summary: PostgreSQL query planner estimates the cost of different execution strategies (seq scan, index scan, hash join, nested loop) using table statistics from pg_statistic. It chooses the plan with lowest estimated cost. Cost estimates depend on: row count estimates, column value distribution, physical I/O cost settings (seq_page_cost, random_page_cost). Stale stats or bad estimates lead to poor plan choices.
Permalink
Q65:

Why does PostgreSQL choose sequential scans sometimes?

Mid

Answer

Sequential scans occur when tables are small, statistics are outdated, or index selectivity is poor.
Quick Summary: Seq scan can be faster than index scan when: the query retrieves a large fraction of the table (reading index then fetching most pages is worse than just reading pages sequentially), the table fits in shared_buffers (already cached), or random_page_cost is set high (sequential I/O is cheaper). Lower random_page_cost (SSD: 1.1 vs HDD: 4.0) encourages PostgreSQL to use indexes more.
Permalink
Q66:

What is ANALYZE and why is it important?

Mid

Answer

ANALYZE updates statistics used by the optimizer. Outdated stats lead to poor performance.
Quick Summary: ANALYZE samples the table data and stores column statistics (histogram, most-common values, null fraction, correlation) in pg_statistic. The query planner uses these to estimate how many rows match a WHERE condition. Inaccurate stats (on high-change tables) lead to bad plan choices. Run manually after bulk loads. Autovacuum handles routine analysis. Also updates the visibility map.
Permalink
Q67:

What is keyset pagination?

Mid

Answer

Keyset pagination uses indexed values to fetch the next rows efficiently, avoiding slow OFFSET scans.
Quick Summary: Keyset pagination (seek method) uses the last seen value as a WHERE filter: WHERE id > last_id LIMIT 10. Much faster than OFFSET pagination for large offsets because PostgreSQL can use an index and jump directly to the start position rather than scanning and discarding rows. Downside: you can't jump to page N directly, only go forward/backward from a cursor position.
Permalink
Q68:

What is constraint exclusion?

Mid

Answer

Constraint exclusion allows PostgreSQL to skip irrelevant partitions in partitioned tables.
Quick Summary: Constraint exclusion allows PostgreSQL to skip partitions (child tables) that can't possibly contain rows matching the WHERE clause. For partitioned tables: SELECT * FROM measurements WHERE date > "2024-01-01" skips partitions with older data. Works by comparing WHERE conditions against partition check constraints. Modern declarative partitioning uses partition pruning (more efficient).
Permalink
Q69:

What are generated columns?

Mid

Answer

Generated columns compute values from other columns, either stored physically or computed on demand.
Quick Summary: Generated columns compute their value from other columns automatically. STORED: value is computed and saved to disk on each INSERT/UPDATE (takes space but fast to read). VIRTUAL: computed on read (not yet supported by PostgreSQL - only STORED is available). Example: full_name TEXT GENERATED ALWAYS AS (first_name || " " || last_name) STORED. Always up-to-date, no trigger needed.
Permalink
Q70:

Difference between BEFORE and AFTER triggers?

Mid

Answer

BEFORE triggers validate or modify data before writes. AFTER triggers run after data is written.
Quick Summary: BEFORE trigger runs before the operation - can modify the NEW row or cancel the operation (return NULL). AFTER trigger runs after the operation completes - sees the final committed state. BEFORE triggers are used for: data validation, transforming input data, setting audit fields. AFTER triggers are used for: logging changes, maintaining denormalized data, sending notifications after successful operation.
Permalink
Q71:

How does concurrent index creation work?

Mid

Answer

CREATE INDEX CONCURRENTLY builds indexes without blocking writes, though it takes longer to complete.
Quick Summary: CREATE INDEX CONCURRENTLY builds an index without taking a lock that blocks writes. Regular CREATE INDEX locks the table for reads and writes during build. CONCURRENTLY: takes longer (two table scans), can't run inside a transaction block. May fail if the table has long-running transactions when the index build starts. Best practice for production: always use CONCURRENTLY for new indexes on live tables.
Permalink
Q72:

What is a partial index and when is it useful?

Mid

Answer

Partial indexes apply only to rows satisfying a condition, reducing index size and improving efficiency.
Quick Summary: Partial index indexes only rows matching a WHERE condition, making it smaller and faster to maintain. Useful when: queries consistently filter on a specific value (WHERE status = "pending"), most of the table data is irrelevant to queries (soft-deleted rows), or you want a partial unique constraint (unique among non-deleted rows). The query must match the partial index WHERE to use it.
Permalink
Q73:

What is the Query Execution Engine?

Mid

Answer

The execution engine runs the query plan, performing scans, joins, filtering, and computations.
Quick Summary: The Query Execution Engine takes the planner's chosen plan and actually executes it. It uses operator nodes (SeqScan, IndexScan, HashJoin, NestLoop, Sort, Aggregate) arranged as a tree - data flows from leaf nodes up through operators to produce the final result. Rows flow in batches through the pipeline. EXPLAIN ANALYZE shows each node's estimated vs actual rows and execution time.
Permalink
Q74:

What are temporary tables?

Mid

Answer

Temporary tables exist only during a session and are used for intermediate data processing.
Quick Summary: Temporary tables exist only for the duration of a session (or transaction). CREATE TEMP TABLE temp_data (...). Automatically dropped when session ends. Not visible to other sessions. Useful for: staging intermediate results, breaking complex queries into steps, storing data for batch processing. Each session gets its own temp table namespace - no naming conflicts. Temp tables can have indexes.
Permalink
Q75:

What causes buffer cache misses and how do you prevent them?

Mid

Answer

Buffer misses occur when needed pages are not in memory. Prevent with tuning shared_buffers, indexing correctly, and reducing full scans.
Quick Summary: Buffer cache miss: requested page is not in shared_buffers, must read from disk (expensive). Prevent by: sizing shared_buffers to fit your working set (start at 25% of RAM), using pg_prewarm to load critical tables/indexes on startup, keeping hot data small (archive old data), and monitoring with pg_statio_user_tables (heap_blks_hit vs heap_blks_read ratio - aim for >99%).
Permalink
Q76:

How does PostgreSQL internally implement MVCC without using locks?

Senior

Answer

PostgreSQL stores multiple row versions with transaction IDs. Readers check tuple visibility using snapshots, avoiding locks. Writers create new versions instead of overwriting data. VACUUM later removes dead tuples.
Quick Summary: MVCC in PostgreSQL works by storing multiple versions of each row. Each tuple has xmin (XID of creating transaction) and xmax (XID of deleting/updating transaction). A transaction can see a tuple if xmin is committed and precedes its snapshot, and xmax is either 0 or from a transaction that started after the snapshot. No locks needed for reads - the snapshot determines visibility mathematically.
Permalink
Q77:

What is the role of hint bits in tuple visibility?

Senior

Answer

Hint bits mark tuples as committed or rolled back after checks, reducing future visibility lookups. These updates happen lazily and do not require WAL writes.
Quick Summary: Hint bits are two bits stored in each tuple header that cache the commit status of xmin and xmax. Initially unset, they are set the first time a query inspects the tuple and checks pg_clog (now pg_xact) for commit status. Once set (COMMITTED or ABORTED), future visibility checks skip the pg_clog lookup. Reduces overhead of repeated visibility checks for the same tuple.
Permalink
Q78:

How do index-only scans work and when do they fail?

Senior

Answer

Index-only scans succeed when the visibility map confirms all tuples on a page are visible. If pages contain dead tuples, PostgreSQL must recheck the heap, making the scan not fully index-only.
Quick Summary: Index-only scans work by reading all needed column values from the index itself, skipping the heap fetch. The visibility map must show the page as all-visible (VACUUM has run recently). They fail when: the query needs columns not in the index, the visibility map bit is not set (heap fetch needed to verify visibility), or the table has recent unfrozen changes. Maximize by using covering indexes and running VACUUM.
Permalink
Q79:

How does PostgreSQL manage tuple freezing and why is it required?

Senior

Answer

Tuple freezing marks old tuples with special XIDs to prevent wraparound. Autovacuum freezes tuples in stages to avoid large rewrites and ensure data remains valid indefinitely.
Quick Summary: Tuple freezing replaces the real transaction ID in old tuples with FrozenXID (a special value that's always considered "in the past"). This is required because XID is 32-bit and wraps around after ~2 billion transactions. Frozen tuples are visible to all transactions regardless of XID comparison. Autovacuum freezes tuples when they're old enough (vacuum_freeze_min_age). Without freezing, XID wraparound causes data to become invisible.
Permalink
Q80:

What are HOT updates and why are they important?

Senior

Answer

HOT updates modify rows without touching indexes when only non-indexed columns change. This reduces index bloat and improves write performance.
Quick Summary: HOT (Heap Only Tuple) updates avoid updating indexes when the row is updated but indexed columns are unchanged and the new tuple fits on the same page. The old tuple gets a HEAP_HOT_UPDATED flag pointing to the new tuple in the same page. Index entries still point to the old tuple, which chains to the new one. Dramatically reduces write amplification for frequent updates to non-indexed columns.
Permalink
Q81:

What is index bloat and how is it handled in PostgreSQL?

Senior

Answer

Index bloat occurs due to dead entries. It is resolved using REINDEX, VACUUM FULL, or pg_repack for online index rebuilds without blocking writes.
Quick Summary: Index bloat: dead index entries accumulate as rows are updated/deleted. Unlike the table, index pages are not reclaimed by regular VACUUM (only reused for new entries). Check bloat with pgstattuple or the check-postgres tool. Fix: REINDEX CONCURRENTLY (rebuilds cleanly without downtime, PostgreSQL 12+) or pg_repack (rewrites table and indexes online). Prevent with appropriate autovacuum tuning.
Permalink
Q82:

How does PostgreSQL implement full-text search?

Senior

Answer

Full-text search uses tsvector and tsquery types with GIN or GiST indexes. Dictionaries handle stemming, tokenization, and stop-word removal.
Quick Summary: PostgreSQL full-text search: tsvector stores preprocessed searchable text (stemmed, stop words removed). tsquery defines a search query with operators. CREATE INDEX ON docs USING GIN(to_tsvector("english", content)) for efficient search. Query: WHERE to_tsvector("english", content) @@ to_tsquery("english", "postgres & performance"). Use ts_rank() for relevance scoring. For advanced needs, consider Elasticsearch.
Permalink
Q83:

Difference between GIN and GiST indexes and when to choose each?

Senior

Answer

GIN is optimized for discrete values like arrays and JSONB. GiST supports flexible structures like geospatial or range types. GIN is fast for reads; GiST handles approximate matching.
Quick Summary: GIN (Generalized Inverted Index): stores a posting list for each element value - fast for lookups (contains, equality). Best for: JSONB (query by key/value), array containment, full-text search. Slower to build and update. GiST (Generalized Search Tree): balanced tree supporting arbitrary ordering/distance. Best for: geometric data (PostGIS), range types, nearest-neighbor searches. Use GIN for containment, GiST for distance/overlap queries.
Permalink
Q84:

Why is autovacuum tuning important in large systems?

Senior

Answer

Autovacuum tuning prevents bloat, avoids XID wraparound, and keeps index efficiency high. Parameters like scale factors, cost delay, and worker count are critical.
Quick Summary: Autovacuum tuning matters because: dead tuple bloat slows queries (more pages to scan), XID wraparound causes catastrophic failure, stale statistics cause bad query plans. Key params: autovacuum_vacuum_scale_factor (default 0.2 = vacuum after 20% rows change - too high for large tables), autovacuum_vacuum_cost_delay (throttles I/O impact). Set lower scale factors for large, high-write tables with per-table storage params.
Permalink
Q85:

How does PostgreSQL use multi-column statistics?

Senior

Answer

Multi-column statistics capture value correlations across columns to help the optimizer choose better join orders and filter strategies.
Quick Summary: Multi-column statistics: by default, PostgreSQL assumes column independence (poor estimate for correlated columns). CREATE STATISTICS stat_name (dependencies) ON col1, col2 FROM table tells PostgreSQL to track correlation between columns. This improves row count estimates for queries filtering on multiple correlated columns (city + zip code, product + category). Introduced in PostgreSQL 10, extended in later versions.
Permalink
Q86:

What is WAL and how does WAL archiving support disaster recovery?

Senior

Answer

WAL logs every change before writing to data files. WAL archiving stores old WAL segments enabling point-in-time recovery.
Quick Summary: WAL records every change before applying it - on crash, replay WAL from last checkpoint to recover. WAL archiving copies WAL segments to a safe location (S3, network) using archive_command. For DR: take a base backup (pg_basebackup), then archive WAL continuously. PITR: restore from base backup + replay archived WAL to any moment in time. Essential for zero data loss recovery and creating standbys from backups.
Permalink
Q87:

What are the performance implications of increasing shared_buffers?

Senior

Answer

Increasing shared_buffers improves caching but may cause slow checkpoints or reduce OS cache efficiency. A balanced configuration is required.
Quick Summary: shared_buffers is the main PostgreSQL cache for table and index pages. Increasing it reduces disk I/O for frequently accessed data. Recommended starting point: 25% of RAM (OS also caches, so more than 40% has diminishing returns). Too small: constant disk I/O. Too large: leaves insufficient RAM for OS cache and sorts. Monitor buffer hit ratio in pg_statio_user_tables - target >99% hit rate.
Permalink
Q88:

How do checkpoints affect PostgreSQL performance?

Senior

Answer

Frequent checkpoints cause I/O spikes, while infrequent ones increase crash recovery time. Proper tuning balances durability with performance.
Quick Summary: Checkpoints flush dirty shared_buffers to disk. Frequent checkpoints: faster crash recovery but continuous I/O load (write storm at each checkpoint). Infrequent checkpoints (larger max_wal_size): less I/O normally but longer recovery time and more WAL to replay. checkpoint_completion_target (default 0.9) spreads the writes over 90% of the checkpoint interval to smooth out I/O spikes.
Permalink
Q89:

What is synchronous_commit and its effect on durability?

Senior

Answer

synchronous_commit waits for WAL flush before acknowledging commits, improving durability but increasing latency.
Quick Summary: synchronous_commit controls when PostgreSQL acknowledges a transaction as committed. on (default): WAL is flushed to disk before commit returns. off: commit returns before WAL write (faster but up to wal_writer_delay of data loss on crash). local: WAL flushed locally but not to synchronous standbys. remote_write/remote_apply: for replication durability. Use off for high-write, loss-tolerant workloads (metrics, logs).
Permalink
Q90:

How does PostgreSQL handle isolation levels, especially REPEATABLE READ?

Senior

Answer

REPEATABLE READ ensures consistent snapshots but not full phantom protection. SERIALIZABLE with SSI is required to prevent anomalies.
Quick Summary: REPEATABLE READ in PostgreSQL uses MVCC snapshot isolation. The transaction sees the database as it was at the START of the transaction - later committed changes are invisible. Prevents dirty reads and non-repeatable reads. Serialization anomalies are still possible (phantom reads don't occur because PostgreSQL uses snapshot-based, not lock-based, isolation). Use READ COMMITTED (default) for most cases; REPEATABLE READ for reporting transactions.
Permalink
Q91:

What is Serializable Snapshot Isolation (SSI)?

Senior

Answer

SSI tracks read-write dependencies to detect anomaly risks. PostgreSQL aborts conflicting transactions to guarantee serializability.
Quick Summary: SSI (Serializable Snapshot Isolation) provides true serializable transactions without traditional locking. PostgreSQL tracks read and write dependencies between transactions and aborts transactions that would create a non-serializable history. Uses SIReadLocks to track what each transaction reads. Overhead: more memory for tracking, higher abort rate under contention. Use SERIALIZABLE isolation level for strict correctness requirements.
Permalink
Q92:

How does PostgreSQL detect lock contention?

Senior

Answer

Lock contention is detected using pg_locks, pg_stat_activity, and wait events. These tools reveal blocked queries and lock chains.
Quick Summary: Lock contention detection: pg_stat_activity shows wait_event and wait_event_type. Wait event "Lock" means waiting for another session's lock. pg_locks view shows all current locks and who is waiting for what. pg_blocking_pids(pid) returns which PIDs are blocking a session. pg_stat_activity joined with pg_locks gives a complete picture of blocking chains. Long-running transactions are the most common cause.
Permalink
Q93:

How can join performance be optimized in PostgreSQL?

Senior

Answer

Optimize joins using proper join orders, indexing, avoiding functions on indexed columns, and ensuring accurate statistics.
Quick Summary: Join performance optimization: ensure join columns are indexed on both sides. Check EXPLAIN ANALYZE for nested loop vs hash join vs merge join. nested loop is good for small tables or index lookups; hash join for large unsorted tables; merge join for pre-sorted data. Increase work_mem (default 4MB) to allow hash joins to stay in memory instead of spilling to disk. Rewrite queries to join on indexed columns.
Permalink
Q94:

How does PostgreSQL choose between various scan types?

Senior

Answer

The optimizer evaluates selectivity, table size, and cost. Sequential scans are chosen for large portions of tables; bitmap scans help combine multiple indexes.
Quick Summary: PostgreSQL scan type selection: SeqScan (scan entire table - good when retrieving large % of rows), IndexScan (index lookup + heap fetch - good for selective queries), IndexOnlyScan (index only, no heap - requires covering index + all-visible pages), BitmapIndexScan + BitmapHeapScan (multiple index conditions combined, efficient for moderate selectivity). Planner chooses based on cost estimates - tune random_page_cost for SSDs.
Permalink
Q95:

What are temp buffers and how do they differ from shared buffers?

Senior

Answer

Temp buffers store session-local temporary table data and bypass shared buffers, affecting performance based on temp_buffers.
Quick Summary: Temp buffers are session-local buffers for temporary tables (SET temp_buffers). Unlike shared_buffers (shared across all connections), temp buffers are private to each session. Default is 8MB per session. Increase if your session creates large temp tables. Shared buffers are allocated at startup from shared memory; temp buffers are allocated per session on demand from the session's heap.
Permalink
Q96:

How does PostgreSQL execute parallel queries?

Senior

Answer

Parallel workers perform scans, joins, and aggregates. Parallelism depends on cost thresholds, table size, and query shape.
Quick Summary: Parallel queries split a sequential scan or aggregation across multiple worker processes. Controlled by max_parallel_workers_per_gather, max_parallel_workers, and min_parallel_table_scan_size. Workers each scan a portion of the table, results gathered and merged. Benefits for large analytical queries. Not useful for OLTP (queries return quickly, parallelism overhead exceeds benefit). Check EXPLAIN for "Gather" or "Gather Merge" nodes.
Permalink
Q97:

How should you design table partitioning for large datasets?

Senior

Answer

Partition keys should have high cardinality. Range and hash partitioning distribute data and enable pruning for faster queries.
Quick Summary: Table partitioning for large datasets: use declarative partitioning (PostgreSQL 10+). Partition by range (date/timestamp for time-series), list (region, status), or hash (for even distribution). Partition pruning automatically skips irrelevant partitions. Attach older partitions to cheaper tablespaces. Detach and archive old partitions without locking. Each partition is its own physical table with independent indexes.
Permalink
Q98:

What is the role of FDW (Foreign Data Wrapper) in enterprise systems?

Senior

Answer

FDWs enable PostgreSQL to query external databases or APIs, supporting federated queries and hybrid architectures.
Quick Summary: FDW in enterprise: query distributed data without ETL. postgres_fdw connects to remote PostgreSQL. Use for: reporting across multiple databases, migrating data incrementally, federated queries across regions, or accessing legacy systems. Push-down optimization: the FDW sends WHERE conditions to the remote server to filter data before transmission. Enterprise systems use FDW for data virtualization layers.
Permalink
Q99:

How does PostgreSQL optimize JSONB querying?

Senior

Answer

GIN indexes accelerate JSONB containment and key-based queries. Operators like @> and ? support flexible search patterns.
Quick Summary: JSONB optimization: GIN index supports key existence (?), containment (@>), and path queries. jsonb_path_ops operator class for containment only - smaller, faster index. Partial indexes on specific JSONB keys: CREATE INDEX ON events ((data->>"type")) WHERE data->>"type" IS NOT NULL. Avoid querying deeply nested paths without indexes. Consider extracting frequently queried JSONB fields into regular columns.
Permalink
Q100:

How are materialized view refresh strategies handled?

Senior

Answer

Materialized views require manual refresh. Concurrent refresh avoids locking but is slower. Used for analytics and reporting.
Quick Summary: Materialized view refresh strategies: REFRESH MATERIALIZED VIEW blocks all reads and writes during refresh. REFRESH MATERIALIZED VIEW CONCURRENTLY allows reads during refresh (requires unique index, takes longer). Automate refresh with pg_cron extension or external scheduler. For near-real-time data: trigger-based incremental refresh or logical replication to maintain the view. Choose refresh frequency based on staleness tolerance.
Permalink
Q101:

What is TOAST and how does PostgreSQL store large values?

Senior

Answer

TOAST stores oversized values externally, compressing or splitting them to keep rows small and efficient.
Quick Summary: TOAST (The Oversized-Attribute Storage Technique) handles values larger than ~2KB. Large values are automatically compressed and/or stored in a separate TOAST table, with a pointer in the main row. Multiple storage strategies: PLAIN (no compression/toast), EXTENDED (compress first, then toast), EXTERNAL (toast without compression - fast retrieval), MAIN (compress, avoid toasting). Transparent to queries but affects performance for large columns.
Permalink
Q102:

How does PostgreSQL handle high write throughput?

Senior

Answer

Use partitioning, tuned WAL settings, more autovacuum workers, and unlogged tables. Scaling writes often requires sharding or logical replication.
Quick Summary: High write throughput in PostgreSQL: use COPY instead of INSERT for bulk loads (10-100x faster). Batch inserts with multi-row VALUES. Disable synchronous_commit for non-critical writes. Use unlogged tables for temp data (no WAL, much faster). Partition tables to distribute writes. Tune autovacuum to keep up with deletes/updates. Use connection pooling (PgBouncer) to reduce connection overhead. SSDs dramatically improve write throughput.
Permalink
Q103:

What is pg_repack and why is it used?

Senior

Answer

pg_repack rebuilds tables and indexes without blocking writes, unlike VACUUM FULL, making it ideal for production cleanup.
Quick Summary: pg_repack reorganizes tables and indexes online without holding an exclusive lock for most of the operation. Rebuilds the table in a new heap, migrates data in batches, tracks changes via triggers, then does a brief swap at the end. Reclaims bloat (like VACUUM FULL) without prolonged downtime. Use on bloated tables that you can't afford to lock. Requires the extension installed in the database.
Permalink
Q104:

How do you handle long-running transactions?

Senior

Answer

Long transactions block vacuum cleanup and cause bloat. Use timeouts, break transactions into smaller pieces, and monitor stale sessions.
Quick Summary: Long-running transactions prevent VACUUM from reclaiming dead tuples (any tuple that might be visible to the long transaction is kept). They also hold locks and cause XID age to grow. Monitor with pg_stat_activity (duration column). Set statement_timeout and lock_timeout to kill stuck queries. Set idle_in_transaction_session_timeout to kill connections stuck in idle-in-transaction state.
Permalink
Q105:

What is parallel query skew and how do you detect it?

Senior

Answer

Parallel skew occurs when workers process uneven workloads. Detected via EXPLAIN with parallel details and fixed with better partitioning or query rewrites.
Quick Summary: Parallel query skew: one worker gets much more work than others, becoming the bottleneck. Happens with data skew (some partition ranges have far more rows), poor parallelization of certain operations, or when parallel workers can't evenly split the work. Detect with EXPLAIN ANALYZE - look for workers with very different row counts. Fix: use hash partitioning, improve statistics, or accept that parallelism doesn't help for that query.
Permalink
Q106:

How does PostgreSQL achieve Serializable isolation without using strict two-phase locking?

Expert

Answer

PostgreSQL uses Serializable Snapshot Isolation (SSI), which tracks read-write dependencies. If a dangerous pattern occurs, it aborts one transaction to maintain serializability. This avoids heavy 2PL locking while preserving correctness.
Quick Summary: SSI tracks read/write dependencies between transactions using predicate locks. Instead of locking rows before reading, it records what each transaction reads. At commit time, PostgreSQL checks if the commit would create a cycle in the dependency graph (non-serializable schedule). If so, one transaction is aborted with "could not serialize access." This is safer than two-phase locking and has no deadlocks - just retryable serialization failures.
Permalink
Q107:

How do you design a PostgreSQL cluster that supports multi-region writes with minimal conflict?

Expert

Answer

Design uses logical replication with conflict-free partitioning where each region owns a separate writable key range. Use region-prefixed keys or GUID variants to avoid collisions. Tools like BDR support conflict handlers for true multi-master write safety.
Quick Summary: Multi-region writes with minimal conflict: use a primary-primary setup via Patroni + BDR (Bi-Directional Replication) or Citus. Shard data by region so each region owns its data (fewer cross-region conflicts). Use conflict resolution rules (last-write-wins or application-defined). Accept higher latency for cross-region transactions. CockroachDB and YugabyteDB are PostgreSQL-compatible alternatives built for this.
Permalink
Q108:

How do you troubleshoot severe performance regression caused by the PostgreSQL planner choosing a wrong execution path?

Expert

Answer

Analyze EXPLAIN ANALYZE output to compare estimated vs actual row counts. Refresh statistics, increase default_statistics_target, create extended stats, add selective indexes, or rewrite queries. Correcting cardinality estimation is the core fix.
Quick Summary: Troubleshoot plan regression: check if statistics are stale (ANALYZE), compare plans with pg_stat_statements before/after. Run EXPLAIN with and without indexes. Check if a new index was added (changed join order). Reset plan cache after schema changes. Set enable_seqscan=off to force index usage for testing. Use pg_hint_plan extension to force specific plans while debugging. Check for correlated columns needing multi-column stats.
Permalink
Q109:

How does PostgreSQL internally manage WAL syncing for extreme durability requirements?

Expert

Answer

PostgreSQL flushes WAL buffers using wal_writer and synchronous_commit settings. Extreme durability requires synchronous_commit=on, full_page_writes=on, fast NVMe storage, tuned WAL-segmentation, and aggressive archiving.
Quick Summary: WAL durability tuning: synchronous_commit=on ensures WAL flushed to disk before commit. wal_sync_method: fdatasync (default Linux), fsync, open_sync. Use hardware with battery-backed write cache for better performance without compromising durability. synchronous_standby_names with remote_apply ensures WAL applied on standby before commit. For extreme durability: synchronous replication + local journal sync = RPO of effectively 0.
Permalink
Q110:

How do you handle massive table bloat in mission-critical tables without downtime?

Expert

Answer

Use pg_repack to rebuild tables and indexes online with shadow copies. Alternatively rebuild tables on replicas via logical replication and fail over. VACUUM FULL is avoided due to locking.
Quick Summary: Handle massive table bloat without downtime: pg_repack rebuilds the table online. CREATE new table, migrate data in batches via background job, swap with original using a brief exclusive lock. Partition the table and detach old bloated partitions. VACUUM FULL on a replica, then promote. Partition pruning prevents scanning bloated old partitions. Prevention: tune autovacuum aggressively for high-write tables.
Permalink
Q111:

How would you architect PostgreSQL for real-time analytics without overwhelming OLTP performance?

Expert

Answer

Use dual-layer architecture: OLTP writes to PostgreSQL, while logical decoding streams WAL changes to OLAP systems. Use incremental materialized views, partitioning, and move historical data to cold storage to isolate workloads.
Quick Summary: PostgreSQL for real-time analytics without impacting OLTP: read from streaming replication standby (dedicated analytics replica). Use materialized views refreshed incrementally. TimescaleDB extension for time-series analytics. Logical replication to a separate analytics database (Redshift, ClickHouse). Connection pooling (PgBouncer) to isolate OLTP connections. Partition tables so analytics scans only relevant partitions.
Permalink
Q112:

How do you detect and fix snapshot-too-old errors in PostgreSQL?

Expert

Answer

Snapshot-too-old occurs when required row versions are vacuumed. Fix by avoiding long-running transactions, tuning autovacuum aggressively, running analytics on replicas, and monitoring vacuum lag via system views.
Quick Summary: Snapshot-too-old error (PostgreSQL 9.6+): when old_snapshot_threshold is set and a query's snapshot is too old to reconstruct MVCC visibility, it fails. Caused by long-running queries combined with high data churn. Fix: reduce long-running transaction duration, increase old_snapshot_threshold (careful: uses more memory), or restructure analytics queries to run faster. Monitor with pg_stat_activity for long snapshots.
Permalink
Q113:

What is logical decoding and how is it used for event-driven PostgreSQL systems?

Expert

Answer

Logical decoding reads WAL and converts changes into logical events using plugins like pgoutput or wal2json. Used for CDC, microservice event sourcing, real-time pipelines, and incremental ETL.
Quick Summary: Logical decoding decodes WAL changes into a logical stream of row-level changes (INSERT/UPDATE/DELETE). Used by: logical replication, change data capture (CDC), audit logging. Output plugins (pgoutput for built-in logical replication, wal2json for JSON output, decoderbufs for Protobuf) format the stream. Consumers (Debezium, Kafka Connect) subscribe to capture changes for downstream systems without polling.
Permalink
Q114:

How do you design partitioning for trillion-row datasets in PostgreSQL?

Expert

Answer

Use multi-level RANGE+HASH partitioning, align queries to pruning keys, distribute partitions across tablespaces, use BRIN indexes, automate partition lifecycle, and separate hot vs cold storage.
Quick Summary: Trillion-row partitioning: use declarative range partitioning on the primary access dimension (time, user_id). Use sub-partitioning (nested partition levels: partition by year, sub-partition by month). Detach old partitions to archive storage. Use partition-wise joins and aggregates. Index each partition independently. Keep partition count manageable (hundreds, not thousands). Use pg_partman extension to automate partition management.
Permalink
Q115:

How do you ensure zero-data-loss failover (RPO=0) in PostgreSQL?

Expert

Answer

Enable synchronous replication with quorum commit so primary waits for WAL confirmation from replicas. Use low-latency networks, fast disks, aggressive lag monitoring, and tuned synchronous_standby_names settings to guarantee RPO=0.
Quick Summary: Zero-data-loss failover (RPO=0): use synchronous replication (synchronous_standby_names = "FIRST 1 (standby)") so every commit is confirmed on the standby before returning. Combine with Patroni for automated failover (promotes standby, reconfigures clients). Use synchronous_commit=remote_apply for the strictest guarantee. Monitor replication lag - alert if it exceeds 0. Test failover regularly to verify zero data loss.
Permalink

Curated Sets for PostgreSQL

No curated sets yet. Group questions into collections from the admin panel to feature them here.

People Also Ask - Related PostgreSQL Questions

What is PostgreSQL?
What is a database cluster in PostgreSQL?
What is a schema in PostgreSQL?
What is the difference between a table and a view?
What is a primary key in PostgreSQL?
What is a foreign key?
Ready to level up? Start Practice