Thread: Commercial postgresql

Commercial postgresql

From
Bo Lorentsen
Date:
Hi ..

Well after discussing how bugs are handled in PG, and the development
culture in both MySQL and Postgresql, my boss agress that it may be a
good aletenertive til MySQL 4.x (InnoDB), but ...

He likes to know about others using Postgres and simple replication (we
need it in at 24/7 system), to know how postgres performs, and how
stable it is regarding its data and the backup feature etc.

Are there some detailed busisness cases, that he/we can read to feel
more assured that PG is a good choice ?

/BL


Re: Commercial postgresql

From
Peter Eisentraut
Date:
Bo Lorentsen writes:

> Are there some detailed busisness cases, that he/we can read to feel
> more assured that PG is a good choice ?

Start at <http://advocacy.postgresql.org/casestudies/>.  There aren't
many, but it's usually difficult to get customers to agree to this sort of
thing.

--
Peter Eisentraut   peter_e@gmx.net


Re: Commercial postgresql

From
Jacob Hanson
Date:
On Sat, 30 Aug 2003 16:18:14 +0200 (CEST), peter_e@gmx.net (Peter
Eisentraut) wrote:
>many, but it's usually difficult to get customers to agree to this sort of
>thing.

Why is that?

Re: Commercial postgresql

From
Christopher Browne
Date:
Oops! Jacob Hanson <jacobhanson@firsthealth.com> was seen spray-painting on a wall:
> On Sat, 30 Aug 2003 16:18:14 +0200 (CEST), peter_e@gmx.net (Peter
> Eisentraut) wrote:
>>many, but it's usually difficult to get customers to agree to this sort of
>>thing.
>
> Why is that?

Because:

1.  What tools they use may be something they consider a "competitive
    advantage."

2.  If they go on a list like this, they become _obvious_ targets for
    Oracle sales people and such.

Those reasons may occasionally be overcome, but that doesn't make them
irrelevant.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/linux.html
"Popularity is the hallmark of mediocrity."  --Niles Crane, "Frasier"

Re: Commercial postgresql

From
Vivek Khera
Date:
>>>>> "BL" == Bo Lorentsen <bl@netgroup.dk> writes:

BL> Hi ..
BL> He likes to know about others using Postgres and simple replication (we
BL> need it in at 24/7 system), to know how postgres performs, and how
BL> stable it is regarding its data and the backup feature etc.

I use it in 24/7/365 system which is heavily written to and read
from.  The drawbacks I have are:

1) upgrade to major versions require dump/restore which is a
   significant amount of downtime for a large DB.

2) the need to run vacuum on tables to keep them from bloating too
   much.  on my system which is very busy, sometimes running vacuum
   pushes the disk beyond its limits and slows the whole system to a
   crawl.

3) Index bloat is apparently a bigger problem than I thought.
   Yesterday I took the hit of reindexing all my tables.  On a 2.7Gb
   database (those of you who notice my numbers keep changing... this
   is the correct value -- I read wrongly before) I just shaved 900Mb
   of 'dead' index pages.  Unfortunately, my largest table of 92M rows
   takes about 43 minutes *per index* to reindex.

I think 2 is probably amplified by 3.  We'll see how the system holds
up this week after the indexes have been optimized.

BL> Are there some detailed busisness cases, that he/we can read to feel
BL> more assured that PG is a good choice ?

If you want commercial support, it is out there.  There are at least
two companies offering it.


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: Commercial postgresql

From
Lamar Owen
Date:
On Monday 01 September 2003 22:08, Vivek Khera wrote:
> I use it in 24/7/365 system which is heavily written to and read
> from.  The drawbacks I have are:

Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year.

> 1) upgrade to major versions require dump/restore which is a
>    significant amount of downtime for a large DB.

I have harped on this at length.  Maybe one day we'll get real upgrading.
Search the archives for the discussions; there are many, and they are long
threads.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute

Re: Commercial postgresql

From
"Claudio Lapidus"
Date:
Lamar Owen wrote:
> Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year.
Oh, great. It's just that 7*52 = 364. That leaves us with a full day to idle
and still honor the SLA, right?

Sorry, couldn't resist :)
cl.

Re: Commercial postgresql

From
Bo Lorentsen
Date:
On Tue, 2003-09-02 at 04:08, Vivek Khera wrote:

> I use it in 24/7/365 system which is heavily written to and read
> from.  The drawbacks I have are:
How depressing, may I ask that PG version you are using ?

> 1) upgrade to major versions require dump/restore which is a
>    significant amount of downtime for a large DB.
Ok, this is not a thing you do very often, and it would help is we got a
"diff" (since last backup) pg_dump. As one could install the new DB in
parallel with produktion, and then just apply the diff dump on the db
swap.

> 2) the need to run vacuum on tables to keep them from bloating too
>    much.  on my system which is very busy, sometimes running vacuum
>    pushes the disk beyond its limits and slows the whole system to a
>    crawl.
How often does this vacuum run, and how many delete/updates are there in
between ?

> 3) Index bloat is apparently a bigger problem than I thought.
This does not sound too nice !

> If you want commercial support, it is out there.  There are at least
> two companies offering it.
But you have not been unsing any of there services ?

/BL


Re: Commercial postgresql

From
"Shridhar Daithankar"
Date:
On 2 Sep 2003 at 11:49, Bo Lorentsen wrote:
> > 3) Index bloat is apparently a bigger problem than I thought.
> This does not sound too nice !

That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with
autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better
than earlier days..

Bye
 Shridhar

--
QOTD:    If you're looking for trouble, I can offer you a wide selection.


Re: Commercial postgresql

From
Bo Lorentsen
Date:
On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote:

> That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with
> autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better
> than earlier days..
Are there a list of things that will be done in 7.4, or better a
development roadmap like the one the Mozilla folks are using ?

These things are quite important when you have to convince your boss :-)

/BL


Re: Commercial postgresql

From
Andrew Sullivan
Date:
On Sat, Aug 30, 2003 at 01:59:20PM +0200, Bo Lorentsen wrote:
> He likes to know about others using Postgres and simple replication (we
> need it in at 24/7 system), to know how postgres performs, and how
> stable it is regarding its data and the backup feature etc.

The replication system released last week (erserver: see gborg) is
the one that we originally used on our production systems.  It held
up under load for 2 years.  If you do a whois for .info or .org
domain names, you are hitting a PostgreSQL database.  You can check
out the SLAs on those systems on the ICANN site, since they post all
the contracts.

A

----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Commercial postgresql

From
"Ivar"
Date:
> These things are quite important when you have to convince your boss :-)

Convincing boss just say: "Caugh board or use postgresql".

Meaning of this is that you can't compare comercical or free ware.
Probably you want to use postgre because of its free, if this isn't case
why you won use oracle ?

"Bo Lorentsen" <bl@netgroup.dk> wrote in message
news:1062498330.25825.7057.camel@localhost...
> On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote:
>
> > That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple
with
> > autovacuum daemon or scheduled vacuums, things (hopefully) will be lot
better
> > than earlier days..
> Are there a list of things that will be done in 7.4, or better a
> development roadmap like the one the Mozilla folks are using ?
>
> These things are quite important when you have to convince your boss :-)
>
> /BL
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>



Re: Commercial postgresql

From
"Shridhar Daithankar"
Date:
On 2 Sep 2003 at 12:25, Bo Lorentsen wrote:

> On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote:
>
> > That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with
> > autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better
> > than earlier days..
> Are there a list of things that will be done in 7.4, or better a
> development roadmap like the one the Mozilla folks are using ?

Yes. Do a CVS checkout and check History and TODO files. There are fairly well
maintained.

Also check pending patches. http://developers.postgresql.org is the palce to
start. 7.4 Documentation is another place you could check for new features..

I think it could have been better organised. A huge bugzilla like KDE uses
could be a great thing. But for sure, there is nothing missing. You just need
to get tuned to the way postgresql gets developed.

Hang on on hackers for some time and you will pick up.

> These things are quite important when you have to convince your boss :-)

I know.:-)

Bye
 Shridhar

--
Hand, n.:    A singular instrument worn at the end of a human arm and    commonly
thrust into somebody's pocket.        -- Ambrose Bierce, "The Devil's Dictionary"


Re: Commercial postgresql

From
Vivek Khera
Date:
>>>>> "BL" == Bo Lorentsen <bl@netgroup.dk> writes:

BL> On Tue, 2003-09-02 at 04:08, Vivek Khera wrote:
>> I use it in 24/7/365 system which is heavily written to and read
>> from.  The drawbacks I have are:
BL> How depressing, may I ask that PG version you are using ?

Currently 7.2 in production, 7.4b2 in testing on the new system...


>> 1) upgrade to major versions require dump/restore which is a
>> significant amount of downtime for a large DB.
BL> Ok, this is not a thing you do very often, and it would help is we got a
BL> "diff" (since last backup) pg_dump. As one could install the new DB in
BL> parallel with produktion, and then just apply the diff dump on the db
BL> swap.

Well, the thing is for a large DB which is very active, it still
requires significant down-time, since you can't do this 'live'.


>> 2) the need to run vacuum on tables to keep them from bloating too
>> much.  on my system which is very busy, sometimes running vacuum
>> pushes the disk beyond its limits and slows the whole system to a
>> crawl.
BL> How often does this vacuum run, and how many delete/updates are there in
BL> between ?

There are *at least* 1 million inserts and 1 million updates per day.
Every two weeks, I purge some old data, which means something like 25
to 30 million rows deleted across several tables (thank $DIETY for
cascade delete).


>> 3) Index bloat is apparently a bigger problem than I thought.
BL> This does not sound too nice !

No, like I said, I shaved 900Mb of index table size this weekend by
re-indexing.  Unfortunately it meant I was partially down for about 45
minutes per index on my largest table, and about 15 per index on the
second largest table, and 5 per index on the third largest, then about
90 seconds total for the rest of the tables ;-)


>> If you want commercial support, it is out there.  There are at least
>> two companies offering it.
BL> But you have not been unsing any of there services ?

yes.  but for a very specific type of support.

Re: Commercial postgresql

From
"Shridhar Daithankar"
Date:
On 2 Sep 2003 at 9:36, Vivek Khera wrote:

> >>>>> "BL" == Bo Lorentsen <bl@netgroup.dk> writes:
>
> BL> On Tue, 2003-09-02 at 04:08, Vivek Khera wrote:
> >> 3) Index bloat is apparently a bigger problem than I thought.
> BL> This does not sound too nice !
>
> No, like I said, I shaved 900Mb of index table size this weekend by
> re-indexing.  Unfortunately it meant I was partially down for about 45
> minutes per index on my largest table, and about 15 per index on the
> second largest table, and 5 per index on the third largest, then about
> 90 seconds total for the rest of the tables ;-)

Umm.. Since you have only 2.7GB of data, all inclusive, would it be real
downtime if you reindex in a transaction, assuming the  "downtime" was not due
to crunch of IO bandwidth..

Just a thought..

Bye
 Shridhar

--
Beauty:    What's in your eye when you have a bee in your hand.


Re: Commercial postgresql

From
Vivek Khera
Date:
>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:

>> second largest table, and 5 per index on the third largest, then about
>> 90 seconds total for the rest of the tables ;-)

SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it
SD> be real downtime if you reindex in a transaction, assuming the
SD> "downtime" was not due to crunch of IO bandwidth..

Reindexing a table takes an exclusive table lock.  If I did it inside
a transaction, wouldn't it still take that lock and block out all
other access?

I just did one index at a time, waited a few minutes did the next for
my big tables, than just reindexed the others all in a row.  Last time
I did this must have been 9 or 10 months.  One index went from 500000
pages to under 220000.  Right now the system is screamingly fast.

Perhaps I need to write an 'auto_reindex' script to notice when this
is necessary and schedule one to run at the wee hours in the morning
at the end of the week...


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: Commercial postgresql

From
"Shridhar Daithankar"
Date:
On 2 Sep 2003 at 10:19, Vivek Khera wrote:

> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
>
> >> second largest table, and 5 per index on the third largest, then about
> >> 90 seconds total for the rest of the tables ;-)
>
> SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it
> SD> be real downtime if you reindex in a transaction, assuming the
> SD> "downtime" was not due to crunch of IO bandwidth..
>
> Reindexing a table takes an exclusive table lock.  If I did it inside
> a transaction, wouldn't it still take that lock and block out all
> other access?

Well, you donm't need to reindex as such. You can create a new index from
scratch and drop the old one inside a transaction.

That will be perfectly non-blocking I believe..

> I just did one index at a time, waited a few minutes did the next for
> my big tables, than just reindexed the others all in a row.  Last time
> I did this must have been 9 or 10 months.  One index went from 500000
> pages to under 220000.  Right now the system is screamingly fast.
>
> Perhaps I need to write an 'auto_reindex' script to notice when this
> is necessary and schedule one to run at the wee hours in the morning
> at the end of the week...

Once again, with 7.4, not needed anymore..

Bye
 Shridhar

--
Anthony's Law of the Workshop:    Any tool when dropped, will roll into the least accessible    corner of the
workshop.Corollary:   On the way to the corner, any dropped tool will first strike    your toes. 


Re: Commercial postgresql

From
Tom Lane
Date:
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> On 2 Sep 2003 at 10:19, Vivek Khera wrote:
>> Reindexing a table takes an exclusive table lock.

> Well, you donm't need to reindex as such. You can create a new index from
> scratch and drop the old one inside a transaction.
> That will be perfectly non-blocking I believe..

It still blocks updates to the table while the new index is being built.

            regards, tom lane

Re: Commercial postgresql

From
Bruno Wolff III
Date:
On Tue, Sep 02, 2003 at 12:25:31 +0200,
  Bo Lorentsen <bl@netgroup.dk> wrote:
> Are there a list of things that will be done in 7.4, or better a
> development roadmap like the one the Mozilla folks are using ?

For a quick look see the current version of the release notes on the web at:
http://developer.postgresql.org/docs/postgres/release.html

This will be a little easier than checking out a copy from CVS. However,
I think the HISTORY file has more detail in it.

Re: Commercial postgresql

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
>   Bo Lorentsen <bl@netgroup.dk> wrote:
>> Are there a list of things that will be done in 7.4, or better a
>> development roadmap like the one the Mozilla folks are using ?

> For a quick look see the current version of the release notes on the web at:
> http://developer.postgresql.org/docs/postgres/release.html

> This will be a little easier than checking out a copy from CVS. However,
> I think the HISTORY file has more detail in it.

I don't believe Bruce has yet made any effort to update the SGML
release-notes file for 7.4.  Instead look at the CVS-tip HISTORY file:

http://developer.postgresql.org/cvsweb.cgi/pgsql-server/HISTORY

            regards, tom lane

Re: Commercial postgresql

From
Bo Lorentsen
Date:
On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote:

> This will be a little easier than checking out a copy from CVS. However,
> I think the HISTORY file has more detail in it.
I think you are right, maybe a cvsview utility would help, regarding
this problem ? But a bugzilla tool, may also be nice :-)

/BL


Re: Commercial postgresql

From
Bo Lorentsen
Date:
On Tue, 2003-09-02 at 23:32, Tom Lane wrote:

> I don't believe Bruce has yet made any effort to update the SGML
> release-notes file for 7.4.  Instead look at the CVS-tip HISTORY file:

> http://developer.postgresql.org/cvsweb.cgi/pgsql-server/HISTORY
Ok thanks, this is very usefull !

/BL


Re: Commercial postgresql

From
"Nigel J. Andrews"
Date:
On Tue, 2 Sep 2003, Bo Lorentsen wrote:

> On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote:
>
> > This will be a little easier than checking out a copy from CVS. However,
> > I think the HISTORY file has more detail in it.
> I think you are right, maybe a cvsview utility would help, regarding
> this problem ? But a bugzilla tool, may also be nice :-)
>
> /BL

There is cvsweb available for the repository. I think it's linked from
somewhere on http://developers.postgresql.org/


--
Nigel J. Andrews


Re: Commercial postgresql

From
Bo Lorentsen
Date:
On Tue, 2003-09-02 at 23:38, Nigel J. Andrews wrote:
> There is cvsweb available for the repository. I think it's linked from
> somewhere on http://developers.postgresql.org/
Thanks, found it, very usefull.

/BL


Re: Commercial postgresql

From
Vivek Khera
Date:
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> I don't believe Bruce has yet made any effort to update the SGML
TL> release-notes file for 7.4.  Instead look at the CVS-tip HISTORY file:

Definitely not since the 7.4b2 INSTALL docs refer to it being version
7.3.


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: Commercial postgresql

From
Vivek Khera
Date:
>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:

>> Reindexing a table takes an exclusive table lock.  If I did it inside
>> a transaction, wouldn't it still take that lock and block out all
>> other access?

SD> Well, you donm't need to reindex as such. You can create a new index from
SD> scratch and drop the old one inside a transaction.

What about the primary keys?  How do you create a new primary key
without first dropping the old one?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: Commercial postgresql

From
Christopher Browne
Date:
After a long battle with technology,lowen@pari.edu (Lamar Owen), an earthling, wrote:
> On Monday 01 September 2003 22:08, Vivek Khera wrote:
>> I use it in 24/7/365 system which is heavily written to and read
>> from.  The drawbacks I have are:
>
> Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year.

Actually, it usually gets presented as 7x24x365, which has the same
sort of insensible transposition as gets used in American dates.  It's
a buzzword, so how you imagine it _should_ get spelled isn't relevant
:-).

>> 1) upgrade to major versions require dump/restore which is a
>>    significant amount of downtime for a large DB.
>
> I have harped on this at length.  Maybe one day we'll get real upgrading.
> Search the archives for the discussions; there are many, and they are long
> threads.

Another strategy would involve replicating to a database running the
new version; this could seriously diminish the need for downtime.

You would have an instance of the old version and one of the new; the
new copy might get set up for replication over some fairly substantial
period of time, and once it got caught up, you'd switch over to the
replicated copy, which might only take minutes.

That's a _real_ dynamic approach, one that involves serious
preparations.  Wildly more involved than other approaches...
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/lisp.html
Appendium to  the Rules  of the  Evil Overlord #1:  "I will  not build
excessively integrated  security-and-HVAC systems. They  may be Really
Cool, but are far too vulnerable to breakdowns."

Re: Commercial postgresql

From
Christopher Browne
Date:
After takin a swig o' Arrakan spice grog, shridhar_daithankar@persistent.co.in ("Shridhar Daithankar") belched out...:
> On 2 Sep 2003 at 10:19, Vivek Khera wrote:
>
>> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
>>
>> >> second largest table, and 5 per index on the third largest, then about
>> >> 90 seconds total for the rest of the tables ;-)
>>
>> SD> Umm.. Since you have only 2.7GB of data, all inclusive, would it
>> SD> be real downtime if you reindex in a transaction, assuming the
>> SD> "downtime" was not due to crunch of IO bandwidth..
>>
>> Reindexing a table takes an exclusive table lock.  If I did it inside
>> a transaction, wouldn't it still take that lock and block out all
>> other access?
>
> Well, you donm't need to reindex as such. You can create a new index
> from scratch and drop the old one inside a transaction.
>
> That will be perfectly non-blocking I believe..

That won't block _reads_ on the table.

It will block writes to the table during the duration of the
transaction.

After all, if you insert a row into the table whilst the index
creation is taking place, there's a bit of a conflict:

 -> For the system to remain consistent, that row's data either must
    be added to the index-in-progress, or be deferred 'til later;

 -> Since the index creation is inside the transaction, the insert
    shouldn't be able to "see" the index yet.

The insert obviously can't affect an index that it can't yet see, so
what happens in practice is that PostgreSQL blocks the insert until
the index is complete.

>> Perhaps I need to write an 'auto_reindex' script to notice when
>> this is necessary and schedule one to run at the wee hours in the
>> morning at the end of the week...

> Once again, with 7.4, not needed anymore..

I haven't had a chance to verify the non-necessity on real data; I
would very much like to see more of our apps testing on 7.4 so as to
verify this, but there's enough work validating that it's all good on
7.3.4...
--
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/nonrdbms.html
When I die, I'd like to go peacefully in my sleep like my grandfather,
not screaming in terror like his passengers...