How We Made PostgreSQL Upserts 300x Faster on Compressed Data

As a developer, your customers' challenges often become your own. This was precisely the case when one of our customers, Ndustrial, an industrial energy optimization platform, reported that they had to resort to workarounds due to suboptimal PostgreSQL upsert performance when using TimescaleDB. Naturally, we were all ears.👂

How Does PostgreSQL Upsert Work?

Before explaining Ndustrial’s use case and schema, let’s talk about PostgreSQL upserts. Upserts work by checking the constraints defined on the target relation during an insertion. This process primarily relies on speculative insertion using a unique index.

When you do speculative insertion, you are basically trying to insert the new row in the index and look for conflicts. If the insert is successful, the row can be safely added. 

However, if there is a conflict, PostgreSQL resolves it based on the ON CONFLICT clause specified in the insert statement. When you choose the DO UPDATE option, you turn the insert statement into an update, which will, in turn, modify the conflicting row with the values from the new row being inserted. A full statement might look like this:

INSERT INTO sensors
  VALUES (1, '2024-09-18', 15.0)
  ON CONFLICT (id, timestamp)
  DO UPDATE SET reading = EXCLUDED.reading;

However, while TimescaleDB is built on PostgreSQL, things work slightly differently under the hood when using compression. So, before diving into the technical details of our optimization, it’s essential to understand a few key TimescaleDB concepts: hypertables, chunks, and batches.

Understanding hypertables, chunks, and batches

• Hypertables: In TimescaleDB, a hypertable is a PostgreSQL table that’s automatically partitioned to handle large-scale time-series data efficiently. A hypertable is the main abstraction that allows users to work with what appears to be a single table while behind the scenes, TimescaleDB manages the partitioning for performance and scalability.
• Chunks: Hypertables are divided into smaller, more manageable pieces called chunks. Each chunk is essentially a temporal partition of the hypertable, perhaps each one represents one (1) day. This partitioning allows for more efficient query performance, as operations can be limited to relevant chunks rather than the entire hypertable.

• Batches: When a hypertable chunk is compressed, it’s broken down into batches, each containing array representations of up to 1,000 rows of data into a single database row. These batches are then compressed together, reducing storage requirements, which in turn improves performance for certain operations through reduced I/O and vectorization.
• Segment_by: The optional segment_by parameter in TimescaleDB’s compression settings specifies the columns used to segment data within a chunk. When you compress a chunk each batch underneath that chunk will have data from a single segment_by value. This increases performance for queries that use the segment_by (SELECT * FROM sensors WHERE sensor_id=100), as well as maximizes compression gains by grouping values that are more likely to be similar.

The Challenge

When we first examined Ndustrial's schema, we noticed they had a unique setup: all their data was first upserted into a staging table, and then later batch written to a compressed hypertable. To maintain the most current view of their data they joined these two tables.

They originally chose this approach because the upsert (INSERT ON CONFLICT) wasn’t performing over compressed data, but as their data grew, this method broke down too (as well as being incredibly hard to maintain, expensive to query, and difficult to reason about). But why did UPSERT not work for them?

Upon inspecting the dataset, our suspicions were confirmed: they had a combination of high segment_by cardinality (some chunks had over 172K compressed batches) and a backfilling process that routinely wrote large amounts of data to compressed chunks. While TimescaleDB supports mutating compressed data it's usually used for "one-off" updates, and this was happening constantly—but could we support it?

The challenge with upserts on compressed hypertables is that the necessary B-tree indexes don't exist, as the rows involved have been compressed and are no longer available in their raw, uncompressed form. To resolve such conflicts, we must first decompress the rows that may be conflicting with the row being inserted. This way, we effectively build the index at insert time and let PostgreSQL handle the speculative insertion from there.

However, this process requires identifying the compressed data batches that could potentially contain the conflicting row for each new insertion. Let’s see how we handled this.

The Technical Solution: Index Scans for Faster Upserts

As mentioned, the performance improvement we needed came from a single simple yet crucial change: modifying the upsert process to use already existing indexes to find all batches of rows that need to be decompressed. Here’s what we did:

Index scans on compressed data

In TimescaleDB, when a chunk is compressed with the segment_by option, a B-tree index is automatically created on the segment_by columns and the batch sequence number. However, in the original implementation, the upsert process did not utilize this index. Instead, it relied on a sequential scan to locate potential conflicts, which was inefficient for high-cardinality datasets like Ndustrial’s (but was performant for most workloads we had seen previously). Why did we overlook this originally? We can't say, but we sure are glad we found the optimization!

The enhancement in this PR was to update the upsert mechanism to use the existing index whenever possible. By doing so, the system could now quickly locate the relevant compressed batches, dramatically reducing the time required to identify and resolve conflicts during an upsert. If the index is missing—an uncommon scenario unless manually removed—the system will fall back to a sequential scan. 

One of the first questions we had was, “Would this trigger a regression for low cardinality or nonsegmented compressed data?” The answer was no! The only time this approach loses to a sequential scan is when the difference is so minuscule that it can barely be measured.

The Result: 300x Faster Upsert Performance for Compressed Data

The impact of the optimization was dramatic for Ndustrial, leading to a 300x increase in upsert performance over compressed data. Here’s a breakdown of what we saw analyzing their workload:

Before optimization (v2.14.2)

Originally an INSERT ON CONFLICT, of 10,000 rows from the staging table into the hypertable, with 10 rows causing conflicts, took 427,580 ms—over seven minutes. This was because the process relied heavily on sequential scans to identify and resolve conflicts. 

Looking at the flame graph of the operation, we can see that the decompress_batches_for_insert function accounts for more than 99 % of the CPU time, of which over 99 % is from getting tuples from the heap and filtering them.

No wonder Ndustrial started investigating using the secondary table as a workaround!

After optimization (v2.16)

After updating the INSERT ON CONFLICT mechanism to use index scans to locate the blocks to uncompressed, the same operation—upserting 10,000 rows with 10 conflicts—completed in just 1,149 ms, or slightly over one second.

By using the index on the segment_by columns, the system could quickly locate the relevant compressed batches, dramatically reducing the time spent on conflict resolution and batch decompression.

The flame graphs now show a very different story, with most of the time spent in the decompress_batches_for_insert function now coming from retrieving the compression settings (a potential improvement for another time).

Conclusion

The journey to optimizing upsert performance on compressed hypertables for Ndustrial highlighted the importance of understanding and addressing key bottlenecks in database operations. 

By diving deep, analyzing the issue, and then making a seemingly simple yet impactful change—leveraging existing indexes for upserts—we were able to unlock a 300x performance improvement for high-cardinality workloads. This optimization not only resolved the immediate performance issues but also opened the door for Ndustrial to manage their data more efficiently and confidently. To learn more about other recent optimizations, check out this blog post.

Or, as our client put it, “We've definitely appreciated working closely with Timescale on this issue and all the work they've been putting into the enhancements!” A happy customer makes for a happy developer. 

If you, too, are looking for a fast, cost-saving PostgreSQL cloud database that can handle all your time series, events, real-time analytics, and vector data, topped by a Support team that is happy to roll out wider optimizations to improve your use case, try Timescale for free.