moai-domain-database

Run EXPLAIN ANALYZE BUFFERS on the query to inspect the actual execution plan, then check that indexes exist for columns used in WHERE, JOIN, and ORDER BY clauses. Add or adjust indexes based on the plan and re-measure on representative data volumes.

Choose Neon for serverless PostgreSQL with auto-scaling and database branching for CI/CD previews. Choose Supabase for pgvector search, Row-Level Security, and real-time subscriptions. Choose Firebase Firestore for mobile-first apps needing offline sync and cross-platform SDKs.

Yes. Schemaless does not mean designless; document structure decisions affect every query and index. Model documents around access patterns, define validation rules, and plan indexes for aggregation pipelines before writing data.

Create a policy on the table using auth.uid(), for example: CREATE POLICY "users_own_data" ON items FOR ALL USING (auth.uid() = user_id). This restricts each user to their own rows for multi-tenant applications.

Schema changes without migrations are unreproducible across environments. Every change needs a reversible migration script (via Alembic, Flyway, or Prisma) so deployments can be applied and rolled back consistently.

Use Redis as a caching layer for hot data, real-time counters, distributed locks, and pub/sub messaging while keeping PostgreSQL or MongoDB as the system of record. Configure explicit TTLs and cache invalidation on writes to avoid stale reads.