Page 5 - SQL

Scale-up and scale-out solve different problems, yet they are often treated as interchangeable options. Vertical scaling means more power for a single instance, horizontal scaling means more instances for the same load. Get this decision wrong and you either build an unnecessarily complex distributed system or hit a hard capacity limit sooner than necessary.

An automatically incrementing primary key sounds like a unified concept, but it is not. MySQL's AUTO_INCREMENT, PostgreSQL's SERIAL and sequences, SQL Server's IDENTITY, and Oracle's sequence objects differ so much in semantics, concurrency, and migration behavior that blindly switching between systems leads to gaps, collisions, or wrong assumptions in application code.

A generated column automatically computes its value from other columns of the same row, without a trigger and without the application having to maintain the value itself. This article covers STORED versus VIRTUAL, practical use cases from derived prices to full-text search indexes, migrations on existing tables, and the most important vendor differences between MySQL and PostgreSQL.

The N+1 problem is the most common performance killer in Symfony projects that use Doctrine ORM. It creeps in silently, often in seemingly harmless template code, and generates hundreds of database queries where a single one would have been enough. With DQL, the QueryBuilder and JOIN FETCH it can be eliminated systematically.

PostGraphile reads a PostgreSQL schema and produces a complete, type-safe GraphQL API without writing a single resolver by hand. Tables, foreign keys, constraints and row-level security policies become fields, relationships and permissions directly, making classic boilerplate between database and API practically disappear.

Graph queries for hierarchies, organizational structures, and networks can be modeled directly in a relational database using adjacency lists, recursive CTEs, and closure tables, without introducing a dedicated graph database for every connected use case. Whoever knows the limits of this approach makes the right choice for their own data volume and query depth.

A stored execution plan gets compiled for a first, unrepresentative parameter value and then reused for every subsequent call, even when the data distribution behind those calls looks completely different. Parameter sniffing is not random instability, it is a logical consequence of how query planners cache execution plans. Understanding the mechanism means recognizing the symptoms immediately and choosing deliberately between statistics maintenance, query hints, and plan guides.

Once a transaction spans multiple independent databases or services, a simple COMMIT no longer guarantees atomicity. Two-phase commit solves this through a coordinator-based prepare-and-commit protocol, but it has structural weaknesses that lead most large, distributed systems today to prefer the saga pattern with compensating actions instead.

Data modeling with entity-relationship diagrams is the step that comes before every single CREATE TABLE statement: entities, attributes and relationships get defined conceptually first, before being translated into actual tables and foreign keys. This post explains entities, attributes, relationship types and cardinality notation through one continuous example, a library system, and shows the complete path from a diagram to SQL DDL.

When DQL hits its limits because a native SQL function like JSON_EXTRACT or MATCH AGAINST is missing, a custom DQL function is often the cleaner solution than falling back entirely to native queries. Doctrine's FunctionNode API makes exactly that possible.