When working with millions of rows in PostgreSQL, writing efficient SQL queries becomes critical. In this performance benchmark, we compare two common approaches for fetching related data—correlated subqueries vs. LEFT JOINs. While both produce identical results, the difference in speed is significant: the LEFT JOIN completes in just 4.5 seconds, while the correlated subquery takes a staggering 14.7 seconds. This post walks through the full setup, explains the reasons behind the performance gap, and helps you understand when to use each approach. Whether you’re optimizing an existing database or designing a new schema, these insights can help you write faster SQL from day one.

PostgreSQL updates aren’t as simple as they seem. Even if you’re not modifying indexed columns, every update can still trigger expensive index rewrites—unless you’re using HOT (Heap-Only Tuple) updates. In this post, we explore how PostgreSQL’s HOT optimization, combined with the right fillfactor setting, can dramatically improve update performance by reducing index overhead. Backed by benchmark data, we explain when and how to tune your tables for HOT updates, and when it’s better to leave things as they are.

Storing dynamic user-defined data in PostgreSQL often comes down to two choices: JSONB or the Entity-Attribute-Value (EAV) model. Both offer schema flexibility, but they differ greatly in performance and maintainability. In this post, we benchmark EAV vs. JSONB at scale—10 million rows, seven properties per entity—and compare their impact on query complexity, write performance, and storage size. The results show that while EAV offers fine-grained control and indexing advantages in certain cases, JSONB provides a simpler, faster, and more efficient solution for most dynamic data needs.