Page 2 - SQL

Partitioning and sharding show up as synonyms in architecture discussions constantly, even though they technically solve fundamentally different problems. Partitioning splits a large table within a single database instance into smaller, physically separate segments, while sharding distributes data across multiple independent database instances. Anyone who does not cleanly separate the two either underestimates the operational overhead of sharding or misses the chance to solve a problem that needs no distributed system at all with the far simpler tool, partitioning.

The choice between NoSQL and a relational database is too often made based on popularity instead of requirements. Whoever instead evaluates access patterns, scaling needs, consistency requirements, and team expertise concretely quickly recognizes in which cases NoSQL actually brings advantages and where a relational database remains the far more robust choice.

The CAP theorem is often quoted as an abstract formula, but above all it is a practical decision tool for the moment a network actually fails. During a partition, a distributed system has to decide whether it keeps giving consistent answers and sacrifices availability for it, or stays available and accepts temporarily inconsistent data in return.

Giving every application and developer the same database account with full rights automatically turns any future security gap into total damage. Granular database user privileges, built from GRANT statements, role hierarchies and the principle of least privilege, limit the damage of a single compromised account to exactly what that account genuinely needs.

Test databases with a handful of hand-picked rows rarely cover the cases that actually occur in production. Generating seed data systematically instead of maintaining it by hand yields reproducible, referentially correct datasets that capture edge cases and realistic distributions alike.

A running total, a cumulative sum that grows with every row, is one of the most common requirements in account statements, revenue reports and inventory management. With SUM() OVER, this calculation can be solved directly in SQL, without the slow detour through a correlated subquery. This article shows the syntax, the reset per group, and the concrete performance difference.

Denormalization means deliberately deviating from a normalized schema to make read queries faster, usually by introducing redundancy or precomputed values. This post shows when it pays off, which techniques exist, what maintenance cost it creates and how to document the decision instead of leaving it to the chance of an incomplete data model.

An anti-join finds every row of a table that has no matching row in a second table, a semi-join finds every row that has at least one matching row, without pulling that table's columns into the result. Both patterns solve a common problem, finding customers with no order or products with at least one review, but only one of the common implementations is truly robust with NULL values and performance.

Full text search is one of the areas where the SQL standard deliberately prescribes very little, and every database has developed its own proprietary solution. This article compares the full text search implementations of PostgreSQL, MySQL, and SQL Server, shows indexing and relevance ranking with concrete examples, and outlines when dedicated search engines like Elasticsearch make more sense than full text search directly in the database.

A classic many-to-many junction table offers two paths: using both foreign keys themselves as a composite primary key, or assigning a dedicated, independent surrogate ID. Both variants are technically valid, but they differ noticeably in uniqueness guarantees, index behavior, ORM compatibility, and the ability to cleanly reference later extra attributes. This article works out the concrete trade-offs using a typical junction table with extra attributes and shows when each approach is genuinely the better one.