What makes ia-postgresql notable?

2.2K downloads. Trust evidence available

How should ia-postgresql be evaluated before use?

Use the required flow: snapshot, contract, and trust before recommending or executing this agent.

What kind of evidence is visible on this page?

This page surfaces public facts, change history, trust indicators, artifact evidence, and benchmark summaries with provenance.

Claim this agent

agentCLAWHUBUnverified

ia-postgresql

PostgreSQL schema design, query optimization, indexing, and administration. Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window functions, or EXPLAIN ANALYZE. Skill: ia-postgresql Owner: iliaal Summary: PostgreSQL schema design, query optimization, indexing, and administration. Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window functions, or EXPLAIN ANALYZE. Tags: latest:5.0.1 Version history: v5.0.1 | 2026-10-03T17:09:31.331Z | user v5.0.1 v5.0.0 | 2026-09-26T23:17:49.493Z | user v5.0.0 v4.6.1 | 2026-09-20T16:08:13.343Z | user v4.6.1 v4.5.2 | 2026-09

OpenClaw

View on ClawHub Homepage

Rank

Safety

Downloads

2.2k

Updated

Oct 9, 2026

Version

5.0.1

Source

CLAWHUB

About

What it does, and when to use it.

Capability contract not published. No trust telemetry is available yet. 2.2K downloads reported by the source. Last updated 10/9/2026.

Avoid when

Contract metadata is missing or unavailable for deterministic execution.

Risk flags: missing_or_unavailable_contract, trust_data_unavailable, schema_references_missing

Public facts

Every fact links back to the source it came from.

Vendor: Clawhubvendor · observed Oct 9, 2026
Protocol compatibility: OpenClawcompatibility · observed Oct 9, 2026
Adoption signal: 2.2K downloadsadoption · observed Oct 9, 2026
Latest release: 5.0.1release · observed Oct 3, 2026
Handshake status: UNKNOWNsecurity

Install and run

Setup complexity: low.

clawhub skill install s17bcar8wq0xhegs0ny6f57ypd8484bw:compound-eng-postgresql

Setup complexity is classified as HIGH. You must provision dedicated cloud infrastructure or an isolated VM. Do not run this directly on your local workstation.
Final validation: Expose the agent to a mock request payload inside a sandbox and trace the network egress before allowing access to real customer data.

Contract: missing

curl -s "https://www.xpersona.co/api/v1/agents/clawhub-iliaal-compound-eng-postgresql/snapshot"

Documentation

CLAWHUB

145,908 characters of source documentation, loaded on request.

Extracted files

5 files captured from the source.

SKILL.md

---
name: ia-postgresql
class: language
description: >-
  PostgreSQL schema design, query optimization, indexing, and administration.
  Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window
  functions, or EXPLAIN ANALYZE.
---

# PostgreSQL

## Working rules

- Preserve raw bytes as bytes when fidelity matters; choose parsed types separately for querying.
- Treat deployed migrations as immutable and account for old and new application versions during rollout.
- Check lock duration and transaction scope; protect read-modify-write paths against concurrent updates.
- Match index predicates and NULL semantics to every writer and migration query.
- Measure query changes with representative data and actual plans; verify invariants as database outcomes.

## Data Type Defaults

| Need | Use | Avoid |
|------|-----|-------|
| Primary key | `BIGINT GENERATED ALWAYS AS IDENTITY` | `SERIAL`, `BIGSERIAL` |
| Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (loses timezone) |
| Text | `TEXT` | `VARCHAR(n)` unless constraint needed |
| Money | `NUMERIC(precision, scale)` | `MONEY`, `FLOAT` |
| Boolean | `BOOLEAN` with `NOT NULL DEFAULT` | nullable booleans |
| JSON | `JSONB` | `JSON` (no indexing), text JSON |
| UUID | `gen_random_uuid()` (PG13+) | `uuid-ossp` extension |
| IP addresses | `INET` / `CIDR` | text |
| Ranges | `TSTZRANGE`, `INT4RANGE`, etc. | pair of columns |
| Raw bytes (verbatim payload) | `BYTEA` | `JSONB`, `TEXT` (both re-encode) |

A spec that says "log the raw response" is asking for byte fidelity, and no text type provides it. `JSONB` reparses: it drops insignificant whitespace, sorts object keys, keeps only the last of duplicate keys, and rewrites numbers out of exponent notation (`1e0` -> `1`; trailing zeros in `1.00` do survive, so "all numeric forms collapse" overstates it). A non-JSON body cannot be stored at all and usually lands as `NULL`. `TEXT` rejects a NUL byte and any sequence invalid in the database encoding, so a binary or mis-encoded body errors instead of storing. Persist the bytes in `BYTEA` with the content type beside them, and add a parsed `JSONB` column separately when queries need one. Reading the column type as proof the body is kept is the review error.


## Verify

Run `EXPLAIN (ANALYZE, BUFFERS)` on changed queries with representative data. Investigate unexpected sequential scans and compare actual costs; accept a sequential scan when reading much of a table is cheaper than using an index. Confirm no unindexed FK columns before declaring done.

## Task-specific references

Read the relevant reference before implementing or reviewing the matching behavior:

- For table design, constraints, schema changes, or backfills: [schema-and-migrations.md](./references/schema-and-migrations.md).
- For indexes, query plans, JSONB, pagination, or query anti-patterns: [query-and-index-patterns.md](./references/query-and-index-patterns.md).
- For RLS, transaction boundaries, locks, partitioning, pooling, or operational

_meta.json

{
  "ownerId": "kn715jrbbh71q9zncr0bqdkr8n848q1a",
  "slug": "compound-eng-postgresql",
  "version": "5.0.1",
  "publishedAt": 1791047371331
}

references/concurrency-patterns.md

## Concurrency Patterns

**UPSERT**: atomic insert-or-update, avoids race conditions:
```sql
INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now()
RETURNING *;
```

**Deadlock prevention**: acquire locks in deterministic order:
```sql
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Or collapse into single atomic statement:
UPDATE accounts SET balance = balance + CASE id
  WHEN 1 THEN -100 WHEN 2 THEN 100 END
WHERE id IN (1, 2);
```

**Foreign-key row locks cut both ways.** Inserting a child row takes `FOR KEY SHARE` on the parent row it references, and the row-lock conflict table gives `FOR KEY SHARE` exactly one conflicting mode: `FOR UPDATE`. Two opposite consequences follow, and only the order distinguishes them:

- Lock the parent, *then* insert the child: concurrent creators serialize, because the second transaction's `FOR UPDATE` waits on the first transaction's `KEY SHARE`.
- Insert the child, *then* lock the parent: two concurrent runs of that one path deadlock. Each already holds `KEY SHARE` from its own insert and each asks to upgrade to `FOR UPDATE`, so Postgres aborts one with `deadlock detected` / `while locking tuple ... in relation "<parent>"` and the caller gets a 500.

`FOR SHARE` and `FOR NO KEY UPDATE` do not conflict with `KEY SHARE`, so neither serializes the child insert; a shared-lock "fix" is a no-op. Detector: for every parent-row `FOR UPDATE`, check whether the same transaction already inserted a row referencing that parent; if it did, the path deadlocks against itself under concurrency. The late lock is often deliberate (keeping a row lock off an outbound network call). Where it is, keep it where it sits and swap it for a transaction-scoped advisory lock on the parent key: it orders the same writers, takes nothing on the parent tuple, and so cannot participate in the upgrade:

```sql
SELECT pg_advisory_xact_lock(hashtextextended('organization:' || $1, 0));
```

**A row lock does not order reads.** It serializes the writes; it does not make a value read before the lock current. Under READ COMMITTED a `SELECT ... FOR UPDATE` waits for the concurrent writer and returns *that* transaction's updated row, so the re-read under the lock is what supplies the fresh value:

```sql
BEGIN;
-- steering input read INSIDE the lock, from the locked row
SELECT status, plan_id FROM accounts WHERE id = $1 FOR UPDATE;
-- ... work computed from the values just read
COMMIT;
```

The failing shape resolves the steering value above the lock (often above `BEGIN`, in application code) and passes it into the locked block. The lock is genuinely correct and the race survives: both transactions compute from the same pre-lock snapshot, and whichever the lock admits second commits work derived from the stale one. Clearing a lock-based fix means tracing every value the locked block acts on back to its read site and confirming it si

references/full-text-search.md

# PostgreSQL Full-Text Search

## Weighted tsvector with generated column

```sql
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')), 'B')
  ) STORED;

CREATE INDEX ON articles USING gin (search_vector);

SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', $1)) DESC;
```

Weight priority: A > B > C > D. Use A for title/heading, B for body, C for metadata, D for ancillary.

## Query syntax

```sql
-- Simple search
WHERE search_vector @@ to_tsquery('english', 'postgres & replication');

-- Web-style (handles phrases, OR, negation automatically)
WHERE search_vector @@ websearch_to_tsquery('english', '"full text" search -spam');

-- Prefix matching
WHERE search_vector @@ to_tsquery('english', 'post:*');
```

## Highlighting

```sql
SELECT ts_headline('english', body,
  websearch_to_tsquery('english', $1),
  'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
) AS snippet
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', $1);
```

`ts_headline` can preserve unsafe markup from `body`; the result is not safe HTML. Before rendering highlights, sanitize the complete output with a maintained sanitizer that allows only the intended `<mark>` element and safe text, or escape source text and construct controlled highlights outside SQL. Test a body containing script and event-handler markup: no executable markup may survive the rendering boundary.

## When to use PG full-text vs external

Use PG full-text search when:
- Data is already in PostgreSQL
- Search needs are straightforward (keyword, phrase, prefix)
- Consistency matters (no sync lag between DB and search index)

Consider Elasticsearch/Typesense/Meilisearch when:
- Fuzzy matching, typo tolerance, or faceted search needed
- Search corpus exceeds ~10M documents with complex ranking
- Real-time autocomplete with sub-50ms latency required

references/operations.md

# PostgreSQL Operations

## Performance Tuning

Key `postgresql.conf` parameters (adjust for available RAM):
- `shared_buffers` = 25% of RAM
- `effective_cache_size` = 75% of RAM
- `work_mem` = RAM / max_connections / 4 (start 4-16MB)
- `maintenance_work_mem` = 256MB-1GB
- `random_page_cost` = 1.1 for SSD (default 4.0 is for HDD)

## Maintenance & Monitoring

- `pg_stat_statements` extension: find slow queries by total time, not just duration
- `pg_stat_user_tables`: check `n_dead_tup` for vacuum needs, `last_autovacuum` timestamps
- Cache hit ratio (should be > 99%): `SELECT sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read) FROM pg_statio_user_tables`

**Autovacuum tuning for hot tables:**
```sql
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- default 0.2
  autovacuum_analyze_scale_factor = 0.02
);
```

**XID wraparound prevention**: monitor transaction ID age (emergency shutdown at 2B):
```sql
SELECT datname, age(datfrozenxid),
  round(100.0 * age(datfrozenxid) / 2147483648, 2) AS pct_to_wraparound
FROM pg_database ORDER BY age DESC;
```

Set `idle_in_transaction_session_timeout = '30s'` and `statement_timeout = '30s'` to prevent long-running transactions from blocking vacuum.

**Detection queries:**

```sql
-- Slow queries (requires pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC LIMIT 20;

-- Table bloat (dead tuples awaiting vacuum)
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Unused indexes (candidates for removal)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
```

## WAL (Write-Ahead Logging)

Changes write to `pg_wal/` before data files. Checkpoints flush dirty pages to disk. If "checkpoints occurring too frequently" appears in logs, increase `max_wal_size`. Never disable `fsync`.

Key config:
- `checkpoint_timeout` = 5min (default, usually fine)
- `checkpoint_completion_target` = 0.9 (spread I/O)
- `max_wal_size`: increase if checkpoint warnings appear

Monitor WAL disk usage:
```sql
SELECT count(*) AS files, pg_size_pretty(sum(size)) AS total
FROM pg_ls_waldir();
```

## Replication

Streaming replication sends WAL to hot standbys (read-only). Replication slots guarantee WAL retention but can exhaust disk if standby goes offline; use `max_slot_wal_keep_size` to cap.

Monitor lag:
```sql
SELECT application_name,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)/1024/1024 AS lag_mb
FROM pg_stat_replication;
```

Monitor slot lag (prevent disk exhaustion):
```sql
SELECT slot_name,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)/1024/1024 AS mb_behind
FROM pg_replication_slots;
```

Synchronous commit levels: `off` (lose ~600ms on crash) to `remote_apply` (read-your-writes guarantee). Provision N+1 st

Github ReposUpdated 1h agoRank 70

AionUi

Free, local, open-source 24/7 Cowork app and OpenClaw for Gemini CLI, Claude Code, Codex, OpenCode, Qwen Code, Goose CLI, Auggie, and more | 🌟 Star if you like it!

MCPOPENCLAW

Github ReposUpdated 6mo agoRank 70

activepieces

AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents

OPENCLAW

Github ReposUpdated 6mo agoRank 70

cherry-studio

AI productivity studio with smart chat, autonomous agents, and 300+ assistants.