email_data_object table has massively increased in size

Hi All,

I'm not sure if this is the correct forum, but I thought of posting it here to see if anyone can help.

We have an instance of WHD with a database over 150GB, which is somewhat massive compared to the rest of our databases. We've narrowed it down to the email_data_object table, which seems to be storing the raw email sent. We've already tried changing the email retention interval from 5 years to 1 week, but the previous emails seem to still stick.

We are using PostgreSQL as an external DB. Has any of you noticed this before, and if so, how did you overcome it?

Find more posts tagged with

PostgreSQL

Database

whd

Accepted answers

All comments

pabely

Hi @navco_loop1 ,

This is exactly the right place to ask such questions, which WHD release version are we talking about here?

navco_loop1

I noticed it on a DB with v12.7.12. But regardless of that, this table is known to increase in size if we aren't mindful of the retention periods. I am having a hard time taking a backup before upgrading this, so I am trying to reduce this before I do so.

pabely

I have just checked one of my Production WHD12.8.6 systems, and it definitely is not purging as it should be, It sounds like a bug, have you logged with SW Support?

navco_loop1

I just did #01978220. As you said, the purging operation does not seem to work as expected. In our prod instances, on average, this table is around 4GB in size, so it's quite manageable. But better keep an eye out

jholzhey_bu

If you figure this out, I hope it can help us, too. We have the largest on prem dbase in the org, and I’ve never gotten a suitable answer on how to reduce its size. And I don’t have enough dbase knowledge to do my own maintenance.

navco_loop1

SW support does not provide database maintenance support; however, they advised that truncating the following tables could free up storage. Note that once truncated, this data cannot be recovered:

Steps:

Take a backup of your WHD database.
Stop WHD.
Connect to your database.
Run the following SQL statements (execution time depends on DB size):

<span><span class="hljs-keyword">TRUNCATE</span> <span class="hljs-keyword">table</span> email_data_object;<span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> email_history_entry;</span>

</code></div><div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-sql">

In our we couldn't truncate the tables, so we also considered running VACUUM FULL at the database or table level. This operation can reclaim space, but it has two major drawbacks:

Requires adequate free disk space to run.
Requires downtime since the target table(s) are locked during the operation.

Our proven method was to use pg_repack on the affected database/tables. pg_repack rebuilds tables/indexes and reclaims space without long exclusive locks. The key difference:

VACUUM FULL locks the table during the operation.
pg_repack can operate online, minimizing disruption.

Both methods require sufficient free storage to complete. In our case, we didn’t have enough free space on the production instance. To work around this:

Took a logical backup (pg_dump) of the bloated database.
Restored it to a staging database with enough allocated storage.
Ran pg_repack on the staging copy to reclaim bloat.
Dumped the cleaned database.
Dropped the bloated production database.
Restored the cleaned version back into production.

This process was time-consuming due to the large DB size, but it successfully reclaimed space and stabilized the environment.

Documenting this here for anyone else who may face the same issue.