Managing Databases

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 for maintenance patterns. See 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 for storage and parallelism. See 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 for file design. See 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 for index configuration. See references/pgvector-querying.md for hybrid search and filtering.

Cross-database conventions

Naming

Normalization decisions

Timestamps

• Store as UTC always
• PostgreSQL: TIMESTAMPTZ
• Parquet: TIMESTAMP with isAdjustedToUTC=true
• DuckDB: reads both correctly

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 (rebuild index if heavily updated)
5. Consider partial indexes for common filters