I have a huge table (millions of records) that keeps history of events.

Table is automatically populated, with newer records added all the time. The field timestamp refers to when a row was added. Records are never UPDATEd.

The most common queries (all too slow!):

  • latest record for a given ip
  • latest record for a given mac
  • latest 100 records for a given ip
  • latest 100 records for a given mac
  • all records for a given IP within the last X days

Here's the table description:

  Column   |            Type             | Nullable | Default
 event     | text                        | not null |
 ip        | inet                        | not null |
 mac       | macaddr8                    | not null |
 timestamp | timestamp without time zone | not null | now()
    "ip_idx" btree (ip)
    "mac_idx" btree (mac)
    "time_idx" btree ("timestamp")
    "timestamp_ip_event_key" UNIQUE CONSTRAINT, btree ("timestamp", ip, event)

I'm thinking about adding a primary key row_id with auto-increment.

Hopefully, that would speed up the queries where the actual value of timestamp doesn't really matter.

Also, it will make the table easier to use with Django, which prefers tables to have numeric primary key.

The script that populates the table relies on the column order, so the new column row_id should be at the end.

For existing rows, row_id should be in the order of ascending timestamp.

This last requirement is puzzling me -- how do I write the ALTER TABLE to ensure that the order of existing records by row_id will be the same as with timestamp?

  • There's no "order of existing records" in a table; rows are ordered by a query if ORDER BY is specified. And why do you expect your proposed surrogate primary key to improve performance of queries based on time? Have you tried indexes on timestamp, ip and timestamp, mac?
    – mustaccio
    Commented Aug 16, 2023 at 19:55
  • The purpose of a Primary Key is for data integrity, not performance. Indexes improve performance, and the data type of the key field of those indexes are negligible when it comes to for performance. Millions of rows is not that much, so if your queries are having performance problems, it's likely an index or query tuning problem. Without seeing the example slow queries and their EXPLAIN ANALYZEs, there's not much else that can be advised specifically.
    – J.D.
    Commented Aug 16, 2023 at 19:56
  • You don't need "time_idx" index because you already have "timestamp" field as first in "timestamp_ip_event_key"
    – Sahap Asci
    Commented Aug 16, 2023 at 20:23
  • @mustaccio by "order of existing records" I meant I was comparing the ordering by newly added primary key vs ordering by timestamp Commented Aug 17, 2023 at 18:32
  • @J.D. it's not just performance. There are other benefits of adding a primary key. Commented Aug 17, 2023 at 18:34

1 Answer 1


There is no reason to think an artificial primary key will speed up those queries. What really need are indexes on (ip, timestamp) and (mac, timestamp).

  • There are other benefits to having a primary key. Commented Aug 17, 2023 at 18:22
  • 1
    Sure. I was responding to your post which mentioned these performance issues, not to something else you could have posted but didn't.
    – jjanes
    Commented Aug 17, 2023 at 19:11

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.