Stephen,
You're a legend! That is exactly the answer I needed to hear from someone who actually knows the score. I am now powering on with Plan B: Postgres64 on Linux64.
After relentless searching I have become fairly convinced that a stable release of 64-bit postgres for Windows doesn't exist yet. (I welcome anyone to show me otherwise). Since I am committed to postgres (already written the code, it works just not quickly), I will have to change my OS.
However, if these performance limitations on Windows were apparent to me from the start, I probably would have chosen MS SQL Server over Postgres (less pain to get the gain). Perhaps this is an argument in favour of 64-bit Windows port to be added to this list:
http://wiki.postgresql.org/wiki/64bit_Windows_portThanks again for all your help.
Tom
On 2 June 2010 15:27, Stephen Frost
<sfrost@snowman.net> wrote:
Tom,
> The impression I was getting from Magnus Hagander's blog was that a 32-bit
> version of Postgres could make use of >4Gb RAM when running on 64-bit
> Windows due to the way PG passes on the responsibility for caching onto the
> OS.. Is this definitely not the case then?
Eh, sure, the data will be cache'd in the Windows OS, so more data will
be in memory, but you're never going to be able to use more than 4G for
any actual *processing*, like sorting, doing hash joins, having data in
shared buffers (to avoid having to go back to the OS and doing a system
call to get the data from the OS's cache..).
Not only that, but the shared_buffers are in *every* backend, so while
you'll only use 512MB for shared_buffers total, each backend will only
have 3.5G (or so) of memory to do 'other stuff'.
On a box with 16GB that's doing alot of relatively small activities
(OLTP type stuff), PG will work "alright". On a box with 96G with
terrabytes of data where you want to do data warehousing kind of work,
running a 32bit version of PG is going to suck.
Thanks,
Stephen
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAkwGalUACgkQrzgMPqB3kigWugCfY411is3uy3grP6lSH3P+neaf
evYAn2vY8/V3GntpQA9Q434U79+GThSW
=ar57
-----END PGP SIGNATURE-----