Обсуждение: managing database with thousands of tables

Hello!

I facing some strange problems with PostgreSQL 8.0 performance.
I have application which handles a lot of tasks, each task is keps in separate
table. Those tables are dropped and created again periodically (precisely -
when new task results came back from remote server). Also each table can have
hundreds of thousands records inside (but mostly they do have just few
thousands).

Sometimes I facing performance loss when working with database, and aafter I
performed vacuuming on entire database, i saw some tables and indexes in pg_*
schemas were optimized and hundreds of thousands records were deleted. Could
that be the reason of performance loss, and if so - how can I fix that?

I have pg_autovacuum up and running all the time

pg_autovacuum -d 3 -D -L /dev/null

but it seems pg_autovacuum does not do vacuuming on system tables.

--
Eugene Dzhurinsky

Eugeny N Dzhurinsky <bofh@redwerk.com> writes:
> but it seems pg_autovacuum does not do vacuuming on system tables.

There was a bug awhile back whereby autovac failed to notice temp table
cleanup at connection end --- maybe you need to update?

            regards, tom lane

On Wed, Jul 05, 2006 at 09:39:31AM -0400, Tom Lane wrote:
> Eugeny N Dzhurinsky <bofh@redwerk.com> writes:
> > but it seems pg_autovacuum does not do vacuuming on system tables.
>
> There was a bug awhile back whereby autovac failed to notice temp table
> cleanup at connection end --- maybe you need to update?

May be. So should I update to newer postgres 8.1 or just upgrade
pg_autovacuum somehow (I don't know how btw ;) )?

--
Eugene N Dzhurinsky

Eugeny N Dzhurinsky wrote:
> On Wed, Jul 05, 2006 at 09:39:31AM -0400, Tom Lane wrote:
>> Eugeny N Dzhurinsky <bofh@redwerk.com> writes:
>>> but it seems pg_autovacuum does not do vacuuming on system tables.
>> There was a bug awhile back whereby autovac failed to notice temp table
>> cleanup at connection end --- maybe you need to update?
>
> May be. So should I update to newer postgres 8.1 or just upgrade
> pg_autovacuum somehow (I don't know how btw ;) )?

Update the whole lot. You should be able to do the upgrade "in place"
but take a backup "just in case".

http://www.postgresql.org/docs/8.1/interactive/release.html

will list all changes between versions.

--
Postgresql & php tutorials
http://www.designmagick.com/