Using the Right Isolation Level for Performance Under Load

Higher isolation levels increase consistency but reduce throughput and raise contention. Most OLTP workloads don't need SERIALIZABLE. Here's what to use instead:

• READ COMMITTED is usually enough: It prevents dirty reads without long locks and works well for most OLTP workloads that don't require full snapshot consistency.
• Avoid SERIALIZABLE unless strictly required: SERIALIZABLE adds overhead through locking in MySQL and conflict tracking in Postgres, which increases contention and the risk of deadlocks. It's rarely worth the cost unless absolutely necessary.
• REPEATABLE READ is more nuanced than it sounds: It provides a consistent snapshot and prevents phantoms, but can block writers during long reads, range scans, or when indexes are missing.
• Set isolation levels per transaction when needed: You can tune isolation dynamically to match the workload. For example, use REPEATABLE READ for financial calculations or cross-table comparisons, and READ COMMITTED for routine inserts or background jobs.
• Monitor what's really happening: Use pg_stat_activity in Postgres or performance_schema in MySQL to identify blocking and lock contentions. Defaults are just starting points, real workloads often need tuning.

Isolation levels impact concurrency just as much as consistency. Choosing the right one reduces lock contention without compromising correctness. If you want to avoid manual tuning or per-transaction tweaks, Readyset serves repeat reads off the critical path, easing load on the primary.

🔍 What We're Watching in the Database Ecosystem

Datadog | Evolving our real-time timeseries storage again: Built in Rust for performance at scale

Datadog built a custom Rust-based time-series engine to handle massive ingest and high-cardinality queries at scale. It's what happens when general-purpose databases hit their limits: you build specialized systems to reduce contention. We see the same in OLTP, which is why we built Readyset to offload repeat queries without rewriting your app.

ORM vs. Raw SQL: A Performance Showdown

This post benchmarks EF Core, an ORM, against Dapper, which uses raw SQL, for loading complex relational data. The results show a clear performance gap as query complexity increases. It's a reminder that abstractions have a cost, and that costs grow with scale.

Query Optimization Essentials from TUM's Database Systems Lecture

The University of Munich's (TUM) lecture series on query optimization walks you through how declarative SQL is translated into efficient execution plans inside the database engine. It breaks down the core mechanics of query optimizers and explains why certain queries underperform despite looking straightforward.

In Case You Missed It

Readyset is headed to AWS Summit in Brazil 🇧🇷

This week, Tanmay, Bianchi and Marcelo are heading to São Paulo, where we are excited to be sponsoring AWS Summit São Paulo!

If you're attending or in the area and want to talk database performance, scaling strategies, or learn how QueryPilot automates query identification and caching, we'd love to meet!