Fire off raw SQL, run parameterized inserts/updates/deletes with automatic type coercion, and peek under the hood with EXPLAIN ANALYZE plans. Async variants let you fire-and-forget long-running operations without blocking your AI workflow.

🪄 Key moves: execute_query for ad-hoc SQL, explain_query to spot missing indexes or seq-scans, async_execute_* for bulk writes that outlive the request.

Peel back the layers of your database: list every table across schemas, drill into column types and nullability, inspect index definitions, trigger functions, check constraints, and navigate partitioned table hierarchies.

🪄 Key moves: describe_table is your go-to for column metadata, get_object_details shows DDL + stats in one shot, list_partitions maps your partitioning tree.

Full DDL surface for schema evolution. Spin up tables with typed columns, create views to simplify complex queries, generate sequences for auto-increment IDs, build indexes for performance, and partition large tables for manageability.

🪄 Key moves: backup_table snapshots a table before risky DDL, alter_view redefines without dropping, create_partition attaches new ranges to existing partition trees.

Move mountains of data in a single call. Insert thousands of rows with structured arrays, run bulk updates and deletes with filtered predicates, or use async_batch_insert_copy leveraging PostgreSQL's COPY protocol for wire-speed ingestion.

🪄 Key moves: async_batch_insert_copy is 2-3x faster than row-by-row INSERT for 10K+ rows, async_batch_update handles conditional multi-row updates atomically.

See how your database is really doing. Track table and index usage stats, measure disk consumption per database or table, calculate cache hit ratios, kick off VACUUM and ANALYZE, and query pg_stat_statements to find your top CPU-hungry queries.

🪄 Key moves: get_cache_hit_ratio tells you if your shared_buffers are sized right, get_pg_stat_statements surfaces slow queries by total time, analyze_table refreshes planner stats on-demand.

See who's connected, what they're running, and how connections are distributed. Diagnose connection bloat, find runaway queries, and identify which application is hogging the pool.

🪄 Key moves: show_running_queries catches long-running queries in-flight, show_connection_summary groups by state/application for a bird's-eye view.

Audit your security posture: enumerate database roles and their login capabilities, inspect table-level and schema-level GRANTs, check role membership chains, and review database-level ACLs.

🪄 Key moves: list_user_privileges surfaces exactly what each user can SELECT/INSERT/UPDATE/DELETE, list_role_memberships reveals privilege escalation paths through role inheritance.

Navigate the sprawling world of postgresql.conf without grepping. View every GUC parameter, look up specific settings by name, and filter by category to zero in on memory tuning, performance knobs, or logging config.

🪄 Key moves: show_memory_settings pulls shared_buffers, work_mem, maintenance_work_mem in one shot, get_setting for a quick SHOW of any parameter.

Dive into the transaction machinery: detect long-running idle-in-transaction sessions, map lock contention chains with pg_blocking_pids, identify deadlocks, check isolation levels, and monitor transaction age to prevent bloat.

🪄 Key moves: show_waiting_locks shows who's blocked by whom, show_deadlocks queries pg_stat_activity for blocked processes, show_transaction_timeout checks idle_in_transaction_session_timeout.

Keep your replicas healthy. Monitor streaming replication lag in bytes and time, inspect replication slots for WAL accumulation, list standby servers with their flush/replay positions, and track pg_stat_progress_basebackup for ongoing backups.

🪄 Key moves: show_replication_status shows sender/receiver pairs with lag, list_replication_slots helps you spot slots that are consuming too much WAL.

Get a comprehensive wellness check: scan for unused indexes that waste write IO, detect duplicate indexes that bloat storage, monitor VACUUM progress across all databases, and get a health score with actionable recommendations.

🪄 Key moves: list_unused_indexes finds indexes with zero scans since stats reset, show_vacuum_progress tracks autovacuum workers in real-time.

Safely and quickly remove all rows from a table while preserving the table structure. Performs privilege validation before executing, and supports RESTART IDENTITY for serial column reset.

🪄 Key moves: Blows away table data faster than DELETE — ideal for staging tables and temp data cleanup.

Analyzes your query workload from pg_stat_statements and suggests index candidates based on WHERE clauses, JOIN conditions, and ORDER BY patterns. Each suggestion includes the estimated impact and DDL to create it.

🪄 Key moves: Run after capturing a representative workload — the suggestions get smarter the more queries pg_stat_statements has sampled.

Deep-dive query forensics with buffer-level analysis. Inspect shared hits vs reads, execution time breakdowns by plan node, row estimate accuracy, and temp file spill detection. audit_performance runs a full sweep across your top queries.

🪄 Key moves: analyze_query_performance with EXPLAIN (ANALYZE, BUFFERS) catches seq-scans on large tables, misestimated row counts, and sort spills to disk.

Run a comprehensive security posture review: find users with superuser privileges, detect excessive schema-level GRANTs, map role inheritance chains that could lead to privilege escalation, and audit columns containing sensitive-sounding names.

🪄 Key moves: audit_security produces an executive summary with severity ratings, audit_role_hierarchy visualizes role grant chains that might bypass intended restrictions.

Unlock fuzzy and phonetic search across your text data. Search by vector similarity via pgvector, find approximate matches with Levenshtein distance, use trigram similarity for flexible substring matching, or try Soundex/Metaphone for phonetic lookups when you don't know the spelling.

🪄 Key moves: trigram_search handles typos and partial matches gracefully, soundex_search finds names that sound alike, full_text_config lets you inspect tsvector configuration.

Manage your PostgreSQL extension ecosystem. List installed extensions with versions, install new extensions from available packages, upgrade to latest versions, remove unused ones, and inspect extension dependencies to understand what cascade effects to expect.

🪄 Key moves: extension_details shows extension-to-extension dependency chains, install_extension supports CASCADE for dependency auto-install.

Evolve your schema surgically without writing raw DDL. Add nullable or NOT NULL columns with default values, change column types (when castable), drop obsolete columns, manage CHECK and UNIQUE constraints, and set or remove column defaults.

🪄 Key moves: add_column with NOT NULL + default backfills existing rows, drop_default stops future inserts from getting the default without touching existing data.

Twist session-level config knobs without editing postgresql.conf. Uses SET LOCAL scoped to the transaction for safety — settings auto-revert on commit/rollback so you never accidentally leave a modified config behind.

🪄 Key moves: set_session_setting for per-query statement_timeout or work_mem tweaks, reset_session_setting reverts a single setting to its cluster default.

Manage database role lifecycle with safety guards. Create users with password and login privileges, alter existing roles (rename, change password, toggle superuser/createrole flags), and drop users with ownership reassignment and dependency checks.

🪄 Key moves: drop_user refuses if the user owns objects (prevents accidental CASCADE carnage), alter_user can set per-role GUC defaults like statement_timeout.

Explore, profile, and clean your data. Export tables to structured results, search across columns with pattern matching, compare two datasets row-by-row, profile column distributions and null ratios, find duplicate rows and orphaned foreign keys, and estimate table bloat to plan VACUUMs.

🪄 Key moves: data_profile gives you min/max/null_counts/approx_distinct per column in one shot, find_orphans detects FK violations where parent rows went missing, show_table_bloat estimates wasted space.

End-to-end schema migration workflow. Generate timestamped migration files from DDL templates, apply pending migrations in order, rollback the last applied migration, track which migrations have been run, and validate the current schema state against the migration log.

🪄 Key moves: validate_migration detects schema drift (manual changes that skipped the migration system), rollback_migration reverts safely using generated down-scripts.

pgvector-powered similarity search for embeddings and vector data. Search by L2 distance, inner product, or cosine similarity with optional metadata filters. Supports indexing (IVFFlat, HNSW) for fast approximate nearest-neighbor lookups.

🪄 Key moves: Perfect for RAG workflows — feed in your embedding vectors and retrieve the top-K most semantically similar results with a single call.

TimescaleDB integration for time-series workloads. Inspect hypertable chunks, analyze compression ratios, review retention policies, and get recommendations for chunk interval tuning based on your ingestion patterns.

🪄 Key moves: Run analyze_timescale to check if your compression policy is keeping up with ingestion volume, or if chunk intervals need resizing.

Move data in and out of PostgreSQL in every common format. Import CSV/JSON with automatic type detection and error handling, export query results to CSV/JSON, paginate through large result sets with keyset pagination, and bulk-load from server-side files.

🪄 Key moves: show_table_data_paginated handles million-row tables without blowing up your context, bulk_load_from_file uses server-side COPY for zero-network-overhead ingestion.