Re: PostgreSQL win32 fragmentation issue

Mark Kirkwood wrote:
> Andrew Dunstan wrote:
> 
>>
>> (I should have thought a single file DB would be MORE liable to
>> fragmentation, BTW).
>>
> 
> The likes of Oracle/SQLServer preallocate the files(s) at database 
> creation time, so there will be little or no initial fragmentation.
> 

... To clarify - preallocated with free space in the files :-) ...

A related point comes to mind, consider the single "tablespace" file for 
example: It may have no fragmentation from a *file* perspective, but 
supposing you have several busy relations being inserted or updated then 
pages (or groups of pages) for these could be "fragmented" throughout 
the tablespace file. This can reduce scan performance, since seeks are 
now involved to "jump over" the page chunks for relations you are not 
interested in.

Some Oracle DBA's I knew used to regularly export|rebuild|import their 
databases precisely to "defragment" their relation|file page distribution.

I'm not trying to say that we don't have an issue, just pointing out 
that preallocated files are not necessarily a complete or ideal solution.

Cheers

Mark