PostgreSQL MCP Server

Find geometries within a bounding box. Swapped min/max values are auto-corrected. Default limit: 10 rows.

Create a buffer zone around geometries. Default limit: 10 rows, default simplify: 10m (set simplify: 0 to disable). Simplification reduces polygon point count for LLM-friendly payloads.

Get buffer cache hit ratio statistics.

Cancel a running query (graceful, preferred over terminate).

Analyze database growth trends and provide capacity planning forecasts. Note: Growth estimates are based on pg_stat_user_tables counters since last stats reset; accuracy depends on how long stats have been accumulating.

Simulate the impact of DELETE, DROP, or TRUNCATE on a table. Returns affected tables, estimated row counts, cascade paths, and severity assessment.

Find TEXT columns that may benefit from case-insensitive comparisons. Looks for common patterns like email, username, name, slug, etc.

Compare two values using case-insensitive semantics. Useful for testing citext behavior before converting columns.

Convert an existing TEXT column to CITEXT for case-insensitive comparisons. This is useful for retrofitting case-insensitivity to existing columns like email or username. Note: If views depend on this column, you must drop and recreate them manually before conversion.

Enable the citext extension for case-insensitive text columns. citext is ideal for emails, usernames, and other identifiers where case shouldn't matter.

List all columns using the citext type in the database. Useful for auditing case-insensitive columns.

Analyze a specific table and recommend which columns should use citext. Provides schema design recommendations based on column names and existing data patterns. Requires the 'table' parameter to specify which table to analyze.

Physically reorder table data based on an index. Call with no args to re-cluster all previously-clustered tables.

Analyze connection usage and provide pool optimization recommendations.

Get connection statistics by database and state.

Analyze all constraints for issues: redundant indexes, missing foreign keys, missing NOT NULL, missing primary keys, and unindexed foreign keys.

Export query results using COPY TO. Use query/sql for custom query or table for SELECT *.

Generate COPY FROM command for importing data.

Count rows in a table, optionally with a WHERE clause or specific column.

Generate a backup strategy recommendation with cron schedule.

Create a GIN index for full-text search on a column.

Create an index on a table. Supports btree, hash, gin, gist, brin index types.

Create a partition. Use subpartitionBy/subpartitionKey to make it sub-partitionable for multi-level partitioning.

Create a partitioned table. Columns: notNull, primaryKey, unique, default. Note: primaryKey/unique must include the partition key column.

Create a new schema.

Create a new sequence with optional START, INCREMENT, MIN/MAX, CACHE, CYCLE, and OWNED BY.

Create a new table with specified columns and constraints. Supports composite primary keys and table-level constraints.

Create a view or materialized view.

Modify an existing cron job. Can change schedule, command, database, username, or active status. Only specify the parameters you want to change.

Delete old job run history records. Helps prevent the cron.job_run_details table from growing too large. By default, removes records older than 7 days.

Enable the pg_cron extension for job scheduling. Requires superuser privileges.

View execution history for cron jobs. Shows start/end times, status, and return messages. Useful for monitoring and debugging scheduled jobs. Default limit is 10 rows.

List all scheduled cron jobs. Shows job ID, name, schedule, command, and status. Jobs without names (jobname: null) must be referenced by jobId. Default limit: 50 rows.

Schedule a new cron job. Supports standard cron syntax (e.g., "0 2 * * *" for 2 AM daily) or interval syntax (e.g., "30 seconds"). Note: pg_cron allows duplicate job names; use unique names to avoid confusion. Returns the job ID.

Schedule a cron job to run in a different database. Useful for cross-database maintenance tasks. Returns the job ID.

Remove a scheduled cron job by its ID or name. If both are provided, jobName takes precedence. Job ID accepts numbers or numeric strings. Works for both active and inactive jobs.

Get the size of a database.

Get the full foreign key dependency graph with cascade paths, row counts, circular dependency detection, and severity assessment. Agent-optimized structured output.

Get detailed table structure including columns, types, and constraints. For tables/views only, not sequences.

Detach a partition. Use concurrently: true for non-blocking. Use finalize: true only after an interrupted CONCURRENTLY detach.

Scores tables by bloat risk using multiple factors: dead tuple ratio, vacuum staleness, table size, and autovacuum effectiveness. Returns per-table risk scores (0-100) with actionable recommendations.

Detects unusual connection patterns by analyzing concentration by user, application, state, and wait events. Flags when a single user or application monopolizes the connection pool, or when idle-in-transaction connections accumulate.

Detects queries deviating from their historical execution time norms using z-score analysis. Requires pg_stat_statements extension. Returns anomalous queries ranked by deviation severity with risk level.

Consolidates key performance metrics into a single actionable report: slow queries, blocking locks, connection pressure, cache hit ratio, disk usage, and top tables by size and activity. Returns per-section health ratings and recommendations with an overall health score.

Find nearby geometries within a distance from a point. Output distance_meters is always in meters; unit parameter only affects the filter threshold.

Add one or more JSON documents to a collection.

Get document collection statistics: row count, size, and indexes.

Create a new JSONB document collection (table with doc JSONB + generated _id primary key).

Create an expression index on document fields for faster queries. Uses PostgreSQL expression indexes on JSONB paths.

Drop a document collection (table).

Query documents in a JSONB collection with optional filter, field projection, and pagination.

List JSONB document collections in a schema. Collections are tables with a 'doc' JSONB column and '_id' text column.

Update documents matching a filter. Set fields with 'set' and remove fields with 'unset'.

Remove documents matching a filter from a collection.

Drop an index from a table. Supports IF EXISTS, CASCADE, and CONCURRENTLY options.

Drop a schema (optionally with all objects).

Drop a sequence. Supports IF EXISTS and CASCADE options.

Drop a table from the database.

Drop a view or materialized view. Supports IF EXISTS and CASCADE options.

Get the pg_dump command for a schema or database.

Generate DDL for a table or sequence. Returns CREATE TABLE for tables, CREATE SEQUENCE for sequences.

Find duplicate or overlapping indexes (same leading columns). Candidates for consolidation.

Execute TypeScript/JavaScript code in a sandboxed environment with access to all PostgreSQL tools via the pg.* API. Available API groups: - pg.core: readQuery, writeQuery, listTables, describeTable, createTable, createIndex, etc. (18 methods) - pg.transactions: begin, commit, rollback, savepoint, execute (7 methods) - pg.jsonb: extract, set, insert, delete, contains, pathQuery (19 methods) - pg.text: search, fuzzy, headline, rank (11 methods) - pg.performance: explain, tableStats, indexStats (16 methods) - pg.admin: vacuum, analyze, reindex (10 methods) - pg.monitoring: databaseSize, tableSiz…

Check if rows exist in a table. WHERE clause is optional: with WHERE = checks matching rows; without WHERE = checks if table has any rows at all. For table *schema* existence, use pg_list_tables.

Show query execution plan without running the query.

Run query and show actual execution plan with timing.

Show query plan with buffer usage statistics.

Fuzzy string matching using fuzzystrmatch extension. Levenshtein (default): returns distance; use maxDistance=5+ for longer strings. Soundex/metaphone: returns phonetic code for exact matches only.

Perform spatial clustering using DBSCAN or K-Means. DBSCAN defaults: eps=100m, minPoints=3. K-Means default: numClusters=5 (provide explicit value for best results).

Analyze spatial indexes and provide optimization recommendations.

Transform geometry from one spatial reference system (SRID) to another.

Create a point geometry from latitude/longitude coordinates. The SRID parameter sets output metadata only; input coordinates are always WGS84 lat/lng.

Create a buffer zone around a WKT or GeoJSON geometry. Returns the buffered geometry as GeoJSON and WKT.

Add a geometry column to a table. Returns alreadyExists: true if column exists.

Compute the intersection of two WKT or GeoJSON geometries. Returns the intersection geometry and whether they intersect.

Transform a WKT or GeoJSON geometry from one SRID to another. Common SRIDs: 4326 (WGS84/GPS), 3857 (Web Mercator).

List indexes with usage statistics. When table is omitted, lists ALL database indexes (can be large). Use schema/limit to filter.

Combined vector similarity and full-text search with weighted scoring.

Suggest missing indexes based on table statistics or query analysis. When sql is provided and HypoPG is installed, creates hypothetical indexes to measure potential performance improvement.

Get index usage statistics.

Find geometries that intersect with a given geometry. Auto-detects SRID from target column if not specified. Default limit: 10 rows.

Aggregate rows into a JSONB array. With groupBy, returns all groups with their aggregated items.

Build a JSONB array from values. Accepts {values: [...]} or {elements: [...]}. Returns {array: [...]}.

Find rows where JSONB column contains the specified value. Note: Empty object {} matches all rows.

Delete a key or array element from a JSONB column. Accepts path as string or array. Note: rowsAffected reflects matched rows, not whether key existed.

Compare two JSONB objects. Returns top-level key differences only (shallow comparison, not recursive).

Extract value from JSONB at specified path. Returns null if path does not exist in data structure. Use select param to include identifying columns.

Analyze JSONB column and suggest indexes. Only works on object-type JSONB (not arrays).

Insert value into JSONB array or object. For arrays, index -1 inserts BEFORE last element (use insertAfter:true to append). For objects, throws error if key already exists.

Get all unique keys from a JSONB object column (deduplicated across rows).

Merge two JSONB objects. deep=true (default) recursively merges. mergeArrays=true concatenates arrays.

Normalize JSONB to key-value pairs. Use idColumn to specify row identifier (default: "id" if exists, else ctid).

Build a JSONB object. Use data: {name: "John", age: 30} or parallel arrays keys: ["name"], values: ["John"]. Returns {object: {...}}.

Query JSONB using SQL/JSON path expressions (PostgreSQL 12+). Note: Recursive descent (..) syntax is not supported by PostgreSQL.

Format JSON/JSONB with indentation for readability. Pass raw JSON string via 'json' param, or extract from a table column via 'table' + 'column'. Uses PostgreSQL's native jsonb_pretty() for table mode.

Scan JSONB for security issues. Only works on object-type JSONB (not arrays). Use larger sampleSize for thorough scans.

Set value in JSONB at path. Uses dot-notation by default; for literal dots in keys use array format ["key.with.dots"]. Use empty path ('' or []) to replace entire column value.

Get statistics about JSONB column usage. Note: topKeys only applies to object-type JSONB, not arrays.

Remove null values from a JSONB column. Use preview=true to see changes without modifying data.

Get JSONB type at path. Uses dot-notation (a.b.c), not JSONPath ($). Response includes columnNull to distinguish NULL columns.

Validate a JSONPath expression and test it against sample data. Supports vars for parameterized paths.

Enable the pg_stat_kcache extension for OS-level performance metrics. Requires pg_stat_statements to be installed first. Both extensions must be in shared_preload_libraries.

Get aggregated OS-level statistics for a database. Shows total CPU time, I/O, and page faults across all queries.

Get query statistics with OS-level CPU and I/O metrics. Joins pg_stat_statements with pg_stat_kcache to show what SQL did AND what system resources it consumed. orderBy options: 'total_time' (default), 'cpu_time', 'reads', 'writes'. Use minCalls parameter to filter by call count.

Reset pg_stat_kcache statistics. Use this to start fresh measurements. Note: This also resets pg_stat_statements statistics.

Analyze queries to classify them as CPU-bound, I/O-bound, or balanced. Helps identify the root cause of performance issues - is the query computation-heavy or disk-heavy?

Get top CPU-consuming queries. Shows which queries spend the most time in user CPU (application code) vs system CPU (kernel operations).

Get top I/O-consuming queries. Shows filesystem-level reads and writes, which represent actual disk access (not just shared buffer hits).

Search text using LIKE patterns. Case-insensitive (ILIKE) by default.

List table constraints (primary keys, foreign keys, unique, check).

List installed PostgreSQL extensions with versions.

List user-defined functions with optional filtering. Use exclude (array) to filter out extension functions. Default limit=500 may need increasing for busy databases.

List database objects filtered by type. Use type: "table" (singular) or types: ["table","view"] (array). Supports: table, view, materialized_view, function, procedure, sequence, index, trigger.

List all partitions of a partitioned table. Returns warning if table is not partitioned.

List all schemas in the database.

List all sequences in the database.

List all tables, views, and materialized views with metadata. Use limit to restrict results.

List all triggers.

List all views and materialized views.

View current lock information.

Convert an existing TEXT column to LTREE type. Note: If views depend on this column, you must drop and recreate them manually before conversion.

Enable the ltree extension for hierarchical tree-structured labels.

Create a GiST index on an ltree column for efficient tree queries.

Find the longest common ancestor of multiple ltree paths.

List all columns using the ltree type in the database.

Match ltree paths using lquery pattern syntax.

Query hierarchical relationships in ltree columns. Supports exact paths (descendants/ancestors) and lquery patterns with wildcards.

Extract a portion of an ltree path.

Generate a migration plan for a schema change.

Execute migration SQL and record it atomically in a single transaction. Auto-provisions the tracking table on first use. On failure, rolls back and records a 'failed' entry. Use pg_migration_record instead if you only need to log an already-applied migration.

Query migration history with optional filtering by status and source system. Returns paginated results ordered by applied_at descending.

Initialize or verify the schema version tracking table (_mcp_schema_versions). Idempotent — safe to call repeatedly. Returns current tracking state.

Record a migration in the schema version tracking table with status 'recorded' (metadata only, SQL not executed). Use pg_migration_apply instead to execute SQL and record with status 'applied'. Auto-provisions the tracking table on first use. Computes SHA-256 hash for idempotency detection.

Analyze proposed DDL statements for risks: data loss, lock contention, constraint violations, and breaking changes. Pre-flight check before executing migrations.

Roll back a specific migration by ID or version. Executes the stored rollback_sql in a transaction and updates status to 'rolled_back'. Use dryRun: true to preview the rollback SQL without executing.

Get current migration tracking status: latest version, counts by status, and list of source systems. Returns initialized: false if tracking table doesn't exist.

Get detailed metadata for a specific database object (table, view, function, sequence, index).

Get detailed information about a partitioned table. Returns warning if table is not partitioned.

Analyze a table and suggest optimal partitioning strategy.

Analyze the health of partition sets managed by pg_partman. Checks for issues like data in default partitions, missing premake partitions, stale maintenance, and retention configuration.

Check if any data exists in the default partition that should be moved to child partitions. Data in default indicates partitions may be missing for certain time/value ranges.

Enable the pg_partman extension for automated partition management. Requires superuser privileges.

Create a new partition set using pg_partman's create_parent() function. Supports time-based and integer-based partitioning with automatic child partition creation. The parent table must already exist before calling this function. Partition type (time vs integer) is automatically detected from the control column's data type. For non-timestamp/integer columns (text, uuid), use raw pg_partman SQL with timeEncoder/timeDecoder parameters. IMPORTANT: For empty tables with no data, you MUST provide startPartition (e.g., 'now' for current date, or a specific date like '2024-01-01'). Without startPar…

Move data from the default partition to appropriate child partitions. Creates new partitions if needed for the data being moved.

Run partition maintenance to create new child partitions and enforce retention policies. Should be executed regularly (e.g., via pg_cron) to keep partitions current. Maintains all partition sets if no specific parent table is specified.

Configure retention policy for a partition set. Partitions older than the retention period will be dropped or detached during maintenance.

View the configuration for a partition set from part_config table.

List all child partitions for a partition set managed by pg_partman.

Convert a partitioned table back to a regular table by moving all data from child partitions to a TARGET table. IMPORTANT: The targetTable parameter is REQUIRED. pg_partman does not consolidate data back to the parent table directly. You must first create an empty table with the same structure as the parent, then specify it as targetTable. Example: undoPartition({ parentTable: "public.events", targetTable: "public.events_consolidated" })

Analyze a slow query and suggest optimizations.

Capture current database performance metrics as a baseline for comparison.

Enable the pgcrypto extension for cryptographic functions.

Hash a password using crypt() with a salt from gen_salt().

Decrypt data that was encrypted with pg_pgcrypto_encrypt.

Encrypt data using PGP symmetric encryption.

Generate cryptographically secure random bytes.

Generate a cryptographically secure UUID v4.

Generate a salt for use with crypt() password hashing.

Hash data using various algorithms (SHA-256, SHA-512, MD5, etc.).

Compute HMAC for data with a secret key.

Check if a point is within any polygon in a table. The geometry column should contain POLYGON or MULTIPOLYGON geometries. Default limit: 10 rows.

Enable the PostGIS extension for geospatial operations.

Build a PostgreSQL query based on requirements. Specify tables and operation type.

Compare execution plans of two SQL queries to identify performance differences.

Get query plan statistics showing planning time vs execution time (requires pg_stat_statements).

Quick SQL query guidance for common operations.

Quick reference for exploring database schema.

Execute a read-only SQL query (SELECT, WITH). Returns rows as JSON. Pass transactionId to execute within a transaction.

Check if server is in recovery mode (replica).

Match text using POSIX regular expressions.

Rebuild indexes to improve performance. For target: database, name defaults to the current database if omitted.

Reload PostgreSQL configuration without restart.

Check replication status and lag.

Reset statistics counters (requires superuser).

Generate pg_restore command for restoring backups.

Generate commands to validate backup integrity and restorability.

Assign (grant) a role to a user/role, establishing role membership. Optionally with ADMIN OPTION to allow re-granting.

Get detailed attributes for a PostgreSQL role: login, superuser, createdb, createrole, replication, bypassrls, inherit, connection limit, expiration, and OID.

Create a new PostgreSQL role with optional attributes (LOGIN, PASSWORD, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, CONNECTION LIMIT, VALID UNTIL).

Drop a PostgreSQL role. Use ifExists (default: true) to skip gracefully if the role does not exist.

Grant privileges (SELECT, INSERT, UPDATE, DELETE, ALL, etc.) on tables, schemas, sequences, or functions to a PostgreSQL role.

Show privileges and memberships for a PostgreSQL role. Includes role attributes, membership in other roles, and optionally table-level grants.

List PostgreSQL roles with attributes (login, superuser, createdb, etc.) and optional name filtering.

Revoke role membership from a user, or revoke specific privileges on objects from a role. For membership: provide role + user. For privileges: provide role + privileges + table/schema.

Enable or disable row-level security (RLS) on a table. Optionally use FORCE to apply RLS even to the table owner.

List row-level security (RLS) policies for a table or all tables in a schema. Shows policy name, command, roles, USING/WITH CHECK expressions, and permissive/restrictive type.

Set the session's active role using SET ROLE, or reset to the original session role with RESET ROLE. Session-scoped and reversible.

Step-by-step playbook for safely restoring a pre-mutation backup snapshot using side-by-side comparison.

Design a database schema for a given use case.

Get a complete schema snapshot in a single agent-optimized JSON structure. Includes tables, columns, types, constraints, indexes, triggers, sequences, and extensions.

Run a comprehensive security audit checking SSL, password encryption, superuser exposure, logging, and HBA rules.

Get encryption status including SSL configuration, password encryption method, and pgcrypto availability.

List detailed pg_hba.conf authentication rules with optional filtering by user or rule type.

Get PostgreSQL host-based authentication (pg_hba.conf) summary — the PostgreSQL equivalent of a firewall.

Apply data masking to sensitive values. Supports email, phone, SSN, credit card, and partial masking.

Validate password strength against configurable policy. Uses local analysis (no database query).

Identify tables and columns that may contain sensitive data based on column name patterns.

Get SSL/TLS connection and certificate status for active connections.

Get comprehensive privilege report for PostgreSQL roles including attributes, membership, and object grants.

Find tables with high sequential scan counts (potential missing indexes). Default minScans=10; use higher values (e.g., 100+) for production databases.

Get PostgreSQL server version information.

Set a configuration parameter for the current session.

Complete guide for setting up case-insensitive text columns with citext for emails, usernames, and tags.

Complete PostgreSQL Document Store setup guide using JSONB collections

Complete guide for setting up OS-level CPU and I/O performance monitoring with pg_stat_kcache.

Complete guide for setting up hierarchical tree-structured data with ltree for categories, org charts, and file paths.

Complete guide for setting up automated partition lifecycle management with pg_partman including time-based and serial partitioning.

Complete guide for setting up job scheduling with pg_cron including cron syntax, common patterns, and monitoring.

Complete guide for setting up cryptographic functions with pgcrypto including hashing, encryption, and secure password storage.

Complete guide for setting up semantic search with pgvector including table design, indexing, and queries.

Complete guide for setting up geospatial operations with PostGIS including spatial types, indexing, and queries.

Show current PostgreSQL configuration settings. Filter by name pattern or exact setting name. Accepts: pattern, setting, or name parameter.

Create a GiST spatial index for geometry column. Uses IF NOT EXISTS to avoid errors on duplicate names.

Get currently running queries and connections.

Get query statistics from pg_stat_statements (requires extension).

Calculate Pearson correlation coefficient between two numeric columns. Use groupBy to get correlation per category.

Calculate descriptive statistics (count, min, max, avg, stddev, variance, sum) for a numeric column. Use groupBy to get statistics per category. Warning: using groupBy on high-cardinality columns will result in large JSON payloads.

Get distinct values from a column with count. Useful for understanding cardinality and unique value distribution.

Analyze data distribution with histogram buckets, skewness, and kurtosis. Use groupBy to get distribution per category.

Get value frequency distribution (count per unique value) ordered by frequency descending. Shows the most common values first.

Perform one-sample t-test or z-test against a hypothesized mean. For z-test, provide populationStdDev (sigma) for accurate results. Use groupBy to test each group separately.

Access data from previous (LAG) or next (LEAD) rows in an ordered set. Useful for comparisons, deltas, and change detection.

Calculate moving average (AVG OVER sliding window) for a numeric column. Specify windowSize for the number of preceding rows to include.

Divide ordered rows into N equal buckets (e.g., quartiles with buckets=4). Returns bucket assignment per row.

Detect statistical outliers in a numeric column using IQR (interquartile range) or Z-score method. IQR is robust against non-normal distributions.

Calculate percentiles (quartiles, custom percentiles) for a numeric column. Use groupBy to get percentiles per category.

Assign rank within an ordered result set. Supports rank (gaps), dense_rank (no gaps), and percent_rank (0-1). Use partitionBy to rank within groups.

Perform linear regression analysis (y = mx + b) between two columns. Use groupBy to get regression per category.

Assign sequential row numbers within an ordered result set. Use partitionBy to restart numbering per group.

Calculate cumulative running total (SUM OVER) for a numeric column. Use partitionBy to reset total per group.

Get a random sample of rows. Use sampleSize for exact row count (any method), or percentage for approximate sampling with bernoulli/system methods.

Get summary statistics (count, avg, min, max, stddev) for multiple numeric columns. Defaults to all numeric columns if none specified.

Aggregate data into time buckets for time series analysis. Use groupBy to get separate time series per category.

Get the top N rows ranked by a column. Auto-excludes long-content columns (text, json, bytea) from output unless selectColumns is specified. Warning: Using a large 'n' (e.g., > 1000) will result in massive JSON payloads.

Analyze current system health and resource usage including CPU, memory, and I/O patterns.

Get sizes of all tables with indexes and total.

Get table access statistics.

Terminate a database connection (forceful, use with caution).

Generate highlighted snippets from full-text search matches. Use select param for stable row identification (e.g., primary key).

Remove accent marks (diacritics) from text using PostgreSQL unaccent extension. Note: Does NOT lowercase or trim—use LOWER()/TRIM() in a query for those operations.

Get relevance ranking for full-text search results. Returns matching rows only with rank score.

Full-text search using tsvector and tsquery.

List available full-text search configurations (e.g., english, german, simple).

Perform basic sentiment analysis on text using keyword matching.

Convert text to tsquery for full-text search. Modes: plain (default), phrase (proximity matching), websearch (Google-like syntax with AND/OR/-).

Convert text to tsvector representation for full-text search operations.

Get a compact index of all available PostgreSQL tools for discovery.

Get tables in safe DDL execution order. 'create' direction: dependencies first (for CREATE TABLE). 'drop' direction: dependents first (for DROP TABLE).

Begin a new transaction. Returns a transaction ID for subsequent operations.

Commit a transaction, making all changes permanent.

Execute multiple statements atomically in a single transaction.

Release a savepoint, keeping all changes since it was created.

Rollback a transaction, undoing all changes.

Rollback to a savepoint, undoing changes made after it.

Create a savepoint within a transaction for partial rollback.

Check the status of an active transaction without modifying it. Returns whether the transaction is active, aborted (needs rollback), or not found.

Find similar strings using pg_trgm trigram matching. Returns similarity score (0-1). Default threshold 0.3; use lower (e.g., 0.1) for partial matches.

Truncate a table, removing all rows quickly. Use cascade to truncate dependent tables.

Find indexes that have never been used (idx_scan = 0). Candidates for removal.

Insert a row or update if it already exists (INSERT ... ON CONFLICT DO UPDATE). Specify conflict columns for uniqueness check. Use data or values for column-value pairs.

Get server uptime and startup time.

List all roles assigned to a user/role, including admin option and SET option (PG 16+).

Run VACUUM to reclaim storage and update visibility map. Use analyze: true to also update statistics. Verbose output goes to PostgreSQL server logs.

Run VACUUM and ANALYZE together for optimal performance. Verbose output goes to PostgreSQL server logs.

Get detailed vacuum statistics including dead tuples, last vacuum times, and wraparound risk.

Add a vector column to a table. Requires: table, column, dimensions.

Calculate average vector. Requires: table, column. Optional: groupBy, where.

Efficiently insert multiple vectors. vectors param expects array of {vector: [...], data?: {...}} objects, NOT raw arrays. Example: vectors: [{vector: [0.1, 0.2], data: {name: "a"}}]

Perform K-means clustering on vectors. Returns cluster centroids only (not row assignments). To assign rows to clusters, compare row vectors to centroids using pg_vector_distance.

Enable the pgvector extension for vector similarity search.

Create vector index. Requires: table, column, type (ivfflat or hnsw).

Reduce vector dimensions using random projection. Supports direct vector input OR table-based extraction.

Calculate distance between two vectors. Valid metrics: l2 (default), cosine, inner_product.

Generate text embeddings. Returns a simple hash-based embedding for demos (use external APIs for production).

Analyze vector column and recommend optimal index parameters for IVFFlat/HNSW.

Insert a vector into a table, or update an existing row's vector. For upsert: use updateExisting + conflictColumn + conflictValue to UPDATE existing rows (avoids NOT NULL issues).

Normalize a vector to unit length.

Analyze vector search performance and index effectiveness. Provide testVector for benchmarking (recommended).

Search for similar vectors. Requires: table, column, vector. Use select param to include identifying columns (e.g., select: ["id", "name"]).

Validate vector dimensions against column. Pass any combination of: vector (to check), table+column (for column dimensions), dimensions (expected).

Execute a write SQL query (INSERT, UPDATE, DELETE). Returns affected row count. Pass transactionId to execute within a transaction.