AWS RDS - Postgres DB Purge

[pshashipreetham]

Hi, 

I am trying to purge a ThingWorx database, which is hosted on AWS RDS using Postgres. unfortunately, the DB has more than 20TB of data. 

I was trying to delete the data in the DB and purged around 80% of the data, but the size of the DB remains the same.

Below are the validations I have done:

1. Re-checked the rows that got purged, and they were not showing up in select After the purge
2. Did Analyze - which deleted all the Dead Tuples from the DB also
3. Checked the Table (public.value_stream) size, still shows the same size of DB which was before the pruge. 

What my research shown is that once the AWS RDS DB is increased to a point, that can't be shirnked/decreased, customer should continue paying for it.

I am looking for a best way possible to decrease the size of DB such that the costings get reduced

Thingworx Version: 9.1

Thanks,

[Constantine]

Hello,

This is not specific to RDS. You can’t decrease size of AWS block storage like EBS (RDS is based on EBS, with a notable exception of Aurora, which uses S3 for most of its data).

The standard approach to downsize an RDS instance is by performing a migration. If you are very careful you can do it with nearly zero downtime. There’s also a Green/Blue deployment method,but I’ve never tried it myself: https://aws.amazon.com/blogs/database/shrink-storage-volumes-for-your-rds-databases-and-optimize-your-infrastructure-costs/

Just to set your expectations straight — you need an AWS architect to do any of that. There are dozens of pitfalls, and it’s easy to screw something up. It’s not trivial.

Another alternative for the way forward would be to try AWS Aurora for Postgres. You’d need to check if it is officially supported by PTC. If yes, you might get better (or worse , but that’s unlikely) performance for your $$$ and also you only pay for the data you actually use. Obviously, this would require migration and an architect’s involvement, too.

Regards,

Constantine

[slangley]

Hi @pshashipreetham 

If you found the solution provided by Constantine helpful, please mark it as the Accepted Solution for the benefit of others in the community.

If you still have questions, please let us know.

Regards.

--Sharon