I'd also like to hear from anyone on the original posters topic of the "24
hour shop".
I too am in that same boat. I have a DB with 7-8 million records on a Dual
550 with 512Meg Ram and 1gig swap and it takes vacuum 10 - 15 minutes each
evening to run.
Users think the site is hosed and management isn't exactly happy about it
either.
There is one DB on the machine that has two tables, one table has 2
columns and the other has about 25 columns.
I'd think some how there could be a way to vacuum without having to lock
up the entire DB.
Andy
On Fri, 14 Apr 2000, Lincoln Yeoh wrote:
> At 01:13 PM 14-04-2000 +0800, Thomas wrote:
> >There has been effort to speed up the vacuuming process, but this isn't the
> >cure. I believe the fault lies on the optimizer.
> >
> >For eg, in Bruce Momjian's FAQ 4.9:
> >
> > PostgreSQL does not automatically maintain statistics. One has to make
> > an explicit vacuum call to update the statistics. After statistics are
> > updated, the optimizer knows how many rows in the table, and can
> > better decide if it should use indices. Note that the optimizer does
> > not use indices in cases when the table is small because a sequential
> > scan would be faster.
>
> Is it too difficult/expensive for Postgresql to keep track of how many
> committed rows there are in each table? Then count(*) of the whole table
> could be faster too.
>
> Since it's just for optimization it could perhaps keep a rough track of how
> many rows would be selected for the past X indexed searches of a table, so
> as to better decide which index to use first. Right now it seems like the
> optimizer can't learn a thing till the database takes a nap and dreams
> about statistics. I prefer the database to be able to learn a few things
> before having to take a nap. And then maybe it will only need to take a nap
> once every few weeks/months.
>
> Also it's better for the optimizer to be good at figuring which index to
> use, than figure whether to use indexes at all. Because in most cases the
> people creating indexes on tables _should_ know whether to use indexes at
> all. So if there's an index use it. So what if it's a bit slower when
> things are small. I put in indexes to make sure that things are still ok
> when things get big!
>
> How many people care about the "slow down" when things are small? It's
> still fast! If things are going to stay small, then the database admin
> should just drop the index.
>
> Often predictable degradation is more useful than academically optimum.
>
> Cheerio,
>
> Link.
>