Message-ID: <42B6DEC5.3080008@arbash-meinel.com>
Date: Mon, 20 Jun 2005 10:20:37 -0500
From: John Arbash Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: Alex Stapleton <alexs@advfn.com>,
	Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: autovacuum suggestions for 500,000,000+ row tables?
References: <BE7E1F60-FA3A-4843-988E-AC2CC1FA22E5@advfn.com>
	<6.2.0.14.0.20050620165134.03b61140@pop.interactivemediafactory.net>
	<C59BA512-5630-4F6E-82DD-BFC9F87046E0@advfn.com>
In-Reply-To: <C59BA512-5630-4F6E-82DD-BFC9F87046E0@advfn.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigA84180580675F413A1EE46C6"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigA84180580675F413A1EE46C6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Alex Stapleton wrote:

>
> On 20 Jun 2005, at 15:59, Jacques Caron wrote:
>
...

>> ANALYZE is not a very expensive operation, however VACUUM can
>> definitely be a big strain and take a looooong time on big tables,
>> depending on your setup. I've found that partitioning tables (at  the
>> application level) can be quite helpful if you manage to keep  each
>> partition to a reasonable size (under or close to available  memory),
>> especially if the partitioning scheme is somehow time- related. YMMV.
>>
>> Jacques.
>
>
> That's not currently an option as it would require a pretty large
> amount of work to implement. I think we will have to keep that in
> mind though.

Remember, you can fake it with a low-level set of tables, and then wrap
them into a UNION ALL view.
So you get something like:

CREATE VIEW orig_table AS
    SELECT * FROM table_2005_04
    UNION ALL SELECT * FROM table_2005_05
    UNION ALL SELECT * FROM table_2005_06
...
;

Then at least your individual operations are fast. As you insert, you
can create a rule that on insert into orig_table do instead ... insert
into table_2005_07 (or whatever the current table is).
It takes a little bit of maintenance on the DB admin's part, since every
month they have to create a new table, and then update all of the views
and triggers. But it is pretty straightforward.
If you are doing append-only inserting, then you have the nice feature
that only the last table is ever modified, which means that the older
tables don't really need to be vacuumed or analyzed.
And even if you have to have each table modified as you go, you still
can break up a VACUUM into only doing one of the sub tables at a time.

I don't know you db schema, but I thought I would mention that true
partitioning isn't implemented yet, you can still get something very
similar with views, triggers and rules.

John
=:->


--------------enigA84180580675F413A1EE46C6
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCtt7FJdeBCYSNAAMRArhQAJ9pCuosGdZek7n6zeCThnmbRMBfegCgsDuH
DC8XMOqzWW3aPGUTm35yKI0=
=feW3
-----END PGP SIGNATURE-----

--------------enigA84180580675F413A1EE46C6--