Thanks for getting back to me. I had a small discussion with @sfrost on the slack team and understand the issue better now. I must admit I didn't realize that the scope of WAL extended to VACUUM operations which is why I suggested a new journaling system. I realize now the issue is not safety(as the WAL already sorts out that issue), but performance. I will rethink my suggestion and let you know if I come up with a useful/performant way of doing this.
*ThanksRyan Sheasby* On Thu, Feb 21, 2019 at 5:27 PM Andreas Karlsson <andr...@proxel.se> wrote: > On 2/21/19 12:16 AM, Ryan David Sheasby wrote: > > I was reading on VACUUM and VACUUM FULL and saw that the current > > implementation of VACUUM FULL writes to an entirely new file and then > > switches to it, as opposed to rewriting the current file in-place. I > > assume the reason for this is safety in the event the database shuts > > down in the middle of the vacuum, the most you will lose is the progress > > of the vacuum and have to start from scratch but otherwise the database > > will retain its integrity and not become corrupted. This seems to be an > > effective but somewhat rudimentary system that could be improved. Most > > notably, the rewriting of almost the entire database takes up basically > > double the storage during the duration of the rewrite which can become > > expensive or even simply inconvenient in IaaS(and probably other) > > installations where the drive sizes are scaled on demand. Even if the > > VACUUM FULL doesn't need to run often, having to reallocate drive space > > for an effective duplication is not ideal. My suggestion is a journal > > based in-place rewrite of the database files. > > Hi, > > VACUUM FULL used to modify the table in-place in PostgreSQL 8.4 and > earlier but that solution was slow and did often cause plenty of index > bloat while moving the rows around in the table. Which is why PostgreSQL > 9.0 switched it to rewiring the whole table and its indexes. > > I have not heard many requests for bringing back the old behavior, but > I could easily have missed them. Either way I do not think there would > be much demand for an in-place VACUUM FULL unless the index bloat > problem is also solved. > > Additionally I do not think that the project would want a whole new kind > of infrastructure just to solve this very narrow case. PostgreSQL > already has its own journal (the write-ahead log) which is used to > ensure crash safety, and I think any proposed solution for this would > need to use the WAL. > > Andreas >
