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. The SET and the query have to run on the same connection, so this library delegates them to your repo. Define an unsafe_internals_query!/3 function on your Ecto repo that runs both in a single transaction:

def unsafe_internals_query!(query, params, query_opts) do
  transaction(fn ->
    query!("SET allow_unsafe_internals = true", [], query_opts)
    query!(query, params, query_opts)
  end)
end

Just be aware these particular queries incur an extra round trip.

Phoenix.LiveDashboard

Phoenix.LiveDashboard's built-in Ecto Stats page picks an extras module from each repo's adapter (Ecto.Adapters.Postgres -> EctoPSQLExtras, and so on). A CockroachDB repo reports Ecto.Adapters.Postgres, so it would be routed to EctoPSQLExtras, whose PostgreSQL-only queries don't work against CockroachDB.

Phoenix.LiveDashboard lets you override that per repo by listing it as a {repo, info_module} tuple in :ecto_repos. EctoCockroachExtras implements the same API as EctoPSQLExtras, so point your CockroachDB repo at it:

live_dashboard "/dashboard",
  ecto_repos: [{MyApp.Repo, EctoCockroachExtras}]

Mixed setups work too - keep Postgres repos as bare modules and use a tuple only for the CockroachDB repo:

live_dashboard "/dashboard",
  ecto_repos: [MyApp.PostgresRepo, {MyApp.CockroachRepo, EctoCockroachExtras}]

The {repo, info_module} form isn't on Hex yet (latest release is 0.8.7); until it ships, depend on Phoenix.LiveDashboard from GitHub in your app's mix.exs:

{:phoenix_live_dashboard, github: "phoenixframework/phoenix_live_dashboard"}

What's not included

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