Backup Size increasing after deleting attachments of hosted feature service

Question

Hi All,

We are on ArcGIS Enterprise 11.3

We have a hosted feature service in which data is coming through Survey 123. The data comprises of records as well as attachments (images). We also have a backup scheduled in task scheduler which takes the backup of relational datastore daily.

Using python script  we have deleted a large number of images however, the size of the new backup file that is getting created is still increasing.

Just wanted to know if it's an expected behavior

lvargas · Answer

ENH-000147259

Add a command line utility to perform full vacuum.

https://support.esri.com/es-es/enhancement/add-a-command-line-utility-to-perform-full-vacuum-enh-000147259

Added in 11.4

https://content.esri.com/support/downloads/other_/114-issuesaddressedlist_032025.pdf

Syntax

maintaindatastore --store {relational | object} [--prompt {yes|no}]

https://doc.esri.com/en/arcgis-enterprise/latest/administer/dsu-maintaindatastore.html?pivots=os-windows

wizgis · Answer

Thanks @RyanUthoff for the detailed explanation and sharing the ideas link with us.

ryanuthoff · Answer

We encounter this exact same "problem." We have a few hundred surveys, all of which have photos in them which consume a lot of storage space on the server. And unfortunately, just "deleting" the photos through Esri isn't good enough.

Under the hood, the ArcGIS Data Store is just a PostgreSQL DB. And for surveys (or any hosted feature services with sync enabled), Esri typically treats them as a "versioned" dataset, where you have your base tables along with the associated versioned tables.

When interacting with the data through Esri, it's always using the versioned view. So for example, if you're viewing the data in ArcGIS Pro or in Portal, you're looking at the versioned view of the dataset. And any modifications you make, including deleting the data, get reflected in the view, but not necessarily the underlying base table. If I delete a record in Pro, that will be reflected in the view, but not the underlying base table.

This is where it becomes a problem, because it gives you the impression that the data is deleted, but it actually isn't. Unfortunately, this is where it becomes difficult to fix the problem because Esri doesn't really have any out of the box tools to deal with this.

Several years ago at the UC, someone from Esri told me that you can disable sync and then re-enable sync capabilities of the hosted feature service, and that would get the changes you made in Pro/Portal reflected in the underlying base tables and that would truly "delete" all the records. While I believe that is technically correct, it also corrupted the hosted feature service for us (I think because we had a huge feature service and it timed out or something). So we don't use that method.

The alternative method is connecting directly to the Data Store DB directly through PGAdmin. From here, you can delete the records in the base table.

However, deleting the records from the table does not actually decrease the table size or give the space back to the OS. The final step you have to do is run a vacuum full command on that table in PG Admin. What that does is actually free the space back up to the OS, and will also result in smaller Data Store backup sizes.

Edit: Just to add, I created an ArcGIS Idea several years ago about this.