Thread: Transactional vs. Read-only (Retrieval) database

Sam,

> I'd like to get some opinions if there are any benefits (i.e. in
> terms of performance) of creating two separate databases, one for
> transactions (insert, update, delete) and the other one is for
> retrieval/search (select).

No.  Not in Postgresql, unless you wanted to get really exotic.  Also,
keep in mind that you cannot join tables from seperate databases in
postgresql.

There are *lots* of performance advantages to be had by managing disk
access and memory carefully. But not through seperate databases.

-Josh Berkus

Hi Josh,

Thank you very much for your input.

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 ?

On different subject, do you know how to monitor the number of connections
in PostgreSQL ?

Regards,
Samuel

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>;
<pgsql-general@postgresql.org>; <pgsql-sql@postgresql.org>
Sent: Thursday, April 11, 2002 7:16 PM
Subject: Re: [SQL] Transactional vs. Read-only (Retrieval) database


> Sam,
>
> > I'd like to get some opinions if there are any benefits (i.e. in
> > terms of performance) of creating two separate databases, one for
> > transactions (insert, update, delete) and the other one is for
> > retrieval/search (select).
>
> No.  Not in Postgresql, unless you wanted to get really exotic.  Also,
> keep in mind that you cannot join tables from seperate databases in
> postgresql.
>
> There are *lots* of performance advantages to be had by managing disk
> access and memory carefully. But not through seperate databases.
>
> -Josh Berkus
>

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

Le Jeudi 11 Avril 2002 15:14, Samuel J. Sutjiono a écrit :
> I'd like to get some opinions if there are any benefits (i.e. in terms of
> performance) of creating two separate databases, one for transactions
> (insert, update, delete) and the other one is for retrieval/search
> (select).

Dear Samuel,

You do not need to create two separate databases. It is possible to write
server-side code to copy static data (read-only) to other tables. This can be
done using triggers and/or cron jobs.

A more sopisticated solution would be to store LEFT JOIN query results in the
original table using triggers. For example : you have tables COMPANY and
EMPLOYEE. Instead of running a LEFT JOIN on EMPLOYEE->COMPANY, it is possible
to store COMPANY name in table employee using a trigger.

Then, you don't need LEFT JOINS any more, simple SELECTs are enough, which is
fantastic for heavy loaded databases.

Same as for many operations. Try the EXPLAIN clause on a query. If too heavy,
try to write some code to simplify the query using triggers...

PostgreSQL is the most advanced open-source database available today. It has
several server-side languages (plpgsql, plperl, plpython). Maybe you should
try plpgsql which is easy to learn.

Well-optimized, PostgreSQL can run 10 times faster than well-known open-source
databases. If you need to test server-side programming, do not hesitate to
download and install pgAdmin2 (http://pgadmin.postgresql.org), PostgreSQL
Windows GUI.

Cheers,
Jean-Michel