Q09 proof · Relational databases: SQL Server and PostgreSQL

9. Relational databases and years of experience

Answer as submitted

“Yes — 20+ years: SQL Server, PostgreSQL, Oracle and MySQL: T-SQL, PL/pgSQL, tuning.”

Technical decision: expand. The submitted line states the engines and the duration but not what the experience actually consists of. This page is the separate, detailed version: it shows the tuning loop step by step — measure, rewrite, index, refresh estimates, choose isolation — with the reads and timings that prove the result.

Expanded answer

Yes — more than 20 years with relational engines, and SQL Server and PostgreSQL as the two I know deepest. That covers T-SQL and PL/pgSQL development (stored procedures, functions, CTEs, window functions, JSONB modelling), schema and index design, execution-plan analysis, partitioning and statistics, ETL/ELT integration with SSIS and Airflow, plus the operational side: connection pooling, isolation choices, deadlock and blocking analysis, index maintenance, backup/restore and capacity planning. Oracle (9i–12c) and MySQL round out the same profile, and every hot query is validated against a real execution plan before release.

Decision	Typical statement	Why it is done this way
Predicate shape	sargable ranges, no functions on columns	The optimiser can seek the index instead of scanning the table.
Plan review	`SET STATISTICS IO, TIME` / `EXPLAIN (ANALYZE)`	Changes are justified by measured reads and time, not by intuition.
Index design	key order + `INCLUDE`	Covers the query shape and removes the key lookup.
Statistics	`UPDATE STATISTICS … FULLSCAN` / `ANALYZE`	Cardinality estimates decide the join strategy and memory grant.
Isolation	read-committed snapshot / snapshot	Readers stop blocking writers without weakening the write path.
Transactions	explicit scope + `rowversion`	One atomic business operation with optimistic concurrency.
Operations	pooling, lock analysis, index maintenance	p95 latency and stability in production, not just correct results.

Live query-tuning workflow proof

The running component below changes its statement/result view through real event binding. Select a step to see the exact statement, the reasoning behind it, and the measurement that proves the effect.

SQL WORKFLOWRUNNING

Choose a tuning step

Select a step to inspect the statement, the reasoning and the measured result.

$ SELECT o.Id, o.CreatedAt, o.Total FROM Orders o WHERE o.CustomerId = 42 AND o.Status = 'Open' ORDER BY o.CreatedAt DESC

Predicates are written sargable: no functions on indexed columns and no implicit conversions, so the optimiser can seek instead of scanning.

Index Seek (IX_Orders_Customer_Status, nonclustered)
logical reads 24  ·  137 rows  ·  3 ms

How the tuning loop is run

Rules I apply. Measure before touching anything; fix the query shape before adding an index; add an index only when the plan shows the seek it buys, and count its write cost; refresh statistics when estimates drift; choose isolation deliberately so readers do not block writers; and keep every business operation inside one explicit transaction with optimistic concurrency. The result is judged on p95 latency and logical reads, not on a single fast run on a warm cache.

Boundary for Q09: this page covers query and schema tuning on relational engines. Cross-engine migration projects, database-level high availability topology and cloud managed-service sizing are separate topics.

Source files

Select a file to inspect the proof with syntax highlighting and line numbers. The first file is the code running the component above; the other two are the production shapes of the same tuning session.

Acceptance criteria

Names the relational engines worked with and the years behind them.
Distinguishes measuring, rewriting, indexing, refreshing statistics and isolation.
Shows an interactive proof of choosing a tuning step and reading its measurement.
Justifies every change with logical reads and timing, not with an opinion.
Defers cross-engine migration, HA topology and managed-service sizing to a later topic.