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)))

(defn meta-as-header [x]
  (kind/md (str "### " x "\n" (:doc (meta x)))))

Setup

Dependencies

{:deps 
{com.layerware/hugsql {:mvn/version "0.5.3"}
com.layerware/hugsql-adapter-next-jdbc {:mvn/version "0.5.3"}
org.scicloj/clay {:mvn/version "2-beta11"}
org.postgresql/postgresql {:mvn/version "42.7.3"}
seancorfield/next.jdbc {:mvn/version "1.2.659"}}}

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 db-from-uri
  (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 0x6f994d46 "hugsql.adapter.next_jdbc.HugsqlAdapterNextJdbc@6f994d46"]

Do a health check to ensure connectivity

(pgex/health-check db)

{:now #inst "2025-06-29T23:59:43.870451000-00:00",
 :version
 "PostgreSQL 16.9 (Debian 16.9-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit"}

(pgex/health-check db-from-uri)

{:now #inst "2025-06-29T23:59:43.878021000-00:00",
 :version
 "PostgreSQL 16.9 (Debian 16.9-1.pgdg110+1) on aarch64-unknown-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))

name	setting	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

{:name "checkpoint_completion_target",
 :setting "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

#’postgres-extras-clj.core/databases

List all databases

(show (pgex/databases db))

oid	db_name	db_size	table_count	size_in_tables	extension_count
16384	main	161 MB	72	162 MB	2

example row

(first (:databases dd))

{:oid 16384,
 :db_name "main",
 :db_size "161 MB",
 :table_count 72,
 :size_in_tables "162 MB",
 :extension_count 2}

#’postgres-extras-clj.core/schemas

List all shemas in current database

(show-public (pgex/schemas db))

size_pretty	description	view_count	size_plus_indexes	table_count	function_count	size_plus_indexes_bytes	s_name	system_object	owner	size_bytes
132 MB	standard public schema	2	154 MB	4	3	161226752	public	false	pg_database_owner	138706944

example row

(first (:schemas dd))

{:size_pretty "248 kB",
 :description nil,
 :view_count 65,
 :size_plus_indexes "248 kB",
 :table_count 4,
 :function_count 11,
 :size_plus_indexes_bytes 253952,
 :s_name "information_schema",
 :system_object true,
 :owner "postgres",
 :size_bytes 253952}

#’postgres-extras-clj.core/views

List all view objects in current database

(show-public (pgex/views db))

size_pretty	description	owned_by	size_plus_indexes	rows	oid	v_name	size_plus_indexes_bytes	s_name	system_object	view_type	size_bytes
0 bytes		postgres	0 bytes	-1	16488	pg_stat_statements_info	0	public	false	view	0
0 bytes		postgres	0 bytes	-1	16499	pg_stat_statements	0	public	false	view	0

example row

(first (:views dd))

{:size_pretty "0 bytes",
 :description nil,
 :owned_by "postgres",
 :size_plus_indexes "0 bytes",
 :rows -1,
 :oid 16488,
 :v_name "pg_stat_statements_info",
 :size_plus_indexes_bytes 0,
 :s_name "public",
 :system_object false,
 :view_type "view",
 :size_bytes 0}

#’postgres-extras-clj.core/indexes

List all index objects in current database

(show-public (pgex/indexes db))

total_columns	unique_index	rows_indexed	oid	index_size_bytes	i_name	partial_index	index_size	key_columns	valid_index	s_name	system_object	primary_key	t_name
1	true	10.0	16440	16384	pgbench_branches_pkey	false	16 kB	1	true	public	false	true	pgbench_branches
1	true	100.0	16442	16384	pgbench_tellers_pkey	false	16 kB	1	true	public	false	true	pgbench_tellers
1	true	1000000.0	16444	22487040	pgbench_accounts_pkey	false	21 MB	1	true	public	false	true	pgbench_accounts

example row

(first (:indexes dd))

{:total_columns 1,
 :unique_index true,
 :rows_indexed 10.0,
 :oid 16440,
 :index_size_bytes 16384,
 :i_name "pgbench_branches_pkey",
 :partial_index false,
 :index_size "16 kB",
 :key_columns 1,
 :valid_index true,
 :s_name "public",
 :system_object false,
 :primary_key true,
 :t_name "pgbench_branches"}

#’postgres-extras-clj.core/columns

List all database column objects

(show-public (pgex/columns db))

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

example row

(first (:columns dd))

{:description nil,
 :generated_column false,
 :source_type "table",
 :data_type "oid",
 :c_name "oid",
 :position 1,
 :s_name "pg_catalog",
 :system_object true,
 :default_value nil,
 :t_name "pg_proc"}

#’postgres-extras-clj.core/tables

List all table objects in current database

(show-public (pgex/tables db))

size_pretty	description	owned_by	size_plus_indexes	rows	oid	data_type	size_plus_indexes_bytes	s_name	system_object	t_name	size_bytes	bytes_per_row
144 kB		postgres	160 kB	10	16433	table	163840	public	false	pgbench_branches	147456	14746
240 kB		postgres	256 kB	100	16427	table	262144	public	false	pgbench_tellers	245760	2458
130 MB		postgres	152 MB	1000000	16430	table	158924800	public	false	pgbench_accounts	136437760	136
1832 kB		postgres	1832 kB	32000	16424	table	1875968	public	false	pgbench_history	1875968	59

example row

(first (:tables dd))

{:size_pretty "144 kB",
 :description nil,
 :owned_by "postgres",
 :size_plus_indexes "160 kB",
 :rows 10,
 :oid 16433,
 :data_type "table",
 :size_plus_indexes_bytes 163840,
 :s_name "public",
 :system_object false,
 :t_name "pgbench_branches",
 :size_bytes 147456,
 :bytes_per_row 14746}

#’postgres-extras-clj.core/functions

List all function objects in current database

(show-public (pgex/functions db))

description	f_name	source_code	owned_by	proc_security	result_data_types	access_privileges	argument_data_types	s_name	proc_language	system_object
	pg_stat_statements_reset	pg_stat_statements_reset_1_7	postgres	invoker	void	postgres=X/postgres	userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0	public	c	false
	pg_stat_statements_info	pg_stat_statements_info	postgres	invoker	record		OUT dealloc bigint, OUT stats_reset timestamp with time zone	public	c	false
	pg_stat_statements	pg_stat_statements_1_10	postgres	invoker	SETOF record		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	public	c	false

example row

(first (:functions dd))

{:description "I/O",
 :f_name "boolin",
 :source_code "boolin",
 :owned_by "postgres",
 :proc_security "invoker",
 :result_data_types "boolean",
 :access_privileges nil,
 :argument_data_types "cstring",
 :s_name "pg_catalog",
 :proc_language "internal",
 :system_object true}

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)

#’postgres-extras-clj.core/vacuum-stats

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

(show (pgex/vacuum-stats db))

schema	table	last_vacuum	last_autovacuum	rowcount	dead_rowcount	autovacuum_threshold	expect_autovacuum
public	pgbench_branches	2025-06-27 10:10	2025-06-27 10:10	10.0	0	50.2
public	pgbench_tellers	2025-06-27 10:10	2025-06-27 10:10	100.0	0	52.0
public	pgbench_accounts	2025-06-27 10:10	2025-06-27 10:10	1000000.0	0	20050.0
public	pgbench_history	2025-06-27 10:10	2025-06-27 10:10	32000.0	0	690.0

example row

(first (:vacuum-stats stats))

{:schema "public",
 :table "pgbench_branches",
 :last_vacuum "2025-06-27 10:10",
 :last_autovacuum "2025-06-27 10:10",
 :rowcount 10.0,
 :dead_rowcount 0,
 :autovacuum_threshold 50.2,
 :expect_autovacuum nil}

#’postgres-extras-clj.core/index-usage

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

(show (pgex/index-usage db))

schema	name	percent_of_times_index_used	rows_in_table
public	pgbench_accounts	99	1000000
public	pgbench_history		32000
public	pgbench_tellers	49	100
public	pgbench_branches	44	10

example row

(first (:index-usage stats))

{:schema "public",
 :name "pgbench_accounts",
 :percent_of_times_index_used 99,
 :rows_in_table 1000000}

#’postgres-extras-clj.core/total-index-size

Total size of all indexes in MB

(pgex/total-index-size db)

[{:size 22519808}]

example row

(first (:total-index-size stats))

{:size 22519808}

#’postgres-extras-clj.core/cache-hit

Index and table hit rate

(show (pgex/cache-hit db))

name	ratio
index hit rate	0.99304478510688361337
table hit rate	0.98213610603614123501

example row

(first (:cache-hit stats))

{:name "index hit rate", :ratio 0.99304478510688361337M}

#’postgres-extras-clj.core/records-rank

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

(show (pgex/records-rank db))

schema	name	estimated_count
public	pgbench_accounts	1000000
public	pgbench_history	32000
public	pgbench_tellers	100
public	pgbench_branches	10

example row

(first (:records-rank stats))

{:schema "public", :name "pgbench_accounts", :estimated_count 1000000}

#’postgres-extras-clj.core/index-cache-hit

Calculates your cache hit rate for reading indexes

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

schema	name	buffer_hits	block_reads	total_read	ratio
public	pgbench_history	0	0	0	0.0
public	pgbench_tellers	32053	2	32055	0.9999376072375604
public	pgbench_branches	28626	2	28628	0.9999301383261143
public	pgbench_accounts	331815	2745	334560	0.991795193687231

example row

(first (:index-cache-hit stats))

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

#’postgres-extras-clj.core/outliers

Queries that have longest execution time in aggregate.

(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	21821.030	0.687812220162681	32000	0.000
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2	28824.341	0.24230609136281778	32000	0.000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)	3137.137	0.02637171958743119	32000	0.000
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2	1084.135	0.009113562034819042	32000	0.000
WITH RECURSIVE Z ( IX, IY, CX, CY, X, Y, I ) AS ( SELECT IX, IY, X::float, Y::float, X::float, Y::float, $1 FROM ( SELECT $2 + $3 * i, i FROM generate_series($4, $5) AS i) AS xgen (x, ix), ( SELECT $6 + $7 * i, i FROM generate_series($8, $9) AS i) AS ygen (y, iy) UNION ALL SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * $10 + CY, I + $11 FROM Z WHERE X * X + Y * Y < $12::float AND I < $13 ) SELECT array_to_string(array_agg(SUBSTRING($14, LEAST (GREATEST (I, $15), $16), $17)), $18) AS art FROM ( SELECT IX, IY, MAX(I) AS I FROM Z GROUP BY IY, IX ORDER BY IY, IX) AS ZT GROUP BY IY ORDER BY IY	613.668	0.005158677349572434	8	0.000
copy pgbench_accounts from stdin with (freeze on)	426.072	0.0035816879460745512	1	0.000
SELECT n.nspname::text AS s_name, p.proname::text AS f_name, pg_get_function_result(p.oid)::text AS result_data_types, pg_get_function_arguments(p.oid)::text AS argument_data_types, pg_get_userbyid(p.proowner)::text AS owned_by, CASE WHEN p.prosecdef THEN $1::text ELSE $2::text END AS proc_security, array_to_string(p.proacl, $3::text) AS access_privileges, l.lanname::text AS proc_language, p.prosrc::text AS source_code, obj_description(p.oid, $4::name)::text AS description, CASE WHEN n.nspname <> ALL (ARRAY[$5::name, $6::name]) THEN $7 ELSE $8 END AS system_object FROM pg_proc p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_language l ON l.oid = p.prolang	416.850	0.003504163473303969	22	0.000
WITH RECURSIVE Z ( IX, IY, CX, CY, X, Y, I ) AS ( SELECT IX, IY, X::float, Y::float, X::float, Y::float, $1 FROM ( SELECT $2 + $3 * i, i FROM generate_series($4, $5) AS i) AS xgen (x, ix), ( SELECT $6 + $7 * i, i FROM generate_series($8, $9) AS i) AS ygen (y, iy) UNION ALL SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * $10 + CY, I + $11 FROM Z WHERE X * X + Y * Y < $12::float AND I < $13 ) SELECT array_to_string(array_agg(SUBSTRING($14, LEAST (GREATEST (I, $15), $16), $17)), $18) AS art FROM ( SELECT IX, IY, MAX(I) AS I FROM Z GROUP BY IY, IX ORDER BY IY, IX) AS ZT GROUP BY IY ORDER BY IY	315.033	0.0026482662209195177	4	0.000
SELECT n.nspname::text AS s_name, p.proname::text AS f_name, pg_get_function_result(p.oid)::text AS result_data_types, pg_get_function_arguments(p.oid)::text AS argument_data_types, pg_get_userbyid(p.proowner)::text AS owned_by, CASE WHEN p.prosecdef THEN $1::text ELSE $2::text END AS proc_security, array_to_string(p.proacl, $3::text) AS access_privileges, l.lanname::text AS proc_language, p.prosrc::text AS source_code, obj_description(p.oid, $4::name)::text AS description, CASE WHEN n.nspname <> ALL (ARRAY[$5::name, $6::name]) THEN $7 ELSE $8 END AS system_object FROM pg_proc p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_language l ON l.oid = p.prolang	237.387	0.0019955443496267496	12	0.000
SELECT abalance FROM pgbench_accounts WHERE aid = $1	205.881	0.001730696812657487	32000	0.000

example row

(first (:outliers stats))

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

#’postgres-extras-clj.core/extensions

Available and installed extensions

(show (pgex/extensions db))

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

example row

(first (:extensions stats))

{:name "plpgsql",
 :default_version "1.0",
 :installed_version "1.0",
 :comment "PL/pgSQL procedural language"}

#’postgres-extras-clj.core/total-table-size

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

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

schema	name	size
public	pgbench_accounts	158924800
public	pgbench_history	1875968
public	pgbench_tellers	262144
public	pgbench_branches	163840

example row

(first (:total-table-size stats))

{:schema "public", :name "pgbench_accounts", :size 158924800}

#’postgres-extras-clj.core/bloat

Table and index bloat in your database ordered by most wasteful

(show (pgex/bloat db))

type	schemaname	object_name	bloat	waste
table	public	pgbench_accounts	1.1	8052736
table	public	pgbench_tellers	26.0	204800
table	public	pgbench_branches	14.0	106496
index	pg_catalog	pg_ts_config_map::pg_ts_config_map_index	2.0	16384
index	pg_catalog	pg_amproc::pg_amproc_fam_proc_index	1.7	16384
index	pg_catalog	pg_depend::pg_depend_depender_index	1.2	16384
table	pg_catalog	pg_class	1.2	16384
index	pg_catalog	pg_auth_members::pg_auth_members_member_role_index	2.0	8192
index	pg_catalog	pg_auth_members::pg_auth_members_oid_index	2.0	8192
index	pg_catalog	pg_auth_members::pg_auth_members_role_member_index	2.0	8192
index	pg_catalog	pg_authid::pg_authid_oid_index	2.0	8192
index	pg_catalog	pg_authid::pg_authid_rolname_index	2.0	8192
index	pg_catalog	pg_cast::pg_cast_oid_index	2.0	8192
index	pg_catalog	pg_cast::pg_cast_source_target_index	2.0	8192
index	pg_catalog	pg_namespace::pg_namespace_oid_index	2.0	8192
index	pg_catalog	pg_tablespace::pg_tablespace_spcname_index	2.0	8192
index	pg_catalog	pg_tablespace::pg_tablespace_oid_index	2.0	8192
index	pg_catalog	pg_shdescription::pg_shdescription_o_c_index	2.0	8192
index	pg_catalog	pg_range::pg_range_rngtypid_index	2.0	8192
index	pg_catalog	pg_range::pg_range_rngmultitypid_index	2.0	8192
index	pg_catalog	pg_auth_members::pg_auth_members_grantor_index	2.0	8192
index	pg_catalog	pg_ts_config::pg_ts_config_oid_index	2.0	8192
index	pg_catalog	pg_language::pg_language_name_index	2.0	8192
index	pg_catalog	pg_language::pg_language_oid_index	2.0	8192
index	pg_catalog	pg_namespace::pg_namespace_nspname_index	2.0	8192
index	pg_catalog	pg_ts_config::pg_ts_config_cfgname_index	2.0	8192
index	public	pgbench_tellers::pgbench_tellers_pkey	2.0	8192
index	public	pgbench_branches::pgbench_branches_pkey	2.0	8192
index	pg_catalog	pg_ts_template::pg_ts_template_tmplname_index	2.0	8192
index	pg_catalog	pg_ts_template::pg_ts_template_oid_index	2.0	8192
index	pg_catalog	pg_ts_parser::pg_ts_parser_prsname_index	2.0	8192
index	pg_catalog	pg_ts_parser::pg_ts_parser_oid_index	2.0	8192
index	pg_catalog	pg_am::pg_am_oid_index	2.0	8192
index	pg_catalog	pg_am::pg_am_name_index	2.0	8192
index	pg_catalog	pg_database::pg_database_datname_index	2.0	8192
index	pg_catalog	pg_database::pg_database_oid_index	2.0	8192
index	pg_catalog	pg_ts_dict::pg_ts_dict_oid_index	2.0	8192
index	pg_catalog	pg_ts_dict::pg_ts_dict_dictname_index	2.0	8192
index	pg_catalog	pg_extension::pg_extension_name_index	2.0	8192
index	pg_catalog	pg_extension::pg_extension_oid_index	2.0	8192
index	pg_catalog	pg_amproc::pg_amproc_oid_index	1.3	8192
index	pg_catalog	pg_amop::pg_amop_opr_fam_index	1.2	8192
index	pg_catalog	pg_amop::pg_amop_fam_strat_index	1.2	8192
table	pg_catalog	pg_depend	1.1	8192
table	pg_catalog	pg_description	1.0	8192
index	pg_catalog	pg_conversion::pg_conversion_default_index	1.0	0
index	pg_catalog	pg_amop::pg_amop_oid_index	1.0	0
index	pg_catalog	pg_aggregate::pg_aggregate_fnoid_index	1.0	0
index	pg_catalog	pg_conversion::pg_conversion_name_nsp_index	1.0	0
index	pg_catalog	pg_conversion::pg_conversion_oid_index	1.0	0
index	pg_catalog	pg_init_privs::pg_init_privs_o_c_o_index	1.0	0
index	pg_catalog	pg_opfamily::pg_opfamily_am_name_nsp_index	1.0	0
index	pg_catalog	pg_opfamily::pg_opfamily_oid_index	1.0	0
table	pg_catalog	pg_am	1.0	0
table	pg_catalog	pg_amop	1.0	0
table	pg_catalog	pg_amproc	1.0	0
table	pg_catalog	pg_attribute	1.0	0
table	pg_catalog	pg_auth_members	1.0	0
table	pg_catalog	pg_authid	1.0	0
table	pg_catalog	pg_cast	1.0	0
table	pg_catalog	pg_constraint	1.0	0
table	pg_catalog	pg_conversion	1.0	0
table	pg_catalog	pg_database	1.0	0
table	pg_catalog	pg_extension	1.0	0
table	pg_catalog	pg_index	1.0	0
table	pg_catalog	pg_init_privs	1.0	0
table	pg_catalog	pg_language	1.0	0
table	pg_catalog	pg_namespace	1.0	0
table	pg_catalog	pg_opclass	1.0	0
table	pg_catalog	pg_operator	1.0	0
table	pg_catalog	pg_opfamily	1.0	0
table	pg_catalog	pg_proc	1.0	0
table	pg_catalog	pg_range	1.0	0
table	pg_catalog	pg_rewrite	1.0	0
table	pg_catalog	pg_shdescription	1.0	0
table	pg_catalog	pg_tablespace	1.0	0
table	pg_catalog	pg_ts_config	1.0	0
table	pg_catalog	pg_ts_config_map	1.0	0
table	pg_catalog	pg_ts_dict	1.0	0
table	pg_catalog	pg_ts_parser	1.0	0
table	pg_catalog	pg_ts_template	1.0	0
index	pg_catalog	pg_description::pg_description_o_c_o_index	0.9	0
table	pg_catalog	pg_type	0.9	0
table	pg_catalog	pg_collation	0.9	0
index	pg_catalog	pg_depend::pg_depend_reference_index	0.9	0
table	public	pgbench_history	0.8	0
index	pg_catalog	pg_constraint::pg_constraint_oid_index	0.7	0
index	pg_catalog	pg_opclass::pg_opclass_oid_index	0.7	0
index	pg_catalog	pg_opclass::pg_opclass_am_name_nsp_index	0.7	0
index	pg_catalog	pg_index::pg_index_indrelid_index	0.7	0
index	pg_catalog	pg_index::pg_index_indexrelid_index	0.7	0
index	pg_catalog	pg_constraint::pg_constraint_contypid_index	0.7	0
index	pg_catalog	pg_constraint::pg_constraint_conrelid_contypid_conname_index	0.7	0
index	pg_catalog	pg_constraint::pg_constraint_conparentid_index	0.7	0
index	pg_catalog	pg_constraint::pg_constraint_conname_nsp_index	0.7	0
table	pg_catalog	pg_aggregate	0.7	0
index	pg_catalog	pg_class::pg_class_relname_nsp_index	0.6	0
index	pg_catalog	pg_collation::pg_collation_name_enc_nsp_index	0.5	0
index	pg_catalog	pg_operator::pg_operator_oprname_l_r_n_index	0.5	0
index	pg_catalog	pg_collation::pg_collation_oid_index	0.5	0
index	pg_catalog	pg_proc::pg_proc_proname_args_nsp_index	0.4	0
index	pg_catalog	pg_class::pg_class_oid_index	0.4	0
index	pg_catalog	pg_type::pg_type_typname_nsp_index	0.4	0
index	pg_catalog	pg_operator::pg_operator_oid_index	0.4	0
index	pg_catalog	pg_attribute::pg_attribute_relid_attnam_index	0.4	0
index	pg_catalog	pg_type::pg_type_oid_index	0.3	0
index	pg_catalog	pg_class::pg_class_tblspc_relfilenode_index	0.2	0
index	pg_catalog	pg_rewrite::pg_rewrite_oid_index	0.2	0
index	pg_catalog	pg_attribute::pg_attribute_relid_attnum_index	0.2	0
index	public	pgbench_accounts::pgbench_accounts_pkey	0.2	0
index	pg_catalog	pg_rewrite::pg_rewrite_rel_rulename_index	0.2	0
index	pg_catalog	pg_proc::pg_proc_oid_index	0.1	0

example row

(first (:bloat stats))

{:type "table",
 :schemaname "public",
 :object_name "pgbench_accounts",
 :bloat 1.1M,
 :waste 8052736}

#’postgres-extras-clj.core/calls

Queries that have the highest frequency of execution

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

query	exec_time_ms	exec_time_ratio	calls	sync_io_time_ms
commit	7.568	6.361382463554805E-5	32001	0.000
begin	8.894	7.475989311512681E-5	32001	0.000
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2	28824.341	0.24229609449873538	32000	0.000
SELECT abalance FROM pgbench_accounts WHERE aid = $1	205.881	0.0017306254090014475	32000	0.000
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2	1084.135	0.00911318603502285	32000	0.000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)	3137.137	0.026370631564860844	32000	0.000
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2	21821.030	0.6877838429756259	32000	0.000
SET application_name = 'PostgreSQL JDBC Driver'	2.554	2.1468025602660974E-5	1269	0.000
SET extra_float_digits = 3	4.454	3.7444256311153375E-5	1269	0.000
SET application_name = 'PostgreSQL JDBC Driver'	1.391	1.169264044194772E-5	702	0.000

example row

(first (:calls stats))

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

#’postgres-extras-clj.core/table-size

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

(show (pgex/table-size db))

schema	name	size
public	pgbench_accounts	136437760
public	pgbench_history	1875968
public	pgbench_tellers	245760
public	pgbench_branches	147456

example row

(first (:table-size stats))

{:schema "public", :name "pgbench_accounts", :size 136437760}

#’postgres-extras-clj.core/connections

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

(first (:connections stats))

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

#’postgres-extras-clj.core/table-cache-hit

Calculates your cache hit rate for reading tables

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

schema	name	buffer_hits	block_reads	total_read	ratio
public	pgbench_branches	571905	0	571905	1.0
public	pgbench_tellers	481950	0	481950	1.0
public	pgbench_history	129126	0	129126	1.0
public	pgbench_accounts	399090	28776	427866	0.9327452987617618

example row

(first (:table-cache-hit stats))

{:schema "public",
 :name "pgbench_branches",
 :buffer_hits 571905,
 :block_reads 0,
 :total_read 571905,
 :ratio 1.0}

#’postgres-extras-clj.core/table-indexes-size

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

(first (:table-indexes-size stats))

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

#’postgres-extras-clj.core/seq-scans

Count of sequential scans by table descending by order

(show (pgex/seq-scans db))

schema	name	count
public	pgbench_branches	35607
public	pgbench_tellers	32324
public	pgbench_accounts	4
public	pgbench_history	3

example row

(first (:seq-scans stats))

{:schema "public", :name "pgbench_branches", :count 35607}

#’postgres-extras-clj.core/index-size

The size of indexes, descending by size

(show (pgex/index-size db))

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

example row

(first (:index-size stats))

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

These show no results for the pgbench example but can be valuable in other scenarios.

#’postgres-extras-clj.core/partition-children

List all child partitions in current database

(show (pgex/partition-children db))

()

#’postgres-extras-clj.core/partition-parents

List all parent partitions in current database

(show (pgex/partition-parents db))

()

#’postgres-extras-clj.core/duplicate-indexes

Multiple indexes that have the same set of columns, same opclass, expression and predicate

(show (pgex/duplicate-indexes db))

()

#’postgres-extras-clj.core/locks

Queries with active exclusive locks

(show (pgex/locks db))

()

#’postgres-extras-clj.core/null-indexes

Find indexes with a high ratio of NULL values

(show (pgex/null-indexes db))

()

#’postgres-extras-clj.core/all-locks

Queries with active locks

(show (pgex/all-locks db))

()

#’postgres-extras-clj.core/blocking

Queries holding locks other queries are waiting to be released

(show (pgex/blocking db))

()

#’postgres-extras-clj.core/unused-indexes

Unused and almost unused indexes

(show (pgex/unused-indexes db {:min_scans 50}))

()

#’postgres-extras-clj.core/long-running-queries

All queries longer than the threshold by descending duration

(show (pgex/long-running-queries db))

()

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.

(comment
  (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"
 "! Warning :bloat public.pgbench_tellers Bloated tables\n{:type \"table\", :schemaname \"public\", :object_name \"pgbench_tellers\", :bloat 26.0M, :waste 204800}"
 "! Warning :bloat public.pgbench_branches Bloated tables\n{:type \"table\", :schemaname \"public\", :object_name \"pgbench_branches\", :bloat 14.0M, :waste 106496}"
 "✓  Passed :bloat pg_catalog.pg_ts_config_map::pg_ts_config_map_index"
 "✓  Passed :bloat pg_catalog.pg_amproc::pg_amproc_fam_proc_index"
 "✓  Passed :bloat pg_catalog.pg_depend::pg_depend_depender_index"
 "✓  Passed :bloat pg_catalog.pg_class"
 "✓  Passed :bloat pg_catalog.pg_auth_members::pg_auth_members_member_role_index"
 "✓  Passed :bloat pg_catalog.pg_auth_members::pg_auth_members_oid_index"
 "✓  Passed :bloat pg_catalog.pg_auth_members::pg_auth_members_role_member_index"
 "✓  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 21821.030M, :prop_exec_time 0.6877539481806065, :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 WITH RECURSIVE Z ( IX, IY, CX, CY, X, Y, I ) AS ( SELECT IX, IY, X::float, Y::float, X::float, Y::float, $1"
 "✓  Passed :outliers copy pgbench_accounts from stdin with (freeze on)"
 "✓  Passed :outliers SELECT n.nspname::text AS s_name, p.proname::text AS f_name, pg_get_function_result(p.oid)::text AS result_data_types, pg_get_function_arguments(p.oid)::text AS argument_data_types,"
 "✓  Passed :outliers WITH RECURSIVE Z ( IX, IY, CX, CY, X, Y, I ) AS ( SELECT IX, IY, X::float, Y::float, X::float, Y::float, $1"
 "✓  Passed :outliers SELECT n.nspname::text AS s_name, p.proname::text AS f_name, pg_get_function_result(p.oid)::text AS result_data_types, pg_get_function_arguments(p.oid)::text AS argument_data_types,"
 "✓  Passed :outliers SELECT abalance FROM pgbench_accounts WHERE aid = $1"
 "✓  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\", :name \"pgbench_accounts\", :buffer_hits 399090, :block_reads 28776, :total_read 427866, :ratio 0.9327452987617618}")

Or just the warnings

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

("! Warning :bloat public.pgbench_tellers Bloated tables\n{:type \"table\", :schemaname \"public\", :object_name \"pgbench_tellers\", :bloat 26.0M, :waste 204800}"
 "! Warning :bloat public.pgbench_branches Bloated tables\n{:type \"table\", :schemaname \"public\", :object_name \"pgbench_branches\", :bloat 14.0M, :waste 106496}"
 "! 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 21821.030M, :prop_exec_time 0.6877050407085642, :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\", :name \"pgbench_accounts\", :buffer_hits 399090, :block_reads 28776, :total_read 427866, :ratio 0.9327452987617618}")

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 :name}})

(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\", :name \"pgbench_accounts\", :buffer_hits 399090, :block_reads 28776, :total_read 427866, :ratio 0.9327452987617618}")

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