skip to content

PostgreSQL

2 roadmaps297 questionsupdated

PostgreSQL is the default open-source relational database, and interviews probe what is its own: MVCC and vacuum, index families, WAL replication and a rich type system.

on this pageshow

guide

overview

~1 min

PostgreSQL is an open-source relational database and, for many teams, the default choice for a new backend. Interviews on it move quickly past generic SQL to what this engine does its own way: it keeps old row versions instead of overwriting them and cleans them up later, logs every change before touching the data files, and lets extensions add types, index methods and whole workloads. A strong answer names the mechanism, the symptom it causes in production, and the setting or command that controls it. The hub splits into the engine, the data and the operator's work. [Engine anatomy](/topics/db-postgresql-architecture) covers processes, pages and how a cluster holds databases and schemas. [MVCC and locking](/topics/db-postgresql-mvcc-transactions) and [vacuum and freezing](/topics/db-postgresql-vacuum) are two halves of one design: versions created on write, reclaimed afterwards. [The write-ahead log and replicas](/topics/db-postgresql-replication-wal) explains durability, standbys, slots and logical decoding. On the data side sit [column types](/topics/db-postgresql-sql-datatypes), the [SQL dialect](/topics/db-postgresql-sql-dialect), [indexes and query planning](/topics/db-postgresql-indexing-query-planning), [multi-model workloads](/topics/db-postgresql-jsonb-extensions) such as documents, search, vectors and messaging, [server-side code](/topics/db-postgresql-server-programming) and [extensibility](/topics/db-postgresql-extensibility). The operator's sections are [server tuning](/topics/db-postgresql-performance-tuning), [monitoring and diagnostics](/topics/db-postgresql-monitoring), [backup and restore](/topics/db-postgresql-backup), [database security](/topics/db-postgresql-security) and the [release lifecycle](/topics/db-postgresql-upgrades). Junior rounds check the model: what a column type really stores, which isolation level you get by default, what a plain vacuum gives back. Middle and senior rounds hand you an `EXPLAIN ANALYZE` plan, a bloated table, a lagging replica or a migration stuck behind a lock, and expect you to reason from the internals to a fix and to say what that fix costs. Start with the engine's anatomy and MVCC, then vacuum and the WAL. Indexing, tuning, replication and backup answers all lean on those four; the dialect, the types and the extensions can be learned in any order once they are in place.

primer

### An UPDATE writes a new row PostgreSQL does not change a row where it sits. Each UPDATE adds a new version of the row and each DELETE only marks a version as ended; every transaction then sees the versions its **snapshot** allows. That is why plain reads and writes do not block each other, and also why tables grow under update-heavy load, why indexes can point at versions nobody can see any more, and why so much of this hub is about cleaning up. Locks still matter when two writers collide, and the isolation level decides whether a collision means waiting or a serialization failure the application has to retry. ### Cleanup is deferred, not optional Space held by dead versions is reclaimed by **vacuum**, normally run in the background by autovacuum. Vacuum also **freezes** old rows, because transaction IDs are 32 bits wide and would otherwise wrap around. Anything that keeps an old snapshot alive, such as a long transaction or a session left idle inside one, stops vacuum from removing what that snapshot might still need. Bloat, wraparound warnings and index-only scans that keep visiting the table are often one story told from different sides. ### One log feeds everything downstream Every change is written to the **write-ahead log** before the data files. Crash recovery replays it, standbys stream it, replication slots hold on to it, logical decoding turns it into row changes, and point-in-time recovery rebuilds from its archive. Questions about durability, replicas, change data capture and backups are questions about who reads the WAL, how far behind each reader is, and what the primary must keep for them. ### A connection is a process The server forks a separate operating-system process for every client connection, and each carries its own memory and per-operation budgets. Connection count is therefore a resource decision rather than a free setting, and poolers, timeouts and modest limits follow from it. Shared memory, mostly the buffer cache, sits beside those processes and is sized at server start. ### The planner guesses from statistics Queries are planned by a cost-based optimizer that estimates row counts from statistics gathered by ANALYZE. Most bad plans trace to a wrong estimate rather than a missing feature, which is why interviewers hand you `EXPLAIN ANALYZE` output and ask where estimate and reality part ways. Index choice follows the same logic: B-tree is the default, and each other access method exists for a particular family of operators. ### Types and extensions are part of the design Choosing `timestamptz`, `numeric`, arrays, ranges or `jsonb` on purpose is an expected skill, and extensions can add new types, operators, index methods and background workers. Much of the senior conversation is about when PostgreSQL can absorb a workload such as documents, search, vectors or a job queue, and when a second system earns its operational cost.

Cluster
One PostgreSQL server instance and its data directory, holding many databases. Roles and tablespaces are shared by all of them; a session connects to one database.
Tuple
PostgreSQL's word for one physical version of a row on a heap page. An UPDATE produces a new tuple; the old one stays until vacuum removes it.
Snapshot
The record of which transactions count as committed for a statement or transaction. It decides which tuple versions are visible to that reader.
Dead tuple
A row version no current or future snapshot can see. It still occupies space in the table and its indexes until vacuum reclaims it.
Autovacuum
The background launcher and workers that vacuum and analyze tables automatically once their changes pass thresholds, which can be overridden per table.
Freezing
Vacuum marking old tuples as visible to every transaction, so their creating transaction ID no longer needs comparing and wraparound cannot hide them.
Transaction ID wraparound
The hazard created by 32-bit transaction IDs: unfrozen old rows would appear to lie in the future. The server forces vacuuming and finally refuses new transaction IDs to prevent it.
Visibility map
A per-table bitmap marking pages whose tuples are all visible, and pages that are all frozen. Vacuum uses it to skip pages; index-only scans use it to skip heap visits.
HOT update
A heap-only tuple update: the new version stays on the same page and no index entries are added. It needs free space on that page and no change to a column a non-BRIN index uses.
Write-ahead log (WAL)
The sequential record of every change, flushed before the data pages it describes. It drives crash recovery, streaming replication, logical decoding and point-in-time recovery.
Checkpoint
The point at which all dirty pages are written to the data files, so crash recovery replays only WAL from after it. Frequent checkpoints cost I/O; rare ones lengthen recovery.
Replication slot
A primary-side marker of how far one consumer has read, which makes the server keep the WAL that consumer still needs, even while it is disconnected.
Hot standby
A replica that continuously replays the primary's WAL while serving read-only queries; it can be promoted to become the new primary.
Planner statistics
Per-column summaries gathered by ANALYZE, such as most common values, histograms and null fraction, that the planner uses to estimate how many rows each step returns.
pg_hba.conf
The host-based authentication file checked for every connection attempt; its first matching line picks the authentication method by connection type, database, user and address.

Follow one write through the server. A client connects, `pg_hba.conf` decides how it must authenticate, and the postmaster forks a backend for the session. The backend parses and plans the statement, reads the pages it needs into shared buffers, and writes a new tuple version for each changed row, recording every change in the WAL. At COMMIT it waits, by default, for that WAL to be flushed; the changed pages themselves reach the data files later, written mostly by the background writer and the checkpointer. The WAL is what makes that delay safe. Background processes then keep working on the same write: - **Autovacuum** finds the dead version left behind, reclaims its space, refreshes statistics and freezes old rows. - **WAL senders** stream the change to standbys and logical subscribers, and a replication slot keeps the WAL until its consumer confirms it. - **WAL archiving** copies finished segments away; with a base backup they make point-in-time recovery possible. The other sections attach to that path. Security acts at three points: `pg_hba.conf` before a session exists, role privileges on every statement, and row-level policies inside the query. Server tuning sets the memory and I/O budgets each step works within. Monitoring reads the counters each step leaves behind. Server-side functions and triggers run inside the backend, in the transaction of the statement that called them. The release lifecycle decides when the whole stack moves to a new major version, and by which route. ```sql -- one UPDATE, as three sections see it UPDATE orders SET status = 'shipped' WHERE id = 42; -- MVCC and vacuum: the old version becomes a dead tuple SELECT n_dead_tup, n_tup_hot_upd, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'orders'; -- WAL: every consumer is measured against this position SELECT pg_current_wal_lsn(); SELECT slot_name, active, restart_lsn FROM pg_replication_slots; ``` The same change shows up in each section: a dead version waiting for vacuum, WAL waiting for every slot, a statistic waiting for the next ANALYZE. Reading those views side by side is often the fastest route from a symptom to the section that explains it.

  1. Engine Anatomy →

    Processes, shared memory, pages and the cluster, database and schema layers that every other answer quietly assumes.

  2. MVCC & Locking →

    Row versions, snapshots, isolation levels and locks: the model behind blocking, retries and most concurrency questions.

  3. Vacuum & Freezing →

    The other half of MVCC: how dead versions are reclaimed, why freezing exists and where bloat comes from.

  4. Indexes & Query Planning →

    Index access methods and the cost-based planner, learned by reading EXPLAIN ANALYZE output against real queries.

  5. Write-Ahead Log & Replicas →

    How one log provides durability, standbys, slots and logical decoding, and what each consumer costs the primary.

  6. Server Tuning →

    Memory, I/O and connection settings, which only make sense once the processes, MVCC and the WAL are clear.

  • Saying an UPDATE modifies the row in place: it writes a new version, which is why update-heavy tables bloat and why vacuum exists at all.

  • Treating VACUUM FULL as routine maintenance: it rewrites the table under a lock that blocks reads and writes; tuning autovacuum is the usual answer.

  • Turning autovacuum down to save I/O, then meeting bloat and wraparound warnings; large tables usually need more aggressive per-table settings, not fewer runs.

  • Leaving sessions idle in transaction: they keep holding their locks and their old snapshot, which stops vacuum from removing rows that died after it.

  • Sizing work_mem as if it were per connection, when each sort or hash step can claim its own budget across many sessions at once.

  • Raising max_connections to absorb more clients instead of pooling, when each connection is a whole process with its own memory.

  • Leaving an unused replication slot in place: the primary keeps every WAL segment the slot still needs until the disk fills.

  • Running a plain CREATE INDEX on a busy production table and blocking its writes for the whole build; see the concurrent build.

  • Calling a nightly physical copy a recovery plan: without archived WAL it restores only to the moment that copy finished, not to the minute before an incident.

This guide assumes PostgreSQL 18. A new major version ships every year and each is supported for five years, so real fleets span several majors and interviewers often ask what changed along the way: - **10** introduced declarative partitioning, built-in logical replication with publications and subscriptions, and identity columns. - **12** stopped treating every CTE as an optimization fence: a side-effect-free, non-recursive CTE referenced once is now inlined unless marked `MATERIALIZED`. It also added stored generated columns. - **15** added `MERGE` and took away every role's default right to create objects in the `public` schema of a new database. - **17** added incremental base backups, reassembled with `pg_combinebackup`, and `RETURNING` on `MERGE`. - **18** added an asynchronous I/O subsystem, the `uuidv7()` function, virtual generated columns as the default kind, `WITHOUT OVERLAPS` temporal keys, the swap transfer mode of `pg_upgrade`, and deprecated MD5 password authentication. When an answer depends on a default, such as how a CTE is planned, who may create objects in `public` or how a generated column is stored, say which version you are describing. Many production servers are still several majors behind, and naming the version is part of a good answer.

PostgreSQL is weighed most often against MySQL and MariaDB, and against Oracle Database and SQL Server where licensing and existing estates decide. Both open-source engines run typical transactional workloads well; PostgreSQL is usually picked for its type system, extensions and SQL coverage, the alternatives often for team familiarity and existing tooling. Many managed and distributed services speak the PostgreSQL wire protocol or are built from its code, so knowledge of the core carries over, though not every extension or setting does. Around the core sit tools an interviewer expects you to place. A connection pooler such as PgBouncer is standard, because every connection is a process. PostGIS adds geospatial types and indexes, TimescaleDB time-series storage, Citus sharding across nodes, and pgvector embeddings. Change data capture tools such as Debezium read logical decoding and publish row changes to Kafka. The recurring design question is consolidation. JSONB competes with document stores such as MongoDB, built-in full-text search with Elasticsearch or OpenSearch, pgvector with dedicated vector databases, and LISTEN/NOTIFY with a real message broker. Keeping the workload in PostgreSQL buys one system to run and transactions shared with relational data; a dedicated store buys scale and specialised features. [Multi-Model Workloads](/topics/db-postgresql-jsonb-extensions) works through where that line falls.

explore

report an issue with this guide →

questions

297 · 15 sections

In PostgreSQL, how do a cluster, a database and a schema relate, and which objects belong to the whole cluster rather than one database?

level: juniorimportance: must knowfreq 72%
basics
~20 s

A PostgreSQL cluster is one server instance and data directory holding many databases; each database holds schemas, which hold tables and functions. Roles, databases and tablespaces are cluster-wide, and every connection is bound to exactly one database.

open as a page

In PostgreSQL, what do initdb, pg_ctl and the postgres server program each do across a cluster's life, from creation to shutdown?

level: juniorimportance: must knowfreq 55%
basics
~20 s

initdb creates a cluster's data directory once; postgres is the server itself, whose first process is the postmaster; pg_ctl is a wrapper that starts, stops, restarts, reloads and reports on that server, tracking it through postmaster.pid.

open as a page

On a PostgreSQL 18 server, ps lists many postgres processes: what is the postmaster, and what do the other processes do?

level: juniorimportance: must knowfreq 72%
basics
~20 s

The postmaster is PostgreSQL's supervisor: it creates shared memory, listens for connections and forks one backend process per client. The other processes are helpers such as the checkpointer, background writer, walwriter and autovacuum launcher, plus background workers.

open as a page

In PostgreSQL, why does a query joining tables from two databases in the same cluster fail, and what can you do instead?

level: middleimportance: must knowfreq 58%
basics
~20 s

Each PostgreSQL backend is attached to one database and reads only its catalogs, so naming another database's table raises "cross-database references are not implemented" (0A000). Put shared data in schemas of one database, or use postgres_fdw, dblink or the application.

open as a page

In PostgreSQL, what do pg_ctl stop's smart, fast and immediate modes do, which is the default, and when would you use each?

level: middleimportance: must knowfreq 62%
basics
~20 s

Smart refuses new connections and waits for every session to end; fast, the default, rolls back open transactions, disconnects everyone and writes a shutdown checkpoint; immediate kills all processes without a checkpoint, so the next start runs crash recovery.

open as a page

In PostgreSQL, what is the default transaction isolation level, how do you choose another, and what do you get if you request READ UNCOMMITTED?

level: juniorimportance: must knowfreq 78%
basics
~20 s

PostgreSQL defaults to READ COMMITTED. You pick a level per transaction with BEGIN ISOLATION LEVEL or SET TRANSACTION before its first query, or change default_transaction_isolation; READ UNCOMMITTED is accepted but behaves exactly like READ COMMITTED.

open as a page

In PostgreSQL, does an UPDATE overwrite the row in place, and what happens to a table's size after you update every row?

level: juniorimportance: must knowfreq 62%
basics
~20 s

No. A PostgreSQL UPDATE writes a complete new row version and stamps the old one's xmax with the updating transaction's ID; the old version stays until VACUUM reclaims it, so updating every row can roughly double the table.

open as a page

Under PostgreSQL's REPEATABLE READ, what happens when an UPDATE targets a row changed and committed after the snapshot, and what must the application do?

level: middleimportance: must knowfreq 64%
basics
~20 s

It waits for the other writer; if that transaction commits an update or delete of the row, the UPDATE fails with could not serialize access due to concurrent update, SQLSTATE 40001. The application must roll back and rerun the whole transaction.

open as a page

In PostgreSQL, how should workers of a transactional-email queue claim rows with SKIP LOCKED, and how does that differ from NOWAIT?

level: middleimportance: must knowfreq 66%
basics
~20 s

Each worker runs a SELECT ... FOR NO KEY UPDATE SKIP LOCKED LIMIT n that locks only rows no other transaction holds, so workers get disjoint batches without waiting. NOWAIT instead raises SQLSTATE 55P03 the moment a selected row is locked.

open as a page

In PostgreSQL, which table-level lock do SELECT, SELECT ... FOR UPDATE and UPDATE each take, and which mode alone blocks a plain SELECT?

level: juniorimportance: should knowfreq 52%
basics
~20 s

A plain SELECT takes ACCESS SHARE, SELECT ... FOR UPDATE takes ROW SHARE and UPDATE takes ROW EXCLUSIVE, and none of the three conflict. Only ACCESS EXCLUSIVE, taken by DROP TABLE, TRUNCATE and many ALTER TABLE forms, blocks a plain SELECT.

open as a page

In PostgreSQL, what does a plain VACUUM do with dead tuples, and why does the table file usually stay the same size afterwards?

level: juniorimportance: must knowfreq 78%
basics
~20 s

Plain VACUUM removes dead tuples no transaction can still see, records the freed room in the free space map for new rows, and sets visibility map bits. It returns disk space only when empty pages sit at the table's end.

open as a page

In PostgreSQL, what does VACUUM FULL do that plain VACUUM does not, and what does it cost while it runs?

level: juniorimportance: must knowfreq 72%
basics
~20 s

Plain VACUUM makes dead-row space reusable inside the table's existing files while reads and writes continue. VACUUM FULL writes a compacted new copy of the table and rebuilds its indexes, returning space to the OS, but blocks all access and needs extra disk.

open as a page

In PostgreSQL, why do 32-bit transaction IDs make freezing necessary, and what does VACUUM's freezing actually change on a row?

level: juniorimportance: must knowfreq 72%
basics
~20 s

PostgreSQL compares 32-bit transaction IDs modulo 2^32, so a row's inserting XID looks past for only about two billion transactions. VACUUM freezes old rows, setting a header flag that makes them visible to every future transaction.

open as a page

In PostgreSQL 18, what thresholds make autovacuum pick a table for VACUUM or ANALYZE, and why do the defaults fail large tables?

level: middleimportance: must knowfreq 68%
basics
~20 s

Autovacuum vacuums a table once dead tuples pass 50 + 0.2 × reltuples (capped at 100 million in PostgreSQL 18) or inserts pass a similar bar, and analyzes after 50 + 0.1 × reltuples changes, so big tables wait for millions of dead rows.

open as a page

A PostgreSQL payroll ledger table doubled on disk after a one-off backfill and must stay writable during payroll runs; how would you shrink it with pg_repack, and what can go wrong?

level: seniorimportance: must knowfreq 52%
basics
~20 s

pg_repack copies the table while a trigger logs concurrent writes, builds the copy's indexes, replays the log, then swaps the copy in under a brief ACCESS EXCLUSIVE lock. It needs a primary or unique NOT NULL key and about double the disk.

open as a page

In PostgreSQL, what index does a plain CREATE INDEX build, and which WHERE and ORDER BY clauses on that column can it serve?

level: juniorimportance: must knowfreq 72%
basics
~20 s

A plain CREATE INDEX builds a B-tree, PostgreSQL's default method. It serves <, <=, =, >=, > and combinations such as BETWEEN and IN, IS NULL and IS NOT NULL, and ORDER BY on the column in either direction.

open as a page

In PostgreSQL, what does EXPLAIN ANALYZE do to an UPDATE or DELETE, and how do you measure one without changing your data?

level: juniorimportance: must knowfreq 64%
basics
~20 s

EXPLAIN ANALYZE really executes an UPDATE or DELETE and only discards its output, so in autocommit the change commits. Wrap it in BEGIN and ROLLBACK: the data change is undone, but the locks, WAL and any sequence values drawn are not.

open as a page

In PostgreSQL, beyond the default B-tree, which index access methods ship in core, and what kind of query is each one built for?

level: juniorimportance: must knowfreq 74%
basics
~10 s

Core PostgreSQL adds hash (equality only), GIN (values holding many keys: arrays, jsonb, tsvector), GiST and SP-GiST (geometry, ranges and nearest-neighbour ordering with <->) and BRIN (tiny per-block-range summaries for huge, physically ordered tables).

open as a page

In PostgreSQL, what does the ANALYZE command do, how does it differ from VACUUM and EXPLAIN ANALYZE, and when should you run it by hand?

level: juniorimportance: must knowfreq 66%
basics
~20 s

ANALYZE samples a table and rewrites its planner statistics in pg_statistic (shown by pg_stats). Unlike VACUUM it reclaims no space; unlike EXPLAIN ANALYZE it runs no query. Run it by hand after bulk loads, on temporary tables and after changing statistics settings.

open as a page

In a PostgreSQL database with an en_US.UTF-8 collation, why does title LIKE 'Dune%' skip the B-tree index on title, and what fixes it?

level: middleimportance: must knowfreq 60%
basics
~10 s

PostgreSQL turns an anchored LIKE prefix into a byte-wise range, which only fits an index ordered byte by byte. Under en_US.UTF-8 the default index sorts linguistically, so build it with text_pattern_ops or COLLATE "C".

open as a page

In PostgreSQL, how do text, varchar(n) and char(n) differ in storage and behaviour, and which should a new column use?

level: juniorimportance: must knowfreq 72%
basics
~10 s

PostgreSQL stores text, varchar(n) and char(n) in the same variable-length format with no speed difference; varchar(n) only adds a length check, and char(n) pads with blanks, making it usually the slowest. Default to text.

open as a page

In PostgreSQL, what does a timestamptz column actually store, and how does the session TimeZone setting change what you read back?

level: juniorimportance: must knowfreq 76%
basics
~10 s

A PostgreSQL timestamptz stores one UTC instant and discards the zone you wrote. Input without an offset is read in the session's TimeZone, and every value is displayed converted to that TimeZone.

open as a page

In PostgreSQL 18, how do the libc, ICU and builtin collation providers differ, and which would you pick for a new UTF8 cluster?

level: middleimportance: must knowfreq 50%
basics
~20 s

libc borrows the OS C library's locales; ICU uses the ICU library and allows tailored and nondeterministic collations; builtin (17+) sorts by code point with no external library. A sound pick: builtin default, ICU on columns needing linguistic order.

open as a page

In PostgreSQL, how do you find recipes whose text[] tags column holds one given tag, all of several tags, or any of them?

level: middleimportance: must knowfreq 63%
basics
~20 s

Use 'vegan' = ANY(tags) for one tag, tags @> ARRAY['vegan','quick'] for all of a list, and tags && ARRAY['vegan','quick'] for any of it. Containment and overlap ignore order and duplicates; ANY and ALL follow SQL NULL rules.

open as a page

In PostgreSQL, how do you add a new value to an enum type like order_status, and what can you not do to it afterwards?

level: middleimportance: must knowfreq 56%
basics
~20 s

ALTER TYPE order_status ADD VALUE 'refunded' adds a label, optionally BEFORE or AFTER another, without rewriting any table; inside a transaction block it is unusable until commit. Labels can be renamed but not removed or reordered.

open as a page

In PostgreSQL, how does COPY ... FROM '/path/file.csv' differ from COPY ... FROM STDIN, and what privilege does each one need?

level: juniorimportance: must knowfreq 70%
basics
~20 s

COPY FROM a file name makes the PostgreSQL server open the file on the database host as its own OS user, needing superuser or pg_read_server_files; COPY FROM STDIN streams rows over the client connection and needs only INSERT.

open as a page

In PostgreSQL, why is GENERATED AS IDENTITY preferred over serial or bigserial for a new table's primary key?

level: juniorimportance: must knowfreq 72%
basics
~20 s

serial is shorthand for a separate sequence plus a nextval() default; an identity column owns an internal sequence. Identity needs no sequence grant, copies cleanly with LIKE, can reject hand-written ids, and is the SQL-standard form.

open as a page

In PostgreSQL, which rows does FOR VALUES FROM ('2026-03-01') TO ('2026-04-01') accept, and what happens to a row no partition accepts?

level: juniorimportance: must knowfreq 58%
basics
~20 s

FROM is inclusive and TO is exclusive, so the partition holds 2026-03-01 up to but not including 2026-04-01. A row that fits no partition fails with 'no partition of relation found for row' unless a DEFAULT partition exists.

open as a page

In PostgreSQL, how do ILIKE, ~ and ~* differ from LIKE when you search stock symbols or company names?

level: juniorimportance: must knowfreq 58%
basics
~20 s

ILIKE is a case-insensitive LIKE that must match the whole string with % and _ wildcards; ~ and ~* are POSIX regular-expression matches, case-sensitive and case-insensitive, that match anywhere unless anchored with ^ and $.

open as a page

In PostgreSQL, how does INSERT ... ON CONFLICT DO NOTHING differ from ON CONFLICT DO UPDATE, and what does EXCLUDED refer to?

level: juniorimportance: must knowfreq 80%
basics
~10 s

ON CONFLICT DO NOTHING skips any proposed row that collides with an arbiter unique index or constraint; DO UPDATE instead updates the existing row, and EXCLUDED names the row that was proposed for insertion.

open as a page

In PostgreSQL, what is the difference between the json and jsonb column types, and which one should a new column normally use?

level: juniorimportance: must knowfreq 78%
basics
~20 s

json stores the exact input text and reparses it on every access; jsonb stores a parsed binary form that drops whitespace, key order and duplicate keys but is faster to query, comparable and indexable. New columns should normally use jsonb.

open as a page

In PostgreSQL full-text search, what are tsvector and tsquery, and what does the @@ operator check between them?

level: juniorimportance: must knowfreq 62%
basics
~20 s

A tsvector is a document reduced to sorted, normalised lexemes with their positions; a tsquery is normalised lexemes joined by &, |, ! and <->. The @@ operator returns true when the tsvector satisfies the tsquery's expression.

open as a page

In pgvector, what do the <->, <=> and <#> operators compute, and why must an index's operator class match the operator?

level: juniorimportance: must knowfreq 62%
basics
~20 s

pgvector's <-> is L2 distance, <=> cosine distance and <#> negative inner product. An HNSW or IVFFlat index is built with one operator class, such as vector_cosine_ops, and serves only queries ordering by that class's operator.

open as a page

In PostgreSQL, what do the jsonb operators ->, ->> and @> return, and when should a WHERE clause use @> instead of ->>?

level: middleimportance: must knowfreq 68%
basics
~20 s

The -> operator returns jsonb, ->> returns text, and @> returns a boolean saying whether the left document contains the right one's keys, values and structure. Use @> for type-aware matches on nested values or array members; ->> to read one scalar.

open as a page

In PostgreSQL, when is a NOTIFY issued inside a transaction delivered to listeners, and what happens to it if that transaction rolls back?

level: middleimportance: must knowfreq 58%
basics
~20 s

A NOTIFY is held until its transaction commits and only then delivered; if the transaction rolls back, nothing is ever sent. A listener that is inside its own transaction receives the notification only after that transaction ends.

open as a page

In PostgreSQL, what does CREATE EXTENSION actually do, and what must already exist on the server before it can succeed?

level: juniorimportance: must knowfreq 55%
basics
~20 s

CREATE EXTENSION runs an extension's install script in the current database and records every object it creates as a member of one package. The control file, SQL scripts and any C library must already be installed on the server.

open as a page

In PostgreSQL, why do some extensions need shared_preload_libraries and a server restart while others work right after CREATE EXTENSION?

level: middleimportance: must knowfreq 62%
basics
~20 s

Libraries that reserve shared memory, start background workers or install global hooks must be loaded by the postmaster at startup through shared_preload_libraries, which takes a restart. Libraries that only provide functions load on first call, so CREATE EXTENSION is enough.

open as a page

With PostgreSQL's postgres_fdw, which objects do you create to query another server's tables, and which object holds the host and which the password?

level: middleimportance: must knowfreq 55%
basics
~20 s

After CREATE EXTENSION postgres_fdw, a foreign server holds host, port and dbname; a user mapping holds each local role's remote user and password; foreign tables, usually made by IMPORT FOREIGN SCHEMA, name the remote tables.

open as a page

With PostgreSQL's postgres_fdw, how do you see which parts of a query run on the remote server, and what stops a WHERE clause from being pushed down?

level: seniorimportance: must knowfreq 48%
basics
~20 s

EXPLAIN VERBOSE shows each foreign scan's Remote SQL. postgres_fdw ships a WHERE clause only when its functions, operators and types are built-in or from a listed extension, immutable and collation-safe; otherwise rows cross the network and are filtered locally.

open as a page

In PostgreSQL, what is a foreign table, and how does querying one differ from querying an ordinary local table?

level: juniorimportance: should knowfreq 38%
basics
~20 s

A foreign table is a local definition whose rows live elsewhere; a foreign data wrapper such as postgres_fdw fetches them at query time. It has no local storage or indexes, and PostgreSQL assumes its declared constraints hold without enforcing them.

open as a page

In PostgreSQL, what can a procedure invoked with CALL do that a function invoked from a SELECT cannot, and how does each return results?

level: juniorimportance: must knowfreq 66%
basics
~20 s

A PostgreSQL procedure, run only with CALL, can COMMIT or ROLLBACK inside its body and returns just its OUT and INOUT parameters as one row; a function runs inside the caller's statement and transaction and returns a value or a set.

open as a page

In PostgreSQL, how do you build a BEFORE UPDATE row trigger that stamps an updated_at column, and what must its trigger function return?

level: juniorimportance: must knowfreq 68%
basics
~20 s

Write a function declared RETURNS trigger that sets NEW.updated_at and returns NEW, then attach it with CREATE TRIGGER ... BEFORE UPDATE ... FOR EACH ROW EXECUTE FUNCTION. Returning NULL instead silently skips that row's update.

open as a page

In PostgreSQL, what do the IMMUTABLE, STABLE and VOLATILE labels on CREATE FUNCTION let the planner do, and which is the default?

level: middleimportance: must knowfreq 58%
basics
~20 s

VOLATILE, the default, allows no optimisation and re-evaluates per row; STABLE lets PostgreSQL evaluate once per statement, so it can drive an index scan; IMMUTABLE allows plan-time folding and is required in index expressions. PostgreSQL trusts the label without verifying it.

open as a page

In PL/pgSQL, how does SELECT INTO behave with and without STRICT when zero or several rows match, and what does FOUND report?

level: middleimportance: must knowfreq 62%
basics
~10 s

Without STRICT, SELECT INTO takes the first row or sets NULLs and never errors; FOUND says whether a row came back. INTO STRICT requires exactly one row, raising NO_DATA_FOUND (P0002) or TOO_MANY_ROWS (P0003) otherwise.

open as a page

In PostgreSQL, what does REFRESH MATERIALIZED VIEW CONCURRENTLY require, and what does it cost compared with a plain refresh of the same view?

level: middleimportance: must knowfreq 55%
basics
~20 s

PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY needs a populated view with a unique index on plain columns and no WHERE clause. It keeps readers unblocked by diffing new rows against old, at the cost of more work and dead tuples.

open as a page

In PostgreSQL's pg_hba.conf, what do the fields of a record mean, and how do the local, host, hostssl and hostnossl connection types differ?

level: juniorimportance: must knowfreq 65%
basics
~20 s

A pg_hba.conf record lists a connection type, database, user, client address (absent on local lines) and an authentication method with optional options. local means Unix sockets, host any TCP/IP, hostssl only TLS-encrypted TCP, hostnossl only unencrypted TCP.

open as a page

In PostgreSQL, what do the LOGIN, SUPERUSER, CREATEROLE and REPLICATION role attributes allow, and why doesn't role membership pass them on?

level: juniorimportance: must knowfreq 64%
basics
~20 s

LOGIN lets a role open a session, SUPERUSER skips every permission check except login, CREATEROLE creates and administers roles it holds ADMIN on, and REPLICATION opens WAL-sender connections. Attributes belong to the role itself, so members never inherit them.

open as a page

Moving a PostgreSQL cluster from md5 to scram-sha-256, how do password_encryption and the pg_hba.conf method interact, and in what order do you switch them?

level: middleimportance: must knowfreq 60%
basics
~20 s

password_encryption decides how a newly set password is hashed; the pg_hba.conf method decides which exchange the server demands. An md5 line still accepts SCRAM-hashed roles, but a scram-sha-256 line fails MD5-hashed ones, so rehash every password first, then switch lines.

open as a page

In PostgreSQL, why do tables created by a migration login stay unreadable to analysts after an admin ran ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES?

level: middleimportance: must knowfreq 58%
basics
~20 s

ALTER DEFAULT PRIVILEGES applies only to objects later created by the role named in FOR ROLE, which defaults to whoever ran it. Tables the migration login creates use that login's own entries, so the admin's entry never fires.

open as a page

In PostgreSQL, which roles bypass a table's row-level security policies once it is enabled, and what does FORCE ROW LEVEL SECURITY change?

level: middleimportance: must knowfreq 62%
basics
~20 s

Superusers and roles with the BYPASSRLS attribute always bypass row security, and the table owner bypasses it by default. ALTER TABLE ... FORCE ROW LEVEL SECURITY subjects the owner to its policies; superusers and BYPASSRLS roles still bypass.

open as a page

In PostgreSQL, what does max_connections limit, what is its default, and what does changing it require?

level: juniorimportance: must knowfreq 68%
basics
~20 s

max_connections caps concurrent client backends, default 100. It has postmaster context, so a change needs a server restart, and a client arriving when every slot is taken is rejected with FATAL: sorry, too many clients already (SQLSTATE 53300).

open as a page

In PostgreSQL, what does shared_buffers control, what is its default, and why does changing it require a server restart?

level: juniorimportance: must knowfreq 68%
basics
~10 s

shared_buffers sizes PostgreSQL's own cache of 8 kB data pages in shared memory, used by every backend. It defaults to 128MB and has postmaster context, because that segment is allocated once at server start.

open as a page

In PostgreSQL, which configuration files set server parameters, which one wins a conflict, and how do you see where a value came from?

level: juniorimportance: must knowfreq 64%
basics
~20 s

postgresql.conf, with any include or include_dir files, is read first, then postgresql.auto.conf, written by ALTER SYSTEM, whose entries win; the last entry for a parameter counts. SHOW gives the value; pg_settings adds source, sourcefile and sourceline.

open as a page

In PostgreSQL, how do statement_timeout, idle_in_transaction_session_timeout, transaction_timeout and idle_session_timeout differ in what they measure and what they end?

level: middleimportance: must knowfreq 64%
basics
~10 s

statement_timeout cancels one statement with ERROR 57014 and the session survives. idle_in_transaction_session_timeout, transaction_timeout (17+) and idle_session_timeout end the whole session with FATAL. All four are off by default and take milliseconds.

open as a page

In PostgreSQL, why is work_mem not a per-query or per-connection limit, and how does that change the way you choose its value?

level: middleimportance: must knowfreq 74%
basics
~20 s

work_mem (default 4MB) budgets each sort or hash operation in a plan; hash nodes get work_mem times hash_mem_multiplier (2.0). A query holds several budgets, per parallel worker, across many sessions, so keep it modest globally and raise it locally.

open as a page

In PostgreSQL, what does the pg_stat_activity view show, and what do its state values such as 'idle in transaction' mean?

level: juniorimportance: must knowfreq 78%
basics
~20 s

pg_stat_activity has one row per PostgreSQL server process, showing who is connected, what each runs or waits on, and since when. The state idle in transaction means a transaction is open but no statement is running.

open as a page

In PostgreSQL, what do log_statement and log_min_duration_statement each log, and which one catches slow queries?

level: juniorimportance: must knowfreq 68%
basics
~20 s

log_statement logs statements by class (none, ddl, mod, all) before they run, with no duration. log_min_duration_statement logs every completed statement that ran at least its threshold, in milliseconds (-1 disables it). Only the second one catches slow queries.

open as a page

In PostgreSQL, how do you find which tables of a database take the most disk space, and why sort by pg_total_relation_size rather than pg_relation_size?

level: juniorimportance: must knowfreq 62%
basics
~20 s

Select pg_total_relation_size(relid) from pg_stat_user_tables, order by that bigint and wrap it in pg_size_pretty only for display. It counts the heap, its free space and visibility maps, TOAST and every index; pg_relation_size returns just the main fork.

open as a page

In PostgreSQL, how do pg_cancel_backend() and pg_terminate_backend() differ, and which one ends a session stuck idle in transaction?

level: middleimportance: must knowfreq 72%
basics
~20 s

pg_cancel_backend(pid) aborts only the statement a session is running; pg_terminate_backend(pid) closes the whole session, rolling back its open transaction. An idle-in-transaction session runs no statement, so a cancel does nothing and terminating it is what releases its locks.

open as a page

In PostgreSQL, how do you use pg_stat_statements to find the query costing the most total time, and why not rank by mean_exec_time?

level: middleimportance: must knowfreq 72%
basics
~20 s

Query pg_stat_statements ordered by total_exec_time, which is calls times mean time per normalised query. A 3 ms query run two million times outweighs one 45-second report, so ranking by mean finds slow queries rather than expensive ones.

open as a page

In PostgreSQL, what does the pg_wal directory hold, how is an LSN written, and why must you never delete its files by hand?

level: juniorimportance: must knowfreq 62%
basics
~20 s

pg_wal holds PostgreSQL's write-ahead log as numbered 16 MB segment files; an LSN is a byte position in that log, written like 16/B374D848. Deleting segments by hand can remove WAL crash recovery needs, so the server may not start.

open as a page

In PostgreSQL, how do you replicate three tables from an orders database to an analytics server using CREATE PUBLICATION and CREATE SUBSCRIPTION?

level: juniorimportance: must knowfreq 66%
basics
~20 s

Set wal_level = logical on the orders server and publish the three tables there; create the same tables on the analytics server, then CREATE SUBSCRIPTION, which makes a slot, copies existing rows and streams every later change.

open as a page

In PostgreSQL, how do you promote a streaming standby to primary, and why does a standby never promote itself when the primary fails?

level: juniorimportance: must knowfreq 64%
basics
~20 s

Run pg_ctl promote on the standby's host or call SELECT pg_promote() over SQL; both end recovery and open the server for writes. Core PostgreSQL ships no failure detector, so promotion is always an operator's or HA tool's decision.

open as a page

In PostgreSQL streaming replication, what does a replication slot guarantee that a standby without one lacks, and what risk does it add?

level: juniorimportance: must knowfreq 72%
basics
~20 s

A PostgreSQL replication slot makes the primary keep all WAL its consumer has not yet confirmed, even while that consumer is offline, so the standby cannot fall behind recycled WAL. The risk: an abandoned slot retains WAL until the disk fills.

open as a page

In PostgreSQL 18, what makes a server start as a streaming standby, and what can clients do while connected to it?

level: juniorimportance: must knowfreq 72%
basics
~20 s

An empty standby.signal file in the data directory plus primary_conninfo pointing at the primary make PostgreSQL start in standby mode and stream WAL; with hot_standby on (the default) clients may run read-only queries, never writes.

open as a page

What does PostgreSQL's pg_basebackup copy from a running cluster, and what must the server permit before it can connect and run?

level: juniorimportance: must knowfreq 62%
basics
~20 s

pg_basebackup copies a running PostgreSQL cluster's files: every database and tablespace at once, never a single database. It uses a replication connection, so it needs a REPLICATION or superuser role, a pg_hba.conf replication line and free WAL senders.

open as a page

What are PostgreSQL pg_dump's four output formats, and which tool restores each of them?

level: juniorimportance: must knowfreq 72%
basics
~10 s

pg_dump writes plain SQL (-Fp), replayed with psql, or one of three archives read by pg_restore: custom (-Fc, one compressed file), directory (-Fd, per-table files, the only parallel-dump format) and tar (-Ft, uncompressed).

open as a page

In PostgreSQL, which settings turn on continuous WAL archiving, and what must an archive_command guarantee before it exits with status zero?

level: juniorimportance: must knowfreq 62%
basics
~10 s

Set wal_level to replica or higher, archive_mode = on (restart required) and either archive_command or archive_library. The command must exit zero only after the segment is durably stored, because PostgreSQL then recycles it.

open as a page

In PostgreSQL, what do pg_basebackup's -X none, -X fetch and -X stream do, and why is a copy without its WAL unusable?

level: middleimportance: must knowfreq 58%
basics
~20 s

Copied files are consistent only after replaying the WAL written from the backup's start to its end. -X stream (default) receives that WAL live, -X fetch collects it at the end, and -X none leaves it to the WAL archive.

open as a page

Copying one PostgreSQL database to staging with pg_dump, why do owner and grant statements fail on restore, and how do you fix that?

level: middleimportance: must knowfreq 61%
basics
~10 s

pg_dump exports one database but not the cluster-wide roles its owners and grants name, so those statements fail on a fresh cluster. Restore the roles first with pg_dumpall --globals-only, or restore with --no-owner --no-privileges.

open as a page

In PostgreSQL, what is the difference between a major release such as 18 and a minor release such as 18.6, and how is each applied?

level: juniorimportance: must knowfreq 70%
basics
~20 s

A PostgreSQL major (17, 18) adds features and may change the on-disk format, so reaching it needs dump/restore, pg_upgrade or logical replication. A minor (18.4 to 18.6) only fixes bugs and security holes: install the binaries and restart.

open as a page

Why can't PostgreSQL 18 binaries start on a data directory initialized by PostgreSQL 17, and what are the three routes to a new major version?

level: juniorimportance: must knowfreq 62%
basics
~20 s

A major release may change the system-catalog layout, so the server refuses a data directory from another major. The routes are dump and restore, pg_upgrade, which rebuilds the catalogs and reuses the data files, and a logical-replication cutover.

open as a page

In PostgreSQL 18's pg_upgrade, how do the copy, link, clone, copy-file-range and swap modes differ in speed, disk use and the way back?

level: middleimportance: must knowfreq 55%
basics
~20 s

Copy, the default, duplicates files and keeps the old cluster intact; link hard-links them, fast, but the old cluster dies once the new starts; clone and copy-file-range share blocks where supported; 18's swap moves directories, destroying the old cluster.

open as a page

In PostgreSQL, how do you check which server version you are connected to, and why might version() still report 18.4 after 18.6 packages were installed?

level: middleimportance: should knowfreq 46%
basics
~20 s

Use SELECT version() or SHOW server_version to read it, and server_version_num (180006 for 18.6) in scripts. If version() still shows 18.4 after the 18.6 packages went in, the server has not been restarted and is still running the old code.

open as a page

A PostgreSQL minor update is meant to be a binary swap and restart; what can its release notes still require, with 18.x examples?

level: middleimportance: should knowfreq 30%
basics
~20 s

A PostgreSQL minor's Migration section can still ask you to reindex indexes the old code built wrongly, run ANALYZE, clean affected data or change configuration; 18.6 added output_plugin_libraries, which blocks third-party logical decoding plugins until they are listed.

open as a page