SQL Developer Interview Questions

A SQL query is easier to reason about when each clause has one clear responsibility.

SELECT

SELECT defines the expressions or columns returned by the query.

`sql

SELECT id, company, salary

FROM jobs;

Avoid using SELECT * automatically in production application queries. Explicit columns communicate the required contract and can reduce unnecessary data transfer.

WHERE

WHERE filters individual rows before later grouping and aggregation stages.

`sql

SELECT id, company

FROM jobs

WHERE status = 'active';

The condition must evaluate as true for a row to pass the filter. SQL NULL behavior must therefore be considered carefully.

ORDER BY

Without an explicit ORDER BY, application code should not depend on rows being returned in a particular order.

`sql

SELECT id, created_at

FROM applications

ORDER BY created_at DESC;

Multiple expressions can define deterministic tie-breaking:

`sql

ORDER BY created_at DESC, id DESC

This is especially important for pagination.

LIMIT

LIMIT restricts the maximum number of rows returned.

`sql

SELECT id, company

FROM jobs

ORDER BY created_at DESC

LIMIT 20;

OFFSET

OFFSET skips rows before returning the requested portion.

`sql

LIMIT 20 OFFSET 40

This is simple for small result sets, although large offsets can become inefficient and can also produce unstable pagination when rows are inserted or removed between requests.

Clause responsibilities

A useful conceptual model is:

1. Determine source rows

2. Filter rows

3. Perform later grouping or aggregation when present

4. Produce selected values

5. Sort results

6. Return the requested portion

SQL implementations may optimize physical execution differently, so this conceptual order should not be interpreted as a claim that the database literally performs each operation in exactly that sequence.

Deterministic results

For APIs, reports, and pagination, define ordering explicitly.

For example, ordering only by a timestamp that several records share may still leave ties. Add a stable secondary key such as an ID.

The important distinction is that filtering answers which rows qualify, while sorting answers how the qualifying result should be ordered.

NULL requires different reasoning from ordinary values because it represents the absence of a known value rather than a normal value such as zero or an empty string.

NULL is not zero

These values represent different states:

Do not substitute one for another unless the business model explicitly defines them as equivalent.

Comparisons

This is not the correct way to test for NULL:

`sql

WHERE deleted_at = NULL

Use:

`sql

WHERE deleted_at IS NULL

or:

`sql

WHERE deleted_at IS NOT NULL

Three-valued logic

SQL predicates can conceptually evaluate to:

For example, if salary is NULL:

`sql

salary > 100000

does not become true or false in the ordinary sense; the comparison produces an unknown result.

A WHERE clause retains rows for which the condition is true, so rows producing false or unknown do not pass the filter.

NOT IN and NULL

NULL behavior can create surprising results with expressions such as NOT IN when the comparison set itself contains NULL.

For exclusion logic involving nullable values, understand the exact semantics and consider approaches such as NOT EXISTS when modeling an anti-match against another relation.

COALESCE

COALESCE returns the first non-NULL expression.

`sql

SELECT COALESCE(display_name, username)

FROM users;

This is useful for presentation or explicitly defined defaults.

Do not apply COALESCE everywhere simply to hide NULL. Sometimes NULL carries important domain meaning that should remain visible.

Aggregates and NULL

Many aggregate functions ignore NULL inputs.

For example:

`sql

COUNT(*)

counts rows, while:

`sql

COUNT(salary)

counts rows where salary is not NULL.

This distinction can materially change reports.

Modeling

If a column must always contain a value, express that invariant with a NOT NULL constraint rather than relying solely on application validation.

NULL handling should therefore be considered during schema design, query filtering, aggregation, and application serialization.

Aggregation transforms sets of rows into summary information.

Common aggregate functions include:

GROUP BY

GROUP BY partitions qualifying input rows into groups that share specified values.

`sql

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id;

This produces one result row per department represented by the qualifying input.

WHERE before grouping

Use WHERE to filter individual input rows.

`sql

SELECT department_id, AVG(salary)

FROM employees

WHERE active = TRUE

GROUP BY department_id;

Inactive employees are removed before the averages are calculated.

HAVING after grouping

HAVING filters groups based on group-level conditions.

`sql

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

HAVING AVG(salary) > 100000;

This keeps only groups whose calculated average exceeds the threshold.

WHERE versus HAVING

Do not move ordinary row predicates into HAVING merely because the query contains aggregation.

For example, filtering to active employees belongs naturally in WHERE because it controls which rows participate in the group.

Filtering departments by COUNT(*) belongs in HAVING because that value exists only after grouping.

Selected columns

In a grouped query, ordinary selected columns generally need to be grouping columns or otherwise valid according to the database's grouping rules.

For example:

`sql

SELECT department_id, employee_name, COUNT(*)

FROM employees

GROUP BY department_id;

is conceptually invalid because one department can contain many different employee names and the query has not defined which name belongs in the single result row.

Aggregate NULL behavior

Many aggregates ignore NULL input values.

Also remember that aggregate behavior over no input rows differs by function. For example, PostgreSQL documents count as returning zero while aggregates such as sum can return NULL when no rows are selected.

Grouping cardinality

Adding more expressions to GROUP BY creates more granular groups.

Grouping by department gives department-level totals; grouping by department and job title produces department-and-title totals.

Before writing aggregation SQL, state the intended grain of one result row. That prevents many reporting mistakes.

Reports often require several metrics derived from the same base rows.

For example, one query may need to return:

Running four unrelated queries is not always necessary.

CASE expressions

A CASE expression returns different values depending on conditions.

`sql

CASE

WHEN status = 'offer' THEN 1

ELSE 0

END

This can be aggregated:

`sql

SUM(

CASE

WHEN status = 'offer' THEN 1

ELSE 0

END

)

Conditional counting

Another common form is:

`sql

COUNT(

CASE

WHEN status = 'offer' THEN 1

END

)

Because COUNT(expression) ignores NULL values, rows for which the CASE returns NULL are not counted.

Be deliberate about the expression because COUNT(*) has different semantics.

Aggregate FILTER

PostgreSQL supports filtering an individual aggregate:

`sql

COUNT(*) FILTER (

WHERE status = 'offer'

)

This often communicates intent more clearly when several aggregates operate on different row subsets.

WHERE versus aggregate FILTER

A query-level WHERE removes rows from every aggregate.

An aggregate FILTER affects only the particular aggregate to which it belongs.

For example, if a report needs both total applications and offers, using:

`sql

WHERE status = 'offer'

would remove non-offer rows from the total as well.

CASE for categorization

CASE can also produce report dimensions:

`sql

CASE

WHEN salary < 75000 THEN 'low'

WHEN salary < 150000 THEN 'medium'

ELSE 'high'

END

Be careful with condition ordering because the first matching branch wins.

NULL semantics

Conditional aggregation frequently depends on NULL behavior.

Always confirm whether a non-matching row should become:

because aggregate functions treat those differently.

Conditional aggregation is most useful when several metrics share the same base row set and grouping grain.

DISTINCT, ORDER BY, and pagination solve different problems and should not be treated as interchangeable.

DISTINCT

SELECT DISTINCT removes duplicate rows from the selected result expressions.

`sql

SELECT DISTINCT country

FROM candidates;

The duplicate definition applies to the selected output values.

If more columns are selected, those columns participate in determining whether result rows are duplicates.

`sql

SELECT DISTINCT country, city

FROM candidates;

now produces distinct country-and-city combinations rather than distinct countries.

DISTINCT should not hide modeling errors

A common mistake is adding DISTINCT after a query unexpectedly returns duplicate rows.

Sometimes that is appropriate, but duplication can also indicate that the query structure created a different row grain from the intended one.

Understand the source of duplication before suppressing it.

ORDER BY

SQL result ordering should be considered undefined unless it is explicitly requested.

`sql

ORDER BY created_at DESC

may still be insufficient for deterministic pagination if many rows share the same timestamp.

Add a stable tie-breaker:

`sql

ORDER BY created_at DESC, id DESC

OFFSET pagination

A typical page query is:

`sql

ORDER BY created_at DESC, id DESC

LIMIT 20 OFFSET 40

This is simple and useful for many interfaces.

However, large offsets may require the database to process rows that are eventually discarded.

Additionally, if records are inserted or deleted between page requests, users may observe skipped or repeated rows.

Keyset pagination

For large or frequently changing ordered datasets, applications often use the previous page's ordering key as the next boundary.

Conceptually:

`sql

WHERE (created_at, id) < (:created_at, :id)

ORDER BY created_at DESC, id DESC

LIMIT 20

The exact comparison must match the sort direction and database semantics.

Keyset pagination trades random page-number navigation for more stable incremental traversal.

Determinism

A deterministic order uniquely positions rows relative to each other for the use case.

For pagination, choose ordering keys that are stable and sufficiently unique.

Do not confuse duplicate elimination with deterministic sorting: DISTINCT changes which result rows remain, while ORDER BY defines their requested order.

Complex reporting SQL becomes unreliable when developers start by writing expressions instead of first defining what one output row represents.

A disciplined design process prevents subtle double counting and contradictory metrics.

1. Define the result grain

State the meaning of one result row before writing SQL.

Examples:

Every selected dimension should be consistent with that grain.

2. Define the population

Determine which base rows are eligible before aggregation.

For example:

These conditions normally belong in row-level filtering.

3. Define every metric independently

For each metric write down:

For example, offer_rate might mean:

`text

offers / submitted applications

but another stakeholder might mean:

`text

offers / completed applications

The SQL can be syntactically correct while answering the wrong business question.

4. Keep WHERE and HAVING responsibilities separate

Use WHERE to decide which source rows participate.

Use HAVING when the requirement filters groups based on calculated group characteristics.

For example:

`sql

HAVING COUNT(*) >= 10

can require a minimum sample size after grouping.

5. Make NULL behavior explicit

A missing salary can mean unknown, not zero.

Replacing every NULL with zero before averaging could materially distort the metric.

Likewise, division should handle zero denominators intentionally.

6. Use conditional aggregation for related metrics

When several metrics share one population and grain, conditional aggregates can calculate them consistently.

For example:

can all use the same candidate-level grouping.

7. Avoid accidental fan-out

When a later report combines data from several one-to-many relationships, row multiplication can inflate totals.

For example, if one candidate has three applications and four tags, combining both relationships without controlling grain can produce twelve intermediate rows.

Solutions can include:

Do not automatically apply DISTINCT to every aggregate because that can hide the symptom while changing metric semantics.

8. Validate using small known datasets

Create fixtures where the expected totals can be calculated manually.

Include cases with:

9. Validate invariants

Examples include:

10. Define ordering once metrics are correct

Once metrics are correct, define presentation ordering.

If the report is paginated, include deterministic tie-breaking.

11. Measure performance only after correctness is established

Indexes, pre-aggregation, materialized structures, and other optimizations can improve execution later.

An incorrect query that runs in 50 milliseconds is still incorrect.

Senior SQL work requires protecting metric meaning as aggressively as query performance.

A join combines rows from two table expressions according to a relationship between them.

The key difference between join types is what happens when one side has no matching row.

INNER JOIN

An inner join returns rows for which the join condition matches on both sides.

`sql

SELECT

a.id,

j.title

FROM applications AS a

JOIN jobs AS j

ON j.id = a.job_id;

Applications without a matching job and jobs without a matching application are not represented.

LEFT JOIN

A left outer join preserves every qualifying row from the left side.

When no right-side row matches, right-side columns are NULL.

`sql

SELECT

c.id,

a.id AS application_id

FROM candidates AS c

LEFT JOIN applications AS a

ON a.candidate_id = c.id;

This can be useful when candidates with zero applications must still appear.

RIGHT JOIN

A right join performs the corresponding preservation for the right side.

Many teams prefer to rewrite a right join as a left join with table order reversed because this can make query flow easier to read, but both forms are valid.

FULL JOIN

A full outer join preserves unmatched rows from both sides.

It is useful when comparing two datasets and both missing-left and missing-right cases matter.

Self-join

A self-join joins a table to another logical reference to itself.

Aliases distinguish the instances.

For example, an employee table containing manager_id can be joined back to the employee table to retrieve the manager name.

Join cardinality

A join does not necessarily produce one output row per row on either side.

If one parent row has five matching child rows, joining them normally produces five rows for that parent.

If two one-to-many relationships are joined simultaneously, rows can multiply further.

This matters greatly for aggregates.

ON versus WHERE with outer joins

Predicates placed in ON participate in deciding which right-side rows match.

Predicates applied later in WHERE can remove NULL-extended outer-join rows.

For example, placing a right-table predicate in WHERE can unintentionally make a left join behave more like an inner join for that condition.

The correct join type therefore depends on which entities must remain in the result when no relationship exists.

A subquery is a query embedded inside another SQL statement.

Its meaning depends on where it is used and how many values it is expected to return.

Scalar subquery

A scalar subquery is used where one value is expected.

`sql

SELECT

employee_id,

salary,

(

SELECT AVG(salary)

FROM employees

) AS company_average

FROM employees;

A scalar subquery must satisfy the database rules for producing a single scalar result.

Table subquery

A subquery in FROM behaves like a derived table and should normally have an alias.

`sql

SELECT x.department_id,

x.average_salary

FROM (

SELECT

department_id,

AVG(salary) AS average_salary

FROM employees

GROUP BY department_id

) AS x;

EXISTS

EXISTS tests whether its subquery returns at least one row.

`sql

SELECT c.id

FROM candidates AS c

WHERE EXISTS (

SELECT 1

FROM applications AS a

WHERE a.candidate_id = c.id

);

The selected expression inside an ordinary EXISTS test is generally not the important part; the condition asks whether any qualifying row exists.

Correlated subquery

The previous example is correlated because the inner query references c.id from the outer query.

Conceptually, its result depends on the current outer row.

IN

IN compares a value against a set of values.

`sql

WHERE department_id IN (

SELECT id

FROM departments

WHERE active = TRUE

)

IN expresses membership naturally.

Be careful with NULL semantics, especially with NOT IN, because a NULL in the comparison set can make exclusion logic behave differently from intuitive two-valued Boolean reasoning.

NOT EXISTS

NOT EXISTS is often a clear way to express an anti-relationship:

`sql

SELECT c.id

FROM candidates AS c

WHERE NOT EXISTS (

SELECT 1

FROM applications AS a

WHERE a.candidate_id = c.id

);

This asks for candidates for whom no matching application exists.

Join versus EXISTS

If the requirement is merely to check whether a related row exists, EXISTS often expresses that intent directly and avoids creating repeated outer rows solely because several matching child rows exist.

If columns from the related table must be returned, a join may be more natural.

Choose the construct that matches the semantic question rather than assuming joins or subqueries are always superior.

A Common Table Expression, or CTE, is introduced using WITH and gives an auxiliary statement a name that can be referenced by the larger query.

Ordinary CTE

`sql

WITH active_candidates AS (

SELECT id, name

FROM candidates

WHERE active = TRUE

)

SELECT *

FROM active_candidates;

CTEs can improve readability when a larger query contains meaningful intermediate concepts.

They are especially useful when an intermediate result is referenced several times or when separating query stages makes business logic easier to verify.

CTEs are not automatically temporary physical tables

A CTE is a query construct. Do not assume that writing WITH means PostgreSQL must always physically create and persist an intermediate table.

Planner treatment depends on the query and CTE properties.

Recursive CTEs

Recursive CTEs can express hierarchical or graph-like traversal.

A recursive query typically contains:

1. A nonrecursive base term

2. A recursive term

3. A union connecting them

For example, an employee hierarchy can begin with a manager and repeatedly find employees whose manager_id references already discovered employees.

`sql

WITH RECURSIVE hierarchy AS (

SELECT id, manager_id, name, 0 AS depth

FROM employees

WHERE id = $1

UNION ALL

SELECT

e.id,

e.manager_id,

e.name,

h.depth + 1

FROM employees AS e

JOIN hierarchy AS h

ON e.manager_id = h.id

)

SELECT *

FROM hierarchy;

UNION ALL versus UNION

UNION ALL preserves duplicates, while UNION removes duplicates.

Recursive queries often use UNION ALL, but the correct choice depends on the desired semantics and cycle behavior.

Cycle considerations

Recursive traversal over arbitrary graph data can revisit nodes.

Queries need an explicit strategy when cycles are possible rather than assuming the hierarchy is perfectly acyclic.

Depth and paths

Useful recursive output often includes calculated information such as:

These values can help order, inspect, or constrain traversal.

Readable staging

CTEs are also useful without recursion for breaking a complex query into stages such as:

1. Eligible applications

2. Candidate-level metrics

3. Ranked candidates

4. Final presentation

Use CTEs to express meaningful query stages, not simply to wrap every SELECT in another name.

Window functions perform calculations across rows related to the current row without collapsing those rows into one output row.

This is the major conceptual difference from ordinary grouped aggregation.

GROUP BY

`sql

SELECT

department_id,

AVG(salary)

FROM employees

GROUP BY department_id;

This produces one row per department.

Window aggregate

`sql

SELECT

id,

department_id,

salary,

AVG(salary) OVER (

PARTITION BY department_id

) AS department_average

FROM employees;

Every employee row remains, while each row also receives the average for its department.

PARTITION BY

PARTITION BY divides the input rows into independent windows for the function.

Without it, the entire qualifying input can act as one partition.

ORDER BY inside OVER

Window ordering defines sequence within each partition.

This matters for functions such as:

The window ORDER BY is not the same thing as the query's final ORDER BY.

One controls calculation order inside the window; the other controls presentation order of the final result.

Window frame

Some window functions operate over a frame within the partition.

For running calculations, explicitly defining a frame can make intent clear.

For example:

`sql

SUM(amount) OVER (

PARTITION BY account_id

ORDER BY created_at, id

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

)

calculates a running total using rows through the current row in the defined ordering.

Default frame behavior

Default frames can interact with peer rows in ways that surprise developers, especially when ordering values are tied.

For important running calculations, understand and often specify the intended frame explicitly.

Filtering window results

Window functions are evaluated at a stage where their values cannot ordinarily be referenced directly by a normal WHERE condition in the same SELECT level.

A common pattern is to calculate them in a subquery or CTE and filter in the outer query.

Keep grain

Window functions are powerful because they add context without changing the grain of the base result.

Use them when you need both row-level detail and group-level or sequence-level calculations.

Window functions solve many problems that otherwise require complicated self-joins or procedural processing.

ROW_NUMBER

row_number() assigns a sequential number to each row within its partition according to the window ordering.

`sql

ROW_NUMBER() OVER (

PARTITION BY department_id

ORDER BY salary DESC, id

)

Each row receives a distinct number.

RANK

rank() gives tied peer rows the same rank and leaves gaps afterward.

If salaries are:

`text

100

100

90

ranks conceptually become:

`text

1

1

3

DENSE_RANK

dense_rank() also gives peers the same rank but does not leave gaps:

`text

1

1

2

Top-N per group

One common pattern is to rank rows inside each group and filter externally:

`sql

WITH ranked AS (

SELECT

e.*,

ROW_NUMBER() OVER (

PARTITION BY department_id

ORDER BY salary DESC, id

) AS position

FROM employees AS e

)

SELECT *

FROM ranked

WHERE position <= 3;

This returns at most three rows per department under the defined ordering.

If business rules require all ties at the cutoff to be included, rank() or dense_rank() may be more appropriate than row_number().

LAG

lag() retrieves a value from a preceding row in the window ordering.

It is useful for:

LEAD

lead() performs the corresponding lookup into a following row.

Deterministic ordering

If the ranking ORDER BY contains ties and the requirement needs exactly one deterministic winner, include a stable tie-breaker.

For example:

`sql

ORDER BY score DESC, created_at ASC, id ASC

Peer semantics

Understand whether the business requirement means:

Those requirements can produce different answers and determine which ranking function is appropriate.

Complex SQL becomes incorrect most often because query stages operate at incompatible grains.

A senior SQL developer should be able to state what one row means after every important stage.

1. Start with the final grain

Suppose the requirement is one row per candidate containing:

The final grain is one candidate.

Every intermediate dataset must either preserve that grain or deliberately transition back to it before being combined.

2. Avoid uncontrolled one-to-many fan-out

Joining candidates directly to applications and also directly to skills can multiply rows:

`text

1 candidate

× 4 applications

× 5 skills

= 20 joined rows

Aggregating after this multiplication can inflate application counts.

Instead, pre-aggregate each one-to-many relationship to candidate grain before combining them when that matches the requirement.

3. Use EXISTS for Boolean relationship questions

If the requirement is only:

“Has this candidate ever received an offer?”

an EXISTS expression can communicate the question directly without joining all offer rows into the main result.

4. Create meaningful CTE stages

For example:

Each CTE should have a clearly documented grain.

5. Use window functions after producing the intended row population

If ranking candidates by application count, first calculate one row per candidate and then rank those candidate rows.

Ranking raw application rows and aggregating later would answer a different question.

6. Top-one-per-group

To find the latest application per candidate, calculate row_number() partitioned by candidate and order by a deterministic recency definition.

Then retain row_number = 1 in an outer stage.

7. Preserve missing entities deliberately

If candidates with no applications must appear, join candidate-level metrics using LEFT JOIN and define what missing metrics mean.

For example, zero applications may reasonably become 0, whereas a missing last-application timestamp should remain NULL.

8. Keep existence and counting distinct

Do not count joined offer rows merely to answer a Boolean existence question unless that count itself is required.

Likewise, do not use DISTINCT automatically after a fan-out. Fix the stage at which duplication was introduced.

9. Validate each stage

Before composing the entire query, verify:

Small known datasets make these checks easier.

10. Separate correctness from optimization

After correctness is established, examine the execution plan and indexes in the performance-focused stage of optimization.

Do not rewrite a clear correct query into a semantically risky form based only on assumptions about which SQL syntax should be faster.

The most important principle is that every join, aggregation, subquery, and window function must operate at a deliberate grain.

A database index is an additional data structure maintained alongside table data to help the database locate qualifying rows efficiently.

Without a useful index, the database may need to examine a large portion of a table to find matching rows.

B-tree indexes

PostgreSQL creates B-tree indexes by default.

They are well suited to many common predicates involving values that have an ordered comparison relationship.

Typical cases include:

For example:

`sql

SELECT id, email

FROM users

WHERE email = $1;

may benefit from an index on email if the condition is selective enough.

Likewise:

`sql

SELECT id, created_at

FROM applications

WHERE created_at >= $1

AND created_at < $2;

can often benefit from a B-tree index on created_at.

Indexes and ORDER BY

A B-tree index may also provide rows in the requested order and avoid a separate sort when the index order matches the query requirements.

This can be particularly useful with ORDER BY ... LIMIT because the database may be able to stop after retrieving the first required rows.

An index is not always faster

If a query needs a very large proportion of the table, sequentially scanning the table can be cheaper than traversing an index and then visiting many table rows.

The optimizer chooses between alternatives using cost estimates.

Index costs

Indexes are not free.

They require:

Adding indexes to every column can therefore make write-heavy workloads slower.

Selectivity matters

An index is generally most valuable when it helps identify a relatively small useful subset of rows or supplies useful ordering.

For a Boolean column containing roughly half true and half false values, a standalone index might be much less useful than an index serving a highly selective identifier lookup.

The correct question is not simply “Does this column have an index?” but “Does this index support an important workload efficiently enough to justify its maintenance cost?”

A multicolumn index stores more than one indexed key in a defined order.

For example:

`sql

CREATE INDEX applications_candidate_created_idx

ON applications (

candidate_id,

created_at DESC

);

This can fit a query such as:

`sql

SELECT id, created_at

FROM applications

WHERE candidate_id = $1

ORDER BY created_at DESC

LIMIT 20;

Column order matters

A multicolumn B-tree index is not equivalent to several independent single-column indexes.

The ordering of index keys determines how efficiently portions of the structure can be located and scanned.

A common useful pattern is:

1. Equality predicates on leading columns

2. Then range or ordering columns

For example:

`sql

WHERE tenant_id = $1

AND created_at >= $2

ORDER BY created_at

often maps naturally to:

`sql

(tenant_id, created_at)

Do not apply rules mechanically

Index design depends on the complete workload.

Important factors include:

One index may support several queries

A composite index can sometimes support predicates using a useful prefix or otherwise usable subset of its keys.

However, do not assume (a, b) provides the same access characteristics as both (a) and (b) independently.

Modern PostgreSQL planner behavior

Current PostgreSQL can use multicolumn B-tree indexes in more cases than older simplistic “leftmost prefix only” explanations suggest, including skip-scan strategies in appropriate workloads.

Therefore interview answers should avoid the absolute claim that a later column can never contribute unless every preceding column is constrained.

The practical design principle remains to place columns according to the most important real predicates and ordering requirements and confirm behavior with EXPLAIN.

A normal B-tree index is appropriate for many workloads, but PostgreSQL provides several more specialized indexing techniques.

Partial indexes

A partial index contains entries only for rows satisfying an index predicate.

For example:

`sql

CREATE INDEX jobs_active_created_idx

ON jobs (created_at DESC)

WHERE status = 'active';

This can be useful when active jobs are a relatively small, frequently queried subset.

The query condition must imply the partial-index predicate in a form the planner can recognize.

A partial index should not be used as a substitute for proper partitioning.

Expression indexes

An index can be created on an expression rather than only a raw column.

`sql

CREATE INDEX users_lower_email_idx

ON users (LOWER(email));

This can support queries using the corresponding expression:

`sql

WHERE LOWER(email) = LOWER($1)

Expression indexes add computation and maintenance cost when indexed data changes.

Covering indexes and INCLUDE

PostgreSQL supports non-key included columns.

`sql

CREATE INDEX jobs_company_idx

ON jobs (company_id)

INCLUDE (title, salary);

This can allow an index-only scan when the query needs only data available from the index and PostgreSQL visibility conditions permit avoiding heap access.

Including payload columns does not guarantee that every query becomes an index-only scan.

Wide included values also increase index size.

GIN

GIN indexes are useful for data where one logical value contains multiple searchable components, such as several full-text and collection-oriented workloads.

GiST and SP-GiST

These support classes of searches that do not fit ordinary scalar B-tree ordering, depending on the data type and operator class.

BRIN

BRIN indexes summarize ranges of table blocks and can be effective on very large tables where indexed values strongly correlate with physical row location.

They are much smaller than typical B-tree indexes but have different selectivity and access characteristics.

Use the index type that matches the actual operators and data distribution rather than selecting one merely because it sounds more advanced.

EXPLAIN is one of the most important tools for investigating SQL performance in PostgreSQL.

EXPLAIN

`sql

EXPLAIN

SELECT *

FROM jobs

WHERE company_id = 10;

This asks PostgreSQL to display the plan chosen by the planner without executing the query in the normal EXPLAIN form.

The plan contains estimates including:

EXPLAIN ANALYZE

`sql

EXPLAIN ANALYZE

SELECT ...;

executes the statement and reports actual execution information in addition to estimates.

Because it actually runs the statement, using EXPLAIN ANALYZE on modifying statements can change data unless the investigation is safely wrapped or otherwise controlled.

Read plans from the inside out

Execution-plan trees are usually easiest to understand by examining lower-level child operations and then how their results feed parent nodes.

Common nodes include:

Estimated versus actual rows

One of the most important comparisons is:

`text

estimated rows

versus

actual rows

Large cardinality-estimation errors can cause the planner to choose inappropriate joins or access strategies.

Loops

A node can execute multiple times.

When evaluating actual work, consider both per-loop rows/timing and the number of loops.

Buffers

Options such as BUFFERS can provide information about buffer access and help distinguish CPU work from data-access behavior.

Do not optimize by node name alone

A sequential scan is not automatically bad.

For a small table or a query retrieving most rows, it can be the correct plan.

Likewise, an index scan is not automatically proof of a good query.

Evaluate total execution behavior relative to workload, data volume, and latency requirements.

A cost-based optimizer must predict how much work different execution strategies will require before actually running the query.

A central part of this process is estimating how many rows each operation will produce.

Cardinality estimate

Cardinality is the estimated or actual number of rows produced by a stage.

For example, a predicate might be estimated to match:

`text

100 rows out of 10 million

or:

`text

7 million rows out of 10 million

Those two cases can justify very different access strategies.

Selectivity

Selectivity represents how strongly a predicate narrows the data.

Highly selective predicates often make targeted index access attractive.

Low-selectivity predicates may make a sequential scan more efficient.

Planner statistics

PostgreSQL collects statistics about table data and distributions that the planner uses to estimate result sizes.

ANALYZE collects or refreshes these statistics.

Autovacuum normally performs automatic analysis as tables change, but statistics can become temporarily inaccurate after major data changes.

Why incorrect estimates matter

Suppose PostgreSQL estimates a join input will produce ten rows but it actually produces one million.

A join strategy that is inexpensive for ten rows may become extremely expensive for one million.

Cardinality errors can affect choices involving:

Correlated columns

Per-column statistics may not capture relationships between columns.

For example, country and state are not independent.

PostgreSQL supports extended statistics across selected column sets to improve estimates in cases where useful cross-column relationships exist.

Optimization workflow

When estimates and actual rows differ significantly:

1. Confirm statistics are current

2. Inspect data distribution

3. Check whether columns are correlated

4. Consider statistics-target or extended-statistics needs

5. Re-run the representative plan

Do not disable planner strategies globally merely because one query receives a poor plan.

Fix the information, schema, or query issue when practical.

Production SQL optimization should begin with workload evidence rather than with CREATE INDEX.

A query can be slow because of SQL structure, data distribution, locking, I/O, memory, inaccurate statistics, result size, application behavior, or missing indexes.

1. Identify the actual workload

Start with questions such as:

A query taking one second once per day may matter less than a 50 ms query executed millions of times.

PostgreSQL provides pg_stat_statements for tracking planning and execution statistics across statements when the module is enabled.

2. Reproduce representative conditions

Use realistic:

Do not optimize from a tiny development table if production contains hundreds of millions of rows.

3. Capture the plan

Use EXPLAIN, then controlled EXPLAIN ANALYZE where safe.

Inspect:

4. Find the first meaningful divergence

A huge estimated-versus-actual row difference low in the plan can explain several poor decisions higher in the tree.

Do not focus only on the visually largest top-level cost.

5. Check statistics

Confirm the table has current statistics.

For correlated predicates, determine whether extended statistics could improve cardinality estimation.

6. Review indexes against workload

Ask whether existing indexes:

Do not add several overlapping indexes to solve one query without considering write overhead.

7. Consider query shape

Possible improvements include:

Every rewrite must preserve duplicate and NULL semantics.

8. Consider physical strategy

Very large workloads may require architectural changes such as:

A single additional index does not solve every scaling problem.

9. Measure write impact

Every new index increases storage and maintenance cost.

Before keeping it, evaluate:

10. Validate after the change

Repeat the same representative workload.

Compare:

11. Monitor overall statement impact

A local optimization can make one query faster while increasing write cost enough to hurt the rest of the platform.

The best index strategy optimizes total workload value rather than maximizing the number of queries that happen to use indexes.

A transaction groups database operations that belong to one logical unit of work.

For example, transferring money between accounts may require:

1. Decreasing one balance

2. Increasing another balance

3. Recording the transfer

Those changes should normally succeed or fail together.

BEGIN

A transaction can be started explicitly:

`sql

BEGIN;

Statements then execute inside the transaction until it finishes.

COMMIT

`sql

COMMIT;

commits the transaction and makes its successful changes part of the durable database state, subject to the database durability guarantees and configuration.

ROLLBACK

`sql

ROLLBACK;

abandons changes made by the current transaction that have not been committed.

This is useful when one operation in a multi-step workflow fails.

Atomicity

Atomicity means the logical transaction is treated as one unit with respect to committing its changes.

The application should not leave half of a required database state change committed simply because a later statement failed.

Consistency

Transactions should move the database from one valid state to another while database constraints and application rules continue to hold.

Database constraints are an important part of protecting those invariants.

Isolation

Isolation controls how concurrent transactions observe and interact with one another.

It does not mean all transactions necessarily execute one at a time.

PostgreSQL uses MVCC and locking mechanisms to allow concurrency while preserving the guarantees of the selected isolation level.

Durability

Once a transaction commits successfully, its effects are expected to survive normal subsequent processing according to the durability guarantees of the database configuration.

Transaction boundaries

Keep a transaction focused on database work that truly belongs together.

Do not unnecessarily keep a transaction open while:

Long transactions can retain locks, consume resources, and delay cleanup of old row versions.

The transaction boundary should reflect one meaningful consistency boundary rather than one entire application request by default.

Database constraints protect rules even when data is written from several applications, scripts, migrations, or administrative tools.

Primary key

A primary key identifies each row using a value or combination of values that must satisfy the database primary-key rules.

`sql

CREATE TABLE candidates (

id bigint PRIMARY KEY,

email text NOT NULL

);

A table should have a stable identifier appropriate to its domain and access patterns.

UNIQUE constraint

A unique constraint prevents duplicate values according to the database uniqueness semantics.

`sql

email text UNIQUE

Use it when duplicate values would violate a real invariant.

Application-level pre-checks alone are not sufficient because two concurrent requests can both observe that a value appears available and then attempt to insert it.

The database constraint arbitrates the race safely.

Foreign key

A foreign key requires referencing values to correspond to an eligible key in the referenced relation.

`sql

candidate_id bigint NOT NULL

REFERENCES candidates(id)

This protects referential integrity.

Referential actions

Foreign keys can define behavior for referenced-row changes, such as:

Choose actions from domain semantics rather than automatically using ON DELETE CASCADE everywhere.

CHECK constraint

A check constraint requires inserted or updated rows to satisfy a Boolean condition according to constraint semantics.

`sql

CHECK (salary >= 0)

It is appropriate for invariants expressible from values in the row under the supported database rules.

NOT NULL

NOT NULL requires a column to contain a non-null value.

Use it when missing data is not a legitimate state.

Constraints versus application validation

Application validation improves user experience and provides domain-specific messages.

Database constraints provide the final integrity boundary.

Use both where appropriate.

Foreign-key indexes

The referenced key is backed by the required uniqueness mechanism, but the referencing foreign-key column is not automatically guaranteed to have the index needed for every workload.

When parent updates or deletes need to locate dependent child rows efficiently, indexing the foreign-key column is often beneficial.

Constraints express truth about the data model. Indexes primarily address data access and enforcement performance.

PostgreSQL uses Multi-Version Concurrency Control, or MVCC, to manage concurrent access without requiring ordinary readers and writers to serialize every operation through one global lock.

Row versions

Conceptually, updates create versions whose visibility depends on transaction state and the snapshot used by the reading statement or transaction.

Readers therefore do not simply read whatever physical row version was most recently written by any session.

Read Committed

Read Committed is PostgreSQL's default isolation level.

Each command starts with a snapshot appropriate to that statement.

As a result, two SELECT statements inside the same transaction can observe different committed database states if another transaction commits between them.

This isolation level is appropriate for many normal application operations, but multi-statement logic must account for concurrent changes.

Repeatable Read

Repeatable Read provides a stable transaction-level view for ordinary snapshot reads once the relevant transaction snapshot is established.

The transaction does not simply see newly committed changes from other transactions in later reads.

Concurrent update conflicts can cause transactions to abort rather than silently producing an inconsistent result.

Serializable

Serializable provides the strongest PostgreSQL isolation semantics.

The system attempts to ensure that successfully committed Serializable transactions have an effect equivalent to some serial ordering of those transactions.

This does not mean PostgreSQL literally executes one transaction at a time.

It allows concurrency while detecting dangerous dependency patterns.

A transaction can fail with a serialization error and must be retried as a complete transaction when appropriate.

Isolation is not a substitute for constraints

Use primary keys, unique constraints, foreign keys, and appropriate checks for invariants that can be expressed structurally.

Isolation controls concurrent observation and interaction; constraints enforce data rules.

Choosing isolation

Use the weakest isolation level that correctly supports the transaction semantics, but do not lower isolation merely for perceived performance without understanding race conditions.

Ask:

Concurrency correctness should be derived from the complete transaction rather than one query in isolation.

MVCC allows substantial concurrency, but some workflows require transactions to explicitly coordinate access to the same logical records.

SELECT FOR UPDATE

A locking query such as:

`sql

SELECT balance

FROM accounts

WHERE id = $1

FOR UPDATE;

locks the selected row against conflicting modifications according to PostgreSQL row-lock semantics until the current transaction ends.

This can be useful for read-modify-write workflows when the transaction must base a decision on data that another transaction should not modify concurrently in a conflicting way.

Example

Consider inventory:

1. Read current inventory

2. Verify enough stock exists

3. Reduce stock

If concurrent transactions independently read the same value and then both write based on that stale value, correctness may fail depending on the statement design and isolation strategy.

Explicit locking is one possible solution.

Atomic conditional updates are another useful technique:

`sql

UPDATE inventory

SET quantity = quantity - $2

WHERE product_id = $1

AND quantity >= $2

RETURNING quantity;

This may eliminate the need for a separate read-and-lock step for some workflows.

Lock only what is required

Broad locking reduces concurrency.

Prefer the smallest consistency boundary compatible with correctness.

Deadlock

A deadlock can occur when transactions wait on resources held by each other.

For example:

Transaction A:

1. Locks account 1

2. Waits for account 2

Transaction B:

1. Locks account 2

2. Waits for account 1

Neither can proceed without intervention.

PostgreSQL detects deadlocks and aborts a transaction so progress can continue.

Consistent lock ordering

A primary defense is acquiring required locks in the same deterministic order across transactions.

For transfers between two account IDs, an application can lock the lower ID first and higher ID second regardless of transfer direction.

Short transactions

Do not hold locks while performing unnecessary network calls or waiting for users.

Retry behavior

If PostgreSQL aborts a transaction because of a deadlock, the application should normally retry the logical operation when retry is safe.

Retry the transaction from a valid boundary rather than continuing from a transaction already marked failed.

Locking is a correctness mechanism with concurrency cost, not something to add around every read automatically.

Relational schema design begins with the meaning and relationships of data rather than with the screens that happen to display it.

Identify entities

Examples might include:

Each table should represent a coherent concept with a stable key.

One-to-many relationships

A company can have many jobs.

A foreign key on the job identifies the company:

`sql

jobs.company_id

Many-to-many relationships

A candidate can have many skills, and one skill can belong to many candidates.

Represent this naturally with an associative table:

`sql

candidate_skills (

candidate_id,

skill_id

)

A composite primary key or unique constraint can prevent duplicate associations.

Normalization

Normalization aims to structure related data so that facts are represented at an appropriate location instead of unnecessarily repeated across many rows.

For example, duplicating the company name, billing email, and address into every job row can create update inconsistencies when company information changes.

Instead, those company attributes commonly belong to the company entity.

Functional dependency reasoning

Ask what attributes depend on which keys.

If one company ID determines one canonical company billing address, storing that address repeatedly in unrelated child rows creates redundant copies of the same fact.

Avoid over-normalization as ritual

A design should remain understandable and serve the workload.

Not every derived or duplicated value is automatically wrong.

For example, preserving a historical price on an order line can be correct because it represents the price at the time of the transaction rather than a redundant copy of today's product price.

Denormalization

Denormalization intentionally duplicates or precomputes data to meet a demonstrated access, reporting, or performance need.

Examples can include:

Denormalization creates synchronization responsibility.

Document which value is authoritative and how derived copies are updated or repaired.

Constraints

Schema structure should express invariants using:

Do not make application code the only guardian of structural integrity.

Design from behavior too

A clean logical schema still requires consideration of:

Normalize for correctness first, then introduce deliberate exceptions when evidence justifies them.

High-concurrency database correctness requires several complementary mechanisms rather than one universal lock or isolation setting.

1. Define the invariant first

Before choosing transaction syntax, state what must never become false.

Examples:

The invariant determines the appropriate database mechanism.

2. Prefer declarative constraints where possible

Use:

for invariants those mechanisms can represent.

Do not replace a UNIQUE constraint with:

`text

SELECT whether value exists

then INSERT

because concurrent sessions can race between the check and insert.

3. Prefer atomic statements when they express the rule

Inventory decrement can often be represented as:

`sql

UPDATE inventory

SET quantity = quantity - $1

WHERE product_id = $2

AND quantity >= $1

RETURNING quantity;

The application checks whether a row was updated.

This can be simpler than separately selecting, locking, validating, and updating.

4. Use explicit locks when coordination requires them

Some workflows genuinely require reading multiple rows before deciding how they should change.

Use the narrowest appropriate row locks and acquire them in a deterministic order.

5. Choose isolation deliberately

Read Committed is appropriate for many workloads.

For multi-statement decisions requiring a stable snapshot, Repeatable Read may better match the semantics.

When correctness requires outcomes equivalent to serial transaction execution, Serializable can provide stronger protection but requires complete-transaction retry handling for serialization failures.

Do not simply set every transaction to Serializable without considering throughput, contention, and retry design.

6. Design retries before production

Transactions may fail because of:

A retry should restart the logical transaction from a valid boundary.

External side effects complicate this.

If a database transaction calls a payment provider and the database subsequently rolls back, the payment provider does not automatically undo the charge.

Use patterns such as:

when coordinating database state with external systems.

7. Keep transactions short

Never hold database locks unnecessarily while:

8. Avoid deadlocks structurally

Standardize lock ordering throughout the application.

If several rows must be locked, derive an ordering rule that all transactions follow.

Deadlocks can still occur, so applications must handle transaction aborts correctly.

9. Design for contention

A schema can be logically correct but create one hot row updated by thousands of transactions.

Examples include global counters or one frequently updated aggregate record.

Consider whether the invariant truly requires one centralized write point.

Possible alternatives can include:

Do not weaken consistency only to improve benchmark throughput without understanding the business requirement.

10. Protect historical meaning

Transactional systems often need historical facts such as:

Do not replace historical state with a join to mutable current state when the domain requires an immutable snapshot.

11. Monitor concurrency

Production investigation should include:

PostgreSQL exposes lock information through pg_locks, which can help inspect currently held and awaited locks.

12. Optimize after correctness

Do not remove locking or reduce isolation solely because a benchmark improves.

First establish which correctness property the mechanism protects, then improve the implementation without losing that guarantee.

A production SQL design succeeds when concurrent execution remains correct under races, retries, failures, and partial infrastructure problems.

Table partitioning divides one logical table into smaller physical partitions while allowing applications to query the partitioned table through one logical parent relation.

Declarative partitioning

PostgreSQL supports declarative partitioning using methods including:

Range partitioning

Range partitioning is useful when rows naturally fall into ordered ranges.

Common examples include:

For example, event data might be partitioned by month.

List partitioning

List partitioning assigns specific values to specific partitions.

Examples can include:

Use it only when the value categories form a practical partition-management strategy.

Hash partitioning

Hash partitioning distributes rows using a hash of the partition key.

It can be useful when the goal is distributing rows relatively evenly rather than grouping them by a natural range.

Partition routing

Rows inserted into the partitioned parent are routed to a matching partition according to the partition key and partition bounds.

If no appropriate partition exists, the insert can fail unless the design includes a suitable default partition or matching partition.

Partition pruning

When query predicates align with the partition key, PostgreSQL can exclude partitions that cannot contain matching rows.

This reduces unnecessary scanning.

Partition pruning is valuable only when queries provide usable predicates and the partitioning scheme matches real access patterns.

Partitioning is not indexing

Individual partitions can still need indexes.

Partitioning does not automatically make every query fast.

A query accessing many partitions can still perform substantial work.

Operational benefits

Partitioning can simplify operations such as:

Costs

Too many partitions can increase planning and management overhead.

Do not partition a small table simply because partitioning exists.

Partition when data volume, retention strategy, maintenance, or workload characteristics justify the additional design complexity.

Views and materialized views both provide named query results, but they have very different storage and freshness behavior.

Normal view

A normal view stores a query definition rather than a persistent copy of its query result.

`sql

CREATE VIEW active_jobs AS

SELECT *

FROM jobs

WHERE status = 'active';

When the view is referenced, PostgreSQL evaluates the underlying query as part of the referencing statement.

This means a view normally reflects current underlying table data according to transaction visibility.

Materialized view

A materialized view persists the query result in table-like storage.

`sql

CREATE MATERIALIZED VIEW monthly_metrics AS

SELECT ...;

Reading the materialized view can therefore avoid recalculating an expensive underlying query every time.

Freshness tradeoff

Persisted results can become stale.

Use:

`sql

REFRESH MATERIALIZED VIEW monthly_metrics;

to replace the stored contents with results from its defining query.

The application must define how fresh the data needs to be.

For example:

Indexes

Materialized views can be indexed like table-like stored data.

This can make repeated analytical access substantially faster.

Concurrent refresh

PostgreSQL supports concurrent materialized-view refresh under specific requirements.

This allows existing contents to remain available to concurrent readers while replacement results are produced, but the required unique-index conditions and operational tradeoffs must be understood.

Precomputation alternatives

A materialized view is not always the correct precomputation mechanism.

Other possibilities include:

The key tradeoff is usually:

freshness versus computation cost.

Do not introduce stale derived state unless ownership, refresh, and failure behavior are clearly defined.

High-volume writes should not automatically be implemented as thousands or millions of independent single-row application round trips.

Set-based INSERT

Multiple rows can be inserted in one statement:

`sql

INSERT INTO skills (id, name)

VALUES

(1, 'SQL'),

(2, 'PostgreSQL'),

(3, 'Analytics');

This reduces per-statement overhead compared with separate requests for every row.

COPY

PostgreSQL provides COPY for moving large amounts of data between tables and external representations.

COPY FROM is commonly useful for bulk ingestion.

It operates differently from repeatedly issuing application-level INSERT statements and has its own privilege and feature constraints.

Validate operational requirements before using it in user-facing ingestion paths.

INSERT ON CONFLICT

ON CONFLICT defines an alternate action when a proposed insert conflicts with an eligible uniqueness or exclusion rule.

Two common forms are:

`sql

ON CONFLICT DO NOTHING

and:

`sql

ON CONFLICT (...) DO UPDATE

This supports atomic insert-or-update behavior driven by database conflict detection rather than an unsafe application sequence of:

1. SELECT to see if row exists

2. INSERT or UPDATE

Use constraints as arbiters

An UPSERT requires a meaningful conflict identity.

For example, an import keyed by an external record ID might have:

`sql

UNIQUE (source_system, external_id)

The constraint expresses what duplicate means.

Idempotency

Idempotency means repeating an operation does not create unintended duplicate business effects.

ON CONFLICT can be one part of an idempotency design, but it is not automatically equivalent to business idempotency.

For example, retrying:

`sql

SET retry_count = retry_count + 1

changes state on every retry even if implemented through an UPSERT.

RETURNING

RETURNING allows write statements to return affected values without a separate SELECT.

This can be useful for generated IDs and updated values.

Large transaction size

Bulk writes need sensible transaction boundaries.

One transaction containing enormous amounts of work can hold resources for a long time and create large rollback or replication impact.

On the other hand, committing every individual row produces unnecessary overhead.

Choose batch size from measurement and failure semantics.

Error strategy

Before ingestion, define:

Efficient ingestion requires both throughput and correct retry semantics.

Production database reliability requires maintenance and observability in addition to correct SQL syntax.

Why VACUUM exists

PostgreSQL MVCC creates multiple row versions over time.

Old versions cannot always be removed immediately because active transactions may still need them.

Vacuuming performs important maintenance related to reclaiming reusable space, visibility information, and transaction-ID safety.

Autovacuum

PostgreSQL normally automates vacuum and analyze activity through autovacuum.

Do not disable autovacuum simply because maintenance creates visible background work.

If one table changes extremely quickly, tune its maintenance behavior based on evidence rather than turning maintenance off globally.

VACUUM FULL

Ordinary VACUUM and VACUUM FULL are not interchangeable.

VACUUM FULL rewrites the table and requires stronger locking, so it should not be treated as routine ordinary vacuum maintenance.

ANALYZE

ANALYZE collects statistics used by the optimizer.

Poor or stale statistics can produce incorrect cardinality estimates and poor execution plans.

Autovacuum can trigger automatic analysis after sufficient table changes.

pg_stat_activity

pg_stat_activity shows information about current server processes and their activity.

It can help investigate:

Do not terminate sessions simply because they appear in this view; understand what they are doing first.

pg_stat_statements

When enabled, pg_stat_statements tracks aggregate planning and execution statistics for SQL statements.

It helps answer workload-level questions such as:

Monitoring maintenance

Useful operational signals can include:

Long-running transactions

An old transaction can interfere with removal of obsolete row versions because those versions may still need to remain visible to its snapshot.

That makes idle-in-transaction sessions an important operational concern.

Maintenance is part of application scalability. A schema that works on day one can degrade significantly if vacuuming, statistics, and transaction lifecycle are ignored.

Database availability and database recoverability are different requirements.

A replica does not eliminate the need for backups, and a backup does not automatically provide real-time failover.

Logical SQL dump

Tools such as pg_dump export database objects and data in a logical form.

Logical backups are useful for purposes including:

They differ from physical cluster backups.

Physical base backup

A physical base backup copies a PostgreSQL database cluster at the storage level in a form suitable for physical recovery.

pg_basebackup can take a base backup of a running cluster and can be used as a starting point for point-in-time recovery or a standby server.

Write-Ahead Log

PostgreSQL records changes in WAL before modified data pages need to be written to their final table files.

WAL supports crash recovery and provides the change stream used by several recovery and replication mechanisms.

Continuous archiving and PITR

With WAL archiving, the system preserves the sequence of required WAL files in addition to a base backup.

Point-in-time recovery can restore the base backup and replay WAL to a selected recovery point.

This can help recover from events such as an accidental destructive change when the required archive history is available.

Physical streaming replication

A physical standby can receive and replay WAL from a primary server.

The standby follows storage-level database changes for the cluster.

Streaming replication can reduce replication delay compared with depending only on archived WAL retrieval.

Logical replication

Logical replication operates on logical data changes associated with published database objects rather than copying the complete storage representation byte for byte.

This allows finer-grained replication scenarios.

Uses can include:

Logical and physical replication solve different problems.

Replication lag

A standby can lag behind its source.

Applications reading from replicas must understand the possibility of stale reads.

Backups must be tested

A backup that has never been restored is an unverified recovery plan.

Define and test:

Reliable systems design for recovery before an incident occurs.

Senior SQL architecture requires balancing correctness, performance, operational complexity, and recoverability.

There is no single feature such as indexing, partitioning, or replication that solves every scaling problem.

1. Begin with invariants and workload

Define:

Schema and infrastructure decisions should follow these constraints.

2. Enforce critical integrity in the database

Use:

where they represent durable invariants.

Do not rely exclusively on application pre-checks under concurrent writes.

3. Design indexes from real workloads

Capture query behavior and plans rather than indexing every column.

Evaluate:

Use partial, expression, covering, or specialized index types only when the workload justifies them.

4. Bound database concurrency

Database capacity is finite.

Application pools, worker concurrency, background processing, and reporting workloads should not be allowed to generate unlimited simultaneous database operations.

More connections do not automatically mean more throughput.

5. Keep transactions deliberate

Use the shortest transaction that protects the required consistency boundary.

Avoid holding transactions open across unrelated network calls.

Design deadlock and serialization retries before deploying concurrency-sensitive logic.

6. Choose partitioning for the right reason

Partition large datasets when it improves meaningful concerns such as:

Do not partition merely because a table is large.

Table structure and partition layout should follow retention and access boundaries.

7. Separate transactional and analytical needs when necessary

Operational systems and analytical workloads can compete for CPU, I/O, locks, and memory.

Options can include:

Do not send arbitrary expensive analytical scans to the transactional primary simply because SQL makes the query possible.

8. Design schema migrations for production

Large tables make schema changes operational events.

Consider:

For suitable production workloads PostgreSQL provides mechanisms such as concurrent index creation that reduce blocking compared with ordinary index creation, though they have their own restrictions and failure behavior.

Use staged migrations when application and schema versions overlap during deployment.

9. Maintain MVCC health

Monitor autovacuum and table churn.

Long-running transactions and very high write rates can interfere with cleanup and produce increasing table/index bloat or transaction-management pressure.

Maintenance settings may need per-table tuning for extreme workloads.

10. Observe real workload behavior

Monitor:

Use tools such as PostgreSQL cumulative statistics views and pg_stat_statements when available.

11. Define retention deliberately

Not all data belongs forever in the primary transactional database.

Define:

Partitioning can make time-based removal easier when the physical design matches retention boundaries.

12. Protect availability with replicas appropriately

Physical streaming standbys can support failover and read capacity depending on architecture.

But replicas can lag and can reproduce logical mistakes from the primary.

Replication does not replace independent recovery backups.

13. Design for disaster recovery

Set explicit:

Then design backups, WAL retention, replicas, restore automation, and operational procedures to meet those objectives.

Test restores regularly.

14. Prefer evidence over database folklore

Avoid rules such as:

Use execution plans, statistics, measurements, and correctness requirements.

15. Rehearse failure

Production readiness should include testing:

Locking, transaction isolation, resources, and recovery are all related. A scalable design manages them consistently.