From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-performance@postgresql.org
Subject: Re: How to avoid vacuuming a huge logging table
Date: Wed, 21 Feb 2007 19:08:58 -0000
Message-ID: <ddcb039d012ec533c0cb330b5754b579@biglumber.com> (raw)
In-Reply-To: <eri1gd$29bq$1@news.hub.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> Take a really different approach. Log in CSV format to text files
> instead, And only import the date ranges we need "on demand" if a report
> is requested on the data.
Seems like more work than a separate database to me. :)
> 2. We could find a way to exclude the table for vacuuming, and let it
> grow even larger. Putting the table in it's own database would
> accomplish that, but it would nice to avoid the overhead of a second
> database connection.
Specific exclusions is generally what I've done for similar problems in
the past. If you can live without the per-database summary at the end of
the vacuum, you can do something like this:
SET search_path = 'pg_catalog';
SELECT set_config('search_path',
current_setting('search_path')||','||quote_ident(nspname),'false')
FROM pg_namespace
WHERE nspname <> 'pg_catalog'
ORDER BY 1;
\t
\o pop
SELECT 'vacuum verbose analyze '||quote_ident(relname)||';'
FROM pg_class
WHERE relkind = 'r'
AND relname <> 'ginormous_table'
ORDER BY 1;
\o
\i pop
Or put any tables you don't want vacuumed by this script into their own schema:
...
SELECT 'vacuum verbose analyze '||quote_ident(relname)||';'
FROM pg_class c, pg_namespace n
WHERE relkind = 'r'
AND relnamespace = n.oid
AND nspname = 'novac'
ORDER BY 1;
...
Just flip the equality operator, and you've got a way to vacuum just those
excluded tables, for example once a week during a slow time.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200702211402
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFF3JeivJuQZxSWSsgRA7LZAKC7Sfz4XBTAfHuk1CpR+eBl7ixBIACeML8N
1W2sLLI4HMtdyV4EOoh2XkY=
=eTUi
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@turnstep.com
Subject: Re: How to avoid vacuuming a huge logging table
In-Reply-To: <ddcb039d012ec533c0cb330b5754b579@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox