Configured work_mem=64MB, shared_buffers=2GB, and effective_cache_size=4GB for improved query planning and caching.
~20–30% improvement in query planning and caching efficiency.
Autovacuum Tuning
Set autovacuum_naptime=1min, vacuum_scale_factor=0.02, and analyze_scale_factor=0.01 to keep statistics updated.
Reduced query execution times by ~10–20% due to better statistics and lower table bloat.
pg_stat_statements
Enabled query performance monitoring to identify and tune slow queries.
Indirect gains through improved query optimization.
JSONB with GIN Index
Created tables with JSONB data and a GIN index for flexible storage and fast queries on semi-structured data.
~20–30% faster query response on JSONB data.
HNSW Indexing for Vector Search
Configured pgvector with an HNSW index on the embedding column (m=16, ef_construction=200) for efficient nearest neighbor searches.
On large datasets, nearest neighbor queries can be 5–10x faster (~70–80% improvement compared to sequential scans).
Web Server
Optimization
Description
Estimated Benefit
Nginx Proxy Optimization
Configured Nginx with proxy_pass http://localhost:8055 to route and load balance requests to Directus.
Efficient request routing; benefits scale with higher loads.
Redis Caching
Integrated Redis for caching frequently accessed data to reduce database load and improve API response times.
~30–40% faster API responses and reduced DB load.
Directus
Optimization
Description
Estimated Benefit
Proper Working Directory & .env Loading
Ensured Directus starts from the correct directory (/root/directus) so environment variables like PORT=8055 load correctly.
Improved stability and proper port binding.
Running in Fork Mode
Switched from cluster mode to fork mode (single instance) to reduce unnecessary idle CPU consumption.
Idle CPU usage dropped dramatically (from 50–90% to ~1–5%).
AI/LLM Specific
Optimization
Description
Estimated Benefit
Vector Search and JSONB Storage
Using pgvector (with HNSW indexing) for storing and efficiently querying vector embeddings, along with JSONB for large datasets.
Enables scalable RAG and inference workflows; with large datasets, vector search can improve performance by ~70–80%.
Future Scalability with ETL & Partitioning
(Optional) Setup of ETL pipelines and table partitioning (e.g., with pg_partman) for massive datasets.
Potential 2–3x improvements as data volume grows.
Overall Estimated Gains: Under light load, improved caching and tuned database settings can yield 30–50% faster query response times and more efficient resource utilization. With heavy load and large data volumes, benefits from vector search and improved indexing may provide 5–10x performance improvements for specific operations—all while using the same hardware resources.
