Should I install 32-bit database or 64-bit database?

Solution 1:

under what conditions that it starts to make sense to use 64-bit installations. Sorry - what good would be installing a 32 bit database on a 64 bit server? And - imagine - SERVER 2008R2 is ONLY available in 64 bit.

There is - today - no scenario where it makes sense to install a 32 bit SQL Server version if one has a chance.

Databases are specific in this - as they want to use a lot of memory as cache if necessary. A lot more than the meager 2gb / 3gb a 32 bit process can give them. PAE is not the same. Even ignoring limits, PAE memory is not equal to real memory for a SQL Server´(it is only used for ONE thing - caching db pages).

32 bit OS - is on the same level. it makes no sense on modern hardware at all to install a 32 bit OS.

Solution 2:

PostgreSQL benefits from having a 64-bit build in two main ways. First, data types that can fit into 64-bits (larger integers and timestamp types mainly) can be more efficiently passed around directly in registers rather than using pointers. Second, it's possible to allocate more memory for the database's dedicated buffer cache. The point of diminishing returns on that tunable (shared_buffers) is usually around 8GB, but it will be limited to <2GB on a 32-bit system.

However, if you are on Windows, PostgreSQL doesn't handle shared memory as efficiently as on UNIX-ish platforms. The point of diminishing return generally ends up being <=512MB of dedicated memory for the database whether you have a 32-bit or 64-bit build of PostgreSQL. You'll do better to leave the rest for the operating system cache rather than dedicate it to the database. Accordingly, there really isn't that much of a performance gain going from 32 to 64 bits with PostgreSQL on Windows; the main tunable that would normally benefit from having more RAM available doesn't actually utilize it very well.