Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #133 > unrolled thread

migrating oracle to postgres

Back to article view | Back to comp.databases.postgresql

Contents

  migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 19:28 +0200
    Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 20:08 +0000
      Re: migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 23:45 +0200
        Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 22:37 +0000
        Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 02:01 +0000
    Re: migrating oracle to postgres "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-21 09:18 +0200
      Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 13:38 +0000
        Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-22 12:30 +0200
          Re: Is PostgreSQL good? Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2011-06-22 13:15 +0200
          Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-22 12:02 +0000
            Re: Is PostgreSQL good? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-06-22 21:52 +0200
              Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-22 22:43 +0000
            Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 12:29 +0200
              Re: Is PostgreSQL good? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-06-24 12:57 +0200
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 14:53 +0200
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-24 16:32 +0000
              Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-24 13:53 +0000
                Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-27 11:17 +0200
                  Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 06:11 +0000
                    Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-28 10:34 +0200
                      Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 13:30 +0000
                        Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-29 09:50 +0200
                          Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-29 21:38 +0000
                            Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-30 09:14 +0200
                              Re: Is PostgreSQL good? Mladen Gogala <mgogala@no.address.invalid> - 2011-06-30 13:22 +0000
              Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 07:05 -0700
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 15:41 +0000
                  Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-06-30 16:49 +0100
                    Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 19:24 +0000
                      Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:21 -0700
                        Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 22:42 +0000
                          Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:57 -0700
                            Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 22:58 +0000
                      Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-07-01 14:07 +0100
                        Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:29 +0000
                  Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:17 -0700
                    Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-30 23:53 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 10:13 +0200
                  Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:38 +0000
                    Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 16:58 +0200
                      Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-07-01 15:33 +0000
                  Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:39 -0700
                    Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-04 16:39 +0200
                      Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:11 -0700
                        Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-05 08:58 +0200
                          Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-05 11:29 -0700
                            Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-06 09:00 +0200
                              Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-06 01:00 -0700
                                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-07 09:05 +0200
                                  Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-07 00:34 -0700
                    Re: Is PostgreSQL good? Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-07-04 20:12 +0100
                      Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:24 -0700
        Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-23 17:48 +0200
          Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 16:42 +0000
            Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 18:57 +0000
            Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-26 17:35 +0200
              Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-26 19:08 +0000
            Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-27 00:32 -0700
              Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-28 16:00 +0000
                Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-28 22:28 +0200

Page 1 of 3  [1] 2 3  Next page →

#133 — migrating oracle to postgres

Hello group,

we are facing the task of migrating a large (600k LOC) oracle backend to 
postgresql. As i figured out by now is that we theoretically have two 
options:

1. teach Postgres to understand PL/SQL by using 3rd-party-software
2. convert oracle PL/SQL to PG PL/SQL

Has anyone of you already experience with such a job?
Where are problems to be expected?
Are there some tools for automated translating oracle PL/SQL to PG 
PL/SQL? How good are they?
Are there other options except the two above?
What option is to be preferred?

....and many, many more...

Thank you in advance.

Thomas

[toc] | [next] | [standalone]

#134

On Mon, 20 Jun 2011 19:28:45 +0200, Username wrote:

> Hello group,
> 
> we are facing the task of migrating a large (600k LOC) oracle backend to
> postgresql. As i figured out by now is that we theoretically have two
> options:
> 
> 1. teach Postgres to understand PL/SQL by using 3rd-party-software 2.
> convert oracle PL/SQL to PG PL/SQL
> 
> Has anyone of you already experience with such a job? Where are problems
> to be expected?
> Are there some tools for automated translating oracle PL/SQL to PG
> PL/SQL? How good are they?
> Are there other options except the two above? What option is to be
> preferred?
> 
> ....and many, many more...
> 
> Thank you in advance.
> 
> Thomas

I've had such a pilot project but was forced to give it up. There are 
several major snags with Postgres:
1) Postgres is the only major database without hints. That makes
   keeping any deadlines impossible and makes it necessary to rewrite 
   each and every SQL. Tuning SQL in Postgres is done by rewriting it
   and fixing the model, which takes time, in my case more time than
   I was alloted. They do have "set mode" statements for the session, 
   but that's in effect for the entire session. Granularity is wrong.
   
2) Postgres partitioning is not very robust. It's even weaker than
   Oracle7 partition views because partition elimination only happens
   during the parse phase. Once the statement is parsed, that's it. 
   Did I mention that there are no global indexes? Also, optimizer seems
   to be confused with partitioning and is frequently producing bad plans,
   based on the full table scan of the entire table.

3) The most important problem are the people leading the community. They
   stubbornly refuse to even talk about hints. Also, there is a closed
   source version of Postgres, called EnterpriseDB which has hints. One
   of the pillars of Postgres community is a guy who works for
   EnterpriseDB and is opposed to hints in the open source version but is
   merrily selling them in the closed source version. Here is a pearl of
   wisdom, contributed to this valley of tears by another shining pillar 
   of the Postgres community: http://tinyurl.com/68gu822
   To dispel any doubts, I am the DBA that this genius is talking about.
  
4) Postgres optimizer is bad. Statistics anomalies are frequent, there is 
   an email list devoted to Postgres performance and every other question 
   is "how to speed up this query". There is no tuning methodology, no 
   event interface, nothing.
5) There is no parallelism. Oracle can parallelize queries, Postgres can 
   not. It's as simple as that. There is no cure in sight.

There are some other problems, like not caching parsed SQL (no shared 
pool) but these were sufficient to cast serious doubt on Postgres as a 
viable option. I am an Oracle DBA for more than 20 years and recent 
changes in the pricing policy have motivated the company that I work for 
to look for alternatives, starting with OSS software. Sadly, there are no 
open source alternatives. The spirit in the Postgres community ensures 
that things will remain as they are, for the foreseeable future. The next 
step is to look for the commercial alternatives, like DB/2. However, if 
you are building a data warehouse, take a look at MongoDB. It's a NoSQL 
database which can be  used to create a very decent data warehouse.
If you want to rewrite Oracle code and use it on Postgres, forget it. 
It's going to be a frustrating and humiliating experience and will 
produce an underperforming application system. You can contact me 
directly, should you need any more information.




-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#135

Hello Mladen,

thank you for your detailed response.

> I've had such a pilot project but was forced to give it up.

Hmm. That's just *not* the answer was hoping to hear - but the one i 
expected.

> 1) Postgres is the only major database without hints.

The "hint-discussion" is not new to me. I am already following it for 
some time.

> 2) Postgres partitioning is not very robust.

OK. Good to know. But that would not be the show stopper in my case.

> 3) The most important problem are the people leading the community. They
>     stubbornly refuse to even talk about hints. Also, there is a closed
>     source version of Postgres, called EnterpriseDB which has hints.

Would EnterpriseDB be a better destination to migrate to from oracle?

> 4) Postgres optimizer is bad.

Let's assume that this would not be a problem to me.

> 5) There is no parallelism. Oracle can parallelize queries, Postgres can
>     not. It's as simple as that. There is no cure in sight.

Also good to know. But not critically to my szenario.

> I am an Oracle DBA for more than 20 years and recent
> changes in the pricing policy have motivated the company that I work for
> to look for alternatives, starting with OSS software.

That's exactly the situation that i am facing now.

Most of your critics on postgres refer to large amounts of data and 
perfomance critical applications. In my scenario the oracle-db neither 
uses hints or partitioned tables nor does performance matter that much. 
I "only" have the problem of porting the 600k LOC PL/SQL application 
logic (triggers, strored procedures etc.) to postgres.

Leaving your legitimate criticism on performance issues aside, and only 
focussing on the pl/sql code, would you then still stay with your 
introductory statement that it is impossible to migrate the code?

Thomas

[toc] | [prev] | [next] | [standalone]

#136

On Mon, 20 Jun 2011 23:45:38 +0200, Username wrote:

> Most of your critics on postgres refer to large amounts of data and
> perfomance critical applications. In my scenario the oracle-db neither
> uses hints or partitioned tables nor does performance matter that much.
> I "only" have the problem of porting the 600k LOC PL/SQL application
> logic (triggers, strored procedures etc.) to postgres.

If that is the case, I would try mixing PlPgSQ with PLPerl. PlPgSQL is 
far inferior to PL/SQL. No packages, no procedures, but with Perl you can 
get it all. With the untrusted version, you can call external modules. 
Before you start: RAISE NOTICE is the PLPgSQL equivalent of 
dbms_output.put_line.





-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#137

On Mon, 20 Jun 2011 23:45:38 +0200, Username wrote:


> Would EnterpriseDB be a better destination to migrate to from oracle?

That depends. It definitely has more useful goodies than the normal 
Postgres, but I am not sure how stable the company is. They didn't make a 
splash, EnterpriseDB is not becoming wildly popular, which is rather 
strange, given how unhappy people are with the Oracle pricing. I don't 
really know what seems to be the problem, but I am a DBA, not a business 
person.



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#138

Username wrote:
> we are facing the task of migrating a large (600k LOC) oracle backend to postgresql. As i figured out by now is that we 
> theoretically have two options:
>
> 1. teach Postgres to understand PL/SQL by using 3rd-party-software
> 2. convert oracle PL/SQL to PG PL/SQL
>
> Has anyone of you already experience with such a job?
> Where are problems to be expected?
> Are there some tools for automated translating oracle PL/SQL to PG PL/SQL? How good are they?
> Are there other options except the two above?
> What option is to be preferred?

I think that option 1 won't work.
You can look at EnterpriseDB, which is a closed source database based
on PostgreSQL that tries to be as similar to PostgreSQL as possible in
order to facilitate migration, but don't expect that you can just
go on using your PL/SQL procedures without modification.
Ask them, they'll be happy to tell you more.

Option 2 might or might not be feasible.

The syntax of PL/pgSQL is similar enough to PL/SQL that the code
itself won't be hard to port. You'll run into problems as soon as
you make heavy use of Oracle's goodies like the extensive PL/SQL
library, the LOB interface, autonomous transactions, bulk operations,
the object oriented cruft -- to name just a few of the more common
things that I can think of right away.

If your code uses these a lot, porting will be pretty difficult and
will probably end up in a rewrite. Note also that, different from PL/SQL,
PL/pgSQL is not designed to be an efficient all-purpose programming
language, it's designed for database manipulations where SQL won't suffice.
If you need to access the file system or other things outside the database,
it's better to use other languages like PL/Perl.

On the other hand, if your code is e.g. mostly triggers that modify
tables, porting might not be too difficult.

Yours,
Laurenz Albe

PS: Don't let people discourage you. PostgreSQL is a good and stable
database system. 

[toc] | [prev] | [next] | [standalone]

#139

On Tue, 21 Jun 2011 09:18:07 +0200, Laurenz Albe wrote:

> PS: Don't let people discourage you. PostgreSQL is a good and stable
> database system.

It is stable, no contest there. Is it good or not is a matter of opinion. 
I could list several grave faults which make it less than good in my eyes.
The first and foremost is, of course, the lack of hints. Postgres is the 
only major database system which lacks hints. The second is ludicrous 
need to "vacuum" database. The third is the fact that Postgres doesn't do 
multi-block reads. Fixed size archive logs (16M) would be the next issue. 
The lack of shared pool makes it impossible to see the plan of the 
statement being executed. And there is more.


-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#140 — Is PostgreSQL good? (was: migrating oracle to postgres)

Mladen Gogala wrote:
> It is stable, no contest there. Is it good or not is a matter of opinion.
> I could list several grave faults which make it less than good in my eyes.
> The first and foremost is, of course, the lack of hints. Postgres is the
> only major database system which lacks hints. The second is ludicrous
> need to "vacuum" database. The third is the fact that Postgres doesn't do
> multi-block reads. Fixed size archive logs (16M) would be the next issue.
> The lack of shared pool makes it impossible to see the plan of the
> statement being executed. And there is more.

You begin to sound like a broken record.
There are pros and cons to hints, and you can like them or not, but
from what you write it seems to be more of an emotional issue than anything
else, so I won't enter a discussion here.

A liberal use of pejoratives is no substitute for a technical argument.

I understand that with your Oracle background it is easy to think
that there is only one way to skin the cat, and other approaches are
"ludicrous".

Please don't turn into a troll.

Yours,
Laurenz Albe

PS: I have a question concerning Oracle's superior query optimizer.
I am dinking around with a "Foreign Data Wrapper" for the PostgreSQL 9.1
implementation of SQL/MED to access Oracle. Now it would be nice
to get optimizer information from Oracle and feed it back to PostgreSQL
so that it can be used for planing a query with a foreign table.

Is there any way to get a decent estimate how expensive Oracle thinks
a query might be? Something that can be expressed in units like blocks read
from disk or time spent? All I can find is the TIME column of PLAN_TABLE
which has a granularity of seconds (!) and is never less than 1. 

[toc] | [prev] | [next] | [standalone]

#141 — Re: Is PostgreSQL good?

Laurenz Albe, 22.06.2011 12:30:
> Please don't turn into a troll.

He's been in my killfile since about 1 year...

[toc] | [prev] | [next] | [standalone]

#142 — Re: Is PostgreSQL good? (was: migrating oracle to postgres)

On Wed, 22 Jun 2011 12:30:15 +0200, Laurenz Albe wrote:

> You begin to sound like a broken record. 

Of course. There is no change in the situation.

> There are pros and cons to
> hints, and you can like them or not, 

All other major databases have them. That should be enough for now.

> but from what you write it seems to
> be more of an emotional issue than anything else, so I won't enter a
> discussion here.

So what is this post all about, if you don't want to enter discussion?




-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#143 — Re: Is PostgreSQL good?

Am 22.06.2011 14:02, schrieb Mladen Gogala:
> On Wed, 22 Jun 2011 12:30:15 +0200, Laurenz Albe wrote:
> 
>> You begin to sound like a broken record. 
> 
> Of course. There is no change in the situation.
> 
>> There are pros and cons to
>> hints, and you can like them or not, 
> 
> All other major databases have them. That should be enough for now.
> 
>> but from what you write it seems to
>> be more of an emotional issue than anything else, so I won't enter a
>> discussion here.
> 
> So what is this post all about, if you don't want to enter discussion?
> 

In my understanding it is about asking you not to go completely
berserk.

/Str

[toc] | [prev] | [next] | [standalone]

#144 — Re: Is PostgreSQL good?

On Wed, 22 Jun 2011 21:52:24 +0200, M. Strobel wrote:

> In my understanding it is about asking you not to go completely berserk.

I am not going "berserk" at all, quite contrary. I am remaining polite, 
even in the face of posts like yours. It is not me who is refusing the 
debate. I do have a voice in the Oracle community and I will continue to 
warn people considering Postgres as a possible alternative to Oracle what 
are they up against, should they decide to try going down that road. 
There are many deficiencies of PostgreSQL and I'll gladly point them out 
to any Oraclite who is considering PgSQL as a possible replacement. The 
phrase about Postgres being an open source Oracle is a misleading lie.

If you want to try debating the possible reasons for not providing hints 
to Postgres users, I'll gladly debate it with you. Frankly, I don't think 
it makes much sense, the spirit of Postgres community is far from 
instilling confidence. Maybe that's why PGEast was such a flop? By the 
way, what do you think, why is Postgres so far behind in popularity 
behind MySQL? I just want to be the voice of reason, even at the risk of 
sounding like a broken record. 

Lastly, this forum is not moderated and the only way I can be dealt with 
is the force of arguments. From what I've seen so far, there is no danger 
of that ever happening. I would even suggest that rude posts like 
Laurenz's or yours, in which you accuse a complete stranger of "sounding 
like a broken record" or "going berserk", are more akin to trolling than 
polite and rational posts of the kind I am attempting to promote here.

I don't intend to get into a shouting match with either of you, but if 
somebody asks about conversion from Oracle, you can count on me being 
here. Again, the only way to make me go away is the force of arguments.



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#148 — Re: Is PostgreSQL good? (was: migrating oracle to postgres)

Mladen Gogala wrote:
> So what is this post all about, if you don't want to enter discussion?

I have no problem to discuss hints and other perceived or real shortcomings
of PostgreSQL, only before I do this I wanted to ascertain that you are still
able to post a civil reply. I wanted to bring the heat level down.

I think that founded criticism of PostgreSQL is not off-topic here, and
I have no problem with it. Yet lately you have spread a great deal of
FUD around here.

So here I go.

About hints:
============

Let me say that I personally do not feel very strongly
either way - I have used hints in Oracle and get by without them in PostgreSQL.

To begin, I searched the archives to verify your (insulting) claims about
what the PGDG thinks about query hints. Here is a good statement I've found:
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00436.php
And there is of course the entry in the TODO list that states that optimizer
hints are not wanted. It points to three threads:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00506.php
http://archives.postgresql.org/pgsql-hackers/2006-10/msg00517.php
http://archives.postgresql.org/pgsql-hackers/2006-10/msg00663.php

Sifting through some of the e-mails, here is a list of some arguments against
query hints that I found:
- They create a maintenance burden whenever there is a change in the optimizer.
- They are a hack that does not address the real problem (a deficiency in the
  planer) and may not work well in the next version or when the data change.
- They will cause people to work around deficiencies in the planer, rather than
  report their problem so it can be fixed.

The first point is something that could be handled if more able people were
willing to put resources into it. The third point is probably more idealistic
than a commercial vendor would be (fewer bug reports mean less work for support).
Still I think all three are valid points.

The only recognizable argument you made so far is "everybody else has them,
so they must be the right thing". PostgreSQL hackers will reply that they are
out to do things *better* than everybody else, so while it is certainly a good
idea to seriously consider something that everybody else does, that does not
dictate that PostgreSQL has to do it the same way.

If you really want to influence the PostgreSQL planer, there is a number of
configuration parameters that allow you to do this (only for a whole query though).
Also, a query rewrite can often do that same thing that a hint would do
(didn't you post an example recently?).

Interestingly, Oracle also seems to think that hints are not so great.
I quote from
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF00219

  Hints were introduced in Oracle7, when users had little recourse if the
  optimizer generated suboptimal plans. Now Oracle provides a number of tools,
  including the SQL Tuning Advisor, SQL plan management, and SQL Performance
  Analyzer, to help you address performance problems that are not solved by
  the optimizer. Oracle strongly recommends that you use those tools rather
  than hints. The tools are far superior to hints, because when used on an
  ongoing basis, they provide fresh solutions as your data and database
  environment change.

  Hints should be used sparingly, and only after you have collected statistics
  on the relevant tables and evaluated the optimizer plan without hints using
  the EXPLAIN PLAN statement. Changing database conditions as well as query
  performance enhancements in subsequent releases can have significant impact
  on how hints in your code affect performance.

So it seems that Oracle considers hints a legacy from when their optimizer was
even worse and they had nothing better to offer.

About vacuum:
=============

Of course vacuum is a pain, but every database system has to pay a similar
price somewhere. Either you reduce concurrency (DB2), or (if you use some kind
of MVCC) you can move dead rows somewhere else (Oracle) or leave them where
they are and clean up later.

Each approach has its disadvantages.

Oracle has to burn extra cycles (and disk access?) to move rows away (and maybe
back) right when the performance might be needed elsewhere. Moreover, there's
the problem of having to maintain an extra data structure (undo space) which
may run out, causing an expensive long-running operation to fail randomly.

PostgreSQL's VACUUM is of course also unpleasant, but the pain has become much
less with the greatly improved autovacuum of recent versions. Normally, you don't
have to worry about it any more.
The design principle behind VACUUM is that the maintenance burden is delayed
to a time when the system is not totally busy, so that performance is not affected
by it.

ROWID:
======

You make the point that because of VACUUM there is nothing compared to
Oracle's ROWID. But do you really need that? Shouldn't a row be referenced
by its primary key?

Oracle itself says in
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/pseudocolumns008.htm#SQLRF00254

  You should not use ROWID as the primary key of a table. If you delete and
  reinsert a row with the Import and Export utilities, for example, then its
  rowid may change. If you delete a row, then Oracle may reassign its rowid
  to a new row inserted later.

Other maintenance commands like ALTER TABLE ... SHRINK SPACE will also change
ROWID.

Oracle pays a price to keep ROWID as stable as it is. Why? Because reference via
ROWID is faster than anything else? Why do you need that outside of the scope of
one transaction? And inside one transaction, PostgreSQL's ctid should do the trick
for you.

I do not understand how lack of ROWID should affect global indexes on partitioned
tables. Can you explain?

Fixed size of WAL segments:
===========================

Maybe I am dense, but what is the problem there?

Yours,
Laurenz Albe 

[toc] | [prev] | [next] | [standalone]

#149 — Re: Is PostgreSQL good?

Laurenz Albe wrote on 24.06.2011 12:29:
> You make the point that because of VACUUM there is nothing compared to
> Oracle's ROWID.

PG's ctid is very similar as Oracle's ROWID (as similar as things like that can be between completely different architectures)

[toc] | [prev] | [next] | [standalone]

#150 — Re: Is PostgreSQL good?

Hans Castorp wrote:
>> You make the point that because of VACUUM there is nothing compared to
>> Oracle's ROWID.
>
> PG's ctid is very similar as Oracle's ROWID (as similar as things like
> that can be between completely different architectures)

I mentioned that. I am afraid that my posting was too long and maybe not
organized enough so that it got lost in the noise. Sorry.

Yours,
Laurenz Albe 

[toc] | [prev] | [next] | [standalone]

#152 — Re: Is PostgreSQL good?

On Fri, 24 Jun 2011 12:57:11 +0200, Hans Castorp wrote:

> PG's ctid is very similar as Oracle's ROWID (

Unfortunately, it's only valid within a transaction. There is no 
permanent rowid in Postgres. This wouldn't be a problem in itself because 
one should never use it as a primary key. This only presents a problem 
because the lack of the permanent rowid is what prevents Postgres from 
allowing global indexes on the partitioned tables.
If you do "another level of redirection" as the old joke says, you have 
to access block headers and possibly do another I/O to read the row 
itself, which makes things slower. In a large partitioned table, with 
hundreds of millions of records, every row counts and you need all the 
speed you can get. 
I created global indexes on a partitioned table using Sphinx. Sphinx 
requires a bigint primary key, which was available here and the index 
works well. However, I cannot enforce the primary key or have a globally 
unique key without a ROWID. If there an implementation of global indexes 
without the unique row identifier, I don't need it any more. What I am 
talking about is a table like this:


news=# select count(*) from moreover_documents;
   count   
-----------
 181491106
(1 row)

             Table "moreover.moreover_documents"
        Column        |            Type             | Modifiers 
----------------------+-----------------------------+-----------
 document_id          | bigint                      | not null
 dre_reference        | bigint                      | not null
 headline             | character varying(4000)     | 
 author               | character varying(200)      | 
 url                  | character varying(1000)     | 
 rank                 | bigint                      | 
 content              | text                        | 
 stories_like_this    | character varying(1000)     | 
 internet_web_site_id | bigint                      | not null
 harvest_time         | timestamp without time zone | 
 valid_time           | timestamp without time zone | 
 keyword              | character varying(200)      | 
 article_id           | bigint                      | not null
 media_type           | character varying(20)       | 
 source_type          | character varying(20)       | 
 created_at           | timestamp without time zone | 
 autonomy_fed_at      | timestamp without time zone | 
 language             | character varying(150)      | 
Indexes:
    "moreover_documents_pkey" PRIMARY KEY, btree (document_id)
Triggers:
    insert_moreover_trigger BEFORE INSERT ON moreover_documents FOR EACH 
ROW EXECUTE PROCEDURE moreover_insert_trgfn()
Number of child tables: 10 (Use \d+ to list them.)

I need, global index and global stats, as well as the optimizer capable 
of dealing with that.


-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#151 — Re: Is PostgreSQL good? (was: migrating oracle to postgres)

On Fri, 24 Jun 2011 12:29:19 +0200, Laurenz Albe wrote:

> Mladen Gogala wrote:
>> So what is this post all about, if you don't want to enter discussion?
> 
> I have no problem to discuss hints and other perceived or real
> shortcomings of PostgreSQL, only before I do this I wanted to ascertain
> that you are still able to post a civil reply. I wanted to bring the
> heat level down.

You wanted to bring the level down by telling me that I sound like a 
broken record? I do find that interesting.

> 
> I think that founded criticism of PostgreSQL is not off-topic here, and
> I have no problem with it. Yet lately you have spread a great deal of
> FUD around here.
> 
> So here I go.
> 
> About hints:
> ============
> 
> Let me say that I personally do not feel very strongly either way - I
> have used hints in Oracle and get by without them in PostgreSQL.

Unfortunately, I was forced to kill a porting project because I couldn't 
have make the SQL perform properly within the allotted time frame. The 
goal was to make an oracle application written in Java work against 
Postgres. The tables and indexes were converted using ora2pg. There were 
few triggers which I recoded manually. However, the performance was 
abysmal because of bad plans and the only chance to make the deadline 
was  to have a mechanism to force specific plans. I didn't have that. I 
was even reasonable enough to suggest that such mechanism should be only 
put on TODO list, but nobody wanted to even discuss that. I was 
steamrolled, so I killed the project and stopped all the plans for 
porting applications. In the end, we both lost: Postgres lost a user, I 
lost an opportunity to save some money for the company.


> Sifting through some of the e-mails, here is a list of some arguments
> against query hints that I found:
> - They create a maintenance burden whenever there is a change in the
> optimizer. - 

They do. The optimizer itself is a maintenance burden. Every new feature 
is a maintenance burden.

> They are a hack that does not address the real problem (a
> deficiency in the   planer) 

This argument is ludicrous. Hints are the way around the deficiencies in 
the planner. Achieving the "perfect optimizer" is impossible at this 
state of technology. Oracle has excellent optimizer and vast resources 
and yet it provides hints. Hints are a solution of the last resort. 
Undesired and unloved, but it has to be there when necessary. If they are 
not there, the database will not get used.

> and may not work well in the next version or when the data
>   change.

Well, that's up to the maintainers. However, the type of decisions 
influenced by hints is unlikely to change between versions.

> - They will cause people to work around deficiencies in the planer,
> rather than
>   report their problem so it can be fixed.

Unfortunately, the real developers and project managers work on projects 
that have deadlines. Users must have tuning methodology. Postgres will 
have to trust its users to report problems. On the other hand, if the 
community doesn't trust its users, it is very likely that it will not 
have many of them. Personally, I don't think that posting the problem on 
the mailing list is a satisfactory tuning methodology. I'd rather kill 
the project than do it that way. So I did.


> 
> The first point is something that could be handled if more able people
> were willing to put resources into it. The third point is probably more
> idealistic than a commercial vendor would be (fewer bug reports mean
> less work for support). Still I think all three are valid points.

On the other hand, I interpret those points as a deep mistrust of the 
Postgres community to its users and the reluctance to provide the 
necessary tools for the users to have chance to help themselves. 


> The only recognizable argument you made so far is "everybody else has
> them, so they must be the right thing". 

No. The argument that I have made is not about everybody else. That 
"argument" was thrown when I was faced with the "everybody else is 
against" argument. The real argument is that hints are needed and that 
the users must have a way of helping themselves. What you and the rest of 
the Postgres community are arguing against is allowing users to help 
themselves. Essentially, Postgres is the only database that doesn't allow 
its users to help themselves. That is the argument.

> PostgreSQL hackers will reply
> that they are out to do things *better* than everybody else, so while it
> is certainly a good idea to seriously consider something that everybody
> else does, that does not dictate that PostgreSQL has to do it the same
> way.

Let's be realistic here. Postgres is one of the available RDBMS systems 
on the market, far behind other systems in popularity, even behind MySQL. 
Nobody uses PgSQL for benchmarks (see www.tpc.org) because of the 
performance problems. Such grandeur is not justified. The goal must be to 
give the users something that they can use as an alternative to the much 
better performing commercial databases like Oracle or DB2. In other 
words, Postgres must be an alternative to Oracle. At present, it isn't. I 
tried, I failed and looked elsewhere. I am not the only one, I am just 
the most vocal one.
When NY PUG was revived in 2010, there were people from BoA, Goldman 
Sachs, Verizon and other major NYC companies. They stopped coming to the 
meetings and PGEast in NYC in March this year was a flop. I haven't 
noticed any attempts to figure out what was the problem.


> 
> If you really want to influence the PostgreSQL planer, there is a number
> of configuration parameters that allow you to do this (only for a whole
> query though). Also, a query rewrite can often do that same thing that a
> hint would do (didn't you post an example recently?).

Yes, I did post an example. There are limited cases, but that is not 
enough. There must be a way of making SQL perform adequately in minutes. 
If a VP is looking over your shoulder and asking "is it working yet", 
there is no time to wait for Tom Lane to respond. 

> 
> Interestingly, Oracle also seems to think that hints are not so great. I
> quote from
> http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/
sql_elements006.htm#SQLRF00219

Don't get me wrong. Hints are the last resort, the solution used only in 
desperation. However, they must be there and they are there, at least in 
Oracle. Real life situations, with real life problems also makes 
desperate situations highly probable. As DBAs, we are frequently in dire 
straits, and have to be the sultans of swing. And we can't swing if don't 
have hints.


> 
>   Hints were introduced in Oracle7, when users had little recourse if
>   the optimizer generated suboptimal plans. Now Oracle provides a number
>   of tools, including the SQL Tuning Advisor, SQL plan management, and
>   SQL Performance Analyzer, to help you address performance problems
>   that are not solved by the optimizer. Oracle strongly recommends that
>   you use those tools rather than hints. The tools are far superior to
>   hints, because when used on an ongoing basis, they provide fresh
>   solutions as your data and database environment change.

Of course that Oracle recommends those tools. Performance and diagnostic 
packs are $5000 per CPU thread, each. Therein lies the problem. If it 
wasn't for Oracle pricing, I wouldn't even look twice at Postgres, would 
try the pilot project and discard it straight away. This is sales pitch. 
Oracle, however, knows better than to take hints away. Users must have 
the self help methodology. The main problem of Postgres is that it 
doesn't provide that. We've both read Cary Millsap's book. This book 
outlines the tuning methodology for Oracle. The tuning methodology has 
two parts to it:

1) Figure out where the time is spent
2) Shorten the time.

What is so groundbreaking in Millsap's book is the recommendation to 
always start from the application instead of the database and to stop 
looking at "performance indicators" like the infamous "buffer cache hit 
ratio" or "BCHR", as it was known before Connor McDonald's method of 
adjusting it according to your heart's desires.
In order to aid users with that methodology, Oracle has developed an 
extensive set of V$ tables which help users dissect the performance 
problems and en extensive system of hints which can be used to solve the 
performance problems. Those "profiles" mentioned in the Oracle manual are 
nothing more than the complete sets of hints. See the recent book by 
Christian Antognini. In other words, OEM will write your hints for you 
and store them as "profile".
Speaking of the tuning methodology, that's precisely the problem with 
Postgres: it has none. When faced with performance problems in Oracle, 
there are things I can do, short of rewriting the application and 
rethinking the data model. In Postgres, I can only post to the list and 
hope for the best.


> 
>   Hints should be used sparingly, and only after you have collected
>   statistics on the relevant tables and evaluated the optimizer plan
>   without hints using the EXPLAIN PLAN statement. Changing database
>   conditions as well as query performance enhancements in subsequent
>   releases can have significant impact on how hints in your code affect
>   performance.

Hints in the code are needed mainly for two purposes: 

1) To ensure/prevent the use of the particular index.
2) To select a join method (hash/merge/nl).

I don't see the need for those decisions being eliminated anytime soon. 
All versions of the optimizer will have to make such decisions in the 
foreseeable future. In the likely situation that the decision will 
sometimes be wrong, the users must be allowed to make the choice for the 
optimizer. The main problem with Postgres is that it has no self help 
methodology for users. I once asked Joshua Drake what is user to do when 
faced with a performance problem. The answer was something that sounds 
like a bad joke: "post it in the mailing list and we'll tell you what to 
do". Unfortunately, I cannot accept such "tuning methodology". Postgres 
will have to come up with a better one or sink into obscurity.

> 
> So it seems that Oracle considers hints a legacy from when their
> optimizer was even worse and they had nothing better to offer.

I don't see it that way, as explained above.

 
> Oracle has to burn extra cycles (and disk access?) to move rows away
> (and maybe back) right when the performance might be needed elsewhere.

Well, Oracle still defeats Postgres in performance, and by a wide margin. 
That means that the cost is not as great as it may seem at the first 
glance.

> Moreover, there's the problem of having to maintain an extra data
> structure (undo space) which may run out, causing an expensive
> long-running operation to fail randomly.

Well, there is a whole philosophy about "snapshot too old". That, I 
suppose, would be open to vote.

> ROWID:
> ======
> 
> You make the point that because of VACUUM there is nothing compared to
> Oracle's ROWID. But do you really need that? Shouldn't a row be
> referenced by its primary key?

Rowid is needed for the global indexes. I am frustrated with Postgres 
partitioning, which leaves a lot to be desired. I can build a global 
index using Sphinx, and yes, it does use the primary key. Unfortunately, 
the problem with building the global index on the partitioned table is 
precisely the lack of the universal primary key, like ROWID. Somebody has 
mentioned CTID, but here is what the manual (
http://www.postgresql.org/docs/9.0/static/ddl-system-columns.html)

says:
************************************************************************
ctid

    The physical location of the row version within its table. Note that 
although the ctid can be used to locate the row version very quickly, a 
row's ctid will change if it is updated or moved by VACUUM FULL. 
Therefore ctid is useless as a long-term row identifier. The OID, or even 
better a user-defined serial number, should be used to identify logical 
rows. 
************************************************************************

That is the reason for nagging against vacuum and CTID.

> 
> Oracle itself says in
> http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/
pseudocolumns008.htm#SQLRF00254
> 
>   You should not use ROWID as the primary key of a table. If you delete
>   and reinsert a row with the Import and Export utilities, for example,
>   then its rowid may change. If you delete a row, then Oracle may
>   reassign its rowid to a new row inserted later.

Oracle, however, does have partitioning with global indexes, which is 
what I was really after. Let me clarify: I do not advocate using ROWID as 
the primary key, nor do I even allow that in the databases entrusted to 
me. 


> 
> Other maintenance commands like ALTER TABLE ... SHRINK SPACE will also
> change ROWID. 

Only if you allow it. There is a command "alter table <name> enable row 
movement". If this command is not executed, rowid remains constant during 
the lifetime of the row. That also makes index maintenance much more 
expensive, which is why people tend to not do that lightly.



> I do not understand how lack of ROWID should affect global indexes on
> partitioned tables. Can you explain?

Index is a mathematical structure called B*Tree. It locates the key and 
with the key, there is the information how to locate the row(s) 
containing this key. If you want to have a global index, spanning several 
tables, you must have a global identifier for rows, something like 
file.block.offset, which is precisely what Oracle ROWID is. Oracle's 
solution to partitioning was to disassociate segment from an object, 
which used to be the same before Oracle8.  Object in Oracle can have many 
segments, which is a sort of dirty solution. I like the Postgres idea of 
inheritance, but I really need global indexes. I don't think that having 
them is possible without a permanent global row identifier. 


> 
> Fixed size of WAL segments:
> ===========================
> 
> Maybe I am dense, but what is the problem there?

Sheer number of files is annoying. I think I can live with that, it's 
just an aesthetic complaint. However, it is indicative of the fact that 
the creators of Postgres have never been DBA's working on the real life 
databases and do not think high of us, "the DBA types". Josh Berkus 
expressed his disdain for Dan Tow and people like him. In return, I can 
express my contempt for Josh and his ilk. Unfortunately for Josh, it is 
people like me who decide which database will get to live in the 
corporate server room. I can only advise an attitude re-adjustment. If 
Postgres is meant for the corporate users, then it should provide us with 
what we need, in order to be able to do our jobs. Only after that can we 
work on the perfect optimizer, the final frontier and the mission to 
boldly go where no RDBMS system has gone before.




-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#156 — Re: Is PostgreSQL good? (was: migrating oracle to postgres)

Mladen Gogala wrote:
>> I have no problem to discuss hints and other perceived or real
>> shortcomings of PostgreSQL, only before I do this I wanted to ascertain
>> that you are still able to post a civil reply. I wanted to bring the
>> heat level down.
>
> You wanted to bring the level down by telling me that I sound like a
> broken record? I do find that interesting.

I apologize for the quip -- I tried to stay calm in the face of all your
insults against PostgreSQL and its developers, but I couldn't hold this
one back.

I try to sum up your arguments why hints are necessary:
- You missed them in a time constrained porting project where they might
  have saved the day.

I think that this is a valid concern (remember, I'm not dead set against hints).
However, a porting project from a database system you know well to one
that you don't know well -- with project managers tapping fingers and
breathing down your neck -- is something that in my experience has a very
good chance of failing, no matter what.

There probably went some tuning effort (and some hinting) into getting the
original system to perform as it did, and that was done by somebody who
knows Oracle.
That's not your fault -- whoever planed that migration project should have
known better than to assume that you just have to rewrite a couple of triggers
and the system will perform well.

I trust that you made sure that it were really bad query plans that brought
the performance down, that you had current statistics and default_statistics_target
set high enough.

[...]
> Hints are a solution of the last resort.
[...]

I think we agree on that one. Good.

Another thing where I agree with you is that Oracle has more
options for the end user to analyze what a long running query is currently
doing and why. In PostgreSQL you'll typically have to analyze these things
later, and you'll need tools that are not part of core PostgreSQL.

> If they are not there, the database will not get used.

That's a bold statement. How do you explain that there are people who
successfully use PostgreSQL for big performance-critical applications?

Writing to the mailing list to solve a performance problem might work
better than you think. But you are not limited to that. You can either
hire people who can give you the answers, or you can build up skills to
answer the question yourself. Writing to the list is only the low-cost,
low-end option.

Free open source software works differently in many ways. Don't consider it
as something to save money primarily. If you want to do it right, you will
have to invest money, either into building skills or into hiring them.
You can read the code and modify the optimizer, you can even build a hint
system into it. That's what I call "providing the necessary tools for
the users to have chance to help themselves".
But by then you probably understand PostgreSQL well enough to get away
without hints :^)

Another misunderstanding on your side is to separate "the community" and
"the users". In free open source, the users are an integral part of the
community. I guess you sense a split here because you "got rejected" on
a mailing list.

I did not witness the exchange, but could it be that you entered there with
the deprecatory language and the absolute certainty that you are right
that you exhibited here? I normally get good reasons if my ideas get rejected.
Your hapless Oracle Analyst will have to remain polite even if you call
Oracle's optimizer a crock of shit, but there is no such constraint on
people who talk to you on PostgreSQL mailing lists.

Anyway, I digressed from a technical discussion. Sorry.

But before I return, let me complain about some unfounded accusations that you make:

"Nobody uses PgSQL for benchmarks (see www.tpc.org) because of the
performance problems."

Can you justify this statement?

"... the much better performing commercial databases like Oracle or DB2."

Can you justify that?

"Oracle still defeats Postgres in performance, and by a wide margin."

You use this to swipe any concern that Oracle's implementation may be
suboptimal in some respects. How can we make reasonable comparisons in the
face of arguments like this?

As you as an engineer must know, performance is not a magic number that
drops out of a benchmark program and has a linear order.
That's what management would like, and commercial databases go a long way
to "tune" for commercial benchmarks just for that reason.
I would never claim that PostgreSQL performs better than Oracle nor vice versa.
You need a lot of additional constraints to make a meaningful statement,
and that statement could not easily be used outside its context.

I think we have pretty much exhausted what we have to say concerning hints.
It seems that we don't disagree that much, except that I don't think that
PostgreSQL's lack of query hints renders it useless in real-life "enterprise"
scenarios.

About ROWID:

Upon further meditation, I think that Oracle did not originally intend
to provide ROWID as a goodie for the users to quickly locate rows.
The reason to keep it stable must be to make the implementation of indexes
easier. I don't think that "rows as moving targets" are the real problem,
that is easily solved with unique keys. The problem is that a changing
ROWID will force the index to change whenever the row is updated.
This is a price that PostgreSQL has to pay (extra work on update,
indexes must get vacuumed), but Oracle does not get this for free.
In order to keep ROWIDs stable, it must leave a trail of pointers from
the original location of a row to where the row is actually stored
("row chaining" is the technical term).
This will lead to extra disk accesses and a performance penalty in the
critical path of statement execution, and if things get too bad one might
have to reorganize the table (thereby changing the ROWIDs of the affected rows).

So I'd say that ROWID is at least a mixed blessing.

I still don't see how "rows as a moving target" should prevent global
indexes on inheritance hierarchies. Normal indexes have to deal with the
problem as well. What should make it harder for a global index?
In fact, there has been at least one attempt to implement such a solution:
http://archives.postgresql.org/pgsql-hackers/2009-03/msg01046.php
So I don't think that it is impossible by design.

Finally:

> Unfortunately for Josh, it is
> people like me who decide which database will get to live in the
> corporate server room.

That is interesting. You seem to operate in a different kind of business
environment than most of us.
Usually management (where purchase decisions of that magnitude are made)
will resort to glossy brochures and hired consultants rather than asking their
DBA. To some extent, that is because they know that the DBA will always advise
them to use the system he or she knows best. Right?

Yours,
Laurenz Albe 

[toc] | [prev] | [next] | [standalone]

#157 — Re: Is PostgreSQL good? (was: migrating oracle to postgres)

On Mon, 27 Jun 2011 11:17:17 +0200, Laurenz Albe wrote:


> I apologize for the quip -- I tried to stay calm in the face of all your
> insults against PostgreSQL and its developers, but I couldn't hold this
> one back.

Accepted. As for my insults, I will have to refer you back to this:
http://tinyurl.com/68gu822
That, in my humble opinion, is a work of an idiot, period. That isn't an 
insult, that is just a statement of fact.

> 
> I try to sum up your arguments why hints are necessary: - You missed
> them in a time constrained porting project where they might
> have saved the day.

They would have saved the day. I needed the way to make certain that the 
existing indexes are used. In other words, I believe that I am smarter 
than the optimizer and that I should be able to override its decisions, 
at my own peril, of course.

> 
> I think that this is a valid concern (remember, I'm not dead set against
> hints). However, a porting project from a database system you know well
> to one that you don't know well -- with project managers tapping fingers
> and breathing down your neck -- is something that in my experience has a
> very good chance of failing, no matter what.

Well, my knowledge of PostgreSQL was, in my opinion, quite satisfactory. 
I did read the literature, I did read several articles, I did read Greg 
Smith's tuning book and I did engage in the learning process for several 
months. Given my experience with other databases (Oracle, MySQL), I'd say 
that my knowledge was far above the knowledge of an average Postgres user.


> There probably went some tuning effort (and some hinting) into getting
> the original system to perform as it did, and that was done by somebody
> who knows Oracle.

True. The original system was tuned by the people who know oracle.

> That's not your fault -- whoever planed that migration project should
> have known better than to assume that you just have to rewrite a couple
> of triggers and the system will perform well.

Actually, that is precisely the punch line of the Postgres promotors. 
EnterpriseDB even sells a product that is supposed to make it easier 
still. They are saying that similarities are such that it is fairly easy 
to do just that, change few things, recompile and, voila. I am not going 
to name names, but I have attended several of the NY PUG meetings and 
that's precisely the claim that has been made. I am glad that we agree on 
that. You can trust me, however, that hints would have saved the day.

> I trust that you made sure that it were really bad query plans that
> brought the performance down, that you had current statistics and
> default_statistics_target set high enough.

I did. The size of the histograms is 1024. That ought to be enough.

> 
> [...]
>> Hints are a solution of the last resort.
> [...]
> 
> I think we agree on that one. Good.

But they have to be there, as a solution of the last resort. The main 
problem with Postgres is that there is no tuning methodology, there is 
not much one can do to help himself. Postgres doesn't allow its users to 
control the query plan. It reflects a deep mistrust toward its own users.

> 
> Another thing where I agree with you is that Oracle has more options for
> the end user to analyze what a long running query is currently doing and
> why. In PostgreSQL you'll typically have to analyze these things later,
> and you'll need tools that are not part of core PostgreSQL

It's not just tools, it's the architecture. Plans of the running 
statement are not visible because they are not shared. You cannot see 
them. There is no way to tell whether the executing process has just 
executed "set enable_seqscan=off". You don't see the running plan and you 
don't see the execution environment for that process. The only way you 
could see that would be to have a shared memory area to read it from. In 
Oracle terminology, that would mean a shared pool.


> 
>> If they are not there, the database will not get used.
> 
> That's a bold statement. How do you explain that there are people who
> successfully use PostgreSQL for big performance-critical applications?

I don't. I can only see that PostgreSQL is much less popular than MySQL 
and I can see that it isn't getting better. I can see that the meeting in 
NYC was a flop, despite people like me desperately seeking for an 
alternative to Oracle. Sorry, PostgreSQL is not it. As I've stated above, 
the main problem is a complete lack of tuning methodology. There is not 
much user can do, short of posting the output of explain analyze, 
preferably formatted using www.depesz.com. User or a DBA doesn't have a 
methodology that could lead to a better performing plan. And yes, the 
fact that Postgres designers do not trust their users enough to provide 
them with means to override the optimizer decisions, should all else 
fail, is also deeply disturbing. Of course, now I can pull my final 
objection: PostgreSQL is the ONLY major database system which does so. 
Even Firebird designers have accepted the inevitable and will provide 
basic hints in the version 3.0, despite the similar objections as are the 
one made by PostgreSQL developers.


> 
> Writing to the mailing list to solve a performance problem might work
> better than you think. 

It is not a tuning methodology. Sorry. This is simply unacceptable. I 
will not do so, under any circumstances.

> But you are not limited to that. You can either
> hire people who can give you the answers, or you can build up skills to
> answer the question yourself. Writing to the list is only the low-cost,
> low-end option.
> 
> Free open source software works differently in many ways. Don't consider
> it as something to save money primarily. 

Unfortunately, if I have to spend money for the pilot project to succeed, 
it will not be on PostgreSQL. If we are talking about spending money, 
there are other databases which are commercial from scratch. That is 
probably the reason for such a low popularity of PostgreSQL.


> If you want to do it right, you
> will have to invest money, either into building skills or into hiring
> them. You can read the code and modify the optimizer, you can even build
> a hint system into it. That's what I call "providing the necessary tools
> for the users to have chance to help themselves". But by then you
> probably understand PostgreSQL well enough to get away without hints :^)

The trouble is that I need to have hints as an insurance policy. Should 
all else fail, I want to be able to force my decision upon the optimizer.
Humans are still much smarter than computer programs and know the data 
better.

> Another misunderstanding on your side is to separate "the community" and
> "the users". In free open source, the users are an integral part of the
> community. I guess you sense a split here because you "got rejected" on
> a mailing list.

I didn't just get rejected, I got steamrolled. The argument that hints 
are unnecessary is indefensible, unless you can prove that the optimizer 
is smarter than humans. On the other hand, such a claim would be not only 
preposterous, it would be downright rude.
I've seen many bad optimizer decisions by the Postgres optimizer which 
I've influenced through the set of "set" commands. Unfortunately, when 
application is written in Hibernate, it's easy to rewrite a SQL command 
or two, but to enter countless "set" commands is daunting at best. Of 
course, those "set" commands will stay in effect even after the 
problematic command has been executed, so you'll have to do it all over 
again, for the next command, plus keeping in mind the switches for the 
previous executions.


> I did not witness the exchange, but could it be that you entered there
> with the deprecatory language and the absolute certainty that you are
> right that you exhibited here? 

Because nobody has proven me wrong. The pearls of wisdom, like the URL I 
quoted at the beginning of this post, just reinforce me in my argument. 
In essence, the argument boils down to the following points:
- Hints are needed. I know that for a fact from my personal experience.
  That is beyond debate. I do trust my own eyes.
- Humans are smarter than computer programs. To claim otherwise is rude,
  to say the least.
- Refusal to provide hints reflects deep mistrust toward the users.
- PostgreSQL has no usable tuning methodology that user could follow to
  speed up the SQL execution. None whatsoever. 



> I normally get good reasons if my ideas
> get rejected. Your hapless Oracle Analyst will have to remain polite
> even if you call Oracle's optimizer a crock of shit, but there is no
> such constraint on people who talk to you on PostgreSQL mailing lists.

On the other hand, many people who have tried talking sense into 
PostgreSQL people and have failed, might have stopped using PostgreSQL 
altogether. Maybe that's why PGEast in NYC was such a flop? There is a 
reason for so much larger degree of popularity of MySQL, even after its 
purchase by Oracle. Has anyone ever asked what is PostgreSQL doing wrong?

> 
> Anyway, I digressed from a technical discussion. Sorry.

> But before I return, let me complain about some unfounded accusations
> that you make:
> 
> "Nobody uses PgSQL for benchmarks (see www.tpc.org) because of the
> performance problems."
> 
> Can you justify this statement?

Yes, of course I can. I was unable to find even a single TPC benchmark on 
the TPC site, done by using Postgres. TPC (stands for "Transaction 
Processing Council") is the place where people place TPC benchmarks. 
Databases used for benchmarks are the usual suspects: Oracle, MS SQL, 
DB2, Sybase and some more exotic versions EXASQL. I didn't find any 
Postgres benchmark there, feel free to correct me if I'm wrong.

> "... the much better performing commercial databases like Oracle or
> DB2."
> 
> Can you justify that?

Yes, I can. The reasons why both Oracle and DB2 are, generally speaking, 
much faster than Postgres are 
- proper partitioning
- the ability to parallelize queries
Both of those abilities are missing from Postgres. In particular, Oracle 
can do many things that Postgres cannot. Here I have in mind things like 
the star schema, bitmap indexes are still somewhat painful topic, index 
organized tables, index clusters, hash clusters, full index scan, fast 
index scan or a skip scan. And I didn't even mention RAC, "AS OF" queries 
or flashback.


> "Oracle still defeats Postgres in performance, and by a wide margin."
> 
> You use this to swipe any concern that Oracle's implementation may be
> suboptimal in some respects. How can we make reasonable comparisons in
> the face of arguments like this?

The "reasonable comparison" must start with the fact that Oracle is the 
2nd largest software company in the world and that its resources are many 
times larger than the resources at the disposal to the Postgres 
developers. If Postgres wants to compete with Oracle, it can do so only 
based on price, definitely not on the features or performance. Even so, 
if you want people to consider Postgres being alternative at all, which 
wast majority of companies is not doing, you have to ask corporate users 
what do they want. The topic of hints have popped up again and again. And 
will not go away. As a matter of fact, I once stated that I will write a 
hint system myself. The project that I am currently working on and is 
consuming the most of my spare time will be done by August the 1st. I 
will then renew my C programming, I used to be pretty good at that, and 
write it, even if it takes me a full year to do so. 

> As you as an engineer must know, performance is not a magic number that
> drops out of a benchmark program and has a linear order. That's what
> management would like, and commercial databases go a long way to "tune"
> for commercial benchmarks just for that reason. I would never claim that
> PostgreSQL performs better than Oracle nor vice versa. You need a lot of
> additional constraints to make a meaningful statement, and that
> statement could not easily be used outside its context.

What allows me to make such a broad comparison is the fact that PostgreSQL 
has no tuning methodology. If, for instance, the two of us are given the 
same SQL statement to make faster, you on Postgres and me on Oracle, I 
will have a method to follow and will, most likely, end up with a 
solution, much, much before you. If all else fails, I will be able to 
just force the execution plan that I like. You will still be waiting for 
an answer from the mailing list.


> I think we have pretty much exhausted what we have to say concerning
> hints. It seems that we don't disagree that much, except that I don't
> think that PostgreSQL's lack of query hints renders it useless in
> real-life "enterprise" scenarios.

Well, it's like cruising on a passenger ship which doesn't have life 
boats. Most likely, you will not need them but the passengers on the 
Titanic could have used few more of those. 




> I still don't see how "rows as a moving target" should prevent global
> indexes on inheritance hierarchies. Normal indexes have to deal with the
> problem as well. What should make it harder for a global index? In fact,
> there has been at least one attempt to implement such a solution:
> http://archives.postgresql.org/pgsql-hackers/2009-03/msg01046.php So I
> don't think that it is impossible by design.

Well, I will refrain from judgment here, but I am still waiting for an 
implementation. I've mentioned Sphinx, which requires globally unique 
bigint as the key. Maybe, there is a way to do it without. Let's wait and 
see. 

> 
> Finally:
> 
>> Unfortunately for Josh, it is
>> people like me who decide which database will get to live in the
>> corporate server room.
> 
> That is interesting. You seem to operate in a different kind of business
> environment than most of us.
> Usually management (where purchase decisions of that magnitude are made)
> will resort to glossy brochures and hired consultants rather than asking
> their DBA. To some extent, that is because they know that the DBA will
> always advise them to use the system he or she knows best. Right?

It seems that we do operate in different environments. In my world, DBA 
acts as goalkeeper who evaluates and filters out acceptable solutions. 
I've been working for several CIO's and have been advising on a vide 
range of software, from monitoring software, backup software to 
alternative DB software. I cannot recollect an instance of my advice not 
being followed. The decision, of course, is made by the CIO, but the 
evaluation is my responsibility. That is, after all, how I ended up with 
Postgres. Now, I have a problem: CIO has made a decision, based on my 
recommendation, and I have to tell him that I was wrong. I was able to 
kill one porting project, but that doesn't mean that I am off the hook.




Finally, let me explain what am I doing here. What I've set out to do is 
to warn anybody who wants to replace Oracle by Postgres that it will not 
be as easy as it looks and that there are terminal deficiencies in the 
mentality of PostgreSQL developer which will never allow it to gain much 
popularity or become a real alternative to Oracle. Had I been properly 
warned, I wouldn't have ended with such an egg on my face. I will only 
react if someone mentions conversion from Oracle. People ought to be 
warned.


-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#158 — Re: Is PostgreSQL good?

Mladen Gogala wrote:
> As for my insults, I will have to refer you back to this:
> http://tinyurl.com/68gu822
> That, in my humble opinion, is a work of an idiot, period. That isn't an
> insult, that is just a statement of fact.

I guess you feel insulted by his comments about "older DBAs" and their
habits. It is probably exaggerated, but I think he is refering to people
who say "let's use hints" before they even start to think properly.

>> That's not your fault -- whoever planed that migration project should
>> have known better than to assume that you just have to rewrite a couple
>> of triggers and the system will perform well.
>
> Actually, that is precisely the punch line of the Postgres promotors.
> EnterpriseDB even sells a product that is supposed to make it easier
> still. They are saying that similarities are such that it is fairly easy
> to do just that, change few things, recompile and, voila.

You have to be able to tell marketing talk from reality.
And for simple applications it might work well enough.

> I can only see that PostgreSQL is much less popular than MySQL
> and I can see that it isn't getting better.

First, be patient. And second, popularity is not always the measure
of quality (ever heard of Microsoft?).

>> Free open source software works differently in many ways. Don't consider
>> it as something to save money primarily.
>
> Unfortunately, if I have to spend money for the pilot project to succeed,
> it will not be on PostgreSQL. If we are talking about spending money,
> there are other databases which are commercial from scratch. That is
> probably the reason for such a low popularity of PostgreSQL.

Well, you said that you spent months learning PostgreSQL. That's an
investment that can be measured in money, right?

Commercial software has a different and enticing pricing model. You'll
get the thing for a moderate price at first (or you get a crippled free
version), then you realize that without (expensive) support you can't
even get patches for the showstopping bugs, and the next time you'll have
to negotiate license fees you'll have to pay more.

With open source software you may have a higher initial cost: you will
have to spend more money on integrating it into your environment, you
might have to invest more into education, you might have to develop
add-ons that you need and that are not supplied. But once this is done,
costs will drop rapidly, and in the long run you'll be cheaper.

Furthermore, you'll get added values with open source: the ability to
influence decisions (the only enhancement request I ever made for
Oracle years ago did not even receive an answer), the ability to help
yourself if you run into a bug or need a feature.

>> "Nobody uses PgSQL for benchmarks (see www.tpc.org) because of the
>> performance problems."
>>
>> Can you justify this statement?
>
> Yes, of course I can. I was unable to find even a single TPC benchmark on
> the TPC site, done by using Postgres. TPC (stands for "Transaction
> Processing Council") is the place where people place TPC benchmarks.
> Databases used for benchmarks are the usual suspects: Oracle, MS SQL,
> DB2, Sybase and some more exotic versions EXASQL. I didn't find any
> Postgres benchmark there, feel free to correct me if I'm wrong.

Maybe there are other reasons besides terrible performance?
Do you have to pay for TPC? Is it expensive?

>> "... the much better performing commercial databases like Oracle or
>> DB2."
>>
>> Can you justify that?
>
> Yes, I can. The reasons why both Oracle and DB2 are, generally speaking,
> much faster than Postgres are
> - proper partitioning
> - the ability to parallelize queries
> Both of those abilities are missing from Postgres. In particular, Oracle
> can do many things that Postgres cannot. Here I have in mind things like
> the star schema, bitmap indexes are still somewhat painful topic, index
> organized tables, index clusters, hash clusters, full index scan, fast
> index scan or a skip scan. And I didn't even mention RAC, "AS OF" queries
> or flashback.

PostgreSQL has some of these things.
Even if Oracle has some performance related features that PostgreSQL does not,
that does not prove that it will always perform better. I'm sure that Oracle
will sometimes be faster than PostgreSQL, but I don't accept a general statement
like that.

> The "reasonable comparison" must start with the fact that Oracle is the
> 2nd largest software company in the world and that its resources are many
> times larger than the resources at the disposal to the Postgres
> developers. If Postgres wants to compete with Oracle, it can do so only
> based on price, definitely not on the features or performance. Even so,
> if you want people to consider Postgres being alternative at all, which
> wast majority of companies is not doing, you have to ask corporate users
> what do they want.

There you go. PostgreSQL with its limited resources has to concentrate
on the core system. It is not out to provide everything that Oracle does,
but it is out to do the things it does better than everybody else.
I believe that PostgreSQL is quite interested in what corporate users want.
Think of in-core replication. But that does not imply that PostgreSQL can
and wants to provide everything that corporate users ask for.

> The topic of hints have popped up again and again. And
> will not go away. As a matter of fact, I once stated that I will write a
> hint system myself. The project that I am currently working on and is
> consuming the most of my spare time will be done by August the 1st. I
> will then renew my C programming, I used to be pretty good at that, and
> write it, even if it takes me a full year to do so.

I am sure that you'll find the community helpful if you have questions.
However, it will be pretty difficult to get that into core.
You'd have to be patient at least.
But as long as you can scratch your itch, that's a minor inconvenience.

> What allows me to make such a broad comparison is the fact that PostgreSQL
> has no tuning methodology. If, for instance, the two of us are given the
> same SQL statement to make faster, you on Postgres and me on Oracle, I
> will have a method to follow and will, most likely, end up with a
> solution, much, much before you. If all else fails, I will be able to
> just force the execution plan that I like. You will still be waiting for
> an answer from the mailing list.

As I mentioned, there *are* other things you can do.
- You can examine the output of EXPLAIN ANALYZE yourself and figure out
  where and why things go wrong.
- You can influence statistics levels on a per-column basis.
- You can influence query planning with a number of parameters.
  And they don't have to "stay around" after the query is done.
  There is SET LOCAL which takes effect only in the current transaction.
- Often a simple change in an SQL statement will get the planner on the
  right track.

> Finally, let me explain what am I doing here. What I've set out to do is
> to warn anybody who wants to replace Oracle by Postgres that it will not
> be as easy as it looks and that there are terminal deficiencies in the
> mentality of PostgreSQL developer which will never allow it to gain much
> popularity or become a real alternative to Oracle. Had I been properly
> warned, I wouldn't have ended with such an egg on my face. I will only
> react if someone mentions conversion from Oracle. People ought to be
> warned.

That's ok, share your experiences.
But if you want your opinion to be heard, refrain from insult and sweeping
generalizations with no more proof than your authority.

Yours,
Laurenz Albe 

[toc] | [prev] | [next] | [standalone]

Page 1 of 3  [1] 2 3  Next page →

Back to top | Article view | comp.databases.postgresql

csiph-web