Puppet Community

is there is way to wipe old facts manually?

<https://forge.puppet.com/modules/puppetlabs/pe_databases/readme|https://forge.puppet.com/modules/puppetlabs/pe_databases/readme> has some code to configure pg_repack. that will clean up dead code. worth a try

puppetdb has no history of facts. when a new factset is inserted, the old records are updated

yeah i saw that, but not sure that might help in this specific case

so a) your facts are huuuuge or b) you have a huge amount of dead data autovacuum didnt delete

you can also check your nodes, run puppet facts show and check for unused information and disable them

i think that’s b) because the DB size keeps growing constantly

and if you have arrays or hashes as facts, ensure that their ordering doesnt change

then install pg_repack and give it a try, check if you have IO bottlenecks and upgrade storage/ram, upgrade postgresql to 13, run pg_tune to optimize the postgresql.conf

and I recommend starting pg_repack in a tmjx session or as systemd job, ot might run some time and in the worst comdition needs free disk space as big as the table it runs on

and please report back, I would like to know if it helped

```kenyon@beta ~ % cat /tmp/test.yaml
? /var/lib/kubelet/pods/d77877b3-f32a-4318-bec1-1f3cc1860b2c/volumes/kubernetes.io~local-volume/pvc-b2fd0dbc-000a-40c6-8f1c-71562ebee94c
: available: 825.05 GiB
  available_bytes: 885885857792

kenyon@beta ~ % irb
irb(main):001:0&gt; require 'yaml'
=&gt; true

irb(main):002:0&gt; YAML.load_file('/tmp/test.yaml')
=&gt; {"/var/lib/kubelet/pods/d77877b3-f32a-4318-bec1-1f3cc1860b2c/volumes/kubernetes.io~local-volume/pvc-b2fd0dbc-000a-40c6-8f1c-71562ebee94c"=&gt;{"available"=&gt;"825.05 GiB", "available_bytes"=&gt;885885857792}}```

<@U1PC2RAH0> and you know what? it did the trick
``` public.factsets                                          | 118 MB```


hope it didnt break anything :stuck_out_tongue:

Puppet fact data can be "bursty". Autovaccum can't help with a set of facts that report large values on one run and then stop. Space has to be allocated to hold the large dataset, even if ends up being transient.

All autovaccuum can do is ensure space is re-used once allocated.

I think autovacuum also reclaims disk space, doesnt  it?

No, it just marks disk space allocated to the database "heap" as available for re-use.

The only time autovacuum can free space is if the 8 kB page file at the end of the heap, or a run of consecutive page files that lead to the end end up holding rows that have all been superseded. In that case, autovacuum can remove those page files instead of marking them as entirely empty.

However, that's a vanishingly rare situation as there is usually at least one live database row in each page file.

Ideally, autovacuum holds the heap at an equilibrium where the existing page files are sufficient to hold the live table data + older data that may still be needed by in-progress queries + any churn from inserts and updates that have accumulated since the last autovacuum run.

So, the three scenarios where table size starts to grow are:

• The size of the live data set increases, perhaps due to facts, catalogs, or reports temporarily being larger than normal.
• The age of the oldest in-progress query starts increasing.
• The time required to autovacuum the table becomes larger, so more churn builds up between runs.
Usually, one of the first two scenarios occurs. Time required for autovacuum to process the table increases with table size, so the third scenario item usually shows up as a "death spiral" resulting from one of the first two pushing the system past a tipping point.

yep. I saw scenario 3 often in bigger setups, bevause the default autovacuum settings are quite conservative to ensure it doesnt eat up too much IO

Yeah. PE had a problem with that for a while because we increased the number of autovacuum workers so that more tables would be processed concurrently thinking that each worker had it's own I/O "budget".

But, the I/O budget is actually shared by all workers, so that tuning attempt ended up backfiring by slowing autovacuum progress down across all tables.

and with all the noise from the node_encrypt problem there was a lot going on in the database

you can always do `puppet facts upload` I think to reseed the database with facts

well i tried to apply a catalogue which actually uses facts from puppetdb and nothing broke, so looks like all needed stuff is still there

is it PE ? This helps
```puppet_enterprise::enable_database_maintenance: true```

I can highly recommend the awesome #postgresql room at libera irc. they can explain all the autovacuum options and tune your config way better than I can

```# cat /etc/systemd/system/pe_databases-facts.service
[Unit]
Description=Service to repack PE database tables
Wants=pe_databases-facts.timer

[Service]
User=pe-postgres
Group=pe-postgres
Type=oneshot
ExecStart=/opt/puppetlabs/server/apps/postgresql/11/bin/pg_repack -d pe-puppetdb --jobs 2 -t factsets -t fact_paths```