Postgres

Ref:

At Scale:

  • Citus: scale out postgres
  • Timescale: timeseries database
  • Stolon: cloud native PostgreSQL manager for HA

Configuration

Ref:

Cheatsheets

SHOW all;
SHOW config_file;

SET configuration_parameter TO DEFAULT;

SELECT * FROM pg_settings;
UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

SELECT pg_reload_conf();    -- Reload config

Slow Query

Ref:

Check if query are waiting for another query to complete. Need to tuning up the query.

SELECT * FROM pg_stat_activity WHERE waiting = TRUE;           -- PostgreSQL 9.5 and earlier
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL;   -- PostgreSQL 9.6
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL AND backend_type = 'client backend';   -- PostgreSQL 10 and later 

Check ratio of index scan vs seq scan

SELECT 
	relname,
	seq_tup_read,
	idx_tup_fetch,
	cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct 
FROM pg_stat_user_tables 
WHERE (idx_tup_fetch + seq_tup_read)>0 ORDER BY idx_tup_pct;

Extensions

List:

  • pg_stat_statements: tracking execution statistics of all SQL statements executed by a server
  • pg_repack: remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes

Cheatsheet

SELECT * FROM pg_extension                        -- list of installed extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";       -- create
DELETE EXTENSION IF EXISTS "uuid-ossp";           -- drop

WAL

Ref:

Check if database bloated (live tupple > dead tupple) in master database. Need manual VACCUM or aggresive AUTOVACUUM

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
FROM pg_stat_user_tables 
WHERE n_live_tup < n_dead_tup;

Indexing

Ref:

Check unused index

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Multicolumn indexing (ref_1 ref_2)

-- create indexing example
CREATE INDEX index_name ON table_name(a,b,c,...);

-- optimizer WILL consider using the index
SELECT * FROM table_name WHERE a = v1 and b = v2 and c = v3;
SELECT * FROM table_name WHERE a = v1 and b = v2;
SELECT * FROM table_name WHERE a = v1;

-- optimizer WILL NOT consider using the index
SELECT * FROM table_name WHERE c = v3;
SELECT * FROM table_name WHERE b = v2 and c = v3;
SELECT * FROM table_name WHERE b = v2 and a = v1;
SELECT * FROM table_name WHERE a = v1 or b = v2 or c = v3;

Saving Space

Table size

SELECT 
   relname AS "table_name", 
   pg_size_pretty(pg_table_size(C.oid)) AS "table_size" 
FROM pg_class C 
   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
   AND nspname !~ '^pg_toast' 
   AND relkind IN ('r')
ORDER BY pg_table_size(C.oid) DESC;

Replication

Ref:

-- Check replication status
SELECT client_addr, state, sent_location, write_location, flush_location, replay_location
FROM pg_stat_replication;

-- Check replication delay
select now() - pg_last_xact_replay_timestamp() AS replication_delay;

SELECT * FROM pg_stat_replication;

Backup

# dump data only as insert statement
pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

# backup specific table
psql -h <host1> ... -c 'copy (select ... from <tablename> where ...)
to stdout' | psql -h <host2> ...  -c 'copy <tablename> from stdin'

Connections

Percentage of max connections in use

SELECT (SELECT SUM(numbackends) FROM pg_stat_database) / (SELECT setting::float FROM pg_settings WHERE name = 'max_connections')