Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!weretis.net!feeder4.news.weretis.net!newsfeed.utanet.at!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail From: "Laurenz Albe" Newsgroups: comp.databases.postgresql References: <1308640710.210659@proxy.dienste.wien.at><1308738638.232318@proxy.dienste.wien.at><1308911383.819034@proxy.dienste.wien.at> Subject: Re: Is PostgreSQL good? (was: migrating oracle to postgres) Date: Mon, 27 Jun 2011 11:17:17 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 Organization: dienste.wien.at ISP Message-ID: <1309166263.561072@proxy.dienste.wien.at> X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/) Lines: 152 NNTP-Posting-Host: 141.203.254.23 X-Trace: 1309166270 aconews.univie.ac.at 73248 141.203.254.23 X-Complaints-To: abuse@univie.ac.at Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:156 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