When Rows Outgrow the Page: RegattaDB vs. PostgreSQL TOAST

TL;DR

Agentic AI workloads often push row size and update volume into this territory as a matter of course. That's where slotted pages start to cost the database.

Large rows break slotted pages. We tested rows just over a page (~10KB) against PostgreSQL's TOAST and RegattaDB's log-structured row store.

• RegattaDB inserted rows 8.6x faster.
• On a fresh table, RegattaDB full-scanned 2.7x faster.
• After 1.2 billion point-updates, PostgreSQL's scan was 25x slower than RegattaDB. RegattaDB's scan time held flat past 2.4 billion updates.

Introduction

Agentic AI needs a database that handles large, varied data at scale, with real serializability and sustained performance as agents keep writing. RegattaDB was built for that. Its scale-out design grows to thousands of nodes without giving up performance or efficiency on small deployments, down to a single node.

What does a row look like in an agentic workload? A single agent turn can carry a tool-call trace, retrieved context passages, one or more embeddings, and running conversation state, often serialized together as one row. As they work within context, they can easily expand row size well beyond what typical humans can decipher or reason.

Agentic AI, as well as modern applications, use row sizes that can easily land past a few KB, and well into the range where slotted pages start needing an overflow strategy. And unlike a typical OLTP row that's written once and read many times, agent state gets revised repeatedly as a task runs: append a step, update a status, patch in a new tool result. High row size and high update churn, together, on the same row. That combination is exactly what this experiment isolates.

Background

We built RegattaDB for performance, performance at scale, and performance efficiency. Its scale-out design enables RegattaDB to grow to thousands of nodes in a cluster, without compromising the performance or efficiency of small configurations – not even of single-node ones.

Data Layouts and Storage I/O Performance Matter

Storage I/O performance has a major effect on the overall performance of the database. The performance of the underlying disks (e.g., SSDs) does matter, no doubt about that. However, the data layouts used by the database have a cardinal impact over the database performance. I’m using the term Data Layout to describe the persistent data-structures as well as the corresponding mechanisms used by the database. The data layout, and the entire “storage-engine” of the database dictate how much (read and write) disk I/O amplification takes place, whether the underlying disks are accessed in ways that resonate well with their performance characteristics, and whether the database can leverage or even saturate the underlying disks.

About The Slotted Pages Data Layout

Many traditional relational databases use variations of the Slotted Pages data layout to store table rows. The slotted pages data layout performs reasonably well for small rows, especially fixed-size ones. When rows are larger, change in size, or the table shrinks, the slotted pages approach becomes inefficient.

One of the main problems with slotted pages is related to how the database handles rows that are larger than a page. For many databases the size of a page is pretty limited, e.g., 8KB, due to architectural reasons that are outside the scope of this discussion. Slotted pages based relational databases use various strategies to fight that fundamental problem. Some databases use the concept of “overflow pages”, or variations of such strategies – e.g., PostgreSQL’s TOAST, which I’ll cover shortly… Those strategies unavoidably degrade storage I/O performance as they lead to disk I/O amplifications, yet are essential to enable handling of larger rows.

Unfortunately, modern data often needs rows that are larger than the typical slotted page size.

RegattaDB’s Modular Data Layout Architecture

As mentioned in the RegattaDB Architecture blog, RegattaDB has a modular data layout architecture, allowing various layouts to optimally support different data and access patterns.
It was important for us to design a data layout that would work well with modern data and media. RegattaDB’s first row-store data layout is specifically optimized for flash media. It allows us to optimally support both traditional small-rows-with-more-or-less-fixed-size, and variable-sized-large-rows-with-a-large-dynamic-range-of-sizes (all within the same table). Since traditional slotted pages were not an ideal fit for that purpose, we developed our own log-structured data layout. This data layout is optimal for a large variety of workload types.

Note: We chose not to use the common log-structured merge tree (LSM tree) approach, since some properties of LSM trees don’t always align well with flash media or certain types of workloads. RegattaDB’s log-structure data layout operates very differently from LSM tree.

Note: As an additional disk I/O performance optimization, RegattaDB implements its own data layouts directly on raw block storage, bypassing the filesystem and the I/O overheads it would otherwise introduce.

Planning a Comparison between RegattaDB’s Data Layout and PostgreSQL’s Slotted Pages

We conducted a comparative experiment between RegattaDB and PostgreSQL to demonstrate the performance differences between RegattaDB’s data layout and a slotted pages data layout when handling rows that span more than a page. Specifically, we looked at PostgreSQL’s slotted pages implementation.

In this blog we will describe the experiment and share some performance results.

Even though RegattaDB is a distributed database that was designed for large scale, we will still compare only a single node of RegattaDB against a (single-node) PostgreSQL.

Guiding Principles

To get meaningful, fair and reliable comparison results, we obeyed the following guiding principles:

• Measure performance over time: The performance of data layouts may change over time. It is common that a data layout starts its life providing a certain performance envelope, and that performance degrades the more data and/or more operations on the data take place. How a data layout “ages” is a very important attribute, sometimes overlooked by too short experiments.
• Minimize read cache effect: Data layout and the related disk I/O performance are all about interactions with data on-disk. In-RAM cached data may have a blurring effect on such measurements, as when accessing data that’s cached in RAM, the data layout “penalty” is bypassed. Don’t get me wrong, in-RAM read cache has an important impact on database performance, although its effect often diminishes in workloads that use large-enough on-disk working-sets. RegattaDB uses a unique “logical” caching for its read cache that may result in much more effective caching than the usual “physical” page caching approaches used by most other databases around. However, we wanted to completely neutralize those effects, and focus on the data layout and storage part of the house. This could be achieved by various techniques, such as (1) decreasing the read cache size, and/or (2) enlarging the dataset size, and/or (3) coming up with experiments that naturally negate the read cache effect. In our experiments we chose a combination of (1) and (3), mainly to speed up the experiments.
• Minimize computations, network, etc.: Excessive computations or network traffic might also have a blurring effect on the measurements. Therefore, the comparative experiment should attempt to minimize those as much as possible.
• Be fair: Reasonably optimize PostgreSQL: We started with the recommended PGTune settings as a baseline. Then we further optimized the PostgreSQL configuration and tuning for even better performance. Additionally, whenever any of the databases had a different recommended method of doing something, we complied with that database’s own optimal approach.
• Maintain ACID: The experiment should comply with real life scenario constraints. Therefore, we want to maintain full ACID.

The Experiment, at a High-Level

We wanted to conduct a reasonably simple experiment:

1. Preparation: Create a simple schema, with a single table of rows that are a bit larger than a slotted page (e.g., ~10KB).
2. Insertion phase: Insert a large-enough number of rows (but not amazingly large – e.g., 30 million).
3. Post-insertion full scan phase: Initiate a full scan that will need to access sufficient parts of the row. It should be light on computation and light on the “data returned to the client” (to minimize computations and network – see guiding principles above). Measure how long the full scan takes, how much the underlying disk is utilized, etc.
4. Update phase: Start a lengthy period of random updates to those rows.
5. Stop: Stop the updates, and wait for any background tasks (e.g., VACUUM) to complete.
6. Post-update full scan phase: Repeat the same full scan as in step (3). Measure how long it takes, how much the underlying disk is utilized, etc.

We mainly wanted to measure the full scan performance (steps 3 and 6), but we couldn’t resist the temptation, and had an eye on the performance of other stages of the experiment as well…

We used a bare-metal server and a 1.5 TB NVME SSD, capable of serving a sustained ~5-6 GB/s read workload and ~2 GB/s (or slightly less) write workload. That disk was dedicated to the database, as there was a separate disk for the operating system. We allocated identical servers to PostgreSQL and to RegattaDB.

Note: We also switched between the servers, from time to time, ensuring both servers perform the same.

For both databases, we limited the execution to a single NUMA domain (out of two) to avoid any unwanted side effects. For the server running PostgreSQL, we used PostgreSQL 18.3, running on a Red Hat Linux 8. The filesystem was XFS, with configuration options optimized specifically for PostgreSQL. Nothing fancy. We did limit the cache PostgreSQL can see to ~8 GB (we should remember that PostgreSQL also leverages the file-system cache so this should be incorporated as well). Limiting the cache of RegattaDB is even simpler as it does not use any file-system cache (not explicitly and not implicitly). RegattaDB was installed on an identical server (and single NUMA, same OS, etc.), except that the (identical) disk was not formatted with a filesystem since RegattaDB uses raw block storage.

To make things simple, we configured a table with 20 varchar columns of roughly 500 bytes each. We also configured a means (e.g., a 64 bit primary key) to uniquely identify a row during the update phase.

For the workload generation, we used a home-grown Python program for both PostgreSQL and RegattaDB. The program differs only at its bottom layer: For PostgreSQL it uses the asyncpg python library that provides high performance asynchronous processing. For RegattaDB it uses RegattaDB’s pyregatta library that links with RegattaDB’s low-level connector. To avoid network bottlenecks we located the program in the same server as the database. We ensured that the workload generator is efficient and would not consume resources from the database itself. We monitored that closely during all stages of the experiment, for both PostgreSQL and RegattaDB.

About PostgreSQL’s TOAST

When a row is larger than a page (8KB), PostgreSQL stores some cells of the row in the “table file” (called Heap File in PostgreSQL’s terminology) and stores the remaining cells in a separate file called the TOAST File. Those cells will be denoted here as the TOASTED Cells. That’s PostgreSQL’s variation of how to handle the “slotted page overflow”.

The Insertion Phase

Following the initial setup, the first step was to insert 30 million rows into the table. To be honest, we originally thought this would be a “utilitarian” step: After all, to conduct a full scan (which was “our goal”) we needed to have rows to scan, which is nothing to write home about…

We expected both databases to be able to perform the insertion quickly and efficiently. However, we quickly realized that there is a surprising difference between RegattaDB and PostgreSQL:

RegattaDB

RegattaDB reached a sustained insert rate of 196,068 rows per second (2 minutes and 33 seconds for the entire insertion phase). The recommended technique for RegattaDB is to use insert statements that each contain multiple rows. Concurrency can further boost performance.

Note: Unlike many databases, a RegattaDB client can submit multiple transactions concurrently over a single connection/session. Therefore, concurrency can be achieved by either multiple sessions/connections or by a
single connection.

An interesting aspect of RegattaDB’s architecture is that it does not need to store the row’s data first to a WAL. Instead, the row’s data is written only once, directly to the log-structured row-store. In other databases (and PostgreSQL is not an exception), the row’s data is written twice: first to the WAL, and later to the heap file. Avoiding that “double write” write amplification is a RegattaDB optimization that further improves its performance and performance efficiency.

It should be noted that the insert operations obeyed strong ACID, and we did not use any techniques such as bypassing SQL parsing. The insertion rate was optimal, and RegattaDB effectively saturated the underlying disk.

PostgreSQL

For PostgreSQL, we began by using a similar insertion technique to what we used for RegattaDB – that is, multiple rows inserted per statement, and with concurrency. Note that the concurrency was achieved by multiple connections/sessions since PostgreSQL cannot send multiple concurrent transactions per connection/session.

The INSERT Technique

The insertion rate was low: less than 10,000 rows per second. This surprised us. Sure, we did expect some write amplifications (TOAST, WAL, etc.), but that might have resulted in PostgreSQL performing maybe 2X or 3X slower than RegattaDB. However, we observed a much higher factor.

We tried to further play with concurrency, with the number of rows per insert statement, etc. However, we didn’t reach any meaningful improvement.

At a later stage we further optimized PostgreSQL’s WAL and were able to improve the INSERT technique to around 14,800 rows per second. See Figure 1.

The COPY Technique

Since we were not satisfied with the results of the INSERT approach, we switched to another technique that is recommended by PostgreSQL: The COPY command. With that technique, the client streams rows to the database using a protocol that bypasses SQL parsing. It still obeys ACID rules, so we found that technique “legit”.

A single COPY resulted in a too-low rate. We played with concurrency – that is, divided the 30 million rows population among multiple concurrent COPY operations. That improved things, yet increasing the concurrency hit a glass ceiling very quickly.

Note: Dividing each COPY stream to smaller bulks, and executing them serially, concurrently to other COPY streams, did not help improve the PostgreSQL insertion rate.

We then tried some additional PostgreSQL tuning parameters like further increasing WAL size. It made a difference. With a single COPY we reached an insertion rate of 11,081 rows per second (slightly more than 45 minutes), and with a concurrency level of 8, we reached a rate of 22,924 rows per second (21 minutes and 49 seconds). Higher concurrency levels did not improve results any further.

Discussion

The maximum insertion rate that we were able to achieve with PostgreSQL was 22,924 rows per second (vs. RegattaDB’s 196,068 rows per second). PostgreSQL’s write amplifications (caused by the slotted pages data layout approach), as well as the fact that RegattaDB does not need to write to any WAL for its row’s data, are probably only part of the picture. The additional explanation is a bit more speculative: We presume that PostgreSQL was, on one hand, not able to saturate the disk with concurrency level of one (very reasonable), but on the other hand, suffering from some sort of internal contentions when higher concurrency level values were used.

Note that the client-side was not stressed at all, so that was not the source of the problem.
Figure 1 summarizes the insertion phase results.

Conclusion

We tried to optimize PostgreSQL as much as we could. RegattaDB was able to insert the 30 million rows 8.6X faster than PostgreSQL. The reasons for that difference are a variety of data layout and storage-engine I/O-amplifications, as well as possible contentions and other overheads in PostgreSQL.

RegattaDB’s data layout showed good performance (and performance efficiency) that was close to the physical limits.

The Post-Insertion Full Scan Phase

We inserted the rows, and reached the first full scan phase. As mentioned, we wanted the full scan to access multiple parts of each of the rows, and yet be lightweight in terms of CPU and network. We used the following statement:

SELECT count(*) FROM test_table WHERE left(c1,4)='abcd' OR left(c2,4)='abcd'
OR left(c3,4)='abcd' OR left(c4,4)='abcd' OR left(c5,4)='abcd' OR
left(c6,4)='abcd' OR left(c7,4)='abcd' OR left(c8,4)='abcd' OR left(c9,4)='abcd'
OR left(c10,4)='abcd';

We planted the data in such a way that none of the rows would satisfy the predicate, so that the entire predicate must be calculated, accessing 10 different cells of each row.

RegattaDB

RegattaDB performed the full scan in 50 seconds (599,520 rows per second). The underlying disk was fully saturated. It should be noted that RegattaDB’s row-store data layout following the insertion phase was completely “fresh”, giving optimal results. That was our expectation. We also expected the full scan duration to be somewhat increased following the next lengthy update phase, but in a reasonably limited and “controlled” manner.

We repeated the full scan. The results were the same. Good. Let’s switch to PostgreSQL now…

PostgreSQL

First Full Scan is Slower

Switching to PostgreSQL, we noticed that the first full scan (just after the insert phase) was significantly slower than the next, repeated, full scans. We will provide more information about this later. For now, let’s review results that exclude those slower “first” full scans.

The Results

Ignoring the very-first (slower) PostgreSQL full scan, the next full scan took 277 seconds. 5.5X slower than RegattaDB (50 seconds). PostgreSQL was configured to run with 8 parallel workers, as recommended by PGTune.

The heap and the TOAST files were in a reasonably “fresh” state, meaning that we expected PostgreSQL to give its best.

At a later phase of the experiment, we discovered that increasing the number of parallel workers to a larger number improved PostgreSQL’s full scan performance. We then re-ran the full scan with larger number of parallel workers, and we were able to improve the results: With 20 parallel workers, the scan took 151 seconds, and PostgreSQL maxed out with a value of 60 parallel workers, resulting in 136 seconds, only 2.7X slower than RegattaDB.

Intermediate Conclusion

Both databases saturated the disk, reaching around 6 GB/s of read throughput. And yet, RegattaDB was 2.7X faster. Why is that? The answer is simple: PostgreSQL’s data layout approach causes read amplifications that hurt the overall performance and performance efficiency.

However, that’s how those data layouts behave in their “fresh” state. How will they “age”? We will soon find out.

PostgreSQL First Full Scan is Slower

Before we move on, let’s talk about PostgreSQL’s first full scan after the insertion phase. As mentioned above, we noticed that the very first full scan, following the insertion phase, took longer.

How long? For example, with the fastest 60 parallel-workers setting, the first full scan took 245 seconds while the second (and the following) took 136 seconds. The first PostgreSQL scan was 1.8X slower than the following PostgreSQL scans.

One may suggest it’s the effect of read-caching. However, it is arguably not the explanation for what we observed. In fact, we measured meaningful writing activity during the first full scan, and zero writing activity for the later scans. Apparently, as part of the full scan, for the heap pages it read, PostgreSQL opportunistically set the “committed” bit in the row headers, which caused those pages to be then flushed to disk. That activity is related to the MVCC processing of those recently written row-versions (regardless of whether those were inserts or updates). That meaningful write activity slowed down the scan, and the scan read I/Os were effectively blocked by those write I/Os.

Note: Those pages were already written to the heap file earlier, and became dirty due to the MVCC-related metadata changes described above. So, the data was written once to the WAL, then written (second time) to the heap files, and then rewritten (“third time”) to the heap files. We may dive into a more detailed explanation in a future blog, where we will compare RegattaDB’s and PostgreSQL’s MVCC approaches.

The Update Phase

To see how the data layouts “age” (=perform over time), we started a lengthy phase of “point-update” transactions, where each transaction updates one random cell of one random row. The row was identified in a unique manner (e.g., using a primary index in PostgreSQL). How lengthy did we want to get? Well, we wanted to go beyond one billion updates. We mainly viewed that entire process as a “utilitarian” phase, and yet we tried to get a healthy point-update transaction rate. We increased the concurrency level of transaction submissions to get higher transaction rates.

PostgreSQL

With PostgreSQL:

• Concurrency level 48: We were able to achieve an update rate of 22,126 transactions per second (TPS) with a concurrency level of 48. Higher concurrency levels did not improve the rate. However, after ~684 million point-update transactions (~8 hours and a half), we reached the end of disk capacity and were forced to stop the experiment. We had a dedicated ~1.5 TB disk, and only 30 million 10KB rows (around 300 GB net capacity). Just for the elimination of doubt: The updates do not increase the number of rows or change individual row sizes.
• Concurrency level 24: Re-doing the experiment with a concurrency level of 24 (which lowered the update rate to 15,035 TPS) allowed PostgreSQL to complete without overflowing the disk capacity.

What happened here?

This has to do with PostgreSQL’s MVCC design. More specifically, with PostgreSQL’s unneeded row-version cleaning mechanism: VACUUM. Before we continue with our analysis, let’s briefly discuss the topic of unneeded row-version cleaning.

About Row-Version Cleaning and VACUUM

In PostgreSQL, any new row-version of a row is written elsewhere on disk, and the previous row-versions of the row are not overwritten or removed. Those older row-versions may become unneeded very quickly.

Terminology: We use the term Unneeded Row-Version to denote a row-version that no transaction could possibly read. At any given moment in time, a row may have multiple row-versions that could be read by various transactions that use a variety of point-in-times for their readings. However, once those transactions complete, some of the row-versions may effectively become unneeded.

However, PostgreSQL cannot know where those unneeded row-versions are located on the disk. In addition, calculating whether such an older row-version is indeed unneeded is non-trivial and is subject to a variety of restrictions.

PostgreSQL’s VACUUM is a process that crawls the tables’ heap files, scanning all the row-versions, and checking, for each row-version, whether VACUUM can deduce that it became unneeded – all that, subject to a variety of restrictions that may cause VACUUM not to classify the row-version as unneeded although it actually is… For example, row-versions that are younger than the oldest existing snapshots cannot be categorized as unneeded even if they are. The VACUUM algorithm is not amazingly efficient as it needs to crawl and check every row-version (not to mention misdetections due to the various restrictions that may delay the detection for later crawls…). There is an additional complication when the rows are indexed, as the way VACUUM works requires it to perform a heap scan, then an index scan (for each of the table’s indexes), and then a second heap scan, which further increases its work as well as further delays its row-version cleaning. VACUUM may badly, and often unpredictably, affect the overall I/O performance of PostgreSQL. However, disabling VACUUM in an effort to gain performance would lead to capacity explosion. In our case, disabling VACUUM during the update phase would inflate the size of the heap files to potentially gigantic size – orders of magnitudes larger than our disk’s capacity.

Row-Version Cleaning in RegattaDB

RegattaDB treats unneeded row-version cleaning completely differently. RegattaDB’s Ranger technology maintains precise metadata about which row-versions became unneeded. There is no crawling, no approximation, and no dependency on the oldest active snapshot. Long-running analytical queries do not stall transactional cleanup. OLTP and OLAP workloads run concurrently over the same data without inflating each other's version debt.

Comparing VACUUM with RegattaDB’s Ranger is a topic for another blog. Stay tuned… But for now, let’s get back to our utilitarian update phase…

Analysis of the Capacity Overflow (Concurrency Level 48)

PostgreSQL’s point-update transactions and VACUUM “competed” with each other, as both needed to perform disk I/O and CPU work. When updates are “winning” then more and more old unneeded row-versions remain, and the newer updates increase the heap file size. On the other hand, if more priority is given to VACUUM, then it needlessly consumes (or shall I say, wastes) more and more disk I/O resources for its crawling, which further slows the update transaction rate.

The competition between the point-update transactions and VACUUM has chaotic attributes. It’s somewhat fluctuating. In the concurrency level 48 case, the updates eventually won. VACUUM lost. As a result, the disk overflowed…

Philosophically, each point-update leaves a previous row-version cleaning “debt”. Many systems have to (or are intentionally designed to) defer some debt work to asynchronous background tasks. Such systems should arguably be able to deal with increased background debt, for example, by pushing back the foreground activities. This is almost always non-trivial. At Regatta, we nicknamed that phenomenon “the curse of the background”.

It seems that PostgreSQL’s internal throttling mechanisms could be improved (which probably is non-trivial due to the nature of the VACUUM algorithm), or that users would need to dynamically throttle their workloads.

Note: Agentic AI workloads are less predictable than human-scale workloads. Requesting agents to “calm down” will probably not always be a good strategy. The problem of unneeded row-version cleaning would become even more challenging in combined transactional (OLTP) and analytical (OLAP) workloads, as the mixture of lengthier and shorter transactions and snapshots might make the inability to clean row-versions that are younger than the oldest active snapshot more painful.

RegattaDB’s unneeded row-version cleaning is also done in the background. As mentioned, it’s much more efficient (no crawling, knowing exactly which row-versions to remove, and “just on time” removal). Additionally, in extreme cases, those background operations can dynamically throttle foreground activities. Therefore, for example, a massive update transactional workload will not cause RegattaDB’s capacity to grow indefinitely…

Continuing with Concurrency Level 24

As mentioned earlier, we restarted the entire experiment with concurrency level 24 for the update phase. The (rather fluctuating) average point-update transaction rate was 15,035 TPS.

At that concurrency level, the point-update transactions and VACUUM were obviously still “competing” with each other. When the updates were “winning” then older unneeded row-versions remained, and the newer updates increased the heap file size – which is practically irreversible.

We executed 1.2 billion point-update transactions. That process took approximately 22 hours. Once we stopped the updates, we let VACUUM and any other background processes complete before we moved on. At the end, the heap file size was 360 GB (increased sixfold from 57 GB before the updates started). The TOAST file size grew from 278 GB (before the update phase) to 761 GB, even though the number of rows in the table remained unchanged. That sparseness is clearly undesired for both capacity consumption and performance reasons although, as discussed below, it became just one contributor (and not the major one) to the performance results of the full scan that was executed after the update phase.

Note: In another run under the same conditions, the heap size grew to ~450 GB instead of the 360 GB reported above – a reminder of how unpredictable the competition between the point-update transactions and VACUUM can be. (The other results in this post were consistent across multiple runs; this variability was specific to heap growth.)

RegattaDB

We let RegattaDB perform the same number of point-update transactions (1.2 billion), at a rate of ~29,500 TPS (we did not try to optimize that, quite frankly) and completed the phase in ~11.5 hours.

Note: We executed this update phase with various levels of concurrency, including concurrency levels of 32, 64, 128, 256 and 512. In all those cases, the results (capacity-consumption-wise, as well as the following full scans) were effectively the same. That demonstrated how RegattaDB’s internal mechanisms balance background and foreground activities without suffering from the “curse of the background” phenomenon.

In the end, RegattaDB’s data layout was about 90% dense, which would be friendlier to the later full scan performance.

Geek note: RegattaDB’s log-structured row-store data layout is not residing inside a file, as RegattaDB bypasses the filesystem and works directly on top of raw block storage. The data layout inherently and automatically maintains its density. In our case, the data layout did not really become sparse. However, even in cases where RegattaDB’s row-store data layout becomes sparser, RegattaDB does not need to read the “empty spaces”, as its algorithms “know” where those empty spaces reside. Furthermore, as an MVCC data layout, there may be stored row-versions that our full scan won’t need (e.g., for other timestamps, serving reads for other point-in-times). RegattaDB’s full scan algorithms will not need to read such row-versions at all! It can simply skip them. PostgreSQL, on the other hand, cannot avoid reading empty spaces, just to realize they are empty, and must read all the stored row-versions, regardless of whether they are related to that full scan or to other points-in-time. In our experiment, we did not focus on exemplifying those differences. We may post about this topic in another blog post soon.

The Post-Update Full Scan Phase

We completed the 1.2 billion point-updates, waited for background processes to complete, and initiated the same full scan as performed before the update phase. As a reminder, we wanted to see how the data layouts “aged” after handling those update transactions.

RegattaDB

For RegattaDB, we expected the full scan duration to increase in a reasonably limited and controlled manner following the lengthy update phase. Indeed, RegattaDB performed the full scan in 59 seconds (508,810 rows per second). The expected small degradation versus the first “fresh” scan was mainly accounted for by the difference in read I/O sizes that were submitted to the underlying disk.

Repeating the full scan provided similar results.

The results were in-line with what we expected. As already mentioned earlier, we expected the post-update full scan duration to be somewhat increased, but in a reasonably limited and “controlled” manner.

PostgreSQL

First Attempt

Switching to PostgreSQL. As usual, let’s completely exclude the “first full scan that is slower”. With the recommended 8 parallel workers, PostgreSQL’s (second) full scan took 5,506 seconds, i.e. slightly less than 1 hour and 32 minutes(!). This is 93X slower than RegattaDB.

The underlying disk was very far from being saturated.

Improving PostgreSQL

We realized that PostgreSQL is most probably unable to submit sufficient concurrent read I/Os to the disk – possibly because some of the reads depend on others (e.g. we have heap and TOAST elements in town, including the heap page that references a TOAST-ed cell that requires an additional TOAST index lookup to fetch , and so forth).

As a result, we started to gradually increase the number of parallel workers (including re-runs of the full scan). As we increased it, the full scans’ durations became shorter, and the utilized disk bandwidth became higher, until we reached the limit of disk saturation. Voilà. At that point, as expected, further increase in the number of parallel workers did not help anymore. That point was at 60 parallel workers.

Note: We can’t say whether using such a large number of parallel workers would have negative side-effects for PostgreSQL’s normal operation.

With 60 parallel workers, PostgreSQL executed the full scan in 1,470 seconds, i.e. 24 minutes and 30 seconds. 25X slower than RegattaDB.

Note: This result excludes the “first scan” (which was even slower).

Analysis of the Full Scans’ Performance

Result Summary

Let’s try to analyze the following full scan related results:

• Prior to the update phase, PostgreSQL’s full scan executed 2.7X slower than RegattaDB (136 seconds vs. 50 seconds). Both were able to saturate the disk (with a healthy ~6 GB/sec read throughput).
• After the update phase, PostgreSQL’s full scan executed 25X slower than RegattaDB (1470 seconds vs. 59 seconds)
• PostgreSQL’s full scan performance deteriorated from around 136 seconds prior to the update phase to 1,470 seconds following that phase – a deterioration of 10.8X.

Note: We completely ignored PostgreSQL’s extra slowness “in the first full scan”. We only worked with results of the scans “after the first scan”.

Slotted Page Row-Overflow Solution has its Cost

PostgreSQL

Prior to the update phase, PostgreSQL’s full scan was 2.7X slower than RegattaDB, mainly because a slotted pages data layout requires some on-disk solution for the rows that cannot fit a slotted page (TOAST). Such a solution unavoidably involves some levels of read amplification. PostgreSQL had to read each row from the heap as well as from the TOAST area. Prior to the update phase, since each of the rows was inserted as a whole, the TOAST columns of each row tended to sit together in the same disk page. As a result, the read amplification (vs. RegattaDB) was real, and yet reasonably bound.

RegattaDB

RegattaDB’s row-store data layout was dense and therefore no read amplification occurred.

Heap File Sparseness (After the Update Phase)

PostgreSQL

As mentioned, after the update phase PostgreSQL’s heap file became sparser – 6.3X larger than its size just after the insertion phase. That would increase the heap file part of the scan by about 6.3X. In general, this could become a meaningful deficiency for PostgreSQL full scans, related to the entire VACUUM and MVCC operation.

Interestingly, however, in our specific case, it was arguably not the main contributor to the degradation (the “TOAST locality effect” described below is the dominant contributor).

RegattaDB

In contrast, RegattaDB’s row-store data layout generally maintained its density.

Note: As already discussed earlier, RegattaDB’s full scan can “skip empty areas” (as well as not need to read row-versions that are not related to the relevant point-in-time of the concrete full scan). Therefore, and especially with larger rows, even a sparser data layout wouldn’t necessarily meaningfully degrade RegattaDB’s full scan performance.

The TOAST “Locality Effect”

The update phase gradually deteriorated the locality of the TOAST-ed data. As the update phase progressed, cells started to move around to other areas in the TOAST file. As a result, at the end of the update period, reading of each row required quite a few additional read I/Os from disk. When PostgreSQL reads a cell of 0.5KB from disk, it still needs to read the entire 8KB page. The worse the locality is, the higher the read amplification becomes.

That was the dominant part of PostgreSQL’s performance degradation.

Analysis Summary

To conclude:

1. Prior to the update phase, PostgreSQL’s full scan executed 2.7X slower than RegattaDB because the slotted pages data layout requires some on-disk solution for the rows that cannot fit a slotted page (TOAST). That solution unavoidably introduced some levels of read amplification.
2. After the update phase, PostgreSQL’s full scan executed 25X slower than RegattaDB because of the combination of:
   • The need for a solution for overflowed rows (see (1) above).
   • The sparseness of the heap file (6.3X larger than after the insertion phase).
   • The loss of locality among cells in the TOAST file.

Deterioration Over Time

The above analysis demonstrates why data layout performance should be measured over time. The performance of a “fresh” data layout may deteriorate over time, and different data layouts may vary in their deterioration characteristics. The experiment described so far had two “measurement points” for the full scans: One before the update phase and the other after the 1.2 billion point-updates.

As additional evidence for the above analysis, let’s view the deterioration over time, by using additional intermediate measurement points.

We re-ran the experiment, and measured full scans after 150 million, 300 million, 600 million, and 900 million point-updates, both PostgreSQL and RegattaDB.

The figure below shows the results, combined with the post-ingress results (i.e., the 0 point-updates value) and the full scan results after 1.2 billion updates.

PostgreSQL’s deterioration begins very early. After 150M point-updates, it is already 4.5X slower than prior to the beginning of the update phase.

RegattaDB’s degradation is very mild and is arguably not correlated with the number of point-updates. To strengthen the observation that RegattaDB’s scans are not affected by the “aging” of the data layout, we re-tested RegattaDB with larger number of point-update transactions, reaching up to 2.4 billion update transactions, as shown in Figure 5.

Conclusion

Summary

In this experiment we demonstrated some of the differences between RegattaDB’s row-store data layout and PostgreSQL’s slotted pages. We focused on rows that are larger than a slotted page, and saw how, for those rows, the performance and performance efficiency differences between RegattaDB and PostgreSQL became very meaningful. While the lion share of the performance differences was related to the difference between PostgreSQL’s TOAST/slotted-pages vs RegattaDB’s log-structured approaches, we also observed significant differences in MVCC handling (e.g., VACUUM, update-MVCC data on read) as well as performance degradation resulting from data layout sparseness. We also saw how RegattaDB’s WAL-free data hardening could further improve performance and performance efficiency. In addition, we observed how differently RegattaDB and PostgreSQL deal with “the curse of the background”, and with high level of update transaction concurrency.

The experiment was done with fairness in mind (e.g., optimizing PostgreSQL, letting each database do its thing in its best recommended manner) and with full ACID.

Summary of the Results

Highlights

• RegattaDB was 8.6X faster than PostgreSQL inserting the rows to the table.
• RegattaDB was 2.7X faster than PostgreSQL in full-scanning a “fresh” data layout (just after the insertion phase)
• RegattaDB was 25X faster than PostgreSQL in full-scanning an “aged” data layout (following 1.2 billion point-update transactions)

Below is a summary of the main results in the various phases of the experiment:

Insertion Phase

• RegattaDB’s ingress was 8.6X faster than PostgreSQL. It inserted 30 million rows in 2 minutes and 33 seconds (196,068 rows per second) while it took PostgreSQL 21 minutes and 49 seconds (22,924 rows per second) to do the same.
• In addition to its data layout that made storing medium-sized rows more efficient, RegattaDB did not need to write the row data to a WAL, which further contributed to its performance and performance efficiency.

Post-Insertion Full Scan Phase

• After the insertion completed, and before the update transactions began, RegattaDB performed the full scan in 50 seconds (599,520 rows per second) and PostgreSQL performed the same scan in 136 seconds (220,022 rows per second) – RegattaDB was 2.7X faster.
• The data layouts were in their “fresh” state, before they started to “age” by ongoing update transactions. At their fresh state, the data layouts were completely dense, and PostgreSQL’s TOAST locality was in a good shape. All that resulted in the best full scan results for both databases.
• Both databases’ full scans saturated the disk. And yet, RegattaDB was meaningfully faster, as PostgreSQL’s data layout involved a meaningful read amplification that was the source of the performance differences.
• PostgreSQL’s “first scan” following write activity was meaningfully slower (245 seconds vs. 136 seconds in repeated scans) due to MVCC handling that is done synchronously to related read operations. Regatta’s “first scan” is as fast as the next scans.

Update Phase

• PostgreSQL was not able to cope with a sustained point-update transaction concurrency level of 48 (that was maxed at 22,126 TPS) and, as a result, the in-use disk capacity (~1.5 TB) overflowed.
  • VACUUM and the update transactions competed in a somewhat chaotic manner. It seems that PostgreSQL’s internal mechanisms were not able to cope with the “curse of the background”.
• PostgreSQL completed the update phase (1.2 billion point-update transactions) with a concurrency level of 24 (15,035 TPS) without overflowing the disk, but with a non-insignificant sparseness.
• Without special optimizations, handled as a “utilitarian phase”, RegattaDB completed the update phase at a rate of ~29,500 TPS.
• PostgreSQL completed the update phase in ~22 hours and RegattaDB completed that phase in ~11.5 hours.
• RegattaDB was able to perform those 1.2 billion point-update transactions with a varying levels of concurrency, such as 64, 128, 256, 512, without being hit by the “curse of the background”. This is a result of:
  • RegattaDB’s Ranger technology - an efficient cleaning of unneeded row-versions
  • Internal throttling mechanisms that dynamically balance foreground activities with background activities effectively
• At the end of the update phase, RegattaDB’s row-store data layout remained dense.

The Post-Update Full Scan Phase

• After the update phase, as well as after associated background tasks completed (e.g., VACUUM), RegattaDB performed the full scan in 59 seconds (508,810 rows per second) and PostgreSQL performed the same scan in 24 minutes and 30 seconds(only 20,415 rows per second). RegattaDB was 25X faster.

Note: PostgreSQL’s even slower “first scan” was excluded from the reported results.

• Those results demonstrate how important it is to measure data layout performance over time, letting it “age”, and inspect the deterioration over time.
• PostgreSQL’s data layout meaningfully deteriorated over time, mainly due to the slotted pages’ row-overflow handling (TOAST) mechanism, and its loss of locality over time. In addition, the sparseness of the slotted pages data layout contributed to the performance degradation.
• RegattaDB scan performance was similar before and after the update phase (with an expected, mild yet “controlled” degradation). That degradation was not correlated with the number of update transactions and doesn’t change over time.
  • Re-runs of the experiment for RegattaDB with an expanded update phase (up to 2.4 billion point-update transactions) reinforced the conclusion that RegattaDB scans performance was not correlated with the number of previous update transactions.

Additional Topics

The experiment demonstrates scenarios and dynamics that are relevant for real-life workloads. Traditional relational databases use slotted pages and users were traditionally educated to prefer small rows for performance reasons. However, modern data does and will require more data per row. As for the minimized cache: That approach was taken in order to focus on measuring data layout performance in as ‘’clean” manner as possible. However, it should be noted that this configuration is equivalent to other real-life scenarios where one has a large amount of data that is much larger than the RAM of the database server. Deficiencies in data layout performance cause users to limit their on disk capacity such that it would not be meaningfully larger than the database server’s RAM. That obviously hurts overall TCO, as more and more servers will be required for larger data. In our experiment we used a table with only 30 million rows. Modern workloads, such as agentic AI, as well as other types of modern data may require significantly larger number of bigger rows, resulting in much larger capacity. Therefore, our experiment arguably represents real life configurations.