Re: [HACKERS] Ultimate DB Server

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.