Largest index. Table size.
--- a/postgres.rst Mon Sep 16 23:10:34 2019 +0300
+++ b/postgres.rst Wed Sep 18 09:29:55 2019 +0300
@@ -127,7 +127,8 @@
Granting default privileges for new objects::
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user;
- ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO rw_user;
+ ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON SEQUENCES TO rw_user;
+ ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT EXECUTE ON FUNCTIONS TO rw_user;
Revoke privileges and drop role
===============================
@@ -381,6 +382,8 @@
\l+
+ SELECT pg_database_size('mydb');
+
List tables sizes (and constraints)::
\d+
@@ -389,10 +392,16 @@
SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));
+Table size with TOAST (without indexes)::
+
+ SELECT pg_table_size('schemaname.tablename');
+
Sole table size (without indexes and other)::
SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));
+ SELECT pg_relation_size(t.schemaname ||'.'|| t.tablename), t.* FROM pg_tables t;
+
Largest table in the PostgreSQL database::
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
@@ -418,6 +427,14 @@
SELECT pg_column_size(5::int);
SELECT pg_column_size(5::bigint);
+Largest index::
+
+ select pg_relation_size(oid), * from pg_class
+ where relkind = 'i'
+ order by pg_relation_size(oid) desc;
+
+https://www.postgresql.org/docs/9.6/functions-admin.html
+ Database Object Management Functions.
https://wiki.postgresql.org/wiki/Disk_Usage
Finding the size of various object in your database.