Print out disk usage per database:
SELECT pg_database.datname, pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(pg_database.datname))
FROM pg_catalog.pg_database
ORDER BY pg_catalog.pg_database_size(pg_database.datname) DESC;
Print sizes of tables in the current database (filtering out tables smaller than 1MB rules out most of the built-in metadata tables):
SELECT relname, pg_size_pretty(pg_relation_size(pg_class.oid))
FROM pg_class LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_relation_size(pg_class.oid) > 1000000
ORDER BY pg_relation_size(pg_class.oid) DESC;
Estimate row counts per table:
SELECT schemaname, relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables;
Table size and relkind, together with estimated row counts (for ordinary tables):
SELECT pg_class.relname, pg_class.relkind, pg_size_pretty(pg_relation_size(pg_class.oid)),
n_tup_ins, n_tup_del, n_live_tup, n_dead_tup
FROM pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT JOIN pg_stat_user_tables ON pg_stat_user_tables.relname = pg_class.relname
WHERE pg_relation_size(pg_class.oid) > 1000000
ORDER BY pg_relation_size(pg_class.oid) DESC
Show current connections:
SELECT * FROM pg_stat_activity;
Reload configuration:
SELECT pg_reload_conf();
psqlHelpers for psql: https://github.com/datachomp/dotfiles/blob/master/.psqlrc#L53
psql CLIRun a single command on the ‘blog’ database and return.
psql blog -c "TABLE posts"
Run a query and format the results in clean TSV.
psql -P footer=off -A -F $'\t' blog -c "TABLE posts"
-P footer=off omits the (123 rows) line in the footer-A turns off the default of padding with whitespace to align results in tabular format-F $'\t' uses the tab character to separate results rather than the default | characterpsql REPLToggle pager off/on:
\pset pager
Connect to a different database:
\connect database
Show how long each query you submit takes (shows up after the query results as something like “Time: 1.667 ms”):
\timing on
Don’t show column headers.
\t off
Disable the record view format
(forcing psql to format all results as tabular data with columns):
\x off
Print in column format, but wrap contents within their cells, in order to fit the whole table inside the terminal width:
\pset format wrapped
Set a variable to use later:
\set query '''chris b'''
SELECT * FROM users WHERE name % :query;