/managing-databases

name: managing-databases
description: Guides database architecture for PostgreSQL, DuckDB, Parquet, PGVector, and Neo4j. Use when designing schemas, choosing storage strategies, optimizing queries, configuring vector or graph workloads, or diagnosing performance issues.

Database Management

Decision guidance for PostgreSQL, DuckDB, Parquet, and Neo4j in hybrid storage architectures.

Contents

• When to use which database
• PostgreSQL quick reference
• DuckDB quick reference
• Parquet quick reference
• PGVector quick reference
• Neo4j quick reference
• Cross-database conventions
• Performance debugging checklist

When to use which database

| 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:**

• PostgreSQL: transactional data, relationships, users (metadata)
• DuckDB + Parquet: analytical content, aggregations, time-series

PostgreSQL quick reference

**Use for:** Metadata, relationships, OLTP workloads, anything needing ACID.

**Key decisions:**

• Partition tables >100M rows or with retention requirements
• Index columns in WHERE/JOIN clauses, not everything
• Tune autovacuum for high-churn tables

See [references/postgres-architecture.md](references/postgres-architecture.md) for maintenance patterns. See [references/postgres-querying.md](references/postgres-querying.md) for advanced query techniques.

DuckDB quick reference

**Use for:** Analytics, aggregations, Parquet queries, data exploration.

**Key decisions:**

• Prefer Parquet files over CSV (10-100x faster)
• Let DuckDB auto-parallelize; don't micro-optimize
• For remote data, increase threads beyond CPU count

See [references/duckdb-architecture.md](references/duckdb-architecture.md) for storage and parallelism. See [references/duckdb-querying.md](references/duckdb-querying.md) for DuckDB-specific SQL features.

Parquet quick reference

**Use for:** Storing analytical data, data interchange, columnar compression.

**Key decisions:**

• Target 128MB-1GB file sizes
• Partition by low-to-moderate cardinality columns (date, region)
• Sort by columns used in filters for better pruning

See [references/parquet-architecture.md](references/parquet-architecture.md) for file design. See [references/parquet-querying.md](references/parquet-querying.md) for query optimization.

PGVector quick reference

**Use for:** Similarity search, RAG applications, semantic search, recommendations.

**Key decisions:**

• HNSW for low-latency, high-recall (default choice)
• IVFFlat for memory-constrained or batch-updated data
• Use iterative scan for filtered queries
• Consider hybrid search (vector + keyword) for 8-15% accuracy boost

See [references/pgvector-architecture.md](references/pgvector-architecture.md) for index configuration. See [references/pgvector-querying.md](references/pgvector-querying.md) for hybrid search and filtering.

Neo4j quick reference

**Use for:** Graph traversals, relationship-heavy queries, pattern matching, knowledge graphs.

**Key decisions:**

• Model around your queries, not your source data
• Promote properties to nodes when you need to traverse through shared values
• Use specific relationship types to avoid supernode bottlenecks
• Bound all variable-length paths (`[*1..5]`, never `[*]`)
• Use parameters in Cypher for execution plan caching

See [references/neo4j-architecture.md](references/neo4j-architecture.md) for data modeling, indexing, and maintenance. See [references/neo4j-querying.md](references/neo4j-querying.md) for Cypher optimization and anti-patterns.

Performance debugging checklist

PostgreSQL slow query

1. Run `EXPLAIN (ANALYZE, BUFFERS)` on the query 2. Check for sequential scans on large tables 3. Verify indexes exist on filter/join columns 4. Check `pg_stat_user_tables` for bloat (dead tuples) 5. Review `work_mem` if seeing disk sorts

DuckDB slow query

1. Check if reading CSV instead of Parquet 2. Verify not doing `SELECT *` on remote data 3. Check thread count matches workload 4. Look for unnecessary type conversions

Parquet slow reads

1. Verify predicate pushdown is working (check query plan) 2. Check file sizes (too small = overhead, too large = no parallelism) 3. Confirm data is sorted by filter columns 4. Look for high-cardinality partition keys (too many small files)

PGVector slow search

1. Verify index exists and is being used (EXPLAIN) 2. Check `ef_search` (HNSW) or `probes` (IVFFlat) settings 3. Enable iterative scan for filtered queries 4. Check if IVFFlat recall degraded