ViduraRedshift Tools19 Tools

Redshift Performance Tools

Vidura exposes 19 specialized tools for querying Amazon Redshift system tables and views. These tools are called by the LangChain agent to gather performance data, storage metrics, and query analysis.

Connection

All Redshift tools use a psycopg2 connection (synchronous) wrapped in anyio.to_thread.run_sync() to avoid blocking the async FastAPI event loop.

Mathematical Foundations

Storage Calculations

Storage Math

# Block to GB conversion (Redshift blocks = 8 KB each)
spill_gb = (spilled_block_count * 8.0) / (1024 * 1024)

# Vacuum reclaimable space (each impacted_block = 1 MB)
reclaimable_gb = impacted_blocks / 1024.0

# Storage utilization percentage
storage_pct = (1.0 * used_mb / capacity_mb) * 100

# Estimated deleted row size
deleted_size_gb = (est_deleted_rows * 4) / 1024.0 / 1024.0
# Assumes 4 bytes average per deleted row

Ghost Row Percentage

Ghost Row Formula

-- Ghost rows = rows marked for deletion but not yet vacuumed
pct_rows_marked_for_deletion =
  (tbl_rows - estimated_visible_rows)
  / NVL(tbl_rows, 1) * 100
-- NVL prevents division by zero on empty tables

Node Type Detection

Node Type Detection Logic

CASE
  WHEN capacity = 190633 AND NOT is_nvme THEN 'dc1.large'
      -- 176 GB traditional SSD
  WHEN capacity = 380319              THEN 'dc1.8xlarge'
      -- 352 GB traditional SSD
  WHEN capacity = 190633 AND is_nvme  THEN 'dc2.large'
      -- 160 GB NVMe SSD
  WHEN capacity = 760956              THEN 'dc2.8xlarge'
      -- 640 GB NVMe SSD
  WHEN capacity = 726296              THEN 'dc2.8xlarge'
      -- Alternative dc2.8xlarge config
  WHEN capacity = 952455 AND part_count = 1 THEN 'ra3.xlplus'
      -- 32 TB managed storage
  WHEN capacity = 3339176 AND part_count = 1 THEN 'ra3.4xlarge'
      -- 128 TB managed storage
  WHEN capacity = 3339176 AND part_count = 4 THEN 'ra3.16xlarge'
      -- 128 TB per node, 4 partitions
END AS node_type

Alert Event Bitmask

Alert Bitmask Encoding

-- Each bit position encodes a distinct alert type
-- Event integer stores multiple simultaneous alerts compactly

DECODE(event & 1,  1, 'Sortkey ',   '') ||  -- Bit 0: Sort key issue
DECODE(event & 2,  2, 'Deletes ',   '') ||  -- Bit 1: High delete rows
DECODE(event & 4,  4, 'NL ',        '') ||  -- Bit 2: Nested loop join
DECODE(event & 8,  8, 'Dist ',      '') ||  -- Bit 3: Distribution mismatch
DECODE(event & 16, 16,'Broadcast ', '') ||  -- Bit 4: Large broadcast
DECODE(event & 32, 32,'Stats',      '')     -- Bit 5: Stale statistics

-- Example: event=37 (binary 100101)
-- = Sortkey (1) + Deletes (4) + Stats (32) = 'Sortkey Deletes Stats'

Tool Reference

General

run_sql(query)Direct Redshift

Execute arbitrary read-only SQL directly on Redshift. Used by the agent for ad-hoc queries not covered by other tools.

application_names()quper.applications

Lists all registered application names for the cluster, used to filter query analysis by application.

Monitoring

alert_logs()quper.alert_logs

Returns the 5 most recent entries from the Quper alert log table. Shows which alerts have fired recently.

table_alerts()stl_alert_event_log

Alert events from the past 7 days per table. Alert types encoded as bitmask (Sortkey=1, Deletes=2, NL=4, Dist=8, Broadcast=16, Stats=32).

Storage

column_stats()quper.column_stats

Returns column-level statistics including NULL percentage, distinct value count, and data type distribution.

table_storage_summary()svv_table_info + stl_vacuum

Large table storage analysis. Only returns tables with >1M rows. Shows size, distribution skew, and vacuum history.

reclaimed_space()stl_vacuum

Top 5 tables by reclaimable space post-vacuum. Formula: reclaimable_gb = impacted_blocks / 1024.0 (each block = 1 MB).

volume_stats()CTE: stl_insert + stl_delete + table stats

DML volume since last ANALYZE per table using a CTE chain. Shows inserts, deletes, and net row change to guide ANALYZE scheduling.

Performance

daily_app_average()sys_query_history + sys_connection_log

Computes the daily average query execution time per application using a 7-day rolling window with a JOIN between query history and connection logs.

long_running_queries()stv_wlm_query_state

Returns currently executing queries with exec_time > 300 seconds (5 minutes). Includes WLM queue, user, and query text.

query_spill()sys_query_detail

Top 10 queries by disk spill volume. Spill size is computed as: (spilled_block_count × 8.0) / (1024 × 1024) GB.

queue_time_query()sys_query_history

Queries where queue_time > 0, indicating WLM contention. Shows queue wait time vs. execution time for each query.

table_scan_stats()quper.table_scan_stats

Table scan frequency and size statistics from the Quper-maintained stats table.

top_50_queries_by_runtime()stl_query (7-day window)

Queries aggregated by MD5 fingerprint, ordered by average runtime. Shows execution count, avg/max runtime, and compile overhead.

top_50_queries_by_diskspill()stl_query (7-day window)

Same as top_50_queries_by_runtime but ordered by total disk spill volume instead of runtime.

Health

freshness_stats()svv_table_info + stl_analyze

Shows the last ANALYZE timestamp per table. Flags tables not analyzed in >7 days as stale.

Infrastructure

node_details()stv_slices + stv_node_storage_capacity

Cluster node hardware specifications. Node type is determined by matching storage capacity against known capacity thresholds for DC1, DC2, DS2, and RA3 nodes.

Optimization

sort_key_and_encoding_details()svv_table_info + pg_attribute + svv_columns

Column-level encoding and sort key details for all tables. Used to identify compression opportunities and sort key effectiveness.

Metadata

table_info()10+ system views

Comprehensive table metadata joining svv_table_info, stl_vacuum, stl_alert_event_log, pg_attribute, and more. The most data-rich tool in the suite.

Maintenance

vacuum_stats()svv_table_info + stl_vacuum

Top 5 tables by unsorted_pct. Includes reclaimable space estimate and last vacuum date for vacuum prioritization.

RAG

book_qa(query)Milvus Vector DB

Semantic search over AWS Redshift documentation (Developer Guide + Management Guide). Returns relevant text chunks for the query.