Database Design and PostgreSQL Interview Questions

A primary key uniquely identifies each row in a table. It must be unique so no two rows are ambiguous, and non-null because a missing identifier can't reliably distinguish or reference that row from elsewhere.

A column (or set of columns) referencing another table's primary/unique key -- it enforces referential integrity, preventing a row from pointing to a parent row that doesn't exist (or, depending on the configured action, controlling what happens if that parent row is deleted).

A functional dependency exists when one column's value determines another's (e.g. `zip_code` determines `city`). Normalization rules are defined in terms of eliminating dependencies that don't rely on the whole primary key, to avoid redundant, inconsistency-prone data.

Every column must hold a single, atomic value (no repeating groups or comma-separated lists stuffed into one column), and each row must be uniquely identifiable.

2NF eliminates partial dependencies, where a non-key column depends on only part of a composite primary key rather than the whole key -- it's only a meaningful concern for tables with a composite (multi-column) primary key.

Transitive dependencies, where a non-key column depends on another non-key column rather than directly on the primary key -- e.g. storing both `employee.department_id` and `employee.department_name` when `department_name` really depends on `department_id`, not on the employee.

Common mistake: Storing a derived/dependent attribute (like department_name) directly on a row instead of looking it up through its proper foreign key relationship.

Intentionally introducing redundancy (duplicating or precomputing data) that a fully normalized schema wouldn't have, typically to avoid expensive joins/aggregations on frequently-read, rarely-changed data -- a justified tradeoff when read performance genuinely matters more than the added complexity of keeping the duplicate in sync.

Cardinality describes how many of one entity relate to how many of another (one-to-one, one-to-many, many-to-many); optionality describes whether that relationship is required or may be absent (e.g. must every order have a customer, or can it be null).

With a junction (join) table containing foreign keys referencing both related tables' primary keys -- e.g. a `students` and `courses` many-to-many relationship needs an `enrollments` table with `student_id` and `course_id` foreign keys.

It surfaces cardinality, optionality, and normalization questions early, when they're cheap to change -- discovering a modeling mistake after a schema is populated with real data and referenced by application code is far more expensive to fix.

`VARCHAR(n)` limits length to n characters; `TEXT` has no length limit; `CHAR(n)` is fixed-length, padding shorter values with spaces. In PostgreSQL specifically, `TEXT` and unconstrained `VARCHAR` have essentially identical performance, so `TEXT` is commonly preferred unless a specific length constraint is meaningful.

`NUMERIC` stores exact decimal values with no rounding error, at some performance cost; `FLOAT`/`DOUBLE PRECISION` use approximate binary floating-point representation, which can introduce small rounding errors. Money and other exact-precision values should use `NUMERIC`, not floating-point types.

Common mistake: Storing monetary amounts as FLOAT/DOUBLE PRECISION, introducing rounding errors that accumulate over calculations.

It rejects any row where that column's value would be `NULL`. If a column is `NOT NULL` with no `DEFAULT` and the `INSERT` doesn't provide a value, the statement fails with a constraint violation.

Both enforce uniqueness, but a table can have only one primary key (which also implies `NOT NULL`), while it can have multiple `UNIQUE` constraints, and a `UNIQUE` column can generally still allow `NULL` values (with `NULL` not considered equal to another `NULL` for uniqueness purposes).

An arbitrary boolean expression a row must satisfy -- e.g. `CHECK (price >= 0)` -- enforcing domain-specific business rules directly at the database level, not just presence or uniqueness.

A table with a foreign key referencing another table can't be created until the referenced table (and its referenced key) already exists -- creating tables out of dependency order causes the foreign key constraint definition to fail.

`CASCADE` automatically deletes dependent child rows when the referenced parent row is deleted; `RESTRICT` (the typical default-like behavior) instead blocks the deletion of the parent row entirely while dependent child rows still exist.

An auto-incrementing integer column, commonly used for surrogate primary keys, where PostgreSQL automatically assigns the next sequential value on insert if none is explicitly provided.

`TIMESTAMPTZ` stores the value normalized to UTC internally and converts to the session's time zone on display; plain `TIMESTAMP` stores the literal value with no time zone awareness at all, which can cause subtle bugs when data spans multiple time zones.

Common mistake: Using plain TIMESTAMP for data accessed across multiple time zones, causing values to be misinterpreted depending on the reading session's local time zone assumptions.

`JSONB` stores JSON in a decomposed binary format that supports indexing and efficient querying of nested fields; plain `JSON` stores the exact input text and re-parses it on every read, with no indexing support -- `JSONB` is generally preferred unless preserving exact original formatting/key order matters.

`INNER JOIN` returns only rows with a match in both tables; `LEFT JOIN` returns every row from the left table, with `NULL`s filled in for unmatched right-table columns when no match exists.

A `WITH name AS (SELECT ...)` block that names a query result for reuse later in the same statement -- it can improve readability of complex queries by breaking them into named, sequential steps, compared to deeply nested subqueries.

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

A subquery that references a column from the outer query, meaning it's conceptually re-evaluated once per outer row -- an equivalent join often lets the query planner process the data in a single set-based pass instead.

A window function (`OVER (...)`) computes an aggregate or ranking across a set of related rows while still returning one row per original row -- `GROUP BY` collapses rows into one row per group, losing the individual row detail.

SELECT name, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg FROM employees;

`ROW_NUMBER()` always assigns a unique, strictly sequential number to every row; `RANK()` assigns the same rank to tied rows (by the `ORDER BY` in the window) and then skips subsequent rank numbers accordingly.

`WHERE` filters individual rows before grouping/aggregation happens; `HAVING` filters groups after aggregation, so it's the correct clause to use when filtering on an aggregate result like `COUNT(*) > 5`.

Common mistake: Trying to filter on an aggregate result (like COUNT(*)) using WHERE instead of HAVING.

A join of a table with itself, used when rows in the same table relate to each other -- e.g. an `employees` table with a `manager_id` column referencing another row's `id`, requiring a self-join to list each employee alongside their manager's name.

`UNION` combines two result sets and removes duplicate rows (requiring an implicit sort/dedup pass); `UNION ALL` combines them without deduplication, which is faster when duplicates are known not to occur or are acceptable.

It implicitly depends on the table's exact current column set and order; adding, removing, or reordering columns later can silently break application code relying on positional access or an unexpectedly-included new column, whereas naming columns explicitly makes the query's real dependency clear.

`EXPLAIN` shows the query planner's chosen execution plan (which scans, joins, and estimated costs it will use) without running the query; `EXPLAIN ANALYZE` actually executes the query and adds real timing and row-count data alongside the plan, letting you compare estimated vs. actual behavior.

Atomicity (a transaction's operations all succeed or all roll back together), Consistency (a transaction moves the database from one valid state to another), Isolation (concurrent transactions don't see each other's uncommitted changes, per the chosen isolation level), Durability (once committed, a transaction's changes survive a crash).

A dirty read occurs when a transaction reads another transaction's uncommitted (and possibly later rolled-back) changes. PostgreSQL's default isolation level, Read Committed, never allows dirty reads -- that anomaly is prevented even at the lowest isolation level PostgreSQL supports.

When a transaction reads the same row twice and gets different values because another transaction committed a change to that row in between -- PostgreSQL's Repeatable Read and Serializable isolation levels prevent this; Read Committed does not.

A lost update happens when two transactions both read the same row, then both write back an update based on that stale read, and the second write silently overwrites the first's change. `SELECT ... FOR UPDATE` locks the selected row(s), blocking a second transaction from reading (for update purposes) or updating them until the first transaction commits or rolls back.

Common mistake: Reading a row's current value, computing a new value in application code, then writing it back without any locking, allowing a concurrent update to be silently overwritten.

`ROLLBACK` undoes every change made since the transaction began, restoring the database as if none of them happened. Wrapping related writes in one transaction ensures they succeed or fail together, so a partial failure (e.g. crash mid-way) can never leave data in an inconsistent, half-applied state.

A separate data structure (commonly a B-tree) that lets PostgreSQL find matching rows without scanning the whole table -- it speeds up reads on the indexed column(s) but adds storage overhead and slows down writes, since every insert/update/delete must also maintain the index.

Every additional index adds write overhead (each insert/update/delete must update every relevant index) and storage cost, without necessarily being used by any real query -- indexes should be added deliberately based on actual query patterns, not preemptively on everything.

Common mistake: Adding an index to every column speculatively, adding write overhead without a corresponding read benefit for columns that are never actually filtered or joined on.

A regular view is a stored, named query that re-executes against live data every time it's queried; a materialized view stores its result set physically on disk at creation/refresh time, serving stale-but-fast results until explicitly refreshed.

Grant each application role (or user) only the specific permissions it actually needs (e.g. `SELECT`/`INSERT` on certain tables) rather than broad admin/superuser access, limiting the damage a compromised credential or a buggy query can cause.

Ordered, version-controlled migrations can be applied consistently and repeatably across environments (dev, staging, production), reviewed before running, and rolled back if a step fails -- an ad hoc manual script run once against production leaves no reliable record of what changed or how to undo it.

A logical backup (`pg_dump`) exports the database's data and schema as portable SQL/archive output, restorable even to a different PostgreSQL version; a physical backup copies the actual on-disk data files, faster for very large databases but generally tied to the same major version and configuration.

A real recovery plan also needs a tested, documented restore procedure and a known recovery time -- backups that have never actually been restored in practice can turn out to be corrupted, incomplete, or too slow to restore within an acceptable downtime window when actually needed.

RPO is the maximum acceptable amount of data loss, measured in time (e.g. "at most 1 hour of data") -- more frequent backups (or continuous write-ahead-log archiving) reduce the RPO by shrinking the gap between the last backup and a failure.

Which migrations have already been applied to a given database, usually via a dedicated metadata table -- this lets the tool apply only the new, not-yet-run migrations when deploying to an environment, rather than re-running everything.

The migration must decide what value existing rows get -- without a default, it fails outright; even with a default, rewriting every existing row to add the new column can lock the table for a long time on a large table, blocking application traffic during the migration.

Common mistake: Adding a NOT NULL column with no default to a large production table during business hours, causing a long table lock that blocks application queries.

A connection pooler (e.g. PgBouncer) reuses a limited set of actual database connections across many application requests, since each raw PostgreSQL connection has real memory/process overhead -- without pooling, a high-traffic application can exhaust the database's maximum connection limit.

PostgreSQL's MVCC (multi-version concurrency control) model leaves old row versions behind after updates/deletes; `VACUUM` reclaims that space and updates statistics the query planner relies on -- without it, table bloat grows and query planning can become less accurate over time.

A superuser role can bypass row-level security, alter any schema, and access any data -- if application credentials are compromised (e.g. via a SQL injection or leaked secret), a scoped least-privilege role limits the resulting damage far more than a superuser role would.

A copy of the database kept continuously synchronized with the primary via streaming replication -- typically used for read scaling (routing read-only queries to replicas) and/or failover (promoting a replica to primary if the original fails).

A migration that runs instantly on a near-empty local table can lock or take an unacceptably long time on a table with millions of rows in production -- data-volume-dependent behavior (locking duration, index build time) often only surfaces at realistic scale.