techgaun · May 7, 2024 14:44 · May 7, 2024 · Nov 26, 2020 · May 31, 2020 · May 13, 2020
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -126,4 +126,7 @@ relname AS TableName
 FROM pg_stat_user_tables;
 
 -- autovacuum stats
-SELECT relname, last_autovacuum,last_autoanalyze FROM pg_stat_user_tables;
+SELECT relname, last_autovacuum,last_autoanalyze FROM pg_stat_user_tables;
+
+-- largest 10 tables
+SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -114,4 +114,16 @@ select pid,
        pg_blocking_pids(pid) as blocked_by, 
        query as blocked_query
 from pg_stat_activity
-where cardinality(pg_blocking_pids(pid)) > 0;
+where cardinality(pg_blocking_pids(pid)) > 0;
+
+-- dead tuples
+SELECT
+relname AS TableName
+,n_live_tup AS LiveTuples
+,n_dead_tup AS DeadTuples
+,last_autovacuum AS Autovacuum
+,last_autoanalyze AS Autoanalyze
+FROM pg_stat_user_tables;
+
+-- autovacuum stats
+SELECT relname, last_autovacuum,last_autoanalyze FROM pg_stat_user_tables;
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -82,6 +82,24 @@ select pg_size_pretty(pg_relation_size('users_pkey'));
 -- postgres table size including indexes
 select pg_size_pretty(pg_total_relation_size('users'));
 
+-- postgres table sizes
+SELECT *, pg_size_pretty(total_bytes) AS total
+    , pg_size_pretty(index_bytes) AS INDEX
+    , pg_size_pretty(toast_bytes) AS toast
+    , pg_size_pretty(table_bytes) AS TABLE
+  FROM (
+  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
+      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
+              , c.reltuples AS row_estimate
+              , pg_total_relation_size(c.oid) AS total_bytes
+              , pg_indexes_size(c.oid) AS index_bytes
+              , pg_total_relation_size(reltoastrelid) AS toast_bytes
+          FROM pg_class c
+          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+          WHERE relkind = 'r'
+  ) a
+) a;
+
 -- list enums
 select n.nspname as enum_schema,  
        t.typname as enum_name,  

diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -80,4 +80,20 @@ select pg_size_pretty(pg_relation_size('users'));
 select pg_size_pretty(pg_relation_size('users_pkey'));
 
 -- postgres table size including indexes
-select pg_size_pretty(pg_total_relation_size('users'));
+select pg_size_pretty(pg_total_relation_size('users'));
+
+-- list enums
+select n.nspname as enum_schema,  
+       t.typname as enum_name,  
+       e.enumlabel as enum_value
+from pg_type t 
+   join pg_enum e on t.oid = e.enumtypid  
+   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
+
+-- list blocked queries
+select pid, 
+       usename, 
+       pg_blocking_pids(pid) as blocked_by, 
+       query as blocked_query
+from pg_stat_activity
+where cardinality(pg_blocking_pids(pid)) > 0;
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -72,4 +72,12 @@ SELECT relid::regclass as table, indexrelid::regclass as index
 
 -- postgres db size at rest
 select pg_size_pretty(pg_database_size('zego'));
-p
+
+-- postgres table size
+select pg_size_pretty(pg_relation_size('users'));
+
+-- postgres index size
+select pg_size_pretty(pg_relation_size('users_pkey'));
+
+-- postgres table size including indexes
+select pg_size_pretty(pg_total_relation_size('users'));
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -68,4 +68,8 @@ SELECT relid::regclass as table, indexrelid::regclass as index
   JOIN pg_index
  USING (indexrelid)
  WHERE idx_scan = 0
-   AND indisunique IS FALSE order by pg_relation_size(indexrelid)
+   AND indisunique IS FALSE order by pg_relation_size(indexrelid)
+
+-- postgres db size at rest
+select pg_size_pretty(pg_database_size('zego'));
+p
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -60,3 +60,12 @@ JOIN pg_index i ON ui.indexrelid = i.indexrelid
 WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
 pg_relation_size(i.indexrelid) DESC;
+
+-- completely unused indexes
+SELECT relid::regclass as table, indexrelid::regclass as index
+     , pg_size_pretty(pg_relation_size(indexrelid))
+  FROM pg_stat_user_indexes
+  JOIN pg_index
+ USING (indexrelid)
+ WHERE idx_scan = 0
+   AND indisunique IS FALSE order by pg_relation_size(indexrelid)
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
@@ -48,3 +48,15 @@ $ pg_dump -U username -h hostname databasename > dump.sql
 
 -- Import dump into existing database
 $ psql -d newdb -f dump.sql
+
+-- top unused indexes
+$ SELECT
+  schemaname || '.' || relname AS table,
+  indexrelname AS index,
+  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
+  idx_scan as index_scans
+FROM pg_stat_user_indexes ui
+JOIN pg_index i ON ui.indexrelid = i.indexrelid
+WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
+ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
+pg_relation_size(i.indexrelid) DESC;
diff --git a/Useful PostgreSQL Queries and Commands → postgres_queries_and_commands.sql b/Useful PostgreSQL Queries and Commands → postgres_queries_and_commands.sql
diff --git a/postgres.sql → Useful PostgreSQL Queries and Commands b/postgres.sql → Useful PostgreSQL Queries and Commands
diff --git a/postgres.sql b/postgres.sql
@@ -42,3 +42,9 @@ ORDER BY n_live_tup DESC;
 -- how many indexes are in cache
 SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
 FROM pg_statio_user_indexes;
+
+-- Dump database on remote host to file
+$ pg_dump -U username -h hostname databasename > dump.sql
+
+-- Import dump into existing database
+$ psql -d newdb -f dump.sql
diff --git a/postgres.sql b/postgres.sql
@@ -42,7 +42,3 @@ ORDER BY n_live_tup DESC;
 -- how many indexes are in cache
 SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
 FROM pg_statio_user_indexes;
-
--- Dump database on remote host to file
-pg_dump -U username -h hostname databasename > dump.sql
-
diff --git a/postgres.sql b/postgres.sql
@@ -44,5 +44,5 @@ SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_b
 FROM pg_statio_user_indexes;
 
 -- Dump database on remote host to file
-pg_dump -U sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql
+pg_dump -U username -h hostname databasename > dump.sql
 
diff --git a/postgres.sql b/postgres.sql
@@ -42,3 +42,7 @@ ORDER BY n_live_tup DESC;
 -- how many indexes are in cache
 SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
 FROM pg_statio_user_indexes;
+
+-- Dump database on remote host to file
+pg_dump -U sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql
+
diff --git a/postgres.sql b/postgres.sql
@@ -16,6 +16,9 @@ SELECT pg_cancel_backend(procpid);
 -- kill idle query
 SELECT pg_terminate_backend(procpid);
 
+-- vacuum command
+VACUUM (VERBOSE, ANALYZE);
+
 -- all database users
 select * from pg_stat_activity where current_query not like '<%';
 

diff --git a/postgres.sql b/postgres.sql
@@ -28,10 +28,7 @@ from pg_database
 order by pg_database_size(datname) desc;
 
 -- cache hit rates (should not be less than 0.99)
-SELECT 
-  sum(heap_blks_read) as heap_read,
-  sum(heap_blks_hit)  as heap_hit,
-  (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
+SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
 FROM pg_statio_user_tables;
 
 -- table index usage rates (should not be less than 0.99)

diff --git a/postgres.sql b/postgres.sql
@@ -23,34 +23,22 @@ select * from pg_stat_activity where current_query not like '<%';
 select * from pg_user;
 
 -- all tables and their size, with/without indexes
-select datname,
-       pg_size_pretty(pg_database_size(datname))
-  from pg_database
- order by pg_database_size(datname) desc;
-
+select datname, pg_size_pretty(pg_database_size(datname))
+from pg_database
+order by pg_database_size(datname) desc;
 
 -- cache hit rates (should not be less than 0.99)
 SELECT 
   sum(heap_blks_read) as heap_read,
   sum(heap_blks_hit)  as heap_hit,
   (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
-FROM 
-  pg_statio_user_tables;
+FROM pg_statio_user_tables;
 
 -- table index usage rates (should not be less than 0.99)
-SELECT 
-  relname, 
-  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
-  n_live_tup rows_in_table
-FROM 
-  pg_stat_user_tables 
-ORDER BY 
-  n_live_tup DESC;
+SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
+FROM pg_stat_user_tables 
+ORDER BY n_live_tup DESC;
 
 -- how many indexes are in cache
-SELECT 
-  sum(idx_blks_read) as idx_read,
-  sum(idx_blks_hit)  as idx_hit,
-  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
-FROM 
-  pg_statio_user_indexes;
+SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
+FROM pg_statio_user_indexes;
diff --git a/postgres.sql b/postgres.sql
@@ -1,9 +1,15 @@
--- show running queries
+-- show running queries (pre 9.2)
 SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
 FROM pg_stat_activity 
 WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;
 
+-- show running queries (9.2)
+SELECT pid, age(query_start, clock_timestamp()), usename, query 
+FROM pg_stat_activity 
+WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
+ORDER BY query_start desc;
+
 -- kill running query
 SELECT pg_cancel_backend(procpid);
 

diff --git a/postgres.sql b/postgres.sql
@@ -7,6 +7,9 @@ ORDER BY query_start desc;
 -- kill running query
 SELECT pg_cancel_backend(procpid);
 
+-- kill idle query
+SELECT pg_terminate_backend(procpid);
+
 -- all database users
 select * from pg_stat_activity where current_query not like '<%';
 

diff --git a/postgres.sql b/postgres.sql
@@ -36,4 +36,12 @@ SELECT
 FROM 
   pg_stat_user_tables 
 ORDER BY 
-  n_live_tup DESC;
+  n_live_tup DESC;
+
+-- how many indexes are in cache
+SELECT 
+  sum(idx_blks_read) as idx_read,
+  sum(idx_blks_hit)  as idx_hit,
+  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
+FROM 
+  pg_statio_user_indexes;
diff --git a/postgres.sql b/postgres.sql
@@ -18,3 +18,22 @@ select datname,
        pg_size_pretty(pg_database_size(datname))
   from pg_database
  order by pg_database_size(datname) desc;
+
+
+-- cache hit rates (should not be less than 0.99)
+SELECT 
+  sum(heap_blks_read) as heap_read,
+  sum(heap_blks_hit)  as heap_hit,
+  (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
+FROM 
+  pg_statio_user_tables;
+
+-- table index usage rates (should not be less than 0.99)
+SELECT 
+  relname, 
+  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
+  n_live_tup rows_in_table
+FROM 
+  pg_stat_user_tables 
+ORDER BY 
+  n_live_tup DESC;
diff --git a/postgres.sql b/postgres.sql
@@ -0,0 +1,20 @@
+-- show running queries
+SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
+FROM pg_stat_activity 
+WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
+ORDER BY query_start desc;
+
+-- kill running query
+SELECT pg_cancel_backend(procpid);
+
+-- all database users
+select * from pg_stat_activity where current_query not like '<%';
+
+-- all databases and their sizes
+select * from pg_user;
+
+-- all tables and their size, with/without indexes
+select datname,
+       pg_size_pretty(pg_database_size(datname))
+  from pg_database
+ order by pg_database_size(datname) desc;