pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Transactional vs. Read-only (Retrieval) database
5+ messages / 3 participants
[nested] [flat]

* Transactional vs. Read-only (Retrieval) database
@ 2002-04-11 13:14 Samuel J. Sutjiono <ssutjiono@wc-group.com>
  2002-04-11 14:25 ` Re: Transactional vs. Read-only (Retrieval) database Jean-Michel POURE <jm.poure@freesurf.fr>
  2002-04-11 23:16 ` Re: [SQL] Transactional vs. Read-only (Retrieval) database Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Samuel J. Sutjiono @ 2002-04-11 13:14 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql

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).

Thanks in advance for your input.
Sam
_________________________________________________
 Expand your wireless world with Arkdom PLUS
 http://www.arkdom.com/

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Transactional vs. Read-only (Retrieval) database
  2002-04-11 13:14 Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-04-11 14:25 ` Jean-Michel POURE <jm.poure@freesurf.fr>
  1 sibling, 0 replies; 5+ messages in thread

From: Jean-Michel POURE @ 2002-04-11 14:25 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-general@postgresql.org; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Transactional vs. Read-only (Retrieval) database
  2002-04-11 13:14 Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-04-11 23:16 ` Josh Berkus <josh@agliodbs.com>
  2002-04-12 14:51   ` Re: [SQL] Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2002-04-11 23:16 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-general@postgresql.org; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Transactional vs. Read-only (Retrieval) database
  2002-04-11 13:14 Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
  2002-04-11 23:16 ` Re: [SQL] Transactional vs. Read-only (Retrieval) database Josh Berkus <josh@agliodbs.com>
@ 2002-04-12 14:51   ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
  2002-04-12 17:12     ` Re: Transactional vs. Read-only (Retrieval) database Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Samuel J. Sutjiono @ 2002-04-12 14:51 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org; pgsql-sql

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
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Transactional vs. Read-only (Retrieval) database
  2002-04-11 13:14 Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
  2002-04-11 23:16 ` Re: [SQL] Transactional vs. Read-only (Retrieval) database Josh Berkus <josh@agliodbs.com>
  2002-04-12 14:51   ` Re: [SQL] Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-04-12 17:12     ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2002-04-12 17:12 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-04-12 17:12 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-11 13:14 Transactional vs. Read-only (Retrieval) database Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-04-11 14:25 ` Jean-Michel POURE <jm.poure@freesurf.fr>
2002-04-11 23:16 ` Josh Berkus <josh@agliodbs.com>
2002-04-12 14:51   ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-04-12 17:12     ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox