How do I add a primary key to a huge table without a primary key

Question

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()
Indexes:
    "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?

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