Skip to content

Instantly share code, notes, and snippets.

@rdeguzman
Last active April 23, 2018 06:43
Show Gist options
  • Save rdeguzman/d826fb80079af5bc861317ab33add9eb to your computer and use it in GitHub Desktop.
Save rdeguzman/d826fb80079af5bc861317ab33add9eb to your computer and use it in GitHub Desktop.
Postgres functions for disk monitoring, etc
# postgres mgmt funcitons
## show total_bytes, index_bytes
SELECT c.oid,
nspname AS table_schema,
relname AS TABLE_NAME,
c.reltuples AS row_estimate,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_bytes,
pg_size_pretty(pg_indexes_size(c.oid)) AS index_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
ORDER BY row_estimate DESC;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes
--------+--------------------+---------------------------+--------------+-------------+-------------
302543 | dfms_20800 | gps_histories | 1.86702e+07 | 12 GB | 2573 MB
282734 | dfms_10324 | gps_histories | 1.63947e+07 | 10167 MB | 2226 MB
304958 | dfms_30500 | gps_histories | 1.16624e+07 | 7400 MB | 1582 MB
293366 | dfms_10731 | gps_histories | 9.78157e+06 | 6068 MB | 1327 MB
303026 | dfms_20900 | gps_histories | 6.79223e+06 | 4372 MB | 935 MB
305927 | dfms_30700 | gps_histories | 5.14508e+06 | 3253 MB | 708 MB
284183 | dfms_10401 | gps_histories | 4.42729e+06 | 2769 MB | 605 MB
300128 | dfms_10977 | gps_histories | 3.9728e+06 | 2481 MB | 544 MB
286115 | dfms_10522 | gps_histories | 3.9027e+06 | 2491 MB | 529 MB
290460 | dfms_10665 | gps_histories | 3.46105e+06 | 2210 MB | 477 MB
## total index
SELECT pg_size_pretty(SUM(pg_indexes_size(c.oid)))
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r';
## timestamp => date
SELECT extract(epoch from '2018-03-30T00:00:00+00' AT TIME ZONE 'UTC');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment