Welcome to Opinionated Pattern Picking (OPP), a monthly session we run at Arinco to foster an environment of discussion and learning for our application developers and anyone else interested. Each month the team discusses a topic and attempts to elect a “best default” pattern for developers to use on future projects. View the rest of the blog series here.
What solution are we designing for?
Instead of looking to build an enterprise solution, we take the approach of starting with a straightforward solution: we create a solution that involves an API and some data that can be retrieved either in response to an end-user request or by a daemon service running in the background. This simple approach is still quite detailed, providing us with enough substance to delve into before adding further complexity.
Pattern Picking
SQL vs NoSQL
The opinionated pattern picking session this month explored database approaches that are commonly used in application development. We discussed the strengths and limitations of SQL databases, NoSQL databases, and hybrid approaches. The session aimed to identify when each approach might be most appropriate for different scenarios.
SQL Databases (Azure SQL)
Azure SQL received notable mentions for its mature tooling ecosystem, particularly Entity Framework, which has “been out of the gates for so long” that it provides reliable stability for development teams. Its ACID compliance makes it well-suited for applications where data integrity is critical, such as banking and commerce platforms.
Migration benefits were highlighted for organisations moving from on-premises SQL Server. “Migration from existing on-prem services is easier because you don’t need to reinvent so much,” explained one participant. Backup capabilities and built-in messaging queues were mentioned as additional strengths.
However, cost structure emerged as a significant concern. In Azure SQL, “the amount of storage and performance RAM is tied up in the amount of vCores that you buy,” limiting flexibility in resource allocation. Schema rigidity presents challenges for evolving applications, with participants noting that “schema changes are quite serious” in production environments. Performance at scale requires specialized knowledge that typical development teams might lack. “You have to take care of indexes and so many other things to tune up query performance,” citing how SQL’s limitations with hierarchical data and many-to-many relationships can create additional complexity.
NoSQL Databases (Cosmos DB, MongoDB)
NoSQL databases were valued primarily for their flexible schema and natural domain model alignment. “One of the most valuable things about NoSQL databases is that you can actually model your domain model in your database,” explained a developer with Domain Driven Design experience, contrasting this with SQL where “you have to shoehorn your domain into your schema.”
Cosmos DB’s global distribution capabilities received attention for specialized use cases, with participants noting the ease of adding regions and configuring various consistency levels. Point read performance with denormalized data was identified as superior to SQL alternatives: “If you denormalise your database, you can get a bunch of connected entities in one shot.”
Challenges included partition key selection, which one participant described as critical because poor choices could “cause primitive damage to your instance.” Consistency management requires more application-level code, and development tooling was criticised.
Cost considerations varied, with Cosmos DB described as expensive while MongoDB Atlas was positioned as offering “similar features at approximately one-fifth the cost.”
Hybrid Approaches (PostgreSQL with JSON)
The team explored PostgreSQL with JSON capabilities as an increasingly popular middle ground that combines relational structure with schema flexibility. “It’s a compelling option—you get some benefits of relational but then the flexibility of a JSON data structure,” advocated one participant.
PostgreSQL’s ability to index JSON properties and perform partial updates on JSON data was highlighted as technically superior to SQL Server’s JSON implementation. The cost advantage was repeatedly emphasised: “If you wanted a relational database, PostgreSQL would be a far cheaper cost than Azure SQL.”
This approach was particularly valued for projects with evolving requirements. “Changing requirements—you go one way, the customer goes the other way—you need that flexibility sometimes,” explained a developer. PostgreSQL’s extension ecosystem (including vector, geospatial, and graph capabilities) further expanded its versatility. The main limitation acknowledged was the potential for suboptimal implementation if teams don’t carefully design their hybrid approach, though most participants felt the pragmatic benefits outweighed these concerns.
Opinionated Pattern Picked: Pragmatic Selection with PostgreSQL Preference
After rounded debate, the group reached a conclusion that acknowledges context-specific needs while still providing clear guidance. Rather than a simplistic one-size-fits-all recommendation, the team identified several factors that should drive database selection.
The PostgreSQL Advantage for Everyday Applications
For most typical business applications developed in consulting scenarios, the PostgreSQL hybrid approach emerged as the preferred solution. This preference was rooted in several practical observations:
“Given the applications we build, MongoDB or PostgreSQL would be the cheapest, simpler, easiest way to go… we don’t generally deal with applications that have tens of millions or billions of records,” noted one participant, highlighting how discussions often default to enterprise-scale considerations when the reality of consulting work rarely demands such capacity.
The PostgreSQL hybrid approach with JSON capabilities offers distinct advantages:
- Cost-effectiveness compared to Azure SQL
- Flexibility for evolving requirements
- Ability to index and query JSON properties
- ACID compliance where needed
- Lower operational complexity than pure NoSQL solutions
One participant strongly advocated: “If it’s best of both worlds, in what cases wouldn’t you use it? I’d use it every time.”
Context-Driven Selection Framework
Despite the PostgreSQL preference, the team identified several scenarios where alternative approaches might be more appropriate:
When to Use SQL Databases:
- When data integrity and consistency are non-negotiable (banking, financial systems)
- For reporting-heavy applications with complex queries and aggregations
- When migrating existing SQL Server workloads to the cloud with minimal changes
- When strong referential integrity enforcement is required at the database level
When to Use NoSQL Databases:
- For applications with evolving or unstructured data requirements
- When building domain-driven designs where the model maps poorly to relational tables
- For globally distributed applications requiring multi-region writes
- When point-read performance at scale is critical
- For applications handling large volumes of geospatial data
Practical Considerations Beyond Technology:
The discussion acknowledged that database selection in consulting contexts involves more than technical factors:
- Client ecosystem: “It comes down to the ecosystem of the company… are they Windows or Linux?”
- Team capabilities: “The skill sets of the company we’re doing the work for” can be decisive when choices are otherwise balanced”
- Budget constraints: Are organisation prepared to pay for certain features or to maintain their existing internal knowledge base or expertise?
- Specialised requirements: Specific needs like offline synchronization for mobile applications may drive particular database choices
