Tuning for PostGreSQL Database

Sean Chittenden sean at chittenden.org
Mon Jul 21 12:26:47 PDT 2003

> > Softupdates on, async off.  Softupdates is just a better async.
> postgresql fsync's all its files before returning from a commit in
> order to ensure durability, right?  Does softupdates interfere with
> the functioning of sync(2)/fsync(2)?

It can, yes, but that's the risk of soft updates.  From tuning(7):

     Softupdates drastically improves meta-data performance, mainly
     file creation and deletion.  We recommend enabling softupdates on
     most file systems; however, there are two limitations to
     softupdates that you should be aware of when determining whether
     to use it on a file system.  First, softupdates guarantees file
     system consistency in the case of a crash but could very easily
     be several seconds (even a minute!) behind on pending write to
     the physical disk.  If you crash you may lose more work than
     otherwise.  Secondly, softupdates delays the freeing of file
     system blocks.  If you have a file system (such as the root file
     system) which is close to full, doing a major update of it,
     e.g. ``make installworld'', can run it out of space and cause the
     update to fail.  For this reason, softupdates will not be enabled
     on the root file system during a typical install.

> > > I would recommend 4.8 over 5.1.  Especially if you intend this
> > > to be a production server.  5.1 is not ready for public
> > > consumption.
> >
> > Public consumption, yes.  Production consumption, no.
> I recently decided to take up the issue of migrating towards 5.1 at
> my company.  The way I dealt with it was to publish all of my
> services to 5.1 boxes and run regression tests.  For me, 5.1
> provided adequate performance, and since 5.1 is on the branch where
> all new dev work is going on and 4.x is going to be end-of-lifed in
> the not-too-distant future, I decided to go with 5.1.
> Personally, I would only go with 4.8 if testing showed 5.1 to be
> inadequate for your particular situation with your particular apps,
> hardware, load, etc.  But you can only tell that by testing.

Glad to hear that 5.1 is getting used in the work place.  Were there
any places where your testing showed huge gaps in performance?  -sc

Sean Chittenden

More information about the freebsd-performance mailing list