Arnt Gulbrandsen wrote: >Wow. I disagree with almost everything you write.
Well, maybe that indicates we start with wildly differing assumptions. >On Wed, Nov 20, 2013 at 12:54:22PM +0100, Stephen R. van den Berg wrote: >> Externalising the attachments allows for some extra flexibility: >> - When disaster hits (disk(s) full), it allows for some immediate >> relief by creating a script that quickly runs through all >> attachments and deletes some large/unimportant ones. >So much faster than > delete from bodyparts where bytes>10000000; >that it's worth complicating the code? Running the delete does not free up space instantly, only after a vacuum full. Actually, due to the transactional nature of the db, it will slightly increase the storage requirements first. >> - It reduces the probability that the db runs into a problem >> due to full partitions (because you can put the attachments >> on different partitions than the db itself). >Given that large attachments account for a one-digit percentage of >space used, I find it difficult to believe that this makes a >worthwhile difference. Try this on your database: > select sum(bytes) from bodyparts where bytes > 10000000; >How many per cent of the total space does that report? Valid question. I asked my production db, about 5 minutes ago, the query is still running. I'm not certain I can leave it running to completion without disrupting service too much. Watch this space... >> - A postgresql vacuum will finish sooner (under normal conditions). >Doesn't vacuum depend mostly on the number of rows? A few very large >rows won't make much difference, then. If there are many attachments >involved, there are also many messages being harmed and we're not >talking about a few unimportant attachments any more. If you actually want to free up space, a vacuum needs to copy big amounts of data to compact. The copying is not free. >> - A postgresql vacuum will actually finish (if the db has reached >> sizes of >300GB, a postgresql vacuum is a problem by itself). >Reducing the size to 280GB makes little difference. I was considering reducing it to 20GB actually, that *will* make a dent. >> at some later point in time. Most of the repository is going >> to be coherent and accessible soon. Restoring the attachments >> (which first) can then be prioritised according to urgency. >Unfortunately, while the database is coherent as far as SQL goes, it's >not coherent in the IMAP sense, and fixing that up woud be real work. It would mean that imap would return a message with (forced) empty attachments, you think that would be difficult to get right? -- Stephen.
