Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    18

    Unanswered: PostgreSQL for Windows - does the hype apply?

    We're looking at migrating a business-critical 40GB database from MSSQL2000Std to PostgreSQL. All my research suggested that PostgreSQL is ready for this and will handle it well.

    Then I realized that all of my research probably related to the Linux version, not the Windows version. We're limited to Windows unfortunately.

    Does all the hype about PostgreSQL apply to the Windows version as well? All the stuff about being enterprise ready, rock solid reliable, high performance etc - is it true if I run it on Windows?

    Also, are there any performance comparisons of the Windows version compared to, say, MSSQL2000 or Linux-PostgreSQL?

    Obviously we are going to run our own performance comparisons before making the switch, but I'd like some assurance that we're on the right track - or some warning of the pitfalls we might hit.

    Thanks in advance for your help.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by brendan.hill
    All the stuff about being enterprise ready, rock solid reliable, high performance etc - is it true if I run it on Windows?
    The problem (as far as I can tell) is that the Windows file system (NTFS) is a lot slower than the really fast ones on Linux which is a crucial part for a DBMS server. But I'm neither a Windows nor a Linux expert. The server version of NTFS might(?) be different to the NTFS version used on the "workstations".

    The folks on the postgres mailing list usually consider Windows slower than Linux and it wasn't until 8.2 (I think) that they called the Windows version stable enough for production use.

    Postgres heavily relies on the file cache of the file system (unlike SQL Server or Oracle which handle their own caching). So you should put a lot of memory into the server, which kind of requires a 64bit Windows.

    As far as I know there is no 64bit version of Postgres available for Windows, so I don't know if that would limit the performance (but at last a 64bit Windows would be able to use the large memory for the file cache and Postgres definitely benefits from that).

    Obviously we are going to run our own performance comparisons before making the switch, but I'd like some assurance that we're on the right track - or some warning of the pitfalls we might hit.
    Postgres is not a "drop-in" replacement for SQL Server. An application that was tuned for SQL Server might be slow on Postgres (or Oracle). Which is true for the other direction as well.

    The "SQL Server way" of doing large updates with temp tables (to avoid locking) and things like that usually slow down a system with a better concurrency handling (i.e. using MVCC like Postgres, Firebird and Oracle).

    There are a lot of SQL syntax oddities in SQL Server that need to be taken care of as well (just thinking of + vs. ||).
    Postgres is also more strict when it comes to datatype checking and has less implicit casting.

    Migrating the application (and how it behaves) will be much more important (and challanging) to make it run fast on a non Microsoft DBMS, and might actually be the most difficult part.

  3. #3
    Join Date
    May 2009
    Posts
    18
    Quote Originally Posted by shammat
    The problem (as far as I can tell) is that the Windows file system (NTFS) is a lot slower than the really fast ones on Linux which is a crucial part for a DBMS server. But I'm neither a Windows nor a Linux expert. The server version of NTFS might(?) be different to the NTFS version used on the "workstations".

    The folks on the postgres mailing list usually consider Windows slower than Linux and it wasn't until 8.2 (I think) that they called the Windows version stable enough for production use.

    Postgres heavily relies on the file cache of the file system (unlike SQL Server or Oracle which handle their own caching). So you should put a lot of memory into the server, which kind of requires a 64bit Windows.

    As far as I know there is no 64bit version of Postgres available for Windows, so I don't know if that would limit the performance (but at last a 64bit Windows would be able to use the large memory for the file cache and Postgres definitely benefits from that).

    Postgres is not a "drop-in" replacement for SQL Server. An application that was tuned for SQL Server might be slow on Postgres (or Oracle). Which is true for the other direction as well.

    The "SQL Server way" of doing large updates with temp tables (to avoid locking) and things like that usually slow down a system with a better concurrency handling (i.e. using MVCC like Postgres, Firebird and Oracle).

    There are a lot of SQL syntax oddities in SQL Server that need to be taken care of as well (just thinking of + vs. ||).
    Postgres is also more strict when it comes to datatype checking and has less implicit casting.

    Migrating the application (and how it behaves) will be much more important (and challanging) to make it run fast on a non Microsoft DBMS, and might actually be the most difficult part.
    Thanks for the detailed feedback, it's helpful. Migrating the application has begun, and thanks to some good design it shouldn't been too hard (built in functions for string formatting, funneling all queries through the same code, etc etc)

    For performance, I guess we'll need to set it up, then run some clean performance tests to know for sure.

    We have 4GB RAM in the server currently, although we recently discovered SQL2000Std is only using 2GB of it (inherent limit) - I think the extra memory for Postgres should give it a head start regardless.

    I'll check out the postgres mailing list and see what they have to say too.

    -Brendan

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by brendan.hill
    I'll check out the postgres mailing list and see what they have to say too.
    You should do that definitely. There is no better place to ask for Postgres help.
    The developers and architects of Postgres are monitoring the list(s) and they are very helpful.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The other issue that I've heard about, is that pg on Windows servers don't scale up (as far numbers of concurrent users) as well as pg on linux does.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    May 2009
    Posts
    18
    Yes, I found several mentions of this. Apparently PostgreSQL on Windows struggles (or just totally fails) to handle more than 125 concurrent connections, because it's relying on the built in networking libraries rather than it's own (or something like that).

    I plan to religiously connection pool it with less than 50 concurrent connections.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •