`postgres-extras-clj` example with pgbench

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

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

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 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 0x3f341f89 "hugsql.adapter.next_jdbc.HugsqlAdapterNextJdbc@3f341f89"]

Do a health check to ensure connectivity

(pgex/health-check db)

{:now #inst "2024-07-11T15:18:45.956844000-00:00",
 :version
 "PostgreSQL 16.2 (Debian 16.2-1.pgdg110+2) 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))

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	400		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	263168	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

List all databases

(show (pgex/databases db))

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

example row

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

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

{: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}

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	19715	pg_stat_statements_info	0	public	false	view	0
0 bytes		postgres	0 bytes	-1	19726	pg_stat_statements	0	public	false	view	0

example row

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

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	19782	16384	pgbench_branches_pkey	false	16 kB	1	true	public	false	true	pgbench_branches
1	true	100.0	19784	16384	pgbench_tellers_pkey	false	16 kB	1	true	public	false	true	pgbench_tellers
1	true	1000000.0	19786	22487040	pgbench_accounts_pkey	false	21 MB	1	true	public	false	true	pgbench_accounts

example row

{:total_columns 3,
 :unique_index true,
 :rows_indexed 0.0,
 :oid 2696,
 :index_size_bytes 32768,
 :i_name "pg_statistic_relid_att_inh_index",
 :partial_index false,
 :index_size "32 kB",
 :key_columns 3,
 :valid_index true,
 :s_name "pg_catalog",
 :system_object true,
 :primary_key true,
 :t_name "pg_statistic"}

List all database column objects

(show-public (pgex/columns db))

generated_column	source_type	data_type	c_name	position	s_name	system_object	t_name
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
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

example row

{: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"}

List all table objects in current database

(show-public (pgex/tables db))

size_pretty	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	19775	table	163840	public	false	pgbench_branches	147456	14746
256 kB	postgres	272 kB	100	19769	table	278528	public	false	pgbench_tellers	262144	2621
130 MB	postgres	152 MB	1000000	19772	table	158941184	public	false	pgbench_accounts	136454144	136
1800 kB	postgres	1800 kB	32000	19766	table	1843200	public	false	pgbench_history	1843200	58

example row

{:size_pretty "280 kB",
 :description nil,
 :owned_by "postgres",
 :size_plus_indexes "312 kB",
 :rows 409,
 :oid 2619,
 :data_type "table",
 :size_plus_indexes_bytes 319488,
 :s_name "pg_catalog",
 :system_object true,
 :t_name "pg_statistic",
 :size_bytes 286720,
 :bytes_per_row 701}

List all function objects in current database

(show-public (pgex/functions db))

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

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

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

(show (pgex/vacuum-stats db))

schema	table	last_vacuum	last_autovacuum	rowcount	autovacuum_threshold
public	pgbench_branches	2024-07-11 09:08	2024-07-11 09:09	10.0	50.2
public	pgbench_tellers	2024-07-11 09:08	2024-07-11 09:09	100.0	52.0
public	pgbench_accounts	2024-07-11 09:08	2024-07-11 09:09	1000000.0	20050.0
public	pgbench_history	2024-07-11 09:08	2024-07-11 09:09	32000.0	690.0

example row

{:schema "public",
 :table "pgbench_branches",
 :last_vacuum "2024-07-11 09:08",
 :last_autovacuum "2024-07-11 09:09",
 :rowcount 10.0,
 :dead_rowcount 0,
 :autovacuum_threshold 50.2,
 :expect_autovacuum nil}

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	57	100
public	pgbench_branches	47	10

example row

{:schema "public",
 :name "pgbench_accounts",
 :percent_of_times_index_used 99,
 :rows_in_table 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))

name	ratio
index hit rate	0.99319155048877683195
table hit rate	0.98325516438754727960

example row

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

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

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

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	37366	2	37368	0.9999464782701777
public	pgbench_branches	31113	2	31115	0.9999357223204243
public	pgbench_accounts	332535	2745	335280	0.9918128131710809

example row

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

Queries that have longest execution time in aggregate.

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

query	exec_time_ms	prop_exec_time	calls
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2	12927.214	0.6589330487430934	32000
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2	56300.972	0.279089357936559	32000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)	4857.433	0.02407876287821654	32000
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2	1595.393	0.007908518366362697	32000
copy pgbench_accounts from stdin with (freeze on)	861.353	0.0042698124100655245	1
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder	801.851	0.003974851522791272	1
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder	754.769	0.0037414608194893707	1
CREATE EXTENSION IF NOT EXISTS postgis	383.233	0.0018997250306442772	1
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	381.987	0.0018935453850837023	6
CREATE EXTENSION IF NOT EXISTS postgis	375.006	0.0018589403048529087	1

example row

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

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

example row

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

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

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

schema	name	size
public	pgbench_accounts	158941184
public	pgbench_history	1843200
public	pgbench_tellers	278528
public	pgbench_branches	163840

example row

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

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	8069120
table	public	pgbench_tellers	28.0	221184
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
table	pg_catalog	pg_class	1.2	16384
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_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_ts_config::pg_ts_config_cfgname_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_extension::pg_extension_oid_index	2.0	8192
index	pg_catalog	pg_ts_dict::pg_ts_dict_dictname_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_oid_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_extension::pg_extension_name_index	2.0	8192
index	pg_catalog	pg_amproc::pg_amproc_oid_index	1.3	8192
index	pg_catalog	pg_amop::pg_amop_fam_strat_index	1.2	8192
index	pg_catalog	pg_amop::pg_amop_opr_fam_index	1.2	8192
table	pg_catalog	pg_depend	1.1	8192
index	pg_catalog	pg_depend::pg_depend_depender_index	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_depend::pg_depend_reference_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_collation	0.9	0
table	pg_catalog	pg_type	0.9	0
table	public	pgbench_history	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_oid_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_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_class::pg_class_oid_index	0.4	0
index	pg_catalog	pg_type::pg_type_oid_index	0.3	0
index	pg_catalog	pg_attribute::pg_attribute_relid_attnum_index	0.2	0
index	pg_catalog	pg_rewrite::pg_rewrite_oid_index	0.2	0
index	pg_catalog	pg_rewrite::pg_rewrite_rel_rulename_index	0.2	0
index	pg_catalog	pg_class::pg_class_tblspc_relfilenode_index	0.2	0
index	public	pgbench_accounts::pgbench_accounts_pkey	0.2	0
index	pg_catalog	pg_proc::pg_proc_oid_index	0.1	0

example row

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

Queries that have the highest frequency of execution

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

query	exec_time_ms	exec_time_ratio	calls
begin	18.790	9.313107695188783E-5	32001
commit	16.909	8.381033978988401E-5	32001
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2	56300.972	0.27905307309743405	32000
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2	12927.214	0.6588473798381789	32000
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2	1595.393	0.007907490167656872	32000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)	4857.433	0.024075632361009357	32000
SELECT abalance FROM pgbench_accounts WHERE aid = $1	357.088	0.0017698895506576837	32000
SET extra_float_digits = 3	3.398	1.6843645212852338E-5	330
SET application_name = 'PostgreSQL JDBC Driver'	1.779	8.817865438357988E-6	330
insert into pgbench_tellers(tid,bid,tbalance) values ($1,$2,$3)	0.384	1.9028265937977505E-6	100

example row

{:query "begin",
 :exec_time_ms 18.790M,
 :exec_time_ratio 9.314608859977958E-5,
 :calls 32001,
 :sync_io_time_ms 0.000M}

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

(show (pgex/table-size db))

schema	name	size
public	pgbench_accounts	136454144
public	pgbench_history	1843200
public	pgbench_tellers	262144
public	pgbench_branches	147456

example row

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

Returns the list of all active database connections

(show (pgex/connections db))

username	client_address	application_name
postgres	172.20.0.1/32	psql
postgres	172.20.0.1/32	PostgreSQL JDBC Driver

example row

{:username "postgres",
 :client_address "172.20.0.1/32",
 :application_name "psql"}

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	671318	0	671318	1.0
public	pgbench_tellers	488771	0	488771	1.0
public	pgbench_history	129424	0	129424	1.0
public	pgbench_accounts	400211	28776	428987	0.9329210442274475

example row

{:schema "public",
 :name "pgbench_branches",
 :buffer_hits 671318,
 :block_reads 0,
 :total_read 671318,
 :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	name	count
public	pgbench_branches	33352
public	pgbench_tellers	27304
public	pgbench_accounts	4
public	pgbench_history	3

example row

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

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

{:schema "public", :name "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"
 "! Warning :bloat public.pgbench_tellers Bloated tables\n{:type \"table\", :schemaname \"public\", :object_name \"pgbench_tellers\", :bloat 28.0M, :waste 221184}"
 "! 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_class"
 "✓  Passed :bloat pg_catalog.pg_range::pg_range_rngmultitypid_index"
 "✓  Passed :bloat pg_catalog.pg_auth_members::pg_auth_members_grantor_index"
 "✓  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 :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 12927.214M, :prop_exec_time 0.6587952573944417, :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 CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder"
 "✓  Passed :outliers CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder"
 "✓  Passed :outliers CREATE EXTENSION IF NOT EXISTS postgis"
 "✓  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 CREATE EXTENSION IF NOT EXISTS postgis"
 "✓  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 400211, :block_reads 28776, :total_read 428987, :ratio 0.9329210442274475}")

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 28.0M, :waste 221184}"
 "! 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 12927.214M, :prop_exec_time 0.6586800877968829, :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 400211, :block_reads 28776, :total_read 428987, :ratio 0.9329210442274475}")

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 400211, :block_reads 28776, :total_read 428987, :ratio 0.9329210442274475}")

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

postgres-extras-clj example with pgbench

Utility functions

Setup

Dependencies

Database benchmark

Driver Setup

Settings at a glance

Full map of database objects, the “data dictionary”

List all databases

List all shemas in current database

List all view objects in current database

List all index objects in current database

List all database column objects

List all table objects in current database

List all function objects in current database

Full map of diagnostic statistics

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

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

Total size of all indexes in MB

Index and table hit rate

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

Calculates your cache hit rate for reading indexes

Queries that have longest execution time in aggregate.

Available and installed extensions

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

Table and index bloat in your database ordered by most wasteful

Queries that have the highest frequency of execution

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

Returns the list of all active database connections

Calculates your cache hit rate for reading tables

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

Count of sequential scans by table descending by order

The size of indexes, descending by size

The Kill Switch

Diagnostics

Just for fun

To recreate this notebook

`postgres-extras-clj` example with pgbench