postgres internals for djangoconf 2024

Postgres Internals
Djangocon US 2024
Elizabeth Christensen

## psql basics

--whoami
\conninfo

--user list
\du

--formatting
\x auto

## exploring postgres with psql

--find my postgres settings
select \* from pg_settings;

--list database
\l

--change database
\c

--show tables
\dt+

--describe table
\d+ <table-name>

## What is happening in your database

--what is running now, querying pg_stat_activity
SELECT
now() - query_start AS duration, query_start, pid, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
AND backend_type = 'client backend'
ORDER BY 1 DESC LIMIT 3;

--who/what are running processes
SELECT usename, state,
COUNT(\*) AS count
FROM pg_stat_activity
WHERE state <> 'idle'
GROUP BY usename, state;

--transaction volume
SELECT datname, xact_commit, xact_rollback
FROM pg_stat_database;

-current locks
SELECT locktype, relation::regclass, pid, mode, waitstart
FROM pg_locks
WHERE NOT granted;

--list of process views
\dv pg_stat_progress*

--most recent table vacuums
SELECT relname, last_vacuum, last_autovacuum
FROM pg_stat_user_tables;

## Cumulative Stats

--slowest queries
SELECT (total_exec_time / 1000 / 60) as total_min, mean_exec_time as avg_ms, calls, query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 10;

--cache hit ratio
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;

--does this table need an index?
SELECT relname, seq_scan - idx_scan AS too_much_seq,
CASE WHEN seq_scan - idx_scan > 0 THEN 'Missing Index?'
ELSE 'OK'
END, pg_relation_size (relid) AS rel_size, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE schemaname <> 'information_schema'
AND schemaname NOT LIKE 'pg%'
ORDER BY too_much_seq DESC;

--index usage
SELECT schemaname,relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
ORDER BY 4 DESC;

--reset your stats
pg_stat_reset();

## postgres stuff at

https://www.crunchydata.com/postgres-tips