Samuel,
> About managing disk access and memory, do you know how to ensure the
> "read-only (to do the search on)" tables(i.e. product catalog) loaded
> and
> stay loaded in memory (provided buffer size is big enough for those
> tables)
> ? Are you familiar with RAMDISK ?
Yeah, I know about doing this with MySQL. However, Postgres is a
transactional database (MySQL is not), and as such it's a little
trickier. My suggestion would be to create the RAMDISK as a mounted
volume, and then copy the tables there. For that matter, I'd put your
transaction log (WAL_FILES) on a different RAMDISK, which would speed
up reads and writes considerably.
As far as I know, nobody has ever tried this before. It would be
extremely risky ... if you lost power, the entire database would be
dead and not retrieveable except from backup ... but in theory could be
very, very fast. A much less experimental approach would be to install
a really good RAID array for your machine on a very fast SCSI
controller, or two.
Where you go from here depends on what you have more of ... time or
money. If it's time, please experiment with RAMDISKs and write up your
tests for Techdocs. You can get a considerable amount of help from the
lists if you promise to write up your results as a HOWTO. If you have
more money than time, hire PostgreSQL Inc. or RedHat to help you build
the server and maximize performance.
If you have neither time nor money, I'd say use MySQL on a RAMDISK for
the read-only tables and do your joins in your interface code. The
RAMDISK technique is well documented and tested on MySQL, and after all
that's what MySQL was built for ... fast, read-only access.
> On different subject, do you know how to monitor the number of
> connections
> in PostgreSQL ?
Depends on how you're connecting.
-Josh Berkus