Feedback on the Prune Branch History Tool

[PierreloupDucroix]

Hello,

I'd like to share my experience with the prune branch history tool.

After a year of using Utility Network in production, we've accumulated a lot of versions and branch versioning history.

I've therefore started a process to delete versions (which will be automated in the future) and prune the history.

I have several observations and questions.

First, here are some raw statistics.
Total processing time: 8 hours and 34 minutes.

table_name	records_count  before_prune	records_count  after_prune	duration_logs  postgre (min)	delete  count	delete  stats	row_per  min_ratio
un_6_eidmappings	7983639	957829	68	7025810	88%	103932
electricjunctionobject	527421	157201	20	370220	70%	18831
un_6_associations	473888	368256	14	105632	22%	7819
un_6_dirtyareas	425856	110420	6	315436	74%	49133
electricdevice	397987	211428	34	186559	47%	5502
un_6_subnetworks	385461	138946	167	246515	64%	1477
electricsubnetline	374137	138036	200	236101	63%	1183
electricline	241855	132954	3	108901	45%	40334
un_6_junctions	223761	45008	0	178753	80%	940805
structurejunction	151429	127753	1	23676	16%	20952
un_6_aggregations	148090	55346	0	92744	63%	713415
un_6_systemjunctions	137440	17326	1	120114	87%	176638
electricjunction	103885	76691	1	27194	26%	28625
un_6_edges	98835	19959	0	78876	80%	985950

1- We can see that the main table to prune is the eidmappings table, which isn't unusual, given that I've had to disable/enable the topology several times to improve the data model, which is known to increase the size of this table.

2- We can see that the time spent on certain tables compared to the number of rows deleted is very long, mainly subnetworks and subnetline. These two tables alone account for 70% of the processing time, with a very low ratio of rows processed. Is this normal?

3- It's not visible in the statistics, but the size of the database tables doesn't seem to have decreased. For example, the eidmapping table is still around 2GB after processing, just like before. Are there any additional processes that should be run on the database to reduce the allocated space (Vacuum, for example)?

4- Surprisingly, I first pruned one month of branch history (January 2025, no previous history), then one year (all of 2025), and the time spent was roughly the same (about 8 hours total, with 70% of that time spent on the two tables, subnet and subnetline).

Context : Pro 3.5.7, AGE and SDE 11.5, UN v6

[RobertKrisher]

Depending on the database you're using you may need to run something like a prune or vacuum if you want to reclaim the disk space associated with deleted records. If you are pruning on a regular basis, then doing this after every prune isn't necessary, since you'll need to reallocate the same disk space. If you've hit some kind of unreasonable high water mark, then reclaiming the disk space may be worth while.

[TomBrown]

 Pierreloup thank you for your post and this level of detail you've captured.

My first question, I'm surprised you feel as if you need to prune at this point - there really isn't much historical data (row counts for these tables are very small). Are you pruning with the objective to reclaim storage or the need to address performance issues. If its performance related (hopefully not), it would be valuable to identify what is causing the performance issue - any specific queries?

Why is the prune spending so much time on those two specific tables... we would need to capture and understand the queries access path which would highlight where the time is being spent. Are you working on Postgres?

If you're able to provide a copy of the database backup to Esri support, we would be more then willing to test and debug your specific issue - this would be very valuable.

Thank you and we look forward to helping you and address this issue - its definitely not our expectation that it would take this long to execute.

Thomas Brown
ESRI

[SamDeLore]

I ran a prune on our SQL DB a few months ago, after about 6 months of being live in production we'd noticed a distinct slow down on Isolation tracing.

But for us the biggest offenders were the various Subnetlines & UN_6_Topology. EID_Mappings did not change as we've not had to disable/enable in a production setting yet, but it's still a hefty table.

I did get a big drop in size, from around 35GB to 17GB. It's not the biggest database in the world but it's a decent percentage.

I'm wondering what others are doing for a DB Maintenance schedule?

[Thomas_Brown]

Hello Sam,

if you are seeing a slow down in an Isolation trace you should open a support incident for guidance. In server if you increase the logging level to verbose and perform the trace operation we log detailed statistics for the operation which will help explain where the time is being spent.

Not

knowing the columns of editing you’ve performed and the number of validates in the DEFAULT version you may actually need to disable and enable the topology to essentially reduce the number of pages accessed from the index to solve the trace operation - therefore improving performance.

Support can also provide guidance reviewing the logs.

Thank you

[SamDeLore]

Thanks for the tip @Thomas_Brown, makes sense - we've had substantial editing volume since the network was originally built, so after many topology validations I'd imagine the pages are a bit of a mess by now.

I have a backlog of schema and network rule changes queued up, so it's a good opportunity to bundle those in and get it all done in one window with a prune as well.

[RobertKrisher]

@SamDeLore tips for looking at logs to understand performance:

Utility Network Diagnostics

Parsing Utility Network Diagnostic Logs

[SamDeLore]

Thanks @RobertKrisher, I'll have a read through your links

[PierreloupDucroix]

Thanks everyone for your replies.

First, @RobertKrisher, I don't need to reclaim any disk space, so I agree with you; the allocated space can be kept for future database use. PostgreSQL's autovacuum should handle that if necessary.

@TomBrown, the main goal of this cleanup is precisely performance. Users are noticing a degradation in drawing performance over time (but not in subnet calculations), and we've had some bad experiences in the past with increasing the number of versions and archive rows. But that was with traditional versioning, so the comparison probably ends there. I understand that with this database size, it shouldn't be a problem.

Just one question: can the number of named versions impact performance, or is it only the number of archive rows?

Now, I'm going to focus more on attribute rules since many improvements were introduced in version 11.5.

The other main goal is to develop my skills and understand the mechanisms involved in pruning, in order to implement a version control and database management system that is effective in the long term.

I can provide a backup of the PostgreSQL database and add you to the support case for further testing.

[Thomas_Brown]

Good evening,

I’m still concerned users would be seeing any display performance degradation at this point, therefore it will be valuable to profile the spatial queries.

Answering your question… the number of versions has 0 impact on the performance of our queries, but as you highlight the historical rows “might” in the case of a poorly behaving application (query request).

we expect to support hundreds of thousands of versions (just as we did with traditional versioning). Of course there is a correlation between versions and the volume of edits in each version - thus the reason for having a version.

Performance is directly related to io and cpu consumed by any query. A reasonable spatial query returning a few hundred features should be extremely fast - as the access path is the spatial index followed by index scans to obtain the active row in the version.

of course if you have inefficient query requests which result in full table or index scans all bets are off and those queries need to be eliminated (fix the workflow or index attributes accordingly).

I would like to review your support incident. Please email me the incident number - [email protected].

Postgres is a very important database we support and we want to ensure our queries are behaving as expected.

Thank you