EctoCockroachExtras

Elixir library exposing CockroachDB cluster and database statistics for use from IEx, scripts, or Phoenix.LiveDashboard.

It mimics the API of ecto_psql_extras, but ecto_psql_extras doesn't work against CockroachDB: it depends on PostgreSQL internals CockroachDB doesn't have (pg_stat_activity, pg_locks, pg_stat_statements, autovacuum), and CockroachDB's pg_catalog compatibility shims for those are unpopulated stubs. This library instead queries CockroachDB's own introspection surface: SHOW JOBS, SHOW CHANGEFEED JOBS, SHOW STATISTICS, SHOW CLUSTER SETTINGS, and the crdb_internal.* virtual tables.

Installation

def deps do
  [
    {:ecto_cockroach_extras, "~> 0.1.0"}
  ]
end

Usage

EctoCockroachExtras.jobs(MyApp.Repo)
EctoCockroachExtras.changefeed_jobs(MyApp.Repo)
EctoCockroachExtras.table_statistics(MyApp.Repo, "users")
EctoCockroachExtras.sessions(MyApp.Repo)
EctoCockroachExtras.running_queries(MyApp.Repo, args: [threshold: 5])
EctoCockroachExtras.locks(MyApp.Repo)
EctoCockroachExtras.contention(MyApp.Repo)
EctoCockroachExtras.cluster_settings(MyApp.Repo)
EctoCockroachExtras.cluster_settings(MyApp.Repo, args: [all: true, pattern: "sql.defaults%"])
EctoCockroachExtras.index_usage(MyApp.Repo)
EctoCockroachExtras.unused_indexes(MyApp.Repo)
EctoCockroachExtras.table_sizes(MyApp.Repo)
EctoCockroachExtras.index_sizes(MyApp.Repo)
EctoCockroachExtras.top_queries(MyApp.Repo)
EctoCockroachExtras.diagnose(MyApp.Repo, tables: ["users", "orders"])

By default an ASCII table is printed. Pass format: :raw to get the underlying %Postgrex.Result{} instead:

EctoCockroachExtras.jobs(MyApp.Repo, format: :raw)

You can also run a query by name:

EctoCockroachExtras.query(:cluster_settings, MyApp.Repo, format: :raw)

SHOW CLUSTER SETTINGS

cluster_settings/2 wraps SHOW CLUSTER SETTINGS (public settings only) by default. Pass args: [all: true] to switch to SHOW ALL CLUSTER SETTINGS, which also includes non-public/internal settings and one extra public column. Pass args: [pattern: "..."] to filter variable with a SQL LIKE pattern.

allow_unsafe_internals

A few crdb_internal.* virtual tables (locks, index_usage, unused_indexes) are gated behind CockroachDB's allow_unsafe_internals session variable in production/CCL builds. This library handles that automatically by running the SET and the query in the same transaction - no setup needed on your end, just be aware these particular queries incur an extra round trip.

Phoenix.LiveDashboard

Phoenix.LiveDashboard's built-in Ecto Stats page dispatches by repo.__adapter__(), and a CockroachDB repo reports Ecto.Adapters.Postgres just like a real Postgres repo - so it gets wrongly routed to EctoPSQLExtras, not this library. Use the page shipped with this library instead:

live_dashboard "/dashboard",
  additional_pages: [
    cockroach_stats: {EctoCockroachExtras.LiveDashboardPage, repos: [MyApp.Repo]}
  ]

This requires adding :phoenix_live_dashboard as a dependency of your application (it's an optional dependency of this library).

What's not included (v1)

No CockroachDB equivalent, or lower priority for now: duplicate_indexes, records_rank, table_foreign_keys, missing_fk_indexes, missing_fk_constraints, table_schema, bloat, vacuum_stats, ssl_used, extensions, kill_all/cancel-session support, and multi-node {repo, node} dispatch (all repo access is local-node only).

Testing

The test suite runs real queries against a live CockroachDB instance - nothing is mocked. It defaults to localhost:26257 insecure; override with COCKROACH_HOST, COCKROACH_PORT, COCKROACH_DATABASE, COCKROACH_USERNAME, COCKROACH_SSL env vars.

Start a local instance if you don't already have one running:

docker run -d --name crdb -p 26257:26257 cockroachdb/cockroach:v26.2.0 start-single-node --insecure

Then:

mix deps.get
mix test

License

MIT