How We Fixed Long-Running PostgreSQL now( ) Queries (and Made Them Lightning Fast)

Sven Klemm -

It was just another regular Wednesday in our home offices when we received a question in the forum about a query with the Postgres now() function. A TimescaleDB user with dozens of tables of IoT data reported a slow degradation in query performance and a creeping server CPU usage. After struggling with the issue, they turned to our community for help.

via GIPHY

That same question came up in our forum, Community Slack, and support more often than we’d like. We could relate to this particular pain point because we also struggled with it in partitioned vanilla PostgreSQL. After a closer look at the user’s query, we found the usual suspect: the issue of high planning time in the presence of many chunks—in Timescale slang, chunks are data partitions within a table—and in a query using a rather common function: now().

Usually, the problem with these queries is that the chunk exclusion happens late. Chunk exclusion is what happens when some data partitions are not even considered during the query to speed up the process. The logic is simple: the fewer data a query has to go through, the faster it is.

However, the problem is that now(), similarly to other stable functions in PostgreSQL, is not considered during plan-time chunk exclusion, those precious moments in which your machine is trying to find the quickest way to execute your query while excluding some of your data partitions to further speed up the process. So, your chunks are only excluded later, at execution time, which results in higher plan time—and yes, you guessed it—slower performance.

Until now, every time this issue popped up, we knew what to do. We had written a wrapper function, marked as immutable, that would call the now() function and whose only purpose was to add the immutable marking so that PostgreSQL would consider it earlier during plan-time chunk exclusion, thus improving query performance.

Well, not anymore.

Today, we’re announcing the optimization of the now() function with the release of TimescaleDB 2.7, which solves this problem by natively performing as our previous workaround.

In this blog post, we’ll look at the basics of the now() function, explain how it works in vanilla PostgreSQL and our previous TimescaleDB version, and wrap everything up with a description of our optimization, which evaluates now()expressions during plan-time chunk exclusion, significantly reducing planning time. Finally, we include a performance comparison that will blow you away (all we can say for now is “more than 400 times faster”).

via GIPHY

If you are already a TimescaleDB user, check out our docs for instructions on how to upgrade. If you are using Timescale, upgrades are automatic, so all you need to do is sit back and enjoy this very fast ride! (New to Timescale? You can start a free 30-day trial, no credit card required.)

now( ) in Vanilla PostgreSQL

Queries with now() expressions are common in time-series data to retrieve readings of the last five minutes, three hours, three days, or other time intervals. In sum, now() is a function that returns the current time or, more accurately, the start time of the current transaction. These queries usually only need data from the most recent partition in a hypertable, also called chunk.

A query to retrieve readings from the last five minutes could look like this:

SELECT * FROM hypertable WHERE time > now() - interval ‘5 minutes’;

To understand our users' slowdown, it’s vital to know that constraints in PostgreSQL can be constified at different stages in the planning process. The problem with now() is that it can only be constified during execution because the planning and execution times may differ.

Since now() is a stable function, it’s not considered for plan-time constraint exclusion; therefore, all chunks will have to be part of the planning process. For hypertables with many chunks, this query's total execution time is often dominated by planning time, resulting in poor query performance.

If we dig a little deeper with the EXPLAIN output, we can see that all chunks of the hypertable are part of the plan, painfully increasing it.

 Append  (cost=0.00..1118.94 rows=1097 width=20)
   ->  Seq Scan on _hyper_3_38356_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())
   ->  Seq Scan on _hyper_3_38357_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())
   ->  Seq Scan on _hyper_3_38358_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())
   ->  Seq Scan on _hyper_3_38359_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())
   ->  Seq Scan on _hyper_3_38360_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())
   ->  Seq Scan on _hyper_3_38361_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())

We had to do something to improve this, and so we did.

now( ) in TimescaleDB

As proud builders on top of PostgreSQL, we wanted to come up with a solution. So in previous versions of TimescaleDB, we did not use the now() expression for plan-time constraint exclusion.

In turn, we implemented constraint exclusion at execution time in a bid to improve query performance. If you want to learn more about how we did this, check out this blog post, which offers a detailed behind-the-scenes explanation of what happens when you execute a query in PostgreSQL.

While the resulting plan does look much slimmer than the original, all the chunks were still considered during planning and removed only during execution. So, even though the resulting plan looks very different (look at those 1,096 excluded chunks), the effort is very similar to the vanilla PostgreSQL plan.

Custom Scan (ChunkAppend) on metrics1k  (cost=0.00..1113.45 rows=1097 width=20)
   Chunks excluded during startup: 1096
   ->  Seq Scan on _hyper_3_39453_chunk  (cost=0.00..1.01 rows=1 width=20)
         Filter: ("time" > now())

Close, but not good enough.

now( ) We're Talking

With our latest release, TimescaleDB 2.7, we approached things differently, adding an optimization that would allow the evaluation of now() expressions during plan-time chunk exclusion.

Looking at the root of the problem, the reason why now() would not be correct is due to prepared statements. If you execute now() but only use that value in a transaction half an hour later, the value does not reflect the current time—now()—anymore.

However, it will still hold true for certain expressions even as time goes by. For example, time >= now() will be true at this moment, in 5 minutes and 10 hours. So, when optimizing this, we looked for expressions that held as time passed and used those during plan-time exclusion.

The initial implementation of this feature works for intervals of hours, minutes, and seconds (e.g., now() - ‘1 hour’).

As you can see from the EXPLAIN output, chunks are no longer excluded during execution. The exclusion happens earlier, during planning, speeding up the query. Success!

 Custom Scan (ChunkAppend) on metrics1k  (cost=0.00..1.02 rows=1 width=20)
   Chunks excluded during startup: 0
   ->  Seq Scan on _hyper_3_39453_chunk  (cost=0.00..1.02 rows=1 width=20)
         Filter: (("time" > '2022-05-24 12:41:31.266968+02'::timestamp with time zone) AND ("time" > now()))

In the next TimescaleDB version, 2.8, we are removing the initial limitations of the now() optimization, making it also available in intervals of months and years. This means that you will be able to make the most of this improvement in a wider range of situations, as any time > now() - Intervalexpression will be usable during plan-time chunk exclusion. 

 Custom Scan (ChunkAppend) on metrics1k  (cost=0.00..1.02 rows=1 width=20)
   Chunks excluded during startup: 0
   ->  Seq Scan on _hyper_3_39453_chunk  (cost=0.00..1.02 rows=1 width=20)
         Filter: ("time" > now())

This code is already committed in our GitHub repo, and will be available shortly.

How Does It Work?

But how did we make this current version happen? The optimization works by rewriting the constraint. For example: 

time > now() - INTERVAL ‘5 min’

turns into

(("time" > (now() - '00:05:00'::interval)) AND ("time" > '2022-06-10 09:58:04.224996+02'::timestamp with time zone))

This means that the constified part of the constraint will be used during plan-time chunk exclusion. And, assuming that time only moves forward, the result will still be correct even in the presence of prepared statements, as the original constraint is ANDed with the constified value.

Rewriting the constraint makes the constified value available to plan-time constraint exclusion, leading to massive reductions in planning time, especially in the presence of many chunks.

So we know that this translates into faster queries. But how fast?

Performance Comparison—now( ) That Is Fast!

As shown in our table, the optimization’s performance improvement scales with the total number of chunks in the hypertables. The more data partitions you’re dealing with, the more you’ll notice the speed improvement—up to 401x faster in TimescaleDB 2.7 for a total of 20,000 chunks when compared to the previous version.

now()that is fast. 🔥

The table lists the total execution time of the query (at the beginning of the post) on hypertables with a different number of chunks

now( ) Go Try It

There are few things more satisfying for a developer than solving a problem for your users, especially a recurring one. Achieving such performance optimization is just the icing on the cake.

If you want to experience the lightning-fast performance of PostgreSQL now()queries for yourself, TimescaleDB 2.7 is available for Timescale and self-managed TimescaleDB.

Once you’re using TimescaleDB, connect with us! You can find us in our Community Slack and the Timescale Community Forum. We’ll be more than happy to answer any question on query performance improvements, TimescaleDB, PostgreSQL, or other time-series issues.