The size of the database explodes when PostgreSQL uses the archive log.

[mdc-webdb]

Whenever the feeds are updated,
the entire database is rewritten instead of just the changes. This leads to an extreme use of space on the database server if the wal archive mode is used. However, this is a prerequisite for incremental database backups.

[mdc-webdb]

In my case the database itself will be 3GB, but when the updater runs(and find updates), on every run 17GB wal archive logs are generated.

[4D617274696E]

We are currently investigating a solution for this problem, no ETA yet.

[timopollmeier]

I've created #2355 to limit the size of certain INSERT queries that were getting very large and there will be another patch release of gvmd once this is merged.

[mdc-webdb]

Hi @timopollmeier,
I have tested it, but it is making it much worse.
Now with feed update of today, 27G of WAL archive logs are generated for an database with an size of 11GB.

SELECT pg_size_pretty( pg_database_size('gvmd') );
 pg_size_pretty 
----------------
 11 GB

After the feed update

du -h --max-depth=1 /var/lib/pgsql/wal_archive
27G     /var/lib/pgsql/wal_archive

Before:

du -h --max-depth=1 /var/lib/pgsql/wal_archive
30M     /var/lib/pgsql/wal_archive

[bjoernricks]

Hi @mdc-webdb, as we don't use the wal archive mode (at least not at the moment) we don't have a solution for your issue. If you have ideas on how to improve the situation, let us know in this issue. Maybe we can take another look into it again. Until then I am sorry but we wont be able to fix it.

[mdc-webdb]

Hi @bjoernricks,
I think that before a record is written to the database during the update, it must first be checked whether it already exists with the same content. This is because an SQL update statement is always executed ‘stupidly’, even if what is written is already in the database. This means that a transaction is created each time, which must be logged.