Limitations of PostgreSQL

On Mon, Oct 03, 2005 at 12:01:02PM -0000, Denis G Dudhia wrote:

I would like to know the limitations of PostgreSQL.

Also, I would like to know, whether there is any limitations for the
number of records or the size of the tables or database in
PostgreSQL?

See PostgreSQL FAQ.

Section 4.4) What is the maximum size for a row, a table, and a database?

http://www.postgresql.org/docs/faqs.FAQ.html#4.4

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Denis G Dudhia wrote:

Hello There...

I am new to PostgreSQL.

I usually check out negative sides of any software or system, before
implementing it or using it.

Compared to MySQL, I can't think of any downsides. All relevant
usability issues have been solved, though there are some functions like
INTERVAL that are not supported (see my migration guide at
http://www.metatrontech.com/wpapers/)

Compared to Oracle, DB2, Terradata, etc, PostgreSQL lacks support for
parallelism in queries, thereby making it a poor choice for certain
types of data warehousing applications. This being said, 8.1, depending
on the application, may meet your needs once it comes out if you are
doing data warehousing. Josh Berkus can correct me if I am wrong, but
I believe he is working on a version of the open source Bizgress with
will have massively parallel queries.

Multimaster async replication w/updates is a pain at the moment and
mostly a set of kludges.

I am not aware of any good sync. replication solutions for PostgreSQL at
the moment.

Does not have full XA support at the moment (does have TPC).

May not be the best choice on Windows for production use, though for
development, it should be adequate.

Hope this helps,
Chris Travers
Metatron Technology COnsulting

On Wed, 2005-10-12 at 16:16, Chris Travers wrote:

Denis G Dudhia wrote:

Hello There...

I am new to PostgreSQL.

I usually check out negative sides of any software or system, before
implementing it or using it.

Compared to MySQL, I can't think of any downsides. All relevant
usability issues have been solved, though there are some functions like
INTERVAL that are not supported (see my migration guide at
http://www.metatrontech.com/wpapers/)

What, exactly, is the interval function in MySQL? IS that one that
creates a sequence of numbers or whatnot? If so, there is an equivalent
in 8.0 now. By the way, interval is a SQL reserved keyword, so it's
surprising MySQL would choose to name a function after it.

Thought I'd comment on this.

According to the author of the innodb engine, innodb uses MVCC.
OTOH, I consider innodb to be broken in production, due to issues with
constant growth and no way to reclaim the lost space.

This means that vacuuming, a minor annoyance in PostgreSQL, is a major
issue for 24/7 mysql databases running on innodb, where they must be
shut down and restarted to clear up the unused space in the innodb
tablespace.

About Mammoth, I'm pretty sure that when you purchase a support contract
you get a copy of the source code.

Multimaster async replication w/updates is a pain at the moment and
mostly a set of kludges.

There really are too many use cases for there to be a "simple"
resolution to the problems presented by multi-master replication. It's
a complex problem that creates more complex problems as you attempt to
solve it.

I am not aware of any good sync. replication solutions for PostgreSQL at
the moment.

pgpool does a good job. Many folks miss the fact that it can do
replication as well as load balancing. pgcluster uses parts of pgpool
to do its clustering as well. They are, however, statement level, not
log level.

Does not have full XA support at the moment (does have TPC).

I'd point out here that MySQL's XA support is quite primitive, and only
useful for a fairly smaller number of cases.

May not be the best choice on Windows for production use, though for
development, it should be adequate.

Agreed...

[snipped]

May not be the best choice on Windows for production use, though for
development, it should be adequate.

Are there known issues with the Windows version for production use, or
is this simply because of the relative newness of the Windows-native
version?

- Bill

On Wed, Oct 12, 2005 at 04:49:59PM -0500, Scott Marlowe wrote:

On Wed, 2005-10-12 at 16:16, Chris Travers wrote:

Compared to MySQL, I can't think of any downsides. All relevant
usability issues have been solved, though there are some functions like
INTERVAL that are not supported (see my migration guide at
http://www.metatrontech.com/wpapers/)

What, exactly, is the interval function in MySQL? IS that one that
creates a sequence of numbers or whatnot? If so, there is an equivalent
in 8.0 now. By the way, interval is a SQL reserved keyword, so it's
surprising MySQL would choose to name a function after it.

Surprising? C'mon now, this is MySQL :->

Here's an excerpt from the MySQL documentation:

INTERVAL(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is
NULL. All arguments are treated as integers. It is required
that N1 < N2 < N3 < ... < Nn for this function to work
correctly. This is because a binary search is used (very fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
-> 0

--
Michael Fuhr

Wow, I must be missing something, because I cannot even dream up a case
where I think I might find a use for the mysql INTERVAL() function, far
less actually NEED it :)

Terry

Michael Fuhr wrote:

On Wed, Oct 12, 2005 at 04:49:59PM -0500, Scott Marlowe wrote:

On Wed, 2005-10-12 at 16:16, Chris Travers wrote:

Compared to MySQL, I can't think of any downsides. All relevant
usability issues have been solved, though there are some functions like
INTERVAL that are not supported (see my migration guide at
http://www.metatrontech.com/wpapers/)

What, exactly, is the interval function in MySQL? IS that one that
creates a sequence of numbers or whatnot? If so, there is an equivalent
in 8.0 now. By the way, interval is a SQL reserved keyword, so it's
surprising MySQL would choose to name a function after it.

Surprising? C'mon now, this is MySQL :->

Here's an excerpt from the MySQL documentation:

INTERVAL(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is
NULL. All arguments are treated as integers. It is required
that N1 < N2 < N3 < ... < Nn for this function to work
correctly. This is because a binary search is used (very fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
-> 0

--
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

Scott Marlowe wrote:

On Wed, 2005-10-12 at 16:16, Chris Travers wrote:

Denis G Dudhia wrote:

Hello There...

I am new to PostgreSQL.

I usually check out negative sides of any software or system, before
implementing it or using it.

Compared to MySQL, I can't think of any downsides. All relevant
usability issues have been solved, though there are some functions like
INTERVAL that are not supported (see my migration guide at
http://www.metatrontech.com/wpapers/)

What, exactly, is the interval function in MySQL? IS that one that
creates a sequence of numbers or whatnot? If so, there is an equivalent
in 8.0 now. By the way, interval is a SQL reserved keyword, so it's
surprising MySQL would choose to name a function after it.

It is sort of like LEAST and GREATEST but somewhat different....

For example, INTERVAL(45, 0, 20, 40, 80, 100) will return 3 (I think)
because 45 is between 40 and 80.

I don't know if it is specified in the standard or not or even if it is
useful. Just thought I would mention it.

Thought I'd comment on this.

According to the author of the innodb engine, innodb uses MVCC.
OTOH, I consider innodb to be broken in production, due to issues with
constant growth and no way to reclaim the lost space.

Any sources on that? I would love to have info on that.

This means that vacuuming, a minor annoyance in PostgreSQL, is a major
issue for 24/7 mysql databases running on innodb, where they must be
shut down and restarted to clear up the unused space in the innodb
tablespace.

No kidding. Would like more info.

Multimaster async replication w/updates is a pain at the moment and
mostly a set of kludges.

There really are too many use cases for there to be a "simple"
resolution to the problems presented by multi-master replication. It's
a complex problem that creates more complex problems as you attempt to
solve it.

I have come up with some ways of doing this but they are difficult. And
the question is always "How good is good enough"

I am not aware of any good sync. replication solutions for PostgreSQL at
the moment.

pgpool does a good job. Many folks miss the fact that it can do
replication as well as load balancing. pgcluster uses parts of pgpool
to do its clustering as well. They are, however, statement level, not
log level.

I will remember that.

Does not have full XA support at the moment (does have TPC).

I'd point out here that MySQL's XA support is quite primitive, and only
useful for a fairly smaller number of cases.

Again, I was comparing with DB2 and Oracle. One should consider all new
features of MySQL to be both overmarketed and primitive for some time.

Best Wishes,
Chris Travers
Metatron Technology Consulting

On Wed, 2005-10-12 at 16:49 -0500, Scott Marlowe wrote:

On Wed, 2005-10-12 at 16:16, Chris Travers wrote:

Denis G Dudhia wrote:

Hello There...

I am new to PostgreSQL.

I usually check out negative sides of any software or system, before
implementing it or using it.

<snip request for pimples>

<snip pimple list>

Remarkable how exchanges like that continue to leave an impression with
me. I can't believe I'm the only one, though..

Somebody says, "Hey, can you tell me what's wrong with your product?"
And there's an answer from the product's camp: "Well, the consensus is
pretty much this...".

Simple request, honest answer.
That's the kind of currency that should be coined everywhere.

PostgreSQL has a strong and growing following because of its quality and
capability, and more power to it, I say. But there are plenty of us who
have made the choice between equal functionalities, based completely
upon the quality I've just mentioned.

Outstanding, folks.

(as well as outstanding folks)

Andy

Bill Bartlett wrote:

[snipped]

May not be the best choice on Windows for production use, though for
development, it should be adequate.

Are there known issues with the Windows version for production use, or
is this simply because of the relative newness of the Windows-native
version?

Performance isn't as good (yet) as on the *nix releases, and there are
issues with the autovacuum module and unicode support. Might be worth
checking out the 8.1 beta release if you have specific requirements.

--
Richard Huxton
Archonet Ltd

I would like to believe that its the latter, it should improve with time.

On Wed, 12 Oct 2005, Bill Bartlett wrote:

[snipped]

May not be the best choice on Windows for production use, though for
development, it should be adequate.

Are there known issues with the Windows version for production use, or
is this simply because of the relative newness of the Windows-native
version?

- Bill

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

[snipped]

May not be the best choice on Windows for production use,

though for

development, it should be adequate.

Are there known issues with the Windows version for

production use, or

is this simply because of the relative newness of the

Windows-native

version?

Performance isn't as good (yet) as on the *nix releases, and
there are issues with the autovacuum module and unicode
support. Might be worth checking out the 8.1 beta release if
you have specific requirements.

Specifically, both autovacuum and unicode support is fixed in 8.1. Along
with other things, of course , but those are the two big ones biting
win32 people in 8.0.
(IIRC nothing specific done for win32 regarding performance since 8.0.x,
for example - but it was done in one of the early 8.0.x releases)

//Magnus

On Wed, 2005-10-12 at 23:11, Chris Travers wrote:

Scott Marlowe wrote:

Thought I'd comment on this.

According to the author of the innodb engine, innodb uses MVCC.
OTOH, I consider innodb to be broken in production, due to issues with
constant growth and no way to reclaim the lost space.

Any sources on that? I would love to have info on that.

Just do a google search for innodb and mvcc. Lots of soruces, not sure
how good any one might be.

This means that vacuuming, a minor annoyance in PostgreSQL, is a major
issue for 24/7 mysql databases running on innodb, where they must be
shut down and restarted to clear up the unused space in the innodb
tablespace.

No kidding. Would like more info.

Setup a MySQL database with 5.0.13 and insert and delete a lot in an
mvcc table. The innodb tablespace just grows and grows.

Multimaster async replication w/updates is a pain at the moment and
mostly a set of kludges.

There really are too many use cases for there to be a "simple"
resolution to the problems presented by multi-master replication. It's
a complex problem that creates more complex problems as you attempt to
solve it.

I have come up with some ways of doing this but they are difficult. And
the question is always "How good is good enough"

Right, and the real issue is that what might be a good compromise
between performance and data security in one situation might be an awful
choice in another. So, multi-master replication isn't likely to become
a plug in module for postgresql any time soon.

Does not have full XA support at the moment (does have TPC).

I'd point out here that MySQL's XA support is quite primitive, and only
useful for a fairly smaller number of cases.

Again, I was comparing with DB2 and Oracle. One should consider all new
features of MySQL to be both overmarketed and primitive for some time.

True. PostgreSQL IS working on XA support (specifically the jdbc list
is.)

On Wed, 2005-10-12 at 20:08, Michael Fuhr wrote:

On Wed, Oct 12, 2005 at 04:49:59PM -0500, Scott Marlowe wrote:

On Wed, 2005-10-12 at 16:16, Chris Travers wrote:

Compared to MySQL, I can't think of any downsides. All relevant
usability issues have been solved, though there are some functions like
INTERVAL that are not supported (see my migration guide at
http://www.metatrontech.com/wpapers/)

What, exactly, is the interval function in MySQL? IS that one that
creates a sequence of numbers or whatnot? If so, there is an equivalent
in 8.0 now. By the way, interval is a SQL reserved keyword, so it's
surprising MySQL would choose to name a function after it.

Surprising? C'mon now, this is MySQL :->

Here's an excerpt from the MySQL documentation:

INTERVAL(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is
NULL. All arguments are treated as integers. It is required
that N1 < N2 < N3 < ... < Nn for this function to work
correctly. This is because a binary search is used (very fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);

I could see how it might be possible to make a two argument user defined
function that took an argument like:

select intvl(10,'20 30 40 50 60');

so that the multiple arguments are really just a space or comma
separated list fed to the function. I wouldn't name it interval though.
:)

On Thu, Oct 13, 2005 at 10:46:29AM -0500, Scott Marlowe wrote:

choice in another. So, multi-master replication isn't likely to become
a plug in module for postgresql any time soon.

It's not even a thing, so it can't become a plug-in.

Consider just two kinds of multi-master:

1. Oracle's RAC. This is a shared-disk, engine-failover kind of
multi-master. It provides a certain amount of scaling, but nothing
I've seen or heard suggests that the license cost couldn't just as
easily and effectively be thrown at larger hardware for better
scaling. The really big reason to use RAC is five-nines situations:
you're trying to make sure that even unlikely failures of your
machines never cause the database to stop working (for suitably
lawyer-understood values of "stop". RAC remastering is not a
zero-cost, nor even invisible, operation. But from an application
perspective, it can be made to look like "database is slow" as
opposed to "database crashed").

2. Disconnected sales forces with local copies of some portion
of the sales database. This is completely distributed database use,
with potential for conflicts and an associated need for conflict
resolution strategies.

These are different sorts of systems addressing completely different
use cases. But they're both potentially marketed as "multi-master".
Often, a manager asking for multi-master thinks s/he is buying all of
this; which desire is probably impossible to satisfy with one piece
of software (as opposed to one thing all called by the same name by
the marketing department).

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell

What's the point of a binary search if the list is small enough to fit
on a line or two? And if a query can be substituted for N1-NN, you have
to read all the values anyway, and then the function is trivially
expressed as a normal query with no decrease in speed.

-- Dean

Scott Marlowe <smarlowe@g2switchworks.com> writes:

I could see how it might be possible to make a two argument user defined
function that took an argument like:

select intvl(10,'20 30 40 50 60');

so that the multiple arguments are really just a space or comma
separated list fed to the function. I wouldn't name it interval though.

Use an array:

select intvl(10, array[20,30,40,50,60]);

I think you could even code this as a generic polymorphic function:

create function intvl(anyelement, anyarray) returns anyelement ...

Anybody feel like filling it in with a standard binary search algorithm?

regards, tom lane

I have to admit, my thoughts on it were to build a query with case
statements in it and execute it. That sounds about like you're
proposing, right?

Andrew Sullivan wrote:

On Thu, Oct 13, 2005 at 10:46:29AM -0500, Scott Marlowe wrote:

choice in another. So, multi-master replication isn't likely to become
a plug in module for postgresql any time soon.

It's not even a thing, so it can't become a plug-in.

I was referring specifically to Multimaster Async Replication.

Consider just two kinds of multi-master:

1. Oracle's RAC. This is a shared-disk, engine-failover kind of
multi-master. It provides a certain amount of scaling, but nothing
I've seen or heard suggests that the license cost couldn't just as
easily and effectively be thrown at larger hardware for better
scaling. The really big reason to use RAC is five-nines situations:
you're trying to make sure that even unlikely failures of your
machines never cause the database to stop working (for suitably
lawyer-understood values of "stop". RAC remastering is not a
zero-cost, nor even invisible, operation. But from an application
perspective, it can be made to look like "database is slow" as
opposed to "database crashed").

So this is basically a multimaster synchronous replication solution
utilizing a shared disk architecture. I generally agree with your
assessment that the license costs could be better spent on redundant
hardware and more scalable hardware. Also if the shared disk fails, you
may lose everything after your last backup.

Now, what about PgPool as a multimaster sync replication solution? Sure
it is statement level.... But is there any reason why you cannot have
multiple PgPool instances running against a number of DB servers?

2. Disconnected sales forces with local copies of some portion
of the sales database. This is completely distributed database use,
with potential for conflicts and an associated need for conflict
resolution strategies.

This is multimaster async replication. But it can be further broken
down into four types:

1) Insert-only, small number of nodes (not too hard to do with Slony-I).
2) Insert-only, large number of nodes (a real pain to do with Slony-I,
could become unmanageable easily, but I suppose one could build
automated management tools)
3) Insert/Update, small number of nodes. Probably would require a
custom solution on top of Slony-I
4) Insert/update, large number of nodes. Not sure if this is possible
to manage effectively under any circumstances.

Best Wishes,
Chris Travers
Metatron Technoloy Consulting

On Thu, Oct 13, 2005 at 10:53:51AM -0700, Chris Travers wrote:

Now, what about PgPool as a multimaster sync replication solution? Sure
it is statement level.... But is there any reason why you cannot have
multiple PgPool instances running against a number of DB servers?

Well, to begin with, you have a serious race condition:

pgpool begins T1 on M1 and M2.
Someone logs into M2 and does some work in T2.
M1 completes the work of T1.
M2 completes the work of T2.
pgpool issues COMMIT.
M1 replies with the COMMIT.
M2 detects a deadlock when T2 tries to COMMIT.

Now what? There's nothing to prevent this in the system, as near as
I can see, so it's just not bullet proof enough for the cases where
people really, really need only five minutes of down time a year. If
you _really_ needed that, you'd be willing (and able) to pay the
costs. Of course, we can do what we can to make those costs go down.
:-> But they're not that low yet.

Also, there is still (or was last I checked) a limitation on the
number of machines pgpool could address, and there are some stability
and reliability issues we've seen. It's a great piece of code, don't
get me wrong; but it's not stable enough yet to bet millions of
dollars on.

ObNit: ORAC isn't really synchronous; it just looks that way.

This is multimaster async replication. But it can be further broken
down into four types:

Sure; I think you could break it even smaller sub-types, if you
worked at it, too. For example, an async system that tolerates
farily brief interruptions in two-way communications is very
different from the one where your sales force (or your Palm) shows up
after a week and dumps a whole bunch of new conflicts on your lap.
This second case is something Slony wouldn't tolerate; but I think a
relatively-high availability would probably work with some multi-way
conflict resolution, if someone were willing to build it. That
wasn't the itch Afilias needed scratching, because of the kinds of
problems we have to solve (to begin with, exactly one person may be
the registrant of record of a domain name at any one time, so
conflict resolution is just not allowed in our problem set: we have
to maintain global uniqueness). But we did have some discussions
about how one might file the corners of the hole to make it square
enough for the peg. I think it's possible, if someone volunteers to
do the work (maybe in a sub-project, maybe as a co-operative
project). I don't have the problem, so I can't justify the staff
time. So if someone _else_ has the problem, maybe s/he can.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin