Re: PostgreSQL disk fragmentation causes performance problems on Windows - Mailing list pgsql-performance

From Andrew Dunstan
Subject Re: PostgreSQL disk fragmentation causes performance problems on Windows
Date
Msg-id 5540F10C.9030400@dunslane.net
Whole thread Raw
In response to Re: PostgreSQL disk fragmentation causes performance problems on Windows  ("ktm@rice.edu" <ktm@rice.edu>)
List pgsql-performance
On 04/29/2015 10:35 AM, ktm@rice.edu wrote:
> On Wed, Apr 29, 2015 at 07:07:04AM -0700, Joshua D. Drake wrote:
>> On 04/29/2015 01:08 AM, Andres Freund wrote:
>>
>>>> Which OS and filesystem is this done on? Because many halfway modern
>>>> systems, like e.g ext4 and xfs, implement this in the background as
>>>> 'delayed allocation'.
>>> Oh, it's in the subject. Stupid me, sorry for that. I'd consider testing
>>> how much better this behaves under a different operating system, as a
>>> shorter term relief.
>> This is a known issue on the Windows platform. It is part of the
>> limitations of that environment. Linux/Solaris/FreeBSD do not suffer
>> from this issue in nearly the same manner.
>>
>> jD
>>
> You might consider a CLUSTER or VACUUM FULL to re-write the table with
> less fragmentation.
>

Or pg_repack if you can't handle the lockup time that these involve.

cheers

andrew


pgsql-performance by date:

Previous
From: "ktm@rice.edu"
Date:
Subject: Re: PostgreSQL disk fragmentation causes performance problems on Windows
Next
From: David Osborne
Date:
Subject: Index Scan Backward Slow