Calculate PostgresSQL Database and OBJECT Size
Scripts which are useful in calculating size of postgressql
database
Individual postgresql database size
SELECT pg_size_pretty(pg_database_size('db_name'));
Get a listing of all of your databases in
Postgres and their sizes in GB, ordering by the largest size first
SELECT pg_database.datname as "database_name",
pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM
pg_database ORDER by size_in_GB DESC;
Get a listing of all of your databases in
Postgres and their sizes by using meta-command
\l+
Script to Find all the table size in
the current database.
SELECT table_schema || '.' || table_name AS TableName, pg_size_pretty(pg_total_relation_size('"'
|| table_schema || '"."' || table_name || '"')) AS TableSize FROM
information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema ||
'"."' || table_name || '"') DESC
Script to Find all the table and index size in
the current database.
SELECT
TableName
,pg_size_pretty(pg_table_size(TableName)) AS TableSize
,pg_size_pretty(pg_indexes_size(TableName))
AS IndexSize
,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM
(
SELECT ('"' ||
table_schema || '"."' || table_name || '"') AS TableName
FROM
information_schema.tables
) AS Tables
ORDER BY 4 DESC
Checking
table size as well as dependencies size
SELECT schemaname,
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "
table_Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as "External Size"
FROM
pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
Size of all tables , table related objects size
and total table size in a current schema or any schema
SELECT stats.relname
AS table,
pg_size_pretty(pg_relation_size(statsio.relid))
AS table_size,
pg_size_pretty(pg_total_relation_size(statsio.relid)
-
pg_relation_size(statsio.relid))
AS
related_objects_size,
pg_size_pretty(pg_total_relation_size(statsio.relid))
AS
total_table_size,
stats.n_live_tup
AS live_rows
FROM
pg_catalog.pg_statio_user_tables AS statsio
JOIN pg_stat_user_tables AS
stats
USING (relname)
WHERE stats.schemaname =
current_schema -- Replace with any
schema name
UNION ALL
SELECT 'TOTAL'
AS table,
pg_size_pretty(sum(pg_relation_size(statsio.relid)))
AS table_size,
pg_size_pretty(sum(pg_total_relation_size(statsio.relid)
-
pg_relation_size(statsio.relid)))
AS
related_objects_size,
pg_size_pretty(sum(pg_total_relation_size(statsio.relid)))
AS
total_table_size,
sum(stats.n_live_tup)
AS live_rows
FROM pg_catalog.pg_statio_user_tables
AS statsio
JOIN pg_stat_user_tables AS
stats
USING (relname)
WHERE stats.schemaname =
current_schema -- Replace with any
schema name
ORDER BY live_rows ASC;
Meta-command is also useful for listing the
existing tables size in a current schema
\d+
Meta-command is useful To show tables
size of all schema
\dt+ *.*
command is useful To show tables size for a
particular schema
\dt+ schema_name.*
How to get the current locks in database.
select * from pg_locks;
select * from pg_locks;
How to check the details of replication between
the primary and slave.
select * from
pg_stat_replication ;
How to get the database statistics like
hitratio, dml statistics.
SELECT
sdb.datname,blks_read,blks_hit,round((blks_hit::float / (blks_read+blks_hit+1)
* 100)::numeric, 2) as
cachehitratio,xact_commit,xact_rollback,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,ROUND(100.0*AGE(DATFROZENXID)/
(SELECT SETTING::float FROM PG_SETTINGS WHERE NAME ILIKE 'autovacuum_freeze_max_age'))
FROM pg_stat_database sdb INNER JOIN pg_database db on sdb.datname=db.datname
WHERE sdb.datname !~ '^(template(0|1))' ORDER BY cachehitratio desc;
The above query will
give you the database statistics.
No comments:
Post a Comment