postgres-extras-clj.core — com.github.perrygeo/postgres-extras-clj 0.3.23

A namespace of useful functions for inspecting PostgreSQL databases in Clojure.

The SQL query logic is based on

• https://github.com/heroku/heroku-pg-extras/tree/main
• https://github.com/pawurb/ecto_psql_extras/tree/main
• https://github.com/rustprooflabs/pgdd

Queries ported to HugSQL syntax to access them as Clojure data structures.

all-locks clj

(all-locks db)

(all-locks db params)

(all-locks db params options & command-options)

Queries with active locks

autovacuum-disabled clj

(autovacuum-disabled db)

(autovacuum-disabled db params)

(autovacuum-disabled db params options & command-options)

Tables with autovacuum disabled

bgwriter-stats clj

(bgwriter-stats db)

Checkpoint and background writer activity.

bgwriter-stats-legacy clj

(bgwriter-stats-legacy db)

(bgwriter-stats-legacy db params)

(bgwriter-stats-legacy db params options & command-options)

Checkpoint and background writer activity (PostgreSQL < 17)

bgwriter-stats-pg17 clj

(bgwriter-stats-pg17 db)

(bgwriter-stats-pg17 db params)

(bgwriter-stats-pg17 db params options & command-options)

Checkpoint and background writer activity (PostgreSQL >= 17)

bloat clj

(bloat db)

(bloat db params)

(bloat db params options & command-options)

Table and index bloat in your database

blocking clj

(blocking db)

(blocking db params)

(blocking db params options & command-options)

Queries holding locks other queries are waiting to be released

cache-hit clj

(cache-hit db)

(cache-hit db params)

(cache-hit db params options & command-options)

Index and table hit rate

calls clj

(calls db {:keys [limit] :or {limit 10}})

Queries that have the highest frequency of execution.

calls-legacy clj

(calls-legacy db)

(calls-legacy db params)

(calls-legacy db params options & command-options)

Queries that have the highest frequency of execution (PostgreSQL < 17)

calls-pg17 clj

(calls-pg17 db)

(calls-pg17 db params)

(calls-pg17 db params options & command-options)

Queries that have the highest frequency of execution (PostgreSQL >= 17)

columns clj

(columns db)

(columns db params)

(columns db params options & command-options)

List all database column objects

connection-states clj

(connection-states db)

(connection-states db params)

(connection-states db params options & command-options)

Connections grouped by state

connections clj

(connections db)

(connections db params)

(connections db params options & command-options)

Returns the list of all active database connections

constraints clj

(constraints db
             &
             {:keys [include-system-objects?]
              :or {include-system-objects? false}})

List all named constraints (primary key, unique, foreign key, check, exclusion, and constraint trigger) on tables in the current database.

By default, system objects (constraints on tables in pg_catalog, information_schema, pg_, and TimescaleDB's timescaledb, timescaledb_information, and timescaledb_experimental schemas) are excluded. Pass :include-system-objects? true to include them.

constraints-raw clj

(constraints-raw db)

(constraints-raw db params)

(constraints-raw db params options & command-options)

List all constraint objects in current database

database-stats clj

(database-stats db)

(database-stats db params)

(database-stats db params options & command-options)

Cumulative database-level counters

databases clj

(databases db)

(databases db params)

(databases db params options & command-options)

List all databases

db-settings clj

(db-settings db)

(db-settings db params)

(db-settings db params options & command-options)

Values of selected PostgreSQL settings

ddl-comments clj

(ddl-comments db)

(ddl-comments db params)

(ddl-comments db params options & command-options)

Table/view/column comments for COMMENT ON statements

ddl-function-defs clj

(ddl-function-defs db)

(ddl-function-defs db params)

(ddl-function-defs db params options & command-options)

Function and procedure CREATE OR REPLACE definitions

ddl-index-defs clj

(ddl-index-defs db)

(ddl-index-defs db params)

(ddl-index-defs db params options & command-options)

Index definitions, excluding primary-key and constraint-owned indexes

ddl-rule-defs clj

(ddl-rule-defs db)

(ddl-rule-defs db params)

(ddl-rule-defs db params options & command-options)

Rule definitions, excluding the internal _RETURN rules of views

ddl-table-columns clj

(ddl-table-columns db)

(ddl-table-columns db params)

(ddl-table-columns db params options & command-options)

Columns of tables (relkind r/p/f), with rendered type/default/identity

ddl-table-constraints clj

(ddl-table-constraints db)

(ddl-table-constraints db params)

(ddl-table-constraints db params options & command-options)

Table constraints (PK/unique/check/FK), excluding NOT NULL (contype n)

ddl-table-options clj

(ddl-table-options db)

(ddl-table-options db params)

(ddl-table-options db params options & command-options)

Persistence, storage params, tablespace and partition key of tables

ddl-trigger-defs clj

(ddl-trigger-defs db)

(ddl-trigger-defs db params)

(ddl-trigger-defs db params options & command-options)

Trigger definitions, excluding internal triggers

ddl-view-defs clj

(ddl-view-defs db)

(ddl-view-defs db params)

(ddl-view-defs db params options & command-options)

View and materialized view SELECT definitions

default-diagnostic-fns clj

Defines the critical queries and their cutoffs for the diagnose function. The value is a map containing a predicate fn (true = pass) to test against each result, and a description of the problem if false.

diagnose clj

(diagnose stats
          &
          {:keys [diagnostic-fns] :or {diagnostic-fns default-diagnostic-fns}})

Run assertions on stats and return a seq of result maps. Iterate through the diagnostic functions, run them, then iterate through each record, applying the predicate. Returns a flat list of result maps.

diagnose-warnings clj

(diagnose-warnings stats
                   &
                   {:keys [diagnostic-fns]
                    :or {diagnostic-fns default-diagnostic-fns}})

Run assertions on stats and return a seq of result maps. Iterate through the diagnostic functions, then through the results, applying the predicate. Returns a flat list of result maps, filtered to contain only actionable warnings. An empty return sequence indicates all your dianostics passed.

duplicate-indexes clj

(duplicate-indexes db)

(duplicate-indexes db params)

(duplicate-indexes db params options & command-options)

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

extensions clj

(extensions db)

(extensions db params)

(extensions db params options & command-options)

Available and installed extensions

functions clj

(functions db)

(functions db params)

(functions db params options & command-options)

List all function objects in current database

health-check clj

(health-check db)

(health-check db params)

(health-check db params options & command-options)

Checks the db for liveliness

idle-in-transaction clj

(idle-in-transaction db)

(idle-in-transaction db params)

(idle-in-transaction db params options & command-options)

Sessions idle in transaction (vacuum blockers)

index-cache-hit clj

(index-cache-hit db)

(index-cache-hit db params)

(index-cache-hit db params options & command-options)

Calculates your cache hit rate for reading indexes

index-size clj

(index-size db)

(index-size db params)

(index-size db params options & command-options)

The size of indexes

index-usage clj

(index-usage db)

(index-usage db params)

(index-usage db params options & command-options)

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

indexes clj

(indexes db)

(indexes db params)

(indexes db params options & command-options)

List all index objects in current database

invalid-constraints clj

(invalid-constraints db)

(invalid-constraints db params)

(invalid-constraints db params options & command-options)

Constraints that are NOT VALID

invalid-indexes clj

(invalid-indexes db)

(invalid-indexes db params)

(invalid-indexes db params options & command-options)

Indexes that are invalid and need REINDEX

key-relationships clj

(key-relationships db
                   &
                   {:keys [include-system-objects?]
                    :or {include-system-objects? false}})

List all foreign key relationships as ERD-ready edges.

Each map describes one foreign key constraint: :constraint — name of the foreign key constraint :schema, :table — referencing side :columns — referencing columns, in constraint order :foreign_schema, :foreign_table — referenced side :foreign_columns — referenced columns, parallel to :columns :delete_action — ON DELETE action (NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT) :update_action — ON UPDATE action :match_type — MATCH type (SIMPLE, FULL, PARTIAL) :deferrable, :initially_deferred, :validated

Composite foreign keys produce a single relationship map with parallel :columns and :foreign_columns vectors.

By default, system objects are excluded. Pass :include-system-objects? true to include them.

key-relationships-raw clj

(key-relationships-raw db)

(key-relationships-raw db params)

(key-relationships-raw db params options & command-options)

List all foreign key relationships, one row per column pair

kill-all! clj

(kill-all! db)

(kill-all! db params)

(kill-all! db params options & command-options)

Kill all the active database connections

locks clj

(locks db)

(locks db params)

(locks db params options & command-options)

Queries with active exclusive locks

long-running-queries clj

(long-running-queries db)

(long-running-queries db params)

(long-running-queries db params options & command-options)

All queries longer than the threshold

maintenance clj

(maintenance db)

(maintenance db params)

(maintenance db params options & command-options)

Last vacuum/analyze and their counts per table

mandelbrot clj

(mandelbrot db)

(mandelbrot db params)

(mandelbrot db params options & command-options)

The mandelbrot set

missing-indexes clj

(missing-indexes db)

(missing-indexes db params)

(missing-indexes db params options & command-options)

Tables with low index usage and many rows

null-indexes clj

(null-indexes db)

(null-indexes db params)

(null-indexes db params options & command-options)

Find indexes with a high ratio of NULL values

outliers clj

(outliers db {:keys [limit] :or {limit 10}})

Queries that have longest execution time in aggregate.

outliers-legacy clj

(outliers-legacy db)

(outliers-legacy db params)

(outliers-legacy db params options & command-options)

Queries that have longest execution time in aggregate (PostgreSQL < 17)

outliers-pg17 clj

(outliers-pg17 db)

(outliers-pg17 db params)

(outliers-pg17 db params options & command-options)

Queries that have longest execution time in aggregate (PostgreSQL >= 17)

partition-children clj

(partition-children db)

(partition-children db params)

(partition-children db params options & command-options)

List all child partitions in current database

partition-parents clj

(partition-parents db)

(partition-parents db params)

(partition-parents db params options & command-options)

List all parent partitions in current database

read-data-dictionary clj

(read-data-dictionary db
                      &
                      {:keys [include-system-objects?]
                       :or {include-system-objects? false}})

Create a data dictionary summarizing all major objects in your PostgreSQL database. Respects SQL COMMENTS, thus serves as a human-readable description of your data model.

By default, system objects (schemas in pg_catalog, information_schema, pg_, and TimescaleDB's timescaledb, timescaledb_information, and timescaledb_experimental schemas) are excluded. Pass :include-system-objects? true to include them.

read-stats clj

(read-stats db
            &
            {:keys [limit include-system-objects?]
             :or {limit 10 include-system-objects? false}})

Query postgres instance for all available diagnostic information. This will invoke all of the public, read-only diagnostic queries in the namespace.

The :outliers and :calls stats require enabling the pg-stat-statements extension, otherwise they will log a warning and return nil.

By default, :stats-collector-drift excludes system objects. Pass :include-system-objects? true to include them.

records-rank clj

(records-rank db)

(records-rank db params)

(records-rank db params options & command-options)

All tables and their row counts from two sources: the query planner (pg_class.reltuples) and the statistics collector (pg_stat_user_tables.n_live_tup)

replication-lag clj

(replication-lag db)

(replication-lag db params)

(replication-lag db params options & command-options)

Standby replication lag in seconds

replication-slots clj

(replication-slots db)

(replication-slots db params)

(replication-slots db params options & command-options)

Replication slots with lag and retention

replication-stats clj

(replication-stats db)

(replication-stats db params)

(replication-stats db params options & command-options)

Standby connections and per-standby lag from the primary

roles clj

(roles db)

(roles db params)

(roles db params options & command-options)

Roles and their privilege/expiry attributes

schemas clj

(schemas db)

(schemas db params)

(schemas db params options & command-options)

List all shemas in current database

seq-scans clj

(seq-scans db)

(seq-scans db params)

(seq-scans db params options & command-options)

Count of sequential scans by table

sequence-origin clj

(sequence-origin db)

(sequence-origin db params)

(sequence-origin db params options & command-options)

How each sequence was created: serial, identity, or explicit CREATE SEQUENCE

sequences clj

(sequences db)

(sequences db params)

(sequences db params options & command-options)

Sequences near exhaustion or on small integer types

server-version-num clj

(server-version-num db)

(server-version-num db params)

(server-version-num db params options & command-options)

Numeric PostgreSQL server version (e.g. 170005 for 17.5)

stats-collector-drift clj

(stats-collector-drift db
                       &
                       {:keys [include-system-objects?]
                        :or {include-system-objects? false}})

Row-count drift between the planner estimate (reltuples) and the statistics collector (n_live_tup). Includes a :drift_probability from 0..1 (0=perfect agreement, 1=worst case drift). Higher values indicate a manual ANALYZE may be necessary.

stats-collector-drift-raw clj

(stats-collector-drift-raw db)

(stats-collector-drift-raw db params)

(stats-collector-drift-raw db params options & command-options)

Probability of drift between the planner estimates and the statistics collector. 0 = perfect agreement, 1 = total disagreement.

table-cache-hit clj

(table-cache-hit db)

(table-cache-hit db params)

(table-cache-hit db params options & command-options)

Calculates your cache hit rate for reading tables

table-indexes-size clj

(table-indexes-size db)

(table-indexes-size db params)

(table-indexes-size db params options & command-options)

Total size of all the indexes on each table

table-size clj

(table-size db)

(table-size db params)

(table-size db params options & command-options)

Size of the tables (excluding indexes)

tables clj

(tables db)

(tables db params)

(tables db params options & command-options)

List all table objects in current database

tables-without-primary-key clj

(tables-without-primary-key db)

(tables-without-primary-key db params)

(tables-without-primary-key db params options & command-options)

Tables without a primary key

total-index-size clj

(total-index-size db)

(total-index-size db params)

(total-index-size db params options & command-options)

Total size of all indexes in MB

total-table-size clj

(total-table-size db)

(total-table-size db params)

(total-table-size db params options & command-options)

Size of the tables (including indexes)

transaction-id clj

(transaction-id db)

(transaction-id db params)

(transaction-id db params options & command-options)

Transaction ID wraparound risk per database

triggers clj

(triggers db)

(triggers db params)

(triggers db params options & command-options)

List all trigger objects in current database

unindexed-foreign-keys clj

(unindexed-foreign-keys db)

(unindexed-foreign-keys db params)

(unindexed-foreign-keys db params options & command-options)

Foreign keys without a leading index on the referencing columns

unlogged-tables clj

(unlogged-tables db)

(unlogged-tables db params)

(unlogged-tables db params options & command-options)

Unlogged tables (data lost on crash)

unused-indexes clj

(unused-indexes db)

(unused-indexes db params)

(unused-indexes db params options & command-options)

Unused and almost unused indexes

unused-tables clj

(unused-tables db)

(unused-tables db params)

(unused-tables db params options & command-options)

Tables never accessed via an index

vacuum-stats clj

(vacuum-stats db)

(vacuum-stats db params)

(vacuum-stats db params options & command-options)

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

views clj

(views db)

(views db params)

(views db params options & command-options)

List all view objects in current database

wal-archiver clj

(wal-archiver db)

(wal-archiver db params)

(wal-archiver db params options & command-options)

WAL archiving health

wal-stats clj

(wal-stats db)

(wal-stats db params)

(wal-stats db params options & command-options)

WAL activity (PostgreSQL 14+)