Hi Greg, I've added you to the cc list because I'm proposing to change
some wiki content which you wrote
On Wed, Nov 7, 2012 at 11:54 AM, Lists <lists@benjamindsmith.com> wrote:
> On 11/07/2012 09:01 AM, Jeff Janes wrote:
>>
>> Ben, did you ever figure out where the space was going?
>
>
>
> Now, here's where it gets weird. From the disk space usage wiki,
> (http://wiki.postgresql.org/wiki/Disk_Usage) you'll recognize how I used it
> to get a total disk space used result:
>
> with mytable AS (
> SELECT
> nspname || '.' || relname AS "relation",
> pg_total_relation_size(C.oid) AS "size"
> FROM
> pg_class C
> LEFT JOIN pg_namespace N ON
> (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> AND C.relkind <> 'i'
> AND nspname !~ '^pg_toast'
> ORDER BY
> pg_total_relation_size(C.oid) DESC
> )
> SELECT sum(size) AS size FROM mytable
I question the wisdom of that where clause (from the wiki)
If the pg_catalog relations are big, then they are big and why
shouldn't they get reported as such? And if they are not big, that is
why the limit is there on the wiki page.
I'm tempted to go change it, but maybe there is a good reason it is
there which I do not understand.
...
>
> Google returns this page:
> http://www.postgresql.org/docs/9.1/static/catalog-pg-attribute.html which
> doesn't help me much. So, am I doing something wrong with admin? Our current
> process is that every night in the middle of the night, a script connects to
> each database on each server and runs a query to get all tables in each
> database and, for each, run
>
> "VACUUM ANALYZE $table"
>
> for each table in the database.
I take it your script that does that is not including the pg_catalog tables?
Why not just run "vacuum analyze" and let it do the entire database?
> I will note that autovacuum is off because it occasionally causes
> transactions and queries to hang when an update causes a vacuum mid-day,
> effectively taking us offline randomly.
Hang as in they are blocking on locks? Or they just get slow because
the autovacuum is consuming too much IO?
Cheers,
Jeff