Re: database size growing continously

On Fri, Oct 30, 2009 at 1:18 PM, Jeremy Harris <jgh(at)wizmail(dot)org> wrote:
> So, on the becoming more intelligent front:  PostgreSQL already does
> some operations as background maintenance (autovacuum).  Extending
> this to de-bloat indices does not seem conceptually impossible

It could be done but it's not easy because there will be people
concurrently scanning the index. Vacuum is limited to operations it
can do without blocking other jobs.

>, nor for the collection of table-data statistics for planner guidance

Well autovacuum already does this.

> (also, why
> could a full-table-scan not collect stats as a side-effect?).

That's a good idea but there are difficulties with it. The full table
scan might not run to completion meaning you may have done a lot of
work for nothing. Also gathering and processing that data is fairly
expensive, especially for higher stats targets. It requires sorting
the data by each column which takes some cpu time which we wouldn't
want to make sql queries wait for.

>  Further out, how about the gathering of statistics on queries to guide the automatic
> creation of indices?

I think we do need more run-time stats. How to make use of them would
be a complex question. We could automatically tune the cost
parameters, we could automatically try other plans and see if they run
faster, we could even automatically build indexes. Not all of these
would be appropriate in every environment though.

>  Or to set up a partitioning scheme on a previously monolithic table?

Well that involves moving data that other users might be busy
accessing. Again we wouldn't want an automatic background job blocking
user queries.

--
greg