Re: What popular, large commercial websites run

Shaun Thomas wrote:
> On Mon, 29 Apr 2002, pgsql-gen Newsgroup wrote:
>
> > The way I see it, some managers will buy Oracle. They will have low
> > profit margines. Some programers will use PostgreSQL. They will have
> > high margins.
>
> That's all well and good provided postgres and Oracle were 100% feature
> compatible. They're not. Want inter-database queries? Too bad.
> Replication? Nope. Parallel queries? Scratch that. Packages? Big
> goose-egg. ADA-style error catching? Zero. In/Out variables? Only in
> your dreams, buddy. Views that take parameters? Zilch. Wanna actually
> drop your foreign keys, or change their status or triggering order for
> loading purposes? Not here.

Agreed. We are not 100% feature compatible with Oracle. We do have
some things Oracle doesn't, and they have some things we don't. I will
admit they have more things we don't have, and some of the things they
have are really useful, as you pointed out. However, some of they stuff
they have is just bloat, which can be a negative.

> Why are our databases bloating, even after hourly full vacuums? Because
> we have a database with a 50-100% data turnover rate at about 100,000
> rows, and postgres just can't handle it. I've watched our 100mb
> database grow to 500mb, then 2gigs. Full dump and restore? 70mb
> again. Oh, and the spiking load, and table locks that occur during
> full vacuums? Just take the hit, web-surfers be damned.

I have read the thread an no one seems to have addressed your problem
--- that a full vacuum doesn't reduce the db sizes back to 100mb. There
are two possible causes, one is an old transaction that is keeping those
expired tuples visible (unlikely), and index growth, particularly for
ever-increasing keys, like primary keys.

We have this TODO item:

* Certain indexes will not shrink, e.g. oid indexes with many inserts

I believe this has not been done because there are some problems with
doing this in a crash-safe manner (?).

Would you see if recreating some of the larger indexes helps reduce your
disk usage after vacuum?

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