2

I have a (partitioned) table in a PostgreSQL that stores events. It has the following properties:

  • rows are rarely deleteed
  • rows are frequently inserted (~ 100-200 per second, every second on average)
  • new rows always have the current timestamp (we never insert old events)

I want all queries to return items in descending order (most recent to oldest).

Therefore I want all indexes to be b-tree's ending with the "timestamp" column in descending order.

CREATE INDEX events_type ON events USING BTREE ("type", "timestamp" DESC)

My questions are:

  1. Does this make sense?
  2. Would inserts be MORE efficient if the "timestamp" field in BTREE indexes was in ASCending order? Will using ..."timestamp" DESC)... negatively impact performance versus ..."timestamp" ASC)... when frequently inserting newer items?

2 Answers 2

1

Are you adding the "timestamp" DESC to the index so that it can support an ORDER BY "timestamp" DESC clause added to each query? Or because you think this will make it automatically return rows in that order without needing to specify the ORDER clause? If the first case, the DESC is not needed as PostgreSQL can follow the index in either direction. In the 2nd case, that might kind of work some of the time, but is not reliable.

But what adding the DESC will do is make your index bloated. When an index leaf page is full and needs to be split and the row to be inserted is beyond the current end of the page, it splits the page asymmetrically, Leaving the old page still full, and the new page empty to be filled in the future. This optimization does not apply to the at the other end, so in that case the page is split about 50/50. One of those pages will never get insert into again, so your index will consist almost entirely of half-full pages.

1

It does not matter whatsoever. PostgreSQL can read indexes in both directions with the same speed.

2
  • Thanks for your response. Can PostgreSQL also write to the indexes at the same speed?
    – nick314
    Commented Nov 30, 2022 at 18:58
  • I believe so. I know that there are special provisions for inserting at the upper end, and I cannot imagine that the lower end would be treated differently (but I am too lazy to look at the code). You could verify it with a quick benchmark. Commented Dec 1, 2022 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.