- Basic knowledge of PostgreSQL
- PostgreSQL installed and configured as Mirth’s database
Things to Know Beforehand
- Vacuuming a PostgreSQL database will first increase your drive size before making it smaller. Make sure you have sufficient room to complete the vacuum before beginning.
- This article is not intended to be a comprehensive overview of PostgreSQL’s vacuuming feature, but rather, to demonstrate a basic vacuum to clean up the database’s dead rows for Mirth.
Preforming a Vacuum
If you are using PostgreSQL as your Mirth database (or any DBMS for that matter) then you will want to monitor disk storage on your Mirth server. Depending on the environment’s message volume, available disk space, the message storage level, and data pruner settings, disk space can fill up rather quickly.
Mirth Connect Administrator typically won’t even start if your disk space fills up completely. Moreover, Mirth would not be able to store any messages on the drive. This will likely not be an issue with a Test system because of the lack of volume coming through. However, if this happens to your Production system you will obviously want to get it resolved as soon as possible.
The way PostgreSQL works is that when data is deleted from the database, the data is removed but disk space is not freed up. Check out the following from PostgreSQL’s website:
“In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control… the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.” 1
Click here for more information on how this process works. The documentation also goes over potential solutions to the need to regularly vacuum a PostgreSQL database.
If you need to manually vacuum a PostgreSQL database you can easily do it via pgAdmin, PostgreSQL’s in-house graphical user interface (GUI) for interacting with the database.
1. Open pgAdmin
Note that your installation location could differ especially if you are running Mirth on a Windows server.
2. Enter the master password for your PostgreSQL database and click ‘OK’. This is the same password you set when you installed and created your database.
3. Expand Servers on the left-hand side.
4. Expand Databases on the left-hand side.
4. Right-click on mirthdb.
5. Select Maintenance.
6. Make sure the Maintenance operation is set to ‘VACUUM’, Vacuum’s ‘FULL’ option is set to Yes (if you need to do a full vacuum), and Verbose Messages is set to ‘Yes’.
7. Click ‘OK’. Depending on what version of PostgreSQL you are running, the process will either start immediately or PostgreSQL will create the maintenance job. pgAdmin should notify you once the maintenance is complete.
Note that you can watch your drive space grow/shrink as the vacuuming process completes.
8. Once the vacuum is done the ‘Running…’ message will say ‘Successfully completed’.
9. Check your drive space and confirm that space was freed up.
There are many more options when it comes to configuring vacuuming for your PostgreSQL database. One of these is the autovacuum feature. As its name implies, the feature automatically performs a vacuum periodically.
PostgreSQL’s autovacuum feature should be turned on by default. However, you can verify that with the following query.
SELECT NAME,SETTING FROM PG_SETTINGS WHERE NAME='autovacuum';
PostgreSQL has some additional documentation that specifically goes over autovacuum amongst other features. Click here now to learn more.