Postgres Tips & Tricks

 Copy
SELECT
		pid,
		datname,
		usename,
		application_name,
		client_addr,
		client_port,
		to_char (now (), 'YYYY-MM-DD HH24:MI:SS') as now,
		to_char (now () - xact_start, 'DD HH24:MI:SS MS') as xact_time,
		to_char (now () - query_start, 'DD HH24:MI:SS MS') as query_time,
		state,
		to_char (now () - state_change, 'DD HH24:MI:SS MS') as state_time,
		wait_event,
		wait_event_type,
		left (query, 40)
	  FROM
		pg_stat_activity
	  WHERE
		state != 'idle'
		and pid != pg_backend_pid ()
	  ORDER BY
		query_time desc;

 Copy
WITH sos AS (
			SELECT array_cat(array_agg(pid),
				   array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
			FROM pg_locks
			WHERE NOT granted
		)
		SELECT a.pid, a.usename, a.datname, a.state,
			   a.wait_event_type || ': ' || a.wait_event AS wait_event,
			   current_timestamp-a.state_change time_in_state,
			   current_timestamp-a.xact_start time_in_xact,
			   l.relation::regclass relname,
			   l.locktype, l.mode, l.page, l.tuple,
			   pg_blocking_pids(l.pid) blocking_pids,
			   (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
			   coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),a.pid||'.0') lock_depth,
			   a.query
		FROM pg_stat_activity a
			 JOIN sos s on (a.pid = any(s.pids))
			 LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
		ORDER BY lock_depth;

 Copy
ALTER SYSTEM SET lock_timeout = '10s';

 Copy
ALTER database postgres SET log_min_duration_statement = '250ms';

 Copy
ALTER DATABASE postgres SET log_statement = 'all';

Valid values include all, ddl, none, mod

 Copy
ALTER DATABASE postgres SET log_lock_waits = 'on';

 Copy
ALTER DATABASE mydatabase SET statement_timeout = '60s';

 Copy
SELECT
	total_exec_time,
	mean_exec_time as avg_ms,
	calls,
	query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

 Copy
SELECT count(*),
	   state
FROM pg_stat_activity
GROUP BY state;

If you see idle connections is above 20, it is recommended to explore using PgBouncer.

 Copy
SELECT pg_relation_size('table_name');

-- For prettier formatting you can wrap with:

SELECT pg_size_pretty(pg_relation_size('table_name'));

 Copy
SELECT relname AS relation,
       pg_size_pretty (
         pg_total_relation_size (C .oid)
       ) AS total_size
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

 Copy
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;

 Copy
SELECT reltuples::numeric as count
FROM pg_class
WHERE relname='table_name';

 Copy
CREATE INDEX CONCURRENTLY foobar ON foo (bar);

 Copy
\timing

You can save this in your `.psqlrc` to be a default setting

 Copy
\x auto

You can save this in your `.psqlrc` to be a default setting

 Copy
\e

 Copy
\pset null 👻

You can save this in your `.psqlrc` to be a default setting

 Copy
\set HISTFILE ~/.psql_history- :DBNAME

You can save this in your `.psqlrc` to be a default setting

 Copy
psql -E

 Copy
psql -qtA

 Copy
psql -qtH

 Copy
(reverse-i-search)

 Copy
\! clear

 Copy
\watch

 Copy
\dconfig

 Copy
\set ON_ERROR_ROLLBACK interactive

 Copy
psql <connection-string> --csv -c 'select * from test;'

 Copy
\i filename

 Copy
\pset border 2

You can save this in your `.psqlrc` to be a default setting

 Copy
\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > interval ''5 minutes'' ORDER by 2 DESC;'
\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''table hit rate'' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'
\set 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;'
\set table_sizes 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (''pg_catalog'', ''information_schema'') AND n.nspname !~ ''^pg_toast'' AND c.relkind=''r'' ORDER BY pg_table_size(c.oid) DESC;'

 Copy
\pset linestyle unicode

You can save this in your `.psqlrc` to be a default setting

 Copy
SELECT starelid::regclass AS table_name,attname AS column_name, 
(SELECT string_agg('',format(E''%s': %s%%
', v,ROUND(n::numeric*100, 2)))
FROM unnest(stanumbers1,stavalues1::text::text[])nvs(n,v)) pcts
FROM pg_statistic
JOIN pg_attribute ON attrelid=starelid
AND attnum = staattnum
JOIN pg_class ON attrelid = pg_class.oid
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema'
\x\g\x

 Copy
SELECT id, 
       coalesce(ip, 'no IP') 
FROM logs;

You can supply two columns as well prior to your replacement value and the function will use first not null value.

 Copy
IMPORT FOREIGN SCHEMA "public";

You can IMPORT FOREIGN SCHEMA when mapping a foreign data wrapper to save you from building a new one

 Copy
SELECT * FROM
generate_series(now() - '3 month'::interval, now(), '1 day');

 Copy
SELECT date_trunc('day', now());

 Copy
SELECT now() - '1 month'::interval;

 Copy
select pg_sleep(2.5);

 Copy
pg_dump -t my_table > test.sql

 Copy
SELECT
	seqs.relname AS sequence,
	format_type(s.seqtypid, NULL) sequence_datatype,
CONCAT(tbls.relname, '.', attrs.attname) AS owned_by,
	format_type(attrs.atttypid, atttypmod) AS column_datatype,
	pg_sequence_last_value(seqs.oid::regclass) AS last_sequence_value,
TO_CHAR((
	CASE WHEN format_type(s.seqtypid, NULL) = 'smallint' THEN
		(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)
	WHEN format_type(s.seqtypid, NULL) = 'integer' THEN
		(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)
	WHEN format_type(s.seqtypid, NULL) = 'bigint' THEN
		(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)
	END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,
TO_CHAR((
	CASE WHEN format_type(attrs.atttypid, NULL) = 'smallint' THEN
		(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)
	WHEN format_type(attrs.atttypid, NULL) = 'integer' THEN
		(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)
	WHEN format_type(attrs.atttypid, NULL) = 'bigint' THEN
		(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)
	END) * 100, 'fm9999999999999999999990D00%') AS column_percent
FROM
	pg_depend d
	JOIN pg_class AS seqs ON seqs.relkind = 'S'
		AND seqs.oid = d.objid
	JOIN pg_class AS tbls ON tbls.relkind = 'r'
		AND tbls.oid = d.refobjid
	JOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjid
		AND attrs.attnum = d.refobjsubid
	JOIN pg_sequence s ON s.seqrelid = seqs.oid
WHERE
	d.deptype = 'a'
	AND d.classid = 1259;

 Copy
psql -f test.sql

 Copy
GRANT pg_read_all_data TO username;

 Copy
SELECT
		c.relname AS source_table_name,
		c.relpages AS source_table_number_of_pages,
		c.reltuples AS source_table_number_of_tuples,
		c.reltoastrelid AS toast_table_oid,
		t.relname AS toast_table_name,
		t.relpages AS toast_table_number_of_pages,
		t.reltuples AS toast_table_number_of_tuples
	 FROM
		pg_class c
		JOIN pg_class t ON c.reltoastrelid = t.oid
	 WHERE
		t.relpages > 0;

 Copy
SELECT usename, state, count(1) cnt
FROM pg_stat_activity
WHERE usename is not null
GROUP BY usename, state
ORDER BY usename, state;

 Copy
WITH pids AS (
  SELECT pid
  FROM pg_stat_activity
  WHERE usename='test'
)

SELECT pg_terminate_backend(pid)
FROM pids;

 Copy
WITH pids AS (
  SELECT pid 
  FROM pg_stat_activity 
  WHERE username='test'
)

SELECT pg_cancel_backend(pid)
FROM pids;