PostgreSQL
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 pageshowhide
guide
overview
~1 minPostgreSQL 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.
- Engine Anatomy →
Processes, shared memory, pages and the cluster, database and schema layers that every other answer quietly assumes.
- MVCC & Locking →
Row versions, snapshots, isolation levels and locks: the model behind blocking, retries and most concurrency questions.
- Vacuum & Freezing →
The other half of MVCC: how dead versions are reclaimed, why freezing exists and where bloat comes from.
- Indexes & Query Planning →
Index access methods and the cost-based planner, learned by reading EXPLAIN ANALYZE output against real queries.
- Write-Ahead Log & Replicas →
How one log provides durability, standbys, slots and logical decoding, and what each consumer costs the primary.
- 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 FULLas 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_memas if it were per connection, when each sort or hash step can claim its own budget across many sessions at once.Raising
max_connectionsto 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 INDEXon 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
- Engine Anatomy22 questions
- Processes & Shared Memory5 questions
- Pages, TOAST & HOT5 questions
- Databases, Schemas & Tablespaces6 questions
- Cluster Init, Startup & Shutdown6 questions
- MVCC & Locking14 questions
- Tuple Visibility & Snapshots5 questions
- Isolation & Serialization Failures4 questions
- Lock Modes & Advisory Locks5 questions
- Vacuum & Freezing16 questions
- Dead Tuples & Autovacuum5 questions
- XID Wraparound6 questions
- Bloat & Table Rewrites5 questions
- Indexes & Query Planning33 questions
- Access Methods18 questions
- Choosing a Plan15 questions
- Column Types17 questions
- Text, Numbers & Time6 questions
- Collations & Encoding5 questions
- Arrays, Ranges & Domains6 questions
- SQL Dialect37 questions
- Upserts, MERGE & RETURNING5 questions
- Bulk Loading with COPY5 questions
- UUIDs, Serial & Generated Columns5 questions
- Exclusion & Temporal Constraints5 questions
- Online Schema Changes5 questions
- Declarative Partitioning Rules6 questions
- DISTINCT ON, CTEs & Regex6 questions
- Multi-Model Workloads23 questions
- JSONB Documents6 questions
- Full-Text & Trigram Search6 questions
- Vector Similarity Search6 questions
- LISTEN/NOTIFY Messaging5 questions
- Extensibility12 questions
- Extensions & Contrib Modules6 questions
- Foreign Data Wrappers6 questions
- Server-Side Code21 questions
- PL/pgSQL Programming5 questions
- Functions & Procedures6 questions
- Trigger Functions & Event Triggers5 questions
- View Security & Refresh5 questions
- Database Security17 questions
- Login Methods & TLS6 questions
- Role Attributes & Grants6 questions
- Row-Level Policies5 questions
- Server Tuning16 questions
- Parameter Files & Reloads5 questions
- Memory & I/O Settings6 questions
- Connection Limits & Timeouts5 questions
- Monitoring & Diagnostics15 questions
- Activity & Wait Events5 questions
- Cumulative Stats & Sizes5 questions
- Query Stats & Logging5 questions
- Write-Ahead Log & Replicas28 questions
- Checkpoints & Commit Durability6 questions
- Streaming Standbys6 questions
- Replication Slots & Retention5 questions
- Logical Decoding & Publications6 questions
- Standby Promotion & Timelines5 questions
- Backup & Restore15 questions
- Logical Dumps5 questions
- Base & Incremental Copies5 questions
- WAL Archiving & PITR5 questions
- Release Lifecycle11 questions
- Version Cadence & Support5 questions
- Major-Version Migration6 questions
questions
297 · 15 sectionsIn PostgreSQL, how do a cluster, a database and a schema relate, and which objects belong to the whole cluster rather than one database?
basics
~20 sA 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.
In PostgreSQL, what do initdb, pg_ctl and the postgres server program each do across a cluster's life, from creation to shutdown?
basics
~20 sinitdb 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.
On a PostgreSQL 18 server, ps lists many postgres processes: what is the postmaster, and what do the other processes do?
basics
~20 sThe 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.
In PostgreSQL, why does a query joining tables from two databases in the same cluster fail, and what can you do instead?
basics
~20 sEach 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.
In PostgreSQL, what do pg_ctl stop's smart, fast and immediate modes do, which is the default, and when would you use each?
basics
~20 sSmart 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.
In PostgreSQL, what is the default transaction isolation level, how do you choose another, and what do you get if you request READ UNCOMMITTED?
basics
~20 sPostgreSQL 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.
In PostgreSQL, does an UPDATE overwrite the row in place, and what happens to a table's size after you update every row?
basics
~20 sNo. 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.
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?
basics
~20 sIt 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.
In PostgreSQL, how should workers of a transactional-email queue claim rows with SKIP LOCKED, and how does that differ from NOWAIT?
basics
~20 sEach 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.
In PostgreSQL, which table-level lock do SELECT, SELECT ... FOR UPDATE and UPDATE each take, and which mode alone blocks a plain SELECT?
basics
~20 sA 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.
In PostgreSQL, what does a plain VACUUM do with dead tuples, and why does the table file usually stay the same size afterwards?
basics
~20 sPlain 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.
In PostgreSQL, what does VACUUM FULL do that plain VACUUM does not, and what does it cost while it runs?
basics
~20 sPlain 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.
In PostgreSQL, why do 32-bit transaction IDs make freezing necessary, and what does VACUUM's freezing actually change on a row?
basics
~20 sPostgreSQL 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.
In PostgreSQL 18, what thresholds make autovacuum pick a table for VACUUM or ANALYZE, and why do the defaults fail large tables?
basics
~20 sAutovacuum 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.
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?
basics
~20 spg_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.
In PostgreSQL, what index does a plain CREATE INDEX build, and which WHERE and ORDER BY clauses on that column can it serve?
basics
~20 sA 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.
In PostgreSQL, what does EXPLAIN ANALYZE do to an UPDATE or DELETE, and how do you measure one without changing your data?
basics
~20 sEXPLAIN 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.
In PostgreSQL, beyond the default B-tree, which index access methods ship in core, and what kind of query is each one built for?
basics
~10 sCore 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).
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?
basics
~20 sANALYZE 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.
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?
basics
~10 sPostgreSQL 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".
In PostgreSQL, how do text, varchar(n) and char(n) differ in storage and behaviour, and which should a new column use?
basics
~10 sPostgreSQL 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.
In PostgreSQL, what does a timestamptz column actually store, and how does the session TimeZone setting change what you read back?
basics
~10 sA 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.
In PostgreSQL 18, how do the libc, ICU and builtin collation providers differ, and which would you pick for a new UTF8 cluster?
basics
~20 slibc 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.
In PostgreSQL, how do you find recipes whose text[] tags column holds one given tag, all of several tags, or any of them?
basics
~20 sUse '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.
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?
basics
~20 sALTER 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.
In PostgreSQL, how does COPY ... FROM '/path/file.csv' differ from COPY ... FROM STDIN, and what privilege does each one need?
basics
~20 sCOPY 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.
In PostgreSQL, why is GENERATED AS IDENTITY preferred over serial or bigserial for a new table's primary key?
basics
~20 sserial 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.
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?
basics
~20 sFROM 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.
In PostgreSQL, how do ILIKE, ~ and ~* differ from LIKE when you search stock symbols or company names?
basics
~20 sILIKE 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 $.
In PostgreSQL, how does INSERT ... ON CONFLICT DO NOTHING differ from ON CONFLICT DO UPDATE, and what does EXCLUDED refer to?
basics
~10 sON 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.
In PostgreSQL, what is the difference between the json and jsonb column types, and which one should a new column normally use?
basics
~20 sjson 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.
In PostgreSQL full-text search, what are tsvector and tsquery, and what does the @@ operator check between them?
basics
~20 sA 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.
In pgvector, what do the <->, <=> and <#> operators compute, and why must an index's operator class match the operator?
basics
~20 spgvector'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.
In PostgreSQL, what do the jsonb operators ->, ->> and @> return, and when should a WHERE clause use @> instead of ->>?
basics
~20 sThe -> 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.
In PostgreSQL, when is a NOTIFY issued inside a transaction delivered to listeners, and what happens to it if that transaction rolls back?
basics
~20 sA 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.
In PostgreSQL, what does CREATE EXTENSION actually do, and what must already exist on the server before it can succeed?
basics
~20 sCREATE 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.
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?
basics
~20 sAfter 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.
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?
basics
~20 sEXPLAIN 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.
In PostgreSQL, what is a foreign table, and how does querying one differ from querying an ordinary local table?
basics
~20 sA 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.
In PostgreSQL, what can a procedure invoked with CALL do that a function invoked from a SELECT cannot, and how does each return results?
basics
~20 sA 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.
In PostgreSQL, how do you build a BEFORE UPDATE row trigger that stamps an updated_at column, and what must its trigger function return?
basics
~20 sWrite 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.
In PostgreSQL, what do the IMMUTABLE, STABLE and VOLATILE labels on CREATE FUNCTION let the planner do, and which is the default?
basics
~20 sVOLATILE, 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.
In PL/pgSQL, how does SELECT INTO behave with and without STRICT when zero or several rows match, and what does FOUND report?
basics
~10 sWithout 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.
In PostgreSQL, what does REFRESH MATERIALIZED VIEW CONCURRENTLY require, and what does it cost compared with a plain refresh of the same view?
basics
~20 sPostgreSQL'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.
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?
basics
~20 sA 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.
In PostgreSQL, what do the LOGIN, SUPERUSER, CREATEROLE and REPLICATION role attributes allow, and why doesn't role membership pass them on?
basics
~20 sLOGIN 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.
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?
basics
~20 spassword_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.
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?
basics
~20 sALTER 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.
In PostgreSQL, which roles bypass a table's row-level security policies once it is enabled, and what does FORCE ROW LEVEL SECURITY change?
basics
~20 sSuperusers 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.
In PostgreSQL, what does max_connections limit, what is its default, and what does changing it require?
basics
~20 smax_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).
In PostgreSQL, which configuration files set server parameters, which one wins a conflict, and how do you see where a value came from?
basics
~20 spostgresql.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.
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?
basics
~10 sstatement_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.
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?
basics
~20 swork_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.
In PostgreSQL, what does the pg_stat_activity view show, and what do its state values such as 'idle in transaction' mean?
basics
~20 spg_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.
In PostgreSQL, what do log_statement and log_min_duration_statement each log, and which one catches slow queries?
basics
~20 slog_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.
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?
basics
~20 sSelect 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.
In PostgreSQL, how do pg_cancel_backend() and pg_terminate_backend() differ, and which one ends a session stuck idle in transaction?
basics
~20 spg_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.
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?
basics
~20 sQuery 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.
In PostgreSQL, what does the pg_wal directory hold, how is an LSN written, and why must you never delete its files by hand?
basics
~20 spg_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.
In PostgreSQL, how do you replicate three tables from an orders database to an analytics server using CREATE PUBLICATION and CREATE SUBSCRIPTION?
basics
~20 sSet 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.
In PostgreSQL, how do you promote a streaming standby to primary, and why does a standby never promote itself when the primary fails?
basics
~20 sRun 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.
In PostgreSQL streaming replication, what does a replication slot guarantee that a standby without one lacks, and what risk does it add?
basics
~20 sA 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.
In PostgreSQL 18, what makes a server start as a streaming standby, and what can clients do while connected to it?
basics
~20 sAn 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.
What does PostgreSQL's pg_basebackup copy from a running cluster, and what must the server permit before it can connect and run?
basics
~20 spg_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.
What are PostgreSQL pg_dump's four output formats, and which tool restores each of them?
basics
~10 spg_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).
In PostgreSQL, which settings turn on continuous WAL archiving, and what must an archive_command guarantee before it exits with status zero?
basics
~10 sSet 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.
In PostgreSQL, what do pg_basebackup's -X none, -X fetch and -X stream do, and why is a copy without its WAL unusable?
basics
~20 sCopied 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.
Copying one PostgreSQL database to staging with pg_dump, why do owner and grant statements fail on restore, and how do you fix that?
basics
~10 spg_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.
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?
basics
~20 sA 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.
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?
basics
~20 sA 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.
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?
basics
~20 sCopy, 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.
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?
basics
~20 sUse 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.
A PostgreSQL minor update is meant to be a binary swap and restart; what can its release notes still require, with 18.x examples?
basics
~20 sA 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.