Database Design & Optimisation

PostgreSQL is the right choice for the vast majority of products: it handles complex relational data, JSON documents, full-text search, and time-series data all in one engine. MySQL is a reasonable alternative with wide hosting support. MongoDB makes sense when your documents have highly variable schemas and you do not need joins. We assess your access patterns and recommend the engine that fits, not the one that is trendy.

We follow the expand-contract pattern: add new columns as nullable, deploy the application code that writes to both old and new columns, backfill existing rows in batches, then drop the old column in a later release. This allows rolling deployments with zero downtime even for large tables with millions of rows.

An N+1 query occurs when you load a list of N records and then issue one additional query per record to fetch related data — resulting in N+1 round trips to the database. We fix it by using JOIN queries or eager loading (DataLoader, Prisma include) so all data is fetched in a small number of queries regardless of result size. Finding and fixing N+1 patterns is often the single biggest performance win for growing applications.

We apply least-privilege access — the application user has only the permissions it needs, never superuser. We enforce encryption at rest (AWS RDS default KMS) and in transit (TLS-only connections). Credentials are stored in a secrets manager and rotated regularly. We also audit for common vulnerabilities like SQL injection at the schema and ORM layer.

Yes. We start with a performance audit: reviewing slow query logs, running EXPLAIN ANALYSE on the heaviest queries, and profiling index usage. The most common wins are adding missing indexes, rewriting inefficient queries, setting appropriate connection pool sizes, and enabling query caching. We measure before and after so the improvements are quantified.

There are three main patterns: shared schema with a tenant_id column (simple, cost-efficient), separate schemas per tenant (strong isolation, more complex migrations), or separate databases per tenant (maximum isolation, highest cost). We choose based on your security requirements, scale expectations, and migration complexity tolerance. Each approach has tradeoffs we walk through before committing.

Yes. For time-series workloads we use TimescaleDB (PostgreSQL-compatible with automatic partitioning) or ClickHouse for high-ingest analytics. For reporting and BI we often introduce a read replica or a separate OLAP store to avoid contention with the transactional database. We design the pipeline from ingestion to query to dashboard.

Connection pooling is critical at scale because databases have a fixed connection limit. We deploy PgBouncer in transaction mode for PostgreSQL, which allows thousands of application connections to share a small pool of real database connections. We also tune pool size based on query duration and concurrency measurements rather than guessing.

We configure automated daily snapshots retained for 30 days, continuous WAL archiving for point-in-time recovery (PITR), and monthly restore drills to validate the backup is actually usable. For critical data we also set up cross-region snapshot copies. The goal is a tested recovery process, not just a backup that has never been restored.

We follow domain-driven design principles at the schema level: each aggregate has a clear owner table, foreign keys enforce referential integrity, and check constraints encode business rules that should never be violated regardless of the application layer. We use database-level constraints as the last line of defence, not a replacement for application validation.

Yes — and in most cases the answer is SQL. NoSQL databases like MongoDB or DynamoDB solve specific problems well (high write throughput, flexible schemas, global key-value lookups) but add operational complexity and lack the query flexibility of SQL. We analyse your access patterns, consistency requirements, and team familiarity before making a recommendation.

Yes. We configure slow query logging, connection pool metrics, replication lag monitoring, and disk usage alerts. These feed into your existing observability stack (Datadog, Grafana, CloudWatch) with dashboards and alert thresholds set before launch. We also include quarterly database health reviews in our retainer plans.