99% of Developers Don't Get PostgreSQL transcript

99% of developers don't get PostgreSQL. Many can't even begin to explain the advantages of PostgreSQL over MySQL. Knowing the difference between SQL and NoSQL is table stakes and it simply does not cut it. Understanding ACID and CAP theorem is the barebones expectations in 2025 for every competent entry-level engineer. But the engineers who get ahead in this market, they understand the low-level architecture that makes PostgresQL special as a database management system.

If you don't know these concepts, then you should consider watching this video where I'm going to break down how PostgrSQL works, its architecture, and some of its nifty details around multi-verion concurrency control and write ahead logging. This is the knowledge that will make you stand out as an engineer. Let's get started. Postgrql is a relational database system. It stores structured data and lets you query and manipulate it using SQL.

But it's not just a data store. It's a transactional, concurrent, extensible data engine built to guarantee correctness, consistency, and durability at scale. At its heart, PostgresQL implements the acid model. Atomicity, every transaction is all or nothing. Consistency. Data always moves from one valid state to another. Isolation. Concurrent transactions do not interfere. And durability. Once a transaction commits, it will not be lost even after crashes.

PostgreSQL's entire architecture from memory management to logging is designed around enforcing these four principles efficiently. But actually PostgreSQL is an object relational database. This means it combines traditional relational database features like tables, rows, foreign keys with object-oriented concepts like custom data types, functions, and inheritance. You can define complex types, store arrays, JSON or geometric objects and even attach methods or operators to them.

This flexibility allows PostgreSQL to handle both standard relational workloads and more advanced semistructured data patterns. Now let's look into the architecture of PostgresQL. Postrql follows a client server model. The server or postmaster manages data and clients such as applications, APIs or the PSQL CLI connect via TCP. When you start PostgreSQL, it spawns several background processes. Backend processes are one per active connection which execute the SQL statements.

Background workers handle caching, vacuuming, and checkpointing, which we'll get to later. And the writer and write ahead logging processes manage persistence and recovery. We'll touch on this as well. Underneath all data is stored on disk in a directory called the data cluster, which contains databases, schemas, tables, indexes, and write ahead logging logs. Postrql separates logic such as how queries are executed from storage, which is how data lives on disk.

It's a design that gives it both flexibility and fault tolerance. Now onto the data storage model. Every table in Postgrql is stored as one or more heap files on disk. These heap files are collections of fixed-sized, typically 8 kilobyte pages on disk. Within a heap file, data is organized into pages. Each page can hold multiple rows of data. Pages contain a header with metadata, transaction pointers, and an array of item pointers, which point to the actual row data within the page.

Each page contains tpples, which are PostgreSQL's internal representation of rows. A tpple isn't just the data itself. It also stores metadata such as which transaction created it or xmin, whether it's been deleted or xmax, and visibility information for multi-verion concurrency control, which we'll touch on very soon. Because PostgreSQL uses heap storage, tpples are appended in no fixed order. One of the most interesting parts of Postgrql is this concept called toast.

It stands for the oversized attribute storage technique. It's an automatic mechanism for handling large data values within a row that exceed the standard 8 kilobyte page size. Postrql requires entire rows to fit within a single page, which poses a challenge when dealing with large data types like text bite A or JSON B. When tpples grow too large, like big JSON blobs or long text, PostgresQL automatically stores them out of line using toast.

Toast addresses this problem by employing two primary strategies. The first is using compression. When a large field value exceeding a certain threshold, typically around 2 kilobytes, is encountered. Postgrql attempts to compress it using an internal algorithm. For example, PGLZ. If compression reduces the value sufficiently to fit within the main table row, it remains there in a compressed format. PGLZ stands for Postgrql Lemple Ziv.

It's the default lightweight internal compression algorithm used by Postgrql for its toast storage system. At a low level, it works by identifying and replacing repeated data sequences with shorter references to save space, balancing compression ratio with low CPU overhead. PGLZ is very similar to Lemple Ziv Welsh, which is used for GIF compression. They're both lossless algorithms that use dynamic dictionaries to find and replace repeated patterns in data.

However, since PostgreSQL 14 LZ4 is available as a faster alternative compression algorithm for toast, the second option is outofline storage. If compression is not enough or if the data type is configured to prioritize outofline storage, Postrql moves the large field value to a separate automatically created toast table. The original row in the main table then stores a small pointer or reference to the location of the data in the toast table.

This guarantees the main table rows remain compact improving performance for operations that don't need access to the large values. Now let's talk about one of the greatest concepts in PostgrSQL transactions and MVCC. PostgresQL achieves isolation and concurrency through multi- version concurrency control or MVCC which is a concurrency control mechanism. The goal of MVCC is to allow multiple queries to read and write to the database simultaneously without interfering with each other when possible.

In other words, multiple transactions can access and modify data concurrently without blocking each other. Unlike traditional lockbased systems where readers might block writers and vice versa, MVCC achieves this by maintaining multiple versions of data rows. The basic idea of MVCC is that the database management system never overwrites existing rows. Instead, for each logical row, the database management system maintains multiple physical versions.

When the application executes a query, the DBMS determines which version to retrieve to satisfy the request according to some version ordering. For example, creation timestamp. But let's take a step back from MVCC and talk about vanilla PostgreSQL. PostgresQL requires significant manual effort to configure, secure, and scale while also forcing developers to build crucial back-end services like APIs, authentication, and real-time subscriptions entirely from scratch.

That's why this segment is sponsored by Superbase, the open- source backend built on PostgresQL. If you follow this channel, you know I'm very passionate about tools that empower developers to build powerful applications faster. And Superbase fits that description perfectly. At their Superbase Select 2025 conference, the co-founders talked about building with data and two themes really resonated with the work I do and the topics we cover here, AI and scale.

Here are the key takeaways for developers like us. Shipping AI features fast. They demonstrated how Superbase helps you quickly integrate AI into your apps. For instance, vector search for embeddings and integrating ML models right alongside your database logic. It lowers the barrier for building the next generation of intelligent applications. Second, production grade Postgress without the headache. They showcased real stories from teams handling massive workloads.

More importantly, they announced new tooling to make scaling safer, including deep observability into indexes, vacuums, and performance right from their dashboard. Superbase has also continued to make quality of life improvements. For instance, their tooling for rowle security, a flexible API, and a tight feedback loop between their SDKs and their underlying SQL. When you write something simple and declarative in their client library like this, Superbase translates it under the hood into optimized SQL, enforces security rules at the database layer, and can even stream realtime updates directly to your front end.

It's an incredibly efficient way to work. Superbase is reinforcing its mission to make powerful back-end tools accessible to developers like us. You get the full authority and visibility of Postgress just without all the manual setup and configuration. I definitely recommend that you give them a look for your next project. Now, let's get back to the video. The benefit of this approach is that multiple queries can read older versions of rows without getting blocked by another query updating it. queries observe a snapshot of the database as it existed when the DBMS started that query's transaction effectively snapshot isolation.

So let's break this down. When a transaction modifies a row, PostgresQL does not overwrite the existing data. It creates a new version of that row marking the old version as obsolete. Each version is associated with the transaction ID that created it. That's the concept of multiple versions. Another concept is consistent snapshots. Each transaction sees a consistent snapshot of the database as it existed at the start of that transaction.

This means a transaction will only see data committed before its own start time regardless of subsequent updates by other concurrent transactions. Now there is the concept of non-blocking reads. Readers never block writers and writers never block readers. A reading transaction will simply access the appropriate version of the data based on its snapshot. While a writing transaction creates a new version without affecting ongoing reads.

Now onto visibility rules. Postrql uses internal mechanisms including transaction ids xmin xmaxx on each row to determine which version of a row is visible to a given transaction based on its isolation level and the rows creation and expiration details. When you read data, you only see tpples whose creation XID is less than yours, meaning committed before your transaction started, and whose deletion XID is greater than yours, which means not yet deleted.

Over time, as updates create new versions and old versions become obsolete, the database accumulates dead tpples or obsolete row versions. The vacuum process known as autovacuum is essential for reclaiming this disk space and maintaining database efficiency. By removing these dead tpples, it removes outdated versions no longer visible to any active transaction. This model keeps concurrency high and avoids read locks, but makes maintenance such as vacuum and freeze essential.

Now, we're going to talk about another important concept, write ahead logging and durability. Durability in Postgrql is guaranteed by the write ahead log or WA. Before any change is written to the data files, it's first written to the WA, a sequential log on disk. If PostgresQL crashes before flushing changes to data pages, it can recover by replaying the write ahead log entries. The write ahead log also underpins replication.

Secondary servers can replay the write ahead log stream in near real time, creating consistent read replicas or failover standbys. This log first approach ensures both safety and performance because sequential writes to write ahead log are much faster than random writes to data pages. A checkpoint is a critical process that ensures data durability and enables efficient crash recovery. It functions as a synchronization point between the in-memory state of the database and its persistent storage on disk.

Checkpoints periodically flush dirty buffers from memory to disk so that recovery doesn't have to replay an entire write ahead log history. The balance between checkpoint frequency and write ahead log size determines write performance and recovery speed. So let's touch on flushing dirty pages. During normal operation, changes to data are initially made to in-memory buffers. These modified pages known as dirty pages are eventually written to disk.

What a checkpoint does is it explicitly triggers the flushing of all dirty pages from memory to the physical data files on disk, guaranteeing that these changes are permanently stored. After flushing the dirty pages, a special checkpoint record is written to the right- ahead log. This record contains important information including the log sequence number or LSN marking the specific point in the write ahead log up to which all data changes have been flushed to disk.

The primary purpose of checkpoints is to facilitate fast and reliable crash recovery. In the event of an unexpected database shutdown or crash, PostgreSQL uses the latest checkpoint record to determine the starting point for recovery. instead of replaying the entire write- ahead log from the very beginning, it only needs to replay the write ahead log entries that occurred after the last completed checkpoint based on the log sequence number.

If you want to learn how to build Docker, Reddus, and compilers from scratch, I highly recommend you check out Code Crafters using the link in the description to start your journey on becoming a 10X developer. And if you want to start using a production grade Postgress via an open source backend, I highly recommend that you check out Superbase down below. As always, thank you very much for watching and happy coding.