Database
42 articles
Practical Introduction to Databases: Efficient SQL through the Relational Model
A review of a practical introduction to databases taught from theory via the relational model. Learning databases from relational theory helps organize how you approach RDBs, grounding the principles behind efficient SQL in set theory and logic.
SQL Anti-Patterns
A review of SQL Antipatterns. A go-to classic that introduces the antipatterns you tend to fall into with databases and SQL, with clear explanations. Knowing common mistakes and how to avoid them helps with design and implementation decisions.
Mastering SQL: A Comprehensive Guide for Advanced Beginners, 2nd Edition
A review of Mastering SQL: A Comprehensive Guide for Advanced Beginners, 2nd Edition. A book for learning the varied expressions of SQL beyond the basics. Full of oh-you-can-write-it-this-way moments that expand your repertoire, it suits those who want to step beyond the beginner level.
Practical Introduction to SQL: Writing Fast and Understandable Queries
A review of Practical Introduction to SQL. A book that organizes what to keep in mind when writing SQL. Grounded in execution plans, it teaches how to write queries that are both fast and easy to read.
What is a Database Index? How It Works and When You Need It
Understand how database indexes work internally—B-tree and hash structures—when to add or avoid indexes, and how to measure index effectiveness using EXPLAIN.
Database ID Design: UUID vs Auto Increment vs ULID — Which to Choose?
Compare auto-increment integers, UUIDs, and ULIDs as database IDs. Learn the trade-offs in performance, sortability, and distributed system compatibility.
NULL in Database Design: Common Pitfalls and Best Practices
Learn how NULL works in relational databases, the three-valued logic problem, common query mistakes with NULL, and schema design strategies to avoid NULL-related bugs.
Notes on Spanner Insights
Explore Cloud Spanner architecture, replication strategies, indexing, and performance optimization for distributed databases.
Exploring Aurora Serverless
Explore Aurora Serverless v2 capabilities including multi-AZ support, auto-scaling, cost optimization, and deployment considerations for databases.
MySQL Transaction Isolation Levels: Preventing Dirty Reads, Phantom Reads, and More
Learn MySQL's four transaction isolation levels, the anomalies each prevents (dirty read, non-repeatable read, phantom read), and how to choose the right level.
Transaction Overview
Understand database transaction fundamentals including ACID properties, concurrency control, and isolation for data integrity.
Sharding vs Partitioning: Key Differences and When to Use Each
Understand the difference between database sharding and partitioning. Learn horizontal and vertical strategies, sharding trade-offs, and when each approach makes sense.
Full-Text Search with MySQL
Implement MySQL full-text search with FULLTEXT INDEX, ngram parsing, and MATCH...AGAINST query patterns.
Exploring NewSQL Resources
A roundup of the various resources I read while researching NewSQL. I originally set out to compare several NewSQL databases, but since I needed to understand the technologies used under the hood, the collection also includes many articles on the related underlying technologies.
Encountered 'Permission denied' Error When Starting MySQL Container on Ubuntu 20.04.2 LTS
Fix MySQL container permission denied errors on Ubuntu 20.04 by properly configuring docker-compose user UID/GID mappings.
Cannot Start MySQL 8.0.17 Container on M1 Mac
Fix the MySQL container startup failure on Apple M1 Mac (failed to create new OS thread errno=22). Use MySQL 8.0.26+ with --platform=linux/amd64 in the Dockerfile for ARM architecture support.
Tools for Automatically Generating DB Documents (ER Diagrams, etc.) - schemaspy, tbls
A hands-on look at open-source tools for automatically generating database documentation such as ER diagrams. It tries out SchemaSpy and tbls, comparing their setup, generated output, and usability to help you choose between them.
SQL for Generating Test Data in MySQL
A note on how to generate test data using only MySQL. While generating test data with a script offers more flexibility, when you just want tens of thousands of records for a performance test, plain SQL can be enough. This article shows how to write such SQL.
Approaches to Table Design for Many-to-One Relationships with Multiple Tables
Learn how to handle a table related to multiple tables in many-to-one relationships. Compares polymorphic associations (SQL anti-pattern, no FK constraints) with cross/pivot tables.
ER Diagram Relationship Types: Dependent, Independent, and Many-to-Many
Understand the three types of ER diagram relationships: dependent (parent-child, solid line), independent (dotted line), and many-to-many. Includes SQL table design examples for each.