Transactional systems
Where correctness, constraints and transactional guarantees are the point and a relational store is almost always the right answer.
Database consulting and data architecture
The data model outlives the application. Frameworks are replaced every few years; the schema that other systems came to depend on is still there, which is why it deserves more design attention than it usually gets.
Overview
Database decisions are the most durable technical decisions an organisation makes. Applications are rewritten, frameworks are replaced and teams change, and the schema persists because other systems, reports and integrations came to depend on it.
They are also where most performance problems originate. In the overwhelming majority of applications we assess, the constraint is data access — queries returning more than needed, missing indexes, or a model that does not match how the data is actually used.
Our work here covers modelling, performance, the choice between relational, document and analytical stores, and the retention and correctness decisions that determine whether a figure can be defended years later.
Enterprise use cases
Where we see this used to good effect, and what makes each case a fit.
Analytical and reporting estates
Where query patterns scan rather than seek, and a columnar or purpose-built analytical store changes the economics entirely.
Document and semi-structured data
Where the shape genuinely varies per record rather than where schema design was simply deferred.
Time-series and telemetry
High-volume sensor, meter and event data, where retention and aggregation strategy is the dominant design decision.
Migration and modernisation
Moving off an unsupported engine or a model that no longer fits, without stopping the systems that depend on it.
Performance remediation
Estates where response times have degraded as data grew and nobody has profiled where the time actually goes.
Architecture
The first decision is store type, and it should follow access patterns rather than preference. Relational stores suit data with relationships and correctness requirements — which is most business data. Document stores suit genuinely variable shapes. Analytical stores suit scanning.
The second is how many stores an estate should have. Each additional engine carries operational cost — backup, patching, monitoring, expertise — and estates accumulate them one reasonable decision at a time until nobody can operate all of them well.
- Store type chosen from access patterns, not from a preferred technology
- The number of distinct engines constrained deliberately, since each has a cost
- A stated position on which store masters which entity
- Consistency requirements stated per operation rather than assumed globally
Best practices
The practices that matter most are about constraints and change. Constraints belong in the database — a rule enforced only in application code is a rule that a second application, a script or a migration will eventually violate.
Schema change is the second. Migrations should be version-controlled, reviewed and reversible, and backward-compatible during deployment so the application and the schema can be released independently rather than in lockstep.
- Constraints enforced in the database, since application-only rules get violated
- Migrations version-controlled, reviewed and backward-compatible during deployment
- Naming and modelling conventions agreed once, because schemas outlive teams
- Indexes justified by a query, since each one has a write cost
Security
Databases concentrate the data worth stealing, which makes access control and encryption the areas that repay attention. The recurring finding is applications connecting with far more privilege than they need — frequently administrative — because that is what was easiest during development.
Non-production environments are the second consistent exposure. Copies of production data in test environments carry production obligations with considerably weaker controls, and that combination accounts for a meaningful share of real breaches.
- Application accounts with least privilege, not administrative by default
- Non-production data masked or synthesised rather than copied from production
- Encryption at rest and in transit with a key ownership model that is stated
- Access and change auditing where the data is sensitive or regulated
Scalability
The database is the constraint in most systems, and scaling it is harder than scaling anything above it. Read replicas help read-heavy workloads and introduce replication lag that the application must handle deliberately rather than ignore.
Sharding and partitioning are the answers at genuine scale and both add substantial complexity. They are frequently proposed before the simpler options — indexing, query redesign, archiving — have been exhausted, which is usually a mistake.
- Indexing, query design and archiving exhausted before sharding is considered
- Replication lag handled explicitly where reads are served from replicas
- Partitioning designed against real query and retention patterns
- Connection pooling sized to the database's capacity, not the application's demand
Performance
Database performance work is measurement work. The slowest query is rarely the most expensive one in aggregate, and optimising by intuition reliably addresses something other than the actual constraint.
The most common findings are straightforward: a missing index, a query returning far more rows than the application uses, and N+1 patterns from an ORM configured for convenience. All three are cheap to fix once they have been identified rather than guessed at.
- Query profiling by aggregate cost, not by the single slowest execution
- N+1 patterns detected in tests rather than discovered in production
- Index coverage assessed against real queries, with unused indexes removed
- Archiving and retention applied, since table size affects everything above it
Integration
The most consequential integration decision is whether other systems read your database directly. Direct access turns your schema into a public interface, at which point it can no longer be changed without coordinating with consumers you may not know about.
Change data capture is usually the better pattern where other systems need the data, because it publishes an interface you control rather than exposing an internal structure you did not intend to publish.
- Direct cross-system database access avoided, since it publishes your schema
- Change data capture where other systems need the data, publishing a real contract
- Batch and streaming applied where each genuinely fits the latency requirement
- Reference data mastered in one place, because drift causes silent errors
Operations
The operational property that matters most is whether recovery works. Backups verified by job status rather than by restore are a common and consequential gap, because the first genuine test is during an incident.
Beyond that, version lifecycle and capacity are the recurring items. Database engines reach end of support, and an unsupported engine holding critical data is a risk that grows quietly rather than announcing itself.
- Recovery verified by restore and timed against the stated objective
- Engine version kept within support, since patches are otherwise unavailable
- Growth monitored and projected rather than discovered when capacity runs out
- Slow query logging enabled and actually reviewed
Governance
Governance here is about who may change the schema and how, because schemas outlive the teams that create them. Migrations reviewed like code, with a documented rollback position, keep a schema comprehensible over years.
Retention is the second governance item and the one most often deferred. Deciding what is kept and for how long is a business and regulatory question, and where nobody answers it the default is to keep everything indefinitely at growing cost.
- Schema changes reviewed like code, with a rollback position documented
- Retention decided per dataset against business and regulatory need
- Data classification recorded, so protection can be proportionate
- Ownership named per schema, because unowned data degrades by default
Recommended industries
Sectors where this comes up most often, and the constraint that puts it there.
Industry
Banking & Financial Services
Core system modernisation, real-time payment flows, and architecture that can evidence regulatory controls.
IndustryHealthcare
Interoperability between clinical systems, patient data privacy, and legacy platforms that cannot be taken offline.
IndustryTelecommunications
OSS/BSS complexity, network-scale data volumes and availability expectations measured in minutes per year.
IndustryInsurance
Claims and policy administration on ageing platforms, and data trapped in systems never designed to talk to each other.
IndustryEnergy & Utilities
Grid and asset telemetry, regulatory reporting, and operational systems that cannot tolerate an outage window.
IndustryRetail & E-commerce
Traffic that is seasonal rather than steady, omnichannel inventory accuracy, and catalogue scale.
Related services
The engagements this work is usually delivered within.
AI & Data Engineering
Data architecture, platform modernisation, integration and analytics — the foundations that have to exist before AI is a sensible conversation.
Enterprise Architecture
Target-state design, modernisation roadmaps, integration and cloud architecture, and the governance that keeps decisions consistent after the consultants leave.
Software Development
Custom application, web, mobile and API development, and modernisation of systems that are constraining the business.
Cloud Consulting
Migration strategy, landing-zone design, cost modelling and the architecture decisions that determine whether cloud spend buys capability.
Cybersecurity
Security architecture, assessment and compliance advisory, designed in rather than reviewed afterwards.
Managed IT Services
Application support, infrastructure management and incident response under defined service levels.
Frequently asked questions
Relational for most business data — it has relationships and correctness requirements, and constraints belong in the database. Document stores suit data whose shape genuinely varies per record. Choosing a document store because schema design felt slow is deferring the design, not avoiding it.
In the large majority of systems we assess, yes. Missing indexes, queries returning far more rows than the application uses, and N+1 patterns from an ORM configured for convenience account for most of it — and all three are cheap to fix once identified rather than guessed at.
Fewer than most estates end up with. Each engine carries backup, patching, monitoring and expertise costs that are paid continuously, and estates accumulate them one reasonable decision at a time until nobody can operate all of them well.
Only after indexing, query redesign and archiving have been exhausted. Sharding adds substantial and permanent complexity, and it is regularly proposed before the simpler options have been tried — which is how estates acquire complexity they did not need.
They can, and it is worth understanding what that costs. Direct access turns your schema into a public interface which can no longer change without coordinating with consumers you may not know about. Change data capture publishes a contract you control instead.
Only if you have restored from them and timed it against your recovery objective. Backups verified by job status are a common and consequential gap — the first genuine test is during an incident, which is the worst possible time to discover a problem.
The database. A rule enforced only in application code will eventually be violated by a second application, a data fix script or a migration — and the resulting bad data is usually discovered long afterwards, by which point correcting it is its own project.
By making migrations backward-compatible during deployment, so the application and the schema can be released independently rather than in lockstep. That usually means a change is delivered in stages — add, populate, switch, remove — which is more steps and considerably less risk.
Rarely, and it is a consistent source of real breaches. A copy of production data carries production obligations with considerably weaker controls. Masking or synthesis addresses it, and the objection that test data must be realistic is usually solvable without using the actual records.
As long as business need or regulation requires, decided per dataset and implemented rather than documented. Where nobody answers the question the default is to keep everything indefinitely, which grows cost, backup time and the consequence of a breach simultaneously.
Yes, and it is worth treating as urgent rather than important — an unsupported engine receives no security patches. The migration itself is usually straightforward compared with establishing what actually depends on the current schema, which is where the real work sits.
Next step
Discuss a data architecture
A free first consultation covering your data model, where your performance constraint actually is, and whether the number of database engines you run is one you chose.
No cost, no obligation. Pricing is transparent and includes GST.