idea Better Data Management in ArcGIS Data Store in ArcGIS Enterprise Ideas

Better Data Management in ArcGIS Data Store

RyanUthoff — Mon, 06 Jan 2020 20:45:38 GMT

I would like to see some additional data management options in the ArcGIS Data Store. I'm currently noticing some data management issues that could probably be easily resolved if Esri provided some additional data management tools.

For example, the size of our ArcGIS Data Store is getting bloated and we have no way to reduce the file size. We host the ArcGIS Data Store on its own VM, but deleting the data from the hosted feature service (such as through ArcGIS Pro) does not actually release data back to the OS.

I would like to see an Esri tool that would actually release the free space back to the OS (usually accomplished by using a Full Vacuum command in PostgreSQL). I understand that deleting records from the tables will release the space back at the table level (usually through the regular vacuum command which appears to be happening, at least according to the ArcGIS Data Store logs), but I would like to extend that further to release it back to the OS. I deleted approximately 100GB worth of photos, but none of that data is being released back to the OS, and our available storage space is still going down because we're still collecting data in other tables. It is not sustainable for us to just continually adding space to the VM. We need to be able release free space back to the OS to make room for other surveys that contain photos.

I have access to the ArcGIS Data Store through pgAdmin, but would prefer to perform these actions using an "Esri approved workflow", such as using an ArcGIS Data Store command utility.

Furthermore, since I do have access to the ArcGIS Data Store through pgAdmin, I am noticing that Esri is not always deleting tables it no longer needs (such as when deleting the hosted feature service in Portal or when updating an existing Survey that requires it to "delete" and re-add the table because it is making a schema change to the survey). It leaves the old tables in the database, and just creates new tables. This is very dirty data management which results in a bloated ArcGIS Data Store which Esri gives us no option to cleanup unless if we modify the ArcGIS Data Store directly through pgAdmin which Esri does not recommend. If there could be some cleanup operation that detected orphaned tables and deleted them, that would be nice.

Re: Better Data Management in ArcGIS Data Store

LarryJahn — Thu, 05 Mar 2020 15:45:57 GMT

To piggy back off this great idea, I believe it would also be very useful to have some basic data recovery tools for Portal Data Stores. For instance, if someone accidentally deletes 100 features from a Hosted Feature Service, it's not efficient to recover the deleted data by restoring Enterprise using the webdr tool.

Re: Better Data Management in ArcGIS Data Store

1523Carver — Sat, 23 Jan 2021 01:51:41 GMT

How were you able to connect using pgAdmin? I am trying to do just that and I'm struggling with making it happen. Would you be willing to point me in the right direction?

Re: Better Data Management in ArcGIS Data Store

Henry — Thu, 10 Mar 2022 19:58:56 GMT

I've had some frustrations with DataStore as well, especially related to efficient content management.

My experience was limited to using Enterprise deployed on an Azure VM so there may be unique configurations related to that - but it seemed unnecessarily difficult to generate a list of content and how much space that content was taking up so I could audit what could be removed or relocated, not to mention weird duplications of data - specifically anything with attachments and attachment tables that appeared to balloon storage.

Never ended up installing 10.9 - so maybe there are some more tools for that purpose in that release.

Re: Better Data Management in ArcGIS Data Store

JTessier — Wed, 09 Mar 2022 16:58:50 GMT

Agree this needs some attention/tooling.

Re: Better Data Management in ArcGIS Data Store

JTessier — Mon, 25 Apr 2022 18:44:42 GMT

@ThomasEdghill would love to see this get attention in future version.

Re: Better Data Management in ArcGIS Data Store

NHannig — Mon, 25 Apr 2022 18:45:20 GMT

Agreed, this would be very helpful

Re: Better Data Management in ArcGIS Data Store

AndrewRyder1 — Mon, 25 Apr 2022 19:38:03 GMT

Some ideas for areas to improve in addition to @RyanUthoff 's initial post:

Generate report of feature service (and other applicable item types) size on disk.
- Perhaps also provide that information in portal or server manager?
Clean up data that has been orphaned for whatever reason.
Clean up domain item references. Currently if a feature class is published with a domain table name that already exists in datastore, a new domain item is created with an underscore at the end. This is evident in the AGS logs.
- ex. scenario: Datastore attempts to create Domain table name "YesNo" but 2 versions of the same table already exist, so a domain item is created named YesNo_2, there are now 3 tables (YesNo, YesNo_1, YesNo_2), referenced by 3 different feature services. If the feature service referencing YesNo is deleted, the domain item still exists in the datastore. This leads to the potential to 100's if not thousands of duplicate domains, with an unknown number of unused versions.

I'm sure there are other areas to address as well, but these would go a long way towards helping.

Re: Better Data Management in ArcGIS Data Store

Brian_Wilson — Mon, 29 Apr 2024 16:33:32 GMT

Yes @1523Carver I realize you asked this a long time ago. Maybe it will help other people

"WHY use pgAdmin at all on DataStore???", I hear you asking. You can learn more about how it works. I don't make changes, I just look in there to see what is going on. I was able to study replication on independent (not Esri controlled) PostgreSQL servers to see how it works and thereafter was able to make the Primary/Standby set up actually work. And to confirm it worked!! Up until then things were kind of broken and backwards and Esri support was unable to help. (The standby machine thought it was the primary)

You can use pgAdmin by

1. Allowing the connection to Datastore's PostgreSQL server. Do this either by editing the pg_hba.conf file or use the "allowconnection" tool provided with Datastore. Like for instance if I have pgAdmin running on a server with ip address 10.10.10.210 I would allow connections for user "sde" to every database on the Datastore.

allowconnection 10.10.10.210 sde all

FYI the allowconnection command will just edit pg_hba.conf for you, the file is in C:/arcgisdatastore/pgdata/ and it figures out the name and the IPV6 addresses so it created these lines

hostssl db_0eoas sde cc-gislicense.clatsop.co.clatsop.or.us scram-sha-256
hostssl db_0eoas sde 10.10.10.150/32 scram-sha-256
hostssl db_0eoas sde fe80:0:0:0:7c95:69a:56c0:99e7%3/128 scram-sha-256

2. Use the sde user and password when you connect. You have to get the password from Datastore with the "listadminusers.bat" tool -- on my machine,

C:\Program Files\ArcGIS\DataStore\tools\listadminusers.bat

Admin users for relational data store ds_atjwr8b3
================================================
Database Admin User.... adm_o77v5 / XXXXXXXXXXXXX
Database Repl User..... dsrepuser / XXXXXXXXXXXXXX
GDB Admin User......... sde / XXXXXXXXXXXXXX

2. Using the right connection port in pgAdmin. It's port 9876 -- I don't have pgadmin running right now so I can't do screen shots for you. The default for PostgreSQL is on 5432

BTW you can also access the PostgreSQL instance that hides inside Portal but not going there in this posting.. 🙂 Sometimes you need to access PostgreSQL in Portal to fix up things that have gotten out of sync with Server. I usually use the command line "psql" tool for this. But as with Datastore, you can learn more about how your services work if you know how the data is stored. It makes you better at your job.

Re: Better Data Management in ArcGIS Data Store

JCGuarneri — Wed, 18 Sep 2024 12:39:35 GMT

This is definitely critical functionality, and I'm not sure why it's not included out of the box.

Re: Better Data Management in ArcGIS Data Store

RachaelHarbes — Fri, 28 Mar 2025 15:48:03 GMT

@AndrewRyder1 After logging in via pgadmin. We are seeing a number of items in our Relational Data Store that is attached to hosted. When calculating # of items orphaned and size it is nearly 200 GB of data that no longer exists in the hosting server for portal. Do you happen to know a way to clean up these items from the data store?

That being said a nice enhancement to the enterprise would be some better clean up for the systems. We have noticed orphaned items in portal and the data store. We have also noticed that notebook, hosted server and additional federated servers do not always clean up after users have deleted items. It is a lot of comparison to what is in portal and what is in the systems for us and carefully backing up data and doing clean up.
ENHANCEMENT PLEASE!!! Provide a mechanism to validate what is in portal and the various other servers and data store attached to portal and identify mismatches ie. if it exists in server/data store but not portal or if it exists in portal but not server/data store. Then provide a safe way to clean up items that no longer exist in the enterprise.

Re: Better Data Management in ArcGIS Data Store

MarcusAndersson — Mon, 31 Mar 2025 09:23:29 GMT

Hi @RachaelHarbes,

As I understand the PostgreSQL-database that is running in AGE is that it only performs the "VACUUM" command on a schedule (daily?weekly? can't remember now), this makes it so that the database can never shrink in "disk-size".

To be able to reclaim space that has been previously used, i.e. stop it from bloating, you sometimes need to run the "VACUUM FULL" command on the database. This operation can "free space", but should not be done without a proper understanding of the potential benefits and drawbacks. More info on the topic can be found here: PostgreSQL: Documentation: 17: 24.1. Routine Vacuuming
The postgreSQL-documentation says that if you regularly vacuum the db then you shouldn't need to perform the vacuum full operation.. While this might be true in the abolute majority of cases, I don't think it's true for all, and not in the case of (at least ours) AGE anyways. If it's down to how AGE interacts with the db or that it's a mismatch on some other level I don't know, but I see that you do in some cases need to use the vacuum full-command on a AGE-system.

I would not recommend doing this in a prod environment without having properly tested it in a dev/sandbox first. Preferrably with an exact replica of the data you have in your production environment. You should also have the backups you need readily available and having tested the backup routines before you do this. Prepare yourselves on that the vacuum-full takes a lot longer time than the regular vacuum-command.

Link to the compatibility of the postgreSQL-versions and ArcGIS Enterprise: FAQ: What PostgreSQL Database Versions Are Supported by ArcGIS Enterprise and ArcGIS Pro?

I'm by no means any postgreSQL-expert, but these are at least some findings I've done when dealing with similar issues/thoughts that you have. So take it for what it is and good luck 🙂

BR,
Marcus

Re: Better Data Management in ArcGIS Data Store

RachaelHarbes — Mon, 31 Mar 2025 12:43:31 GMT

@MarcusAndersson Thank you for the information on the "VACUUM". I was looking for a more robust clean-up of data which is no longer in the portal/hosting server, but remaining in the data store. Do you happen to know of a safe way to remove the orphaned layers/tables?

Re: Better Data Management in ArcGIS Data Store

MarcusAndersson — Tue, 01 Apr 2025 09:18:02 GMT

@RachaelHarbes
Yes. That would be the "Vacuum full", then.

BR,
Marcus

Re: Better Data Management in ArcGIS Data Store

RachaelHarbes — Tue, 01 Apr 2025 14:59:54 GMT

@MarcusAndersson VACUUM scans a table, marking tuples that are no longer needed as free space so that they can be overwritten by newly inserted or updated data.

How would it know what tables/feature layers are orphaned and delete them from the database? These tables still exist in the database, but do not exist in hosted/portal. These tables are still valid and exist as functioning tables from a database perspective. Is this a special function ESRI does to compare?

Re: Better Data Management in ArcGIS Data Store

lvargas — Wed, 20 May 2026 18:17:03 GMT

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