Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Ultimate DB Server

From: Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>
To: mlw <markw(at)mohawksoft(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, "Mike Rogers" <temp6453(at)hotmail(dot)com>
Subject: Re: [HACKERS] Ultimate DB Server
Date: 2001-10-29 08:10:04
Message-ID: 4.2.0.58.20011029085200.00ce5600@pop.freesurf.fr (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-generalpgsql-hackers
If you answer, please email to pgsql-general(at)postgresql(dot)org

**************************************************************************** 
***************************
>Server side programming is a double edged sword. PostgreSQL is not a
>distributed database, thus you are limited to the throughput of a single
>system. Moving processing off to PHP or Java on a different system can reduce
>the load on your server by distributing processing to other systems. If 
>you can
>cut query execution time by moving work off to other systems, you can
>effectively increase the capacity of your database server.

Yes, but for the Web, SQL queries are SELECT with LEFT JOINS to get display 
values of OIDs.
If you store LEFT JOIN results using triggers, you divide complexity by a 
factor of 10.

MySQL
A simple example would be :
SELECT customer_name, category_name FROM customer_table WHERE customer_oid 
= xxx
LEFT JOIN customer_category ON customer_oidcategory = category_oid;

PostgreSQL
Because Categories do not change a lot, it is possible to create a 
category_name_tg field
table customer_table and store the value using a trigger. As UPDATE account 
for 5% of all queries, it is not a real overhead.

To maintain consistency, you also add a customer_timestamp to 
customer_table. When Category value changes all you need to do is: UPDATE 
customer_table SET customer_timestamp = 'now' WHERE customer_oidcategory = yyy;

Under PostgreSQL, your query becomes
SELECT customer_name, customer_category_tg FROM customer_table WHERE 
customer_oid = xxx

>Typically, on a heavily used database, you should try to limit server side
>programming to that which reduces the database work load. If you are moving
>work, which can be done on the client, back to the server, you will bottleneck
>at the server while the client is sitting idle.

I do not always agree server-side programming should be limited. It 
depends. In some cases yes, in some cases no. Optimization is a progressive 
task, where you start with basic things and end up with more complex 
architecture. For what I noticed, 95% of applications were not truly 
optimized.

> > This is to say that, in some circomstances, PostgreSQL running on an i586
> > with IDE drive beats MySQL on a double Pentium. In real life, applications
> > are always optimized at software level first before hardware level. This is
> > why PostsgreSQL is *by nature* better than MySQL.
>
>One of the reasons why PostgreSQL beats MySQL, IMHO, is that it has the SQL
>features that allow you to control and reduce the database work load by doing
>things smarter.

Agreed, This is what I meant when I said PostgreSQL beat MySQL.

> > Unless MySQL gets better, there is no real challenge in comparing both 
> systems.
>
>It is funny, I know guys that love MySQL. Even when I show them the cool 
>things
>they can do with Postgres, they just don't seem to get it. It is sort of like
>talking to an Amiga user.

On heavy workload systems MySQL cannot compare to PostgreSQL. It's funny to 
read these mails
of people doing benchmarks.

In response to

pgsql-hackers by date

Next:From: Philip WarnerDate: 2001-10-29 09:23:39
Subject: Re: planner/optimizer question
Previous:From: Michael MeskesDate: 2001-10-29 08:00:58
Subject: Re: Deadlock? idle in transaction

pgsql-admin by date

Next:From: Craig JarmanDate: 2001-10-29 12:27:22
Subject: Error: Relation 'testdb' does not exist
Previous:From: Jean-Michel POUREDate: 2001-10-29 07:50:36
Subject: Re: Ultimate DB Server

pgsql-general by date

Next:From: Mark kirkwoodDate: 2001-10-29 08:53:56
Subject: Re: On Distributions In 7.2
Previous:From: John FabianiDate: 2001-10-29 08:08:06
Subject: Re: How to know PostgreSql is install in Linux system.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group