The JusDB Database Engineering Blog

A top-down playbook for high-performance MongoDB: measure with the profiler and explain(), model for access patterns, index by the ESR rule, keep the working set in the WiredTiger cache, pool connections, and scale reads with secondaries and sharding — with flow diagrams for each layer.

A repeatable, top-down method for tuning PostgreSQL: measure with pg_stat_statements, read plans with EXPLAIN (ANALYZE, BUFFERS), fix queries and indexes before parameters, then tune memory, I/O, WAL, connection pooling, and autovacuum — with a ready-to-adapt postgresql.conf baseline.

Use hypopg to test how a new PostgreSQL index would affect query plans before building it. Includes install, hypothetical index creation, EXPLAIN workflow, and index sizing.

A critical bug in MongoDB 8.0 (SERVER-97368) affects TTL index behavior — causing documents to persist beyond expiration or delete prematurely. Here's how to detect and mitigate it.

Understand when MySQL JSON columns help and hurt performance. Learn functional indexes, JSON_TABLE, and when to migrate JSON to normalized columns.

Use MySQL 8.0 generated columns to index computed values, JSON paths, and date parts. Covers virtual vs stored tradeoffs and functional index patterns.

Add vector similarity search to PostgreSQL with pgvector. Covers HNSW and IVFFlat indexes, cosine similarity queries, OpenAI embedding integration, and hybrid search.

Choose jsonb over json for PostgreSQL: GIN indexing, containment operators, path queries, and update operations with || and jsonb_set. Includes performance comparison.

Implement PostgreSQL full-text search with tsvector columns, GIN indexes, and ts_rank scoring. Covers phrase search, weighted fields, and ts_headline snippets.

Master database index design: composite index column order, covering indexes with INCLUDE, partial indexes for filtered queries, functional indexes, and unused index auditing.

MySQL InnoDB full-text search is underused and often misconfigured. With 8.4 LTS, new defaults and tuning opportunities can cut FTS query time by 60% or more.

pgvector 0.8 delivers significant HNSW indexing improvements and new distance functions. Here is what changed and how to take advantage of it in production.