Thread: Re: Is PostgreSQL ready for mission criticalapplications?

Kane Tao <death@solaris1.mysolution.com> wrote:
: The reason why opinions are so varied has alot to do with the expertise of
: each person in relation to PostgreSQL and Linux.  Often problems that are
: considered simple to resolve by some are very difficult for others.  And
: sometimes problems are caused by actions that are done out of inexperince
: with the system like cancelling certain operations in progress etc...
: You probably would not be able to determine reliability from opinions.  The
: thing is PostgreSQL is extremely reliable if u know what you are doing and
: know how to handle/get around any bugs.

Sorry, this is simply not true. We are talking about reliability here and
not about some features that might be difficult to find for the inexperienced
user or something like that. For instance, I had to fight with PostgreSQL and
Perl to get Notify to work. It might be difficult to get this to work, because
the lack of documentation or bugs in the way it is implemented, but I got
it to work. This is the thing a beginner stumbles over, and if not persistent
enough will label as a bug, although it might be only the documentation that
is buggy, or his level of understanding of the workings of the database is
just not good enough.

But I am not imagining the random "I have rolled back the current transaction
and am going to terminate your database system connection and exit." messages.
If there is a way to kill a database as a normal user, it is not reliable.
Maybe, if I knew more about PostgreSQL, I would be able to not trigger the
bugs, but that is not the point. The bugs should not be there or there
should be at least a meaningful error message saying: "I am sorry Dave, I can't
let you do this, because it would trigger a bug." I have seen random chrashes
without any indication to the problem and I have seen strange messages
hinting at a problem deep down in a btree implementation or something like
that. And the worst thing is, that these bugs are not repeatable in a way
that someone could start debugging them or at least work around them.

To be fair, I have never lost any data (or had it corrupted) that was
already *in* the database, although there is one unresolved case, which might
have been a database corruption but was probabely an application error. But
I have lost data, because the application wasn't able to put it in the
database in the first place and the database was not accessible. But that is
probabely an application error too, because it only buffered data in memory
and not on disk, in case of a database failure. I thought that this is enough,
because databases are supposed to be more reliable then simple filesystems...

: Lookig at some of the other posts about reliability...the number of records
: in a database will mainly determine the ability of a database to maintain
: performance at larger file/index sizes.  It does not really impact
: stability.  Stability is mainly affected by the number of
: reads/updates/inserts that are performed.  Usually u want to look at large
: user loads, large transaction loads and large number of
: updates/inserts/deletes to gauge reliability.   I havent seen anyone post
: saying that they are running a system that does this...perhaps I just missed
: the post.

While this is generally true, a huge database can have an impact on
stability. For instance, if you have a very small memory leak, it will not
show in small databases but might show in big ones, triggering a bug. Or
an index grows over some bound and a hash file has to be increased or whatever.
And there are some problems of this kind in PostgreSQL. I am logging all
logins and logouts from a radius server into PostgreSQL and after it ran
well for several months, it slowed to a crawl and vacuum wouldn't work
anymore. So, yes, I do have a lot of inserts, although about 6000 inserts a
day and a total of a few hundert thausend records is not really much.

My question of an earlier posting is still not answered. Does anybody here,
who reported PostgreSQL to be very stable, use advanced features like pl/pgsql
procedures, triggers, rules and notifies? Lets have a show of hands. I would
really like to know, why I am the only one having problems. :-) Although
it might be, because, as this is a PostgreSQL mailing list, most of the
readers are people who are happy with PostgreSQL, because all the others
have left and are on an Oracle list now. :-)

I would really, really like PostgreSQL to be stable and useful for mission
critical things, because it has some very nice features, is easy to setup,
and easy to maintain and generally a lot better then all the other databases
I know, weren't it for the problems described above. I hope that my criticism
here is not perceived as PostgreSQL bashing but as an attempt to understand
why so many people are happy with PostgreSQL and I am not.

Jochen
--
Jochen Topf - jochen@remote.org - http://www.remote.org/jochen/

>  The
> : thing is PostgreSQL is extremely reliable if u know what you are doing
and
> : know how to handle/get around any bugs.
>
> Sorry, this is simply not true. We are talking about reliability here and
> not about some features that might be difficult to find for the
inexperienced
> user or something like that. For instance, I had to fight with PostgreSQL
and
> Perl to get Notify to work. It might be difficult to get this to work,
because
> the lack of documentation or bugs in the way it is implemented, but I got
> it to work. This is the thing a beginner stumbles over, and if not
persistent
> enough will label as a bug, although it might be only the documentation
that
> is buggy, or his level of understanding of the workings of the database is
> just not good enough.

> But I am not imagining the random "I have rolled back the current
transaction
> and am going to terminate your database system connection and exit."
messages.
> If there is a way to kill a database as a normal user, it is not reliable.
> Maybe, if I knew more about PostgreSQL, I would be able to not trigger the
> bugs, but that is not the point. The bugs should not be there or there
> should be at least a meaningful error message saying: "I am sorry Dave, I
can't
> let you do this, because it would trigger a bug." I have seen random
chrashes
> without any indication to the problem and I have seen strange messages
> hinting at a problem deep down in a btree implementation or something like
> that. And the worst thing is, that these bugs are not repeatable in a way
> that someone could start debugging them or at least work around them.

I guess I can see that point :)  The ability for a less experienced user or
admin to reasonably do a task in a short amount of time without srewing
things up is more a factor of ease of use than reliability...The ease of
accidentally causing serious harm to the integrity of a database that
requires major repair (foolproofing) is a factor of reliability ;)


> and not on disk, in case of a database failure. I thought that this is
enough,
> because databases are supposed to be more reliable then simple
filesystems...

No only more flexible ;)   Not much is more reliable than a flat file...just
you have to write all the routines to handle multiple users accessing the
file and routines to indeex and find what you are looking for :)

> While this is generally true, a huge database can have an impact on
> stability. For instance, if you have a very small memory leak, it will not
> show in small databases but might show in big ones, triggering a bug. Or
> an index grows over some bound and a hash file has to be increased or
whatever.
> And there are some problems of this kind in PostgreSQL. I am logging all
> logins and logouts from a radius server into PostgreSQL and after it ran
> well for several months, it slowed to a crawl and vacuum wouldn't work
> anymore. So, yes, I do have a lot of inserts, although about 6000 inserts
a
> day and a total of a few hundert thausend records is not really much.

What version of PostgreSQL did this occur on?  And how often were you
running vacuums?


> My question of an earlier posting is still not answered. Does anybody
here,
> who reported PostgreSQL to be very stable, use advanced features like
pl/pgsql
> procedures, triggers, rules and notifies? Lets have a show of hands. I
would
> really like to know, why I am the only one having problems. :-) Although
> it might be, because, as this is a PostgreSQL mailing list, most of the
> readers are people who are happy with PostgreSQL, because all the others
> have left and are on an Oracle list now. :-)

:)
 In reference to your other posting...if you are experienced enough to
understand the inner workings of PostgreSQL.  You are experienced enough to
DBA Oracle yourself ;)  Dont waste your money hiring a $100,000 certified
DBA (unless u need the extra help)  ;)




************

>  The
> : thing is PostgreSQL is extremely reliable if u know what you are doing
and
> : know how to handle/get around any bugs.
>
> Sorry, this is simply not true. We are talking about reliability here and
> not about some features that might be difficult to find for the
inexperienced
> user or something like that. For instance, I had to fight with PostgreSQL
and
> Perl to get Notify to work. It might be difficult to get this to work,
because
> the lack of documentation or bugs in the way it is implemented, but I got
> it to work. This is the thing a beginner stumbles over, and if not
persistent
> enough will label as a bug, although it might be only the documentation
that
> is buggy, or his level of understanding of the workings of the database is
> just not good enough.

> But I am not imagining the random "I have rolled back the current
transaction
> and am going to terminate your database system connection and exit."
messages.
> If there is a way to kill a database as a normal user, it is not reliable.
> Maybe, if I knew more about PostgreSQL, I would be able to not trigger the
> bugs, but that is not the point. The bugs should not be there or there
> should be at least a meaningful error message saying: "I am sorry Dave, I
can't
> let you do this, because it would trigger a bug." I have seen random
chrashes
> without any indication to the problem and I have seen strange messages
> hinting at a problem deep down in a btree implementation or something like
> that. And the worst thing is, that these bugs are not repeatable in a way
> that someone could start debugging them or at least work around them.

I guess I can see that point :)  The ability for a less experienced user or
admin to reasonably do a task in a short amount of time without srewing
things up is more a factor of ease of use than reliability...The ease of
accidentally causing serious harm to the integrity of a database that
requires major repair (foolproofing) is a factor of reliability ;)


> and not on disk, in case of a database failure. I thought that this is
enough,
> because databases are supposed to be more reliable then simple
filesystems...

No only more flexible ;)   Not much is more reliable than a flat file...just
you have to write all the routines to handle multiple users accessing the
file and routines to indeex and find what you are looking for :)

> While this is generally true, a huge database can have an impact on
> stability. For instance, if you have a very small memory leak, it will not
> show in small databases but might show in big ones, triggering a bug. Or
> an index grows over some bound and a hash file has to be increased or
whatever.
> And there are some problems of this kind in PostgreSQL. I am logging all
> logins and logouts from a radius server into PostgreSQL and after it ran
> well for several months, it slowed to a crawl and vacuum wouldn't work
> anymore. So, yes, I do have a lot of inserts, although about 6000 inserts
a
> day and a total of a few hundert thausend records is not really much.

What version of PostgreSQL did this occur on?  And how often were you
running vacuums?


> My question of an earlier posting is still not answered. Does anybody
here,
> who reported PostgreSQL to be very stable, use advanced features like
pl/pgsql
> procedures, triggers, rules and notifies? Lets have a show of hands. I
would
> really like to know, why I am the only one having problems. :-) Although
> it might be, because, as this is a PostgreSQL mailing list, most of the
> readers are people who are happy with PostgreSQL, because all the others
> have left and are on an Oracle list now. :-)

:)
 In reference to your other posting...if you are experienced enough to
understand the inner workings of PostgreSQL.  You are experienced enough to
DBA Oracle yourself ;)  Dont waste your money hiring a $100,000 certified
DBA (unless u need the extra help)  ;)




************

> > My question of an earlier posting is still not answered. Does anybody
> here,
> > who reported PostgreSQL to be very stable, use advanced features like
> pl/pgsql
> > procedures, triggers, rules and notifies? Lets have a show of hands. I
> would
> > really like to know, why I am the only one having problems. :-) Although
> > it might be, because, as this is a PostgreSQL mailing list, most of the
> > readers are people who are happy with PostgreSQL, because all the others
> > have left and are on an Oracle list now. :-)

I use triggers, PL/pgSQL procedures/functions, and rules on 6.5.2, and I have
experienced a number of what might be called instability problems for whatever
reason.  A review of the posts to the pgsql mailing lists will confirm that you
are not alone in finding some points of instability.  But the extent of any
instability is not clear.  Watch for a web poll announcement in January to get
a better handle on that data...

Cheers,
Ed Loehr

It would be helpful to me to hear about successful and stable
implementations as well.  If some of you who are using PostgreSQL
successfully could comment on your experiences, I think it would shed some
worthwhile light on it's capabilities.  I'm considering using it for a
mission critical project, and I would like to know what I am getting into.
Thank you.

David Barnes

-----Original Message-----
From: owner-pgsql-general@postgreSQL.org
[mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of Ed Loehr
Sent: Wednesday, December 29, 1999 11:23 AM
To: Jochen Topf
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Re: Is PostgreSQL ready for mission
criticalapplications?


> > My question of an earlier posting is still not answered. Does anybody
> here,
> > who reported PostgreSQL to be very stable, use advanced features like
> pl/pgsql
> > procedures, triggers, rules and notifies? Lets have a show of hands. I
> would
> > really like to know, why I am the only one having problems. :-) Although
> > it might be, because, as this is a PostgreSQL mailing list, most of the
> > readers are people who are happy with PostgreSQL, because all the others
> > have left and are on an Oracle list now. :-)

I use triggers, PL/pgSQL procedures/functions, and rules on 6.5.2, and I
have
experienced a number of what might be called instability problems for
whatever
reason.  A review of the posts to the pgsql mailing lists will confirm that
you
are not alone in finding some points of instability.  But the extent of any
instability is not clear.  Watch for a web poll announcement in January to
get
a better handle on that data...

Cheers,
Ed Loehr


************

Barnes wrote:

> It would be helpful to me to hear about successful and stable
> implementations as well.  If some of you who are using PostgreSQL
> successfully could comment on your experiences, I think it would shed some
> worthwhile light on it's capabilities.  I'm considering using it for a
> mission critical project, and I would like to know what I am getting into.

BTW, I also still think pgsql the best thing going for open source RDBMS if you
need transactions and can't spend $20K-$25K for Oracle.  And I have found the
folks on the mailing lists to be critically helpful to working through some
non-trivial issues (as well as a bonehead mistake or two).  I also think the
system is rapidly becoming more stable/mature, though I don't think you can
count on problem-free operation (as of 6.5.2/3) just yet.  But I'm still
betting on pgsql...

Cheers,
Ed Loehr

hi, there, I have an "ugly" question:

I need to design a table, seems good
for using array (ok, I know I'd better think it twice for using it ;-)
the application should be as portable as possible for Oracle and Sql
server7.  I know the difference between oracle's and pg's array, but
I do not know sql server7's. any ideas?

thanks

Barnes wrote:
>
> It would be helpful to me to hear about successful and stable
> implementations as well.  If some of you who are using PostgreSQL
> successfully could comment on your experiences, I think it would shed some
> worthwhile light on it's capabilities.  I'm considering using it for a
> mission critical project, and I would like to know what I am getting into.
> Thank you.
>
> David Barnes
>

We've used it successfully in a production environment (24 x
7) for over a year now. Simply reading the mailing list will
greatly improve your chances of success. The problems with
PostgreSQL can be avoided if you know, in advance, what to
avoid. But must people don't. Here's my list of things which
can get you into trouble:

0. Running with fsync on - There is the probability that
modified records written into kernel buffers but not written
to disk could exist at the moment of an operating system
crash. Therefore, PostgreSQL's default mode is to run with
fsync() on. This slows down the database by (quite
literally) several orders of magnitude. We've run with fsync
off (-o -F) without a problem. Dump/Reload of large
databases with fsync() on really tests one's pain threshold.
If you trust your OS, run with it off.

1. Making use of oids - Problems with dumping/restoring oids
make this development path dangerous. Most people use the
SERIAL data type to generate primary keys. However, SERIAL
itself has some peculiarities, since it just auto-creates a
sequence. Dropping the associated table doesn't drop the
sequence (IIRC), so scripted or automated schema creation
may not be obvious. I prefer to manually use a sequence and
an int4 type for primary keys. In fact, you could use the
same sequence for all of your primary keys, if you aren't
exposing the value to the user in any meaningful way, and
don't plan to hit the 4.2 billion limit of int4 soon, and
don't care about gaps...(although a purist would argue, and
I agree, that IF you are going to use generated keys THEN
the key should have no more meaning then  that it refers to
a record).

2. Using views created with large queries - Views use the
rewrite system and rules to rewrite a query against it to
properly fetch data from the underlying tables. Because
there is currently a limit on the size of a single database
record (8192 bytes), the queries associated with views can
only be so big. In addition, you can get into trouble if
views are built on top of user-defined functions, which is a
common thing to do. If you drop/recreate the underlying
function, then the view needs to be dropped and recreated as
well. In addition, I've had trouble with dump/reload of
views in the past, and have always kept my schema in
separate views.sql script, just in case...

3. Using non-standard types - Because of problems with data
comparisons, type coercion and spaces, we avoided types such
as bpchar, char, and even text. We avoided text since ODBC
clients could not determine maximum field width. We also
avoided all of the non-4 byte integer types, such as int2.
This is because the default type coercion (sp?) code in the
past has had trouble being smart enough to use indexes when
given a SELECT such as:

CREATE TABLE y (x text, z int2);

SELECT x FROM y WHERE z = 3;

because the 3 would be coerced to an int4 and, if z was an
int2, would result in a sequential scan, whereas:

SELECT x FROM y WHERE z = '3';

would use the index since it is initially parsed as a string
and coerced properly at a later point. I think much of this
has been fixed, but nevertheless... In addition, our
varchar() types are pretty much under the 255 limit since
some ODBC clients have problems with varchar() types greater
than 255. We only use: int4, varchar, datetime, and float8.
On rare occasion, we'll use text for free-form information,
but we NEVER index it. Although its VERY tempting, (and
PostgreSQL itself uses them), we avoid arrays.

4. Be careful about user-defined functions/triggers -
PostgreSQL keeps track of everything by its oid, not by name
(which would obviously be too slow). But, unfortunately, it
does not yet support the modification of functions, allowing
the function to retain its original oid (or perform a
cascading update - it will be nice when RI is integrated
into the system catalogue!). As a result, odd things can
happen if you drop and recreate a function. For example, you
could have a trigger call a procedural language which, in
turn, could select from a view, from which one of the
attributes is the result of a function. If you
dropped/recreated that function, things go a bit weird and
usually result in an error such as "function not in cache".

5. Using DDL statements in transactions - PostgreSQL has
trouble rolling back transactions which have aborted which
contain DDL statements. As a result, you might find yourself
having to delete a filesystem file, because, even though a
TABLE create might have been rolled back as far as the
system catalogue is concerned, the underlying file might
still manage to exist. Or worse, rollback of index
DROP/CREATE in a transaction yields erroneous results.

6. Using indexes on large fields - Apparently the code
requires 3 tuples per page (or something like that) for the
index to function properly. This can include plpgsql source,
so be careful. We never index on anything larger than 255,
but I believe around 2.8K is the limit before tickling
bugs...

7. Using INSERTS instead of COPY - Even when you have
fsync() off and are running INSERT statements in a
transaction, the processing of individual INSERT statements
by the thousands is also several orders of magnitude slower
than COPY. We have large mainframe datasets which we import
nightly - we first covert them to data appropriate for COPY
and then COPY them in, instead INSERT's record by record.
The problem with COPY is it runs as user postgres, so you
need to have the data files readable by user postgres.

8. Not running VACUUM - PostgreSQL won't use indexes, or
won't optimize correctly unless the record count and
dispersion estimates are up-to-date. People have reported
problems with running vacuum while under heavy load. We
haven't seen it, but we run vacuum each night at 4:05 a.m.
However, if you perform a LARGE number of INSERTS/UPDATES,
it is better for you to do the following:

DROP INDEX index_on_heavilty_used_table;
VACUUM ANALYZE;
CREATE INDEX index_on_heavily_used_table;

Because VACUUM will sit there, and, row by row, essentially
"defragment" your indexes, which can take damn near forever
for any number of updates or deletes greater than, say,
30,000 rows.

9. ALTER TABLE ADD COLUMN - Its better to rebuild the table
by hand then to use this DDL statement. First off, any
column constraints (such as NOT NULL), will silently
ignored, and secondly, inherited relations have problems
with dump/restore.

10. IN, INTERSECT, EXCEPT - When writing your application,
these SQL functions seem nice, particularly since the data
in your design database may be small, initially. But all
three of these SQL expressions (whatever) force a nested
sequential scan on the relation. For example:

emptoris=> explain SELECT employee FROM employees WHERE
employee NOT IN (SELECT webuser FROM webusers);
NOTICE:  QUERY PLAN:

Seq Scan on employees  (cost=3.95 rows=59 width=12)
  SubPlan
    ->  Seq Scan on webusers  (cost=7.78 rows=145 width=12)

EXPLAIN

Since INTERSECT/EXCEPT rewrite the query to use IN, the
problem exists with them as well. And since PostgreSQL does
not yet have outer joins, you should instead write the query
using a correlated sub query (EXISTS):

emptoris=> explain SELECT employee FROM employees WHERE NOT
EXISTS (SELECT webuser FROM webusers WHERE webusers.webuser
= employees.employee);
NOTICE:  QUERY PLAN:

Seq Scan on employees  (cost=3.95 rows=59 width=12)
  SubPlan
    ->  Index Scan using k_webusers1 on webusers  (cost=2.05
rows=1 width=12)

EXPLAIN

There are many more such things which, if avoided, allow
PostgreSQL to work great. But with each release, a lot of
these things become obsolete.

Mike Mascari

This is a great list.  I have addressed the oid/sequence issue in my
book, chapter 7.

> Barnes wrote:
> >
> > It would be helpful to me to hear about successful and stable
> > implementations as well.  If some of you who are using PostgreSQL
> > successfully could comment on your experiences, I think it would shed some
> > worthwhile light on it's capabilities.  I'm considering using it for a
> > mission critical project, and I would like to know what I am getting into.
> > Thank you.
> >
> > David Barnes
> >
>
> We've used it successfully in a production environment (24 x
> 7) for over a year now. Simply reading the mailing list will
> greatly improve your chances of success. The problems with
> PostgreSQL can be avoided if you know, in advance, what to
> avoid. But must people don't. Here's my list of things which
> can get you into trouble:
>
> 0. Running with fsync on - There is the probability that
> modified records written into kernel buffers but not written
> to disk could exist at the moment of an operating system
> crash. Therefore, PostgreSQL's default mode is to run with
> fsync() on. This slows down the database by (quite
> literally) several orders of magnitude. We've run with fsync
> off (-o -F) without a problem. Dump/Reload of large
> databases with fsync() on really tests one's pain threshold.
> If you trust your OS, run with it off.
>
> 1. Making use of oids - Problems with dumping/restoring oids
> make this development path dangerous. Most people use the
> SERIAL data type to generate primary keys. However, SERIAL
> itself has some peculiarities, since it just auto-creates a
> sequence. Dropping the associated table doesn't drop the
> sequence (IIRC), so scripted or automated schema creation
> may not be obvious. I prefer to manually use a sequence and
> an int4 type for primary keys. In fact, you could use the
> same sequence for all of your primary keys, if you aren't
> exposing the value to the user in any meaningful way, and
> don't plan to hit the 4.2 billion limit of int4 soon, and
> don't care about gaps...(although a purist would argue, and
> I agree, that IF you are going to use generated keys THEN
> the key should have no more meaning then  that it refers to
> a record).
>
> 2. Using views created with large queries - Views use the
> rewrite system and rules to rewrite a query against it to
> properly fetch data from the underlying tables. Because
> there is currently a limit on the size of a single database
> record (8192 bytes), the queries associated with views can
> only be so big. In addition, you can get into trouble if
> views are built on top of user-defined functions, which is a
> common thing to do. If you drop/recreate the underlying
> function, then the view needs to be dropped and recreated as
> well. In addition, I've had trouble with dump/reload of
> views in the past, and have always kept my schema in
> separate views.sql script, just in case...
>
> 3. Using non-standard types - Because of problems with data
> comparisons, type coercion and spaces, we avoided types such
> as bpchar, char, and even text. We avoided text since ODBC
> clients could not determine maximum field width. We also
> avoided all of the non-4 byte integer types, such as int2.
> This is because the default type coercion (sp?) code in the
> past has had trouble being smart enough to use indexes when
> given a SELECT such as:
>
> CREATE TABLE y (x text, z int2);
>
> SELECT x FROM y WHERE z = 3;
>
> because the 3 would be coerced to an int4 and, if z was an
> int2, would result in a sequential scan, whereas:
>
> SELECT x FROM y WHERE z = '3';
>
> would use the index since it is initially parsed as a string
> and coerced properly at a later point. I think much of this
> has been fixed, but nevertheless... In addition, our
> varchar() types are pretty much under the 255 limit since
> some ODBC clients have problems with varchar() types greater
> than 255. We only use: int4, varchar, datetime, and float8.
> On rare occasion, we'll use text for free-form information,
> but we NEVER index it. Although its VERY tempting, (and
> PostgreSQL itself uses them), we avoid arrays.
>
> 4. Be careful about user-defined functions/triggers -
> PostgreSQL keeps track of everything by its oid, not by name
> (which would obviously be too slow). But, unfortunately, it
> does not yet support the modification of functions, allowing
> the function to retain its original oid (or perform a
> cascading update - it will be nice when RI is integrated
> into the system catalogue!). As a result, odd things can
> happen if you drop and recreate a function. For example, you
> could have a trigger call a procedural language which, in
> turn, could select from a view, from which one of the
> attributes is the result of a function. If you
> dropped/recreated that function, things go a bit weird and
> usually result in an error such as "function not in cache".
>
> 5. Using DDL statements in transactions - PostgreSQL has
> trouble rolling back transactions which have aborted which
> contain DDL statements. As a result, you might find yourself
> having to delete a filesystem file, because, even though a
> TABLE create might have been rolled back as far as the
> system catalogue is concerned, the underlying file might
> still manage to exist. Or worse, rollback of index
> DROP/CREATE in a transaction yields erroneous results.
>
> 6. Using indexes on large fields - Apparently the code
> requires 3 tuples per page (or something like that) for the
> index to function properly. This can include plpgsql source,
> so be careful. We never index on anything larger than 255,
> but I believe around 2.8K is the limit before tickling
> bugs...
>
> 7. Using INSERTS instead of COPY - Even when you have
> fsync() off and are running INSERT statements in a
> transaction, the processing of individual INSERT statements
> by the thousands is also several orders of magnitude slower
> than COPY. We have large mainframe datasets which we import
> nightly - we first covert them to data appropriate for COPY
> and then COPY them in, instead INSERT's record by record.
> The problem with COPY is it runs as user postgres, so you
> need to have the data files readable by user postgres.
>
> 8. Not running VACUUM - PostgreSQL won't use indexes, or
> won't optimize correctly unless the record count and
> dispersion estimates are up-to-date. People have reported
> problems with running vacuum while under heavy load. We
> haven't seen it, but we run vacuum each night at 4:05 a.m.
> However, if you perform a LARGE number of INSERTS/UPDATES,
> it is better for you to do the following:
>
> DROP INDEX index_on_heavilty_used_table;
> VACUUM ANALYZE;
> CREATE INDEX index_on_heavily_used_table;
>
> Because VACUUM will sit there, and, row by row, essentially
> "defragment" your indexes, which can take damn near forever
> for any number of updates or deletes greater than, say,
> 30,000 rows.
>
> 9. ALTER TABLE ADD COLUMN - Its better to rebuild the table
> by hand then to use this DDL statement. First off, any
> column constraints (such as NOT NULL), will silently
> ignored, and secondly, inherited relations have problems
> with dump/restore.
>
> 10. IN, INTERSECT, EXCEPT - When writing your application,
> these SQL functions seem nice, particularly since the data
> in your design database may be small, initially. But all
> three of these SQL expressions (whatever) force a nested
> sequential scan on the relation. For example:
>
> emptoris=> explain SELECT employee FROM employees WHERE
> employee NOT IN (SELECT webuser FROM webusers);
> NOTICE:  QUERY PLAN:
>
> Seq Scan on employees  (cost=3.95 rows=59 width=12)
>   SubPlan
>     ->  Seq Scan on webusers  (cost=7.78 rows=145 width=12)
>
> EXPLAIN
>
> Since INTERSECT/EXCEPT rewrite the query to use IN, the
> problem exists with them as well. And since PostgreSQL does
> not yet have outer joins, you should instead write the query
> using a correlated sub query (EXISTS):
>
> emptoris=> explain SELECT employee FROM employees WHERE NOT
> EXISTS (SELECT webuser FROM webusers WHERE webusers.webuser
> = employees.employee);
> NOTICE:  QUERY PLAN:
>
> Seq Scan on employees  (cost=3.95 rows=59 width=12)
>   SubPlan
>     ->  Index Scan using k_webusers1 on webusers  (cost=2.05
> rows=1 width=12)
>
> EXPLAIN
>
> There are many more such things which, if avoided, allow
> PostgreSQL to work great. But with each release, a lot of
> these things become obsolete.
>
> Mike Mascari
>
> ************
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thanks, Mike!  This is the most lucid, concise explanation of so many
postgresql "gotchas" I've seen yet.

Mike Mascari wrote:

> 2. Using views created with large queries - Views use the
> rewrite system and rules to rewrite a query against it to
> properly fetch data from the underlying tables. Because
> there is currently a limit on the size of a single database
> record (8192 bytes), the queries associated with views can
> only be so big. ...

One additional anomaly as of 6.5.2 regarding backup and recovery...

If one simply compares the before/after output of load/dump scripts, it can at
first appear that pg_dump will occasionally convert a view built on non-empty
tables into a table itself with zero records.  This happens during the
following backup test sequence for me:

% pg_dump -d mydb > db.out
% destroydb mydb
% createdb mydb
% psql -d mydb < db.out
% pg_dump -d mydb > db2.out
% diff db.out db2.out

This is because a view _is_ actually implemented as a table combined with a
redirecting rule, and thus not a problem.  See the following for details.

    http://www.deja.com/getdoc.xp?AN=559228857

Cheers,
Ed Loehr

On Wed, 29 Dec 1999, Barnes wrote:

> It would be helpful to me to hear about successful and stable
> implementations as well.  If some of you who are using PostgreSQL
> successfully could comment on your experiences, I think it would shed some
> worthwhile light on it's capabilities.  I'm considering using it for a
> mission critical project, and I would like to know what I am getting into.
> Thank you.

At work, its the backend for our DNS/DHCP tables, servicing over 4000
lap/desktops ...

For business, its the accounting backend for two ISPs that I work with for
their dialup lines, is the backend for the search engine that Vince and I
are currently working on getting online for PostgreSQL...is the backend
for a project I'm working with that deals with, esssentially, resource
management for banks...

The only one above that I don't consider "mission critical" is the
search...

 >
> David Barnes
>
> -----Original Message-----
> From: owner-pgsql-general@postgreSQL.org
> [mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of Ed Loehr
> Sent: Wednesday, December 29, 1999 11:23 AM
> To: Jochen Topf
> Cc: pgsql-general@postgreSQL.org
> Subject: Re: [GENERAL] Re: Is PostgreSQL ready for mission
> criticalapplications?
>
>
> > > My question of an earlier posting is still not answered. Does anybody
> > here,
> > > who reported PostgreSQL to be very stable, use advanced features like
> > pl/pgsql
> > > procedures, triggers, rules and notifies? Lets have a show of hands. I
> > would
> > > really like to know, why I am the only one having problems. :-) Although
> > > it might be, because, as this is a PostgreSQL mailing list, most of the
> > > readers are people who are happy with PostgreSQL, because all the others
> > > have left and are on an Oracle list now. :-)
>
> I use triggers, PL/pgSQL procedures/functions, and rules on 6.5.2, and I
> have
> experienced a number of what might be called instability problems for
> whatever
> reason.  A review of the posts to the pgsql mailing lists will confirm that
> you
> are not alone in finding some points of instability.  But the extent of any
> instability is not clear.  Watch for a web poll announcement in January to
> get
> a better handle on that data...
>
> Cheers,
> Ed Loehr
>
>
> ************
>
>
>
> ************
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org