On 2010-10-12 18:02, Scott Carey wrote: > However, for large reporting queries and sequential scans, XFS will > win in the long run if you use the online defragmenter. Otherwise, > your sequential scans won't be all that sequential on any file system > over time if your tables aren't written once, forever, serially. > Parallel restore will result in a system that is fragmented -- ext4 > will do best at limiting this on the restore, but only xfs has online > defragmentation. We schedule ours daily and it noticeably improves > sequential scan I/O. > > Supposedly, an online defragmenter is in the works for ext4 but it > may be years before its available.
If some clever postgres hacker could teach postgres to allocate blocks using posix_fallocate in quite large batches, say .. something like: fallocate(min(current_relation_size *0.1,1073741824));
So if you have a relations filling 10GB allready, they the next "file" for the relations is just fully allocated on the first byte by the filesystem. That would ensure that large table is sitting efficiently on the filesystem level with a minimum of fragmentation on ext4(and other FS's supporting posix_fallocate) and for small systems it would only fill 10% more of diskspace... ..
.. last night I spend an hour looking for where its done but couldnt find the source-file where extention of an existing relation takes place.. can someone give directions?