Tag: SQL

1. Using JSON Columns in Relational Databases

   Nov 5, 2025

   JSON columns give a relational database exactly the modeling freedom that otherwise serves as an argument for a NoSQL switch, without giving up transactions, joins, and referential integrity. Whoever correctly indexes, queries, and validates JSONB in PostgreSQL or JSON in MySQL covers many semi-structured use cases within the existing database.

   Read more

2. SQL Injection Vectors Beyond the Basics: Second-Order, Blind, Out-of-Band

   Nov 4, 2025

   Prepared statements stop classic SQL injection at the input field, but far from every vector runs through a single form field. Second-order injection, blind injection without a visible error message, out-of-band exfiltration via DNS, and injection in identifier contexts such as ORDER BY bypass exactly the safeguards teams usually rely on.

   Read more

3. Approximate Aggregation for Large Datasets: HyperLogLog and More

   Nov 3, 2025

   Approximate aggregation replaces exact calculations like COUNT DISTINCT or percentiles with probabilistic algorithms that deliver a result on billions of rows in a fraction of a second, with a controlled error usually under one percent. This article explains how HyperLogLog and t-digest work, when approximate aggregation pays off, and when exact calculation remains the right choice.

   Read more

4. Index Strategies Thought Through, Framework-Agnostic

   Oct 30, 2025

   A good index strategy does not depend on which ORM or framework sits on top of the database, but on fundamental principles such as selectivity, column order and the deliberate use of covering indexes. Once you understand these principles, you make better indexing decisions regardless of whether the application is written in PHP, Java, Python or plain SQL.

   Read more

5. Time Series Aggregation in SQL: Bucketing Data and Filling Gaps

   Oct 30, 2025

   A plain GROUP BY over a date column is rarely enough for time series aggregation, because days without events simply do not show up in the result. This article explains how to build time buckets with DATE_TRUNC, how calendar tables and generate_series reliably fill missing periods, and how to compute comparison periods like previous year or previous week directly in SQL.

   Read more

6. Diagnosing Lock Contention: Finding Blocking Sessions

   Oct 29, 2025

   When requests suddenly hang while CPU usage stays low, lock contention is often the reason. With performance_schema in MySQL and pg_locks in PostgreSQL, lock contention can be diagnosed systematically, instead of guessing anew with every incident which session is blocking which other one.

   Read more

7. Nested Aggregation: Computing Aggregates of Aggregates

   Oct 28, 2025

   Nested aggregation computes a metric from values that are already aggregated, such as average daily revenue from daily sums, instead of directly from individual rows. Since SQL forbids nesting aggregate functions like AVG(SUM(column)) directly at the same SELECT level, this article shows how subqueries and CTEs implement nested aggregation cleanly and correctly.

   Read more

8. Understanding Eventual Consistency in Practice

   Oct 27, 2025

   Eventual consistency is not a compromise for poorly built systems but a deliberate consequence of the CAP theorem in every distributed system, including read replicas in relational databases. Whoever concretely understands consistency models, conflict resolution, and replication lag makes better decisions about where a short delay is acceptable and where it is not.

   Read more

9. Weighted Aggregates: Weighted Average and Weighted Sum in SQL

   Oct 13, 2025

   Weighted aggregates account for the fact that not every value in a report should count equally, for example because one order contains more items than another, or an exam counts more than a quiz. This article shows why a plain AVG returns misleading averages in such cases, and how weighted average and weighted sum, using SUM(value times weight) divided by SUM(weight), compute the correct result.

   Read more

10. Implementing Performant Bulk Operations: Batching, Locking, Constraints

    Oct 6, 2025

    Anyone who inserts, updates or deletes large data volumes one row at a time produces lock escalation, bloated transaction logs and runtimes measured in hours. With well designed batching, temporarily disabled indexes and correctly sized chunks, the same bulk operations become a task of minutes, without blocking the database's live operation.

    Read more