Moving attachments to filesystem doesn't free up space in DB

Infos:

  • Used Zammad version: 3.3
  • Used Zammad installation source: DEB packages
  • Operating system: Ubuntu 16.04.6 LTS
  • Browser + version: -

Expected behavior:

  • Changing the storage type from database to filesystem and running the appropriate migration script moves the data from the database to the filesystem - and frees up the space in the database

Actual behavior:

  • Attachments are stored on the filesystem, but the toast_bytes data of the store_provider_dbs still contain all attachments data as well!

Steps to reproduce the behavior:

  • In the past we stored all attachments in our Postgresql database, which grew the toast_bytes of the store_provider_dbs table to over 100 GBs and slowed down everything significantly.
    Hence we changed the mechanism to filesystem and executed the migration:
    rails> Store::File.move(‘DB’, ‘File’)

After the migration finished successfully, the attachments are written to thhe filesystem AND reside in the database.

Do I have to execute anything else to remove the attachments from the database?

HI there,

IIRC, this does not have anything to do with Zammad but with PosgtreSQL itself.
I honestly do not know if you can do the following from within Zammad using some Rails routines…

Try to do some vacuuming tasks on the DB - please see this:

Or this:

HTH,
Martin

Hi Martin,

thank you very much, this may very well be the case. I will try to clean up the database.
Thanks a lot,
Andreas

1 Like

Hi Andreas,
please keep us posted - we would like to know if this was the solution to your problem.

Thanks and regards,
Martin

I was going to do a FULL VACUUM on the table this weekend, as it might cause stress on the database. But I was greated with a pleasent surprise. The autovacuum mechanism of the database freed up the used space on its own.

So if you can wait a bit PostgreSQL will clean up itself, as long as the appropriate autovacuum configuration is enabled. Otherwise you will have to do a full vacuum manually - which might interfere with ongoing DB requests.

1 Like

this is good news - thanks for sharing your experience!

Cheers

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.