PostgreSQL BTREE descending but insert ascending

Question

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?

Answer 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.

Answer 2

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