Back to interview questions
By
Team Sherlock.sh
PostgreSQL interviews should test whether a candidate understands the database behind a working SQL statement. Recruiters hiring application engineers, database specialists, or platform engineers need evidence that candidates can choose useful data types, explain concurrent behavior, interpret a query plan, and protect availability without sacrificing correctness.
These 120 questions move from PostgreSQL architecture and schema fundamentals to indexing, transaction behavior, maintenance, and production decisions. Unlike a general SQL coding exercise bank, this guide concentrates on PostgreSQL-specific mechanisms and operational judgment: why a snapshot retains old rows, why an index may not help, what a replica acknowledgment guarantees, and how to prove a backup can restore service.
How can recruiters distinguish practical PostgreSQL expertise from a rehearsed explanation of database terminology?
Ask candidates to explain one plan estimate, reason through two concurrent transactions, or describe the evidence they would collect before changing a production setting. Change an assumption and ask how their diagnosis would change.
Sherlock AI helps recruiters assess signals of potential AI cheating, deepfakes, impersonation, and unauthorized assistance during interviews.
Combine those signals with concrete technical follow-up and work samples; they are not conclusive proof of misconduct. A strong PostgreSQL candidate can explain both an intervention and the conditions under which it would be unsafe.
PostgreSQL Interview Questions
Use the complete question bank to select an interview path before reading the answers. The levels progress from core mechanisms through practical implementation to reliability and architecture decisions.
PostgreSQL Interview Questions for Beginners
These questions establish PostgreSQL vocabulary, architecture, types, constraints, transactions, and the basic boundaries of access and data integrity.
What is PostgreSQL, and what does its client-server architecture imply?
How do a PostgreSQL cluster, database, and schema differ?
Why does the number of client connections matter in PostgreSQL?
What are shared buffers, and are they the only database cache?
What is write-ahead logging, and why is it called write-ahead?
How is psql different from SQL itself?
What can system catalogs and the information schema tell you?
What does search_path control, and why is it a security concern?
How does PostgreSQL treat quoted and unquoted identifiers?
Why can a client-side file path fail in a server-side COPY command?
When would you choose numeric instead of a floating-point type?
How do text, varchar, and char differ in PostgreSQL?
What are the three possible states of a nullable Boolean column?
How do timestamp and timestamptz differ?
When should you choose jsonb rather than json?
When is a PostgreSQL array appropriate, and when is a child table better?
What problem do range types solve?
What should a candidate know about PostgreSQL UUID values?
When is an enum useful, and what is its main modeling trade-off?
How does a domain differ from an ordinary column constraint?
How does a primary key differ from a unique constraint?
What does a foreign key enforce, and what does it not automatically provide?
Why does CHECK (amount > 0) still allow NULL?
Why are identity columns usually clearer than serial for new schemas?
How do generated columns differ from defaults in PostgreSQL 18?
When would you use IS DISTINCT FROM instead of the equality operators?
What does INSERT ... ON CONFLICT solve?
Why is RETURNING useful after a data modification?
How does a normal view differ from a materialized view?
How should you decide which rules belong in database constraints?
What does a transaction guarantee, and what does ACID mean in practice?
What is a savepoint, and does it create an independent transaction?
What does MVCC mean, and why are old row versions needed?
What is PostgreSQL's default isolation level?
What is an index, and why should a table not have an index on every column?
How do temporary and unlogged tables differ?
How are PostgreSQL users and groups represented?
Why can a role have SELECT on a table and still fail to read it?
What is a PostgreSQL extension, and why is installing one a controlled change?
How do parameterized queries help keep PostgreSQL application access safe?
PostgreSQL Interview Questions for Intermediate
These questions explore concurrent transactions, index design, plan interpretation, statistics, maintenance, partitioning, and database-side logic.
When does a Repeatable Read transaction acquire its snapshot?
How does Repeatable Read differ from Serializable in PostgreSQL?
What is write skew, and how would you prevent it?
What should an application retry after a serialization failure?
When is SELECT FOR UPDATE appropriate?
How do table-level locks differ from row-level locks?
How does a deadlock differ from ordinary lock waiting?
When are advisory locks useful, and what are their limitations?
Why is SKIP LOCKED useful for job queues but risky for ordinary reporting?
How can a conditional UPDATE avoid a read-then-write race?
How should you choose the column order of a multicolumn B-tree index?
What does PostgreSQL 18 B-tree skip scan change?
When is a partial index more useful than a full index?
What problem does an expression index solve?
Why can an index-only scan still perform heap fetches?
How do you choose a GIN operator class for a jsonb index?
When would GiST be preferable to a B-tree index?
Why can a BRIN index be effective on a very large table?
What are index operator classes and collations responsible for?
What trade-offs come with CREATE INDEX CONCURRENTLY?
How should you read an EXPLAIN plan without confusing cost with time?
What extra evidence does EXPLAIN ANALYZE provide, and what is its risk?
What can EXPLAIN BUFFERS tell you about a slow query?
How do inaccurate row estimates lead to bad execution plans?
When are extended statistics useful?
How do nested-loop, hash, and merge joins differ?
Why can a prepared statement be fast for one parameter and slow for another?
Does a WITH query always act as an optimization fence?
Why is work_mem not a per-connection memory limit?
Why might a query fail to benefit from parallel execution?
Why does ordinary VACUUM usually not shrink a table file substantially?
How should you reason about autovacuum thresholds on a large, busy table?
What enables a HOT update, and why does it matter?
Why must PostgreSQL freeze old transaction IDs?
What is TOAST, and how can it affect query performance?
What makes a good partition key in PostgreSQL?
Why can a partitioned table's unique constraint require the partition key?
How does partition pruning differ from using an index?
What concurrency edge cases matter for INSERT ON CONFLICT?
When should a PostgreSQL trigger enforce behavior instead of application code?
PostgreSQL Interview Questions for Experienced
These scenarios test production diagnosis, capacity, durability, replication, recovery, security, migrations, and evidence-based decisions under operational constraints.
How would you investigate a sudden increase in PostgreSQL response time?
Why can increasing max_connections make an overloaded PostgreSQL service slower?
What application behavior must you verify before adopting transaction pooling?
How would you estimate the risk of increasing work_mem?
How can you distinguish storage pressure from a low PostgreSQL cache-hit ratio?
What evidence would connect periodic latency spikes to checkpoints?
How do you discuss asynchronous commit without confusing it with disabling durability safeguards?
How would you diagnose table growth after heavy updates without immediately rewriting the table?
Why are idle-in-transaction sessions operationally dangerous?
How would you respond to an approaching transaction-ID wraparound limit?
How would you choose physical versus logical replication for a project?
Why can a replication slot endanger disk capacity even when the database is healthy?
How would you diagnose replication lag without relying on one lag number?
Why can a read query be canceled on a hot standby?
What must happen around promotion to avoid split-brain failover?
How do synchronous replication settings change a durability and availability decision?
How would you translate RPO and RTO into a PostgreSQL recovery design?
What does a valid PostgreSQL PITR plan require beyond a base backup?
When is pg_dump useful, and why is it not a complete physical disaster-recovery strategy?
What evidence makes a backup restore test convincing?
How would you design database roles for a production application and its migrations?
What are the important limitations of row-level security in a multi-tenant design?
How would you review a SECURITY DEFINER function before approving it?
How would you audit schema privileges after upgrading a legacy PostgreSQL database?
How would you safely diagnose a pg_hba.conf authentication failure?
What does TLS verification add beyond requiring an encrypted PostgreSQL connection?
How would you plan a migration from MD5 password authentication to SCRAM?
How would you evaluate an extension before installing it in production?
What would you include in an auditable PostgreSQL access and change trail?
How would you choose between shared tables, separate schemas, and separate databases for tenants?
How would you add a constraint to a large busy table without assuming the change is instantaneous?
How would you backfill a new column on a heavily used PostgreSQL table?
What should a partition-retention procedure check before removing old data?
How would you choose and rehearse a major-version PostgreSQL upgrade?
Which PostgreSQL 18 changes would you explicitly check when upgrading from 16 or 17?
What can PostgreSQL data checksums prove, and what can they not prove?
How would you handle suspected index corruption without destroying evidence?
What PostgreSQL observability would you maintain before an incident occurs?
How would you estimate capacity headroom for a growing PostgreSQL workload?
How would you prove that a PostgreSQL performance change is worth deploying?
PostgreSQL Interview Questions and Answers
Answers follow the same wording, numbering, and order as the bank. They connect a direct explanation to an example, trade-off, or failure mode. The technical baseline is PostgreSQL 18. Examples involving version-specific features say so; verify behavior against the installed major version. SQL illustrates mechanisms in a disposable test database or read-only diagnosis, not a runbook for unreviewed production changes. No PostgreSQL server was available for executing the examples.
PostgreSQL Interview Questions and Answers for Beginners
The beginner answers connect everyday database features to their actual behavior. Probe the examples and caveats to distinguish understanding from memorized definitions.
1. What is PostgreSQL, and what does its client-server architecture imply?
PostgreSQL is an open-source relational database system with an extensible type and SQL system. A client sends requests to a database server; the server manages storage, concurrency, permissions, and execution. An application driver and psql are clients, not alternative database engines. This separation matters when diagnosing failures: an unavailable network route, failed authentication, and invalid SQL are different problems. It also matters for file access: a path interpreted by the server refers to the server's filesystem, not necessarily the developer's laptop. A strong candidate distinguishes application behavior from database guarantees rather than treating a driver as the source of transaction semantics.
2. How do a PostgreSQL cluster, database, and schema differ?
A PostgreSQL database cluster is the collection of databases managed by one server instance and its associated data directory. Here, cluster does not automatically mean a multi-machine high-availability deployment. A database contains schemas, and schemas organize named objects such as tables and functions. A connection ordinarily works within one database, while schema-qualified names can address objects in different schemas of that database. Roles are shared across the cluster, but many object definitions and privileges belong to individual databases. For example, billing.invoice identifies a schema and table, not a remote database. This hierarchy influences backup scope, naming, and tenancy boundaries.
3. Why does the number of client connections matter in PostgreSQL?
PostgreSQL normally assigns a server backend process to each client connection, so connections are not free even when applications think of them as lightweight handles. Active queries can additionally consume memory for sorts, hashes, and other operations. An application with hundreds of workers can overwhelm a database if each opens several connections without a concurrency limit. Pooling can reuse connections and cap simultaneous work, but the pool's mode must fit session state and transaction behavior. The useful interview answer is not to maximize max_connections; it is to relate application concurrency, database capacity, and queueing to measured workload demand.
4. What are shared buffers, and are they the only database cache?
Shared buffers are PostgreSQL's shared cache of database pages. Backends can reuse a cached page instead of repeatedly fetching it from the underlying storage path. They are not the only cache: the operating system also caches filesystem data, and applications can maintain their own caches. Increasing shared_buffers therefore does not allocate a universal performance improvement, nor does a buffer hit prove a query is efficient. A query that repeatedly touches millions of cached pages may still consume considerable CPU. Evaluate the whole memory budget, active query memory, access patterns, and storage behavior before changing server settings in a controlled benchmark.
5. What is write-ahead logging, and why is it called write-ahead?
Write-ahead logging, or WAL, records changes so PostgreSQL can recover reliably after a failure. The relevant log records must reach durable storage before the corresponding modified data pages are written there; that ordering is the write-ahead principle. Recovery can replay logged changes instead of requiring every changed table page to be flushed at each commit. WAL is also foundational to physical replication and point-in-time recovery, but WAL files alone are not a complete backup strategy. A candidate should distinguish the durability of a committed transaction from when its table pages happen to be written, and recognize that durability settings change the guarantee.
6. How is psql different from SQL itself?
psql is an interactive PostgreSQL client that sends SQL to a server and provides its own client-side commands. For example, SELECT current_database(); is SQL, while \conninfo and \dt are psql meta-commands. A driver executing SQL cannot generally run these backslash commands unchanged. This distinction is useful when an interview example works in a terminal but fails in application code. Use \conninfo to confirm the actual destination before investigating surprising results, especially when several environments share similar database names. Scripts should deliberately handle errors instead of assuming a command-line session automatically stops after the first failure.
7. What can system catalogs and the information schema tell you?
System catalogs describe PostgreSQL objects and their metadata, including relations, attributes, types, and roles. The information schema exposes standardized views that are useful when portability matters, while PostgreSQL catalogs provide implementation-specific detail. An investigator can inspect names and column definitions rather than guessing how a table was created. For example, SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'billing'; lists accessible matching metadata. Visibility rules mean absence from an information-schema result is not always proof that an object does not exist for every user. Read catalogs through supported queries; do not treat direct catalog modification as routine administration.
8. What does search_path control, and why is it a security concern?
search_path determines which schemas PostgreSQL searches when resolving an unqualified object name. If an application asks for invoice, its result depends on the applicable path; billing.invoice is more explicit. A path containing a schema where an untrusted role can create objects can expose queries or functions to object-name substitution. The concern extends beyond tables because functions and operators also participate in name resolution. Inspect the active path and schema privileges rather than assuming public has identical permissions in every installation. Upgraded databases can retain historical grants, so a fresh-install default is not evidence about an existing production database.
9. How does PostgreSQL treat quoted and unquoted identifiers?
Unquoted identifiers are folded to lowercase in PostgreSQL. A table created as CustomerOrders without double quotes is therefore named customerorders; a table created as "CustomerOrders" retains its mixed case and must subsequently be referenced appropriately. Double quotes delimit identifiers, while single quotes delimit string literals. For example, SELECT 'status' returns text rather than reading a column named status. Consistent lowercase names usually reduce unnecessary quoting, but migrations must respect existing names. Parameter placeholders represent data values, not arbitrary identifiers; dynamic table or column names require separate safe identifier handling and an allowlist where appropriate.
10. Why can a client-side file path fail in a server-side COPY command?
A server-side COPY using a filename reads or writes from the database server's perspective and requires the applicable server privileges. The file may not exist on that machine even when it is present on the client. In contrast, psql's \copy uses the client to transfer data through the connection, so its local path belongs to the client machine. This is an architectural distinction, not a reason to grant broad server-file access. For a controlled import, confirm the destination, file format, encoding, permissions, and error handling first. In production, prefer an authorized transfer workflow rather than experimenting with privileged filesystem paths.
11. When would you choose numeric instead of a floating-point type?
Choose numeric when decimal arithmetic and a declared precision or scale are part of the data contract, such as storing currency amounts with explicit rounding rules. real and double precision use approximate binary floating-point representations and are useful for measurements or scientific calculations where that trade-off is acceptable. For a small read-only illustration, SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric; returns true. Exact storage still does not choose a business rounding policy or a currency conversion rule for you. Numeric arithmetic can be more expensive, so choose types from correctness requirements before benchmarking performance alternatives.
12. How do text, varchar, and char differ in PostgreSQL?
text stores variable-length strings without a declared length limit, while varchar(n) enforces a maximum character length. char(n) is fixed-length and blank-padded, with trailing-space behavior that can surprise application code. PostgreSQL does not offer a general performance advantage merely because a string is declared as varchar(n) instead of text. Use a length constraint when it expresses a real domain rule, not an arbitrary assumption about speed. For example, an external system's 20-character identifier may justify a bound, while a free-form description usually does not. Character counts are also not equivalent to byte counts in multibyte encodings.
13. What are the three possible states of a nullable Boolean column?
A nullable Boolean column can contain true, false, or SQL NULL. NULL represents an unknown or missing value, not a third ordinary truth value equivalent to false. A WHERE enabled filter selects true rows and excludes both false and NULL rows; WHERE enabled IS NOT TRUE includes the latter two. This distinction affects feature flags, survey responses, and incomplete records. If the business concept truly has only two states, NOT NULL with an intentional default often makes the contract clearer. Do not add a false default merely to hide missing data when unknown has a legitimate meaning.
14. How do timestamp and timestamptz differ?
timestamp without time zone stores a date and clock time without identifying a global instant. timestamp with time zone, commonly written timestamptz, represents an instant and renders it using the session time zone; it does not preserve the original named time zone supplied by a user. For an event that happened globally, timestamptz is generally the clearer model. For a recurring local appointment, the application may also need the intended local time and an IANA time-zone name. Daylight-saving transitions make those distinctions material. Avoid ambiguous text input and specify offsets or zones deliberately when importing timestamped events.
15. When should you choose jsonb rather than json?
jsonb stores a decomposed representation that supports useful containment operations and indexing, making it a common choice for queried JSON documents. json preserves the input text, including formatting and duplicate-key representation, but requires reparsing for many operations. jsonb does not preserve whitespace or object-key order and retains only the last duplicate object key. A read-only example is SELECT '{"plan":"team"}'::jsonb ->> 'plan';, which returns text. Prefer ordinary typed columns for stable fields needing relational constraints, joins, or consistent statistics. JSON flexibility is useful, but it does not remove the need to define valid shapes and application semantics.
16. When is a PostgreSQL array appropriate, and when is a child table better?
An array is appropriate when a bounded collection is genuinely one attribute, such as a small ordered vector or a fixed set of measurements treated together. PostgreSQL supports typed arrays and array operators; SELECT 7 = ANY (ARRAY[3,7,9]); illustrates membership without creating objects. A child table is often better when elements require individual foreign keys, independent updates, joins, or their own attributes. An array declaration does not automatically enforce the dimensions written in the type declaration. Avoid choosing arrays simply to bypass relational modeling: repeated membership searches across large growing collections may indicate that the elements deserve separate rows.
17. What problem do range types solve?
Range types represent intervals as values with explicit bounds, rather than forcing every query to reinterpret a start and end column. PostgreSQL supplies ranges for types such as integers, numeric values, dates, and timestamps. Operators can test containment and overlap directly. For example, SELECT daterange('2026-01-01','2026-02-01','[)') @> DATE '2026-01-31'; returns true: the lower bound is included and the upper bound excluded. Choosing bound semantics deliberately prevents adjacent bookings from appearing to overlap. Ranges do not automatically forbid overlapping rows; that requires an appropriate constraint or other coordinated enforcement tailored to the resource being booked.
18. What should a candidate know about PostgreSQL UUID values?
The uuid type stores a 128-bit identifier in a native representation instead of treating it as arbitrary text. Generation and storage are separate concerns: PostgreSQL can store valid UUIDs generated elsewhere. PostgreSQL 18 provides uuidv4() and time-ordered uuidv7() generation; older deployments must be checked for the functions actually available. UUIDs can help generate identifiers independently, but uniqueness should still be enforced with a primary key or unique constraint. Time ordering can affect locality, yet it is not a universal performance guarantee. A UUID is also not an authorization token merely because its value is difficult to guess.
19. When is an enum useful, and what is its main modeling trade-off?
An enum defines a named type with a controlled set of labels and an ordering established by the type definition. It fits a relatively stable vocabulary where invalid labels should be rejected consistently. Comparisons use the enum's order rather than alphabetical ordering, which can be useful but must be intentional. The trade-off is schema evolution: removing or rearranging existing values is not as simple as editing a lookup-table row. A lookup table is often a better choice when values need descriptions, localization, lifecycle metadata, or frequent administrative changes. Do not confuse the order of labels with a validated business-state transition workflow.
20. How does a domain differ from an ordinary column constraint?
A domain is a reusable type definition based on another type, with constraints that can be applied across multiple columns. It can centralize a rule such as a positive measurement rather than copying the same expression into every table. A column constraint belongs to a particular table and can naturally express table-specific relationships. Domains are therefore a tool for reusable value semantics, not a replacement for every relational constraint. Null handling needs special care: domain checks can accept NULL, and some query contexts can produce null values despite domain restrictions. Use column-level NOT NULL where the table requires a value.
21. How does a primary key differ from a unique constraint?
A primary key identifies rows with values that must be both unique and non-null. A table has at most one primary key, though that key can span several columns. A table can have multiple unique constraints for other candidate keys, such as an external reference. PostgreSQL normally creates a unique B-tree index to enforce either kind of constraint. By default, a unique constraint permits multiple NULL values because they are treated as distinct for this purpose; NULLS NOT DISTINCT changes that behavior. Select the primary key for stable identity, and add other constraints to express actual business uniqueness rather than relying solely on application checks.
22. What does a foreign key enforce, and what does it not automatically provide?
A foreign key requires applicable referencing values to match an eligible key in the referenced table, preserving referential integrity across concurrent operations. It can also define what happens when a referenced row is updated or deleted, such as rejecting the operation or cascading it. A nullable foreign-key column can still represent an absent relationship unless additional constraints prohibit it. PostgreSQL does not automatically create an index on the referencing columns merely because a foreign key exists. Such an index is often useful for joins and referenced-row changes, but should be justified by workload. Cascades also deserve careful review because one authorized deletion can affect many related rows.
23. Why does CHECK (amount > 0) still allow NULL?
A check constraint rejects a row when its expression is false; an unknown result caused by NULL does not violate the check. Therefore, CHECK (amount > 0) alone does not mean the amount is required. Add NOT NULL when the business rule requires both presence and positivity. This is a useful interview distinction because the expression looks complete while accepting missing data. Checks should generally express rules about the row being inserted or updated, not pretend to coordinate arbitrary changes in other rows. Use the appropriate unique, foreign-key, or exclusion constraint for cross-row relationships rather than a fragile application-time existence test.
24. Why are identity columns usually clearer than serial for new schemas?
An identity column declares sequence-backed value generation as part of the column definition, using SQL-standard syntax. GENERATED ALWAYS and GENERATED BY DEFAULT give different control over explicitly supplied values. serial is older PostgreSQL shorthand that creates an integer column, a sequence, and a default expression. Neither mechanism alone establishes row uniqueness; add a primary key or unique constraint when required. Neither promises gap-free numbering, because allocated sequence values are not generally reclaimed after rollback. Identity is useful for technical row identifiers, but a legally controlled document-numbering process may require a different, explicitly designed workflow rather than assuming a sequence meets that requirement.
25. How do generated columns differ from defaults in PostgreSQL 18?
A default supplies a value when an insert does not provide one; a generated column derives its value from a generation expression rather than accepting an ordinary direct assignment. PostgreSQL 18 supports both stored generated columns, computed on writes, and virtual generated columns, computed when read; virtual is the default in that release. PostgreSQL 16 and 17 supported stored generated columns, so copying an 18 example into those releases needs review. Generated expressions also have restrictions, including immutability requirements and limitations on referenced data. Choose them for deterministic row-derived values, not for changing lookups into unrelated tables or a live clock.
26. When would you use IS DISTINCT FROM instead of the equality operators?
IS DISTINCT FROM compares values while treating NULL as a comparable state, so its result is always true or false. Ordinary equality or inequality can return NULL when an operand is NULL. For example, SELECT NULL IS DISTINCT FROM 5, NULL IS DISTINCT FROM NULL; returns true and false. This is useful when detecting whether a nullable attribute changed: two missing values count as unchanged, while a missing value and a present value differ. The inverse predicate is IS NOT DISTINCT FROM. Do not replace missing values with an arbitrary sentinel merely to compare them; the sentinel may later become legitimate data.
27. What does INSERT ... ON CONFLICT solve?
INSERT ... ON CONFLICT coordinates an insert with a conflicting unique or exclusion condition instead of requiring a separate check-then-insert race. DO NOTHING skips an applicable conflicting insertion, while DO UPDATE provides an upsert against a suitable unique arbiter and can reference the proposed row through excluded. It is not a generic way to suppress every data error: invalid values, foreign-key failures, and unrelated problems still matter. A useful design question is what should happen if the same business event arrives twice. A uniqueness rule and an idempotent update policy are more important than merely knowing the upsert syntax.
28. Why is RETURNING useful after a data modification?
RETURNING obtains values from the rows affected by an INSERT, UPDATE, or DELETE without a separate lookup that might be ambiguous or race with other activity. It is particularly useful for a generated identifier or values changed by the operation. For example, an application's insert can request RETURNING id rather than guessing the latest sequence value across sessions. Returned rows describe the statement's result; they do not mean the surrounding transaction has committed successfully. The application must still handle commit errors and transaction rollback. Request only needed columns, especially when returning broad rows could expose sensitive fields to a less-privileged caller.
29. How does a normal view differ from a materialized view?
A normal view stores a query definition and presents its result when queried; it does not store a separate durable copy of those result rows. A materialized view stores results that are refreshed deliberately, trading freshness and refresh cost for potentially faster reads. Neither is automatically the right answer for an expensive query: a normal view usually does not remove its underlying work, while stale materialized results can violate business expectations. Define the acceptable freshness and refresh ownership before adopting a materialized view. Access-control behavior also deserves explicit review rather than assuming every view is a complete security boundary simply because it hides columns.
30. How should you decide which rules belong in database constraints?
Put durable integrity rules in the database when every writer must obey them, including applications, imports, migrations, and administrative tools. A database uniqueness rule protects against concurrent writers in a way that a preceding application query alone cannot. Keep user-friendly validation in the application too, but treat it as complementary rather than authoritative. Some rules depend on outside services, changing policy, or multi-step workflows and need a broader design. For example, an order's customer reference belongs in a foreign key, while permission to charge an external payment method cannot be proven by that relationship. Choose a constraint whose semantics actually match the rule.
31. What does a transaction guarantee, and what does ACID mean in practice?
A transaction groups database work into a unit that commits or rolls back. ACID summarizes atomicity, consistency, isolation, and durability, but the terms need concrete interpretation. Atomicity avoids a half-applied transfer; consistency depends on declared constraints and correct application rules; isolation depends on the selected isolation level; durability depends on configured persistence guarantees and reliable storage. A transaction does not automatically make an external email or payment API call rollbackable. Keep such side effects coordinated through an intentional application design. Even a single statement executes within a transaction, while an explicit transaction block can group several statements under one commit decision.
32. What is a savepoint, and does it create an independent transaction?
A savepoint marks a position inside a transaction so work after that position can be rolled back without discarding all earlier work. It is not an independently durable nested transaction: the outer transaction still determines whether retained changes commit. Savepoints are useful for a controlled optional step, but repeated failures should not be hidden without explaining the business outcome. The following session-only demonstration creates no application data:
After a statement error inside an explicit transaction, ordinary work cannot simply continue until the failed transaction state is cleared by rollback, potentially to an appropriate savepoint.
33. What does MVCC mean, and why are old row versions needed?
Multiversion concurrency control, or MVCC, lets PostgreSQL decide which row versions are visible to a query's snapshot. An update can leave an older version that remains necessary for a reader whose snapshot predates the change. Consequently, ordinary reads and writes can often proceed without blocking each other through row-level read locks. This is not a promise of a lock-free system: writers can conflict, and schema operations have their own locking requirements. Retaining obsolete versions also creates cleanup work. Understanding snapshots explains why a query can legitimately see an earlier committed value while another transaction has already produced a newer version.
34. What is PostgreSQL's default isolation level?
The default isolation level is Read Committed. A plain query sees committed data as of the start of that statement, together with applicable changes made earlier by its own transaction. Two successive queries in the same transaction can therefore see different committed data if another transaction commits between them. PostgreSQL treats Read Uncommitted as Read Committed rather than allowing dirty reads. This is enough for many operations, but it does not make a multi-statement business decision immune to concurrent changes. A candidate should explain the required invariant before choosing stronger isolation, explicit locking, or an atomic conditional statement to protect it.
35. What is an index, and why should a table not have an index on every column?
An index is an additional access structure that can help PostgreSQL locate relevant rows or provide a useful ordering without scanning an entire table. The default B-tree suits many equality and range conditions, but an index is not a guarantee that every query will use it. Indexes consume storage and add maintenance work to writes, and some queries are cheaper as sequential scans. Start from important predicates, joins, ordering requirements, and measured plans instead of indexing every column. A low-selectivity flag on a small table is a good interview example: the existence of an index does not establish that using it is efficient.
36. How do temporary and unlogged tables differ?
A temporary table is session-local and can have transaction-end behavior controlled by ON COMMIT; it is useful for work that should not become shared durable application state. An unlogged table is a persistent catalog object accessible across sessions, but changes to its contents are not WAL-logged in the ordinary way. Its data is truncated after a crash or unclean shutdown and is not replicated to physical standbys. That makes unlogged storage a deliberate durability trade-off, not a free acceleration switch. Choose it only for data that can be recreated safely, and distinguish it from a temporary table when planning cleanup and concurrent access.
37. How are PostgreSQL users and groups represented?
PostgreSQL represents both users and groups through roles. A role with LOGIN can serve as a login identity; another role can collect privileges and be granted to other roles. This supports a separation between individual authentication identities and reusable access bundles, such as a reporting role. Role membership has behavior beyond merely listing names, so inherited privileges and the ability to assume a role should be reviewed explicitly. Avoid giving an application superuser privileges to solve an access error. A good access model uses the smallest privileges needed for its workload and separates routine application access from ownership and administrative capabilities.
38. Why can a role have SELECT on a table and still fail to read it?
Access can depend on several permission layers rather than a single table grant. The role generally needs to connect to the database, use the containing schema, and have the necessary privileges on the table or columns. Row-level security can further restrict which rows are visible. If an operation inserts a value through a sequence-backed default, sequence permissions can also matter depending on the mechanism and ownership context. Diagnose the actual error, active role, object owner, schema privileges, and applicable policies before expanding access. Granting every privilege or switching to superuser may hide the missing prerequisite while creating a much larger security problem.
39. What is a PostgreSQL extension, and why is installing one a controlled change?
An extension packages related database objects, such as functions, types, operators, or supporting metadata, into a managed unit. It can add capabilities without making those capabilities part of every PostgreSQL installation. Availability depends on installed server files, the hosting environment, and privileges; some extensions also require configuration or preload steps. CREATE EXTENSION is therefore a database change, not merely an application import statement. Review the extension's trust model, supported PostgreSQL version, upgrade path, and operational ownership before deployment. Do not assume an extension used in a developer environment is permitted or identically configured in a managed production service.
40. How do parameterized queries help keep PostgreSQL application access safe?
Parameterized queries send SQL structure separately from data values, allowing the driver and server to handle values without treating their contents as executable SQL. For example, a query shaped as SELECT id FROM customer WHERE email = $1 can bind an email value through a compatible driver API rather than concatenate it into the text. Placeholder notation varies by driver, so use its documented interface. Parameters do not replace authorization, input validation, or safe handling of dynamic identifiers. Table names and sort directions cannot simply be supplied as ordinary value parameters; use fixed choices or vetted identifier-construction mechanisms for those structural elements.
PostgreSQL Interview Questions and Answers for Intermediate
The intermediate answers focus on mechanisms that affect correctness and performance. Examples are deliberately small; actual plans and maintenance costs depend on data, statistics, settings, and workload.
41. When does a Repeatable Read transaction acquire its snapshot?
Its snapshot is established at the start of the first non-transaction-control statement, not merely by issuing BEGIN. If a session begins a Repeatable Read transaction, pauses, and then makes its first query, changes committed by other sessions before that query can be visible. Later queries retain that snapshot while also seeing the transaction's own prior writes. This timing matters when coordinating a multi-query report with an application event: opening a transaction alone does not pin the report to that instant. Keep the reporting transaction bounded because a retained snapshot can delay cleanup. A stable snapshot also does not guarantee a serializable business outcome; conflicting updates can still require a whole-transaction retry.
42. How does Repeatable Read differ from Serializable in PostgreSQL?
Repeatable Read provides a stable transaction snapshot and prevents phantom reads in PostgreSQL, but it can still permit serialization anomalies. Serializable additionally detects dangerous read/write dependency patterns and may reject a transaction so committed results correspond to a serial execution. Consider two operators who each see that the other is on duty and then independently go off duty. A stable snapshot alone does not preserve the rule that someone remains available. Serializable can protect that decision when all relevant transactions participate appropriately, but the application must retry failures. Neither level should be selected without considering transaction duration and contention.
43. What is write skew, and how would you prevent it?
Write skew occurs when concurrent transactions read overlapping facts but update different rows, producing a combined result that violates an invariant. For example, two approval transactions each observe sufficient total reserve and debit separate accounts, together crossing a required minimum. A unique constraint on either account does not enforce the cross-row rule. Options include Serializable transactions with retries, a deliberately shared coordination row locked before checking the invariant, or a schema that expresses the rule with enforceable constraints. Choose a design that covers every writer; adding a lock to only one code path leaves the invariant exposed.
44. What should an application retry after a serialization failure?
It should retry the entire transaction, including the reads and application decisions that selected its writes, rather than merely repeat the failed statement. SQLSTATE 40001 identifies a serialization failure. A bounded retry loop with backoff and jitter can reduce synchronized conflicts, but it must also respect the request deadline. External side effects require special care: do not resend a payment or email blindly on each attempt. Use an idempotency strategy or an outbox committed with database state. Preserve the original error and retry metrics so persistent contention is diagnosed rather than hidden behind endless retries.
45. When is SELECT FOR UPDATE appropriate?
Use SELECT ... FOR UPDATE when a transaction must reserve existing rows against conflicting changes while making a related decision. For a lab inventory table, lock the item, check quantity, and perform the update within the same short transaction. The lock is not a permanent reservation and is normally released when the transaction ends. It does not lock an absent row or every possible future row matching a predicate, so it cannot alone enforce arbitrary cross-row rules. Prefer a single conditional update when it expresses the invariant more simply, and avoid holding row locks while waiting for human input or external services.
46. How do table-level locks differ from row-level locks?
Table locks coordinate operations on a relation, while row locks coordinate conflicting operations on particular rows. Ordinary reads and writes acquire table locks as well as any relevant row locks. The names can mislead: ROW EXCLUSIVE is a table-level mode, not a lock on one row. Most normal reads remain compatible with ordinary data changes, whereas an ACCESS EXCLUSIVE lock conflicts with ordinary reads too. A migration can therefore wait behind a long transaction even when it changes little data. Before scheduling DDL, identify its exact lock mode, expected duration, and timeout strategy rather than treating all schema changes alike.
47. How does a deadlock differ from ordinary lock waiting?
Ordinary waiting has a possible path forward when a holder finishes; a deadlock contains a cycle of transactions waiting on one another. If transaction A locks account 1 then requests account 2 while B does the reverse, neither can continue. PostgreSQL detects deadlocks and aborts one participant; which participant loses should not be an application assumption. Acquiring objects in a consistent order and keeping transactions short reduces risk. The application should roll back and, where safe, retry the entire unit of work. A lock timeout bounds waiting but does not replace a consistent locking design or explain the cycle.
48. When are advisory locks useful, and what are their limitations?
Advisory locks coordinate application-defined resources that do not map neatly to a row, such as one maintenance job per tenant. PostgreSQL manages the lock, but the meaning of its key and compliance with the protocol belong to the application. Transaction-level advisory locks are released automatically at transaction end; session-level locks can survive transaction rollback and must be managed carefully, especially with connection pools. Design a collision-free key convention and consistent acquisition order. Advisory locks do not prevent an unrelated writer from changing the underlying data unless that writer also participates, so they are not substitutes for database constraints.
49. Why is SKIP LOCKED useful for job queues but risky for ordinary reporting?
SKIP LOCKED allows a worker to skip rows another transaction has locked rather than wait, which helps multiple consumers claim different jobs. A claim should update the job state within the same transaction that selects and locks it. The resulting view intentionally omits some otherwise qualifying rows, making it unsuitable for a complete financial total or general consistency check. Design leases, retries, and idempotent processing for worker crashes; releasing a database lock does not prove an external job completed. Ordering and starvation also need consideration because repeatedly locked work may be skipped for a long time.
50. How can a conditional UPDATE avoid a read-then-write race?
Put the condition and change into one statement so PostgreSQL evaluates the operation while coordinating conflicting writers. In an isolated inventory example, assume item_id is a primary key and available is a non-null integer:
One returned row means the statement decremented one item, but the reservation becomes committed only when the transaction commits; no returned row means the item was absent or did not satisfy the condition. At Read Committed, a competing update can cause waiting and a recheck against the updated row. Add a suitable constraint as defense in depth. This pattern handles a single-row rule, not an arbitrary invariant across multiple rows or external systems.
51. How should you choose the column order of a multicolumn B-tree index?
Choose an order from the actual predicates, ordering requirements, and workload rather than a universal rule such as putting the most selective column first. For an index on (tenant_id, created_at), equality on the tenant followed by a time range gives a useful contiguous search interval. A query only on time may need a different access path. PostgreSQL 18 can sometimes use skip scan for missing leading equality conditions, but that does not make column order irrelevant. Compare representative plans and write overhead before adding another index; two overlapping indexes can cost more than their marginal read benefit.
52. What does PostgreSQL 18 B-tree skip scan change?
Skip scan can perform repeated searches using internally generated equality conditions on a missing leading column, exploiting a condition on a later column. For an index on (region, order_id), a selective order predicate may benefit when there are only a few distinct regions. The planner decides whether those searches are cheaper than scanning more of the index or table. This is a PostgreSQL 18 capability, not a reason to assume identical plans on 16 or 17. It is also not a promise that every suffix-only query becomes fast: leading-column cardinality, selectivity, statistics, and competing indexes still matter.
53. When is a partial index more useful than a full index?
A partial index stores only rows satisfying a predicate, which can reduce maintenance and storage when queries focus on a small, stable subset. For a disposable queue example:
Queries must have conditions the planner can recognize as implying the index predicate. A generic parameterized predicate such as status = $1 cannot always provide that proof during planning. The index also incurs maintenance when jobs enter or leave the subset. Evaluate how the subset evolves; if nearly every row is ready, the size advantage may disappear and a different index may serve more queries.
54. What problem does an expression index solve?
An expression index stores the result of an expression so matching searches can avoid computing it for every candidate row. A lab index on lower(email) can support a query using the same lowercased expression, and a unique expression index can enforce the corresponding normalized uniqueness rule. The functions used must satisfy PostgreSQL's immutability requirements for index expressions. This is a semantic commitment: the chosen normalization must match the application's intended equality, including collation considerations. Expressions add computation and index maintenance to writes, so they should solve a demonstrated search or integrity requirement rather than decorate every computed field.
55. Why can an index-only scan still perform heap fetches?
The index can contain every value a query needs while still lacking enough information to prove a row is visible to that query's MVCC snapshot. PostgreSQL checks the heap page's visibility-map bit; if the page is not marked all-visible, it must visit the heap. Thus an Index Only Scan plan label does not guarantee zero heap access. Inspect the actual heap-fetch count on an appropriate test workload and consider recent writes and vacuum progress. Adding INCLUDE payload columns may enable the plan but cannot make heavily changing pages permanently all-visible, and wide payloads increase index size and write cost.
56. How do you choose a GIN operator class for a jsonb index?
Choose it from the operators the application actually uses. The default jsonb_ops supports key-existence operators as well as containment and supported JSON-path searches; jsonb_path_ops supports a narrower operator set and can offer smaller, more specific index entries for its supported searches. Neither choice accelerates every expression involving JSON. For instance, extracting a scalar and comparing it may call for a targeted expression index instead. Test realistic documents and predicates, including empty structures and common keys. Index selectivity, write amplification, and query flexibility are competing concerns; the smaller index is not automatically the better application design.
57. When would GiST be preferable to a B-tree index?
GiST is an extensible indexing framework useful for data and predicates that do not fit ordinary scalar ordering, such as range overlap or supported nearest-neighbor searches. A scheduling system can use range types and an appropriate GiST-backed exclusion constraint to reject overlapping reservations for a resource. The operator class determines the available semantics and whether lossy matches require rechecks. B-tree remains a natural choice for ordinary equality, ordered ranges, and sorting of scalar keys. Do not select GiST merely because it sounds more general: match the operator, data distribution, and integrity requirement, and benchmark the actual access pattern.
58. Why can a BRIN index be effective on a very large table?
BRIN summarizes ranges of heap pages rather than storing a separate search entry for every row, so it can remain small on a large table. It works especially well when a searched value correlates with physical row placement, such as mostly time-ordered event ingestion. A time predicate can then exclude many page ranges, with matching ranges rechecked during execution. Randomly distributed values weaken that filtering. The pages_per_range choice trades summary size against precision, and new ranges must be summarized. BRIN is not a tiny substitute for a selective B-tree point lookup on every workload; its benefit comes from skipping irrelevant physical regions.
59. What are index operator classes and collations responsible for?
An operator class tells an index access method which comparison or search operations apply to a data type; a collation supplies relevant text comparison rules. An index is useful only when the query's operators and semantics are compatible with that definition. Pattern matching under a locale may need a suitable pattern operator class, while JSON and range indexes have their own supported operators. Treat changes in collation behavior as an integrity and maintenance concern, not merely a display setting. Validate equality, ordering, and uniqueness assumptions with representative values, particularly when an operating-system or collation-library upgrade changes the underlying rules.
60. What trade-offs come with CREATE INDEX CONCURRENTLY?
It allows ordinary writes to continue during most of the build, unlike a conventional index build that blocks them, but performs more work and can wait for relevant transactions. It cannot run inside a transaction block. Failure can leave an invalid index that still consumes storage and may create maintenance overhead; a unique concurrent build also has enforcement nuances during its later phases. Use it only through an authorized migration plan with monitoring, disk headroom, and a recovery procedure. “Concurrently” does not mean impact-free: CPU, I/O, waits, and interactions with other maintenance can still affect production latency.
61. How should you read an EXPLAIN plan without confusing cost with time?
Read the plan as a tree of operations and distinguish estimated startup cost, total cost, rows, and row width. Costs are planner units, not milliseconds, and a parent's cost includes work from its children. Start by understanding how rows enter the plan, where filtering happens, and whether joins multiply them. Then compare candidate paths using the same settings and statistics. A high estimated cost does not alone prove a production incident, and a low estimate can be wrong. Use representative inputs and measured execution evidence before changing indexes or planner configuration to satisfy a visually appealing plan.
62. What extra evidence does EXPLAIN ANALYZE provide, and what is its risk?
EXPLAIN ANALYZE executes the statement and reports measured behavior, including actual rows, timing, and loop counts. For a repeatedly executed node, row and timing figures are reported per loop, so consider the loop count before judging its total contribution. This execution is the main safety caveat: a modifying statement really modifies data, and even a read can invoke side-effecting functions or create heavy load. Prefer an isolated test database and representative data. A transaction rollback is not a universal safety boundary because sequence changes and external effects need not roll back. Ordinary EXPLAIN estimates without running the statement.
63. What can EXPLAIN BUFFERS tell you about a slow query?
Buffer statistics show block activity such as shared-buffer hits and reads, helping distinguish repeated data access from efficient filtering. A shared read means a block was brought into PostgreSQL's shared buffers; it does not prove a physical disk read because the operating system may have cached it. Counts can also include repeated accesses rather than distinct blocks. Compare equivalent runs with awareness of cache warming, temporary-file activity, and concurrent load. Parent-node figures include child work, so summing every node double-counts activity. Combine buffers with row estimates, actual rows, loops, and relevant I/O timing rather than declaring every cache miss the root cause.
64. How do inaccurate row estimates lead to bad execution plans?
The planner uses cardinality estimates to price scans, joins, sorts, and memory demands. Underestimating a filtered outer relation can make many indexed nested-loop lookups appear cheap; the actual query may perform millions. Overestimation can discourage an otherwise efficient selective path. Find the first major estimate divergence near the leaves instead of blaming only the final expensive node. Check stale or insufficient statistics, skew, correlated predicates, expressions, and parameter sensitivity. An index may reduce access cost without correcting the estimate, so inspect the model of the data as well as the available access paths before choosing a fix.
65. When are extended statistics useful?
Extended statistics capture selected relationships among columns or expressions that ordinary per-column statistics cannot represent adequately. Dependencies can help with correlated restrictions, multivariate distinct counts can improve grouping estimates, and multivariate most-common-value statistics can describe common combinations. For a lab table:
The object must be populated before it helps. These statistics are not a universal model of every relationship and PostgreSQL 18 does not generally use them to estimate join selectivity across tables. Add them for an observed estimation problem, then verify the relevant estimate improves on representative inputs.
66. How do nested-loop, hash, and merge joins differ?
A nested loop probes an inner path for each outer row, which can be excellent when the outer side is small and the inner lookup is selective. A hash join builds a hash structure for one side and probes it with the other, commonly fitting larger equality joins. A merge join consumes compatible ordered streams and can benefit from existing order, though sorting may be necessary. No method is universally best. Compare input cardinalities, available indexes, required ordering, memory, and spill behavior. Disabling a join method can be a controlled diagnostic experiment, not a durable substitute for fixing estimates or query design.
67. Why can a prepared statement be fast for one parameter and slow for another?
A custom plan can use particular parameter values, while a generic plan must work without tailoring to each value. Skew makes that distinction important: one tenant may own ten rows and another millions, making different access paths economical. PostgreSQL's automatic plan selection weighs the cost of custom planning against a reusable generic plan. Inspect representative executions and plan behavior rather than assuming preparation always produces one optimal plan. A controlled session-level comparison can establish parameter sensitivity, but a global forced-planning setting may increase planning overhead elsewhere. Also distinguish server-side prepared statements from what a particular client library calls preparation.
68. Does a WITH query always act as an optimization fence?
No. PostgreSQL can fold a non-recursive, side-effect-free CTE into its parent query, particularly when referenced once, allowing joint optimization. Multiple references commonly favor materialization, while MATERIALIZED and NOT MATERIALIZED can influence eligible cases. The trade-off is avoiding repeated computation versus allowing filters and other optimizations to move into the underlying query. An expensive function referenced twice may benefit from materialization, whereas a large intermediate result may benefit from pushed-down restrictions. Check volatility and semantic requirements before rewriting. Do not transfer behavior from old PostgreSQL releases or from data-modifying CTEs to every ordinary WITH expression.
69. Why is work_mem not a per-connection memory limit?
work_mem is a base allowance for individual query operations such as sorts and hash tables, not one fixed allocation shared by an entire connection. A plan can contain several such operations, concurrent sessions multiply demand, and parallel execution can add further memory consumers. Hash operations also involve hash_mem_multiplier. Consequently, raising the global value to eliminate one spill can exhaust memory under peak concurrency. Estimate simultaneous operations, not merely connection count. Compare the latency benefit against a realistic memory budget, and test targeted settings for an approved workload instead of treating all temporary-file use as unacceptable.
70. Why might a query fail to benefit from parallel execution?
Parallel work has setup, coordination, and result-transfer overhead, so a small or highly selective query may run faster serially. The planner also considers whether operations are parallel safe and whether the work can be divided effectively. Worker availability can differ from the number planned, and a serial portion or leader bottleneck can limit improvement. Examine planned versus launched workers and where time is spent on a representative test. Adding workers can increase total resource pressure while barely improving elapsed time. Evaluate throughput and latency under realistic concurrency rather than extrapolating from one isolated scan benchmark.
71. Why does ordinary VACUUM usually not shrink a table file substantially?
Ordinary VACUUM mainly makes space occupied by removable dead tuples available for reuse inside the relation; it does not generally rewrite the table into a smaller file. It may truncate empty pages at the end when conditions permit, but that is different from compacting all internal free space. VACUUM FULL rewrites the relation and requires stronger locking and additional disk capacity, so it is not a routine substitute. Evaluate whether reusable space will serve future writes before pursuing physical shrinkage. A stable large file can be healthy, while a smaller file achieved through disruptive maintenance may soon grow again.
72. How should you reason about autovacuum thresholds on a large, busy table?
Autovacuum scheduling considers table activity and configured thresholds, including fixed thresholds and scale factors; different triggers apply to vacuuming and analyzing. A scale factor that is acceptable for a small table may allow a large backlog on a huge relation. Inspect actual vacuum progress, dead-tuple estimates, transaction age, workload churn, and worker capacity before changing settings. Per-table tuning can be more appropriate than a global change. The goal is sustained cleanup and statistics freshness without overwhelming foreground traffic, not simply the smallest threshold. Autovacuum cannot remove versions still needed by an old snapshot, regardless of how aggressively it is scheduled.
73. What enables a HOT update, and why does it matter?
A heap-only tuple update can avoid new entries in ordinary indexes when the update does not change columns those indexes reference and the new tuple fits on the same heap page. PostgreSQL has a special allowance for summarizing indexes such as BRIN, so the simplistic rule “no indexed column may ever change” is too broad. HOT reduces index maintenance and supports efficient cleanup of version chains. Lower table fillfactor can leave space for updates, but increases initial storage and does not guarantee HOT eligibility. Measure update patterns and HOT statistics before changing storage settings; an extra expression or payload index can alter the trade-off.
74. Why must PostgreSQL freeze old transaction IDs?
Transaction IDs have a finite range, so PostgreSQL must prevent old row versions from eventually being mistaken for future transactions as IDs wrap around. Vacuum performs freezing work to preserve correct visibility over that lifecycle. This is an integrity requirement, not merely an optional performance optimization. Monitor transaction ages and investigate factors that impede progress, including old transactions and retention mechanisms. Aggressive anti-wraparound work can occur even when ordinary workload tuning would prefer less maintenance. Do not respond by disabling protection or deleting internal files; the safe response is to establish what is holding back cleanup and follow an authorized recovery plan.
75. What is TOAST, and how can it affect query performance?
TOAST handles large variable-length values by compressing them and, when necessary, storing parts outside the main heap tuple. This lets wide values such as large text or JSON documents fit PostgreSQL's page-oriented storage model. It does not make reading those values free: fetching, decompressing, transmitting, and parsing them can dominate a query that appears to return few rows. Avoid selecting a large document when the request needs only small identifying fields. Measure actual payload sizes and access patterns before splitting a schema or changing storage settings. An unchanged external value can be reused during an update, so not every update rewrites every large field.
76. What makes a good partition key in PostgreSQL?
A good partition key aligns with important filtering or lifecycle operations and produces manageable partitions. Time can work well when retention removes whole periods and queries constrain those periods; tenant partitioning may help a different workload but risks imbalance. Partitioning does not automatically improve every query and adds planning, operational, and schema-management costs. Consider future partition creation, default-partition behavior, skew, and what happens when a row's partition key changes. Compare the design with a nonpartitioned table using appropriate indexes. The purpose should be measurable maintenance or access benefits, not merely reaching an arbitrary row-count threshold.
77. Why can a partitioned table's unique constraint require the partition key?
PostgreSQL's partitioned-table uniqueness relies on indexes in individual partitions rather than a general global unique index. A primary key or unique constraint on the partitioned parent must therefore include all partition-key columns, with additional restrictions when partition keys use expressions. That lets the partition boundaries prevent duplicate keys from hiding in different partitions. For example, uniqueness of (event_date, event_id) is not the same business rule as uniqueness of event_id alone. Resolve that mismatch during schema design through an appropriate key strategy or separate coordination mechanism, rather than silently weakening the application invariant to fit the partition layout.
78. How does partition pruning differ from using an index?
Partition pruning eliminates partitions whose bounds cannot satisfy a query; an index narrows access to rows within a remaining partition. They can work together, and a pruned query may still scan each surviving partition sequentially when that is cheapest. PostgreSQL can prune during planning and, in supported cases, execution when parameter values become available. Predicates must expose useful constraints on the partition key, so transformations and expression choices matter. Inspect the plan and runtime evidence instead of assuming a date-looking filter guarantees pruning. Too many tiny partitions can add overhead even when the final number of matching rows is small.
79. What concurrency edge cases matter for INSERT ON CONFLICT?
The conflict check and the statement snapshot are not interchangeable. At Read Committed, ON CONFLICT DO NOTHING can skip an insertion because of a concurrently committed conflicting row that was not visible to the statement's snapshot. A following statement can obtain a new snapshot, but a same-statement fallback read should not be assumed to find that row. DO UPDATE has its own atomic insert-or-update behavior, subject to conditions and other errors. Design get-or-create flows around these semantics, meaningful unique keys, and retry logic. Also deduplicate a batch that would otherwise attempt to update the same target row more than once.
80. When should a PostgreSQL trigger enforce behavior instead of application code?
A trigger is useful when behavior must run consistently for every writer, such as maintaining a database-local audit record or a derived relationship that declarative constraints cannot express. Prefer constraints for rules they can represent, because they are clearer and better integrated with integrity enforcement. Understand row-level versus statement-level execution and BEFORE versus AFTER timing. Hidden per-row queries can make bulk COPY or updates unexpectedly expensive, and recursion or external side effects complicate failures and retries. Keep trigger logic inspectable, test multirow statements, and document privilege context rather than assuming an application-only test covers every database entry point.
PostgreSQL Interview Questions and Answers for Experienced
The experienced answers describe investigation sequences and decision criteria rather than universal tuning recipes. Preserve evidence, identify the blast radius, and validate recovery and rollback assumptions before intervening.
81. How would you investigate a sudden increase in PostgreSQL response time?
Separate database execution time from connection acquisition, network time, and application processing before changing settings. Establish when the regression started and whether it affects all queries, one query family, or only peak concurrency. Compare active sessions and wait events, blocker chains, host CPU and storage latency, and recent deployment or data-volume changes. Use normalized query statistics if already available to distinguish more calls from slower calls. Capture a representative plan safely; an expensive EXPLAIN ANALYZE can worsen an incident because it executes the statement. A useful first diagnostic is a read-only snapshot of activity, but take repeated observations rather than treating one snapshot as proof. Activity details for other roles can be hidden without authorized monitoring privileges; an empty result is not proof of an idle server. Prioritize reversible mitigations with an owner and rollback condition, then verify application-level latency improved.
82. Why can increasing max_connections make an overloaded PostgreSQL service slower?
More connections can admit more competing work without increasing CPU, memory, or storage capacity. PostgreSQL backends consume resources, and concurrent sorts, hashes, and parallel workers can multiply memory demand. When the server is already saturated, allowing another wave of queries can increase contention and tail latency rather than throughput. Measure connection utilization, active work, queueing, transaction duration, and resource saturation before selecting a limit. A pool can bound active database work, but its queue also needs deadlines and observability. Keep operational access available and prevent retry storms. For example, a service with hundreds of mostly idle sessions may benefit from pooling, whereas a handful of CPU-heavy queries needs query or capacity work. A higher connection ceiling is not a substitute for admission control.
83. What application behavior must you verify before adopting transaction pooling?
Verify that the application does not assume it owns one server session across transactions. Session settings, temporary objects, session-level advisory locks, and some prepared-statement workflows need deliberate handling; exact support depends on the pooler and its configuration. Prefer transaction-local settings for request-specific context, and test role changes and tenant identity so one request cannot inherit another request's state. Also examine connection initialization hooks, cancellation behavior, migration tools, and long-running transactions. Build an integration test that routes consecutive requests through different backends and checks both correctness and isolation. Transaction pooling can reduce idle backend overhead, but it cannot shorten transactions or remove query bottlenecks. Preserve a suitable direct or session-pooled path for administrative jobs that legitimately require persistent session semantics.
84. How would you estimate the risk of increasing work_mem?
Estimate concurrent memory-consuming operations, not just concurrent connections. work_mem is a budget for operations such as sorts, while hash operations can use a multiple controlled by hash_mem_multiplier; one plan can contain several operations and parallel execution can multiply demand. As a rough illustration, 20 simultaneous queries with two 64 MiB sorts each imply 2,560 MiB of sort budgets before shared buffers, backend overhead, hashes, and other processes. This is a planning estimate, not a reservation or a universal upper bound. Inspect plans and temporary-file activity, then test a targeted session-level adjustment against representative concurrency. A setting that makes one report faster can cause memory pressure when every report runs together. Avoid treating total RAM divided by connection count as a safe formula.
85. How can you distinguish storage pressure from a low PostgreSQL cache-hit ratio?
A cache-hit ratio alone does not establish physical storage traffic or its latency. PostgreSQL buffer misses can still be satisfied by the operating-system cache, and large sequential workloads can legitimately have lower buffer hit rates. Correlate PostgreSQL I/O observations with host storage throughput, latency, queue depth, working-set size, and the timing of affected queries. Compare interval deltas rather than lifetime counters, noting statistics resets. Determine whether pressure comes from reads, writes, WAL, temporary spills, or maintenance, because each suggests a different response. An index change that reduces pages read may help a selective query; allocating more shared memory may not help a streaming scan. PostgreSQL 18's asynchronous I/O changes some execution behavior, but it does not make storage capacity or workload measurements unnecessary.
86. What evidence would connect periodic latency spikes to checkpoints?
Look for a repeatable relationship between latency spikes, checkpoint activity, write throughput, and WAL generation rather than assuming every periodic stall is a checkpoint. Inspect checkpoint logs and the relevant statistics for the installed version, then compare the workload during those windows. Check whether bursts are caused by requested checkpoints, sustained write pressure, or another scheduled task. Increasing the interval or WAL budget can spread some work and reduce checkpoint frequency, but may increase crash-recovery work and disk requirements; it is not a free speed improvement. Preserve durability settings and validate changes in a representative environment. A senior answer should include a before-and-after observation window and a rollback threshold, because storage saturation can remain even when checkpoint scheduling changes.
87. How do you discuss asynchronous commit without confusing it with disabling durability safeguards?
Asynchronous commit allows success to be reported before the transaction's WAL has been flushed locally, so a crash can lose recently acknowledged transactions. It does not mean the same thing as disabling fsync, which can jeopardize the database's ability to recover consistently after an operating-system or hardware failure. The choice must follow the data-loss contract: disposable telemetry and a financial ledger usually have different requirements. Consider a narrowly scoped transaction setting only after an authorized durability review and failure testing, not as an incident shortcut. Also distinguish local WAL flush from synchronous replication acknowledgement; they protect against different failures. A useful interview follow-up asks what the application tells a user after success and whether it can safely reconstruct a lost acknowledged operation.
88. How would you diagnose table growth after heavy updates without immediately rewriting the table?
First distinguish live-data growth, reusable free space, table bloat, index growth, and TOAST growth. Compare relation sizes over time with update/delete rates, vacuum progress, and estimates of live and dead tuples, recognizing that estimates are not an exact bloat measurement. Check whether old snapshots or replication-related retention prevent cleanup. Ordinary vacuum makes space reusable but generally does not compact the whole relation into a smaller file. A rewrite can require substantial extra disk, strong locks, and recovery planning, so it should follow evidence and an approved maintenance plan. Fix the cause as well as the symptom: excessive updates, poor fillfactor for an update-heavy table, or inadequate maintenance can recreate the growth. Measure whether reclaimed space is actually needed before choosing a disruptive operation.
89. Why are idle-in-transaction sessions operationally dangerous?
An idle-in-transaction session has stopped issuing work without ending its transaction. It can retain locks, and an old snapshot or transaction horizon can interfere with cleanup, while the connection remains occupied. Diagnose the session's transaction age, state, wait information, and application ownership rather than killing every idle connection; ordinary idle sessions are different. Review application error paths, connection-pool cleanup, and code that waits on a remote service inside a transaction. A scoped idle_in_transaction_session_timeout can provide a safety net, but clients must handle the disconnect and rollback correctly. Before terminating a production session, understand its business operation and obtain the required operational authorization. The lasting fix is bounded transaction lifetime and reliable commit-or-rollback handling, not periodic indiscriminate cancellation.
90. How would you respond to an approaching transaction-ID wraparound limit?
Treat it as a data-availability incident and investigate the oldest transaction horizons, affected databases and tables, vacuum progress, and blockers. Transaction-ID age is not wall-clock age; a busy system can consume its safety margin quickly. Determine whether long transactions, prepared transactions, replication state, or inadequate maintenance are delaying progress, and involve the responsible owners before changing or removing them. Allow protective maintenance to do useful work and follow a tested, version-appropriate incident procedure. Do not disable autovacuum, delete database files, or use counter-resetting tools as a routine remedy. Monitor the age trend and successful freezing, not merely that a vacuum process exists. After recovery, adjust per-table maintenance capacity and alerting so the team has time to act before PostgreSQL's protective restrictions threaten service.
91. How would you choose physical versus logical replication for a project?
Choose physical replication when the requirement is a close standby of the whole cluster for recovery or read scaling within compatible PostgreSQL versions. Choose logical replication when selected tables, a migration across supported major versions, or a different destination layout is central to the requirement. Neither choice removes application-level cutover work. Evaluate initial copy size, ongoing change volume, schema management, replica identity, sequence handling, failover, and validation. For example, a selective reporting subscriber may fit logical replication, while a standby intended to replace a failed primary usually fits physical replication. Test the exact versions and features involved. Avoid promising that logical replication duplicates every database object or that physical replication permits arbitrary cross-major-version recovery.
92. Why can a replication slot endanger disk capacity even when the database is healthy?
A slot can retain WAL needed by its consumer, so a stalled or abandoned consumer can cause retained WAL to accumulate. Logical slots can also hold horizons relevant to cleanup. Inspect the slot's activity, restart position, consumer progress, and retention limits alongside filesystem headroom; a connected consumer is not necessarily keeping up. Alert on growth rate and estimated time to exhaustion, not just current bytes. Retention limits can protect storage but may make a lagging slot unusable, requiring consumer recovery or resynchronization. Never drop an unfamiliar slot merely to clear space: identify its owner and downstream guarantees first. A sustainable design includes ownership, lag budgets, failure handling, and retirement procedures for consumers, including temporary migration jobs that are easy to forget.
93. How would you diagnose replication lag without relying on one lag number?
Break the path into WAL generation, transport, durable receipt, and replay. A standby may receive data quickly but replay it slowly, or it may be starved by network or storage pressure before replay begins. Compare LSN positions and their trends, sender and receiver state, replay activity, and host resources. Time-based lag fields are not a universal estimate of catch-up time and can be misleading during idle periods. For a business freshness requirement, measure an application-visible heartbeat or relevant data watermark as well. If the primary generates 80 MiB/s and the standby can replay only 60 MiB/s during a sustained workload, the backlog grows by about 20 MiB/s. Faster networking alone would not solve that replay-capacity deficit.
94. Why can a read query be canceled on a hot standby?
Recovery must apply changes from the primary, and an executing standby query can conflict with that replay. For example, cleanup records can invalidate row versions needed by a query's snapshot. The standby may wait within configured limits and then cancel conflicting queries so recovery can proceed. Diagnose the conflict type and query duration rather than labeling every cancellation a network failure. hot_standby_feedback can reduce some cleanup conflicts but can increase dead-row retention on the primary; it is not a cure for all recovery conflicts. Decide whether the replica prioritizes fresh failover readiness or long analytical reads. A dedicated reporting system may need a different design from the standby responsible for a strict recovery-time objective.
95. What must happen around promotion to avoid split-brain failover?
Promotion alone is insufficient: the old primary must be prevented from accepting authoritative writes, clients must move to the selected new primary, and the team must know which acknowledged transactions survived. A safe design includes failure detection, quorum or another reliable decision process, fencing, routing changes, and procedures for rejoining the old server. A network partition can make two healthy machines appear failed to each other, so reachability from one observer is not enough evidence. Test failover with partial network failures, delayed clients, and retrying applications rather than only shutting down a host cleanly. PostgreSQL supplies replication and promotion mechanisms, but a complete high-availability system requires operational coordination around them and a clear authority model.
96. How do synchronous replication settings change a durability and availability decision?
Synchronous replication can make commit wait for specified standby acknowledgements, exchanging additional latency and possible blocking for stronger protection against some primary failures. The exact guarantee depends on which standbys are required and the acknowledgement level: durable receipt is different from replay visibility. remote_apply waits for replay on the required synchronous standby, while other synchronous commit settings provide different milestones. Losing all eligible synchronous standbys can stall commits unless the architecture has an authorized fallback. Define whether the business prefers temporary write unavailability or accepting a larger data-loss window, and test that decision. Geography, quorum selection, failover eligibility, and client routing all matter; enabling a setting by itself is not a universal zero-data-loss promise.
97. How would you translate RPO and RTO into a PostgreSQL recovery design?
RPO specifies how much data loss is acceptable, while RTO specifies how long service restoration may take. Translate both into measurable failure scenarios: losing one node, a storage system, an entire region, or credentials to the backup account are different events. Replication can reduce recovery time, while independently retained backups address accidental deletion and other replicated mistakes. Include detection, decision-making, provisioning, WAL replay, validation, application reconnection, and traffic restoration in the time budget. A five-minute RTO is not demonstrated by a four-minute database startup if routing and verification take another fifteen minutes. Record measured recovery exercises under realistic data volumes and permissions, then compare the observed result with the business target and close the largest gaps.
98. What does a valid PostgreSQL PITR plan require beyond a base backup?
It requires the necessary uninterrupted WAL history, correct recovery configuration and timeline selection, accessible credentials and encryption keys, and enough infrastructure to restore and validate the result. Define the intended recovery target carefully, especially when stopping before a destructive transaction, and rehearse it in isolation so the recovered system cannot accidentally accept production traffic. A successful backup job does not establish that every required WAL segment remains retrievable. Monitor archive failures and retention relationships, and retain the configuration and operational knowledge needed to reproduce the environment. Point-in-time recovery replays a physical history; it is not a selective undo operation for one table. If selective recovery is needed, restoring a separate cluster and extracting validated data may be safer than replacing the whole live database.
99. When is pg_dump useful, and why is it not a complete physical disaster-recovery strategy?
pg_dump is useful for logical portability, selective object restoration, and migrations because it exports a consistent view of one database. It does not provide the physical base backup and continuous WAL chain required for physical PITR. Cluster-wide objects such as roles and tablespaces also need separate handling, and rebuilding a large database through logical restore may take substantial time. Choose the format, restore parallelism, ownership mapping, and extension prerequisites deliberately. Test with realistic data and validate object counts, permissions, and application behavior after restoration. A logical export can complement physical backups rather than replace them. Protect dump files as sensitive data and ensure the backup role's visibility and privileges match the intended backup scope, including row-security considerations.
100. What evidence makes a backup restore test convincing?
A convincing test restores into an isolated environment from the actual retained artifacts and proves both technical integrity and application usability. Verify the backup manifest where applicable, retrieve required WAL, reach the intended recovery point, and record elapsed time for the full process. Then check critical data relationships, permissions, extensions, sequences, and representative application operations using non-production integrations. Manifest verification is useful but does not replace starting the restored database and testing it. Include the loss of the original server and the need to obtain credentials from the documented recovery process. Record what was not tested, such as regional loss or a missing key, and assign owners to those gaps. Restoration should be repeatable by someone other than the person who created the backup.
101. How would you design database roles for a production application and its migrations?
Separate object ownership, migration authority, application access, and human operations instead of granting the runtime account broad administrative power. An owner role can hold objects while a tightly controlled deployment identity performs approved changes; runtime roles receive only the operations they need. Review membership and inheritance deliberately, including the ability to assume more powerful roles. Set future-object privileges for the actual object creator, since default privileges do not magically cover objects created by every role. Test both allowed and denied operations using the real application identity. Also inventory sequence, schema, and function privileges where relevant. A successful deployment should not leave the web application able to alter policies, drop tables, or grant itself additional access simply because those privileges were convenient during development.
102. What are the important limitations of row-level security in a multi-tenant design?
RLS can enforce row visibility and modification checks, but it is not a complete tenant-isolation architecture. Superusers and roles with BYPASSRLS bypass it, and owners normally bypass it unless forced to be subject to policies. Table-wide operations such as TRUNCATE are outside ordinary row filtering. Test policies for reads, writes, role changes, and cross-tenant references, including negative cases. If tenant identity comes from session context, ensure clients cannot forge it and pooled sessions cannot leak it between requests. Evaluate uniqueness errors and other indirect information channels as well as returned rows. Prefer an application role that cannot alter the protected table or its policies. A policy that works when tested as an administrator may conceal the very bypass that makes the test unrepresentative.
103. How would you review a SECURITY DEFINER function before approving it?
Treat it as a privilege boundary because it executes with its owner's authority rather than the caller's ordinary privileges. Keep the owner narrowly privileged, set a safe search path containing trusted schemas with pg_temp listed last, and schema-qualify sensitive object references. Review dynamic SQL for identifier and value handling, validate arguments, and consider whether errors expose protected data. Restrict execution privileges explicitly instead of relying on broad defaults, ideally in the same controlled deployment transaction. Test hostile names in caller-writable schemas and unauthorized argument combinations. A small function with a constrained purpose is easier to audit than a generic privileged SQL executor. SECURITY DEFINER is not a convenient substitute for designing ordinary grants correctly.
104. How would you audit schema privileges after upgrading a legacy PostgreSQL database?
Inspect effective schema ownership and CREATE privileges rather than inferring them from the new server version. A database originating on PostgreSQL 14 or earlier can retain broad creation rights on public after an upgrade. Inventory existing objects, their owners, privileged routines, application search paths, and extensions that rely on those objects. Revoking future creation rights alone does not remove a hostile or accidental object already present. Rehearse a least-privilege change with the actual runtime and migration roles, checking both successful application operations and denied unauthorized creation. Coordinate any grant changes through an approved deployment, since a blanket revocation can break legitimate migrations. Finally, check pooled connection initialization and newly created databases so the audited configuration does not drift back.
105. How would you safely diagnose a pg_hba.conf authentication failure?
Identify the connection's transport, source address, database, requested role, and encryption state, then determine the first matching authentication rule. PostgreSQL does not continue to later rules merely because authentication under the first matching rule fails. Distinguish a rule-selection problem from a bad credential, certificate issue, or missing database privilege. Use server logs and the authentication-rule inspection view where authorized, taking care not to expose secrets. Validate changes before a controlled reload and preserve an established administrative access path. Do not solve an unexplained failure by opening broad address ranges or switching to trust. Test the intended client path and a denied path after the change, because a fix that admits the application may also unintentionally admit other clients.
106. What does TLS verification add beyond requiring an encrypted PostgreSQL connection?
Encryption protects traffic contents, while certificate verification helps the client confirm it reached the intended server. A configuration that encrypts without checking server identity can leave a different threat model than one using a trusted certificate chain and hostname verification. For libpq clients, understand the semantics of sslmode, especially verify-full, and deploy the correct trust material and hostname. Test certificate rotation, expiry alerts, and failure behavior rather than only a successful initial connection. Server authentication rules and TLS policy must agree; a hostssl rule alone does not configure every client's verification behavior. Include replication and administrative tools in the inventory, because securing the main application connection does not secure forgotten backup or maintenance clients.
107. How would you plan a migration from MD5 password authentication to SCRAM?
Inventory client drivers and connection tools first, since every legitimate client must support the chosen authentication method. Plan password-verifier replacement, server authentication-rule changes, secret distribution, and rollback without exposing passwords in logs or shell history. PostgreSQL 18 deprecates MD5 password authentication, making compatibility work important rather than a reason to postpone indefinitely. Changing the password-encryption setting does not retroactively rewrite existing password verifiers; affected credentials need an authorized reset. Test ordinary clients, replication, backup jobs, and emergency access in a staging environment. Roll out in controlled groups and observe authentication failures. Do not confuse SCRAM with transport encryption: use an appropriate TLS configuration as well, and keep credentials in a supported secret-management process.
108. How would you evaluate an extension before installing it in production?
Evaluate its provenance, required privileges, server-code footprint, supported PostgreSQL versions, maintenance record, and operational consequences. An extension can add useful types and indexing, but it can also introduce native code, background workers, configuration requirements, and upgrade dependencies. Test installation, representative queries, backup and restore, failover, and major-version migration in an isolated environment. Document who owns the extension and what happens if a future PostgreSQL upgrade is blocked by it. A trusted extension has a specific installation-permission meaning; the label is not a comprehensive security or performance guarantee. Prefer the smallest justified feature set, and require the normal production change approval before installation. Removing an extension later may affect dependent objects and application behavior, so assess exit costs upfront.
109. What would you include in an auditable PostgreSQL access and change trail?
Start from the questions the organization must answer: who connected, which privileged actions occurred, what schema changed, and who approved the change. Configure logging and any approved auditing capability to support those questions while limiting sensitive query text and parameter exposure. Use distinct human identities where feasible, meaningful application names, reliable timestamps, protected log transport, retention, and access controls. Shared accounts and an editable local log undermine attribution even if many statements are recorded. Test performance and storage overhead under realistic traffic. Database logs alone may not explain an end user's identity behind a service account, so correlate with application and deployment events. Do not claim basic statement logging automatically creates a complete, tamper-proof audit system.
110. How would you choose between shared tables, separate schemas, and separate databases for tenants?
Compare isolation requirements, scale, operational cost, customization, and recovery granularity rather than declaring one model universally best. Shared tables simplify some operations but require strong tenant-key discipline and carefully tested access controls. Separate schemas can organize objects but are not an automatic security boundary without correct privileges. Separate databases offer clearer logical separation, yet databases in one PostgreSQL cluster still share resources and administrative authority. Consider noisy neighbors, per-tenant backups, schema rollout fan-out, connection limits, and how a tenant moves or exits. For high-risk tenants, separate clusters or infrastructure may be warranted. Prototype realistic tenant counts and failure scenarios, and make the chosen isolation guarantees explicit to the business rather than equating a naming convention with security.
111. How would you add a constraint to a large busy table without assuming the change is instantaneous?
Determine the lock level, validation work, rewrite requirements, and application compatibility for the exact operation and PostgreSQL release. Where supported, adding a check or foreign-key constraint as NOT VALID and validating it later can separate enforcement for new changes from the historical-data scan, but the initial operation still requires locks. Test on representative data and transaction activity, with approved lock and statement timeouts and a retry plan. Find existing invalid data before the deployment rather than during a critical window. Coordinate changes across application versions so old writers remain compatible. Do not label a migration zero-downtime merely because it ran quickly on an empty development table; demonstrate bounded blocking, safe cancellation, and a workable rollback or forward-fix path.
112. How would you backfill a new column on a heavily used PostgreSQL table?
Design the backfill as controlled workload, not a single unbounded update. Define a stable progress key, batching strategy, consistency rules for concurrent writes, and an idempotent restart mechanism. Measure lock time, WAL volume, replica lag, dead-row generation, and application latency, then throttle or pause against explicit thresholds. Coordinate dual-write or derivation logic so rows changed during the backfill do not end up stale. Commit batches deliberately while avoiding claims that smaller transactions remove every contention risk. Validate both completeness and meaning before switching reads or adding final constraints. A rollback plan must distinguish reverting application behavior from undoing already written data. Rehearse the process in an isolated environment with production-like size and update patterns before authorization to run it live.
113. What should a partition-retention procedure check before removing old data?
Verify that the partition boundaries match the business retention rule, especially timestamp zones and late-arriving events. Confirm dependencies, foreign keys, legal or operational holds, backup requirements, and whether downstream systems still need the data. Detaching a partition can separate removal from the parent from final deletion, but exact locking and concurrent-detach restrictions must be checked for the release and schema. Precreate future partitions and monitor routing failures so retention maintenance does not hide an ingestion problem. Test the procedure on a representative copy, document the irreversibility of deletion, and require normal production approval. A partition name that contains an old month is not sufficient evidence that every row can be removed safely.
114. How would you choose and rehearse a major-version PostgreSQL upgrade?
Compare dump/restore, pg_upgrade, and logical migration against downtime, data size, extension compatibility, rollback needs, and operational skill. Run compatibility checks with the intended binaries and extensions, then rehearse the full path on a representative copy. Include application drivers, collations, query plans, backup tooling, replicas, monitoring, and authentication changes. PostgreSQL 18 can retain optimizer statistics through pg_upgrade, but that does not prove all plans or workload behavior remain identical. Understand the selected file-transfer mode: for example, link-mode rollback has important restrictions after starting the upgraded cluster. Record the last safe rollback point, the cutover authority, and post-upgrade validation. A successful upgrade command is one milestone, not evidence that the application and recovery system are ready.
115. Which PostgreSQL 18 changes would you explicitly check when upgrading from 16 or 17?
Check features and incompatibilities against the exact source release rather than assuming a two-version jump is equivalent to a one-version jump. PostgreSQL 18 introduces asynchronous I/O support, B-tree skip scan, uuidv7(), and virtual generated columns; virtual is now the default generated-column kind, so specify intent in schema definitions. New clusters enable data checksums by default, and MD5 password authentication is deprecated. These changes affect performance expectations, schema portability, provisioning, and client migration plans in different ways. An upgrade from 16 also requires reviewing intervening 17 changes and extension support. Benchmark the actual workload and inspect configuration and release notes; a feature list does not establish a speedup, a successful authentication transition, or compatibility with an application's assumptions.
116. What can PostgreSQL data checksums prove, and what can they not prove?
Data checksums can help detect certain data-page corruption when pages are checked, but they do not repair the page, validate business correctness, or replace backups. A valid checksum does not prove that an application wrote the right amount or that all logical relationships are correct. PostgreSQL 18 enables checksums by default for newly initialized clusters; verify the actual cluster setting rather than inferring it from server version, particularly after upgrades. If corruption is reported, preserve evidence, identify scope, and involve the storage and database incident owners before attempting repair. Assess backups and replicas independently rather than assuming they are clean. Disabling checksum checks to silence an error can hide damage and should not be presented as recovery.
117. How would you handle suspected index corruption without destroying evidence?
First preserve the error details, relevant logs, affected relation identifiers, and information about recent storage or upgrade events. Determine whether the symptom is reproducible and whether it concerns an index, heap data, or a broader infrastructure issue. Approved integrity tools such as amcheck can provide targeted evidence, but their scope, privileges, overhead, and limitations matter. Rebuilding an index may be appropriate after diagnosis, yet it is not a substitute for understanding possible underlying heap or storage corruption. Avoid destructive trial-and-error repairs on the only copy of the data. Establish a verified recovery path and coordinate any production mutation through incident procedures. A strong response explains what evidence would justify a rebuild and how the team would verify correctness afterward.
118. What PostgreSQL observability would you maintain before an incident occurs?
Maintain a baseline of application latency and errors, throughput, active and queued work, lock waits, connection use, storage headroom, WAL generation, replication freshness, maintenance progress, and backup health. Where approved, normalized query statistics help identify workload changes without treating every literal query as a separate pattern. Record reset times and relevant configuration so interval comparisons remain meaningful. Alerts should represent actionable risk, such as sustained freshness violations or time-to-disk-exhaustion, not every fluctuation. Protect query text and logs because they may expose sensitive data. Test the dashboards during a controlled failure exercise and confirm on-call staff can access them when the database is unavailable. Monitoring that depends entirely on querying an already failed primary is an incomplete incident plan.
119. How would you estimate capacity headroom for a growing PostgreSQL workload?
Model the resource that limits the actual workload: CPU, storage latency or throughput, memory working set, WAL and replication capacity, connections, or a serialized lock path. Use measured arrival rates, query mix, data growth, and concurrency rather than extrapolating only database size. Include maintenance, backup, failover, and migration periods, when the normal spare capacity may disappear. For example, a standby that barely matches current WAL replay cannot comfortably absorb a sustained growth burst or catch up after an outage. Run representative load tests with realistic key distributions and response-time objectives, and identify the saturation knee rather than only maximum throughput. Reserve enough headroom for recovery operations and document what scaling action is available before the next forecast threshold.
120. How would you prove that a PostgreSQL performance change is worth deploying?
Define the hypothesis, success metric, correctness checks, representative workload, and rollback condition before testing. Compare the proposed change against a baseline using the same data distribution, concurrency, cache conditions, and measurement window where practical. Evaluate tail latency, throughput, resource consumption, maintenance cost, and replica impact, not just one query's best execution time. Validate plans and result equivalence, then repeat enough runs to distinguish a stable improvement from noise. A new index might speed reads while increasing write cost and storage, so measure the relevant trade-off. Use a controlled rollout with observability and a clear owner. Do not turn an isolated benchmark into a universal recommendation; the deployment decision should follow the application's workload and reliability objectives.
PostgreSQL Documentation and Further Reading
These primary PostgreSQL references are pinned to major version 18 for a coherent technical baseline. Consult the matching documentation for older installations, especially for generated columns, UUID generation, index skip scans, and upgrade behavior.
Similar Posts