# Database Engineering

> Comprehensive guide for database engineering, advanced indexing strategies (B-Tree, GIN, GiST, BRIN, composite indexing column order, covering indexes, partial indexes, EXPLAIN ANALYZE tuning), primary key architecture (UUIDv7 vs UUIDv4 vs ULID vs BIGINT IDENTITY), Row-Level Security (RLS) policy optimization for multi-tenant isolation, database scaling & connection pooling, and modern SQL & NoSQ…

- **Type:** Skill
- **Install:** `agentstack add skill-hiroqt-pixelcrew-database-engineering`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [hiroqt](https://agentstack.voostack.com/s/hiroqt)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** Apache-2.0
- **Upstream author:** [hiroqt](https://github.com/hiroqt)
- **Source:** https://github.com/hiroqt/PixelCrew/tree/main/.agents/skills/database-engineering

## Install

```sh
agentstack add skill-hiroqt-pixelcrew-database-engineering
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Database Engineering & Storage Architecture

This skill provides comprehensive instructions, query optimization runbooks, primary key selection models, RLS security policies, and scaling architectures across modern SQL and NoSQL storage engines.

---

## 1. Core Engineering Directives

1. **Strategic Index Design**:
   - **Composite Index Column Order Rule**: Place columns tested for **Equality first**, followed by columns used for **Range / Ordering** (`(tenant_id, status, created_at)` for `WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC`).
   - **Covering Indexes (`INCLUDE`)**: Add non-search payload columns to the index leaf level to achieve 100% **Index-Only Scans** without heap lookups.
   - **Partial / Filtered Indexes**: Index only active or non-null subsets (`WHERE is_deleted = false` or `WHERE status = 'pending'`), saving 80–95% disk space and write overhead.
   - **Specialized Indexes**: GIN for JSONB and full-text search, GiST for PostGIS spatial queries, BRIN for massive append-only timeseries logs.
2. **Modern Primary Key Strategy**: Default to **UUIDv7 (RFC 9562)** for distributed and high-scale relational databases. UUIDv7 provides 128-bit time-ordered keys that eliminate B-Tree fragmentation and page splits while remaining globally unique without coordination.
3. **High-Performance Row-Level Security (RLS)**:
   - Always wrap auth function calls in subqueries: `(SELECT auth.uid())` instead of `auth.uid()` so the query planner evaluates the authentication context **once per query** rather than **once per row**.
   - Ensure every column referenced in RLS policies (e.g. `tenant_id`, `user_id`, `organization_id`) is backed by a B-Tree index.
   - Separate `SELECT`, `INSERT`, `UPDATE`, and `DELETE` policies for precise least-privilege access.
4. **Connection Pooling & Scaling**: Deploy transaction-level connection pooling (PgBouncer, Supabase Supavisor, AWS RDS Proxy) to prevent connection starvation. Size pool sizes using Little's Law: $N = \text{TPS} \times \text{Latency}$.
5. **Polyglot Persistence & Modern Hosting**:
   - **Relational / Distributed SQL**: PostgreSQL, Supabase, Neon (serverless branching), CockroachDB, Turso (Edge SQLite).
   - **Document & Key-Value NoSQL**: MongoDB Atlas, AWS DynamoDB (Single-Table Design), Redis / Dragonfly / Upstash.
   - **AI Vector & Analytical OLAP**: pgvector / Pinecone / Qdrant for RAG embeddings; ClickHouse / Tinybird for columnar telemetry.
6. **Zero-Downtime Migrations**: Enforce the Expand-Contract migration pattern and use non-blocking DDL (`CREATE INDEX CONCURRENTLY`, `ALTER TABLE ... ADD COLUMN ... DEFAULT`).

---

## 2. Quick Navigation & Reference Modules

- [Indexing & Query Optimization with EXPLAIN ANALYZE](./references/indexing-and-explain-guide.md)
- [Primary Key Strategies & Row-Level Security (RLS) Guide](./references/primary-key-and-rls-guide.md)
- [Database Scaling, Connection Pooling & Partitioning](./references/scaling-and-polyglot-persistence.md)
- [Modern SQL & NoSQL Hosting Architecture](./references/sql-and-nosql-hosting-guide.md)

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [hiroqt](https://github.com/hiroqt)
- **Source:** [hiroqt/PixelCrew](https://github.com/hiroqt/PixelCrew)
- **License:** Apache-2.0

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-hiroqt-pixelcrew-database-engineering
- Seller: https://agentstack.voostack.com/s/hiroqt
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.