Should we make some primary keys bigint?

[hmpf]

After running "VACUUM ANALYZE" on a copy of a prod site db to generate some stats I get:

argus=> SELECT relname, n_live_tup
FROM pg_stat_user_tables where n_live_tup >= 10000;
              relname               | n_live_tup 
------------------------------------+------------
 argus_incident_incident            |      52833
 argus_incident_acknowledgement     |      48688
 argus_incident_incidenttagrelation |     415072
 argus_incident_event               |     141146
 argus_incident_tag                 |      14362
(5 rows)

These will grow forever and are AFAICS the only ones relevant for bigints.

argus_incident_incidenttagrelation is a many to many between argus_incident_incident and argus_incident_tag using an explicit through-table which means it has an implicit primary key that will never be used for anything, it is possibly
e a good test case for experimentation.

This database is small enough that it might be possible to migrate in one go without doing it at scale.

[hmpf]

Altering the pk's of argus_incident_incident, argus_incident_event and argus_incident_tag to bigint with a bog-standard migration:

% time python manage.py migrate argus_incident
..

Operations to perform:
  Apply all migrations: argus_incident
Running migrations:
  Applying argus_incident.0009_alter_event_id_alter_incident_id_alter_tag_id... OK
python manage.py migrate argus_incident  0,54s user 0,06s system 24% cpu 2,436 total

(This also alters argus_incident_incidenttagrelation and all other implicitly created tables with foreign keys to the above.)

[lunkwill42]

As I see it, any kind of log-style table that will only ever grow should really have bigint primary keys. Argus is still a young project, and there is no Argus database that will see primary key wraparounds in a few years - but that also means converting existing databases with simple migrations should be pretty fast.

The only place I've seen a real-world wrap-around that became a problem was a customer NAV installation that had been running for years and years, where one of the most updated log-style tables had to have its schema changed in production from int to bigint because it wrapped around and caused a bombardment of error messages (this was only last year, IIRC).

[hmpf]

NAV doesn't use django's migration system but just for the record there was an interesting talk about how to go from int to bigint in YUUUUGE systems at Djangocon Europe 2025. Remind me to add a link to the public video here once it is out.

[hmpf]

Video: https://djangotv.com/videos/djangocon-europe/2025/djangocon-eu25-turn-back-timeconverting-integer-fields-to-bigint-using-django-migrations-at-scale/

[hmpf]

.. and sounds like step 1 in getting Argus 2 out is a 1.37 or so to just do this int to bigint migration.. on it.

[hmpf]

This has been done, closing the discussion.