postgres-extras-clj.core — com.github.perrygeo/postgres-extras-clj 0.1.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

bloat clj

(bloat db)

(bloat db params)

(bloat db params options & command-options)

Table and index bloat in your database ordered by most wasteful

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)

(calls db params)

(calls db params options & command-options)

Queries that have the highest frequency of execution

columns clj

(columns db)

(columns db params)

(columns db params options & command-options)

List all database column objects

connections clj

(connections db)

(connections db params)

(connections db params options & command-options)

Returns the list of all active database connections

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

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

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, descending by size

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

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 by descending duration

mandelbrot clj

(mandelbrot db)

(mandelbrot db params)

(mandelbrot db params options & command-options)

The mandelbrot set

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)

(outliers db params)

(outliers db params options & command-options)

Queries that have longest execution time in aggregate.

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)

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

read-stats clj

(read-stats db & {:keys [limit] :or {limit 10}})

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.

records-rank clj

(records-rank db)

(records-rank db params)

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

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

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 descending by order

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, descending by size

table-size clj

(table-size db)

(table-size db params)

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

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

tables clj

(tables db)

(tables db params)

(tables db params options & command-options)

List all table objects in current database

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), descending by size

unused-indexes clj

(unused-indexes db)

(unused-indexes db params)

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

Unused and almost unused indexes

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