Thread: How to avoid vacuuming a huge logging table

Our application has a table that is only logged to, and infrequently
used for reporting. There generally no deletes and updates.

Recently, the shear size (an estimated 36 million rows) caused a serious
problem because it prevented a "vacuum analyze" on the whole database
from finishing in a timely manner.

As I understand, a table with this usage pattern wouldn't need to be
vacuumed anyway.

I'm looking for general advice from people who have faced the same
issue. I'm looking at a number of alternatives:

1. Once a month, we could delete and archive old rows, for possible
re-import later if we need to report on them. It would seem this would
need to be done as proper insert statements for re-importing. (Maybe
there is a solution for that with table partitioning? )

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.

3. 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.

Thanks for any tips.

    Mark

-----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-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


A minor correction to my earlier post: I should have specified the
schema as well in the vacuum command for tables with the same
name in different schemas:

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(nspname)||'.'||quote_ident(relname)||';'
  FROM pg_class c, pg_namespace n
  WHERE relkind = 'r'
  AND relnamespace = n.oid
  AND nspname = 'novac'
  ORDER BY 1;
\o
\i pop

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200702211652
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFF3L+XvJuQZxSWSsgRAwzeAKDz+YmLmm9K0of/ObjUux/P7fg7jwCfeSoK
TfVGoSyThrdFjlGXWn1aEGI=
=/jBZ
-----END PGP SIGNATURE-----

On Wed, 21 Feb 2007 21:58:33 -0000
"Greg Sabino Mullane" <greg@turnstep.com> wrote:
> SELECT 'vacuum verbose analyze '||quote_ident(nspname)||'.'||quote_ident(relname)||';'
>   FROM pg_class c, pg_namespace n
>   WHERE relkind = 'r'
>   AND relnamespace = n.oid
>   AND nspname = 'novac'
>   ORDER BY 1;

I assume you meant "AND nspname != 'novac'"

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.