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:
- Does this make sense?
- 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?