Фул Стэк — программист

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_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 
LIMIT 100;

Задача

Необходимо найти долговыполняющиеся запросы к базе данных.

Решение

Необходимо включить вывод в лог запросов, длительность которых больше заданной длительности.

Читать далее «Поиск медленных запросов»

Examples

In the examples below, [tablename] is optional. Without a table specified, VACUUM will be run on available tables in the current schema that the user has access to.

1. Plain VACUUM: Frees up space for re-use

VACUUM [tablename]

1. Full VACUUM: Locks the database table, and reclaims more space than a plain VACUUM

/* Before Postgres 9.0: */ 
VACUUM FULL 
/* Postgres 9.0+: */ 
VACUUM(FULL) [tablename]

1. Full VACUUM and ANALYZE: Performs a Full VACUUM and gathers new statistics on query executions paths using ANALYZE

/* Before Postgres 9.0: */ 
VACUUM FULL ANALYZE [tablename] 
/* Postgres 9.0+: */ 
VACUUM(FULL, ANALYZE) [tablename]

1. Verbose Full VACUUM and ANALYZE: Same as #3, but with verbose progress output

/* Before Postgres 9.0: */ 
VACUUM FULL VERBOSE ANALYZE [tablename] 
/* Postgres 9.0+: */ 
VACUUM(FULL, ANALYZE, VERBOSE) [tablename]

Читать далее «Обслуживание PostgreSql»