Nominatim - Postgres DB growing very fast in size when doing the daily updates

[jhrvalente]

I have done a full import with the planet from OSM website and scheduled the updates to run daily on a cronjob.

But I have noticed that the disk usage is growing very fast in size on a daily basis. When running the command df -h, I have noticed that every day the disk size grows about 1GB. Not sure if this command does some round up, but even so this size seems very huge.

I have a disk with 1TB free, but this would mean that the disk would be full in about 3 years.

I have tried to inspect the folders under /var/lib/postgresql/<version>/<cluster> and it seems that the folders that concur to this size increase are the folders pg_wal and base/16390.

The folder base/16390 has many files with 1GB each and the folder pg_wal has about 40 something files of 16MB each.

I don't know which files are safe to remove or if there are some configs for the postgresql.conf file that would prevent this huge increase in size each day.

Also don't know if this has to do with some backups or logs that postgres does by default, but I would like to also reduce those backups and logs to a minimum.

Any help on this would be appreciated.

Thanks in advance.

[mtmail]

Can you tell us the postgresql version, when you imported (how many number of days ago) and the number and size of files in the pg_wal directory?

After the import did you change the fsync configuration setting back to on ? (https://nominatim.org/release-docs/develop/admin/Installation/ "Don't forget to re-enable them after the initial import or you risk database corruption.")

[jhrvalente]

The postgresql version is 14. I have imported it on 15th of January more or less.
In pg_wal, accordingly to this command:

ls -lrth /var/lib/postgresql/14/planet_full/pg_wal/ | wc -l
66 

I have 65 files, each with the size of 16MB.

fsync is set to on.

[mtmail]

OK, so the WAL files (write-ahead-log) aren't the issue. There would've been a couple of Postgresql configuration settings to limit both file size and number of files.

[jhrvalente]

But 65 * 16 MB = 1040 MB, so this seems to be contributing to this huge increase in size of the disk.
I have autovacuum set to on, but I've noticed that the pg_stat_user_tables, has not any entry.
With autovacuum running I would also expect to see some entries in that view.

[lonvia]

Postgres produces quite a bit of index bloat when updating, this can't be avoided. The rate will slow down a bit with time but the growth never stops completely. Here is a graph from the planet import for our servers with a reimport in August. You can rebuild the biggest indexes from time to time to bring down the size. Just don't leave it until the last minute because you need at least the amount of free space of the size of the index.

And then there is of course the natural growth. OSM grows by 10% or so every year, so your Nominatim database will grow too.

[jhrvalente]

Yes, but 1GB per day is very much. It corresponds to more than 300GB per year, which is more than 30% of the size it is currently occupying, not the expected 10%.

[lonvia]

Please do open a new discussion for new issues.