Hope for a new PostgreSQL era?

On Wed, Dec 7, 2011 at 7:52 PM, Rodrigo E. De León Plicet
<rdeleonp@gmail.com> wrote:

http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/

Some of the points mentioned:
- MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL
 in some ways.  (Database extensibility if nothing else.)
- Neither EnterpriseDB (which now calls itself “The enterprise
PostgreSQL
 company”) nor the PostgreSQL community leadership have covered
themselves
 with stewardship glory.
- PostgreSQL advancement is not dead.

I'd like to see the author's thoughts filled out on these points.
they seem rather vague and overly simplistic, and I wonder what
specific points he might have to make rather than this vague "hand
wavy" list he has so far.

On Wed, Dec 7, 2011 at 8:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

I'd like to see the author's thoughts filled out on these points.
they seem rather vague and overly simplistic, and  I wonder what
specific points he might have to make rather than this vague "hand
wavy" list he has so far.

Additionally I am not entirely sure what he means by the last point.
If you look at the work that NTT along with EDB has put into
Postgres-XC, for example, it looks to me like the Postgres ecosystem
is growing by leaps and bounds and we are approaching an era where
Oracle is no longer ahead in any significant use case.

The thing I am personally worried about is the ability of one company
to dominate the framing of PostgreSQL service offerings. For example
while in the US it hasn't caught on, a lot of people at MYGOSSCON
accepted EnterpriseDB's framing of the official PostgreSQL release as
the "community edition." If you have a single vendor which dominates
the dialogue that's a bad thing. To be clear this isn't a criticism
of EDB. I greatly appreciate the substantial effort they have put
into building Pg awareness here in SE Asia. However, it is a caution
about the recommendation that we need a corporate steward. I argue
corporate stewardship would be a strong net negative because it would
be first and foremost a way to crowd everyone else out. We have
stewardship. It's the core committee, and it's the best kind of
stewardship we can have.

Here's a useful post that I was forwarded by another LSMB developer.
http://openlife.cc/blogs/2010/november/how-grow-your-open-source-project-10x-and-revenues-5x

Additionally, I would suggest that PostgreSQL has a lot of users
because we have a great---and open---community. I think a new
PostgreSQL era is coming but I don't think it will happen the way that
blog poster implies. There is a tremendous need for Pg skills in SE
Asia right now, and I expect this to continue to grow exponentially.

PostgreSQL advancement also by my view is also not merely "not dead"
but in fact accelerating.

Best Wishes,
Chris Travers

On Dec 8, 2011 1:27 PM, "Chris Travers" <chris.travers@gmail.com> wrote:

Additionally I am not entirely sure what he means by the last point.
If you look at the work that NTT along with EDB has put into
Postgres-XC, for example, it looks to me like the Postgres ecosystem
is growing by leaps and bounds and we are approaching an era where
Oracle is no longer ahead in any significant use case.

While Pg is impressively capable now, I don't agree that Oracle (if DB2,
MS-SQL etc) isn't ahead for any significant use case. Not on a purely
technical basis anyway - once cost is considered there may be a stronger
argument.

Multi-tenant hosting is a weak pint for Pg for quite a few reasons, done of
which appear below. It's not the only role Pg isn't a great fit for, but
probably one of the more obvious.

Areas in which Pg seems significantly less capable include:

- multi-tenant hosting and row level security

- admission control, queuing and resource limiting to optimally load a
machine. Some limited level is possible with external pooling, but only by
limiting concurrent workers.

- performance monitoring and diagnostics. It's way harder to find out
what's causing load on a busy Pg server or report on frequent/expensive
queries etc. Tooling is limited and fairly primitive. It's find, but
nowhere near as powerful and easy as some if the other DBs.

- prioritisation of queries or users. It's hard to say "prefer this query
over this one, give it more resources" or "user A's work always preempts
user B's" in Pg.

- transparent failover and recovery back to the original master.

- shared-storage clustering. Dunno if anyone still cares about this one
though.

On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

Areas in which Pg seems significantly less capable include:

Please can you explain the features Oracle has in these area, I'm not
clear. Thanks.

- admission control, queuing and resource limiting to optimally load a
machine. Some limited level is possible with external pooling, but only by
limiting concurrent workers.

- prioritisation of queries or users. It's hard to say "prefer this query
over this one, give it more resources" or "user A's work always preempts
user B's" in Pg.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 8 Prosinec 2011, 12:24, Craig Ringer wrote:

- admission control, queuing and resource limiting to optimally load a
machine. Some limited level is possible with external pooling, but only by
limiting concurrent workers.

The first thing I'd like to see is "user profiles" - being able to set
things like work_mem, synchronous_commit, etc. on per-user basis
separately.

- performance monitoring and diagnostics. It's way harder to find out
what's causing load on a busy Pg server or report on frequent/expensive
queries etc. Tooling is limited and fairly primitive. It's find, but
nowhere near as powerful and easy as some if the other DBs.

True. Greg Smith actually mentioned this as one of the frequently asked
features in his post about two weeks ago
(http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html).
I've started to build my own tool and got it somehow working for my needs,
and there are other tools available, but none of them is really a complete
solution. Would be nice to form a dev group that would work on this.

- prioritisation of queries or users. It's hard to say "prefer this query
over this one, give it more resources" or "user A's work always preempts
user B's" in Pg.

I wonder if the prioritisation could be done using nice - each backend is
a separate process, so why not to do 'nice(10)' for low priority processes
or something like that.

On 12/08/2011 08:53 PM, Tomas Vondra wrote:

On 8 Prosinec 2011, 12:24, Craig Ringer wrote:

- admission control, queuing and resource limiting to optimally load a
machine. Some limited level is possible with external pooling, but only by
limiting concurrent workers.
o d

The first thing I'd like to see is "user profiles"z- being able to set
things like work_mem, synchronous_commit, etc. on per-user basis
separately.

You can.

ALTER USER username SET work_mem = '100MB';

It's not a hard cap - the user can raise/lower it however they like. The
initial value can be set globally, per-user, per-database, or globally.

I wonder if the prioritisation could be done using nice - each backend
is a separate process, so why not to do 'nice(10)' for low priority
processes or something like that.

Yes, to a limited degree you can prioritise queries using nice and
ionice, but it's awkward because:

- All queries run as `postgres' so you can't do per-user limiting very
easily

- The postmaster doesn't have a way to set the nice level and ionice
level when it forks a backend, nor does the backend have any way to do
it later. You can use your own user-defined C functions for this, though.

- Most importantly, even if you nice and ionice using C functions or
manually with the cmdline utilities, you can't affect the bgwriter, nor
can you affect how much data a low-priority query pushes out of cache.

--
Craig Ringer

On Thu, Dec 08, 2011 at 01:53:45PM +0100, Tomas Vondra wrote:

I wonder if the prioritisation could be done using nice - each backend is
a separate process, so why not to do 'nice(10)' for low priority processes
or something like that.

This won't work because if you are holding a lock on something someone
else needs, your low nice score is going to cause them problems. It
could make things worse rather than better. (This suggestion comes up
a lot, by the way, so there's been a lot of discussion of it
historically.)

--
Andrew Sullivan
ajs@crankycanuck.ca

2011/12/08 21:53, Tomas Vondra wrote:

- performance monitoring and diagnostics. It's way harder to find out
what's causing load on a busy Pg server or report on frequent/expensive
queries etc. Tooling is limited and fairly primitive. It's find, but
nowhere near as powerful and easy as some if the other DBs.

True. Greg Smith actually mentioned this as one of the frequently asked
features in his post about two weeks ago
(http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html).
I've started to build my own tool and got it somehow working for my needs,
and there are other tools available, but none of them is really a complete
solution. Would be nice to form a dev group that would work on this.

Seems a good point. I'm trying to build "a complete solution". :)

Anyway, one of the reasons of such difficulties to build "a complete solution"
is based on necessity of the support from the *entire* core code. Without the
core support, a complete solution would never be built. Obtaining LWLock
statistics or write I/O operations is actually pretty tough work for
"non-experienced" PostgreSQL DBA, like me. :)

For examples, I've been working on investigating PostgreSQL LWLock behaviors
precisely for a few weeks, and it could not be obtained within PostgreSQL
itself, therefore, I picked up SystemTap. However, SystemTap could not be
used in a production system, because it often kills the target processes. :(
How can I observe LWLocks in the production system?

There are several tools to monitor system behaviors around operating systems,
but it is far from understanding PostgreSQL behavior. And DBAs coming from
other RDBMSes, in particular proprietary RDBMSes, need it, because they've
already been using such facilities (or tools) in their RDBMSes.
That's the reason why we need more facilities to observe inside PostgreSQL.

In addition, one more reason of the difficulties is that experienced
PostgreSQL DBAs (or hackers) do not need such facilities in general,
because they can imagine how PostgreSQL works in such particular situation.

I still think we can implement (or enhance) for those facilities if we
focus on it, but I sometimes feel it's like "a chicken and egg situation".

Regards,
--
NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>

On Wed, Dec 7, 2011 at 8:52 PM, Rodrigo E. De León Plicet
<rdeleonp@gmail.com> wrote:

http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/

Some of the points mentioned:
- MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL
 in some ways.  (Database extensibility if nothing else.)

There is simply no comparing mysql's backend programming features with
those of postgres. Postgres is a development platform in a box, mysql
is not.

merlin

Le Thu, 8 Dec 2011 12:27:22 +0000,
Simon Riggs <simon@2ndQuadrant.com> a écrit :

On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc@ringerc.id.au>
wrote:

Areas in which Pg seems significantly less capable include:

Please can you explain the features Oracle has in these area, I'm not
clear. Thanks.

Maybe I can answer from my own Oracle experience. I hope it will be what
Craig had in mind :)

- admission control, queuing and resource limiting to optimally
load a machine. Some limited level is possible with external
pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of accepting
connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
connections (called dispatchers) and processes doing the real work
(called workers, obviously). All of this works internally with
some sort of queuing and storing results in shared memory (I don't
remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database simultaneously. So
it's easier to keep the load on the server to a reasonable value.

- prioritisation of queries or users. It's hard to say "prefer this
query over this one, give it more resources" or "user A's work
always preempts user B's" in Pg.

It's called the resource manager in Oracle. You define 'resource plans',
'consumer groups', etc… and you get some sort of QoS for your queries.
It's mostly about CPU resource allocation if I remember correctly (I
never used it, except during training :) )

Being able of changing the backend's nice level may do something
similar I guess. I don't think Oracle's resource manager solves
the priority inversion due to locking in the database, but I'm not sure
of it.

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc@gmail.com> wrote:

Le Thu, 8 Dec 2011 12:27:22 +0000,
Simon Riggs <simon@2ndQuadrant.com> a écrit :

On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc@ringerc.id.au>
wrote:

Areas in which Pg seems significantly less capable include:

Please can you explain the features Oracle has in these area, I'm not
clear. Thanks.

Maybe I can answer from my own Oracle experience. I hope it will be what
Craig had in mind :)

- admission control, queuing and resource limiting to optimally
load a machine. Some limited level is possible with external
pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of accepting
 connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
 connections (called dispatchers) and processes doing the real work
 (called workers, obviously). All of this works internally with
 some sort of queuing and storing results in shared memory (I don't
 remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database simultaneously. So
it's easier to keep the load on the server to a reasonable value.

you have a couple of very good options to achieve the same in postgres
-- pgbouncer, pgpool.

merlin

On Thu, Dec 08, 2011 at 09:29:28AM -0600, Merlin Moncure wrote:

you have a couple of very good options to achieve the same in postgres
-- pgbouncer, pgpool.

One of the central issues that Postgres has in the "enterprise" land
is exactly this sort of answer: "you have good options" but they're
"not part of the core release".

Now, we all know that this is a stupid and wrong way of thinking about
it. But one cannot complain about being held to those sorts of
enterprisey standards when one is having an enterprisey discussion.
The original analysis (on the blog) seemed to be primarily aimed at
exactly that sort of discussion, and I suspect that this is the kind
of thing that was meant by the "community leadership" not having
covered itself in stewardship glory. One of the "stewardship" tests,
from a business-analysis point of view, is whether you're going to be
able to find a reliable supply of experienced admins at all levels for
your systems.

Having a bunch of different, indifferently-documented projects that
are all doing similar but slightly different things is, to someone
looking from that point of view, a liability and not a strength. I
happen to disagree, but it always seemed to me that something the
Postgres community did poorly (and I count myself in that number,
though less now than in the past) was understanding the hardships of
the integrator and coming up with reasonably simple answers for those
kinds of questions. It is not unreasonable to say that there are no
simple answers here; but as unhappy as it makes me, those reasonably
simple answers are necessary for some classes of users.

And let's face it: companies like Oracle (and products like MySQL) are
in a position to treat those sorts of answers as part of the cost of
doing business, because they have revenue associated with their
licenses so they can pay for coming up with those answers that way.
In Postgres-land, everyone needs to charge money for those answers
(i.e. be consultants), because that's the only real place to make a
living. Alternatively, you can put together those answers as part of
your own package; but in that case, it's not "the core PostgreSQL
product", but something else.

In this respect, the decision of the core team a number of years ago
to say, "We're going to have 'integrated' replication that does x, y,
and z," was the right one, despite the fact that it undermined the
momentum of other interesting projects (and ones better suited to some
environments). Sometimes, it's better to cut off options.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Le Thu, 8 Dec 2011 09:29:28 -0600,
Merlin Moncure <mmoncure@gmail.com> a écrit :

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc@gmail.com>
wrote:

Le Thu, 8 Dec 2011 12:27:22 +0000,
Simon Riggs <simon@2ndQuadrant.com> a écrit :

On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer
<ringerc@ringerc.id.au> wrote:

Areas in which Pg seems significantly less capable include:

Please can you explain the features Oracle has in these area, I'm
not clear. Thanks.

Maybe I can answer from my own Oracle experience. I hope it will be
what Craig had in mind :)

- admission control, queuing and resource limiting to optimally
load a machine. Some limited level is possible with external
pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of
accepting connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
 connections (called dispatchers) and processes doing the real work
 (called workers, obviously). All of this works internally with
 some sort of queuing and storing results in shared memory (I don't
 remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database
simultaneously. So it's easier to keep the load on the server to a
reasonable value.

you have a couple of very good options to achieve the same in postgres
-- pgbouncer, pgpool.

I wish it was the same (I use and like both pgbouncer and pgpool too,
and they do a good job, I'm not arguing on that). But unfortunately it
isn't: you still have the notion of session for each connected client
in Oracle when using the shared servers model.

It means you keep your session variables, your prepared statements,
your running transaction, etc… in each individual session while having
the multiplexing equivalent of a 'statement level' from pgbouncer.

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc@gmail.com>

wrote:

I wish it was the same (I use and like both pgbouncer and pgpool too,
and they do a good job, I'm not arguing on that). But unfortunately it
isn't: you still have the notion of session for each connected client
in Oracle when using the shared servers model.

It means you keep your session variables, your prepared statements,
your running transaction, etc… in each individual session while having
the multiplexing equivalent of a 'statement level' from pgbouncer.

In Oracle - can the pool share connections between DB users and/or databases on the instance? If the answer is yes to either, that is a fair bit better than what we can achieve today.

Brad.

On 8 Prosinec 2011, 14:17, Craig Ringer wrote:

You can.

ALTER USER username SET work_mem = '100MB';

It's not a hard cap - the user can raise/lower it however they like. The
initial value can be set globally, per-user, per-database, or globally.

Oh, shame on me! I wasn't aware of this. Too bad it's not possible to
restrict this (changing certain config values). A simple 'before SET' hook
might do the trick.

I wonder if the prioritisation could be done using nice - each backend
is a separate process, so why not to do 'nice(10)' for low priority
processes or something like that.

Yes, to a limited degree you can prioritise queries using nice and
ionice, but it's awkward because:

- All queries run as `postgres' so you can't do per-user limiting very
easily

- The postmaster doesn't have a way to set the nice level and ionice
level when it forks a backend, nor does the backend have any way to do
it later. You can use your own user-defined C functions for this, though.

Yes, that's what I meant.

- Most importantly, even if you nice and ionice using C functions or
manually with the cmdline utilities, you can't affect the bgwriter, nor
can you affect how much data a low-priority query pushes out of cache.

IMHO bgwriter may be reasonably tuned by bgwriter_* GUC variables. The
user backends are probably more interesting here.

Tomas

On 8 Prosinec 2011, 16:11, Marc Cousin wrote:

- admission control, queuing and resource limiting to optimally
load a machine. Some limited level is possible with external
pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of accepting
connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
connections (called dispatchers) and processes doing the real work
(called workers, obviously). All of this works internally with
some sort of queuing and storing results in shared memory (I don't
remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database simultaneously. So
it's easier to keep the load on the server to a reasonable value.

Which is exactly what pgbouncer and other connection pools are for ...

- prioritisation of queries or users. It's hard to say "prefer this
query over this one, give it more resources" or "user A's work
always preempts user B's" in Pg.

It's called the resource manager in Oracle. You define 'resource plans',
'consumer groups', etc… and you get some sort of QoS for your queries.
It's mostly about CPU resource allocation if I remember correctly (I
never used it, except during training :) )

And it's damn difficult to get it working properly ... the simpler the
better here.

Tomas

On Thu, Dec 8, 2011 at 10:11 AM, Marc Cousin <cousinmarc@gmail.com> wrote:

Le Thu, 8 Dec 2011 09:29:28 -0600,
Merlin Moncure <mmoncure@gmail.com> a écrit :

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc@gmail.com>
wrote:

Le Thu, 8 Dec 2011 12:27:22 +0000,
Simon Riggs <simon@2ndQuadrant.com> a écrit :

On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer
<ringerc@ringerc.id.au> wrote:

Areas in which Pg seems significantly less capable include:

Please can you explain the features Oracle has in these area, I'm
not clear. Thanks.

Maybe I can answer from my own Oracle experience. I hope it will be
what Craig had in mind :)

- admission control, queuing and resource limiting to optimally
load a machine. Some limited level is possible with external
pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of
accepting connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
 connections (called dispatchers) and processes doing the real work
 (called workers, obviously). All of this works internally with
 some sort of queuing and storing results in shared memory (I don't
 remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database
simultaneously. So it's easier to keep the load on the server to a
reasonable value.

you have a couple of very good options to achieve the same in postgres
-- pgbouncer, pgpool.

I wish it was the same (I use and like both pgbouncer and pgpool too,
and they do a good job, I'm not arguing on that). But unfortunately it
isn't: you still have the notion of session for each connected client
in Oracle when using the shared servers model.

It means you keep your session variables, your prepared statements,
your running transaction, etc… in each individual session while having
the multiplexing equivalent of a 'statement level' from pgbouncer.

yeah -- maybe we could use a server side feature that could allow you
to save a session state and load it up later to make life easier for
connection pooled applications. however, it's not really that much
work to organize most of the things you'd use for this in an
application managed session instead of database managed one.

regarding the "enterprises won't use community supplied postgresql add
ons" point, this completely true in many cases. I do think pgbouncer
should be seriously considered for advancement as a core feature. That
said, this should be done on its own merits, not to satisfy the
capricious whims of enterprises.

merlin

Le Thu, 8 Dec 2011 16:27:56 +0000,
"Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> a écrit :

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc@gmail.com>

wrote:

I wish it was the same (I use and like both pgbouncer and pgpool
too, and they do a good job, I'm not arguing on that). But
unfortunately it isn't: you still have the notion of session for
each connected client in Oracle when using the shared servers model.

It means you keep your session variables, your prepared statements,
your running transaction, etc… in each individual session while
having the multiplexing equivalent of a 'statement level' from
pgbouncer.

In Oracle - can the pool share connections between DB users and/or
databases on the instance? If the answer is yes to either, that is a
fair bit better than what we can achieve today.

Between users yes. But there is only one DB per instance in Oracle :)

On 8 Prosinec 2011, 17:11, Marc Cousin wrote:

Le Thu, 8 Dec 2011 09:29:28 -0600,

- admission control, queuing and resource limiting to optimally
load a machine. Some limited level is possible with external
pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of
accepting connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
 connections (called dispatchers) and processes doing the real work
 (called workers, obviously). All of this works internally with
 some sort of queuing and storing results in shared memory (I don't
 remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database
simultaneously. So it's easier to keep the load on the server to a
reasonable value.

you have a couple of very good options to achieve the same in postgres
-- pgbouncer, pgpool.

I wish it was the same (I use and like both pgbouncer and pgpool too,
and they do a good job, I'm not arguing on that). But unfortunately it
isn't: you still have the notion of session for each connected client
in Oracle when using the shared servers model.

True, it is not exactly the same, it's similar. I don't think PostgreSQL
will ever provide 'shared backends' the way Oracle does - it would require
significant code change. Most of the benefits can be achieved by using a
connection pool without the added complexity.

It means you keep your session variables, your prepared statements,
your running transaction, etc… in each individual session while having
the multiplexing equivalent of a 'statement level' from pgbouncer.

Yes. But if you expect that PostgreSQL will mimic Oracle architecture,
then you're mistaken. It's simply a different solution with different
architecture, and that means you may need to use different application
design sometimes.

Tomas