Page 8 - SQL

A hybrid architecture keeps transactional core processes in a relational database, while a document store takes over for variable, heavily read use cases. Whoever deliberately plans data splitting, synchronization, and consistency boundaries gets the advantages of both worlds without settling for an unclean compromise.

Some database problems aren't a matter of missing indexes or poorly written queries, they're rooted deeper in the schema design itself. These structural antipatterns repeat across projects and industries with striking consistency: a generic attribute table that makes any validation impossible, a column per repetition instead of its own row, a foreign key without an accompanying index. Each of these patterns starts out harmless and pragmatic, but turns into a genuine performance and maintenance problem as data volume grows. This collection organizes the most common cases by symptom, cause, and refactoring path.

ROWS and RANGE both define a window frame, but they do it in fundamentally different ways: ROWS counts physical row positions, RANGE looks at logical value ranges of the sort column. With unique sort values, both return the same result, but with ties in the sort column, ROWS and RANGE can produce different numbers for the same query.

No single tool masters every aspect of a modern deployment stack. Bash as glue code connects PHP scripts, SQL dump workflows, Docker build chains and Git hooks into one coherent automation system, with robust exit code handling, targeted data forwarding and clear error escalation that surfaces problems early.

Conditional aggregation is one of the most common needs in reporting queries: how many orders are cancelled, how many completed, how many still open, all in a single row per group. The classic path there runs through COUNT combined with a CASE WHEN expression that returns either a value or null depending on the condition. The SQL standard has offered a noticeably clearer alternative for a while now: the FILTER clause, attached directly to the aggregate function. This article covers how FILTER works, why it beats CASE WHEN for readability in many cases, how it combines with window functions for several parallel metrics, and where database availability draws the line.

Not every data integration needs Airflow, Dagster, or a distributed orchestration system. An ETL pipeline in Bash with clearly separated Extract, Transform, and Load stages, checkpointing, and idempotent runs handles a large share of daily data flows between systems reliably and without additional infrastructure.

UNION and UNION ALL both combine the result sets of multiple SELECT statements into a single output, but only UNION deduplicates every single row with an implicit sort or hash step. Anyone who does not know this difference pays unnecessary compute time on every query for a deduplication that in many cases is not needed at all, because the combined result sets are already disjoint.

A data warehouse structures data for fast analysis, not for transactional write operations. The star schema with fact tables and dimension tables is the most proven structure for this, because it makes aggregation queries over billions of rows easy to read and performant, without the complexity of a fully normalized schema.

Database locks usually protect specific data rows or tables from concurrent, inconsistent access. Advisory locks serve a different purpose: they use the same robust locking infrastructure of the database to coordinate arbitrary application logic, without the lock being tied to any specific row or table. A typical use case is distributed locking for cron jobs or background processes that could run on multiple servers but must never execute the same operation at the same time.

Date functions are one of the areas where databases differ the most, even though the underlying task seems simple. This article compares date arithmetic, formatting, time zone handling, and extraction of individual date parts in PostgreSQL, MySQL, SQL Server, and Oracle using concrete, runnable examples.