Primary key being INT is not big enough

[lzap]

Hey,

the major issue here is not BIG vs BIGINT, but the way we store reports. These should have been stored in text/blob field rather than per-line which is extremely slow and it actually put huge stress on the SQL database. I’ve analyzed this here:

RFC: Optimized reports storage

Hello, I have a preliminary version of patch which speeds up report import by 24x (not 24 % but 24 times) while the data consumed in SQL is cut at half. Let me first described how I measured it. I’ve used a VM (6GB RAM, 2 vCPUs) with Foreman 1.23 running with default installation parameters (PostgreSQL) on CentOS 7.7. In the Rails console I created a small script that tries to create reports with 100 lines. Half of it were same lines and half of it were unique lines. I’ve used benchmark-ips gem…

We haven’t implemented the changes yet tho. In the meantime, we bumped the primary keys for the most exposed tables (logs, fact_values) sometime in 2017:

fixes #13810 - increase value range for id colums

You can easily backport this change into your Foreman without upgrading it up for now, just copy the file and perform migration. This should not break your migration when you choose to upgrade later on.