Received: from www1.translationforge.com (unknown [212.11.36.58]) by postgresql.org (Postfix) with ESMTP id ACF6D476B82; Thu, 11 Apr 2002 10:25:26 -0400 (EDT) Received: from linus.translationforge.com ([192.168.1.102]) by www1.translationforge.com (8.12.1/8.12.1) with ESMTP id g3BEPa83018802; Thu, 11 Apr 2002 16:25:36 +0200 Content-Type: text/plain; charset="iso-8859-1" From: Jean-Michel POURE Reply-To: jm.poure@freesurf.fr Organization: Translationforge To: "Samuel J. Sutjiono" , , Subject: Re: Transactional vs. Read-only (Retrieval) database Date: Thu, 11 Apr 2002 16:25:34 +0200 X-Mailer: KMail [version 1.4] References: <003801c1e15a$c03c5450$110a010a@headquarters.wcgroup.com> In-Reply-To: <003801c1e15a$c03c5450$110a010a@headquarters.wcgroup.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200204111625.34863.jm.poure@freesurf.fr> X-Archive-Number: 200204/445 X-Sequence-Number: 23161 Le Jeudi 11 Avril 2002 15:14, Samuel J. Sutjiono a =E9crit : > 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= =20 server-side code to copy static data (read-only) to other tables. This can = be=20 done using triggers and/or cron jobs. A more sopisticated solution would be to store LEFT JOIN query results in t= he=20 original table using triggers. For example : you have tables COMPANY and=20 EMPLOYEE. Instead of running a LEFT JOIN on EMPLOYEE->COMPANY, it is possib= le=20 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=20 fantastic for heavy loaded databases. Same as for many operations. Try the EXPLAIN clause on a query. If too heav= y,=20 try to write some code to simplify the query using triggers... PostgreSQL is the most advanced open-source database available today. It ha= s=20 several server-side languages (plpgsql, plperl, plpython). Maybe you should= =20 try plpgsql which is easy to learn. Well-optimized, PostgreSQL can run 10 times faster than well-known open-sou= rce=20 databases. If you need to test server-side programming, do not hesitate to= =20 download and install pgAdmin2 (http://pgadmin.postgresql.org), PostgreSQL= =20 Windows GUI. Cheers, Jean-Michel