Commercial postgresql

[Bo Lorentsen]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jacob Hanson]

On Sat, 30 Aug 2003 16:18:14 +0200 (CEST), pe*****@gmx.net (Peter
Eisentraut) wrote:

many, but it's usually difficult to get customers to agree to this sort of
thing.

Why is that?

[Christopher Browne]

Oops! Jacob Hanson <ja*********@firsthealth.com> was seen spray-painting on a wall:

On Sat, 30 Aug 2003 16:18:14 +0200 (CEST), pe*****@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"

[Vivek Khera]

>>>>> "BL" == Bo Lorentsen <[e-mail]> 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: kh***@kciLink.com Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Lamar Owen]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Claudio Lapidus]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Christopher Browne]

After a long battle with technology,lo***@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."

[Bo Lorentsen]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Shridhar Daithankar]

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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bo Lorentsen]

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 ma*******@postgresql.org

[Andrew Sullivan]

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
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ivar]

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" <[e-mail]> 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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Shridhar Daithankar]

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"
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Vivek Khera]

>>>>> "BL" == Bo Lorentsen <[e-mail]> 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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Shridhar Daithankar]

On 2 Sep 2003 at 9:36, Vivek Khera wrote:

>> "BL" == Bo Lorentsen <[e-mail]> 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Vivek Khera]

>>>>> "SD" == Shridhar Daithankar <sh*****************@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: kh***@kciLink.com Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Shridhar Daithankar]

On 2 Sep 2003 at 10:19, Vivek Khera wrote:

>> "SD" == Shridhar Daithankar <sh*****************@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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Christopher Browne]

After takin a swig o' Arrakan spice grog, sh*****************@persistent.co.in ("Shridhar Daithankar") belched out...:

On 2 Sep 2003 at 10:19, Vivek Khera wrote:

>>>>> "SD" == Shridhar Daithankar <sh*****************@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...

[Bruno Wolff III]

On Tue, Sep 02, 2003 at 12:25:31 +0200,
Bo Lorentsen <[e-mail]> 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...s/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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Bruno Wolff III <br***@wolff.to> writes:

Bo Lorentsen <[e-mail]> 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...s/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/cvsw...server/HISTORY

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bo Lorentsen]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Bo Lorentsen]

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/cvsw...server/HISTORY

Ok thanks, this is very usefull !

/BL
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Nigel J. Andrews]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bo Lorentsen]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Vivek Khera]

>>>>> "SD" == Shridhar Daithankar <sh*****************@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: kh***@kciLink.com Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Vivek Khera]

>>>>> "TL" == Tom Lane <tg*@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: kh***@kciLink.com Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match