Ok folks, this is kind of a weird one. I'm going to put it in the

Shrinking a Postgres Table

submited by

Style Pass

2024-11-28 14:30:40

Ok folks, this is kind of a weird one. I'm going to put it in the "you won't ever need this, but if you do, you are going to be glad I wrote this up for ya" category.

As you may or may not know, I recently acquired fireside.fm, an awesome podcast host. While investigating a slow query, I noticed that the database servers were at 87% disk space use.

I typically use a managed Postgres, which makes it real obvious when you are starting to use a lot of disk space and easy to fix. But this is self-managed Postgres on Linode.

So I started up a casual chat with GitHub's Copilot. You know, the typical, "how are you?", "oh I'm fine, you?", "yeah, same", "do you like long walks on the beach?", "yeah, my database is about to fall over too".

downloads seemed the largest so I started there. I wasn't entirely sure how the table was used (again I'm new to this app). So I poked around for min and max created_at times. To my surprise, there were rows going back to 2021.

The downloads table seemed to be a record of all the raw downloads before they are aggregated into tables more efficient for querying in a web request. As downloads are processed, they are deleted from the table.