Return the MCPg server version, access mode, transport, database connection status, and PostgreSQL wal_level / effective_wal_level (the latter is None on PG ≤ 18 where the GUC doesn't exist; on PG 19+ a divergence between the two indicates a reload is still pending).

Return a high-level summary of what mcpg can do, organised into capability buckets (schema introspection, query execution, vector search, RAG telemetry, audit trail, migrations, time-series, etc.). Call this first when discovering mcpg's surface — it's much more compact than walking the full tool catalogue. Returns an object with headline, version, tool_count, capability_count, and a capabilities list, where each capability has id, name, summary, detail, headline_tools (top 3-6 tools to reach for first), tool_count, and all_tools (the full list in that bucket). Read-only; no database access. Pair with list_tools (MCP-protocol) when you need every tool's full schema.

Return the full registered schema for one MCP tool by name — description, input_schema, output_schema, and which capability bucket it belongs to. Use this when an agent hits a tool error and needs to verify the call shape without re-walking the full describe_self payload (handy when the transport surfaces only tools/call, not tools/list). Returns registered=false plus a did_you_mean suggestion list when the name isn't on this server. Read-only; no database access. Example: describe_tool(name='run_select')

Return the in-process Prometheus-format metrics for this MCPg server. Three series: mcpg_tool_calls_total (counter by tool / status), mcpg_tool_duration_seconds (histogram by tool with sum and count). Useful when the HTTP transport's /metrics endpoint is unreachable (e.g. running over stdio) or to fetch via the MCP protocol itself.

List database schemas, excluding PostgreSQL's own schemas unless include_system is true. Returns a list of objects with name.

Example: list_schemas(include_system=false)

List the tables and views in a schema, flagging partitioned tables and partitions. Returns a list of objects with name, type ('table' or 'view'), partitioned, is_partition.

Example: list_tables(schema='public')

Describe the columns of a table, in ordinal order. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change). Returns a list of objects with name, data_type, nullable, default, and vector_dimension (set only for pgvector vector(N) columns).

Example: describe_table(schema='public', table='users')

List the indexes defined on a table. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change). Returns a list of objects with name, method (btree / gin / gist / brin / hash / spgist / hnsw / ivfflat / …), definition (the CREATE INDEX statement), and partitioned.

Example: list_indexes(schema='public', table='users')

List a table's constraints — primary/foreign keys, unique, check, exclusion. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change). Returns a list of objects with name, type, and definition (the constraint SQL).

Example: list_constraints(schema='public', table='orders')

List foreign keys in a schema, resolved to columns and referenced table. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change). Returns a list of objects with name, from_table, from_columns, to_schema, to_table, to_columns.

Example: list_foreign_keys(schema='public')

List the views and materialized views in a schema, with their definitions. Returns a list of objects with name, materialized (bool), and definition (the SELECT SQL).

List the functions and procedures defined in a schema. Returns a list of objects with name, kind ('function' or 'procedure'), arguments (signature string), returns (return-type string), and language (plpgsql / sql / c / etc.).

List the user-defined triggers on a table. Returns a list of objects with name, function (the called function's qualified name), and definition (the CREATE TRIGGER SQL).

Describe how a table is partitioned (strategy and bounds) and list its partitions. Returns an object with partitioned (bool), strategy ('range' / 'list' / 'hash' or null), and partitions (a list of {name, bounds} for each partition).

List the database roles and their attributes, excluding PostgreSQL's own roles unless include_system is true. Returns a list of objects with name, superuser, create_role, create_db, can_login, replication, bypass_rls, connection_limit, member_of.

List the privileges granted on a table — who may do what to it. Returns a list of objects with grantee, privilege (SELECT / INSERT / UPDATE / DELETE / TRUNCATE / REFERENCES / TRIGGER), grantable (bool — may the grantee regrant), and grantor.

List the Row-Level-Security policies on a table, and whether row security is enabled. Returns an object with rls_enabled (bool) and policies — a list of {name, command (SELECT/INSERT/UPDATE/DELETE/ALL), permissive (bool), roles, using_expression, check_expression}.

List the sequences defined in a schema, with their range, increment, and last value. Returns a list of objects with name, data_type, start_value, min_value, max_value, increment, cycle (bool), last_value.

List the enum types in a schema, with their labels in sort order. Returns a list of objects with name and values (list of label strings, in the order defined).

List the domain types in a schema, with base type, default, and check constraints. Returns a list of objects with name, base_type, nullable, default, and constraints (list of CHECK clauses).

List the standalone composite types in a schema with their attributes. Returns a list of objects with name and attributes (list of {name, data_type} for each field).

List the foreign-data wrappers installed in the database. Returns a list of objects with name, handler (qualified function name), validator, and options (dict of wrapper-specific options).

List the foreign servers defined in the database, with their FDW and options. Returns a list of objects with name, wrapper (foreign-data wrapper name), type, version, and options (dict of server options).

List the foreign tables in a schema, with their server and options. Returns a list of objects with name, server (foreign-server name), and options (dict of per-table options).

List role-to-foreign-server mappings; the catch-all appears as user='public'. Returns a list of objects with user (role name or 'public'), server (foreign-server name), and options (dict of mapping options, e.g. credentials).

List logical-replication publications with the tables and operations they include. Returns a list of objects with name, owner, all_tables (bool), publishes_insert / publishes_update / publishes_delete / publishes_truncate (bools), and tables (list of schema.table strings).

List logical-replication subscriptions; requires superuser to see any rows.

List the extensions installed in the database. Returns a list of objects with name and version.

List every extension available to the database, with whether it is installed. Returns a list of objects with name, default_version, installed_version (null when not installed), and installed (bool).

List every GENERATED ALWAYS AS (...) STORED column in a schema, with its data type, the underlying expression, and whether it's stored or virtual. PostgreSQL today supports only the stored form; the kind field is reported anyway so the response shape is forward-compatible when PG adds virtual columns. Returns a list of objects with schema, table, column, data_type, expression, kind ('stored' today; reserved for 'virtual').

List currently-held and waiting locks, joined with backend state from pg_stat_activity. Ordered by (granted ASC, pid) so waiting locks float to the top. Returns lock type, mode, qualified relation name when applicable, transaction / virtualxid, the application_name + state + wait event of the owning backend, and the first 200 chars of its query. Read-only.

Return (blocked, blocking) backend pairs via pg_blocking_pids. Each row pairs a backend waiting on a Lock with one PID holding the lock that's preventing progress. Cycles are possible (A blocks B, B blocks A); render with care. Read-only.

Walk and reconstruct the lock-wait graph of the database. Detects deadlock cycles, traces linear blocking paths to their root blockers, and renders a Mermaid flowchart representing the lock dependency graph. Read-only. Returns an object with cycles (list of detected cycle PID lists), paths (linear blocking paths as PID lists), roots (root blocker PIDs), nodes (dict keyed by PID with per-backend lock detail), and mermaid (the pre-rendered flowchart string).

Read the pg_stat_io view (PostgreSQL 16+). Reports per (backend_type, object, context) cumulative I/O activity — reads, writes, extends, evictions, hits, fsyncs. Useful for spotting buffer-cache misses and write amplification. On PostgreSQL 14 / 15 the view doesn't exist, so the tool returns available=false and an empty list.

Read a high-level summary of the PostgreSQL shared buffer cache usage. Reports total buffers, free/used buffers, dirty buffers, and average usage count. Requires the pg_buffercache extension. If not installed, returns available=false.

Read the list of database relations taking up the most space in the PostgreSQL shared buffer cache. Reports buffered size, percentage of shared buffers, percent of relation buffered, average usage count, and dirty pages. Allows filtering by schema. Requires the pg_buffercache extension. If not installed, returns available=false.

Read Write-Ahead Log (WAL) records information over a specified LSN range. Requires the pg_walinspect extension. If not installed, returns available=false.

Read Write-Ahead Log (WAL) record statistics over a specified LSN range, grouped by resource manager or record type. Requires the pg_walinspect extension. If not installed, returns available=false.

Report WAL-archiving health from pg_stat_archiver + the archive-mode GUCs — the early-warning signal for a failing archive_command / archive_library (full archive volume, bad object-store credentials, network partition), which otherwise silently accumulates WAL in pg_wal/ until the volume fills. Companion to read_pg_wal_records (which inspects WAL records); this covers the WAL archive. Read-only; never raises. The archive_command string is NOT echoed (it can embed credentials) — only a boolean archive_command_set. Returns an object with available, archiving_enabled, archive_mode, archive_command_set, archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset, healthy (bool — false when archiving is on and the latest attempt failed), and detail.

Example: get_wal_archive_status()

Assess whether the cluster is ready for point-in-time recovery (PITR) — the one-call 'could I actually recover to an arbitrary point right now, and if not what's missing?' check. Composes get_wal_archive_status (5.2) with the GUCs that gate PITR: continuous archiving must be healthy, wal_level >= replica, max_wal_senders >= 1 (so pg_basebackup can stream a base backup), and full_page_writes on (torn-page safety during replay). Read-only advisor — changes nothing, emits no secrets. Returns an object with available, ready (bool — true only when all gates pass), wal_level, archiving_healthy, gates (list of objects with name, ok, observed, remediation), remediation (ordered fixes for failing gates), and detail.

Example: check_pitr_readiness()

Return a highly condensed, token-efficient text summary of a schema's tables, columns, primary keys, nullability, and relations to save context window tokens. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change).

Query and summarize historical migrations applied to the database by popular migration frameworks (Alembic, Flyway, Diesel, Django, Prisma, Golang Migrate, Goose, Sequelize). Allows filtering by schema. Returns an object with one optional list per detected framework: alembic, flyway, diesel, django, prisma, golang_migrate, goose, sequelize. Each entry is null when the framework's table isn't present; otherwise it carries the framework-specific row shape (e.g. alembic has {version_num}; flyway has {installed_rank, version, description, type, ...}).

Render a Mermaid ER diagram for a schema. Views and foreign tables are excluded; partitions are excluded by default — pass include_partitions=true to draw each partition as its own entity. Returns the Mermaid erDiagram as a string ready to paste into a Markdown block.

Example: generate_schema_diagram(schema='public', include_partitions=false)

Build a Mermaid graph LR of foreign-key cascade chains in a schema. Each edge runs from the referencing table to the referenced table, labelled with the cascade action(s) on DELETE / UPDATE. By default only FKs with at least one CASCADE / SET NULL / SET DEFAULT action are included — those are the ones that produce a write blast radius. Pass include_all=true to include NO ACTION / RESTRICT FKs too (full FK topology view). Cross-schema FK targets are rendered as separate nodes prefixed with their schema. Returns the Mermaid graph LR diagram as a string.

Generate a detailed Markdown reference of a schema's tables, columns, constraints, indexes, views, foreign tables, and custom enums along with comments / descriptions. Optional include_samples fetches a few distinct, non-null values for each column. Returns a single Markdown document as a string.

Example: generate_schema_docs(schema='public', include_samples=true)

Return the structural diff between two schemas — tables/columns/indexes/constraints/foreign-keys added, removed, or changed. Base tables only; views and custom types are not compared. Renames surface as a paired add + remove.

Example: compare_schemas(left_schema='public', right_schema='staging')

Recommend an ivfflat or hnsw configuration for a pgvector column. Reads the live row count and column dimension, applies the standard pgvector heuristics, and returns the parameters plus a ready-to-run CREATE INDEX statement. Requires the vector extension.

Measure recall@k of an existing pgvector index against a brute-force ground truth (function-form distance, which pgvector documents as non-indexed). Returns the mean overlap over a sample of rows from the table. Requires the vector extension.

Generate a DDL plan that converts a pgvector vector(N) column to halfvec(N) — halving per-element storage (4 → 2 bytes) with typically negligible recall impact at d ≥ 768. Reads the column's current type + dimension from the catalog, finds every index on the column, and emits an ordered migration_sql plan: DROP each affected index, ALTER COLUMN to halfvec(N) via a USING cast, then recreate each index with its halfvec opclass. Also returns a mirror rollback_sql that restores the original vector(N) type plus the original index definitions. Nothing is executed — feed the plan through the shadow-migration workflow (prepare_migration / validate_migration_schema) before applying. Returns already_halfvec=true (and an empty plan) when the column is already halfvec, and refuses any index whose opclass has no halfvec sibling rather than rewriting it incorrectly. Requires the vector extension.

Sweeps ef_search values to measure the latency and recall trade-off curve for a given pgvector query vector against exact brute-force ground truth. Requires the vector extension. Returns a list of objects with ef_search, recall_at_k, mean_latency_ms, and p95_latency_ms — one row per ef_search value tested.

Recommend an hnsw.ef_search value for a target recall@k — the actionable companion to analyze_hnsw_recall. Samples sample_queries rows (default 10) as query vectors, builds an exact brute-force top-k ground truth per query, sweeps ef_values (default 16/32/64/128/256) measuring mean recall@k and p50/p95 latency at each, and recommends the smallest value clearing target_recall (default 0.95). Unlike the single-query curve tool, this VERIFIES an HNSW index actually exists on the column (returns has_hnsw_index=false with guidance otherwise — a sweep without one just measures sequential scans). The query row is excluded from its own results. Requires the vector extension. Returns an object with available, has_hnsw_index, index_name, metric, k, target_recall, sample_queries, recommended_ef_search (int or null), detail, and sweep (list of objects with ef_search, mean_recall_at_k, p50_latency_ms, p95_latency_ms, meets_target).

Example: recommend_hnsw_ef_search(schema='public', table='docs', column='embedding', k=10, target_recall=0.95)

Recommend an ivfflat.probes value for a target recall@k — the IVFFlat analogue of recommend_hnsw_ef_search. Samples sample_queries rows (default 10) as query vectors, builds an exact brute-force top-k ground truth per query, sweeps probe_values (default 1/2/5/10/20/50) measuring mean recall@k and p50/p95 latency at each, and recommends the smallest value clearing target_recall (default 0.95). VERIFIES an IVFFlat index actually exists on the column (returns has_ivfflat_index=false with guidance otherwise — a sweep without one just measures sequential scans). The query row is excluded from its own results. Requires the vector extension. Returns an object with available, has_ivfflat_index, index_name, metric, k, target_recall, sample_queries, recommended_probes (int or null), detail, and sweep (list of objects with probes, mean_recall_at_k, p50_latency_ms, p95_latency_ms, meets_target).

Example: recommend_ivfflat_probes(schema='public', table='docs', column='embedding', k=10, target_recall=0.95)

Recommend a pgvector distance metric (cosine | l2 | inner_product) from the embedding-magnitude distribution. Samples up to sample_size non-NULL rows of schema.table.column, computes each embedding's L2 norm, and applies a small heuristic: pre-normalised (CV < 5% and mean ≈ 1.0) → inner_product; nearly-constant magnitude but not unit-norm → cosine (same ranking as L2, safer default); variable magnitude → cosine (normalises out heterogeneous sources). Returns the metric + a rationale + the underlying distribution stats. Reports available=false if the pgvector extension is not installed.

Find the k rows in target_schema.target_table most similar to a specific row in source_schema.source_table. Locates the source row via source_id_column = source_id_value, reads its embedding from source_embedding_column, then issues a pgvector k-NN query against target_embedding_column. Both columns must be vector(N) of the same N — verified from the catalog up front so a mismatch fails with a clear error rather than a cast error. Useful for entity-resolution / linking across tables whose embeddings come from different models but share a dimension. Returns source_embedding_found=false when no row matches the id value. Reports available=false if pgvector is not installed.

Context-packed k-NN retrieval (a one-shot RAG building block). Runs a pgvector k-NN against schema.table.embedding_column for a caller-supplied query_vector (no embedding model needed), then expands each hit one hop along foreign keys and returns the hit row + its related parent / child records in one object. Parents (when include_parents, default true) are the rows a hit references via outbound FKs; children (when include_children, default true) are rows referencing the hit via inbound FKs, capped at max_related (default 5) per FK. Limitations: 1 hop only; inbound (child) expansion is same-schema only. The embedding column is dropped from every returned row. Requires the vector extension. Returns available, dimension, detail, and hits (each with distance, row, and related — a list of fk_name/direction/related_schema/related_table/rows).

Example: retrieve_with_context(schema='public', table='docs', embedding_column='embedding', query_vector=[0.1, 0.2, 0.3], k=5)

k-means cluster a pgvector column. Samples up to sample_size (default 5000) non-NULL rows of schema.table.embedding_column, runs Lloyd's algorithm with k-means++ seeding (seed for determinism), and returns centroids (one per cluster, with size) + assignments (per-row cluster index + distance). When id_column is set each assignment carries that column's value; otherwise the row's positional sample index. metric='l2' (default — squared Euclidean) or 'cosine' (vectors normalised; centroids re-normalised every iteration). k >= 2 and there must be at least 2k parseable rows. Reports available=false if pgvector is not installed.

Example: cluster_vectors(schema='public', table='docs', embedding_column='embedding', k=8, metric='cosine')

Flag pgvector rows whose embedding sits far from any cluster centroid. Samples up to sample_size (default 5000) non-NULL rows of schema.table.embedding_column, clusters them with k-means (same engine as cluster_vectors), then per cluster computes a z-score on the distance from each row to its centroid and flags rows whose z-score exceeds zscore_threshold (default 3.0). Per-cluster scoring catches rows that are weird-for-their-group rather than weird-overall, which is usually what 'find outliers' should mean. Returns outliers sorted by z-score descending (capped at max_results), total_outliers (the unclipped count), and cluster_stats (per-cluster mean / std of within-cluster distances). When id_column is set each outlier carries that column's value; otherwise the row's positional sample index. k >= 2 and there must be at least 2k parseable rows. Reports available=false if pgvector is not installed.

Compare two time windows of a pgvector column and flag distributional drift. Samples up to sample_size (default 5000) non-NULL embeddings from each window (filtered by timestamp_column), computes the centroid (per-dimension mean vector) and L2-norm distribution of each, then reports the cosine distance between the two centroids (the main drift signal), the relative change in mean / std of the L2-norm distribution, and a boolean drift_detected that flips when cosine distance exceeds drift_threshold (default 0.05). Each window is treated as a half-open [start, end) interval. Useful for ops monitoring of embedding pipelines — an upstream model swap typically shows up as a large centroid cosine distance even if the norm distribution looks stable. insufficient_data is returned distinctly from drift_detected=false when either window is empty. Reports available=false if pgvector is not installed.

Example: monitor_embedding_drift(schema='public', table='docs', embedding_column='embedding', timestamp_column='created_at', baseline_start='2026-01-01', baseline_end='2026-02-01', current_start='2026-02-01', current_end='2026-03-01')

Cross-backend retrieval-quality report for a pgvector or pg_turboquant ANN index. Detects the backend (HNSW / IVFFlat / turboquant), sweeps the matching per-backend knob (ef_search / probes / candidate_limit) across a multiplier curve, computes recall@k vs a brute-force exact baseline, Spearman + Kendall rank correlation, per-query p50/p95 wall-clock latency, and (for turboquant) the page-pruning ratio from tq_last_scan_stats. Emits findings: baseline_recall_low (CRITICAL), rerank_lift_flat / rerank_lift_steep / ranking_degraded / pruning_ineffective (WARNING). Burns sample_size x (1 + len(candidate_multipliers)) queries; ad-hoc diagnostic, not a cron tool. Requires the vector extension; turboquant-arm metrics require pg_turboquant.

Per-query Spearman + Kendall correlation between bi-encoder and cross-encoder ranks, aggregated across queries in the window. Low correlation = the reranker is actively reordering (doing real work); high correlation = the reranker mostly confirms the bi-encoder order. Optional model / retrieval_index filters. Surfaces reranker_idle (WARNING) when the reranker rarely changes ordering. Reads from mcpg_rag.rerank_events; returns a report with zero counts when the table doesn't exist or the window is empty.

Jaccard overlap between top-K-by-bi-rank and top-K-by-cross-rank per query, aggregated. High mean Jaccard means the reranker isn't actually changing the top-K membership. Surfaces topk_stable (WARNING) when the rerank is barely earning its place at this K. Reads from mcpg_rag.rerank_events; returns a report with zero counts when the table doesn't exist or the window is empty.

Equal-width histogram of cross_encoder_score values over the window plus the top-decile share. Surfaces score_clustering (WARNING) when the reranker isn't discriminating (more than half of scores land in the top decile of the range). Reads from mcpg_rag.rerank_events. Returns an object with window_days, event_count, histogram (list of counts), bucket_edges (list of bucket boundaries), top_decile_share, and findings (list of advisory findings).

NDCG@k under bi-encoder ordering vs cross-encoder ordering, averaged across labeled queries (ground_truth_relevance IS NOT NULL). Reports the delta (cross - bi) — positive = the rerank is adding real ranking quality, negative = it's hurting. Surfaces rerank_hurts_ndcg (CRITICAL) or rerank_lifts_ndcg (GOOD evidence). Reads from mcpg_rag.rerank_events; returns zero counts when no labeled rows exist in the window.

Roll-up advisor over the four analytics for one window. Returns a single headline summary + the full list of findings. Built from whichever combination of reranker_idle / topk_stable / score_clustering / rerank_hurts_ndcg / rerank_lifts_ndcg fires. Also feeds the RAG Reranker Pipeline category in audit_database. Reads from mcpg_rag.rerank_events.

Compute corpus-percentile thresholds from accumulated mcpg_rag.efficiency_observations history. Phase E currently adapts three thresholds: baseline_recall_low (p10 of recall_baseline), ranking_degraded_spearman (p10 of spearman), and pruning_ineffective (p10 of pages_pruned_ratio_p50). The remaining four thresholds stay at their hardcoded defaults. Filters by days window + optional backend / metric / k so callers can ask 'what's normal for HNSW+cosine+k=10 in this deployment' vs 'what's normal globally'. Falls back to defaults (with derived_from_corpus=false) when the corpus is smaller than the minimum required. Returns an object with corpus_size, derived_from_corpus (bool), and the threshold fields (baseline_recall_low, baseline_recall_low_adapted, ranking_degraded_spearman, ranking_degraded_spearman_adapted, pruning_ineffective, pruning_ineffective_adapted, rerank_lift_flat_delta, rerank_lift_steep_low, rerank_lift_steep_high, and ranking_degraded_recall).

Read a PostgreSQL schema and emit a valid Prisma .prisma schema string (mirrors prisma db pull). Covers tables, columns, primary/foreign keys, unique constraints, indexes, and enums. Views, foreign tables, partitions, triggers, functions, and policies are out of scope; unmappable types fall back to Unsupported("..."). Returns the rendered schema.prisma source as a single string.

Read a PostgreSQL schema and emit a Drizzle ORM TypeScript schema string (drizzle-orm/pg-core). Covers tables, columns with PG-native types, primary/foreign keys, unique constraints, indexes, defaults, and enums. Single-column FKs emit column-level .references(); composite FKs are a documented v1 gap. Views, foreign tables, partitions, triggers, and functions are out of scope. Returns the rendered TypeScript schema.ts source as a single string.

Read a PostgreSQL schema and emit a Diesel ORM (Rust) schema.rs. One table! macro per table with column SQL types, Nullable<T> for nullable columns, plus joinable! declarations for single-column intra-schema FKs and an allow_tables_to_appear_in_same_query! macro so multi-table joins type-check. Enum types are emitted as Text-backed wrapper enums in a pg_enum module so the output works without diesel_derive_enum. Composite FKs are a documented v1 gap.

Read a PostgreSQL schema and emit a jooq-codegen configuration XML pointing at it. Unlike the other exporters, jOOQ generates Java code itself from a live database — the artefact here is the configuration file the user feeds to mvn jooq-codegen:generate (or the Gradle task). The XML lists every base table explicitly via an regex, excludes MCPg's bookkeeping tables, and emits a for every json / jsonb column so they map to org.jooq.JSON / org.jooq.JSONB out of the box. Default Java package is com.example.jooq; override via the target_package arg.

Read a PostgreSQL schema and emit Ent (Go) Schema struct files — one .go file per table. Each file exports a struct that lists field.X(...) calls for every column, edge.To(...) for single-column intra-schema FKs, and field.Enum().Values() for enum-typed columns. Composite FKs are a documented v1 gap. Returns a JSON object {filename: source} so the agent can write each file.

Read a PostgreSQL schema and emit Ecto (Elixir) schema modules — one .ex file per table, named after the singularised table. Each module uses Ecto.Schema with field declarations, belongs_to for single-column intra-schema FKs, and timestamps() when both inserted_at and updated_at exist. The Elixir top-level module is configurable via app_module (default MyApp). Returns a JSON object {filename: source} so the agent can write each file.

Read a PostgreSQL schema and emit a SQLAlchemy 2.0 declarative models file (DeclarativeBase + Mapped[T] + mapped_column). Covers tables, columns with PG-native types (incl. jsonb via sqlalchemy.dialects.postgresql.JSONB), primary keys, single-column FKs via ForeignKey(), unique constraints (column-level + composite via table_args), defaults, and enums (emitted as Python enum.Enum classes). Composite FKs are a documented v1 gap. Returns the rendered Python models.py source as a single string.

Read a PostgreSQL schema and emit a sqlc-friendly schema.sql (plain DDL). Order: CREATE SCHEMA, CREATE TYPE for each enum, CREATE TABLE statements (columns only), ALTER TABLE ADD CONSTRAINT (PK / unique / check / foreign key in that order), then CREATE INDEX for non-constraint indexes. The file replays cleanly against an empty database so FKs land after all referenced tables exist. In-process — no MCPG_ALLOW_SHELL needed. Returns the rendered schema.sql text as a single string.

Run a set of catalog-driven advisor rules against a schema and return the aggregated findings. Rules cover missing primary keys, unindexed foreign keys, duplicate indexes, and nullable timestamps without time zone. Advisory only — no writes.

Find tables and indexes with zero scans since pg_stat was last reset — a strong signal of dead code, but NOT a verdict. Tables report seq+idx scan counts, write counts, and estimated row count; indexes report size and definition. Excludes PRIMARY KEY and UNIQUE indexes (PG needs those regardless of scans). Run this after the database has been hot for a meaningful period — fresh stats produce false positives. Returns an object with tables (list of candidate tables with their stats) and indexes (list of candidate indexes with size and definition).

Flag columns whose names or types look like they hold sensitive data (passwords, tokens, PII, financial info, health records). Pure heuristic — no row sampling, no value introspection. Categories: credential, financial, contact, identifier, health, government_id, location. Each finding carries a confidence (high / medium / low) so an agent can filter for a first review pass. Treat as a SIGNAL, not a verdict — a column named email_template_id matches the email pattern but isn't itself an email address. Returns an object with findings (list of {schema, table, column, data_type, category, confidence, matched_pattern}) and summary counts by category.

Lint table / column / index naming in a schema. Detects the majority case style (snake_case / camelCase / PascalCase / SCREAMING_SNAKE) per schema and per table, then flags outliers. Also flags indexes whose names do not start with a conventional prefix (idx_, ix_, pk_, uq_, fk_ by default). Findings carry the offender's style and the detected majority — agents can use the style field to filter for renames vs accept-as-is. Pure read. Returns an object with schema_style (detected majority), findings (list of style outliers), and index_prefix_findings (indexes with non-conventional prefixes).

Test what an RLS-bound role can read from a table. Reports whether RLS is enabled on the table, lists the policies that apply to the given role, counts the rows the role can read, and returns up to sample_size rows so the agent can inspect them. Runs as the target role inside a READ ONLY transaction — no writes can leak. Pure read.

Generate synthetic INSERT statements for a table — typed values respecting column type, NOT NULL, and DEFAULT. Returns the SQL as strings; does NOT execute it. Useful for seeding dev / staging environments. The generator is deterministic when a seed is provided. Foreign keys are NOT resolved — the caller must pre-seed referenced rows or drop the FK before applying. Hard cap of 10000 rows per call. Pure read (the actual writes go through run_write under unrestricted mode).

Generate openCypher CREATE/MERGE statements that project a relational schema into an Apache AGE property graph — rows become vertices (one label per table), foreign keys become edges. EMITS the Cypher for review; NEVER executes it (like generate_test_data). With row_limit=0 (default) it returns a schema-level template plan (one CREATE per label, one MERGE per edge type, $prop placeholders) reading only the catalog. With row_limit>0 it also emits concrete per-row statements (values escaped, NULLs omitted, capped at 1000 rows/table). Tables without a primary key still get node CREATEs but their edges are skipped (they can't be reliably MATCHed). NOTE: AGE materialises the data (this is a LOAD, not a virtual view); run the node statements before the edge statements; the projection is 1-hop faithful to the FK graph. Returns an object with available (AGE installed, advisory), schema, graph_name, row_limit, node_labels (list of label, source_table, key_columns, property_columns), edge_types (list of edge_type, from_label, to_label, from_key, to_key, fk_name), cypher_statements (generated, never executed), warnings, and detail.

Example: generate_graph_projection(schema='public', graph_name='g', row_limit=0)

Generate ONE realistic test row for a table — catalogue-aware. Skips identity / generated columns (server fills them in), samples one existing row from each referenced table for FK columns (so the row inserts cleanly), and uses column-name heuristics (*_email → user_N@example.com, *_url → https://example.com/r/N, *_at → recent timestamp, etc.) to make values look like data. Sibling of generate_test_data (bulk) — designed for the shadow-migration workflow where a single realistic row matters more than volume. Returns an object with insert_sql (one ready-to-execute INSERT), columns (per-column ColumnFill with sql_literal + heuristic explanation), schema, table. Does NOT execute the INSERT — caller applies via run_write when ready.

Example: generate_test_row_for(schema='public', table='orders', seed=42)

Surface hot-path inefficiencies from the audit log. Reads mcpg_audit.events over the last lookback_minutes (default 60, capped at 1440) and flags tools called more than hot_threshold times (default 10). Catalogue-listing tools (list_tables / list_schemas / list_indexes / etc.) get a redundant_listing finding pointing at get_compact_schema; other tools get a hot_repeated_call finding suggesting caching. Idle sessions get an idle_session finding. When mcpg_audit.events doesn't exist (audit subsystem off) returns audit_table_present=False with a diagnostic. Returns an object with audit_table_present (bool), events_examined (int), lookback_minutes, findings (list of objects with reason, tool, call_count, suggestion), and detail.

Example: analyze_session_cost(lookback_minutes=30, hot_threshold=15)

Empirically curate describe_self's per-bucket headline_tools from the audit log. Reads mcpg_audit.events over the last lookback_days (default 7, capped at 90), groups successful calls by capability bucket, and reports the top-top_n (default 6) tools per bucket with newcomers (recommended but not in the hand-curated current list) and departures (currently headlined but not in the recommendation). The output is a REVIEWABLE recommendation, not an auto-applied override — operators decide whether to update mcpg.about.CAPABILITIES. Returns audit_table_present=False with a diagnostic when the audit subsystem is off. Returns an object with audit_table_present, lookback_days, top_n, events_examined, detail, and buckets (list of objects with bucket_id, current, recommended, newcomers, departures, call_counts).

Example: recommend_headline_tools(lookback_days=14, top_n=6)

Flag sequences nearing their ceiling — serial / identity / explicit sequences whose last_value / max_value exceeds warning_pct (default 80) or critical_pct (default 95). Sequence overflow is catastrophic and silent until the next nextval() raises 'reached maximum value' — the int4 serial ceiling (2^31-1) is hit far more often than expected. Pure read; available=false on PG < 10 (no pg_sequences). Returns an object with available, total_examined, warning_pct, critical_pct, detail, and sequences (at-risk only, sorted by used_pct desc — each with schema, sequence, last_value, max_value, used_pct, remaining, status).

Example: audit_sequences(warning_pct=80, critical_pct=95)

Sanity-sweep postgresql.conf via pg_settings. Flags dangerous toggles (fsync=off, full_page_writes=off, autovacuum=off, synchronous_commit=off), cross-setting issues (maintenance_work_mem < work_mem, tiny shared_buffers, low checkpoint_completion_target), and — when total_ram_mb is supplied — RAM-relative ratios for shared_buffers / effective_cache_size (PostgreSQL can't see host RAM itself). Pure read. Returns an object with ram_aware (bool), examined_settings (list), detail, and findings (tripped rules only — each with code, setting, current, status, suggestion).

Example: audit_settings(total_ram_mb=16384)

Compute pgtune-style postgresql.conf recommendations. Pure calculator — touches no database. Given total_ram_mb (required), cpu_count (default 4), workload (one of web/oltp/dw/desktop/mixed, default mixed), storage (one of ssd/hdd/san, default ssd), and an optional max_connections override, returns recommended values for shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers, min_wal_size/max_wal_size, checkpoint_completion_target, default_statistics_target, random_page_cost, effective_io_concurrency, and the parallel-worker knobs. Memory fields are postgres-ready strings; settings is the same data as a flat {guc: value} dict for direct rendering. Pair with audit_settings (audit first, then size).

Example: recommend_postgres_conf(total_ram_mb=16384, cpu_count=8, workload='oltp', storage='ssd')

Analyze a SQL query for syntax anti-patterns and performance issues using EXPLAIN plan costs and index scans, returning an optimized version.

Return a one-stop snapshot of a table: columns, primary key, foreign keys, every other constraint, indexes, storage + row-count + last-vacuum/analyze stats, and (optionally) a short sample of rows. Replaces what would otherwise be 4-5 individual tool calls. Set sample_rows=0 on wide / jsonb-heavy tables where the sample isn't useful.

Example: summarize_table(schema='public', table='users', sample_rows=5)

Diagnose why a SQL query might be slow, in one call. Runs EXPLAIN (FORMAT JSON) — does NOT execute the query — walks the plan tree, snapshots concurrent active queries + blocking lock pairs, reads the cluster-wide cache hit ratio, and produces categorised suggestions (plan / contention / cache / maintenance). Read-only; safe to run on a statement the agent doesn't want to materialise yet.

Example: why_is_this_slow(sql='SELECT * FROM orders WHERE customer_id = 42')

Run a read-only SQL query and serialise its rows to CSV or JSON. Reuses the SQL-safety checks of run_select. Truncates at limit rows and flags it in the result so callers can paginate.

Example: export_query(sql='SELECT id, email FROM users', format='csv', limit=10000)

Serialise every row in schema.table (up to limit) to CSV or JSON. Schema and table names must be plain identifiers. Returns an object with format, row_count, truncated (bool — true when the row count hit limit), and content (the serialised payload as a string).

List recent rows from mcpg_audit.events (newest first). Returns an empty list when MCPG_AUDIT_PERSIST has never been turned on (no audit table yet). Optionally filter by tool name.

Verify the HMAC-SHA256 signature chain of persisted audit events in mcpg_audit.events. Returns an object with verified (bool), events_checked (int), the first_event_id and last_event_id covered by the walk, and (on failure) error and first_invalid_id pointing at where the chain broke.

Validate and run a read-only SQL query. Writes, DDL, and other unsafe statements are rejected before execution.

Example: run_select(sql='SELECT id, email FROM users LIMIT 10', max_rows=1000)

Run a read-only SELECT with an elevated, bounded work_mem (and optionally maintenance_work_mem) for THIS statement only. Useful for heavy analytical SELECTs (large sorts / hash joins / GROUP BY) that spill to disk under the default work_mem. The knob is set via SET LOCAL inside the same read-only transaction, so it never leaks back into the pool. Both knobs must match ^\d+(kB|MB|GB)$ and are hard-capped at 2GB (unbounded values are an OOM risk). SQL is validated read-only by the same allowlist as run_select. SET LOCAL only affects transactional statements — non-transactional maintenance (CREATE INDEX CONCURRENTLY, VACUUM) is out of scope.

Example: run_select_tuned(sql='SELECT a, count(*) FROM big GROUP BY a', work_mem='256MB')

Run up to parallel_limit read-only SELECTs concurrently. Each statement is validated by the same safety allowlist as run_select; one bad query does not abort the others — its error is captured in its own outcome slot. Useful for dashboard-style fan-out where round-trip latency dominates (e.g. fetching counters / aggregates from several tables at once). Each outcome includes an index so the caller can correlate results without relying on ordering.

Open a server-side cursor for a SELECT query. The cursor holds the result set on the server side so an agent can page through millions of rows without loading them all. SQL is validated by the same safety allowlist as run_select. Returns the cursor_id; fetch the rows with fetch_cursor and close with close_cursor (or let the 5-minute TTL clean up). Hard cap of 16 concurrent cursors.

Fetch the next batch from an open server-side cursor. exhausted=true means the FETCH returned fewer rows than requested — stop polling. batch_size defaults to 100; hard cap is 10000 per call.

Close a server-side cursor and release its dedicated connection. Idempotent — returns closed=false when the cursor was not open (already closed, expired, or never existed).

List every currently-open server-side cursor with its SQL, rows_returned so far, age in seconds, and the TTL after which it'll be auto-closed.

Return the PostgreSQL execution plan for a query. By default uses EXPLAIN (FORMAT JSON) — plan only, the query is not executed. Set io=true to switch to EXPLAIN (ANALYZE, BUFFERS, TIMING) — runs the query and includes buffer + I/O timing per node (PG 19 additionally surfaces asynchronous-I/O block counts). Validated by the same safety allowlist as run_select, so writes / DDL are rejected.

Example: explain_query(sql='SELECT * FROM orders WHERE customer_id = 42', io=true)

Summarise a query's execution plan: total estimated cost, estimated rows, node types used, and any sequentially-scanned tables. Set io=true to run EXPLAIN (ANALYZE, BUFFERS, TIMING) instead of the plan-only variant — adds actual_total_time_ms, shared_blocks_read/hit, io_read_time_ms, io_write_time_ms, and (PG 19) aio_read_blocks / aio_write_blocks rolled up across the plan tree. Reasoning about AIO needs io=true.

Example: analyze_query_plan(sql='SELECT * FROM orders WHERE customer_id = 42', io=true)

Translate a natural-language question into a read-only PostgreSQL query against schema. The LLM provider (anthropic / openai / gemini / deepseek / qwen / openrouter / perplexity, plus any operator-declared custom OpenAI-compatible provider) sees a compact brief of the schema (tables, columns, foreign keys) and is instructed to return JSON with sql and explanation. When execute=true, the generated SQL goes through the SAME safety allowlist as run_select before running — writes / DDL / multi-statement input are rejected even if the model produced them. Returns the SQL, model rationale, and (when executed) rows / columns / row_count. table_filter narrows the brief to a known subset when the question is clearly scoped. provider, when supplied, selects which configured LLM provider to call (use this to route between the configured vendors per-call when multiple are configured); when omitted, MCPg uses the default (MCPG_NL2SQL_PROVIDER, otherwise the first available in preference order anthropic → openai → gemini → deepseek → qwen → openrouter → perplexity). Call get_server_info to see which providers are configured.

Example: translate_nl_to_sql(question='top 10 customers by revenue last month', schema='public', execute=true)

Run a read-only SELECT that may take longer than the standard limit — for genuine analytical work (large aggregations, multi-table joins, window functions, DISTINCT/GROUP BY over millions of rows). Validated by the same allowlist as run_select, but executed on a DEDICATED connection pool (isolated from the fast-path tools) with an elevated, bounded timeout. Prefer run_select for ordinary queries; reach for this only when a query legitimately needs more time. timeout_ms overrides the per-call budget (clamped to the server's configured maximum, MCPG_ANALYTICAL_MAX_TIMEOUT_MS); work_mem (e.g. '256MB') elevates sort/hash memory for this statement. Runs against the primary database. Returns an object with columns, rows, row_count, and truncated (true when more rows than max_rows were produced) — same shape as run_select.

Example: run_analytical_query(sql='SELECT c, count(*) FROM big GROUP BY c', timeout_ms=180000)

Run database health checks: connection utilisation, buffer cache hit ratio, tables needing vacuum, and invalid indexes. Returns an object with status ('ok' / 'warning' / 'critical') and checks (a list of {name, status, detail} per check).

Example: check_database_health()

Rank a schema's tables and indexes by estimated bloat, worst first. By default uses a cheap catalog-only estimate (relpages vs a reltuples/row-width floor) plus the dead-tuple ratio from pg_stat_user_tables. Set precise=true to use pgstattuple / pgstatindex for an exact (but I/O-heavy) read when the extension is installed — it falls back to the estimate and reports method='estimate' if it isn't. Returns tables and indexes (each capped at limit) with per-object est_bloat_pct, plus available and method.

Example: analyze_table_bloat(schema='public', limit=20, precise=false)

List every database this MCPg server is configured to serve: the primary (the default target of every tool) plus any read-only secondaries from MCPG_SECONDARY_DATABASE_URLS. Read-capable tools accept an optional database argument naming one of these ids; omitting it targets the primary. Secondaries are read-only (PostgreSQL-enforced) — writes / DDL / shell / migrate always run against the primary. Returns an object with primary_id, database_ids (primary first), and databases — a list of objects with id, is_primary, read_only, reachable (a live SELECT 1 probe), and detail (an error string when unreachable).

Run a deep, comprehensive DBA-level database performance, logs, and health audit over the specified schema. Scans memory, checkpoints, temp file spills, contention locks, dead tuple cleanliness, and optionally scans custom logging tables. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change). Returns an object with timestamp, database, version, overall_health ('GOOD' / 'WARNING' / 'CRITICAL'), health_score (int), categories (per-area results), top_issues, recommendations, and raw_stats_snapshot.

Return the slowest queries by mean execution time, via the pg_stat_statements extension. Reports availability=false if the extension is not installed.

Example: analyze_workload(limit=10)

Surface query templates in pg_stat_statements that look like an N+1 loop: hundreds of calls, each returning at most a row or two, with meaningful total wall-clock time spent. Returns the candidates sorted by total time descending so the worst offender appears first. Thresholds (min_calls, max_rows_per_call, min_total_ms) are tunable. Treat results as candidates for investigation, NOT verdicts — a hot cache-miss pattern on a primary-key lookup can trip the same shape. Reports availability=false if pg_stat_statements is not installed.

Return the tables most urgently needing autovacuum, ranked by how close their dead-tuple count is to the per-table autovacuum threshold (autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples, honouring per-table reloptions overrides). Each row carries priority ('overdue' if past the threshold, 'watchlist' if within 50%, 'borderline' otherwise) plus the inputs (n_dead_tup, vacuum_threshold, last_autovacuum, autovacuum_enabled) so the agent can explain why a table landed on the shortlist. Top-level overdue_count lets an agent branch without walking the list. Read-only catalog query.

Example: read_autovacuum_priority(limit=25)

Recommend tables that may benefit from indexing — large tables read mostly by sequential scan. Set fresh=true to bypass the cache and re-read live (e.g. after a schema change). Returns a list of objects with schema, table, live_tuples, sequential_scans, index_scans, and a reason explaining why the table is a candidate.

Example: recommend_indexes(min_live_tuples=10000)

Sibling of recommend_indexes for indexes to remove. Walks pg_stat_user_indexes + pg_stat_user_tables for existing indexes that look like pure cost — large on disk but never (or barely) scanned. Three reason codes, descending strength: never_used (no recorded idx_scans since the last stats reset — candidate for drop, but verify before removal), scan_no_fetch (planner picks it but it returns no rows — usually existence-check pattern), rarely_used (scan rate below low_scan_ratio of the table's total scan activity). Primary-key / unique / exclusion-constraint indexes are excluded (dropping those would be a schema change, not a performance win); indexes below min_index_size_bytes are skipped too. Returns a ready-to-run DROP INDEX CONCURRENTLY statement per candidate. Read-only advisor — execution is on the operator.

Example: recommend_index_drops(schema='public', min_index_size_bytes=1000000, low_scan_ratio=0.01)

Rank a text column's values by pg_trgm trigram similarity to a search term. mode='word' (default) matches fragments within longer text; mode='full' compares whole strings. Reports available=false if pg_trgm is not installed. Returns an object with available (bool), matches (list of {value, similarity} ranked by similarity descending), and mode.

Example: fuzzy_search(schema='public', table='users', column='name', term='janne', mode='word')

Rank a text column's documents against a full-text query using PostgreSQL's built-in tsvector/tsquery. The query accepts web-search syntax (quoted phrases, or, - exclusion). Returns a list of objects with the matched row's primary key columns plus rank (ts_rank score, higher = better match).

Example: full_text_search(schema='public', table='articles', column='body', search_query='"new york" OR -draft')

Find the rows nearest to a query vector by pgvector distance (metric: l2, cosine, or inner_product). Reports available=false if the pgvector extension is not installed.

Example: vector_search(schema='public', table='docs', column='embedding', query_vector=[0.1, 0.2, ...], metric='cosine', limit=10)

Return every row within max_distance of a query vector (a threshold-based query rather than top-k). Useful for de-dup, similarity gating, and clustering pre-passes. Still ordered by distance and capped at limit to avoid pulling huge result sets. Reports available=false if the pgvector extension is not installed.

Diversity-aware vector search: fetch fetch_k nearest candidates by pgvector distance, then re-rank with Maximal Marginal Relevance to return k rows that are relevant but not near-duplicates — better LLM context than raw top-k. lambda_mult in [0,1] trades relevance (1.0) for diversity (0.0); default 0.5. Relevance + diversity are cosine similarities computed over candidate embeddings, so the result is independent of the recall-pass metric. Each hit carries its relevance, mmr_score, and selection rank. Reports available=false if the pgvector extension is not installed.

Example: mmr_search(schema='public', table='docs', column='embedding', query_vector=[0.1, ...], k=10, lambda_mult=0.5)

Combine vector and full-text ranking via reciprocal-rank fusion (RRF) — pulls candidates from each source, then fuses them so rows ranked highly in EITHER source surface. Closes the gap between pure vector (misses keyword/identifier matches) and pure full-text (misses semantic synonyms). Parameters: vector_column, text_column, query_vector, text_query, plus metric / text_config / limit / candidate_pool / rrf_k tunables. Each match carries vector_rank, fts_rank, the fused rrf_score, and (when present) the original distance + ts_rank values.

Example: hybrid_search(schema='public', table='docs', vector_column='embedding', text_column='body', query_vector=[0.1, ...], text_query='postgresql tuning')

Scan a schema for vector(N) columns whose storage could be halved by switching to pgvector v0.7+'s halfvec(N) type (16-bit float). Returns one recommendation per qualifying column with current vs suggested bytes, the savings ratio, and a one-line rationale. Skips columns that are already non-vector and small tables where the absolute saving wouldn't justify the migration.

Find the rows nearest to a lon/lat point by PostGIS distance. Reports available=false if the postgis extension is not installed.

List the queries currently running on the server, with each backend's wait event, duration, and the PIDs blocking it. Returns a list of objects with pid, username, application, state, wait_event (null when not waiting), duration_seconds, query, and blocked_by (list of PIDs holding locks this backend is waiting on).

Report whether MCPg's own connection to PostgreSQL is TLS-encrypted, including the negotiated protocol version, cipher, and key bits (from pg_stat_ssl). Also returns a cluster-wide tally of encrypted vs unencrypted backends (a lower bound under non-superuser privileges). Complements the startup TLS-enforcement check by confirming the live connection actually came up encrypted.

Surface every active CREATE INDEX and its progress from pg_stat_progress_create_index (PG12+, no extension). One row per build with pid, schema.relation.index_name, the command, phase label, blocks_done/total, tuples_done/total, and a computed progress_pct (blocks first, tuples as fallback, null when neither phase reports a denominator). Useful next to list_active_queries when an HNSW / IVFFlat build on a big table is taking longer than expected. Returns a list of objects with pid, schema, relation, index_name, command, phase, blocks_done, blocks_total, tuples_done, tuples_total, and progress_pct (null when no denominator is reported).

Report the health of every configured read replica. Each entry shows index, password-obfuscated DSN, whether the replica is currently degraded (skipped from routing), the last error that took it out, and how many seconds remain before it's re-probed. Returns an empty list when no replicas are configured.

List the pg_cron jobs registered in the database. Returns an empty list when pg_cron is not installed.

List every pg_turboquant ANN index in the database along with the metadata payload that tq_index_metadata() reports for it: algorithm_version, quantizer_family, residual_sketch_kind, fast_path_eligible, capability_flags, delta_state, and maintenance_recommended. Returns an empty list when the pg_turboquant extension is not installed.

Fetch the tq_index_metadata payload for a single turboquant index (schema.index). Documented fields are surfaced as typed attributes; the full upstream payload is preserved in raw_metadata so advisors can reach unanticipated fields. Raises when the extension is not installed or no turboquant index by that name exists. Returns an object with schema, index, algorithm_version, quantizer_family, residual_sketch_kind, fast_path_eligible, capability_flags, delta_state, maintenance_recommended, and raw_metadata (full upstream payload).

Return the exact heap row count tq_index_heap_stats() reports for a single turboquant index (schema.index). The raw upstream payload is preserved in raw for any extra counters upstream may add. Requires the pg_turboquant extension.

Return the backend-local JSON tq_last_scan_stats() reports for the most recent turboquant scan: score_mode, simd_kernel, pages_scanned, pages_pruned, plus the raw payload. Returns null when the extension is absent or no turboquant scan has run on this connection yet.

Walk every pg_turboquant index and emit advisor findings. Rules currently surfaced: prerequisites_unmet (CRITICAL — pgvector is missing) and delta_tier_large (WARNING — upstream's own delta_health.merge_recommended=true advisory, emits a tq_maintain_index suggested_action). Each finding carries a ready-to-run suggested_action SQL statement. Returns an empty list when the extension is not installed. Also feeds the pg_turboquant Indexes category in audit_database.

Run tq_approx_candidates against a turboquant index — approximate k-NN retrieval, no exact rerank. metric is 'cosine' | 'inner_product' | 'l2' (mapped to upstream's runtime metric text). half_precision=True switches to the halfvec overload. probes / oversample_factor are optional per-query knobs (consider calling recommend_turboquant_query_knobs first). Requires the pg_turboquant extension. Returns a list of candidate objects with candidate_id, approximate_distance, and approximate_rank.

Run tq_rerank_candidates against a turboquant index — approximate retrieval followed by SQL-side exact rerank to final_limit results. Returns the candidates with both approximate and exact ranks / distances. half_precision=True switches to the halfvec overload. Requires the pg_turboquant extension.

Run tq_recommended_query_knobs — per-query knob advisor. Two modes: plain (just candidate_limit + optional final_limit) gives generic recommendations; index-aware (supply both index_schema and index_name, plus optional filter_selectivity) specialises the recommendations to the named index's catalog state. Returns probes, oversample_factor, max_visited_codes, max_visited_pages — pass these to turboquant_approx_candidates / turboquant_rerank_candidates. Requires pg_turboquant.

List every pg_search BM25 index in the database along with the parsed reloptions (the WITH (...) config) for each. Surfaces the 13 documented bm25 options (key_field, the six *_fields jsonb configs, layer_sizes, background_layer_sizes, target_segment_count, mutable_segment_rows, sort_by, search_tokenizer). The full parsed dict is preserved in index_options so unsurfaced or future options stay reachable. Returns an empty list when the pg_search extension is not installed.

Fetch the parsed reloptions for a single BM25 index (schema.index). Same shape as one entry of list_pg_search_indexes — typed accessors for the 13 documented options plus the raw index_options dict. Raises when the extension is not installed or no BM25 index by that name exists. Returns an object with schema, index, the typed option fields, and index_options (raw reloptions dict for forward-compat).

Walk every pg_search BM25 index and emit advisor findings. Rules currently surfaced: missing_key_field (CRITICAL — the key_field reloption is required by upstream; an index without it can't satisfy queries) and no_field_configs (WARNING — none of the six *_fields reloptions are set, so the index falls back to default tokenization for every indexed column). Each finding carries a ready-to-run suggested_action SQL statement. Returns an empty list when the extension is not installed. Also feeds the pg_search BM25 Indexes category in audit_database.

Run a BM25 keyword search against a pg_search-indexed table. Returns hits as {id, score, snippets} where id is the value of the caller-supplied key_field and score is pdb.score(t). columns=None searches the whole index; columns=["col"] restricts to a single text field. Multi-column search needs the pdb.parse per-field config JSON and is deferred to a follow-up phase. return_snippets=True requires snippet_field and projects pdb.snippets over that column. Requires the pg_search extension. SECURITY: snippet_start_tag / snippet_end_tag are not sanitized (they pass through to upstream as-is). The defaults match pg_search's HTML defaults; if callers forward untrusted values and a downstream consumer renders snippets as HTML, that's an XSS vector — output escaping is the renderer's responsibility.

Find rows similar to a seed document via pdb.more_like_this + @@@. document_id is the value of key_field for the seed row. All nine documented pdb.more_like_this tuning args (fields jsonb, min_doc_frequency, max_doc_frequency, min_term_frequency, max_query_terms, min_word_length, max_word_length, boost_factor, stop_words) are optional kwargs — when omitted the wrapper does not mention them in the SQL so upstream's defaults apply. Returns the same {id, score} hit shape as pg_search_run. Requires the pg_search extension.

Parse a query string through pdb.parse and return its canonical text form for debugging — useful for confirming the parser interpreted a phrase as expected. lenient=True relaxes syntax checking; conjunction_mode=True treats space-separated terms as AND-joined rather than OR-joined. Requires the pg_search extension.

Combine a BM25 search and a pgvector search via Reciprocal Rank Fusion — the canonical v2 pattern ParadeDB documents in the 2025-10-22 'Hybrid Search Missing Manual' blog post. Returns hits as {id, score, bm25_rank, vector_rank}. score is the summed sum(weight * 1.0 / (k + rank)) across both legs; per-leg ranks are surfaced for transparency (either can be NULL if a row only appeared in one leg's top-K). distance_op is the pgvector operator ('<=>'/'<->'/'<#>' — RRF is operator-agnostic). bm25_columns=None searches the whole BM25 index; bm25_columns=["col"] restricts the BM25 leg to a single field. Defaults mirror upstream's demonstrated form (cosine, k=60, equal weights, per_leg_limit=20). Requires the pg_search and pgvector extensions.

List every TimescaleDB hypertable visible to the current role, with chunk count, compression flag, and total size. Reports available=false when the timescaledb extension is not installed. Returns an object with available (bool) and hypertables (list of {schema, table, num_chunks, compression_enabled, total_size_bytes}).

List the chunks of a TimescaleDB hypertable with each chunk's range_start / range_end and whether it has been compressed. Empty list when the table is not a hypertable. Returns an object with available (bool) and chunks (list of {chunk_name, range_start, range_end, is_compressed, total_size_bytes}).

List all active Apache AGE property graphs in the database. Returns a list of objects with name (graph name) and oid.

Describe the schema structure, vertex labels, and edge labels of a specific property graph. Returns an object with graph_name, vertex_labels, and edge_labels.

Execute an openCypher query on a specific graph database. Supports read queries (MATCH) and write/modifying queries (CREATE, SET, DELETE, MERGE, REMOVE). Returns an object with columns (list of result column names) and rows (list of result rows as dicts keyed by column).

Generate a Mermaid flowchart diagram representing nodes and relationships in a property graph to visualize its schema and topology. Returns the Mermaid flowchart as a string.

List the foreign servers backed by redis_fdw — the FDW that exposes a Redis instance as SQL-queryable foreign tables. Reports each server's connection address, port, database, TLS posture, and whether a user mapping (credential) is configured. Returns an empty list when the redis_fdw extension is not installed. Returns a list of objects with name, address, port, database, tls (bool), password_configured (bool), and options (the full server-options dict).

Example: list_redis_foreign_servers()

Describe one foreign table backed by redis_fdw: which server it's mapped to, the Redis-side key structure (hash / list / string / set / zset), key-prefix, TTL, and SQL-side column shape. Raises an error when the table doesn't exist or isn't backed by redis_fdw. Returns an object with schema, name, server, key_type, key_prefix, ttl_seconds, columns (list of {name, data_type}), and options (the full foreign-table options dict).

Example: describe_redis_cache_table(schema='public', table='sessions_cache')

Best-effort cache metrics for a redis_fdw server. redis_fdw does not ship a uniform stats SQL surface across versions, so the tool validates that the server exists and otherwise reports available=false with a diagnostic. Operators wanting live metrics should query Redis directly (INFO / DBSIZE). Returns an object with server, available (bool), key_count, used_memory_bytes, and detail (a human-readable note).

Example: get_redis_cache_stats(server='redis_primary')

Recommend tables that would benefit from a Redis cache layer. Inspects pg_stat_user_tables for read-heavy, low-write relations whose working set fits comfortably in Redis (default: read/write ratio ≥ 10, ≥ 1000 reads, ≤ 1M rows). When server is provided the generated ready_to_run_sql stub targets that server name; otherwise the stub uses a placeholder operators must substitute. Advisor is read-only — never touches Redis itself. Returns an object with server and candidates — a list of objects with schema, table, reads, writes, read_write_ratio, estimated_row_count, reason (read_only_lookup_table / small_hot_relation / read_heavy_low_write / moderate_read_dominant), and ready_to_run_sql (a CREATE FOREIGN TABLE stub).

Example: recommend_redis_cache_targets(server='redis_primary', limit=10)

Report whether pg_prewarm (and the supporting pg_buffercache) are installed, and whether pg_prewarm is listed in shared_preload_libraries (the autoprewarm worker requires it). Returns an object with pg_prewarm_installed (bool), pg_buffercache_installed (bool), autoprewarm_libraries_present (bool), and shared_preload_libraries (the raw setting).

Example: get_prewarm_extension_status()

Report current shared-buffer residency per relation, ranked by blocks-cached descending. Requires the pg_buffercache extension; returns an empty list when it's missing. Returns a list of objects with schema, table, blocks_cached (8 KiB pages currently in shared buffers), total_blocks (the relation's on-disk size in the same unit), pct_cached (the residency ratio rounded to 2 decimals), and dirty_blocks (pages with pending writes).

Example: list_prewarmed_relations(schema='public', limit=50)

Recommend relations whose first-query latency would benefit from pg_prewarm. Inspects pg_stat_user_tables + pg_statio_user_tables to find high cold-miss-rate / seq_scan-dominant relations, and caps the cumulative cost at shared_buffers_budget_pct * shared_buffers so the recommendation never silently exceeds shared_buffers. The advisor is read-only — never invokes pg_prewarm itself. Returns an object with shared_buffers_blocks (configured shared_buffers in 8 KiB pages), budget_blocks (the cap), total_cost_blocks (sum of recommendations actually returned), and candidates — a list of objects with schema, relation, reason (seq_scan_dominant / high_cold_miss_rate / small_hot_relation_uncached / index_in_critical_path), prewarm_mode, estimated_buffer_cost, heap_blks_read, heap_blks_hit, cache_miss_ratio, and ready_to_run_sql.

Example: recommend_prewarm_targets(shared_buffers_budget_pct=60.0, limit=10)

List the pg_cron jobs MCPg registered for autowarm (jobname LIKE 'mcpg_autowarm%'). Returns an empty list when pg_cron is not installed. Returns a list of objects with jobid, jobname, schedule (the cron expression), and command (the SELECT the job runs).

Example: list_autowarm_jobs()

Report whether SQL/PGQ (the SQL standard for property graph queries, new in PG 19) is usable on this server. SQL/PGQ coexists with the AGE-style graph_operations bucket — get_pgq_status is the agent's hint about which surface to reach for. Never raises; on PG < 19 reports available=false with a diagnostic pointing at run_cypher. Returns an object with available (bool), server_version_num (int), server_version (the human-readable string), and detail (a guidance string the agent can surface to the user).

Example: get_pgq_status()

List SQL/PGQ property graphs defined in the database (reads information_schema.sql_property_graphs). Returns an empty list on PG < 19 or when the catalog view is missing (early Beta builds may not expose it yet — pair with get_pgq_status to disambiguate). Returns a list of objects with schema, name, vertex_tables (list of schema.table strings), and edge_tables (same shape).

Example: list_property_graphs()

Describe one SQL/PGQ property graph by schema-qualified name. Requires PG 19+; raises an error otherwise. Useful when an agent has located a graph via list_property_graphs and needs the full membership before composing a run_pgq query. Returns an object with schema, name, vertex_tables (list of schema.table strings), and edge_tables (same shape).

Example: describe_property_graph(schema='public', name='org_chart')

Execute a SQL/PGQ SELECT ... GRAPH_TABLE query and return the rows. The query must be a single SELECT (or WITH ... SELECT) statement that references GRAPH_TABLE — anything else is refused at the boundary (use run_select for non-graph reads). max_rows caps the result set; when the cap is hit, truncated=true. Requires PG 19+. Returns an object with columns (list of column names from the query's COLUMNS (...) clause), rows (list of dicts keyed by those columns), row_count, and truncated (bool).

Example: run_pgq(query="SELECT * FROM GRAPH_TABLE (org_chart MATCH (e:Employee)-[:REPORTS_TO]->(m:Manager) COLUMNS (e.name AS employee, m.name AS manager))", max_rows=50)

Report whether PG 19's in-server REPACK command is usable. On PG < 19 returns available=false with a diagnostic pointing the agent at the long-standing pg_repack extension shell-out path so the fallback is clear. The in-server REPACK CONCURRENTLY is the headline PG 19 operational win — online table rebuild with no blocking writers. Returns an object with available (bool), server_version_num (int), server_version (the human-readable string), and detail (a guidance string the agent can surface to the user).

Example: get_repack_status()

Report whether PG 19's async-I/O subsystem is usable. On PG < 19 returns available=false with a diagnostic pointing the agent at the existing read_pg_stat_io / run_maintenance tools. Never raises — driver-level errors during the version or settings probe also surface as available=false. Returns an object with available (bool), server_version_num (int), server_version, io_method ('sync' / 'worker' / 'io_uring' / null), io_min_workers, io_max_workers, and detail (a guidance string).

Example: get_aio_status()

Recommend a PG 19 io_method ('sync' / 'worker' / 'io_uring') for the current workload. Reads pg_stat_database aggregates (blks_read / blks_hit / stats_reset) and the current setting, then maps the workload signals to one of: high_concurrent_read_load (→ io_uring), bursty_io_with_cache_pressure (→ worker), low_io_pressure (→ sync), current_setting_optimal (no change), or insufficient_stats (window too short). Read-only — never invokes ALTER SYSTEM; emits a ready_to_run_sql snippet the operator can paste when the recommendation differs from the current setting. Returns an object with available (bool), server_version_num, detail, and recommendations — a list of objects with recommended_method, reason, current_method, cache_miss_ratio, reads_per_second, stats_window_seconds, and ready_to_run_sql (null when no change suggested).

Example: recommend_io_method()

Report whether PG 19's pg_stat_lock and pg_stat_recovery views are usable on this server. Never raises — driver-level errors surface as available=false. On PG < 19 returns available=false with a diagnostic pointing the agent at find_blocking_chains / pg_stat_replication. Returns an object with available (bool), server_version_num (int), server_version, has_pg_stat_lock (bool), has_pg_stat_recovery (bool), and detail (guidance string).

Example: get_pg19_stats_status()

Return every row from PG 19's pg_stat_lock view (per-lock-type acquire / wait / wait-time counters since the most recent pg_stat_reset). Empty list on PG < 19 or when the view isn't present. Returns a list of objects with lock_type (relation / page / tuple / xid / virtualxid / advisory / ...), acquires, waits, and wait_time_us.

Example: read_pg_stat_lock()

Return rows from PG 19's pg_stat_recovery view — replay progress, lag, and startup state for a standby. Empty list on PG < 19, when the view isn't present, or when the server isn't in recovery (a primary running standalone returns no rows). Returns a list of objects with replay_lsn, replay_lag_seconds, last_replayed_at, and startup_state (any of which may be null when not applicable).

Example: read_pg_stat_recovery()

Rank PG 19 pg_stat_lock rows by wait dominance and surface stable reason codes. Read-only — never modifies state. Pair with find_blocking_chains for the active culprits on a specific hot lock_type. Returns an object with available (bool), server_version_num, detail, and hotspots — a list of objects with lock_type, waits, wait_time_us, reason (one of contention_dominant / high_wait_time / high_wait_count / low_contention), and suggested_followup (a human-readable next-step string).

Example: analyze_lock_hotspots()

Report whether PG 19's online data_checksums toggle is usable + the current setting. Never raises — driver-level errors surface as available=false. On PG ≤ 18 reports available=false but still surfaces the current state (set at initdb time) so the agent has context, and points at the offline pg_checksums fallback. Returns an object with available (bool), server_version_num (int), server_version, enabled (bool / null), and detail (guidance string).

Example: get_data_checksums_status()

Report whether PG 19's on-demand wal_level flip is usable, plus the configured wal_level, the new PG 19 effective_wal_level preset GUC, and max_replication_slots. When configured and effective diverge the agent can tell that an ALTER SYSTEM has been done but a reload is still pending. Never raises. Returns an object with available (bool), server_version_num, server_version, wal_level, effective_wal_level, max_replication_slots, and detail.

Example: get_logical_replication_status()

Report whether PG 19's pg_get_roledef() / pg_get_databasedef() / pg_get_tablespacedef() DDL-dump functions are usable on this server. Never raises — driver-level errors surface as available=false. On PG ≤ 18 reports available=false and points the agent at pg_dumpall --roles-only / --globals-only / --tablespaces-only as the fallback. Returns an object with available (bool), server_version_num (int), server_version, has_pg_get_roledef (bool), has_pg_get_databasedef (bool), has_pg_get_tablespacedef (bool), and detail (guidance string).

Example: get_pg19_ddl_status()

Return the CREATE ROLE DDL for a named role using PG 19's pg_get_roledef(oid) function — no shell-out to pg_dumpall --roles-only required. Returns found=false with an empty ddl when the role doesn't exist. Requires PG 19+; raises on older servers (pair with get_pg19_ddl_status to feature-detect). Returns an object with object_type ('role'), object_name, found (bool), and ddl (the verbatim CREATE statement).

Example: get_role_ddl(role_name='app_user')

Return the CREATE DATABASE DDL for a named database using PG 19's pg_get_databasedef(oid) function. Returns found=false with an empty ddl when the database doesn't exist. Requires PG 19+. Returns an object with object_type ('database'), object_name, found, and ddl.

Example: get_database_ddl(database_name='analytics')

Return the CREATE TABLESPACE DDL for a named tablespace using PG 19's pg_get_tablespacedef(oid) function. Returns found=false with an empty ddl when the tablespace doesn't exist. Requires PG 19+. Returns an object with object_type ('tablespace'), object_name, found, and ddl.

Example: get_tablespace_ddl(tablespace_name='fast_ssd')

Report whether PG 19's ALTER TABLE … MERGE PARTITIONS and ALTER TABLE … SPLIT PARTITION forms are usable on this server. Never raises — driver-level errors surface as available=false. On PG ≤ 18 reports available=false and points the agent at the detach / create / attach fallback path. Returns an object with available (bool), server_version_num (int), server_version, and detail (guidance string).

Example: get_pg19_partitions_status()

Report whether PG 19's B-tree skip-scan optimisation is the planner default on this server. Never raises — driver-level errors surface as available=false. On PG ≤ 18 reports available=false and points the agent at the standard 'add a dedicated single-column index' fallback. Returns an object with available (bool), server_version_num (int), server_version, and detail (guidance string).

Example: get_skip_scan_status()

Find composite B-tree indexes whose leading column has low NDV — these are the ones PG 19's skip-scan optimisation unlocks. Each candidate's trailing columns can now be served by the composite index alone, so any dedicated single-column indexes on those trailing columns become review candidates for recommend_index_drops. Returns an empty list on PG ≤ 18 or driver failure — pair with get_skip_scan_status for the diagnostic. max_leading_ndv (default 1000) caps the leading-column NDV that's considered low enough for skip-scan to be profitable. Returns a list of objects with schema, table, index_name, leading_column, trailing_columns (list of strings), estimated_leading_ndv (int), and rationale (human-readable explanation).

Example: recommend_skip_scan_indexes()

Report whether PG 19's WAIT FOR LSN is usable on this server. Also reports whether the current backend is a standby (the only context where the wait does meaningful work). Never raises. On PG ≤ 18 returns available=false and points at the poll-loop fallback. Returns an object with available (bool), server_version_num (int), server_version, is_in_recovery (bool), and detail.

Example: get_wait_for_lsn_status()

Return the current WAL LSN — write-side on a primary (pg_current_wal_lsn()) or replay-side on a standby (pg_last_wal_replay_lsn()). Natural pairing for the read-your-writes workflow: capture on the primary right after a write, then pass to wait_for_lsn on the standby session before the follow-up read. Works on every supported PG version. Returns an object with role ('primary' or 'standby') and lsn (PostgreSQL LSN literal, e.g. '0/1234ABCD').

Example: get_current_wal_lsn()

Advise whether the caller should use WAIT FOR LSN for read-your-writes consistency. Combines server role (primary vs standby), replay lag, and PG version into a structured recommendation. Never raises. reason is one of primary_no_wait_needed, standby_no_lag, standby_lag_unknown, standby_with_lag, standby_pg18_or_older, unavailable. Returns an object with recommend_use (bool), reason, is_in_recovery, server_version_num, current_lag_bytes (int / null), and detail.

Example: recommend_read_your_writes()

Issue WAIT FOR LSN '<lsn>' TIMEOUT <ms> and block until WAL replay catches up on the connected backend. timeout_ms defaults to 0 (wait indefinitely); a positive value bounds the wait — on timeout the helper returns timed_out=true rather than raising so the caller can decide whether to retry or fall through. LSN format is strictly validated (hex/hex) before any SQL is composed. Requires PG 19+; raises on older servers with the poll-loop fallback in the message. Returns an object with lsn, timeout_ms (int), timed_out (bool), and wait_sql (the rendered statement).

Example: wait_for_lsn(lsn='0/1234ABCD', timeout_ms=5000)

Probe the connected server for the WarehousePG (Greenplum-derived MPP) signature. Reports available=true only when BOTH the version string mentions WarehousePG / Greenplum AND the gp_segment_configuration catalog view exists. Surfaces coordinator_role, segment_count (primary segments only), and mirroring (bool). On vanilla PostgreSQL clusters returns available=false with a clean diagnostic — the rest of the mcpg.warehousepg.* family advertise themselves inert via this probe. Read-only; never raises.

Example: get_warehousepg_status()

List the data-distribution policy for each table in a WarehousePG schema (HASH(col,...), RANDOM, or REPLICATED). Joins gp_distribution_policy to pg_attribute for the distribution-key column names in catalog order. schema=None returns every non-system schema. Read-only. On vanilla PG returns available=false with a diagnostic.

Example: list_distribution_policies(schema='public')

Walk gp_segment_configuration and surface MPP segment posture. Returns per-segment status ('u' = up), mode ('s' = sync / 'n' = not-in-sync / 'c' = changetracking), and role vs preferred_role to detect post-failover state. Top-level unhealthy_count + out_of_sync_count let agents branch without walking the segments array. Read-only. On vanilla PG returns available=false.

Example: check_segment_health()

Describe append-optimized (AO) / append-optimized columnar (AO/CO) storage metadata for one table: row vs column orientation, compression_type, compression_level, block_size, checksum. Reads pg_appendonly. Returns is_ao=false cleanly when the table is a regular heap. Read-only. On vanilla PG returns available=false.

Example: describe_ao_table(schema='public', table='events_ao')

List configured WarehousePG resource groups + their utilisation. Reads gp_toolkit.gp_resgroup_status for concurrency, cpu_max_percent, cpu_weight, memory_limit, memory_shared_quota, plus live num_running / num_queueing. Pairs with analyze_workload for 'where's my workload time going' diagnosis. Read-only. On vanilla PG returns available=false.

Example: list_resource_groups()

Run EXPLAIN (ANALYZE, FORMAT JSON) on sql and roll up MPP-specific facts: slice count, motion nodes (Redistribute Motion / Broadcast Motion / Gather Motion), and per-motion metadata (senders, receivers, estimated rows). Uses the same safety pre-flight as analyze_query_plan(io=True) — writes / DDL stay rejected. redistribute_count flags 'data is not co-located with the join key'. On vanilla PG returns available=false.

Example: analyze_mpp_query_plan(sql='SELECT * FROM big JOIN small ON big.k = small.k')

Distribution-skew advisor for a hash-distributed table. Reads pg_stats.n_distinct for every column on the table and suggests a better hash key when the current one is low-cardinality. Pure catalog read — no per-segment scans. Returns ranked candidates, optional recommendation, and ready-to-review suggested_ddl (ALTER TABLE … SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (col)). Diagnosis-only — never executes. On vanilla PG returns available=false.

Example: recommend_redistribute(schema='public', table='fact_sales')