5 ms·
Ask HN: SQL engine reclaiming space from DELETEs
Both PostgreSQL and MySQL are poor in that regard, especially PostgreSQL: after many DELETEs/UPDATEs, the disk space is not released to the OS, and the re-use of the space by the DB engine is often not working well due to the fragmentation. The only option is rebuilding/DROPping the whole table which requires downtime.
I'm essentially looking for an SQL engine that would be suitable as a storage for a queue server. I would like it to release the freed disk space to the OS immediately, even if this requires making extra I/O.
I see that MariaDB comes with multiple engines, maybe some of them could work like that?
I know that Amazon's Aurora is even worse than raw MySQL/PostgreSQL.
- sansnomme 8y agoVacuum?
- void141star 8y agohttps://www.postgresql.org/docs/11/sql-vacuum.html https://www.postgresql.org/docs/11/sql-vacuum.html
- twa927 8y agoRegular VACUUM only marks space as available for reuse. VACUUM FULL returns space to the OS but it requires an exclusive lock and a multi-hour run for a large table.
- salex89 8y agoShouldn't plain vacuum be enough? The space is going to be reused for new items anyway, it won't take more of the space not already reserved by the database? On another topic, do you require using a SQL database for your queue?
- twa927 8y ago> Shouldn't plain vacuum be enough? The space is going to be reused for new items anyway, it won't take more of the space not already reserved by the database? In theory, yes. In practice, I've seen a lot of bloat being preserved despite using aggressive autovacuum settings. I'm guessing this is due to fragmentation? > On another topic, do you require using a SQL database for your queue? I'd need some way to browse the queue and select items for processing using arbitrary queries.
- docuru 8y agoWhen delete or update records, the database engine basically marks the space as available, then the next record will be filled in. Because the file system only allows append or update content on a file. To release the disk space, it will need to completely rewrite the whole data file, which will cause much more things to handle and time. So it is not a way to design a database engine
- twa927 8y agoYep, you're describing a typical design of a DB. I'm looking for an alternative design. I'm guessing it could use multiple small files (a few MBs) and merge them to avoid fragmentation.
- docuru 7y agoWhat is the use case that you need the database for? I'm interested to know more. (Sorry I didn't get notice from HN so I don't know someone left a comment)
- zzzcpan 8y agoMaybe check out TokuDB, I haven't tried it myself, but it has things like tokudb_cleaner_iterations [1] and tokudb_cleaner_period. There is also MyRocks based on RocksDB fork of LevelDB, but LevelDB is piece of garbage, not sure about how much RocksDB fork fixes it though. [1] https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_variables.html#tokudb_cleaner_iterations https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb...
- toomuchtodo 8y agoJuggle tables to meet your use case. Switch queueing to new table, wrap up work in old table, drop old table. Or switch to RabbitMQ if you can (you mentioned queueing, which Rabbit is designed for), which can journal to disk on a per queue basis if necessary.
- natmaka 8y agohttps://www.cybertec-postgresql.com/en/introducing-pg_squeeze-a-postgresql-extension-to-auto-rebuild-bloated-tables/ https://www.cybertec-postgresql.com/en/introducing-pg_squeez...
- zzo38computer 8y agoIn SQLite you can use the VACUUM command to minimize the disk space needed. (And from another comment, it look like PostgreSQL also has a VACUUM command, but I do not use PostgreSQL and do not know much about that)