Thread: Postgres 8.x on Windows Server in production

From:
Ognjen Blagojevic
Date:

Hi all,

What are your experiences with Postgres 8.x in production use on Windows
Server 2003/2008? Are there any limitations, trade-offs or quirks?

My client is accustomed to Windows Server environment, but it seems hard
to google good information about these types of installations.

Regards,
Ognjen

From:
Josh Berkus
Date:

Ognjen,

> What are your experiences with Postgres 8.x in production use on Windows
> Server 2003/2008? Are there any limitations, trade-offs or quirks?

First of all, you need to know that the first *two* digits of a
PostgreSQL version are major version numbers.  So 8.3 is not the same
Postgres which 8.1 is.

Here's the top level summary:

PostgreSQL on Windows, compared to Linux, in general:
    -- is a bit slower
    -- is not as reliable, because the underlying FS and OS are not as
reliable*
    -- some of the tools for Postgres which are available on Linux do not
work on Windows (especially performance tools)
    -- is less secure, because the OS is less secure

Yet 1000's of users are running PostgreSQL on Windows in production.  It
really depends on what kind of application you're running, and what its
demands are.  For a CMS or a contact manager or a personnel directory?
No problem.  For a central payroll system for 18,000 employees?    I'd
use Linux.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

From:
Grzegorz Jaśkiewicz
Date:

On Fri, Apr 10, 2009 at 7:07 PM, Josh Berkus <> wrote:
> Yet 1000's of users are running PostgreSQL on Windows in production.  It
> really depends on what kind of application you're running, and what its
> demands are.  For a CMS or a contact manager or a personnel directory? No
> problem.  For a central payroll system for 18,000 employees?    I'd use
> Linux.

Confirmed from my experience too.

On top of that, I would like to add - that using it on windows first,
might be a good step ahead. And installing linux on server isn't so
hard anymore, and shouldn't be a problem, unlike 8 years ago :)

Give it a try, and please tell us what sort of application you want to
put on it.

--
GJ

From:
"Rainer Mager"
Date:

We use Postgres 8.x in production on Windows Server 2003. We have not done a
direct head-to-head comparison against any *nix environment, so I can't
really compare them, but I can still give a few comments.

First of all, it seems that some of the popular file systems in *nix are
more robust at preventing disk fragmentation than NTFS is. Because of this I
definitely recommend have some defragging solution. What we've settled on in
O&O (that's the company name) Defrag Server version. Their software has some
nice features related to defragging in the background while monitoring
system usage so an to impact performance minimally.

Second, one big difficulty with running on Windows is that most of the
Postgres expertise seems is around *nix environments. This means that when
you do need to investigate a performance issue it can be more difficult to
get direct advice. For example, perusing this mailing list will show lot's
of tips suggesting running various tools to show io performance, etc. Well,
on Windows the toolset is different.


All in all we've been happy enough with Windows. Certainly we've never
considered migrating to *nix because of difficulties with it.



--Rainer

> -----Original Message-----
> From:  [mailto:pgsql-performance-
> ] On Behalf Of Ognjen Blagojevic
> Sent: Friday, April 10, 2009 6:47 PM
> To: 
> Subject: [PERFORM] Postgres 8.x on Windows Server in production
>
> Hi all,
>
> What are your experiences with Postgres 8.x in production use on
> Windows
> Server 2003/2008? Are there any limitations, trade-offs or quirks?
>
> My client is accustomed to Windows Server environment, but it seems
> hard
> to google good information about these types of installations.
>
> Regards,
> Ognjen
>
> --
> Sent via pgsql-performance mailing list (pgsql-
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


From:
Scott Marlowe
Date:

On Sun, Apr 12, 2009 at 5:13 PM, Rainer Mager <> wrote:
> We use Postgres 8.x in production on Windows Server 2003. We have not done a
> direct head-to-head comparison against any *nix environment, so I can't
> really compare them, but I can still give a few comments.

Just wondering, what version are you actually running?  Big
differences from 8.0, 8.1, 8.2, 8.3 and soon 8.4.  For people taking
your advice on running on windows, it helps them make a decision on
whether or not to upgrade.

> First of all, it seems that some of the popular file systems in *nix are
> more robust at preventing disk fragmentation than NTFS is. Because of this I
> definitely recommend have some defragging solution. What we've settled on in

Linux file systems still fragment, they just don't tend to fragment as
much.  As the drive gets closer to being full fragmentation will
become more of a problem.

From:
"Rainer Mager"
Date:

We're running 8.3, but when we started this server about 2 years ago it was
an earlier 8.x, I don't remember which.

--Rainer

> -----Original Message-----
> From:  [mailto:pgsql-performance-
> ] On Behalf Of Scott Marlowe
> Sent: Monday, April 13, 2009 8:41 AM
> To: Rainer Mager
> Cc: Ognjen Blagojevic; 
> Subject: Re: [PERFORM] Postgres 8.x on Windows Server in production
>
> On Sun, Apr 12, 2009 at 5:13 PM, Rainer Mager <>
> wrote:
> > We use Postgres 8.x in production on Windows Server 2003. We have not
> done a
> > direct head-to-head comparison against any *nix environment, so I
> can't
> > really compare them, but I can still give a few comments.
>
> Just wondering, what version are you actually running?  Big
> differences from 8.0, 8.1, 8.2, 8.3 and soon 8.4.  For people taking
> your advice on running on windows, it helps them make a decision on
> whether or not to upgrade.
>
> > First of all, it seems that some of the popular file systems in *nix
> are
> > more robust at preventing disk fragmentation than NTFS is. Because of
> this I
> > definitely recommend have some defragging solution. What we've
> settled on in
>
> Linux file systems still fragment, they just don't tend to fragment as
> much.  As the drive gets closer to being full fragmentation will
> become more of a problem.
>
> --
> Sent via pgsql-performance mailing list (pgsql-
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


From:
Scott Marlowe
Date:

On Sun, Apr 12, 2009 at 5:49 PM, Rainer Mager <> wrote:
> We're running 8.3, but when we started this server about 2 years ago it was
> an earlier 8.x, I don't remember which.

Cool.  PostgreSQL is one of the few projects where I've always
recommended upgrading and keeping on the latest major version as soon
as possible after it comes out.  This stands in stark contrast to
apache 2.0, which was out for over two years before it was worth the
effort to migrate to.  The improvements just weren't worth the effort
to upgrade.  I've seen enough performance and capability in each major
version of pgsql to make it worth the upgrade since 7.0 came out.  I
think we've skipped one or two short releases,  like 7.1 or 8.2, but
in general even those represented useful gains over previous versions.

From:
Ognjen Blagojevic
Date:

Hi all,

First, thank you all for your answers.


Grzegorz Jaśkiewicz wrote:
> Give it a try, and please tell us what sort of application you want to
> put on it.

It is a student database for the college which is a client of ours. The
size of the database should be around 1GB, half being binary data
(images). Not more than 100 users at the time will be working with the
application.

I don't worry about the performance, but more about the maintenance
under Windows. What file system to use? How to schedule vacuuming and
backup? Are there any windows services that should be turned off? Those
questions come to my mind when I consider new OS for the RDBMS.

Regards,
Ognjen



From:
Scott Marlowe
Date:

2009/4/13 Ognjen Blagojevic <>:
> Hi all,
>
> First, thank you all for your answers.
>
>
> Grzegorz Jaśkiewicz wrote:
>>
>> Give it a try, and please tell us what sort of application you want to
>> put on it.
>
> It is a student database for the college which is a client of ours. The size
> of the database should be around 1GB, half being binary data (images). Not
> more than 100 users at the time will be working with the application.
>
> I don't worry about the performance, but more about the maintenance under
> Windows. What file system to use? How to schedule vacuuming and backup? Are
> there any windows services that should be turned off? Those questions come
> to my mind when I consider new OS for the RDBMS.

NTFS, use autovacuum, backup nightly (?), Turn off anti-virus software.

From:
Grzegorz Jaśkiewicz
Date:

2009/4/13 Ognjen Blagojevic <>:
> It is a student database for the college which is a client of ours. The size
> of the database should be around 1GB, half being binary data (images). Not
> more than 100 users at the time will be working with the application.

nice, if you want to store pics, I suggest filesystem - with some nice
schema to do it.
The way I do it, is using md5 of pics/other data, and three level of
directories to lead you to the right file on disc.
It is dead easy to implement, and much faster than bytea in DB. (at
least on linux, I wouldn't hold my breath so much for ntfs, but you
never know).

100 connections is quite a bit, so you need a bit of memory. It also
depends on the actual queries, and schema.
If you have any questions about that, and performance - we are here to help.

> I don't worry about the performance, but more about the maintenance under
> Windows. What file system to use? How to schedule vacuuming and backup? Are
> there any windows services that should be turned off? Those questions come
> to my mind when I consider new OS for the RDBMS.

just like scott said,
Make sure to either turn off anti-virus, or tell it to stay away from
postgresql. (which leaves your machine quite vulnerable, so make sure
to secure it well).

hth

--
GJ

From:
Justin Pitts
Date:

You'll almost certainly want to use NTFS.

I suspect you'll want to set the NTFS Allocation Unit Size to 8192 or
some integer multiple of 8192, since I believe that is the pg page
size. XP format dialog will not allow you to set it above 4096, but
the command line format utility will. I do remember setting it as high
as 64k for SQL Server on Windows Server 2003 (SQL Server does IO in
8-page units called extents)
Someone please correct me if I have that wrong.

Do not allow any indexing service activity on the data or transaction
log volumes. If this is a dedicated database server you may as well
turn indexing service off.

Don't enable compression on the data or transaction log volumes either.

Pay attention to Automatic Updates - you likely don't want your
database server to restart every 4th Wednesday morning or so.

Hope this helps,
Justin
2009/4/13 Ognjen Blagojevic <>:
> Hi all,
>
> First, thank you all for your answers.
>
>
> Grzegorz Jaśkiewicz wrote:
>>
>> Give it a try, and please tell us what sort of application you want to
>> put on it.
>
> It is a student database for the college which is a client of ours. The size
> of the database should be around 1GB, half being binary data (images). Not
> more than 100 users at the time will be working with the application.
>
> I don't worry about the performance, but more about the maintenance under
> Windows. What file system to use? How to schedule vacuuming and backup? Are
> there any windows services that should be turned off? Those questions come
> to my mind when I consider new OS for the RDBMS.
>
> Regards,
> Ognjen
>
>
>
> --
> Sent via pgsql-performance mailing list ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>