The Complete Production SOP: How to Deploy and Run Citus Distributed PostgreSQL Sharding in Autonomous AI Agents
Production SOP to install and run Citus PostgreSQL sharding skills in Codex, Claude Code, and Cursor. Includes verified schema migrations and troubleshooting.
Technical Overview & Architectural Context
Autonomous coding agents frequently fail when migrating monolithic PostgreSQL databases to Citus distributed architectures. Common agent pitfalls include generating standard SERIAL or UUID primary keys without including the distribution column (sharding key), orchestrating cross-shard JOINs on non-co-located tables, and failing to convert small dimension tables into Citus reference tables. These omissions trigger catastrophic PostgreSQL runtime errors (such as Citus error 0A000: cannot create foreign key constraint) or degrade distributed performance into massive coordinator-level full-table fetches. The postgres-citus-distributed-tables skill equips agents with a deterministic operational boundary. It forces the agent to alter primary and unique constraints to composite keys containing the distribution column (e.g., tenant_id), designates static lookup datasets as reference tables replicated across all worker nodes, and mandates EXPLAIN (VERBOSE) validation on generated SQL scripts to ensure distributed executor push-down before code or migrations are committed.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Create the local skill manifest directory and inject verified procedural rules for Citus sharding.
Deploy the SKILL.md file to the workspace agent skills directory (~/.agents/skills or .cursor/skills depending on runtime). Modern agent runtimes parse the YAML frontmatter to index triggers such as 'shard table with citus' and 'create distributed table'. Placing this file in your project or global skill path registers strict procedural boundaries for schema modifications.
mkdir -p .cursor/skills/postgres-citus-distributed-tables && curl -fsSL https://askills.dev/api/skills/postgres-citus-distributed-tables/raw -o .cursor/skills/postgres-citus-distributed-tables/SKILL.md---
name: "postgres-citus-distributed-tables"
description: "Scale PostgreSQL horizontally across worker nodes for multi-tenant and real-time analytics."
metadata:
author: "Citus Data"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "shard table with citus"
- "create distributed table"
- "configure citus reference table"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---
# Citus Distributed PostgreSQL Sharding
Distributes relational tables across a Citus cluster using hash distribution columns, co-located foreign keys, reference tables, distributed transactions, and parallel executor query optimization.
## When to Invoke
Invoke this skill whenever you need to:
- shard table with citus
- create distributed table
- configure citus reference table
## Execution SOP & Rules
1. Always construct composite PRIMARY KEY and UNIQUE constraints that include the distribution column (e.g. PRIMARY KEY (tenant_id, id)).
2. Execute SELECT create_distributed_table('table_name', 'distribution_column') after base table creation.
3. Execute SELECT create_reference_table('lookup_table') for lookup/dimension tables joined with distributed tables.
4. Foreign keys between distributed tables MUST include the distribution column and link to co-located tables.
5. Inspect distributed execution plans using EXPLAIN (VERBOSE) to check for remote distributed query pushes.
Keep the SKILL.md restricted to procedural rules and constraints; offload extensive Citus engine documentation to avoid inflating your agent's system token count.
Ensure that the agent runner has read permissions for the custom skill folder; otherwise, the trigger phrases will be processed using generic PostgreSQL defaults rather than Citus-specific sharding logic.
Environment Secrets & Citus Connection Validation
Inject database credentials safely without leaking secrets into conversation history.
Provide the coordinator connection string to the local development environment or test container. Agents operating under this skill do not require live production database credentials; local development harnesses or test databases should be pointed to a Citus coordinator node running in Docker.
test -f .env.local || echo 'DATABASE_URL=postgres://citus:secret@localhost:5432/citus_dev' >> .env.local && echo '.env.local' >> .gitignore-- Ensure Citus extension is enabled on the coordinator node
CREATE EXTENSION IF NOT EXISTS citus;
-- Verify active worker nodes in cluster
SELECT * FROM citus_get_active_worker_nodes();Never paste database passwords into user prompts; configure your local CLI runtime to load secrets strictly through workspace-scoped environment files like .env.local.
Running Citus sharding commands (create_distributed_table) without adding worker nodes via citus_add_node will cause the table to reside entirely on the coordinator node.
Intent Matching & Trigger Invocation
Verify deterministic activation using tested natural language prompts.
Prompt the agent using explicit trigger phrases found in the skill frontmatter. When the prompt contains 'shard table with citus' or 'create distributed table', the runtime matches the trigger and loads the procedural SOP, enforcing composite keys and reference table conventions immediately.
# In Claude Code CLI or Codex Desktop:
claude "Create a migration to shard table with citus for our multi-tenant SaaS schema: accounts, users, and audit_logs. tenant_id is the partition key."Specify the distribution column explicitly (e.g., 'distribute by tenant_id') in your prompt to prevent the agent from guessing surrogate keys like id or uuid.
Watch for agents attempting to create simple un-scoped UNIQUE(email) constraints on distributed tables; unique constraints must include the distribution column in Citus.
Verification, Linting & Output Quality Gate
Execute automated self-healing checks and migration dry-runs before accepting deliverables.
Instruct the agent to validate the generated DDL statements against a running Citus test instance or migration linter. The agent checks that all child tables contain the distribution key, verify foreign key co-location, and run EXPLAIN queries to ensure coordinator push-down.
psql $DATABASE_URL -f migrations/001_citus_schema.sql && psql $DATABASE_URL -c "SELECT logicalrelid, partkey FROM pg_dist_partition;"-- Confirm distributed tables and their partition keys
SELECT logicalrelid::regclass AS table_name,
partkey::text AS distribution_column,
colocation_id
FROM pg_dist_partition;
-- Verify query execution pushdown to workers
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM users u
JOIN audit_logs a ON u.tenant_id = a.tenant_id AND u.id = a.user_id
WHERE u.tenant_id = 42;Review git diff to verify that no global non-composite foreign keys were introduced between distributed tables before running migrations.
Avoid running transactional DDL statements that combine create_distributed_table() with large data insertions in a single transaction block on older Citus versions.
Real-World Scenario: Multi-Tenant E-Commerce Schema Sharding with Citus
“Write a PostgreSQL migration to shard table with citus for an e-commerce platform. We have tenants (accounts), products, orders, order_items, and a global currencies table. Use tenant_id as the distribution column.”
Runtime Troubleshooting & Failure Modes
Root Cause: The foreign key definition does not include the distribution column (tenant_id), or references a table distributed on a different column, preventing co-located shard validation.
ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey;
ALTER TABLE order_items ADD CONSTRAINT order_items_order_fk FOREIGN KEY (tenant_id, order_id) REFERENCES orders (tenant_id, id);Root Cause: PostgreSQL unique or primary key constraint was created on an identity column (e.g., id or sku) without including the sharding distribution column (tenant_id).
ALTER TABLE products DROP CONSTRAINT products_pkey;
ALTER TABLE products ADD PRIMARY KEY (tenant_id, id);
ALTER TABLE products ADD CONSTRAINT products_tenant_sku_uniq UNIQUE (tenant_id, sku);Frequently Asked Questions (Citus Distributed PostgreSQL Sharding SOP)
Q:Can I use Citus Distributed PostgreSQL Sharding across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the universal SKILL.md specification. Symlink or copy the SKILL.md directory into your workspace (.cursor/skills/, .agents/skills/, or Claude Code config) to enable identical procedural sharding rules across all IDEs and CLI agents.
Q:How does Citus Distributed PostgreSQL Sharding prevent context window overflow during long agent sessions?
By isolating the operational requirements (composite keys, reference tables, co-location requirements, EXPLAIN pushdown) into a tight procedural SOP, preventing the agent from pulling entire database manuals into its context window.
Q:Does this skill require escalated root or container permissions?
No. The skill is rated 98/100 on security, requiring zero shell execution permissions and no network access. It operates purely as declarative context guiding workspace file edits (SQL migrations and schema scripts).
Ready to equip your autonomous agent with Citus Distributed PostgreSQL Sharding?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.