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.<p>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.<p>I see that MariaDB comes with multiple engines, maybe some of them could work like that?<p>I know that Amazon's Aurora is even worse than raw MySQL/PostgreSQL.
Juggle tables to meet your use case. Switch queueing to new table, wrap up work in old table, drop old table.<p>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.
When delete or update records, the database engine basically marks the space as available, then the next record will be filled in.<p>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.<p>So it is not a way to design a database engine
In 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)