Why is the time index default DESC?

TimescaleDB and PostgreSQL
1

Hello all,

Why is the time index default DESC?

Reading this post https://dba.stackexchange.com/questions/340021/should-a-time-index-be-in-ascending-or-descending-order with the answer

“You should definitely create the index in ascending order, unless you insert the rows in descending order. […] But PostgreSQL’s B-tree indexes are optimized for inserting values at the upper end, while there is no such optimization for the lower end. So if you are inserting time series data where the timestamps generally keep increasing, an ascending index is better.”

I would have assumed, that the index is created as ASC by default.

I’d like to understand the reason behind the decision DESC as default. As I couldn’t find anything regarding that topic I’d like to ask if anybody please can explain it to me? Thanks.

Best regards,
Paul

2

Hi @lrpaul, It’s recommended because of the nature of time series data, most frequent queries are generally related to getting the latest data point.

I think this is also the trade-off of optimizing the system for readings.

Check this blog and check that it also has this recommendation:
https://www.timescale.com/blog/select-the-most-recent-record-of-many-items-with-postgresql

Anyway, I’ll share this internally to see if we get more Postgresql hackers involved in this conversation.

3

Hi Paul,

After discussing this internally with the team, I wanted to share some insights about the ASC vs DESC index ordering for time-series data.

The choice between ASC and DESC actually presents an interesting performance tradeoff:

For INSERT operations, an ascending (ASC) index typically performs better since PostgreSQL B-tree indexes are optimized for inserting values at the upper end. There’s less branching and bloat when growing the tree, which is particularly relevant for time-series data where timestamps generally increase.

However, for QUERY operations, especially when frequently accessing recent data (which is a common pattern in time-series workloads), a descending (DESC) index can be advantageous. Our internal benchmarks have shown that scanning against the index order can be about 10-30% slower, which is why we’ve maintained DESC as the default.

The key is to consider your specific workload:

  • If you’re write-heavy with sequential inserts, ASC might be better
  • If you’re read-heavy and frequently query recent data, DESC might be preferable

You can always override the default behavior based on your specific needs and access patterns.

Hope this helps explain the reasoning behind our default choice! Let me know if you have any other questions.

4

Hi Jônatas,

Thanks for these interesting insights. This makes sense now.
This question and indeed another one came to my mind when I was testing the reorder_chunk() / add_reorder_policy() feature. I’ll open an own post for it.

1 Like