SQL: Guides and Practical Know-how | Page 5 | mironsoft Blog
SQL
-
Dec 27, 2025
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.
-
Dec 22, 2025
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.
-
Dec 22, 2025
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.
-
Dec 20, 2025
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.
-
Dec 19, 2025
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.
-
Dec 19, 2025
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.
-
Dec 18, 2025
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.
-
Dec 15, 2025
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.
-
Dec 11, 2025
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.
-
Dec 5, 2025
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.