PostgreSQL vs 64-bit windows

Several completely independent people have asked me this in email already, so I figured it's about time for a blog post on the topic of PostgreSQL on 64-bit versions of Windows.

First for the simple answer: No, there is no 64-bit version of PostgreSQL for Windows. PostgreSQL has supported 64-bit environments on Unix for many years (long before we had x64 to make it available for wintel machines), but there is no Win64 port. Yet. And given the way that PostgreSQL is developed, there is no firm date for when this will be available.

Until then, though, the 32-bit version of PostgreSQL runs perfectly fine on 64-bit Windows. And doing so may actually be a better idea in some cases than a native 64-bit version. But most importantly, the architecture of PostgreSQL makes the requirement for a native 64-bit version much less than for many other competing databases. Particularly:

  • PostgreSQL relies on the operating system to do much of the caching of data. Since the main limitation of running a 32-bit process is the total amount of memory being addressable, a system that is based on the database engine doing all the caching cannot address all the memory on a system with say 16Gb memory. For PostgreSQL, we leave much of that caching to the operating system which can address it, as long as the OS is 64-bit.

  • PostgreSQL uses a multi-process architecture, not multi-thread. I'm not going to get into the discussion about which is better, just summarize by saying there are good and bad points to both of them. In this case it's a good thing. In a multi-threaded database server, all client connections share the memory space, and are again limited by the total addressable memory region. With PostgreSQL, you could easily have more than 1Gb per backend (if necessary) without running out of memory, which significantly pushes away the point when you need to go to 64-bit.

So why would it ever be better to run a 32-bit version than a 64-bit version? Simply because a 64-bit version is more memory-hungry. Every pointer used in the system takes twice as much space. Every int takes twice as much space. And if you're not actually going be able to cash in on the benefits (by addressing loads of memory, for example), it's a very real cost.

I'm well aware that this is a fairly simplistic view on the 32- vs 64-bit situation in general, but it should be enough to explain why 32-bit PostgreSQL on 64-bit Windows isn't such a bad idea after all. And of course, this only applies if you are using an x64 version of Windows - with Itanium it's a whole different story.


Comments

Ok, but what about the more CPU registers? The 8 more general purpose registers should definitely increase performance.

Posted on Dec 15, 2008 at 22:06 by Anton.

But what about the need for 64 bit Postgres libraries. I need access to the postgres libraries from a 64 bit matlab environment (via mex wrappers) which cannot interface with the 32 bit libraries.

Posted on Jan 13, 2009 at 01:07 by .

How about the ODBC drivers for postgresql on a windows 64 bit?

Posted on Feb 25, 2009 at 14:52 by .

Need 64 bit client libraries here too! Maybe there is a 'simple' way to make the 64bit client library?

Posted on May 25, 2009 at 11:20 by Anon.

This is being worked on to be included in stackbuilder in the future. Until then, if you have a 64-bit development environment it's pretty easy to build it - see the chapter on Windows installation in the main documentation.

Posted on May 26, 2009 at 08:38 by Magnus Hagander.

As far as ODBC What about these ones -- I've been using these on 64-bit? Had some finicky issues with this one: http://code.google.com/p/visionmap/wiki/psqlODBC -- this one ironically seems t work a little better though its older and more experimental: http://www.geocities.jp/inocchichichi/psqlodbc/index.html As described here: http://www.postgresonline.com/journal/index.php?/archives/46-Setting-up-PostgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.html

Posted on Jan 11, 2010 at 04:06 by Leo.

Sorry for my English But my server has 12G ram and install windows 2008 R2. Can postgresql support larger than 4G ram?

Posted on Dec 3, 2010 at 16:49 by Tuan Hoang Anh.

Any VM base caching is limited to the maximal mappable address space(which is about 4G in windows 64-bit, 3GB in 32-bit) The rest is filesystem level cache, which probably has a lot lower hitratio than caches on app level. Btw, does postgres support PXE extensions (36-bit, though buffers need to be manually mapped in, up to 64GB) (;-) )

Posted on Mar 26, 2012 at 19:57 by Marcov.

You are missing the point - which is exactly that the filesystem level cache does *not* have a lower cache hit ratio in the context of a postgresql server. We do need a certain amount of shared buffers inside postgresql, but beyond that the filesystem cache does an excellent job. No, PostgreSQL does not, and never will, support PXE. These days, just use the 64-bit version if you need more memory.

Posted on Apr 19, 2012 at 08:22 by Magnus Hagander.

Users who are challenged by the memory taxing qualities of the 64 bit should go back to using 32 bit. It offers smoother functionality. When talking about costs, 32 bit is lower as well, so that is definitely another fine point to note.

Posted on Jun 5, 2012 at 07:04 by Jim Smithson.

I agree totally, the 64-bit version definitely solves the problem if you need more memory. We need to gradually shift away from the 32 bit model as it is getting cheaper to have more memory and we should utilize that.

Posted on Jun 6, 2012 at 05:23 by Matt Spencer.