Guides database architecture decisions for PostgreSQL, DuckDB, Parquet, PGVector, and Neo4j. Use when designing schemas, choosing storage strategies, optimizing queries, tuning maintenance, configuring vector search, modeling graph data, or diagnosing performance issues across OLTP, OLAP, similarity search, and graph workloads.
Guides database architecture decisions and performance tuning for PostgreSQL, DuckDB, Parquet, PGVector, and Neo4j workloads.
/plugin marketplace add rileyhilliard/claude-essentials/plugin install ce@claude-essentialsThis skill inherits all available tools. When active, it can use any tool Claude has access to.
references/duckdb-architecture.mdreferences/duckdb-querying.mdreferences/neo4j-architecture.mdreferences/neo4j-querying.mdreferences/parquet-architecture.mdreferences/parquet-querying.mdreferences/pgvector-architecture.mdreferences/pgvector-querying.mdreferences/postgres-architecture.mdreferences/postgres-querying.mdDecision guidance for PostgreSQL, DuckDB, Parquet, and Neo4j in hybrid storage architectures.
| Workload | Use | Why |
|---|---|---|
| Transactional (CRUD, users, sessions) | PostgreSQL | ACID, row-level locking, indexes |
| Analytical (aggregations, scans) | DuckDB | Columnar, vectorized, parallel |
| Data storage/interchange | Parquet | Compressed, columnar, portable |
| Metadata + relationships | PostgreSQL | Foreign keys, constraints |
| Ad-hoc exploration | DuckDB | Fast on Parquet, no ETL needed |
| Time-series with point lookups | PostgreSQL + partitioning | Partition pruning + indexes |
| Time-series analytics | DuckDB on Parquet | Scan performance |
| Vector similarity search | PostgreSQL + PGVector | HNSW/IVFFlat indexes, hybrid search |
| RAG / semantic search | PostgreSQL + PGVector | Embeddings + metadata in same DB |
| Graph traversals / relationships | Neo4j | Native graph, index-free adjacency |
| Pattern matching / fraud detection | Neo4j | Multi-hop traversal, path finding |
| Knowledge graphs / ontologies | Neo4j | Flexible schema, relationship-first |
Hybrid pattern example:
Use for: Metadata, relationships, OLTP workloads, anything needing ACID.
Key decisions:
See references/postgres-architecture.md for maintenance patterns. See references/postgres-querying.md for advanced query techniques.
Use for: Analytics, aggregations, Parquet queries, data exploration.
Key decisions:
See references/duckdb-architecture.md for storage and parallelism. See references/duckdb-querying.md for DuckDB-specific SQL features.
Use for: Storing analytical data, data interchange, columnar compression.
Key decisions:
See references/parquet-architecture.md for file design. See references/parquet-querying.md for query optimization.
Use for: Similarity search, RAG applications, semantic search, recommendations.
Key decisions:
See references/pgvector-architecture.md for index configuration. See references/pgvector-querying.md for hybrid search and filtering.
Use for: Graph traversals, relationship-heavy queries, pattern matching, knowledge graphs.
Key decisions:
[*1..5], never [*])See references/neo4j-architecture.md for data modeling, indexing, and maintenance. See references/neo4j-querying.md for Cypher optimization and anti-patterns.
| Convention | Example | Applies to |
|---|---|---|
| snake_case tables | dataset_jobs | All |
| snake_case columns | created_at | PG, DuckDB, Parquet |
| camelCase properties | createdAt | Neo4j |
| PascalCase labels | :UserAccount | Neo4j |
| Singular table names | dataset not datasets | PostgreSQL |
| Plural for collections | datasets/ directory | Parquet files |
| Pattern | When to normalize | When to denormalize |
|---|---|---|
| Lookup tables | PostgreSQL, changes frequently | DuckDB/Parquet, static data |
| Repeated values | PostgreSQL, storage matters | Parquet, compression handles it |
| Joins at query time | PostgreSQL, complex relationships | Parquet, pre-join for analytics |
TIMESTAMPTZTIMESTAMP with isAdjustedToUTC=trueEXPLAIN (ANALYZE, BUFFERS) on the querypg_stat_user_tables for bloat (dead tuples)work_mem if seeing disk sortsSELECT * on remote dataef_search (HNSW) or probes (IVFFlat) settingsPROFILE on the query, read operators bottom-upAllNodesScan or NodeByLabelScan (missing index)CartesianProduct (disconnected MATCH patterns)page_cache.hit_ratio (below 98% = need more page cache memory)Activates when the user asks about AI prompts, needs prompt templates, wants to search for prompts, or mentions prompts.chat. Use for discovering, retrieving, and improving prompts.