postgres-extras-clj example with pgbench

postgres-extras-clj is a Clojure toolbox for inspecting and diagnosing PostgreSQL databases.

• Clojars
• Github
• Documentation
• CI tests

In this notebook, we’ll cover the majority of the high-level API, demonstrating some useful fns based on the postgres system catalogs.

This notebook is written in a Clojure namespace and converted to HTML using Clay.

(ns pgbench-tutorial
  "This is a demonstration of postgres-extras-clj using the next.jdbc adapter
  to access a pgbench database."
  (:require
   [hugsql.adapter.next-jdbc :as next-adapter]
   [hugsql.core :as hugsql]
   [next.jdbc :as jdbc]
   [postgres-extras-clj.core :as pgex]
   [scicloj.kindly.v4.kind :as kind]))

Utility functions

(defn show
  "Given a sequence of maps (records),
  Kindly show me a table with some nice UI elements."
  [f]
  (if (empty? f)
    '()
    (let [ncol (count (first f))
          nrow (count f)]
      (kind/table f
                  {:use-datatables true
                   :datatables     {:paging     (< 12 nrow)
                                    :scrollY    false
                                    :scrollX    (< 6 ncol)
                                    :pageLength 12}}))))

(defn show-public
  "Given a sequence of maps (records),
  filter for non-system objects, then show it."
  [f]
  (show
   (filter #(not (:system_object %)) f)))

Database benchmark

We’ll use pgbench to get a database populated with interesting tables:

Pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second) . By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction.

To initialize

 PGPASSWORD=password pgbench --host localhost --port 5432 --username postgres -s 10 -F 100 -i -I dtgvpf main

Then run the benchmarks

 PGPASSWORD=password pgbench --host localhost --port 5432 --username postgres --client 32 --transactions 1000 --jobs 8 main

Driver Setup

We define a JDBC datasource from a map

(def db
  (jdbc/get-datasource
   {:dbtype "postgresql" :dbname "main" :host "localhost" :port 5432 :user "postgres" :password "password"}))

or from a JDBC URI.

(def db2
  (jdbc/get-datasource
   "jdbc:postgresql://localhost:5432/main?user=postgres&password=password"))

Independently, we need to tell hugsql to expect next-jdbc.

(hugsql/set-adapter! (next-adapter/hugsql-adapter-next-jdbc))

#object[hugsql.adapter.next_jdbc.HugsqlAdapterNextJdbc 0x237c078c "hugsql.adapter.next_jdbc.HugsqlAdapterNextJdbc@237c078c"]

Do a health check to ensure connectivity

(pgex/health-check db)

{:now #inst "2026-10-26T01:20:31.990603000-00:00",
 :version
 "PostgreSQL 16.9 (Debian 16.9-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit"}

Settings at a glance

These settings are a subset of the total available via postgresql.conf, but these particular settings are worth paying attention to.

How to tune each of these parameters is beyond the scope of this document. There are plenty of guides online, my recommnedation being PGTune. It provides a simple, web-based tool to generate “optimal” settings depending on your hardware and application.

The default settings that ship with postgres are almost never optimal, they are far too conservative and make poor use of modern hardware. Here’s my configuration tuned for test/dev on my laptop.

(show (pgex/db-settings db))

setting	value	unit	short_desc
checkpoint_completion_target	0.75		Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
default_statistics_target	100		Sets the default statistics target.
effective_cache_size	524288	8kB	Sets the planner's assumption about the total size of the data caches.
effective_io_concurrency	1		Number of simultaneous requests that can be handled efficiently by the disk subsystem.
maintenance_work_mem	655360	kB	Sets the maximum memory to be used for maintenance operations.
max_connections	40		Sets the maximum number of concurrent connections.
max_wal_size	1024	MB	Sets the WAL size that triggers a checkpoint.
min_wal_size	80	MB	Sets the minimum size to shrink the WAL to.
random_page_cost	4		Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
shared_buffers	131072	8kB	Sets the number of shared memory buffers used by the server.
wal_buffers	2048	8kB	Sets the number of disk-page buffers in shared memory for WAL.
work_mem	40960	kB	Sets the maximum memory to be used for query workspaces.

example row

{:setting "checkpoint_completion_target",
 :value "0.75",
 :unit nil,
 :short_desc
 "Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval."}

Full map of database objects, the “data dictionary”

(def dd (pgex/read-data-dictionary db))

(keys dd)

(:partition-children
 :columns
 :functions
 :tables
 :databases
 :schemas
 :partition-parents
 :indexes
 :views)

Or one object, one item at time

List all databases

(show (pgex/databases db))

oid	database	db_size_bytes	db_size_pretty	table_count	size_in_tables_bytes	size_in_tables_pretty	extension_count
19699	main	169038307	161 MB	72	169435136	162 MB	2

example row

{:oid 19699,
 :database "main",
 :db_size_bytes 169038307,
 :db_size_pretty "161 MB",
 :table_count 72,
 :size_in_tables_bytes 169435136M,
 :size_in_tables_pretty "162 MB",
 :extension_count 2}

List all shemas in current database

(show-public (pgex/schemas db))

size_pretty	description	view_count	schema	table_count	function_count	size_plus_indexes_bytes	size_plus_indexes_pretty	system_object	owner	size_bytes
132 MB	standard public schema	2	public	4	3	161259520	154 MB	false	pg_database_owner	138739712

example row

{:size_pretty "132 MB",
 :description "standard public schema",
 :view_count 2,
 :schema "public",
 :table_count 4,
 :function_count 3,
 :size_plus_indexes_bytes 161259520,
 :size_plus_indexes_pretty "154 MB",
 :system_object false,
 :owner "pg_database_owner",
 :size_bytes 138739712}

List all view objects in current database

(show-public (pgex/views db))

size_pretty	description	schema	rows	oid	size_plus_indexes_bytes	size_plus_indexes_pretty	system_object	owner	view_type	view	size_bytes
0 bytes		public	-1	66351	0	0 bytes	false	postgres	view	pg_stat_statements_info	0
0 bytes		public	-1	66362	0	0 bytes	false	postgres	view	pg_stat_statements	0

example row

{:size_pretty "0 bytes",
 :description nil,
 :schema "public",
 :rows -1,
 :oid 66351,
 :size_plus_indexes_bytes 0,
 :size_plus_indexes_pretty "0 bytes",
 :system_object false,
 :owner "postgres",
 :view_type "view",
 :view "pg_stat_statements_info",
 :size_bytes 0}

List all index objects in current database

(show-public (pgex/indexes db))

schema	table	index	total_columns	unique_index	index_size_pretty	rows_indexed	oid	index_size_bytes	partial_index	key_columns	valid_index	system_object	primary_key
public	pgbench_branches	pgbench_branches_pkey	1	true	16 kB	10.0	66383	16384	false	1	true	false	true
public	pgbench_tellers	pgbench_tellers_pkey	1	true	16 kB	100.0	66385	16384	false	1	true	false	true
public	pgbench_accounts	pgbench_accounts_pkey	1	true	21 MB	1000000.0	66387	22487040	false	1	true	false	true

example row

{:schema "public",
 :table "pgbench_branches",
 :index "pgbench_branches_pkey",
 :total_columns 1,
 :unique_index true,
 :index_size_pretty "16 kB",
 :rows_indexed 10.0,
 :oid 66383,
 :index_size_bytes 16384,
 :partial_index false,
 :key_columns 1,
 :valid_index true,
 :system_object false,
 :primary_key true}

List all database column objects

(show-public (pgex/columns db))

description	schema	table	generated_column	source_type	column	data_type	position	system_object	default_value
	public	pg_stat_statements_info	false	view	dealloc	int8	1	false
	public	pg_stat_statements_info	false	view	stats_reset	timestamptz	2	false
	public	pg_stat_statements	false	view	userid	oid	1	false
	public	pg_stat_statements	false	view	dbid	oid	2	false
	public	pg_stat_statements	false	view	toplevel	bool	3	false
	public	pg_stat_statements	false	view	queryid	int8	4	false
	public	pg_stat_statements	false	view	query	text	5	false
	public	pg_stat_statements	false	view	plans	int8	6	false
	public	pg_stat_statements	false	view	total_plan_time	float8	7	false
	public	pg_stat_statements	false	view	min_plan_time	float8	8	false
	public	pg_stat_statements	false	view	max_plan_time	float8	9	false
	public	pg_stat_statements	false	view	mean_plan_time	float8	10	false
	public	pg_stat_statements	false	view	stddev_plan_time	float8	11	false
	public	pg_stat_statements	false	view	calls	int8	12	false
	public	pg_stat_statements	false	view	total_exec_time	float8	13	false
	public	pg_stat_statements	false	view	min_exec_time	float8	14	false
	public	pg_stat_statements	false	view	max_exec_time	float8	15	false
	public	pg_stat_statements	false	view	mean_exec_time	float8	16	false
	public	pg_stat_statements	false	view	stddev_exec_time	float8	17	false
	public	pg_stat_statements	false	view	rows	int8	18	false
	public	pg_stat_statements	false	view	shared_blks_hit	int8	19	false
	public	pg_stat_statements	false	view	shared_blks_read	int8	20	false
	public	pg_stat_statements	false	view	shared_blks_dirtied	int8	21	false
	public	pg_stat_statements	false	view	shared_blks_written	int8	22	false
	public	pg_stat_statements	false	view	local_blks_hit	int8	23	false
	public	pg_stat_statements	false	view	local_blks_read	int8	24	false
	public	pg_stat_statements	false	view	local_blks_dirtied	int8	25	false
	public	pg_stat_statements	false	view	local_blks_written	int8	26	false
	public	pg_stat_statements	false	view	temp_blks_read	int8	27	false
	public	pg_stat_statements	false	view	temp_blks_written	int8	28	false
	public	pg_stat_statements	false	view	blk_read_time	float8	29	false
	public	pg_stat_statements	false	view	blk_write_time	float8	30	false
	public	pg_stat_statements	false	view	temp_blk_read_time	float8	31	false
	public	pg_stat_statements	false	view	temp_blk_write_time	float8	32	false
	public	pg_stat_statements	false	view	wal_records	int8	33	false
	public	pg_stat_statements	false	view	wal_fpi	int8	34	false
	public	pg_stat_statements	false	view	wal_bytes	numeric	35	false
	public	pg_stat_statements	false	view	jit_functions	int8	36	false
	public	pg_stat_statements	false	view	jit_generation_time	float8	37	false
	public	pg_stat_statements	false	view	jit_inlining_count	int8	38	false
	public	pg_stat_statements	false	view	jit_inlining_time	float8	39	false
	public	pg_stat_statements	false	view	jit_optimization_count	int8	40	false
	public	pg_stat_statements	false	view	jit_optimization_time	float8	41	false
	public	pg_stat_statements	false	view	jit_emission_count	int8	42	false
	public	pg_stat_statements	false	view	jit_emission_time	float8	43	false
	public	pgbench_history	false	table	tid	int4	1	false
	public	pgbench_history	false	table	bid	int4	2	false
	public	pgbench_history	false	table	aid	int4	3	false
	public	pgbench_history	false	table	delta	int4	4	false
	public	pgbench_history	false	table	mtime	timestamp	5	false
	public	pgbench_history	false	table	filler	bpchar	6	false
	public	pgbench_tellers	false	table	tid	int4	1	false
	public	pgbench_tellers	false	table	bid	int4	2	false
	public	pgbench_tellers	false	table	tbalance	int4	3	false
	public	pgbench_tellers	false	table	filler	bpchar	4	false
	public	pgbench_accounts	false	table	aid	int4	1	false
	public	pgbench_accounts	false	table	bid	int4	2	false
	public	pgbench_accounts	false	table	abalance	int4	3	false
	public	pgbench_accounts	false	table	filler	bpchar	4	false
	public	pgbench_branches	false	table	bid	int4	1	false
	public	pgbench_branches	false	table	bbalance	int4	2	false
	public	pgbench_branches	false	table	filler	bpchar	3	false

example row

{:description nil,
 :schema "public",
 :table "pg_stat_statements_info",
 :generated_column false,
 :source_type "view",
 :column "dealloc",
 :data_type "int8",
 :position 1,
 :system_object false,
 :default_value nil}

List all table objects in current database

(show-public (pgex/tables db))

size_pretty	description	schema	table	rows	oid	data_type	size_plus_indexes_bytes	size_plus_indexes_pretty	system_object	owner	size_bytes	bytes_per_row
192 kB		public	pgbench_branches	10	66376	table	212992	208 kB	false	postgres	196608	19661
216 kB		public	pgbench_tellers	100	66370	table	237568	232 kB	false	postgres	221184	2212
130 MB		public	pgbench_accounts	1000000	66373	table	158932992	152 MB	false	postgres	136445952	136
1832 kB		public	pgbench_history	-1	66367	table	1875968	1832 kB	false	postgres	1875968

example row

{:size_pretty "192 kB",
 :description nil,
 :schema "public",
 :table "pgbench_branches",
 :rows 10,
 :oid 66376,
 :data_type "table",
 :size_plus_indexes_bytes 212992,
 :size_plus_indexes_pretty "208 kB",
 :system_object false,
 :owner "postgres",
 :size_bytes 196608,
 :bytes_per_row 19661}

List all function objects in current database

(show-public (pgex/functions db))

description	schema	source_code	proc_security	result_data_types	function	access_privileges	argument_data_types	proc_language	system_object	owner
	public	pg_stat_statements_reset_1_7	invoker	void	pg_stat_statements_reset	postgres=X/postgres	userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0	c	false	postgres
	public	pg_stat_statements_info	invoker	record	pg_stat_statements_info		OUT dealloc bigint, OUT stats_reset timestamp with time zone	c	false	postgres
	public	pg_stat_statements_1_10	invoker	SETOF record	pg_stat_statements		showtext boolean, OUT userid oid, OUT dbid oid, OUT toplevel boolean, OUT queryid bigint, OUT query text, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision, OUT temp_blk_read_time double precision, OUT temp_blk_write_time double precision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision	c	false	postgres

example row

{:description nil,
 :schema "public",
 :source_code "pg_stat_statements_reset_1_7",
 :proc_security "invoker",
 :result_data_types "void",
 :function "pg_stat_statements_reset",
 :access_privileges "postgres=X/postgres",
 :argument_data_types
 "userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0",
 :proc_language "c",
 :system_object false,
 :owner "postgres"}

Full map of diagnostic statistics

(def stats (pgex/read-stats db {:limit 100}))

(keys stats)

(:duplicate-indexes
 :db-settings
 :locks
 :vacuum-stats
 :index-usage
 :total-index-size
 :cache-hit
 :records-rank
 :null-indexes
 :index-cache-hit
 :all-locks
 :outliers
 :long-running-queries
 :extensions
 :total-table-size
 :unused-indexes
 :bloat
 :calls
 :table-size
 :connections
 :table-cache-hit
 :table-indexes-size
 :blocking
 :seq-scans
 :index-size)

Dead rows and whether an automatic vacuum is expected to be triggered

(show (pgex/vacuum-stats db))

schema	table	last_vacuum	last_autovacuum	rows	dead_rows	autovacuum_threshold	expect_autovacuum
public	pgbench_branches	2026-10-25 19:20		10.0	1344	50.2	yes
public	pgbench_tellers	2026-10-25 19:20		100.0	2068	52.0	yes
public	pgbench_accounts	2026-10-25 19:20		1000000.0	24048	20050.0	yes
public	pgbench_history	2026-10-25 19:20		-1.0	0	49.98

example row

{:schema "public",
 :table "pgbench_branches",
 :last_vacuum "2026-10-25 19:20",
 :last_autovacuum nil,
 :rows 10.0,
 :dead_rows 1344,
 :autovacuum_threshold 50.2,
 :expect_autovacuum "yes"}

Index hit rate (effective databases are at 99% and up)

(show (pgex/index-usage db))

schema	table	index_usage_ratio	rows
public	pgbench_accounts	0.9999583350693722	1000000
public	pgbench_history		32000
public	pgbench_tellers	0.9974375800756227	100
public	pgbench_branches	0.4845644294463192	10

example row

{:schema "public",
 :table "pgbench_accounts",
 :index_usage_ratio 0.9999583350693722,
 :rows 1000000}

Total size of all indexes in MB

(pgex/total-index-size db)

[{:size 22519808}]

example row

{:size 22519808}

Index and table hit rate

(show (pgex/cache-hit db))

metric	ratio
index hit rate	0.9935828152975972
table hit rate	0.9790910081743869

example row

{:metric "index hit rate", :ratio 0.9935828152975972}

All tables and the number of rows in each ordered by number of rows descending

(show (pgex/records-rank db))

schema	table	rows
public	pgbench_accounts	1000000
public	pgbench_history	32000
public	pgbench_tellers	100
public	pgbench_branches	10

example row

{:schema "public", :table "pgbench_accounts", :rows 1000000}

Calculates your cache hit rate for reading indexes

(show (pgex/index-cache-hit db))

schema	index	buffer_hits	block_reads	total_read	ratio
public	pgbench_history	0	0	0	0.0
public	pgbench_tellers	64438	2	64440	0.9999689633767846
public	pgbench_branches	31430	2	31432	0.9999363705777552
public	pgbench_accounts	329764	2745	332509	0.9917445843571151

example row

{:schema "public",
 :index "pgbench_history",
 :buffer_hits 0,
 :block_reads 0,
 :total_read 0,
 :ratio 0.0}

Queries that have longest execution time in aggregate.

Dispatches to a version-appropriate query because PostgreSQL 17 renamed pg_stat_statements.blk_read_time/blk_write_time into shared/local/temp read/write time columns.

(show (pgex/outliers db {:limit 10}))

query	exec_time_ms	prop_exec_time	calls	sync_io_time_ms
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2	49473.195	0.690894794065738	32000	0.000
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2	36610.332	0.29087308131532197	32000	0.000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)	2424.626	0.007300031640973953	32000	0.000
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2	1168.237	0.0035173112436317922	32000	0.000
copy pgbench_accounts from stdin with (freeze on)	506.178	0.0015239925068128216	1	0.000
COPY adsb_message (icao24, callsign, altitude_ft, speed_kt, heading_deg, vertical_rate_fpm, latitude, longitude, rssi_db, on_ground, emergency, wake_vortex, pressure_setting_mbar, selected_heading_deg) FROM STDIN (FORMAT binary)	382.752	0.0011523847032685096	833	0.000
COPY adsb_message (icao24, timestamp, callsign, altitude_ft, speed_kt, heading_deg, vertical_rate_fpm, latitude, longitude, rssi_db, on_ground, emergency, wake_vortex, pressure_setting_mbar, selected_heading_deg) FROM STDIN (FORMAT binary)	311.324	9.373307548520495E-4	630	0.000
SELECT abalance FROM pgbench_accounts WHERE aid = $1	265.866	8.00466901411818E-4	32000	0.000
alter table pgbench_accounts add primary key (aid)	161.279	4.8557520983859477E-4	1	0.000
alter table pgbench_accounts add constraint pgbench_accounts_bid_fkey foreign key (bid) references pgbench_branches	88.846	2.674967818775456E-4	1	0.000

example row

{:query
 "UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2",
 :exec_time_ms 49473.195M,
 :prop_exec_time 0.6909331621733533,
 :calls 32000,
 :sync_io_time_ms 0.000M}

Available and installed extensions

(show (pgex/extensions db))

extension	default_version	installed_version	description
plpgsql	1.0	1.0	PL/pgSQL procedural language
pg_stat_statements	1.10	1.10	track planning and execution statistics of all SQL statements executed
insert_username	1.0		functions for tracking who changed a table
xml2	1.1		XPath querying and XSLT
intarray	1.5		functions, operators, and index support for 1-D arrays of integers
uuid-ossp	1.1		generate universally unique identifiers (UUIDs)
dict_int	1.0		text search dictionary template for integers
old_snapshot	1.0		utilities in support of old_snapshot_threshold
lo	1.1		Large Object maintenance
unaccent	1.1		text search dictionary that removes accents
pageinspect	1.12		inspect the contents of database pages at a low level
bloom	1.0		bloom access method - signature file based index
pg_walinspect	1.1		functions to inspect contents of PostgreSQL Write-Ahead Log
adminpack	2.1		administrative functions for PostgreSQL
cube	1.5		data type for multidimensional cubes
tcn	1.0		Triggered change notifications
pg_trgm	1.6		text similarity measurement and index searching based on trigrams
tsm_system_time	1.0		TABLESAMPLE method which accepts time in milliseconds as a limit
hstore	1.8		data type for storing sets of (key, value) pairs
btree_gist	1.7		support for indexing common datatypes in GiST
dict_xsyn	1.0		text search dictionary template for extended synonym processing
tablefunc	1.0		functions that manipulate whole tables, including crosstab
pgrowlocks	1.2		show row-level locking information
amcheck	1.3		functions for verifying relation integrity
isn	1.2		data types for international product numbering standards
btree_gin	1.3		support for indexing common datatypes in GIN
autoinc	1.0		functions for autoincrementing fields
pg_prewarm	1.2		prewarm relation data
fuzzystrmatch	1.2		determine similarities and distance between strings
citext	1.6		data type for case-insensitive character strings
postgres_fdw	1.1		foreign-data wrapper for remote PostgreSQL servers
pgstattuple	1.5		show tuple-level statistics
dblink	1.2		connect to other PostgreSQL databases from within a database
file_fdw	1.0		foreign-data wrapper for flat file access
moddatetime	1.0		functions for tracking last modification time
ltree	1.2		data type for hierarchical tree-like structures
pg_freespacemap	1.2		examine the free space map (FSM)
pg_visibility	1.2		examine the visibility map (VM) and page-level visibility info
pg_surgery	1.0		extension to perform surgery on a damaged relation
pgcrypto	1.3		cryptographic functions
sslinfo	1.2		information about SSL certificates
earthdistance	1.2		calculate great-circle distances on the surface of the Earth
refint	1.0		functions for implementing referential integrity (obsolete)
tsm_system_rows	1.0		TABLESAMPLE method which accepts number of rows as a limit
pg_buffercache	1.4		examine the shared buffer cache
intagg	1.1		integer aggregator and enumerator (obsolete)
seg	1.4		data type for representing line segments or floating-point intervals

example row

{:extension "plpgsql",
 :default_version "1.0",
 :installed_version "1.0",
 :description "PL/pgSQL procedural language"}

Size of the tables (including indexes), descending by size

(show (pgex/total-table-size db))

schema	table	size
public	pgbench_accounts	158932992
public	pgbench_history	1875968
public	pgbench_tellers	237568
public	pgbench_branches	212992

example row

{:schema "public", :table "pgbench_accounts", :size 158932992}

Table and index bloat in your database ordered by most wasteful

(show (pgex/bloat db))

type	schema	object_name	bloat	waste
table	public	pgbench_accounts	1.0	5980160
index	public	pgbench_branches::pgbench_branches_pkey	2.0	8192
index	public	pgbench_tellers::pgbench_tellers_pkey	2.0	8192
table	public	pgbench_branches	1.0	0
table	public	pgbench_tellers	1.0	0
index	public	pgbench_accounts::pgbench_accounts_pkey	0.2	0

example row

{:type "table",
 :schema "public",
 :object_name "pgbench_accounts",
 :bloat 1.0M,
 :waste 5980160}

Queries that have the highest frequency of execution.

Dispatches to a version-appropriate query because PostgreSQL 17 renamed pg_stat_statements.blk_read_time/blk_write_time into shared/local/temp read/write time columns.

(show (pgex/calls db {:limit 10}))

query	exec_time_ms	exec_time_ratio	calls	sync_io_time_ms
commit	11.697	3.521543540271427E-5	32001	0.000
begin	11.543	3.475103532381449E-5	32001	0.000
SELECT abalance FROM pgbench_accounts WHERE aid = $1	265.866	8.004432245336049E-4	32000	0.000
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2	49473.195	0.6908743582027831	32000	0.000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)	2424.626	0.007299815714544254	32000	0.000
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2	36610.332	0.2908644776285117	32000	0.000
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2	1168.237	0.003517207205663694	32000	0.000
START TRANSACTION	2.661	8.012453375541591E-6	1466	0.000
COMMIT	0.746	2.2464589852287076E-6	1464	0.000
COPY adsb_message (icao24, callsign, altitude_ft, speed_kt, heading_deg, vertical_rate_fpm, latitude, longitude, rssi_db, on_ground, emergency, wake_vortex, pressure_setting_mbar, selected_heading_deg) FROM STDIN (FORMAT binary)	382.752	0.0011523506170717842	833	0.000

example row

{:query "commit",
 :exec_time_ms 11.697M,
 :exec_time_ratio 3.521675353629051E-5,
 :calls 32001,
 :sync_io_time_ms 0.000M}

Size of the tables (excluding indexes), descending by size

(show (pgex/table-size db))

schema	table	size
public	pgbench_accounts	136445952
public	pgbench_history	1875968
public	pgbench_tellers	221184
public	pgbench_branches	196608

example row

{:schema "public", :table "pgbench_accounts", :size 136445952}

Returns the list of all active database connections

(show (pgex/connections db))

username	client_address	application_name
postgres	172.18.0.1/32	PostgreSQL JDBC Driver

example row

{:username "postgres",
 :client_address "172.18.0.1/32",
 :application_name "PostgreSQL JDBC Driver"}

Calculates your cache hit rate for reading tables

(show (pgex/table-cache-hit db))

schema	table	buffer_hits	block_reads	total_read	ratio
public	pgbench_branches	648493	0	648493	1.0
public	pgbench_tellers	234270	0	234270	1.0
public	pgbench_history	128629	0	128629	1.0
public	pgbench_accounts	336082	28776	364858	0.9211309605380723

example row

{:schema "public",
 :table "pgbench_branches",
 :buffer_hits 648493,
 :block_reads 0,
 :total_read 648493,
 :ratio 1.0}

Total size of all the indexes on each table, descending by size

(show (pgex/table-indexes-size db))

schema	table	index_size
public	pgbench_accounts	22487040
public	pgbench_branches	16384
public	pgbench_tellers	16384
public	pgbench_history	0

example row

{:schema "public", :table "pgbench_accounts", :index_size 22487040}

Count of sequential scans by table descending by order

(show (pgex/seq-scans db))

schema	table	count
public	pgbench_branches	32992
public	pgbench_tellers	164
public	pgbench_accounts	4
public	pgbench_history	4

example row

{:schema "public", :table "pgbench_branches", :count 32992}

The size of indexes, descending by size

(show (pgex/index-size db))

schema	index	size
public	pgbench_accounts_pkey	22487040
public	pgbench_tellers_pkey	16384
public	pgbench_branches_pkey	16384

example row

{:schema "public", :index "pgbench_accounts_pkey", :size 22487040}

The Kill Switch

The only mutating function in the entire library is a kill switch for closing all connections, vital if you need to take a heavily-used database down for maintenance or emergency.

(pgex/kill-all! db)  ;; use with caution

Diagnostics

Query and and print all default diagnostics

(for [d (pgex/diagnose (pgex/read-stats db))]
  (:message d))

("✓  Passed :bloat public.pgbench_accounts"
 "✓  Passed :bloat public.pgbench_branches::pgbench_branches_pkey"
 "✓  Passed :bloat public.pgbench_tellers::pgbench_tellers_pkey"
 "✓  Passed :bloat public.pgbench_branches"
 "✓  Passed :bloat public.pgbench_tellers"
 "✓  Passed :bloat public.pgbench_accounts::pgbench_accounts_pkey"
 "✓  Passed :index-cache-hit public.pgbench_history"
 "✓  Passed :index-cache-hit public.pgbench_tellers"
 "✓  Passed :index-cache-hit public.pgbench_branches"
 "✓  Passed :index-cache-hit public.pgbench_accounts"
 "! Warning :outliers UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 Detected slow query\n{:query \"UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2\", :exec_time_ms 49473.195M, :prop_exec_time 0.6908607150115953, :calls 32000, :sync_io_time_ms 0.000M}"
 "✓  Passed :outliers UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2"
 "✓  Passed :outliers INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)"
 "✓  Passed :outliers UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2"
 "✓  Passed :outliers copy pgbench_accounts from stdin with (freeze on)"
 "✓  Passed :outliers COPY adsb_message (icao24, callsign, altitude_ft, speed_kt, heading_deg, vertical_rate_fpm, latitude, longitude, rssi_db, on_ground, emergency, wake_vortex, pressure_setting_mbar, selected_heading_deg"
 "✓  Passed :outliers COPY adsb_message (icao24, timestamp, callsign, altitude_ft, speed_kt, heading_deg, vertical_rate_fpm, latitude, longitude, rssi_db, on_ground, emergency, wake_vortex, pressure_setting_mbar, selected_"
 "✓  Passed :outliers SELECT abalance FROM pgbench_accounts WHERE aid = $1"
 "✓  Passed :outliers alter table pgbench_accounts add primary key (aid)"
 "✓  Passed :outliers alter table pgbench_accounts add constraint pgbench_accounts_bid_fkey foreign key (bid) references pgbench_branches"
 "✓  Passed :table-cache-hit public.pgbench_branches"
 "✓  Passed :table-cache-hit public.pgbench_tellers"
 "✓  Passed :table-cache-hit public.pgbench_history"
 "! Warning :table-cache-hit public.pgbench_accounts Table sees too much block IO relative to buffer cache hit\n{:schema \"public\", :table \"pgbench_accounts\", :buffer_hits 336082, :block_reads 28776, :total_read 364858, :ratio 0.9211309605380723}")

Or just the warnings

(for [w (pgex/diagnose-warnings (pgex/read-stats db))]
  (:message w))

("! Warning :outliers UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 Detected slow query\n{:query \"UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2\", :exec_time_ms 49473.195M, :prop_exec_time 0.6908266095854058, :calls 32000, :sync_io_time_ms 0.000M}"
 "! Warning :table-cache-hit public.pgbench_accounts Table sees too much block IO relative to buffer cache hit\n{:schema \"public\", :table \"pgbench_accounts\", :buffer_hits 336082, :block_reads 28776, :total_read 364858, :ratio 0.9211309605380723}")

Create your own diagnostics

(def unrealistic-expectations
  {:table-cache-hit
   {:pred    #(> (:ratio %) 0.999)
    :onfalse "The cache hit ratio is not as insanely high as I'd like."
    :idfn    :table}})

(for [w (pgex/diagnose-warnings
         (pgex/read-stats db)
         :diagnostic-fns unrealistic-expectations)]
  (:message w))

("! Warning :table-cache-hit pgbench_accounts The cache hit ratio is not as insanely high as I'd like.\n{:schema \"public\", :table \"pgbench_accounts\", :buffer_hits 336082, :block_reads 28776, :total_read 364858, :ratio 0.9211309605380723}")

Just for fun

(for [m (pgex/mandelbrot db)]
  (:art m))

("             ....................................................................................     "
 "            .......................................................................................   "
 "           .........................................................................................  "
 "          ........................................................................................... "
 "        ....................................................,,,,,,,,,................................."
 "       ................................................,,,,,,,,,,,,,,,,,,............................."
 "      ..............................................,,,,,,,,,,,,,,,,,,,,,,,,.........................."
 "     ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................"
 "     ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,......................"
 "    .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................."
 "   ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,..................."
 "  .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................."
 " .......................................,,,,,,,,,,,,,,,,,,,,,,,,--,,,,,,,,,,,,,,,,,,,,................"
 "......................................,,,,,,,,,,,,,,,,,,,,,,,,,,-+--,,,,,,,,,,,,,,,,,,,..............."
 "....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............."
 "...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,--- -----,,,,,,,,,,,,,,,,,............."
 ".................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++--++,,,,,,,,,,,,,,,,,,............"
 "................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%++---,,,,,,,,,,,,,,,,,............"
 "..............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,..........."
 ".............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----- %%+----,,,,,,,,,,,,,,,,,,.........."
 "...........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---%-+%   ----,,,,,,,,,,,,,,,,,,,........."
 "..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+ +##  %+%---,,,,,,,,,,,,,,,,,,........."
 "........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----#      # +---,,,,,,,,,,,,,,,,,,........"
 ".......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------%       %-----,,,,,,,,,,,,,,,,,........"
 ".....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---------+         ------,,,,,,,,,,,,,,,,,......."
 "....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+@       +-----------,,,,,,,,,,,,......."
 "..................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----@-------++       ++-----------,,,,,,,,,,,,......"
 ".................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--+@% ---+ +@%%@     %%+@+@%------+-,,,,,,,,,,,......"
 "................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----  # ++%               % @-----++--,,,,,,,,,,,....."
 "..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+    %                  %%++ %+%@-,,,,,,,,,,,....."
 ".............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+#                       #%    ++-,,,,,,,,,,,,...."
 "............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+                             @---,,,,,,,,,,,,...."
 "..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------++%                             ---,,,,,,,,,,,,...."
 ".........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+ +                             %+---,,,,,,,,,,,,,..."
 "........,,,,,,,,,,,,,,,,,,,,,--------------------@                                +----,,,,,,,,,,,,..."
 ".......,,,,,,,,,,,,,,,,,,,,,,- +-----------------+                                 ----,,,,,,,,,,,,..."
 ".......,,,,,,,,,,,,,,,,,,,,,--++------+---------+%                                 +++--,,,,,,,,,,,,.."
 "......,,,,,,,,,,,,,,,,,,,,,,--%+-----++---------                                     #+-,,,,,,,,,,,,.."
 ".....,,,,,,,,,,,,,,,,,,,,,,----#%++--+@ -+-----+%                                     --,,,,,,,,,,,,.."
 ".....,,,,,,,,,,,,,,,,,,,,,,-----+## ++@ + +----%                                    +--,,,,,,,,,,,,,.."
 "....,,,,,,,,,,,,,,,,,,,,,,------+@  @     @@++++#                                   +--,,,,,,,,,,,,,.."
 "....,,,,,,,,,,,,,,,,,,,,,-------%           #++%                                      -,,,,,,,,,,,,,.."
 "...,,,,,,,,,,,,,,,,,,,,,------++%#           %%@                                     %-,,,,,,,,,,,,,,."
 "...,,,,,,,,,,,,,,,,,,,--------+               %                                     +--,,,,,,,,,,,,,,."
 "...,,,,,,,,,,,,,,,,,,-----+--++@              #                                      --,,,,,,,,,,,,,,."
 "..,,,,,,,,,,,,,,,,,-------%+++%                                                    @--,,,,,,,,,,,,,,,."
 "..,,,,,,,,,,,-------------+ @#@                                                    ---,,,,,,,,,,,,,,,."
 "..,,,,,,,,,---@--------@-+%                                                       +---,,,,,,,,,,,,,,,."
 "..,,,,,------- +-++++-+%%%                                                       +----,,,,,,,,,,,,,,,."
 "..,,,,,,------%--------++%                                                       +----,,,,,,,,,,,,,,,."
 "..,,,,,,,,,,--+----------++#                                                       ---,,,,,,,,,,,,,,,."
 "..,,,,,,,,,,,,------------+@@@%                                                    +--,,,,,,,,,,,,,,,."
 "..,,,,,,,,,,,,,,,,,------- +++%                                                    %--,,,,,,,,,,,,,,,."
 "...,,,,,,,,,,,,,,,,,,---------+@              @                                      --,,,,,,,,,,,,,,."
 "...,,,,,,,,,,,,,,,,,,,,------- #              %@                                    +--,,,,,,,,,,,,,,."
 "...,,,,,,,,,,,,,,,,,,,,,-------++@           %+                                      %-,,,,,,,,,,,,,,."
 "....,,,,,,,,,,,,,,,,,,,,,-------            %++%                                     %-,,,,,,,,,,,,,.."
 "....,,,,,,,,,,,,,,,,,,,,,,------+#  %#   #@ ++++                                    +--,,,,,,,,,,,,,.."
 ".....,,,,,,,,,,,,,,,,,,,,,,-----+ %%++% +@+----+                                    +--,,,,,,,,,,,,,.."
 ".....,,,,,,,,,,,,,,,,,,,,,,,---%+++--+#+--------%                                    #--,,,,,,,,,,,,.."
 "......,,,,,,,,,,,,,,,,,,,,,,--++-----%%---------                                    @#--,,,,,,,,,,,,.."
 ".......,,,,,,,,,,,,,,,,,,,,,---------------------+@                                +-++,,,,,,,,,,,,..."
 "........,,,,,,,,,,,,,,,,,,,,,--------------------+                                 ----,,,,,,,,,,,,..."
 ".........,,,,,,,,,,,,,,,,,,,,----,,,-------------                                #+----,,,,,,,,,,,,..."
 "..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ +                              +---,,,,,,,,,,,,,..."
 "...........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+%#                           #---,,,,,,,,,,,,...."
 "............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+#                        @   @---,,,,,,,,,,,,...."
 ".............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+#                        +    @--,,,,,,,,,,,,...."
 "..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%   %+@                 %+-+ +++%-,,,,,,,,,,,....."
 "................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----% %@++              # %  -----++-,,,,,,,,,,,,....."
 ".................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-- ++ ---+ + +%@     %++++++------%-,,,,,,,,,,,......"
 "...................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- -------++       +------------,,,,,,,,,,,,......"
 "....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+%       +--------,,,,,,,,,,,,,,,......."
 "......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+#        -----,,,,,,,,,,,,,,,,,,......."
 ".......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+       #----,,,,,,,,,,,,,,,,,,........"
 ".........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+%      %#---,,,,,,,,,,,,,,,,,,,........"
 "..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%+%@  %+%%--,,,,,,,,,,,,,,,,,,........."
 "............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+-+%  %----,,,,,,,,,,,,,,,,,,.........."
 ".............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%@+---,,,,,,,,,,,,,,,,,,,.........."
 "...............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,..........."
 "................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%+ +--,,,,,,,,,,,,,,,,,............"
 "..................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++----,,,,,,,,,,,,,,,,,............."
 "...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,---@-----,,,,,,,,,,,,,,,,,............."
 ".....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............."
 " .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,--%,,,,,,,,,,,,,,,,,,,,..............."
 " .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................."
 "  ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................."
 "   ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,..................."
 "    .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................."
 "     ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,......................"
 "      ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................"
 "       .............................................,,,,,,,,,,,,,,,,,,,,,,,,.........................."
 "        ................................................,,,,,,,,,,,,,,,,,............................."
 "         .....................................................,,,,...................................."
 "          ........................................................................................... "
 "           .........................................................................................  "
 "            ......................................................................................    "
 "             ....................................................................................     "
 "               .................................................................................      "
 "                ..............................................................................        "
 "                  ...........................................................................         "
 "                   ........................................................................           ")

To recreate this notebook

The contents of this namespace are periodically rendered and checked into main. Why not as part of the test suite? running a database to make a meaningful tutorial, with commentary about the results, is not trivial to automate.

(comment
  (require '[scicloj.clay.v2.api :as clay])
  (clay/browse!)
  (clay/make! {:source-path      "examples/pgbench_tutorial.clj"
               :format           [:quarto :html]
               :base-target-path "target/clay"}))

source: examples/pgbench_tutorial.clj