Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
I'd like to add one item to Mike Mascari's excellent and helpful list: Watch out for queries involving many result rows which include functions or even aggregates in the select list: --select f(x) into resulttable from bighugetable; The way that postgres allocates/frees memory results in potentially very large memory use by such queries. (Per- row memory is not freed until the statement completes.) My reading of the todo list is that this is a known bug (or feature!). BTW: Does anyone know if there are plans to fix this one soon? Mike Beller 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
RE: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Ed Loehr
> Sent: Wednesday, December 29, 1999 11:23 AM
> To: Jochen Topf
> Cc: [EMAIL PROTECTED]
> 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: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
Kaare Rasmussen <[EMAIL PROTECTED]> wrote: :> 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. : I'm wondering if you ever reported these problems to this list or the : the hackers list? I've been reading both regularily and don't recall : seeing this descussed before, but maybe I'm wrong. : Generally I find the responsiveness from the development team way better : than any commercial products. _All_ problem reports are treated with : concern. So if you didn't report them before, please take the time to : document your experience and send the problem report to the correct : place. No I haven't reported them. I have reported a minor bug that I could reproduce to the bug tracking system. But all the other problems I had, were, as I said, not reproducable. I tried to come up with a small test case for some of the bugs and failed. Sure I can report them all, but the developers will tell me, and rightly so, that they can't do anything with it, because they can't reproduce it. I know that this is not very helpful, but I know no easy way out here. Jochen -- Jochen Topf - [EMAIL PROTECTED] - http://www.remote.org/jochen/
[GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
> 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. I'm wondering if you ever reported these problems to this list or the the hackers list? I've been reading both regularily and don't recall seeing this descussed before, but maybe I'm wrong. Generally I find the responsiveness from the development team way better than any commercial products. _All_ problem reports are treated with concern. So if you didn't report them before, please take the time to document your experience and send the problem report to the correct place.
[GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
Kane Tao <[EMAIL PROTECTED]> 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 - [EMAIL PROTECTED] - http://www.remote.org/jochen/
Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
On Mon, 22 Nov 1999, Kane Tao 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
Ack, you know the discussion is going downhill when someone mentioned
Linux *sigh* *big, friendly, Daemon grin*
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
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. 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. can I ask what type of application u aer going to use PostgreSQL for? - Original Message - From: The Hermit Hacker <[EMAIL PROTECTED]> To: Stephen Birch <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, November 22, 1999 9:32 PM Subject: Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications? > > Everyone has their own experiences, and difficulties...there are X > platforms out there that PostgreSQL supports, multiply that by however > many different hardware pieces that be thrown the standard box, and you'll > get that many different experiences...would i use it in a mission critical > box? yes, I do on several. have I ever had problems...to be honest, > yes...most of them at the application level. > > On Mon, 22 Nov 1999, Stephen Birch wrote: > > > I have been surprised by the response to this question. I was hoping that the > > responses would be more consistent, after all when software is unreliable it > > is generally known by all users. > > > > Although one would expect a subjective bias to the opinions, the answers > > provided in the thread are highly polarized. Jochen Topf gave a frightening > > description of an unreliable database which gave unpredictable results. For > > example: > > > > > The most frustrating thing is that most bugs are not repeatable or at least > > > not repeatable in a small test script that I could send in with a bug report. > > > Looking at the bug reports that come through the mailing list, there are a > > > lots of the type: X works here but not in this similar situation. This is > > > IMHO a symptom of a bad design. A recent upgrade (I think it was from 6.5 > > > to 6.5.1 or something like that) helped a little bit but on the other hand > > > some query optimizations that worked before didn't work anymore. > > > > > > > This is pretty scary. > > > > However, I then read another reply only to find that Brett McCoy is converting > > "hundreds of thousands of documents" with no PostgresSQL problems at all. > > Brett indicates that: > > > > > So I think PostgreSQL is quite solid and reliable. The only thing I think > > > that is sorely needed in PostgreSQL is referential integrity constraints > > > like foreign keys (although this can be emulated with triggers). > > > > > > > In fact, the lack of referential integrity constraints happens to be my > > biggest concern - assuming the database is reliable, something that is proving > > hard to determine. > > > > Reading on, I see that "The Hermit Hacker" (love the name) also finds the > > database to be reliable: > > > > > Odd, I've been using PostgreSQL since v1.x for exactly this same reason, > > > and we haven't had any problems with the database crashing since v6.x was > > > released. Then again, the radius server opens/closes its connections as > > > required, instead of relynig on one persistent connection, so maybe that > > > helps, but that's just "application programming" vs backend... > > > > > > > There is a subtle implication that perhaps Jochen's problems are self > > inflicted. In a later email, Jochen responds and asks if he is the only one > > using "advanced features" and suggests that they may be the cause of his > > problems. However, his list of "advanced features" is a little scary since > > that are the very features that makes PostgreSQL so attractive in the first > > place - and I fully intend to use them! > > > > So which is is guys, is this database dependable for commercial use - or is an > > academic oddity, worth watching but not using? > > > > Any other success or failure stories would be really helpful > > > > Is PostgresSQL ready for prime time, or is it limpware? > > > > Steve
Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
Everyone has their own experiences, and difficulties...there are X platforms out there that PostgreSQL supports, multiply that by however many different hardware pieces that be thrown the standard box, and you'll get that many different experiences...would i use it in a mission critical box? yes, I do on several. have I ever had problems...to be honest, yes...most of them at the application level. take a look at: http://www.pgsql.com/projects/projects.cgi?sort=name There are ppl there working on projects such as: Arctic and Antarctic Research Center - Scripps Institution of Oceanography / Frank Delahoyde CFAR Molecular Biology Core - University of California San Diego / David J. Looney Online Community Newspaper - Alex Wilson Coldstream Ltd / Anil Amarakoon Camping-USA! - Camping-USA! / Vince Vielhaber POS System for Retail Shop - PIPSE Information System Co. / Yewon Heo Postgres Mail Database - National Center for Supercomputing Applications / Duane Moore Software2Go Online Store - Software2Go, LLC / Eric Schnoebelen Univ Texas @ Arlington - Engineering Distance Learning Site - Univ Texas @ Arlington / Charlie Lindahl Utility Billing - City Of Lake Lotawana / A. Van Hook And that is just a select listing of all the projects currently listed, and doesn't include several hundred that I'm still enterign into the new system... Each one of those is mission critical to the person using it, and, in some cases, I'd say to the ppl that they affect (Utility Billing and POS System are the two that come to mind there)... Any bugs/limitation of the current system can be worked around, and will be improved in each release, as they have been to date. Each release is generally leaps ahead of previous releases...even the minor releases contain changes that improve things... Quite frankly, I think the fact that Jochen is still around *even though* he has problems says alot about the quality of both the software and the development processes that we've developed over the past year, and also gives a good indication of where we are going... If you are still hestitant, write your application in such a way that if things get to the point that you just can't stay with PostgreSQL, you can switch. Use perl/dbi, so that you can switch with a simple chagne to the connect string, its what I do...except, in my case, its to make sure that I can do all my development work in PostgreSQL, while keepign in mind that the end user might not feel comfortable with that, and/or to keep options open for them in the future...so far, I've been lucky, and all my clients have been quite happy with PostgreSQL as well... On Mon, 22 Nov 1999, Stephen Birch wrote: > I have been surprised by the response to this question. I was hoping that the > responses would be more consistent, after all when software is unreliable it > is generally known by all users. > > Although one would expect a subjective bias to the opinions, the answers > provided in the thread are highly polarized. Jochen Topf gave a frightening > description of an unreliable database which gave unpredictable results. For > example: > > > The most frustrating thing is that most bugs are not repeatable or at least > > not repeatable in a small test script that I could send in with a bug report. > > Looking at the bug reports that come through the mailing list, there are a > > lots of the type: X works here but not in this similar situation. This is > > IMHO a symptom of a bad design. A recent upgrade (I think it was from 6.5 > > to 6.5.1 or something like that) helped a little bit but on the other hand > > some query optimizations that worked before didn't work anymore. > > > > This is pretty scary. > > However, I then read another reply only to find that Brett McCoy is converting > "hundreds of thousands of documents" with no PostgresSQL problems at all. > Brett indicates that: > > > So I think PostgreSQL is quite solid and reliable. The only thing I think > > that is sorely needed in PostgreSQL is referential integrity constraints > > like foreign keys (although this can be emulated with triggers). > > > > In fact, the lack of referential integrity constraints happens to be my > biggest concern - assuming the database is reliable, something that is proving > hard to determine. > > Reading on, I see that "The Hermit Hacker" (love the name) also finds the > database to be reliable: > > > Odd, I've been using PostgreSQL since v1.x for exactly this same reason, > > and we haven't had any problems with the database crashing since v6.x was > > released. Then again, the radius server opens/closes its connections as > > required, instead of relynig on one persistent connection, so maybe that > > helps, but that's just "application programming" vs backend... > > > > There is a subtle implication that perhaps Jochen's problems are self > inflicted. In a later email, Jochen responds and
Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?
I have been surprised by the response to this question. I was hoping that the responses would be more consistent, after all when software is unreliable it is generally known by all users. Although one would expect a subjective bias to the opinions, the answers provided in the thread are highly polarized. Jochen Topf gave a frightening description of an unreliable database which gave unpredictable results. For example: The most frustrating thing is that most bugs are not repeatable or at least not repeatable in a small test script that I could send in with a bug report. Looking at the bug reports that come through the mailing list, there are a lots of the type: X works here but not in this similar situation. This is IMHO a symptom of a bad design. A recent upgrade (I think it was from 6.5 to 6.5.1 or something like that) helped a little bit but on the other hand some query optimizations that worked before didn't work anymore. This is pretty scary. However, I then read another reply only to find that Brett McCoy is converting "hundreds of thousands of documents" with no PostgresSQL problems at all. Brett indicates that: So I think PostgreSQL is quite solid and reliable. The only thing I think that is sorely needed in PostgreSQL is referential integrity constraints like foreign keys (although this can be emulated with triggers). In fact, the lack of referential integrity constraints happens to be my biggest concern - assuming the database is reliable, something that is proving hard to determine. Reading on, I see that "The Hermit Hacker" (love the name) also finds the database to be reliable: Odd, I've been using PostgreSQL since v1.x for exactly this same reason, and we haven't had any problems with the database crashing since v6.x was released. Then again, the radius server opens/closes its connections as required, instead of relynig on one persistent connection, so maybe that helps, but that's just "application programming" vs backend... There is a subtle implication that perhaps Jochen's problems are self inflicted. In a later email, Jochen responds and asks if he is the only one using "advanced features" and suggests that they may be the cause of his problems. However, his list of "advanced features" is a little scary since that are the very features that makes PostgreSQL so attractive in the first place - and I fully intend to use them! So which is is guys, is this database dependable for commercial use - or is an academic oddity, worth watching but not using? Any other success or failure stories would be really helpful Is PostgresSQL ready for prime time, or is it limpware? Steve - PS This thread was started in pgsql-general, I cross posted to pgsql-novice as I am sure that some readers of that group would be interested in this topic. If you want to comment, please reply to [EMAIL PROTECTED], I don't want to fork the thread!
