Database Design and Optimization

When Your Database Becomes the Bottleneck

Every growing digital product eventually hits the same wall. The application code is fine. The server has headroom. But queries that used to return in 50 milliseconds are now taking 3 seconds, and your users can feel it. Your DBA (or the developer who's been handling database work) is spending half their time on emergency optimisations instead of building features.

Database problems are insidious because they hide well at low volume and amplify dramatically at scale. A missing index that adds 10ms to a query at 1,000 rows adds 10 seconds at 1,000,000 rows. A schema designed for the product you launched looks very different from what you need after 18 months of feature growth.

At NICKTUNG, we treat database design as a core architectural concern on every project — not an operational detail to sort out later. And when we audit existing systems, it's usually the database where we find the most opportunity.

What Database Design and Optimization Actually Covers

This service spans the full lifecycle of your data infrastructure:

• Schema design — structuring tables, relationships, and constraints to match your actual query patterns, not just to represent your data model logically
• Indexing strategy — choosing the right indexes for your most critical queries without over-indexing (which slows writes and wastes storage)
• Query optimization — rewriting slow queries, eliminating N+1 patterns, using materialized views and CTEs where appropriate
• Connection pooling — managing database connections efficiently so high-concurrency workloads don't exhaust the connection limit
• Partitioning and archiving — splitting large tables by time or tenant to keep queries fast as data volumes grow
• Caching strategy — implementing Redis or in-memory caching for frequently-read data that doesn't change often
• Replication and backup — read replicas to distribute query load, point-in-time recovery, and backup testing

PostgreSQL: Why It's Our Default and What It Can Do

NICKTUNG builds primarily on PostgreSQL — the world's most advanced open-source relational database. It's not just a safe choice; it's actively the right one for most of the applications we build.

PostgreSQL features that Singapore businesses rarely use but often should:

• Row-level security (RLS) — enforcing data access rules at the database level, critical for multi-tenant SaaS where tenant isolation must be absolute
• JSONB columns — flexible semi-structured data storage that doesn't require a schema migration every time a data shape changes
• Full-text search — built-in search capabilities that can replace a separate search service for many use cases
• PostGIS — geospatial queries for location-based features
• Generated columns and partial indexes — computed fields and targeted indexes that make complex queries faster without duplicating data
• pgvector — vector similarity search for AI-powered features like semantic search and recommendation systems

PDPA Data Retention and the Database Layer

Most PDPA compliance implementations focus on the application layer — consent collection, privacy policies, user-facing data access. But the database is where personal data actually lives, and it's where many compliance gaps exist.

NICKTUNG implements PDPA compliance at the database level: retention policies enforced by scheduled jobs rather than relying on application code, hard delete paths that genuinely remove personal data rather than just flagging it as deleted, and audit tables that log who accessed sensitive personal data and when.

A Database Audit Is Often the Fastest Win Available

For existing products experiencing performance issues, a database audit is often the highest-leverage engagement available. In a typical 2–3 day audit of a production database, we identify:

• The 10–20 slowest queries and their root causes
• Missing indexes that would have the highest impact
• Schema design issues that require migration to resolve
• Query patterns in application code that create unnecessary database load
• Security configuration gaps (unused accounts, excessive permissions)

Audit engagements typically run S$4,000 to S$15,000 and frequently identify improvements that pay for themselves within weeks in reduced infrastructure costs and developer time.

Frequently Asked Questions

Our database is slow but we can't afford downtime for optimization. How do you handle this?

Most database optimisations can be applied without downtime. Index creation in PostgreSQL uses CONCURRENTLY by default, allowing the index to build without blocking reads or writes. Query rewrites happen in application code. Schema migrations that require locks are planned for low-traffic windows and executed as fast migrations. We design the remediation plan with your uptime requirements in mind.

At what size does database optimization stop being necessary and start being overkill?

Good database design matters from day one — not because of current volume, but because retrofitting a schema is expensive. At any size beyond a few thousand rows per major table, indexing strategy matters. Query optimization and caching become critical around the millions-of-rows scale. Connection pooling is relevant from the moment you have more than a handful of concurrent users. The question isn't whether to do it — it's doing it in the right order for your stage.

Should we use a NoSQL database for better scalability?

For most Singapore SME applications, PostgreSQL scales further than you'll ever need. NoSQL databases solve specific problems — extremely high write throughput, unstructured data, multi-region distributed writes — that the vast majority of business applications don't face. Choosing NoSQL for "scalability" without a specific bottleneck is usually adding complexity without benefit. We'll tell you honestly if your use case genuinely warrants NoSQL; for most, it doesn't.

Database problems compound over time. Talk to NICKTUNG about an audit — we'll tell you honestly what's holding your system back and what it would cost to fix.