Why "Just Use Postgres" Isn't Always Enough

Episode summary: In this episode of My Weird Prompts, we dive into the "Just Use Postgres" movement and ask a critical question: is the era of specialized databases finally over? While Postgres has become a Swiss Army knife for modern engineering, physical constraints and hardware architecture eventually force a divide between transactional and analytical workloads. We break down the fundamental differences between row-based and columnar storage, explaining why your "Ferrari" database might melt if you try to use it like a "dump truck" for big data. From the power of vectorized execution and SIMD instructions to the complexities of real-time data pipelines using Change Data Capture (CDC) and Apache Kafka, we explore how giants like Netflix manage massive data scales. Whether you are a minimalist developer or a data architect, this deep dive into the internal geometry of databases will change how you think about your tech stack. Show Notes The modern software engineering landscape is currently obsessed with simplification. This trend is best captured by the "Just Use Postgres" movement, which suggests that because PostgreSQL has added support for vectors, time-series data, and search, the need for specialized databases has vanished. However, while simplicity is a virtue, the laws of physics and hardware architecture eventually demand a more nuanced approach. ### The Geometry of Data The divide between Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) is rooted in how data is physically stored on a disk. Traditional databases like Postgres use row-based storage. When a computer saves a record, it places every attribute of that record—name, age, address—side-by-side in a single block of memory. This is perfect for transactions; if a user logs in, the database can grab their entire profile in a single read. However, this model fails during large-scale analytics. If an analyst wants to find the average age of ten million users, a row-based database must still read the entire row for every user. It scans names and addresses it doesn't need just to find the age. This results in a massive waste of disk I/O and memory bandwidth. ### The Columnar Revolution To solve this, specialized analytical databases like Snowflake, ClickHouse, and DuckDB use columnar storage. Instead of storing data row-by-row, they store it column-by-column. All ages are stored together, and all names are stored together. When calculating an average age, the system only touches the "age" column, ignoring everything else. This shift in geometry unlocks two massive advantages: compression and vectorized execution. Because a column contains homogeneous data (like a long list of integers), computers can compress it at ratios of 10:1 or even 50:1. Furthermore, modern CPUs can use SIMD (Single Instruction, Multiple Data) instructions to process these columns in batches. Rather than counting records one-by-one, the CPU can perform calculations on entire chunks of data simultaneously, providing orders-of-magnitude faster results. ### Moving Data at Scale The challenge for modern architects is getting data from the transactional "source of truth" to the analytical "engine" without crashing the system. This is where Change Data Capture (CDC) and streaming platforms like Apache Kafka become essential. Instead of running heavy queries against a production database, CDC tools monitor the database's write-ahead log (WAL). Every change is recorded and turned into a message that is sent to a buffer like Kafka. From there, the data can be fanned out to various specialized destinations—Elasticsearch for search, S3 for long-term storage, or Snowflake for deep analytics. Ultimately, while "Just Use Postgres" is a great starting point for many startups, the physical constraints of hardware ensure that specialized tools will always have a place in high-scale environments. The goal isn't to make one tool do everything, but to build a pipeline that moves data to the tool best suited for the job. Listen online: https://myweirdprompts.com/episode/postgres-vs-specialized-databases