Scheduling and Partitioning Extensions

Scheduling and Partitioning Extensions

postgres-mcp provides comprehensive support for automation and performance monitoring through pg_cron (8 tools), pg_partman (10 tools), pg_stat_kcache (7 tools), and citext (6 tools).

---

pg_cron (8 Tools)

pg_cron enables job scheduling directly within PostgreSQL using familiar cron syntax.

Installation

-- Requires shared_preload_libraries configuration
-- Add to postgresql.conf: shared_preload_libraries = 'pg_cron'
-- Restart PostgreSQL, then:
CREATE EXTENSION IF NOT EXISTS pg_cron;

Tools

Tool	Description
pg_cron_create_extension	Enable pg_cron extension
pg_cron_schedule	Create a scheduled job
pg_cron_schedule_in_database	Schedule job in specific database
pg_cron_unschedule	Remove a scheduled job
pg_cron_alter_job	Modify an existing job
pg_cron_list_jobs	List all scheduled jobs
pg_cron_job_run_details	View job execution history
pg_cron_cleanup_history	Clean up job run history

Cron Syntax

┌──────── minute (0 - 59)
│ ┌────── hour (0 - 23)
│ │ ┌──── day of month (1 - 31)
│ │ │ ┌── month (1 - 12)
│ │ │ │ ┌ day of week (0 - 6, Sunday = 0)
│ │ │ │ │
* * * * *

Example: Schedule Vacuum

-- VACUUM every night at 3 AM
SELECT cron.schedule('nightly_vacuum', '0 3 * * *', 'VACUUM ANALYZE');

-- Delete old data every Sunday
SELECT cron.schedule('weekly_cleanup', '0 0 * * 0',
  'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days''');

---

pg_partman (10 Tools)

pg_partman automates partition management for time-series and ID-based data.

Installation

CREATE EXTENSION IF NOT EXISTS pg_partman;

Tools

Tool	Description
pg_partman_create_extension	Enable pg_partman extension
pg_partman_create_parent	Create parent partition table
pg_partman_run_maintenance	Run partition maintenance
pg_partman_show_partitions	List partitions for a table
pg_partman_show_config	View partition configuration
pg_partman_check_default	Check default partition for data
pg_partman_partition_data	Move data into child partitions
pg_partman_set_retention	Configure retention policies
pg_partman_undo_partition	Undo partitioning
pg_partman_analyze_partition_health	Analyze partition health

Example: Time-Based Partitioning

-- Create partitioned table
CREATE TABLE events (
  id SERIAL,
  event_time TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (event_time);

-- Configure pg_partman
SELECT partman.create_parent(
  'public.events',
  'event_time',
  'native',
  'daily',
  p_premake := 7,
  p_start_partition := NOW()::date::text
);

Maintenance

pg_partman requires periodic maintenance to create new partitions and apply retention:

-- Run maintenance (schedule via pg_cron)
SELECT partman.run_maintenance();

---

pg_stat_kcache (7 Tools)

pg_stat_kcache provides OS-level statistics for CPU and I/O usage per query.

Installation

-- Requires shared_preload_libraries configuration
-- Add to postgresql.conf: shared_preload_libraries = 'pg_stat_kcache,pg_stat_statements'
-- Restart PostgreSQL, then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;

Tools

Tool	Description
pg_kcache_create_extension	Enable pg_stat_kcache extension
pg_kcache_query_stats	Query-level CPU/IO statistics
pg_kcache_top_cpu	Queries by CPU usage
pg_kcache_top_io	Queries by I/O usage
pg_kcache_database_stats	Database-level resource stats (all databases if no database specified)
pg_kcache_resource_analysis	Resource usage analysis
pg_kcache_reset	Reset statistics

Example: Find CPU-Intensive Queries

SELECT query,
       total_cpu_user + total_cpu_system AS total_cpu,
       calls,
       (total_cpu_user + total_cpu_system) / calls AS avg_cpu_per_call
FROM pg_stat_kcache() k
JOIN pg_stat_statements s ON k.queryid = s.queryid
ORDER BY total_cpu DESC
LIMIT 10;

---

citext (6 Tools)

citext provides case-insensitive text storage and comparison.

Installation

CREATE EXTENSION IF NOT EXISTS citext;

Tools

Tool	Description
pg_citext_create_extension	Enable citext extension
pg_citext_convert_column	Convert text column to citext
pg_citext_list_columns	List citext columns in database
pg_citext_analyze_candidates	Find columns suitable for citext
pg_citext_compare	Compare case-insensitive values
pg_citext_schema_advisor	Schema migration recommendations

Example: Case-Insensitive Email

-- Create table with citext email
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email CITEXT UNIQUE,
  name TEXT
);

-- Queries are case-insensitive
INSERT INTO users (email, name) VALUES ('User@Example.com', 'John');

-- These all find the same row:
SELECT * FROM users WHERE email = 'user@example.com';
SELECT * FROM users WHERE email = 'USER@EXAMPLE.COM';
SELECT * FROM users WHERE email = 'User@Example.com';

---

Tool Filtering

Combine Related Groups

# Job Scheduling + Partitioning
--tool-filter cron,partman

# Performance + OS-Level Metrics
--tool-filter performance,kcache

Custom Combinations

# Just pg_cron
--tool-filter cron

# pg_cron + pgcrypto
--tool-filter cron,pgcrypto

# Core operations
--tool-filter core,jsonb,transactions,codemode

---

Related Resources

Resource	URI	Description
Cron Status	postgres://cron	Job status, execution history
Partman Status	postgres://partman	Partition configuration, health
Kcache Status	postgres://kcache	CPU/IO metrics summary

---

Related Prompts

Prompt	Description
pg_setup_pgcron	Complete pg_cron setup guide
pg_setup_partman	Complete pg_partman setup guide
pg_setup_kcache	Complete pg_stat_kcache setup guide
pg_setup_citext	Complete citext setup guide

---

Related

• Extension-Overview - All supported extensions
• Tool-Filtering - Filter configuration