Postgres Tricks

De StoqWiki

Here is a list of useful Postgres queries you can use to find out the sizes of databases, tables and indexes: Everything here is currently specific to Postgres 8.1

Tabela de conteúdo 1 Show hidden queries 2 Size queries 3 Introspection 4 Quick deleting 5 Disable autovaccum

[editar] Show hidden queries

Hidden queries are the onces made by the postgres client itself.

\set ECHO_HIDDEN t

[editar] Size queries

Databases:

SELECT datname,pg_size_pretty(pg_database_size(oid)) FROM pg_database ORDER BY pg_database_size(oid) DESC;

Tables and indexes:

SELECT relname,pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname NOT LIKE 'pg_%' ORDER BY pg_relation_size(oid) DESC;

Tables only:

SELECT pg_tables.tablename, pg_tables.schemaname, pg_size_pretty(pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text)) AS pg_size_pretty, pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) AS rs
    FROM pg_tables
   ORDER BY pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) DESC;

Indexes only:

SELECT pg_indexes.indexname, pg_size_pretty(pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text)) AS pg_size_pretty, pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text) AS rs
    FROM pg_indexes
   ORDER BY pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text) DESC;

[editar] Introspection

If there's a relation with name FOO

SELECT COUNT(relname) FROM pg_class WHERE relname = 'FOO';

[editar] Quick deleting

To delete all content from a table very quickly do the following:

TRUNCATE table_name [, table_name2 ...];

[editar] Disable autovaccum

It might be a good idea to disable autovacuum on big databases, it can be done with the following command:

INSERT INTO pg_autovacuum
(vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit)
SELECT oid,false,-1,-1,-1,-1,-1,-1 FROM pg_class WHERE relkind='r';

Remember that you have to do the same for all entries which references the table and include them on the same line.