How much database bloat is reasonable?

Question

I use personal geodatabases to hold all the data for my projects (probably around 10-20 feature classes across 2-4 datasets on average). I use the personal gdb because I like being able to hook into it through other MS Office products and use mail-merge for reports and access for pivot tables etc etc. Also, ArcGIS 10.1, Windows 7, data located on a network drive.

Over the last few months I have been automating all my boring tasks using arcpy and am noticing some substantial bloat in my databases after I run certain tools (from a python toolbox, not esri stock tools). For instance, this morning I ran the script that iterates through each polygon in a feature class, selects all the features that are inside of them, sums their [COUNT] fields and then uses CalculateField_management tool to write that sum to a field in the polygon attribute table. There are roughly 2500 records in the polygon field, so about 2500 iterations, selecting 1-5 records from 6-7 feature classes during each iteration. When the operation completed, my gdb had bloated up to 2.0 GB (yes, the magic 'now nothing will work' number). After I ran a simple 'Compress Database' in arcCatalog the size dropped to 32MB. That's a decrease of 98.5 percent, which seems egregiously huge.

So, my question is: Is that a normal amount of shrink/swell for a personal geodatabase or am I doing something wrong? Further, if I am doing something wrong, what strategies can I employ to fix it? Would there be significant performance benefits to using a file geodatabase instead of a personal?

Lastly, I am becoming increasingly aware that the bulk of what I am doing in arcpy could be more easily accomplished using SQL in a more robust database environment... Is it time to take the plunge and start learning about PostgreSQL?

Answer 1

It looks like there are a few things that can cause an Access database to grow excessively.

1. Row Locking is discussed in this Stackexchange question: MS-Access database getting very large during inserts
   It looks like the suggested solution is to turn off Row-Locking in the database. This is something that is turned off on the Access database itself, not through ArcGIS. That being said, I'm not sure what effects, positive or negative, this may have related to ESRI's interaction. A quick search didn't turn anything up, so it couldn't hurt to try it.

2. This Microsoft technical article discusses How to Prevent Bloat after using Data Access Objects(DAO). I'm not sure the type of interface that ESRI uses to tie in to the Access database querying and other operations, but I imagine they are related to this as well.

I think those address the size problem pretty well. In the time that I've been using PGDB's and just Access databases in general, I've seen a lot of size fluctuation. There have also been something grew very large while the data they contained didn't seem to support it. It doesn't seem like there is much you can do aside from what these articles may help with.

Now, on to the part of your question that has many more possibilities, and questions.

You are actually asking two different questions here.

1. Should you learn more SQL?
2. Should you learn how to use a server based RDBMS like PostgreSQL?

Question #1 - I would definitely say yes. This would apply regardless of how you approach question number 2. Even if you continue to use Python and a Personal GDB, you could start to move some of your operations from Python code to SQL queries and pass them through. You can do this using Arcpy as you are now, or in combination with a module like pyodbc that lets you interact with any number of database formats. As you said in your question, learning SQL gives you the ability to perform operations much more efficiently.

Question #2 - This, obviously is going to come out on the side of "Yes", as well. It is easier to give concrete examples for why learning an RDBMS will benefit you.
Here are a few:

1. The process of installing and configuring a true RDBMS like PostgreSQL forces you to become familiar with your data and how it is structured. There are so many more potential controls on who has access, and on what is allowed, that you need to put some thought into your data when you first set it up as it can be much more difficult to change later.
2. The fact that an RDBMS is ACID-compliant is a huge and I would say relatively hidden safety net, at least to the lay user. With Access, if a query goes bad it could corrupt your entire table, and possibly your entire database. Knowing that when you are running a query, if it goes bad, it will not affect the integrity of the data already there gives you a lot more flexibility.
3. Multi-User support. Even if you do not use SDE or some other Abstraction layer in between your database and the GIS, this is a huge leap. A personal example is concerning building extended tables of attributes in a PostgreSQL database. When I had the tables in MS Access, if someone was referencing them in an .mxd, I would not always be able to edit them or change their structure until all other user locks were released. With PostgreSQL, I am able to be viewing the data in ArcGIS at the same time as I update attributes and modify the table structure through the database. This saves me a lot of time. It also means that once I have other users accessing this same data, I will be able to make other changes during the day without having to ensure that everyone has closed their references to the database.
4. Getting away from a siloed data structure. Once you centralize your data, and let everybody be able to access it, it lessens the need for smaller groups to have their own copies of the same data, or the only copy of some data that they are unwilling to share due to concerns about it being corrupted, etc. If you know how to use an RDBMS you can ensure that all the data is properly backed up, while allowing different levels of access to different users based on their individual and organizational needs. Also using a database of this type reduces the likelihood of a situation where you are unable to extract data to share with another party. This story is a prime example. Please be aware that the biggest problem in this situation was more an inability to use the software to extract the data to a usable format than it was a problem with the data storage itself. It still highlights the problem of data silos.

My last comment applies to both questions, of why would you want to learn SQL or PostgreSQL. The simple fact is that each becomes another tool in your chest to help you do your job. Knowing SQL enables you to be able to access data from a variety of sources and then perform many operations on said data without the need for specialized software. Knowing PostgreSQL introduces you to a much more robust database structure. Whether you end up using it or a different RDBMS platform, you will find that there are many similarities, so you are effectively gaining knowledge about multiple systems. Python, SQL, PostgreSQL and MS Access are all appropriate in particular circumstances, with some overlap. Having familiarity with them all allows you to take advantage of their individual strengths to streamline your workflow.