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 2 of 3 — ← Prev page 1 [2] 3  Next page →

#159 — Re: Is PostgreSQL good?

On Tue, 28 Jun 2011 10:34:03 +0200, Laurenz Albe wrote:


>> 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?).

Patient? Postgres came about in 1995. It's 2011 now. How much more 
patience is needed? As for Microsoft, it is very good at what it does. MS-
Word is a class in itself, head and shoulders above the competition. 
Microsoft still owns 95% or so of the desktop and office software market, 
despite repeated assaults by both Linux and Apple and the former 
opponents like OS/2, WordPerfect, Borland, Lotus and all other names that 
are now but waning memories. Other than that, I have very good 
experiences with Windows7. I wouldn't exactly take MS as an example for 
the lack of quality. Look at it this way: users are voting with their 
wallets. So far, MS is winning the contest by a wide margin, despite the 
existence of the free alternatives. That says something, doesn't it?

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

Yes, it is. In this case, it was wasted money. I've wasted a year of my 
life. 

> 
> 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.

Depends on the vendor. There are many business models, ranging from Red 
Hat which allows you to download for free and pay for the support if you 
need it to Oracle.

> 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.

Unless the software itself doesn't fulfill your needs. And that is what 
I've been trying to convey to Postgres community all along: hints are not 
optional. They are essential. That is why I have drawn the line in the 
sand.


> 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.

You mean the ability to help yourself if you need something like hints? 
What happens if the product is heavily influenced by bozos saying 
something "we don't need to do that because we're not for profit"?

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

TPC is mainly paid for by the equipment manufacturers. Having the meanest 
database machine on the market allegedly sells boxes. Database vendors 
are usually not too thrilled about benchmarks because of the fear that in 
a few months the vendor will release a better benchmark by some other DB 
and leave them with a huge marketing problem. If Postgres was so fast, I 
assure you, someone would have selected it for a TPC benchmark. The fact 
that it didn't happen puts things in perspective.


> 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.

Look no further than the Postgresql performance mailing list. At least 
once a month, there is a question why does Oracle do this or that faster 
than Postgres. If you asked users with the experience in both worlds, 
what do you think that the answer would be, which one is faster?


> 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 case with hints shows a fatal flaw in the mentality of Postgres 
community. Quite frankly, if Postgres didn't make it to the prime time in 
16 years, I don't think it ever will. There are other options, from NoSQL 
databases like MongoDB to Firebird. I am waiting for Firebird 3.0 to 
assess how usable it will be. It's a major effort and from what I am 
reading about it, it definitely looks interesting.


> 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.

Initially, I've asked for hints to only be put on the TODO list. At that 
time, that would have satisfied me. I was steamrolled. The "community" 
doesn't even want to talk about hints. It's a carnival of vanities, 
they've drawn the line in the sand and now there is no more discussion 
about that. Fine with me. 


> But as long as you can scratch your itch, that's a minor inconvenience.

I can, but not with Postgres. The absence of hints disqualifies Postgres 
for any further projects. And I wished for Postgres to succeed. I was 
hosting meetings and I was very enthusiastic about it. Until I touched 
the topic of hints. 


> 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.

Laurenz, you skipped all my explanations that humans are smarter than 
computer programs and that there must be the way for human to decide and 
override the decisions made by the optimizer. For a database to be 
viable, it must offer a tuning methodology, a finite set of steps that 
user can make to improve performance. I find posting to the mailing an 
unacceptable methodology. This was supposed to be a discussion on the 
technical merits of hints, yet there is no answer to the main points I've 
made. Let me repeat them once again:
- Humans are smarter than the optimizer and have better knowledge of
  the data. Therefore, they should have the possibility to override the 
  decisions made by the optimizer.
- Hints are needed, as the last resort, but they are. When there is no 
  other way of getting the desired performance, hints must be available
  as the method of the last resort.
- Database must offer a tuning methodology, things that user can do to 
  improve performance. You start with a SQL, you run explain analyze and 
  then there must be a series of steps to take. In PostgreSQL, there 
  isn't such a methodology.
- Such refusal to provide a method to override the optimizer decisions 
  reflects deep mistrust of the database developers toward their users.

The only reason for so stubbornly refusing hints is what I call the 
carnival of vanities, the line drawn in the sand by a group of 
developers, who have never been DBA's, and who will rather see their 
product to sink into obscurity than to give in to reasonable demands. At 
this point, I would like to hear one reason against hints. All I've heard 
so far is to be patient and that it's "deep in core", without any 
reasoning against having the option. For patience to make sense, someone 
must acknowledge that hints are necessary and put them on the TODO list. 
With pearls of wisdom like the one previously mentioned, patience makes 
no sense. A change in attitude is needed and I find that unlikely to 
happen.

> 
> 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.

I don't think that I am getting my message across here. I don't care 
about being heard by the Postgres community, that's way behind me. I've 
said what I've had, those who wanted to listen did so. Now, my goal is to 
try warning the Oracle users of the pitfalls and let them know that 
Postgres is essentially inadequate for the task. In other words, my 
message is not intended for the Postgres community at all, it is intended 
for the Oracle users. I've abandoned my hopes for Postgres. With Josh 
Berkus, Kevin Grittner,Joshua Drake, Tom Lane and Bruce Momjian, there 
isn't much hope for the project. Sure, it will live on, maybe for even 
another decade, but it will eventually sink into obscurity. Firebird 
looks promising, NoSQL databases seem like a good solution for the DW 
type use, and there are some other ideas, too. 

-- 
http://mgogala.byethost5.com

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

#162 — Re: Is PostgreSQL good?

Mladen Gogala wrote:
>> 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.

> Look no further than the Postgresql performance mailing list. At least
> once a month, there is a question why does Oracle do this or that faster
> than Postgres. If you asked users with the experience in both worlds,
> what do you think that the answer would be, which one is faster?

That argument is flawed.
Only people who have a problem will write a question to the list.

> This was supposed to be a discussion on the
> technical merits of hints, yet there is no answer to the main points I've
> made. Let me repeat them once again:
> - Humans are smarter than the optimizer and have better knowledge of
>  the data. Therefore, they should have the possibility to override the
>  decisions made by the optimizer.
> - Hints are needed, as the last resort, but they are. When there is no
>  other way of getting the desired performance, hints must be available
>  as the method of the last resort.
> - Database must offer a tuning methodology, things that user can do to
>  improve performance. You start with a SQL, you run explain analyze and
>  then there must be a series of steps to take. In PostgreSQL, there
>  isn't such a methodology.
> - Such refusal to provide a method to override the optimizer decisions
>  reflects deep mistrust of the database developers toward their users.

All but the third of these points are not about technical merits.

#1 is philosophy. I'd agree that humans are smarter than machines in
principle, but I wouldn't enter a number crunching contest with one.
Nor do I personally want to enter a query optimizing contest with
PostgreSQL's optimizer.

#2 is an opinion. What should I answer?

#4 sounds like a conspiracy theory.

I agree with #3 as far that a database must offer tools for tuning.
I have named some that PostgreSQL has. Query tuning is always a bit of
an art, don't pretend that there's a step-by-step foolproof method in
Oracle. I may look like that to you because you know it well.

>> 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.

> I don't think that I am getting my message across here. I don't care
> about being heard by the Postgres community, that's way behind me.

No, I meant be heard by people who post here to get advice and opinions
on migrating from Oracle to PostgreSQL.

Yours,
Laurenz Albe 

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

#163 — Re: Is PostgreSQL good?

On Wed, 29 Jun 2011 09:50:32 +0200, Laurenz Albe wrote:

> All but the third of these points are not about technical merits.
> 
> #1 is philosophy. I'd agree that humans are smarter than machines in
> principle, but I wouldn't enter a number crunching contest with one. Nor
> do I personally want to enter a query optimizing contest with
> PostgreSQL's optimizer.

Well, I have no compunction about doing the latter. However, no choice is 
given. That's the problem. 


> 
> #2 is an opinion. What should I answer?

You should let me know whether you think that there are situations in 
which hints are required and if not, why not? The designers of all other 
major RDBMS systems seem to disagree here.


> 
> #4 sounds like a conspiracy theory.

No, it doesn't. It's a rightful impression. Refusal to give users a 
choice and requirement to post to the list is completely ludicrous. Now, 
had I said that PostgreSQL is intentionally kept relatively useless, so 
that business users have to either buy a commercial product or hire 
consultants, that would be a conspiracy theory. However, I didn't say 
that. All I said is that refusal to provide hints based on arguments that 
the optimizer is smarter than the users is rude and reflects deep 
mistrust toward the users of the software.


> 
> I agree with #3 as far that a database must offer tools for tuning. I
> have named some that PostgreSQL has. Query tuning is always a bit of an
> art, don't pretend that there's a step-by-step foolproof method in
> Oracle. I may look like that to you because you know it well.

Actually, there is a foolproof method in Oracle. You see where the time 
is spent and you address the problem. That is what the wait event 
interface is all about. And it's by no means an art form. 

> No, I meant be heard by people who post here to get advice and opinions
> on migrating from Oracle to PostgreSQL.

Oh, I'll take care of that myself. I'll make myself heard, don't worry.




-- 
http://mgogala.byethost5.com

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

#164 — Re: Is PostgreSQL good?

Mladen Gogala wrote:
>> #2 is an opinion. What should I answer?
>
> You should let me know whether you think that there are situations in
> which hints are required and if not, why not? The designers of all other
> major RDBMS systems seem to disagree here.

I am not sure if there are situations where they are needed.
So far, I have not felt the need in PostgreSQL.

>> I agree with #3 as far that a database must offer tools for tuning. I
>> have named some that PostgreSQL has. Query tuning is always a bit of an
>> art, don't pretend that there's a step-by-step foolproof method in
>> Oracle. I may look like that to you because you know it well.
>
> Actually, there is a foolproof method in Oracle. You see where the time
> is spent and you address the problem. That is what the wait event
> interface is all about. And it's by no means an art form.

EXPLAIN ANALYZE will show me where the time is spent.
The "address the problem" is the nontrivial point here, in PostgreSQL
as well as in Oracle. PostgreSQL is lacking one problem addressing tool
that Oracle provides, but that dows not imply that there are none.

Yours,
Laurenz Albe 

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

#165 — Re: Is PostgreSQL good?

On Thu, 30 Jun 2011 09:14:16 +0200, Laurenz Albe wrote:

>  am not sure if there are situations where they are needed.
> So far, I have not felt the need in PostgreSQL.

And that's essentially what the usual argument against providing hints 
boils down to. Plus, of course, a fair dose of irritation over my being 
stubborn and refusing to go away.

> EXPLAIN ANALYZE will show me where the time is spent. 

Not in sufficient detail. There is nothing like oracle wait event 
interface, but that was not the point here. We're not discussing a wait 
event interface here.

> The "address the
> problem" is the nontrivial point here, in PostgreSQL as well as in
> Oracle. PostgreSQL is lacking one problem addressing tool that Oracle
> provides, but that dows not imply that there are none.

The problem is that Postgres doesn't allow me to override its optimizer 
plan, even if I deem it necessary. In other words, Postgres tells me, as 
some of its creators have written, that its optimizer is smarter than 
humans. And here make a loop toward philosophy again. This is not a minor 
detail, as I have explained before. Optimizer hints are a crucial problem.
And I will not go away and stop pointing at it.

-- 
http://mgogala.byethost5.com

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

#166 — Re: Is PostgreSQL good?

Hi Laurenz, (et al.)

[apologies for not having the technical depth to understand these
issues better -- I just use PostgreSQL as a "means to an end"...
I could use any similar "means" but this seems to work well enough
for me]

I write a lot of code (in C, etc.) and *deliberately* let the compiler
do as much work as possible, for me -- concentrating on picking good
*algorithms* instead of micromanaging individual lines of code.  If
performance is a *real* issue (like processing video frames in
real-time) and I *can't* change the hardware (because this isn't
a handful of servers, but, rather, thousands of "retail devices"),
then I will drop into assembly language to code *very tiny* portions
of the algorithm where the gains are the most substantial.  I.e.,
my philosophy is to let the machine do the thinking/work, wherever
possible.

Can someone please, *gently*, explain *quantitatively* the differences
that these issues make in performance?

(see below)

On 6/24/2011 3:29 AM, Laurenz Albe wrote:

> 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.

Are hints on a par with the 'register' keyword in C?  I.e.,
something you *hope* gives the compiler an insight into your
algorithm -- but, that the compiler is free to *ignore*?
Are there consequences to using a "hint" (e.g., using 'register'
places constraints on what you can do)?

Is the "hint" concept essentially a workaround to compensate for
capabilities *missing* in the optimizer?  I.e., as C compilers
get smarter, they deliberately *choose* to ignore the 'register'
keyword because they actually have *more* insight than the
programmer!

Are there ways (without using a "hint") to coerce the database
to perform in the way your hint would *hope* to have it behave?
(e.g., can you rewrite the SQL to cause the behavior you seek?)

[these all seem to be parallels to the register/compiler
example I posed]

What sort of *quantitative* difference can this make (example?)
that can't, otherwise, be attained?

> 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.

Is this just the difference between automatic garbage collection
and "manual" garbage collection (again, relying on parallels to
other "languages")?

Does the parallel maintain throughout operation or does it fall
apart at the extremes?  E.g., if garbage collection must be
manually initiated (in a language), then failing to do so
can eventually cause you to run out of heap, etc. (which
causes the application to "fail").  Is the same true when
it comes to vacuuming -- or, does performance just suffer?
(i.e., will you ever "fill the disk" with dead rows if you
fail to initiate a VACUUM operation?)

> 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?

The last question seems to be my understanding of how things should
work. (?)  (actually, one of the harder ideas for me to get accustomed
to in this field  :< )

How does a ROWID differ from using an OID?  (which I presume is
not a good thing)

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

<grin>  I don't even know what they *are*!  ;-)

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

#167 — Re: Is PostgreSQL good?

On Thu, 30 Jun 2011 07:05:54 -0700, Don Y wrote:

> Are hints on a par with the 'register' keyword in C?  

Yes.

> I.e., something
> you *hope* gives the compiler an insight into your algorithm -- but,
> that the compiler is free to *ignore*?

C compiler is not free to ignore "register" keyword and neither is the 
database free to ignore hints. Compiler has to place your variable in a 
register, if there is one available. Database also must obey hints, if 
possible. Also, with C there is one more keyword, namely "asm", which may 
be used to coerce the code to behave precisely as you want.
This analogy is not good for several reasons, the main one being the 
difference between the directly executable vs. interpreted paradigms.
If C was the only compiler to disallow "hints", it wouldn't be so 
popular. Now, if PostgreSQL was the only major database to disallow 
hints...?




-- 
http://mgogala.byethost5.com

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

#168 — Re: Is PostgreSQL good?

Mladen Gogala <no@email.here.invalid> writes:
> On Thu, 30 Jun 2011 07:05:54 -0700, Don Y wrote:

>> Are hints on a par with the 'register' keyword in C?  
>
> Yes.
>
>> I.e., something
>> you *hope* gives the compiler an insight into your algorithm -- but,
>> that the compiler is free to *ignore*?
>
> C compiler is not free to ignore "register" keyword and neither is the 
> database free to ignore hints. Compiler has to place your variable in a 
> register, if there is one available.

The relevant text is:

    A declaration of an identifier for an object with storage-class
    specifier register suggests that access to the object be as fast as
    possible. The extent to which such suggestions are effective is
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    implementation-defined.
    ^^^^^^^^^^^^^^^^^^^^^^

i.e. the compiler is free to ignore it.

> Database also must obey hints, if possible. Also, with C there is one
> more keyword, namely "asm", which may be used to coerce the code to
> behave precisely as you want.  This analogy is not good for several
> reasons, the main one being the difference between the directly
> executable vs. interpreted paradigms.  If C was the only compiler to
> disallow "hints", it wouldn't be so popular. Now, if PostgreSQL was
> the only major database to disallow hints...?

-- 
http://www.greenend.org.uk/rjk/

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

#169 — Re: Is PostgreSQL good?

On Thu, 30 Jun 2011 16:49:48 +0100, Richard Kettlewell wrote:

> The relevant text is:
> 
>     A declaration of an identifier for an object with storage-class
>     specifier register suggests that access to the object be as fast as
>     possible. The extent to which such suggestions are effective is
>               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>     implementation-defined.
>     ^^^^^^^^^^^^^^^^^^^^^^


This is what I found:
http://tigcc.ticalc.org/doc/keywords.html#register
**************************************************************************
register

Tells the compiler to store the variable being declared in a CPU register.

In standard C dialects, keyword auto uses the following syntax:

register data-definition;

The register type modifier tells the compiler to store the variable being 
declared in a CPU register (if possible), to optimize access. For example,

register int i;

Note that TIGCC will automatically store often used variables in CPU 
registers when the optimization is turned on, but the keyword register 
will force storing in registers even if the optimization is turned off. 
However, the request for storing data in registers may be denied, if the 
compiler concludes that there is not enough free registers for use at 
this place.
************************************************************************

While there are implementation differences, I would hardly say that the 
compiler is "free to ignore the register keyword". It might end up 
ignoring it, but it's definitely not the default behavior. However, this 
group is about PostgreSQL and this thread was devoted to some particular 
aspects missing in PostgreSQL, not to C language. Analogy with the C 
language is actually a bad analogy to consider when discussing databases, 
primarily because C language isn't a database. I don't think we should 
waste any more time discussing C language.



-- 
http://mgogala.byethost5.com

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

#171 — Re: Is PostgreSQL good?

On 6/30/2011 12:24 PM, Mladen Gogala wrote:
> On Thu, 30 Jun 2011 16:49:48 +0100, Richard Kettlewell wrote:
>
>> The relevant text is:
>>
>>      A declaration of an identifier for an object with storage-class
>>      specifier register suggests that access to the object be as fast as
>>      possible. The extent to which such suggestions are effective is
>>                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>      implementation-defined.
>>      ^^^^^^^^^^^^^^^^^^^^^^
>
>
> This is what I found:
> http://tigcc.ticalc.org/doc/keywords.html#register

You might want to scroll down a bit instead of just taking the
first hit google gives you -- which references *an* implementation
of *a* C compiler (and can't authoritatively speak for The Standard
nor any of the other "conforming compilers" out there)

> While there are implementation differences, I would hardly say that the
> compiler is "free to ignore the register keyword". It might end up
> ignoring it, but it's definitely not the default behavior. However, this
> group is about PostgreSQL and this thread was devoted to some particular
> aspects missing in PostgreSQL, not to C language. Analogy with the C
> language is actually a bad analogy to consider when discussing databases,
> primarily because C language isn't a database. I don't think we should
> waste any more time discussing C language.

Gee, someone expresses an interest in understanding why you have
such a hard-on regarding "hints" and you dismiss it as "irrelevant".

(sigh)  Sure makes me want to try to understand your point of
view better -- NOT!

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

#172 — Re: Is PostgreSQL good?

On Thu, 30 Jun 2011 13:21:17 -0700, Don Y wrote:

> Gee, someone expresses an interest in understanding why you have such a
> hard-on regarding "hints" and you dismiss it as "irrelevant".

What I consider  irrelevant is the analogy with the C language. 
Furthermore, this debate is, at least in my humble opinion, relevant to 
DBA's, project managers and people who will write SQL against a Postgres 
database. I haven't envisioned this debate as a talk show with the 
viewers taking sides, Oprah already covers that market.

As far as me having hard-on for hints, I needed them in one of my 
projects. Hints are a welcome option, offered by virtually all other RDBMS 
systems, yet there seems to be a problem with the designers of Postgres. 
I have yet to see a logical argument against providing that option and 
analogies with the C programming language don't seem to provide it, 
either. So, those analogies are irrelevant, at least in my point of view. 
You don't seem to be a database professional either, I am quite confused 
by the zeal with which you're trying to participate in this debate. Once 
again, this debate is about a specific feature of Postgres, or the lack 
of it.

FYI, hints are a method to force the optimizer to do what the programmer 
wants, rather than what the RDBMS thinks is right. Here are two 
implementations of query hints:

http://dev.mysql.com/doc/refman/5.5/en/index-hints.html
http://msdn.microsoft.com/en-us/library/ms181714.aspx

There are, of course, other implementations, in DB2 and Oracle. As a 
matter of fact, the only other relatively well known database that didn't 
have hints was Firebird. Fortunately, its designers have seen the light 
and the hints are planned for the version 3.0:
http://www.slideshare.net/mindthebird/initial-review-of-firebird-3

The argument is whether the users should have power to override the 
optimizer decisions. I haven't seen a single coherent argument why they 
shouldn't have that option and I definitely did have a need for hints. 


-- 
http://mgogala.byethost5.com

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

#181 — Re: Is PostgreSQL good?

Hi Mladen,

On 6/30/2011 3:42 PM, Mladen Gogala wrote:
> On Thu, 30 Jun 2011 13:21:17 -0700, Don Y wrote:
>
>> Gee, someone expresses an interest in understanding why you have such a
>> hard-on regarding "hints" and you dismiss it as "irrelevant".
>
> What I consider  irrelevant is the analogy with the C language.
> Furthermore, this debate is, at least in my humble opinion, relevant to
> DBA's, project managers and people who will write SQL against a Postgres
> database. I haven't envisioned this debate as a talk show with the
> viewers taking sides, Oprah already covers that market.

Again, you *presume* to understand my reason for participating.
I'm curious as to whether you are using a Ouija Board or a
Crystal Ball to get your information...?

Regardless, what makes "The Participants'" opinions more valuable
than the Viewers'?  *Unlike* Oprah, the Viewers aren't prevented
from *being* Participants -- now, or at some future date!

> As far as me having hard-on for hints, I needed them in one of my
> projects. Hints are a welcome option, offered by virtually all other RDBMS
> systems, yet there seems to be a problem with the designers of Postgres.
> I have yet to see a logical argument against providing that option and
> analogies with the C programming language don't seem to provide it,
> either. So, those analogies are irrelevant, at least in my point of view.
> You don't seem to be a database professional either, I am quite confused
> by the zeal with which you're trying to participate in this debate. Once
> again, this debate is about a specific feature of Postgres, or the lack
> of it.

How do you define a "database professional"?  Someone who is paid to
perform the duties of a DBA?  Someone who went to school to be a
DBA?  Someone who *calls* himself a DBA?

I am *acting* as a DBA on two projects currently.  I suspect I
will represent a greater "user base" than most folks here.  And,
probably, be responsible for more "managed data" than the typical
"viewer/participant".

I suspect I am using the DBMS in ways that most viewers/participants
here have never even considered ("Huh?  You can't do *that*...").
Do I want to make a career out of this?  Nope.  There are many
more interesting application domains to explore... this is just a
tool to facilitate work in a few of them!

Will the code I write and the tables I build stand up to "expert
scrutiny"?  Probably no more than *your* C code would stand up
to my scrutiny!  Yet, it will run as intended within the constraints
of the projects.

More to the point, I suspect I will represent more of a (financial)
contribution to the PostgreSQL project than, at the very least, *you*.
So, it behooves me to understand what I might like to put on my wish
list to *underwrite* (as you appear to have deep pockets -- but short
arms!).

Show me the courtesy of letting me make my own informed decisions
as any other participant/viewer here -- instead of arbitrarily
and unilaterally deciding on the criteria for "admission to the
debate".  I'm sorry if I have to bother you by *asking* questions
instead of just being "infused" with the right answer/opinion.
Unfortunately, I have neither a Ouija Board *nor* a Crystal Ball
to rely upon!

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

#182 — Re: Is PostgreSQL good?

On Fri, 01 Jul 2011 11:57:02 -0700, Don Y wrote:

> How do you define a "database professional"?  Someone who is paid to
> perform the duties of a DBA?  Someone who went to school to be a DBA? 
> Someone who *calls* himself a DBA?
> 
> I am *acting* as a DBA on two projects currently.  I suspect I will
> represent a greater "user base" than most folks here.  And, probably, be
> responsible for more "managed data" than the typical
> "viewer/participant".
> 
> I suspect I am using the DBMS in ways that most viewers/participants
> here have never even considered ("Huh?  You can't do *that*..."). Do I
> want to make a career out of this?  Nope.  There are many more
> interesting application domains to explore... this is just a tool to
> facilitate work in a few of them

And good luck to you.



-- 
http://mgogala.byethost5.com

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

#175 — Re: Is PostgreSQL good?

Mladen Gogala <no@email.here.invalid> writes:
> On Thu, 30 Jun 2011 16:49:48 +0100, Richard Kettlewell wrote:

>> The relevant text is:
>> 
>>     A declaration of an identifier for an object with storage-class
>>     specifier register suggests that access to the object be as fast as
>>     possible. The extent to which such suggestions are effective is
>>               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>     implementation-defined.
>>     ^^^^^^^^^^^^^^^^^^^^^^
>
>
> This is what I found:
> http://tigcc.ticalc.org/doc/keywords.html#register
[...]

That's the manual for a particular compiler.  The text I quoted is from
the language specification.

-- 
http://www.greenend.org.uk/rjk/

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

#176 — Re: Is PostgreSQL good?

On Fri, 01 Jul 2011 14:07:34 +0100, Richard Kettlewell wrote:

> That's the manual for a particular compiler.  The text I quoted is from
> the language specification.

With all due respect, I don't think that the analogy with the C compiler 
is particularly relevant for this discussion.



-- 
http://mgogala.byethost5.com

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

#170 — Re: Is PostgreSQL good?

Hi Mladen,

On 6/30/2011 8:41 AM, Mladen Gogala wrote:
> On Thu, 30 Jun 2011 07:05:54 -0700, Don Y wrote:
>
>> Are hints on a par with the 'register' keyword in C?
>
> Yes.
>
>> I.e., something
>> you *hope* gives the compiler an insight into your algorithm -- but,
>> that the compiler is free to *ignore*?
>
> C compiler is not free to ignore "register" keyword and neither is the
> database free to ignore hints. Compiler has to place your variable in a
> register, if there is one available. Database also must obey hints, if

No, the compiler can (and usually *does*) ignore the 'register'
keyword.  It (was originally) intended as a "hint" (in the generic
sense of the word) to the compiler for a possible optimization
"in the opinion of the programmer".

This is unlike, for example, the contract that "volatile" imposes
on the compiler (which can NOT be ignored).

[I obviously can't commeent on what the DBMS "must" do regarding
*its* "hints"]

I deliberately chose this example as it seems (to me) intuitively
related to what a hint *might* be in database-parlance.

> possible. Also, with C there is one more keyword, namely "asm", which may
> be used to coerce the code to behave precisely as you want.

Are you inferring that database hints impose the same sort of
constraints on the DBMS?

> This analogy is not good for several reasons, the main one being the
> difference between the directly executable vs. interpreted paradigms.
> If C was the only compiler to disallow "hints", it wouldn't be so
> popular. Now, if PostgreSQL was the only major database to disallow
> hints...?

This depends on the role "hints" (in their respective domains)
are expected to play.  E.g., I can *rarely* give a hint to a decent
C compiler that is *better* than what the compiler can deduce on
its own.  That's not a bad reflection on me but, rather, praise
for how advanced C optimizers have become.

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

#173 — Re: Is PostgreSQL good?

On Thu, 30 Jun 2011 13:17:13 -0700, Don Y wrote:

> That's not a bad reflection on me but, rather, praise for how advanced C
> optimizers have become.

Sadly, RDBMS optimizers haven't yet become so advanced.



-- 
http://mgogala.byethost5.com

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

#174 — Re: Is PostgreSQL good?

Don Y wrote:
> [apologies for not having the technical depth to understand these
> issues better -- I just use PostgreSQL as a "means to an end"...
> I could use any similar "means" but this seems to work well enough
> for me]

No worries, but don't take it as rudeness if I give you links for
some of these question instead of answering everything in detail.

> Can someone please, *gently*, explain *quantitatively* the differences
> that these issues make in performance?

Quantifying performance is only possible in a narrowly defined setting
(in my opinion).

> Are hints on a par with the 'register' keyword in C?  I.e.,
> something you *hope* gives the compiler an insight into your
> algorithm -- but, that the compiler is free to *ignore*?
> Are there consequences to using a "hint" (e.g., using 'register'
> places constraints on what you can do)?

Roughly yes. Except that (at least in Oracle) a hint will only
be ignored if it is impossible to use (you cannot force an index
that cannot be used for this query).

> Is the "hint" concept essentially a workaround to compensate for
> capabilities *missing* in the optimizer?  I.e., as C compilers
> get smarter, they deliberately *choose* to ignore the 'register'
> keyword because they actually have *more* insight than the
> programmer!

Yes, it is a workaround for optimizer deficiencies.
The problem here is that optimizing SQL statements is quite difficult
because SQL is a declarative language, i.e. you tell the system what
you want to get and not how to get it. The optimizer will have to
figure out an execution path.

> Are there ways (without using a "hint") to coerce the database
> to perform in the way your hint would *hope* to have it behave?
> (e.g., can you rewrite the SQL to cause the behavior you seek?)

Sometimes, yes. Often a simplified statement will be easier
to optimize. If you know the optimizer well, you can also trick
it into not considering certain optimizations.
The optimizer uses "statistics" which are data about the number
and distribution of data in table columns. A lot of times the
optimizer will perform better if you collect more data.
You can also set database parameters that influence the optimizer.

> What sort of *quantitative* difference can this make (example?)
> that can't, otherwise, be attained?

The effect of choosing a better plan can improve things *a lot*.
If you fetch 10 million rows, only to discard them later because
you join with an empty result set, that's *much* worse than not
fetching anything at all.

>> 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.
>
> Is this just the difference between automatic garbage collection
> and "manual" garbage collection (again, relying on parallels to
> other "languages")?

No. DB2 does not create garbage, because there is only one version
of each row at any given time. To get that, a writer has to wait
until a reader is done (i.e., the reader will take a "read lock"
on the row he or she is reading).

Oracle and PostgreSQL both create garbage, because they both keep
old versions of rows around to avoid readers blocking writers.
Oracle collects the garbage automatically *while* it executes
SQL statements.

PostgreSQL collects garbage at some later time, either manually
by an administrator running the VACUUM command, or automatically
if the autovacuum daemon is turned on.

> Does the parallel maintain throughout operation or does it fall
> apart at the extremes?  E.g., if garbage collection must be
> manually initiated (in a language), then failing to do so
> can eventually cause you to run out of heap, etc. (which
> causes the application to "fail").  Is the same true when
> it comes to vacuuming -- or, does performance just suffer?
> (i.e., will you ever "fill the disk" with dead rows if you
> fail to initiate a VACUUM operation?)

All of this is only relevant if you have autovacuum turned off
(autovacuum is the recommended way these days).

Without autovacuum and if VACUUM is never run, two things will
happen:
1) Your tables and indexes will grow on every insert and update,
   and deletes and updates will create garbage that cannot be
   reused. That can fill the disk and drastically increase the
   duration of table and index scans.
2) You will at some point run out of transaction IDs, and the
   system will shutdown to prevent data loss.

See
http://www.postgresql.org/docs/current/static/routine-vacuuming.html
for details.

>> 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?
>
> The last question seems to be my understanding of how things should
> work. (?)  (actually, one of the harder ideas for me to get accustomed
> to in this field  :< )

Right. It was a rethorical question.

To digress, I always find a library a good analogy for a database.
You have a lot of books there, each has a title and an author.

A primary key is a) an index (like an index in a library) and
b) a rule that says that there may only be one book for every
possible index entry.

Without a primary key, one author could have more than one book with
the same title, and you won't be able to tell them apart (even though
the books themselves may be different).

> How does a ROWID differ from using an OID?  (which I presume is
> not a good thing)

The ROWID is the physical location of the row, basically the offset
in the file. You can sort of just "lseek(3)" there and get it. This
is the fastest way to access a row.

An OID is a number from a system counter that *can* also be used
as a primary key. To some extent, it's just a more old-fashioned
way of what can be done better with a sequence these days.
If used as a primary key, it is no faster than other primary keys.

See the final paragraphs of
http://www.postgresql.org/docs/current/static/ddl-system-columns.html
for details.

>> Fixed size of WAL segments:
>> ===========================
>>
>> Maybe I am dense, but what is the problem there?
>
> <grin>  I don't even know what they *are*!  ;-)

WAL stands for "write ahead log" and is a transaction log.
It is a way to provide durability for committed transactions in case
of a system failure.

See
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html
for details.

Yours,
Laurenz Albe 

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

#177 — Re: Is PostgreSQL good?

On Fri, 01 Jul 2011 10:13:12 +0200, Laurenz Albe wrote:

> No. DB2 does not create garbage, because there is only one version of
> each row at any given time. To get that, a writer has to wait until a
> reader is done (i.e., the reader will take a "read lock" on the row he
> or she is reading).

Actually, DB2 also has MVCC, since the version 9.7 which was released, if 
I remember correctly, in 2009. One should set the isolation level to 
"currently committed" and voila, there is MVCC. 



-- 
http://mgogala.byethost5.com

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

#178 — Re: Is PostgreSQL good?

Mladen Gogala wrote:
>> No. DB2 does not create garbage, because there is only one version of
>> each row at any given time. To get that, a writer has to wait until a
>> reader is done (i.e., the reader will take a "read lock" on the row he
>> or she is reading).
>
> Actually, DB2 also has MVCC, since the version 9.7 which was released, if
> I remember correctly, in 2009. One should set the isolation level to
> "currently committed" and voila, there is MVCC.

Thanks for the correction. I have not worked with DB2 since 2001.

Yours,
Laurenz Albe 

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

Page 2 of 3 — ← Prev page 1 [2] 3  Next page →

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

csiph-web