Database
11 posts in this category
MySQL Isolation Levels: Common Race Conditions and How to Fix Them
MySQL's default REPEATABLE READ doesn't prevent every race condition. Dirty reads, lost updates, phantom reads, write skew — which isolation level prevents which, when SELECT FOR UPDATE is the real fix, and when optimistic locking is better.
Stop Using Database Constraints as Input Validation: Use DTOs Instead
Many backends rely on NOT NULL, VARCHAR(n), and UNIQUE constraints to catch bad input. This is the wrong layer for validation — databases aren't built for it. Where validation actually belongs: the DTO layer and service layer, before data ever reaches the DB.
MySQL Deadlock: How to Read InnoDB Logs and Reduce Occurrence
MySQL auto-resolves deadlocks but randomly rolls back a transaction, causing errors at the app layer. How to read SHOW ENGINE INNODB STATUS to find exactly which row was locked, common causes, and 5 ways to reduce deadlock probability.
N+1 Query in Prisma, TypeORM, and Sequelize: How to Detect and Fix It
N+1 query causes no errors but makes APIs slow as data grows — ORM fetches a list with 1 query then fires N more for each item. How to catch it with SQL logging, count queries per request, and fix with eager loading, batching, or DataLoader.
Can SQL handle large datasets? Indexes and query design in MySQL
Why SQL still works well at scale when schema, indexes, and query design are correct, from filtering before joins to MySQL patterns for millions of customers.
MySQL ORDER BY + LIMIT Slow Despite Index: Filesort, Composite Index, and Cursor Pagination
ORDER BY combined with LIMIT can trigger a filesort over millions of rows even with indexes in place. How MySQL picks its execution plan, how to read EXPLAIN, and 4 practical optimization patterns.
Cloudflare D1 Database: when should you use it?
Cloudflare D1 is serverless managed SQLite for Workers and Pages, useful for small side projects but limited if you later need MySQL/PostgreSQL.
Caching strategies to reduce database load
Practical caching strategies for reducing database and API load: cache-aside, write-through, write-behind, TTLs, and invalidation.
The role of indexes and the query patterns that make MySQL slow
Why indexes matter in MySQL, and common slow-query patterns such as missing indexes, selecting too much data, or filtering in application code.
Design a consistent database with fewer data bugs
Practical database design rules for consistent data: naming, primary keys, foreign keys, unique constraints, not null, and enough normalization.
Optimizing a MySQL query on 300k+ records
Case study: bringing a MySQL query from 12 seconds down to 200ms using EXPLAIN, composite indexes, and a query rewrite.