Largest index. Table size.
authorOleksandr Gavenko <gavenkoa@gmail.com>
Wed, 18 Sep 2019 09:29:55 +0300
changeset 2381 b26d26c54b05
parent 2380 6effef47c861
child 2382 5f3c4cc21532
Largest index. Table size.
postgres.rst
--- 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.