moai-domain-database

Run EXPLAIN ANALYZE BUFFERS on the slow query to identify full table scans, then create partial or composite indexes matching the WHERE, JOIN, and ORDER BY columns. For repeated analytics, use materialized views refreshed concurrently to cut query time from seconds to milliseconds.

Start with a $match stage backed by an index, then use $group for metrics, $lookup for joins, and $project to shape output. Create compound indexes on filter and sort fields, and enable allowDiskUse for large result sets.

Cache-aside checks Redis first and populates it only on a miss, while write-through updates the database and cache together in one operation. Both patterns require explicit cache invalidation on updates to avoid serving stale data.

Yes, python-oracledb is the official successor to cx_Oracle and supports async connection pools, statement caching, and batch operations with executemany. It connects to Oracle 19c and 21c and works with SQLAlchemy's Oracle dialect.

Connection exhaustion happens when pools are undersized or connections leak without being returned. Configure explicit pool_size, max_overflow, pool_pre_ping, and pool_recycle settings, and monitor active connections with pg_stat_activity.

Use polyglot persistence when different data has different access patterns, such as structured records in PostgreSQL, flexible profiles in MongoDB, and real-time counters in Redis. A DataRouter pattern routes each data type to its appropriate store and synchronizes across them.