Lots of rows are getting inserted every second. Batch deletion is performed when the disk is close to full. This causes two problems:
It is impossible to examine actually how much space in how many pages are reusable. All I see is the disk is almost full, because the database does not return disk space to OS after deletion.
Fragmentation happens. The disk usage slowly grows, and eventually becomes full, crashing the machine.
Solutions I have considered:
Monitor disk usage, and perform a
VACCUM FULLwhen the disk is 48% full. But reserving 52% (yes, the disk is mostly occupied by database) of the disk just to delete records sound very wasteful.
Partition my tables by hour of record creation time. Only delete old partitions and hope that the deleted paritions free up complete pages and so
autovacuumwill return disk space of those completely empty pages.
Use a on-file database like
SQLite. Separate data into files by hour. Only load the most updated 24 hours unless otherwise specified. When I need to delete, just delete the files. I haven't tried this, but it sounds like lots of overhead to attach all the 24 databases every hour.
I really appreciate any suggestion. Below are the additional details. Many many thanks!
I am currently using
PostgreSQL to host a streaming of live social media posts. There is a streamer that perform batch inserts, and a middleware that perform purely-read queries for client requests. The whole application is delivered using Docker image.
As the streamer inserts records, some day the disk will be full. So we need to delete records. But deleting records doesn't reclaim disk space and can cause fragmentation. So ultimately a
VACCUM FULL is needed to compact the tables again.
The users who deploy this app is non-technical. They don't even know what is a shell/terminal.
The users will perform no maintenance for the database. So
VACUUM FULLis an option only when the disk have enough space for the new tables.