Primary key being INT is not big enough

[DavidPinAZ]

Problem: We have datacenters with upwards of 25k instances each. We recently ran into an issue where Foreman couldn’t add data to the database (PostgreSQL 9.2) because we exhausted the digits for the primary key in the logs table. We had to change the data type to BIGINT from INT in order to keep computing.

Foreman and Proxy versions: 1.16 - Yes, I know it’s old, we’re working on it.

Distribution and version: OEL7.5

Other relevant data:
My questions are:

1. When we do the upgrades to newer Foreman versions, will having that table be a different data type cause problems with schema updates? If so, which files would I have to modify (change INT to BIGINT) to not cause schema update problems?

2. Has the data type for some of these tables been changed to BIGINT in newer versions of Foreman?

3. If the problem isn’t the data type, but something else causing some sort of cleaning/maintenance to work, what would it be so I can start looking into it?

Thanks!

[areyus]

I know this is not a final answer to your questions, but I just took a look at our production database (currently running 1.22), and I can confirm that the logs table’s id field (the pkey) has been changed to bigint somewhere on the way.
For the questions regarding upgrade problems, someone working on the database migrations themselfes is probably more suited to answer those.

Regards

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