Last active
November 26, 2025 14:51
-
-
Save acanimal/f07cebc6929ef4fcba30efe8bb6a8e56 to your computer and use it in GitHub Desktop.
Postgres get table sizes
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- table sizes | |
| -- | |
| SELECT | |
| relname AS "relation", | |
| pg_size_pretty(pg_relation_size(C.oid)) AS "size", | |
| pg_size_pretty(pg_total_relation_size(C.oid)) AS "size_with_indexes" | |
| 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; | |
| -- | |
| -- expensive queries | |
| -- | |
| SELECT | |
| query, | |
| calls, | |
| -- Total time spent executing this query (in milliseconds) | |
| ROUND(total_exec_time::numeric, 2) AS total_ms, | |
| -- Average time spent executing this query (in milliseconds) | |
| ROUND(mean_exec_time::numeric, 2) AS mean_ms, | |
| -- Percentage of total execution time across all tracked queries | |
| ROUND((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS time_percent | |
| FROM | |
| pg_stat_statements | |
| ORDER BY | |
| total_exec_time DESC | |
| LIMIT 10; | |
| -- | |
| -- realtime long running queries | |
| -- | |
| SELECT | |
| pid, | |
| age(clock_timestamp(), query_start) AS duration, | |
| query, | |
| state, | |
| datname, | |
| usename | |
| FROM | |
| pg_stat_activity | |
| WHERE | |
| state != 'idle' -- Exclude idle sessions | |
| AND query NOT ILIKE '%pg_stat_activity%' -- Exclude the current query | |
| ORDER BY | |
| duration DESC; | |
| -- | |
| -- installed extensions | |
| -- | |
| SELECT | |
| extname, | |
| extversion, | |
| extowner, | |
| extnamespace::regnamespace AS schema_name | |
| FROM | |
| pg_extension | |
| ORDER BY | |
| extname; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment