Agents interacting with traditional OLTP databases often encounter significant bottlenecks at the storage layer. The processes of deploying new instances, creating copies, restoring data, and managing replicas require the movement of substantial data volumes, leading to time-consuming and costly operations. In stark contrast, object storage solutions such as Amazon S3 offer an economical, efficient, and nearly invisible operational experience, establishing a scalable storage layer that complements agent memory.
This brings us to a pivotal inquiry: can object storage effectively underpin a transactional database, thereby facilitating smoother interactions for agents? The answer hinges not solely on the speed of the object store but also on the positioning of the source of truth.
Two OLTP Models
The conventional perspective on OLTP systems is data-centric, where information is structured into tables with rows and columns, each representing distinct entities. In this model, storage serves as the repository for the current state, and the database’s primary function is to store and retrieve that state. However, an alternative model exists: the transaction-centric approach. Here, the database functions as a journal of transactions, with each entry documenting an operation, and storage becomes a timeline of these operations rather than a mere snapshot of the present state. The current state can be derived from this timeline.
Historically, the data-centric model dominated practical applications, as operational teams primarily required reads and writes against the current data. However, recent shifts have seen a growing demand for operations that focus on transaction history, including:
- Requesting an isolated copy of production for analysis.
- Reverting to a previous state prior to recent changes.
- Examining the state of a table before a migration.
- Executing multiple operations simultaneously and selectively deleting some.
These queries emphasize the importance of the timeline. A database that only maintains the present state struggles to provide efficient copies and backups, which can be both slow and costly. Fortunately, Postgres already retains this timeline through its write-ahead log (WAL).
The Writing in the WAL
Postgres’ WAL meticulously records every modification before it is committed to the data files. Initially designed for recovery purposes—ensuring that if the server fails between logging and writing to data files, a WAL replay can bridge the gap—the WAL’s contents extend far beyond mere recovery. For instance, when an insert occurs, Postgres appends the change to the WAL before it reaches the users table on disk. Although the log is binary, tools like pg_waldump can render it into a human-readable format, revealing a detailed history of operations.
Each log entry is accompanied by a log sequence number (LSN), a monotonically increasing identifier that indicates the precise point in the timeline where changes occurred. This log serves not only as a recovery mechanism but also as a comprehensive, ordered account of every page the database has modified, making the timeline inherently addressable. Thus, creating a “database as of a point in time” becomes a straightforward task, requiring merely a storage layer capable of retaining the log and responding to queries against it.
The Log Becomes the Source of Truth
In a traditional Postgres setup, the WAL functions as a means to safeguard the data files, which are considered the database’s core. The log is typically trimmed once its records are safely applied, and storage is confined to the disk attached to the Postgres machine. However, envisioning a paradigm shift where the log itself becomes the database, with data files serving as a derived, cached representation, opens new possibilities. This approach allows for the retention of the complete timeline, eliminating the need for data movement to create copies or rewind the database. Consequently, a database “copy” transforms into a mere pointer rather than a separate set of files, rendering deployments, restores, and replicas cost-effective and manageable.
In Lakebase Postgres, we have implemented this innovative architecture by dividing the system into two distinct layers:
The Compute Layer
The compute layer operates standard Postgres, handling SQL parsing, query planning and execution, enforcing MVCC, and managing locks and indexes. Importantly, no alterations are made to the query engine; rather, the compute node’s role is redefined to focus on executing tasks rather than preserving data. It utilizes RAM for shared buffers and local NVMe as a page cache, allowing it to start, stop, scale, or terminate without jeopardizing durability.
The Storage Layer
The storage layer is tasked with ensuring correctness, durability, and historical integrity. It persists beyond any individual compute node and comprises three components, each with specific responsibilities:
- Safekeepers replicate the WAL. When the compute node generates WAL records, these are streamed to multiple safekeepers. A transaction is deemed committed once a quorum acknowledges the record through a Paxos-based protocol, ensuring durability through replication and consensus rather than relying on a single machine’s
fsync. - The pageserver transforms WAL into pages. It integrates base pages with committed WAL records to materialize the necessary version of a page for a given query, persisting these materialized versions into object storage asynchronously.
- Object storage retains long-term, immutable history. Materialized page versions and historical states are stored as an append-only record, eschewing the need for a mutable filesystem.
The Write Path
The commit process within this system unfolds as follows:
- Postgres applies changes in memory, updating buffers, modifying indexes, and generating WAL records as per usual.
- Instead of flushing the WAL to a local filesystem, the compute node streams it over the network to the safekeepers.
- A transaction is committed once a quorum of safekeepers acknowledges the record, marking the point at which the client receives confirmation of success.
- Page materialization occurs subsequently within the storage layer, off the transaction’s critical path, ensuring that a commit never waits for pages to be written or uploaded.
While one might raise concerns regarding the additional network hop introduced in step two, it is essential to note that any Postgres deployment prioritizing durability typically employs synchronous replication, which inherently involves a network hop. Externalizing the WAL merely substitutes one network round trip for another without introducing additional latency.
The Read Path
Every read request from a compute node specifies a page identifier and an LSN, prompting the storage layer to return the page as it existed at that LSN. This GetPage@LSN operation is central to the architecture, with a preference order for serving requests:
- First, the system checks RAM for Postgres shared buffers, consistent with standard Postgres operations.
- Next, it examines local NVMe storage, which remains fast and local. If the page is not found in memory, the compute node queries its local disk cache.
- Only upon a local miss does the request traverse the network to the pageserver. The pageserver then verifies whether it has the requested page version materialized. If not, it retrieves the most recent image of the page at or before the requested LSN, collects the relevant WAL records, replays them, and returns the reconstructed page.
The retrieved page is subsequently cached in RAM and on NVMe, ensuring that future reads can be served locally. A primary node requests the latest version of every page, allowing it to function like any Postgres reading from a warm cache. However, the protocol does not mandate “latest” reads; users can request a page at an LSN from hours prior and receive that specific version.
Non-Overwriting Storage
In essence, the pageserver never updates a file in place. Instead, files are created, merged, and deleted, but never modified. This approach aligns perfectly with object storage, which does not support random updates, thereby making the retention of historical data cost-effective. Data is organized into two types of layer files:
- An image layer, which captures a snapshot of every key in a key range at a specific LSN.
- A delta layer, which records all changes within a key and LSN range, excluding keys that remain unmodified. Incoming WAL is written out as delta layers.
Image layers are generated in the background for two primary reasons: they shorten the replay chain that a read must traverse and facilitate the collection of old deltas. Without image layers, reconstructing a page could necessitate traversing back through an extensive history.
Thus, GetPage@LSN evolves into a search operation: starting at the requested key and LSN, the system navigates through the layers, gathering WAL records for that page and halting at the first image encountered. To maintain efficiency, delta and image layers undergo background compaction, while layers exceeding the retention window are subject to garbage collection.
How to Find the Right Layer Quickly
The aforementioned search process, while seemingly straightforward, presents complexities that warrant careful consideration, as it ultimately determines the viability of the entire design. A read operation specifies a key and an LSN, necessitating the storage system to locate the nearest layer that encompasses that key at or before the specified LSN. This geometric problem lacks an obvious solution across millions of layers, as a linear scan would be prohibitively slow, and conventional spatial structures do not accommodate the requirement for “the first layer below this point.”
To address this challenge, we adopted a two-step approach:
Step One: Solve for a Single LSN
Initially, we determined which layer corresponds to each key for a fixed LSN. This answer changes only at select points across the key space, allowing us to record those points in a binary search tree. This tree serves as the layer coverage for that specific LSN, enabling rapid responses to read requests at that LSN with a single lookup. However, this solution is limited to a single LSN.
Step Two: Make the Tree Persistent
To extend the solution, we developed a persistent structure that retains old versions. We incrementally built the coverage by inserting layers in LSN order from the bottom up. Inserting a new layer modifies only the nodes along a single path from the root downward. Instead of overwriting these nodes, the system duplicates them, preserving the originals and linking the new copies to the unchanged subtrees on either side.
This approach yields two significant outcomes:
- The insertion incurs minimal costs, requiring only a handful of new nodes rather than an entirely new tree, as everything off the path remains shared.
- The original root continues to accurately describe the tree as it existed prior to the insertion, maintaining its validity as coverage for earlier LSNs.
By applying this method to each layer in sequential order, we ultimately create a unified structure containing every intermediate root, each representing coverage at different LSNs. Consequently, historical reads incur the same cost as current reads: the system selects the appropriate root for the desired LSN and performs a single lookup.
In summary, the key insights are:
- Latest-only reads require a single tree lookup.
- Historical reads utilize an older root, incurring equivalent costs.
- Building these roots remains cost-effective as layers accumulate, ensuring that an extensive history does not hinder lookup speeds.
Where Object Storage Actually Sits
Discussions surrounding the integration of Postgres and object storage often miss critical nuances. The conventional argument against constructing OLTP systems on object storage typically posits that:
- Postgres processes numerous small, latency-sensitive I/Os.
- Object storage is designed for larger requests with higher latency, resulting in read times that can extend into the hundreds of milliseconds.
- Incorporating S3 into query execution would lead to a sluggish database experience.
While this argument holds merit, it fundamentally misinterprets the architecture we propose. It assumes that a database reliant on object storage must read directly from it to fulfill queries. In our design, this is not the case:
- Queries do not access object storage directly. The compute node first checks RAM, followed by local NVMe, and only resorts to the pageserver when necessary. Object storage is utilized solely within the pageserver for reconstructing page versions that are not already available.
- Commits do not write to object storage. A commit is acknowledged once a quorum of safekeepers confirms the WAL record, while the materialization of pages and their subsequent upload occurs afterward.
By architecting Postgres in this manner, we create an evolution of traditional OLTP systems, specifically designed to accommodate agent workloads. This is the essence of Lakebase Postgres: an OLTP database where compute and storage are decoupled, with the durable source of truth rooted in object storage.
Why Use Lakebase Postgres Over Vanilla Postgres
With Lakebase Postgres, transaction history is addressable by LSN, and copies are references rather than duplications of data. This capability enables the development of features that provide Postgres with the lightweight workflows essential for agents.
Branching
One of the most significant advancements is the introduction of branching. Creating a branch no longer necessitates copying pages; instead, it establishes a pointer to a specific LSN, allowing the branch to diverge with copy-on-write semantics. Writes to the branch are recorded as deltas against the parent, enabling the creation of a branch from a 2 TB database in mere seconds, with no associated costs until modifications occur. The parent database experiences no additional load, ensuring safety when operating against production environments. This functionality empowers agents to create branches for individual tasks, execute migrations against real data, and inspect outcomes without impacting the parent database. Multiple agents can operate concurrently, each isolated from one another and from production.
Furthermore, we have extended branching capabilities beyond the database itself. Object Storage buckets, Functions, Managed Better Auth state, and AI Gateway configurations can also branch alongside the database, resulting in an isolated copy of the entire backend rather than just the Postgres tables.
Instant Restore
Point-in-time recovery is akin to branching but serves a different purpose. Restoring from an earlier LSN allows for resumption without the need to copy data back into place, and the associated costs do not scale with database size. The extent of restoration is governed by a retention setting, making it easy for agents to rectify mistakes. When an agent executes an erroneous statement, the solution is not a lengthy recovery plan but rather a simple adjustment of the branch to reference the LSN prior to the erroneous action. The cost of undoing changes remains constant, regardless of database size, enabling agents to retry actions without escalating issues to human intervention.
Time Travel Queries
The pageserver’s ability to reconstruct any page at any LSN within the history window facilitates direct querying of past states without the need for prior restoration. This capability is particularly useful for diffing, allowing users to compare table states before and after migrations, as well as confirming timestamps before committing to a restore.
Read Replicas Without Replicas
Introducing a read-only compute node does not necessitate creating a copy of the data. Instead, it requests pages from the same storage layer as the primary node, allowing for rapid deployment without the need to provision datasets or wait for synchronization. Spinning up a read-only compute node becomes a metadata operation.
Scale to Zero
Since the durable state resides outside the compute layer, idle compute nodes can be completely shut down, eliminating the need for continuous operation to safeguard data. Compute nodes can suspend after five minutes of inactivity and reactivate within mere milliseconds upon receiving a new query. For a fleet of databases tied to individual sessions or branches, most of which remain idle, this feature transforms the cost model from unviable to sustainable. It ensures that an agent session lasting four minutes and subsequently going quiet incurs no compute costs five minutes later, without requiring manual teardown.
One Copy for Transactions and Analytics
Another significant advantage of utilizing object storage for operational data is the elimination of the need for separate copies of data in different formats. Once the durable record of a transactional database is stored in commodity object storage, it becomes accessible to other engines. This principle underpins what we refer to as LTAP, or Lake Transactional/Analytical Processing: rather than maintaining two copies of data in disparate formats synchronized by a pipeline, a single durable copy in open columnar formats serves both transactional and analytical needs.
The mechanism follows the previously described read path. As the pageserver materializes pages into object storage, it transcodes them from Postgres row format into columnar format, preserving the exact representation of every value. An analytical query requests the current LSN from Postgres, which is a straightforward metadata lookup. The majority of the data is then read from object storage as of that LSN, with only the most recent unmaterialized changes fetched from the pageserver. Postgres does not handle any analytical read traffic beyond providing that single number, ensuring that large analytical queries do not compete with transactions for CPU resources.
Lakebase Postgres for Agents
We began this discussion with a pivotal question: can object storage effectively support Postgres and enhance agent interactions? The answer is affirmative. Object storage can indeed serve as a foundation for Postgres, transforming the way agents engage with the database. However, this transformation extends beyond the mere advantages of speed or cost-efficiency associated with S3. As outlined, it necessitates thoughtful engineering. While RAM and local NVMe remain essential for delivering rapid query responses, commits still rely on replicated WAL rather than directly interfacing with object storage.
The WAL component is crucial. Object storage provides a cost-effective and scalable means of retaining historical data, but establishing the WAL as the source of truth is what renders that history addressable, fundamentally altering agent interactions with Postgres and enabling the development of innovative features atop this architecture.
Agents are encouraged to deploy Lakebase Postgres and explore its capabilities. Get started here.
Lakebase Postgres can function as a standalone database, and it can also be seamlessly integrated with the broader Databricks Data + AI Platform, encompassing Unity Catalog governance, lakehouse analytics, notebooks, and AI workflows.
