PostgreSQL Interview Questions

Object relational means PostgreSQL extends the traditional relational model with object oriented concepts like table inheritance and user defined types and functions. This lets developers model more complex data structures directly in the database rather than only working with flat rows and columns.

PostgreSQL emphasizes strict SQL standards compliance and a rich set of advanced features like custom types and full text search, while MySQL has historically prioritized simplicity and raw read speed for simpler workloads. Both are popular open source relational databases, but PostgreSQL is generally favored when data integrity and complex querying matter most.

A primary key is a column or set of columns that uniquely identifies each row in a table, and PostgreSQL automatically creates a unique index on it and enforces that its values can't be null or duplicated. Every table can have at most one primary key.

A foreign key is a column that references the primary key or a unique column of another table, enforcing referential integrity so a row can't reference a value that doesn't exist in the related table. PostgreSQL checks this constraint automatically on insert, update, and delete.

SERIAL is a shorthand PostgreSQL provides for creating an auto incrementing integer column, implemented internally using a sequence that generates the next value automatically whenever a new row is inserted without an explicit value for that column.

CHAR is a fixed length string type padded with spaces to a set length, VARCHAR is a variable length string type with an optional maximum length, and TEXT is a variable length string type with no length limit at all. In PostgreSQL specifically, all three perform similarly under the hood, so TEXT is often preferred for its simplicity.

A schema is a namespace within a database that groups tables, views, and other objects together, letting multiple sets of objects coexist in the same database without name collisions. The default schema most connections use is called public unless configured otherwise.

A database is a completely separate, isolated collection of data that a client connects to individually, while a schema is a logical grouping of objects within a single database. Queries can join across schemas in the same database but not directly across separate databases.

psql is PostgreSQL's official command line interface for connecting to and interacting with a database, letting users run SQL queries, manage database objects, and use built in meta commands like listing tables or describing a table's structure.

An index is a separate data structure that PostgreSQL maintains alongside a table to speed up lookups on specific columns, avoiding the need to scan every row to find matching data. Indexes speed up reads but add overhead to writes since they must be kept up to date.

The default index type in PostgreSQL is B-tree, which works well for equality and range queries on most data types and is the type created automatically when a column is declared UNIQUE or PRIMARY KEY without specifying otherwise.

A view is a saved query that acts like a virtual table, letting users reference the result of a potentially complex query using a simple name without rewriting the underlying logic every time. It doesn't store data itself, just the query definition, unless it's a materialized view.

A regular view runs its underlying query fresh every time it's referenced, always reflecting current data. A materialized view stores the query's result physically on disk at the time it was last refreshed, which makes reads faster but means the data can go stale until it's explicitly refreshed again.

NULL represents the absence of a value or an unknown value in a column, distinct from an empty string or zero. Comparisons involving NULL don't behave like normal equality checks, which is why PostgreSQL requires IS NULL or IS NOT NULL rather than using the equals operator.

The WHERE clause filters rows returned by a query based on a specified condition, evaluated for each row before it's included in the result set. It's used in SELECT, UPDATE, and DELETE statements to target only the rows that meet the given criteria.

An INNER JOIN returns only the rows where there's a matching row in both tables based on the join condition. A LEFT JOIN returns all rows from the left table regardless of whether a match exists in the right table, filling in NULLs for the right table's columns when there's no match.

A transaction is a group of one or more SQL statements executed as a single unit of work, where either all the changes commit together or none of them do if something fails, preserving data consistency. Transactions in PostgreSQL are started implicitly or explicitly with BEGIN and finished with COMMIT or ROLLBACK.

ACID stands for Atomicity, Consistency, Isolation, and Durability, the properties that guarantee reliable transaction processing. PostgreSQL is fully ACID compliant, meaning transactions either complete entirely or not at all, data stays consistent with defined constraints, concurrent transactions don't interfere unexpectedly, and committed changes survive a crash.

pg_dump is a PostgreSQL command line tool that exports a database or specific objects within it into a backup file, which can later be restored using pg_restore or psql. It's commonly used for backups, migrations, or copying a database's structure and data to another environment.

DELETE removes specific rows from a table based on a condition and can be rolled back within a transaction, but it's relatively slow for large tables since it logs each row. TRUNCATE removes all rows from a table quickly without logging individual rows. DROP removes the table object itself, structure and all, from the database entirely.

A sequence is a database object that generates a series of unique numbers, typically used to produce auto incrementing values for a primary key column. SERIAL columns are implemented internally using a sequence that PostgreSQL creates and manages automatically.

A constraint is a rule enforced on a column or table that restricts what data can be stored, such as NOT NULL, UNIQUE, CHECK, or FOREIGN KEY constraints. Constraints protect data integrity by rejecting inserts or updates that would violate the defined rule.

GROUP BY groups rows that share the same values in specified columns into summary rows, typically used together with aggregate functions like COUNT, SUM, or AVG to calculate a value per group rather than across the whole result set.

WHERE filters individual rows before any grouping happens, so it can't reference aggregate function results. HAVING filters groups after GROUP BY has been applied, which is why it's used when a condition needs to reference an aggregate value like a count or sum per group.

PostgreSQL supports array types natively, letting a single column store an ordered list of values of a given type, like an array of integers or text. This is a PostgreSQL specific convenience that avoids needing a separate related table for simple list style data.

JSONB is a PostgreSQL specific data type that stores JSON data in a decomposed binary format rather than as plain text, which makes it faster to query and index than the plain JSON type, at a small cost to insertion speed since the data must be parsed and converted on write.

The JSON type stores data as an exact text copy of the input, preserving formatting like whitespace and key order but requiring re-parsing on every query. JSONB stores a parsed binary representation that's faster to query and supports indexing, though it doesn't preserve the original formatting or duplicate keys.

pg_hba.conf is PostgreSQL's host based authentication configuration file, controlling which users can connect from which hosts, using which authentication method, and to which databases. It's a key file for securing access to a PostgreSQL server.

A role in PostgreSQL is an entity that can own database objects and hold permissions, and it's used to represent both individual users and groups of users. Roles can be granted login privileges to act as a traditional user account or used purely to group permissions together.

EXPLAIN shows the execution plan PostgreSQL's query planner intends to use for a given query, including which indexes it plans to use and the estimated cost of each step, without actually running the query. It's a starting point for understanding and optimizing query performance.

EXPLAIN shows the planner's estimated execution plan without running the query. EXPLAIN ANALYZE actually executes the query and shows the real execution plan along with actual timing and row counts for each step, which is more useful for diagnosing why a query is genuinely slow.

pgAdmin is a popular open source graphical administration and development tool for PostgreSQL, providing a visual interface for managing databases, writing and running queries, and viewing server statistics as an alternative to the command line psql client.

Upsert refers to inserting a row if it doesn't already exist or updating it if it does, and PostgreSQL implements this through the INSERT ... ON CONFLICT clause, which specifies what to do when a row would otherwise violate a unique or primary key constraint.

A CTE is a temporary named result set defined using a WITH clause at the start of a query, which can be referenced later in that same query as if it were a table. It makes complex queries more readable by breaking them into logical, named steps.

UNION combines the result sets of two queries and removes duplicate rows from the final result, which requires extra processing. UNION ALL combines the results without removing duplicates, making it faster when duplicates either don't matter or are known not to exist.

I would use JSONB when the data's structure genuinely varies between rows or when it's rarely queried by individual nested fields, and use normalized tables when the data has a consistent, well defined structure that benefits from relational integrity constraints and needs to be efficiently filtered or joined on specific fields.

I would introduce a connection pooler like PgBouncer in front of PostgreSQL to reuse a smaller number of actual database connections across many application requests, configure an appropriate pool size based on PostgreSQL's max_connections setting and available server resources, and review whether the application itself is leaking connections rather than closing them properly.

I would index only the columns that are actually used in WHERE clauses, joins, or ORDER BY for the most frequent and performance sensitive queries, since every index adds write overhead, and periodically review pg_stat_user_indexes to identify indexes that aren't being used and could be dropped.

I would review the transaction logs to identify the specific tables and lock order involved in the deadlocks, ensure the application consistently acquires locks on multiple tables in the same order across all code paths, and consider whether some operations can use a less restrictive isolation level or shorter transactions to reduce contention.

I would add the column as nullable first, backfill the data in batches to avoid locking the whole table for a long period, and then add the NOT NULL constraint afterward, since in recent PostgreSQL versions adding a column with a constant default no longer requires a full table rewrite.

I would compare the planner's estimated row counts against the actual row counts at each step, since a large discrepancy usually indicates stale or insufficient statistics, and check whether a particular step, like a nested loop join, is unexpectedly slow due to an underlying data skew the planner didn't account for.

I would use PostgreSQL's built in tsvector and tsquery types along with a GIN index on the searchable column, precompute the tsvector in a generated or trigger maintained column to avoid recomputing it on every search, and evaluate whether performance holds up at the dataset's expected scale before considering an external search engine.

I would check whether autovacuum settings are too conservative for that specific table's write volume and tune its thresholds directly, confirm there aren't long running transactions holding back vacuum's ability to clean up dead tuples, and consider a manual VACUUM FULL during a maintenance window if bloat has already become severe.

I would default to PostgreSQL's standard READ COMMITTED for most operations, but use SERIALIZABLE for the specific transactions where preventing subtle anomalies like write skew genuinely matters, accepting the added overhead and need to handle serialization failures with retries only where that correctness guarantee is worth it.

I would check current connection counts against max_connections, identify whether idle connections held open by the application or a missing connection pooler are the main culprit, and review whether long running queries are holding connections open longer than necessary rather than simply raising the connection limit.

I would create a publication on the source database covering only the needed tables, set up a subscription on the reporting database, and monitor replication lag to make sure the reporting database stays reasonably current without impacting the production database's write performance.

I would compare EXPLAIN ANALYZE output for the affected query before and after the upgrade to see if the planner is choosing a different execution plan, check the release notes for planner behavior changes in that version, and verify statistics were refreshed after the upgrade since that alone can sometimes explain a plan change.

I would evaluate what column naturally divides the data, like a date for time series data, use PostgreSQL's declarative partitioning to split the table along that boundary, and confirm queries actually filter on the partition key so the planner can benefit from partition pruning rather than scanning every partition.

I would combine regular base backups using pg_basebackup with continuous archiving of write ahead log files, test the restore process regularly in a non production environment to confirm it actually works, and document the recovery time objective so the backup cadence matches what the business can tolerate losing.

I would recommend storing the files in object storage instead and keeping only a reference or URL in PostgreSQL, since large binary data bloats the database, slows backups, and doesn't benefit from PostgreSQL's relational features, reserving BYTEA storage for cases where transactional consistency with the file genuinely matters.

I would review current memory usage against available server RAM, increase work_mem carefully since it applies per sort or hash operation and can add up quickly under concurrency, and adjust shared_buffers to a reasonable fraction of total memory rather than assuming higher is always better without testing.

I would use a generated column when the derived value is a straightforward, deterministic expression of other columns in the same row, since it's simpler and maintained automatically, and reserve a trigger for cases needing more complex logic or cross table lookups that a generated column expression can't express.

I would query pg_stat_user_indexes to see which indexes have low or zero scan counts, cross check with the actual query patterns the application uses to avoid removing an index that's rarely but critically used, and drop the genuinely unused ones during a low traffic window since dropping large indexes briefly locks the table.

I would check whether the WHERE clause filter and the ORDER BY column together prevent the planner from efficiently using the index, since an index that's great for filtering may not help with sorting if the filtered condition isn't selective, and consider a composite index covering both the filter and sort columns.

I would first confirm indexes and statistics are still appropriate for the new scale, evaluate whether partitioning would help given the query patterns, and check whether the application is fetching more data than it actually needs, like missing pagination, before assuming the database itself needs deeper architectural change.

I would create separate roles per application with only the specific grants each one needs on the relevant schemas and tables, avoid using the superuser account for application connections, and configure pg_hba.conf to restrict which hosts and users can connect at all.

I would check the application's transaction isolation level and whether the read happens in a separate transaction or connection right after the write, since under READ COMMITTED a concurrent transaction that started earlier can still see the pre-update state, and confirm the application isn't reading from a replica that hasn't caught up yet.

I would lean toward a slightly more normalized design early on since it's easier to widen a narrow table later than to safely split a wide one once it has significant production data and many dependent queries, while staying pragmatic if performance testing shows the joins are genuinely a problem for the workload.

I would add read replicas using streaming replication and route read only queries to them through the application or a connection routing layer, being mindful of replication lag for use cases that need strictly current data, and evaluate whether caching frequently read data can reduce load further before adding more replicas.

I would check for table and index bloat accumulated over time, review whether autovacuum has been keeping pace with the growing write volume, look at whether query patterns or data distribution have shifted in ways that no longer match the existing index strategy, and check for any long running transactions holding back cleanup.

I would identify a natural shard key, like a tenant or customer ID, that keeps most queries scoped to a single shard, evaluate whether an extension like Citus or an application level sharding approach fits the workload better, and plan for the operational complexity of cross shard queries and rebalancing before committing to the approach.

I would look at whether the current bottleneck is genuinely CPU, memory, or IO constrained on a single server, which vertical scaling can often resolve more simply, versus whether the workload's growth trajectory will outpace what any single server can handle, which points toward investing in horizontal scaling architecture earlier.

I would set up cross region replication with a defined recovery point and recovery time objective agreed with the business, regularly test actual failover to the secondary region rather than assuming it works, and account for the latency tradeoff between synchronous replication's stronger consistency and asynchronous replication's better write performance.

I would standardize on a migration tool that tracks applied changes per database, require migrations to be backward compatible so old and new application versions can run simultaneously during a rolling deployment, and avoid destructive changes like dropping a column in the same migration that stops using it.

I would model growth based on current trends in both data size and query throughput, build in headroom for unexpected spikes rather than planning to exactly current peak usage, and periodically revisit the plan against actual growth since initial projections are rarely exactly right.

I would weigh the specialized time series optimizations and compression the extension provides against the added operational complexity and vendor specific behavior it introduces, and favor native partitioning when the workload's needs are met well enough by it without requiring the extension's more advanced capabilities.

I would track key metrics like replication lag, connection counts, long running queries, table bloat, and disk space trends, set alert thresholds based on what actually predicts trouble rather than arbitrary numbers, and make sure alerts are actionable rather than generating noise that gets ignored over time.

I would use logical replication or a tool like pg_upgrade with the --link option to minimize downtime, thoroughly test the upgrade process and application compatibility in a staging environment first, and plan a rollback strategy in case issues surface after the cutover that weren't caught in testing.

I would use partitioning by time period so old partitions can be efficiently detached and moved to cheaper storage or archived externally, keep only recent, actively queried data in the primary high performance tables, and make sure the archival process still satisfies whatever compliance access requirements apply to the older data.

I would require an evaluation of the extension's maintenance status, licensing, and operational impact before approval, maintain an approved list so extensions aren't adopted piecemeal across teams, and weigh the specific capability gained against the added operational complexity every extension introduces.

I would tier services by business criticality and set minimum backup frequency and recovery objectives per tier rather than a single blanket policy, require regular restore testing as a non negotiable practice regardless of tier, and audit compliance periodically rather than assuming policies are followed once set.

I would weigh the operational overhead and specialized expertise self managing requires against the cost and reduced control of a managed service, and generally favor managed services for teams without dedicated database expertise while reserving self managed deployments for cases with specific requirements a managed offering can't meet.

I would require encryption at rest and in transit as a baseline, enforce least privilege access through role based grants rather than broad access, and mandate that sensitive columns are clearly identified so audits and compliance reviews can verify appropriate handling.

I would establish a standard supported version range, communicate a clear timeline for retiring unsupported versions, and prioritize upgrading the instances with the highest security or support risk first rather than treating every instance as equally urgent.

I would require EXPLAIN ANALYZE output for any new query expected to run against large tables as part of code review, build automated checks that flag queries missing appropriate indexes where practical, and provide training so reviewers without deep database expertise still catch common issues.

I would first make sure the database itself is reasonably well tuned, since caching can mask underlying inefficiency rather than fix it, and then introduce caching strategically where the workload genuinely benefits from it, like expensive, frequently repeated read queries, rather than as a default first response to any performance concern.

I would mandate that credentials live in a centralized secrets manager rather than embedded in application configuration files, require regular credential rotation, and audit which applications and services actually have access to which databases on a recurring basis.

I would justify the investment by pointing to the cost of past incidents caused by database issues and the growing complexity of the database footprint, and frame dedicated expertise as reducing both the frequency and severity of future incidents rather than a purely discretionary hire.

I would require changes to high impact shared tables to go through a review that considers downstream consumers, since an unreviewed change could silently break another team's queries, while allowing lighter review for schema changes scoped to a single team's clearly owned tables.

I would allow tightly scoped, audited read only access for genuine debugging needs while restricting write access to controlled, reviewed change processes, balancing the operational need for fast troubleshooting against the risk of accidental or unreviewed changes to production data.

I would define a service level expectation tied to the severity of the vulnerability, require a tested rollout process so patching doesn't itself introduce downtime, and track compliance across teams so patching doesn't quietly slip for less visible systems.

I would evaluate each workload against PostgreSQL's actual strengths and known limits, use PostgreSQL as the default for transactional and general purpose relational needs, and reserve specialized systems for workloads where PostgreSQL's own extensions or configuration genuinely can't meet the performance or scale requirement efficiently.

I would anticipate that practices fine at a small scale, like ad hoc schema changes or shared database instances per team, break down with growth, and proactively invest in things like standardized migration tooling, database per service boundaries, and dedicated reliability expertise before they become urgent pain points.

I would translate technical issues into business terms like the revenue or customer trust at risk from past incidents and the engineering hours currently lost to firefighting, and present a phased investment plan with measurable milestones rather than asking for a large undefined commitment.

I would ground the discussion in the organization's actual scale trajectory and available operational expertise for each option, weigh the extension's maturity and community support against the flexibility of a custom approach, and be willing to make the final call myself if the debate isn't converging given the cost of prolonged indecision.

I would have them quantify the actual scale and growth trajectory behind the problem before proposing a solution, since I've seen engineers reach for large architectural changes for problems that targeted indexing or query rewrites would solve, and coach them to exhaust simpler fixes first given the operational cost architectural change carries.

I would push for that knowledge to be documented, including the non obvious reasoning behind past design decisions, deliberately rotate on call and review responsibilities across a wider group, and treat concentrated knowledge as an ongoing risk to manage actively rather than something to fix only once someone leaves.

I would coach them to frame recommendations around a concrete problem a team is already experiencing rather than abstract best practice, help them build a track record of small wins that other teams find credible, and remind them that mandates without buy-in tend to get quietly worked around.

I would look for strong fundamentals in relational database concepts and a demonstrated ability to reason about tradeoffs rather than requiring deep PostgreSQL specific expertise on day one, and pair new hires with an experienced engineer on real operational work before giving them independent ownership of critical systems.

I would invest in self service tooling and clear documentation so routine requests don't all require direct platform team involvement, plan staffing growth ahead of the demand curve rather than reactively, and periodically reassess whether the team's structure still matches the organization's current footprint.

I would define a small set of non negotiable standards, like backup requirements and security baselines, while leaving schema design and query optimization choices to individual teams who understand their own workload best, revisiting where that balance sits as the organization's needs evolve.

I would start a structured knowledge transfer well ahead of their departure, involve less experienced engineers in real design decisions under their guidance rather than relying on passive documentation, and identify one or more successors who get genuine ownership of key systems before the transition completes.

I would make raising these issues a normal, rewarded part of the process rather than something that reflects poorly on the original author, invest in tooling that surfaces common risky patterns automatically during review, and follow through visibly on flagged issues so the team trusts that raising them leads to real improvement.

I would weigh which of the three currently poses the greatest risk to the business, since a fragile foundation undermines the value of new capability, and generally prioritize shoring up reliability and performance before aggressively expanding scope, while keeping a visible channel for the business asks that genuinely can't wait.

I would build self service tools and clear guardrails that let product teams move quickly on routine database work without needing direct platform team involvement, reserve the platform team's direct engagement for genuinely complex or high risk changes, and measure whether this balance is actually reducing bottlenecks over time rather than assuming the structure alone solves it.