Postgres killing my RAM

Problem:
We are handling around 6k nodes with around 30 custom facts. The database is growing bigger than expected, whereas the table m_hostfacts seems to be the one showing something as huge as 2664429 relnames. Not sure if that is related, but the process to add new nodes takes several minutes. Server has 16 Gb of ram and only 150-250 available.

Expected outcome:
Is that table really need to be that big? Is it normal for new nodes to take minutes to be showed on foreman gui?

Foreman and Proxy versions:
Foreman: 1.12.4
Postgres: 11.2
PhusionPAssenger: 4.0.18

Other relevant data:
We got a domain change in the nodes, all of our nodes need to register again, but postgres database seems to have troubles. Foreman GUI and database are on different servers.

Can you double check Foreman version? This is way too old version! We have been working really hard on optimizing facts, but generally - yes it will grow. It will grow a lot, we store all info in Postgres including facts, reports or audits. It’s all stored in normal form with many associations, therefore many indices are needed so expect a lot of stress to your Postgres.

More modern versions of Foreman (1.17+ I think) has a fact filtering capability, you can filter out some uninteresting facts which can dramatically help to decrease load. For example if you have many network cards (e.g. containers or hypervisors can have hundreds of virtual bridge interfaces) this can create a lot of records.

Please consider sharing with us more details from your deployment: Tutorial: Hardware requirements for Foreman deployments

The problem was that another dev decided to manually add a trigger to copy the hosts facts when the host was created and when the host was modified. That caused the overload issues.

1 Like