SQL

This analysis challenges the misconception that frequent database joins are expensive, instead showing they are efficient and scalable. Using DuckDB and PostgreSQL benchmarks, it demonstrates that 'One Big Table' (OBT) models often consume more CPU due to data reassembly and projection costs. Dimensional modeling, as advocated by Ralph Kimball, remains superior for analytical performance.

This article demystifies the fifth normal form (5NF) by advocating for a logical design approach rather than traditional, confusing decomposition methods. By starting with business requirements and clear logical models—specifically the AB-BC-AC triangle and the ABC+D star patterns—developers can create fully normalized database schemas without manual splitting or artificial constraints.

pg_textsearch is a high-performance PostgreSQL extension providing BM25 ranking for text search. It features simple syntax, Block-Max WAND optimization for fast top-k queries, and parallel index builds. Designed for production, it supports PostgreSQL 17-18, partitioning, and configurable ranking parameters, making it a scalable alternative for textual information retrieval within the database.

This article demonstrates how to render and play a chess game using only SQL. By utilizing a table to store piece positions, conditional aggregation to pivot data into an 8x8 grid, and basic CRUD operations to execute turns, the author shows that SQL is capable of complex game visualization and logic without frontend frameworks or JavaScript.

Torsten Grust provides comprehensive course materials on the internal architecture of DuckDB. The 'DiDi' course, designed for undergraduate students at the University of Tübingen, covers 15 weeks of topics including memory management, vectorized query execution, indexing with ART, and query optimization, providing a deep dive into the kernel of the DuckDB relational database system.

The author successfully imported the entire 20-year history of the Linux kernel into pgit, storing 1.4 million commits in a PostgreSQL database using delta compression. This setup enables rapid, complex SQL querying of the entire history, revealing deep technical insights, development trends, and contributor patterns that would be labor-intensive to produce using standard Git tools.

Modern SQLite provides powerful features like native JSON support, FTS5 for full-text search, window functions for analytics, and STRICT tables for schema enforcement. With generated columns, Write-Ahead Logging (WAL) for concurrency, and CTEs, SQLite has evolved into a robust, high-performance database suitable for complex applications without sacrificing its characteristic simplicity and reliability.

CTEs in PostgreSQL have evolved from 'optimization fences' into an efficient tool. Since PostgreSQL 12, the planner automatically inlines most CTEs unless they are recursive, have side effects, or are referenced multiple times. While materialization can be useful for performance or atomicity, it often hides statistics from the query planner, occasionally leading to inefficient execution plans.

This comparison table details the feature differences between two database management tools for PostgreSQL. It covers key functional areas including connection management, schema browsing, table inspection, data management, SQL editing, and file exploration, highlighting specific capabilities unique to each application's implementation.

SQLite 3.53.0 introduces the Query Result Formatter (QRF) library to enhance CLI output, adds support for modifying constraints in ALTER TABLE, improves the query planner, and introduces new JSON functions. It also provides new C APIs and optimizations while addressing critical reliability fixes like the WAL-reset corruption bug.