The Evolution of Database Architecture and the Future of Data Management¶

The database, data storage and data management landscape has seen significant changes over the past two decades, challenging traditional assumptions about transactional and analytical workloads.

This post provides an overview of this evolution from monolithic databases to NoSQL and HTAP aspirations, to the emerging pattern of composable data lakehouses and specialised query engines. The article concludes by providing generalised recommendations for adapting these patterns in FFMVIC systems.

In the Beginning There Was Version 2¶

Note

This post represents the professional opinions of the author and provides general technology recommendations. The content of this post have not been endorsed by the BFS Architecture Forum or DEECA's Architectural Review Board and do not necessarily represent the views of broader team or the Department as a whole. The post is meant as an educational content to discuss emerging technology patterns and does not provide directions to any ongoing teams or projects.

Larry Ellison famously believed that customers won't buy a version 1 software, so the first public release of Oracle was Oracle V2, which was soon followed by IBM's DB2 (IBM Data Base 2).

In 1980s and early 1990s, these Relational Database Management Systems (RDBMS) served both operational and analytical workloads: they performed Online Transaction Processing (OLTP) operations during business hours and switched to performing Online Analytical Processing (OLAP) reports during off peak hours.

Tip

Online here differs from our modern definition of the term; in 1980s the term Online meant something close to what we now call Synchronous i.e., the operator would submit a query and get their results right away, as opposed to Offline systems which were batch oriented, i.e, the operator would submit their query and the results would be printed a few hours/days/weeks later.

This approach was sufficient when datasets were small and compute resources were expensive.

A brief recap:

OLTP systems optimise for high-frequency transactional operations and are characterised by microsecond response times, point lookups, and small data modifications. These workloads benefit from row-oriented storage where individual records are stored contiguously, enabling rapid retrieval and modification of rows/items. They focus on write performance, ACID compliance and maintaining referential integrity across numerous concurrent transactions.

OLAP systems conversely optimise for complex analytical queries involving full-table scans and large-scale aggregations across historical datasets. These workloads benefit from columnar storage where values for each column are stored together, allowing queries to read only specific columns required for analysis. This approach dramatically reduces I/O requirements and improves compression ratios compared with OLTP systems.

The fundamental conflict in resource utilisation particularly regarding CPU, memory, and I/O patterns between these opposing workloads resulted in architectural separation of the two. Database pioneer Michael Stonebraker articulated this in his influential paper "One Size Fits All: An Idea Whose Time Has Come and Gone," arguing for specialised database engines tailored to specific workload requirements. This led to the widespread adoption of dedicated OLTP databases for operational tasks and separate data warehousing platforms such as Teradata and Microsoft SQL Server Analysis Services for analytical workloads.

The NoSQL Movement¶

The mid 2000s brought the NoSQL movement, driven by internet scale companies requiring scalability beyond what traditional relational database systems were capable of. MongoDB, Cassandra and DynamoDB demonstrated that abandoning strict ACID guarantees could enable massive scale for specific use cases.

Though NoSQL databases have been shown to suffer from many of their own challenges (in 2025 MongoDB is still not ACID-compliant, despite what the vendor claims), the NoSQL movement introduced several concepts to modern data architectures:

Distributed storage across commodity hardware provided cost effective scalability
Eventual consistency models demonstrated acceptable trade offs between consistency and availability
Schema flexibility enabled rapid application development without predefined data structures

The ecosystem which later formed around Apache Hadoop extended these concepts to analytical workloads. MapReduce and later Apache Spark demonstrated that analytical processing could be distributed across commodity hardware, significantly challenging the need for expensive proprietary data warehouses. These systems introduced technologies such as distributed file systems (HDFS) and data lakes, which allow cost effective storage of vast quantities of structured and unstructured data.

The Rise and Fall of HTAP¶

Around 2014, Gartner coined the term Hybrid Transactional/Analytical Processing (HTAP) to describe a group of emerging systems which promised to be capable of performing both OLTP and OLAP operations efficiently on the same data store. These systems promised to eliminate ETL pipelines and provide real time analytics on transactional data.

HTAP systems brought innovative concepts to the table. SingleStore (originally called MemSQL) combined in-memory row stores with disk-based column stores, whilst TiDB paired its TiKV row store with TiFlash, a columnar engine based on ClickHouse. These systems showed that careful architectural design could address some fundamental conflicts between transactional and analytical workloads.

However, monolithic HTAP systems failed to achieve widespread market adoption. Several factors contributed to this:

Operational risk: Associated with replacing established OLTP systems.
Scalability requirements: Most organisations do not face Facebook or Google's scaling challenges. Coupled with advances in CPU processing and I/O interfaces, most organisations realised that they can service their transactional workloads with single-machine databases (OpenAI famously uses a single-node PostgreSQL DB).
Cloud costs: Shared object storage containers are much more economical in cloud environments than designs requiring fast local SSDs, as needed by HTAP systems.
Organisational structures: In most organisations, OLTP and OLAP responsibilities remain with different teams.
Technical complexity Optimising a single HTAP system for fundamentally different access patterns resulted in performance compromises.

The Write-Ahead Log (WAL)¶

While companies were trying and mostly failing to make the universal HTAP dream a reality, the simple Write-Ahead Log (WAL) evolved to become a universal interface between operational and analytical systems. Originally designed for database recovery by recording all modifications before they reach the filesystem, the WAL provides a chronological stream of every database change, making it ideal for Change Data Capture (CDC) applications.

Architectures started to emerge which treat the OLTP database as the upstream, and stream events from the relational DB's WAL to downstream analytical systems. CDC tools such as Debezium capture database changes from PostgreSQL, MySQL, and SQL Server transaction logs and publish them to streaming platforms such as Azure Event Hubs and Apache Kafka. This architecture enables near real time data movement and analysis without impacting operational system performance.

Stream processing technologies such as Apache Flink, Kafka Streams and Azure Stream Analytics can further transform these change streams, applying business logic and aggregations before loading data into analytical systems.

This approach separates operational efficiency from analytical processing requirements while maintaining near real time data freshness for analytics.

The Foundation of Data Lakehouses¶

The industry is also converging on adoption of open table formats as the standard storage layer for analytical systems. The three main open table formats are: Apache Iceberg, Databricks's Delta Lake and Apache Hudi. Each originally brought novel technical innovations to the table but all three have recently been converging on providing the following set of capabilities:

ACID Transactions: Ensuring data integrity for concurrent operations
Schema Evolution: Allowing table schemas to change without disruptive rewrites
Time Travel: Enabling queries against historical data versions
Partition Evolution: Facilitating partitioning scheme changes without data migration
Hidden Partitioning: Abstracting physical layout details from users

This combination of object storage and open table formats has become known as the data lakehouse architecture, which combines the scalability and cost effectiveness of data lakes with the data management features of traditional data warehouses.

Apache Iceberg has emerged as the industry standard open table format with native support from AWS S3 Tables, Google BigQuery, Microsoft Fabric, and Snowflake. Its hierarchical metadata structure with manifest files provides advanced capabilities including hidden partitioning and optimistic concurrency control. It has recently added native support for geospatial data types (geometry and geography) and has an active developmend and roadmap with upcoming features such as row lineage tracking.

Apache Arrow has become the universal columnar format that enables high performance analytics across the entire lakehouse ecosystem. All three major table formats, Iceberg, Delta Lake, and Hudi, use Arrow as their standard in-memory representation. Query engines are also embracing Arrow extensively: Apache Spark's PySpark vectorised UDFs show 1.6x performance improvement over traditional UDFs, DuckDB leverages Arrow for microsecond query performance, and Google BigQuery's Storage Read API delivers 10x faster data access through Arrow format.

Hive Metastore (HMS) provides schema abstraction and data discovery capabilities to lakehouses by storing metadata including table schemas, partition information, and storage format specifications. HMS is integral to the lakehouse ecosystem, serving as a bridge between traditional Hadoop architectures and contemporary table formats. Alternative catalogue systems have emerged but complement rather than replace HMS. AWS Glue Data Catalog serves as a drop-in replacement with managed infrastructure, Databricks Unity Catalog supports HMS federation, and Apache Polaris provides REST-based catalogue capabilities.

The recent emergence of technologies such as Apache XTable (formerly OneTable) which aim to provide bi-directional compatibility across all three formats is testament to the maturity of the lakehouse ecosystem.

The Great Decoupling¶

The data processing landscape is undergoing what industry observers call The Great Decoupling: a fundamental shift from monolithic platforms toward specialised, composable architectures. This transformation represents the largest architectural evolution since the introduction of Hadoop, driven by the recognition that most organisational problems are not "Big Data" problems.

The Complexities of Distributed Computing Engines¶

Apache Spark, once considered the dominant force for analytical workloads, has seen its market position significantly eroded. Performance benchmarks consistently show Spark trailing specialised engines, particularly for workloads under 1TB where it suffers significant performance penalties compared to single-node alternatives. This reality has prompted a community consensus that distributed computing complexity is unjustifiable for many use cases.

The Rise of Specialised Query Engines¶

In place of general purpose distributed systems such as Spark, specialised engines have emerged to dominate specific workload categories:

DuckDB has established itself as the leader for small-to-medium analytical workloads. DuckDB provides ~10x better price-performance than cloud warehouses for many workloads.

Polars has carved out a niche for data engineering pipelines with its lazy evaluation engine and superior memory efficiency for DataFrame operations. Built with Rust, it demonstrates the performance advantages of modern systems languages over JVM-based solutions.

StarRocks and ClickHouse represent a new generation of real time analytics engines. StarRocks excels at handling thousands of concurrent users with superior join performance, while ClickHouse dominates time series analytics and log analysis with excellent compression ratios. ClickHouse's architectural purity, optimising aggressively for analytical patterns without transactional compromises, has resulted in orders of magnitude performance advantages over general purpose systems.

Trino (formerly PrestoSQL) has established clear leadership in federated query processing and production readiness (e.g., Netflix operates 15+ Trino clusters processing 10 million queries monthly over 1 million Apache Iceberg tables).

Key Players and Technologies¶

These patterns and technological forces have resulted in the following companies and technologies becoming the key players of the data management landscape in 2025:

Databricks emerged from the Apache Spark project at UC Berkeley and has established itself as the leading lakehouse platform. The company pioneered the concept of unified analytics platforms combining data engineering, data science, and business intelligence on data lake storage. Databricks now faces challenges as organisations recognise that Spark's complexity and cost are unnecessary for many workloads.

Snowflake pioneered cloud data warehousing by separating storage and compute, enabling independent scaling of each layer. The platform's multi cluster shared data architecture allows multiple compute clusters to operate on the same data simultaneously without contention.

PostgreSQL has emerged as the RDBMS of choice for new transactional systems, driven by several technological advantages. Its extensible architecture has enabled the development of sophisticated features such as PostGIS for geospatial data, TimescaleDB for time-series workloads and pgvector for AI/ML embeddings. This extensibility, combined with comprehensive SQL standard compliance, robust JSON support and advanced indexing capabilities has made PostgreSQL the default OLTP for new transactional systems.

Neon represents a new generation of database companies, offering serverless PostgreSQL with storage and compute separation. Neon replaced PostgreSQL's storage layer with a cloud native storage system, enabling features such as git-like database branching and point-in-time recovery.

Crunchy Data established itself as a prominent PostgreSQL support and managed service provider. The company also developed Crunchy Data Warehouse which integrates analytical capabilities directly in PostgreSQL.

PostgreSQL + Analytics¶

Neon and Crunchy Data pursued different strategies to add analytical capabilities to PostgreSQL:

Neon's approach focuses on replacing PostgreSQL's storage layer. By implementing a log-structured storage system and separating storage from compute, Neon enables features such as branching (creating instant database copies for development or analytics) and automatic scaling. This architecture allows analytical workloads to run on separate compute instances without impacting transactional performance.

Crunchy Data's approach involves integrating extensions on top of PostgreSQL. The Crunchy Data Warehouse combines PostgreSQL for operational data with DuckDB for analytical processing and Apache Iceberg for historical data management. This combination provides a unified SQL interface while using specialised engines for different workloads: PostgreSQL handles transactions, DuckDB processes analytical queries, and Iceberg manages the data lakehouse layer.

The HTAP Challenge for Analytics Platforms¶

Both Databricks and Snowflake faced significant technological challenges in their attempts to expand into transactional workloads:

Databricks built its platform around Apache Spark, optimised for batch processing and analytical workloads. The fundamental architecture assumes data is written in large batches and read by distributed scans. Attempting to add OLTP capabilities revealed critical limitations: Spark's job scheduling overhead makes single row operations prohibitively expensive, Delta Lake's file-based storage cannot efficiently handle high-frequency updates, and the lack of traditional database features such as indexes, constraints, and triggers limits transactional use cases.

Snowflake encountered similar architectural impediments. The platform's micro partition architecture struggles with OLTP patterns. Each update potentially requires rewriting entire micro partitions, the platform lacks the low latency response times required for transactional applications. Furthermore, scalable compute designed for analytical workloads is often too costly for sustained transactional processing.

In May 2025, Databricks purchased Neon for approximately USD $1 billion, followed a few weeks later by Snowflake acquiring Crunchy Data for USD $250 million. These acquisitions are an acknowledgment of both Databricks and Snowflake's challenges in building a unified HTAP around their analytics framework, and a recognition that true HTAP requires composition of multiple technologies.

By acquiring proven PostgreSQL teams, Databricks and Snowflake will finally be able to combine compelling transactional capabilities with their analytical platforms. Both companies will likely integrate their new Postgres-based engines using CDC patterns to stream DB changes to their analytics platform.

Where ClickHouse Fits in¶

Also in May 2025, ClickHouse raised USD $350 million, valuing the company at over USD $6 billion. This shows the unique position which ClickHouse occupies in this landscape by explicitly rejecting HTAP ambitions in favour of pure OLAP excellence.

Architectural purity: ClickHouse has optimised aggressively for analytical patterns without transactional compromises. ClickHouse implements columnar storage with sophisticated compression, vectorised query execution using SIMD instructions supported by modern CPUs, and skip indexes that eliminate unnecessary data scanning. This approach has resulted in orders of magnitude performance advantages over Snowflake and other competing systems.

Ecosystem integration: ClickHouse recognised that modern data management requires specialised engines, and as such, positioned itself as a specialised component within the larger ecosystem. Rather than trying to compete with general purpose platforms, ClickHouse occupies the position of an acceleration layer, ingesting from streaming platforms such as RabbitMQ and Kafka, querying data in lakehouse formats (e.g., Iceberg), and serving as a high performance cache for frequently accessed analytical data.

This focus on analytical excellence and embracing compositional architectures has coincided with the emerging industry recognition that specialised engines, properly integrated, outperform monolithic HTAP attempts.

Why Not Azure?¶

Microsoft Azure's approach to modern data architecture reflects a market-following strategy. Azure Datalake Storage (gen2) is Microsoft's implementation of Hadoop's distributed filesystem, HDFS. Azure Synapse Analytics is Microsoft's attempt to provide a managed Apache Spark platform and compete with Databricks. Azure Stream Analytics is Microsoft's simplified implementation of Apache Flink, while Fabric represents Azure's attempt at a unified Data Lakehouse (though as usual with Microsoft, Fabric is more of a "brand" than a singular technology).

These offerings primarily show Microsoft's adoption of proven concepts and technologies from market leaders such as Databricks and Snowflake. These platforms provide good integration with other Azure services and enable a path to modern data management for organisations committed to the pure Microsoft ecosystem; but often lag behind market leaders in performance and functionality.

Recommendations for FFMVIC Systems¶

Based on these observations and emerging patterns, the following recommendations can be made for future adoption by FFMVIC systems:

Trusted Relational Databases for Operational Systems: Continue to use established relational database management systems, specifically Azure SQL and Azure PostgreSQL, for storage of structured data for operational systems.
Data Lakehouses for Unstructured and Binary Data: Gradually adopt the lakehouse architecture, using Azure Datalake Storage (ADLS) gen2 for storage of unstructured and binary data (e.g., documents, raster images, etc).
Apache Icerberg as the Table Format: Adopt Apache Icerberg on top of ADLS gen2 as the open table data format for storing spatial, unstructured and binary data.
Modernise Pipelines: Simple ETLs should be implemented using on Azure Functions:
- Prefer Strongly-typed languages: When the operation is simple enough which doesn't require specialised libraries, or when NuGet or npm packages exist which are sufficient for the task, use C# or TypeScript.
- Python where it makes sense: When the ETL task can benefit from one of Python's specialised data processing libraries such as pandas, numpy, scipy, xarray, use Python in Azure Functions.
- Polars for DataFrames: For more complex tasks which are best implemented with DataFrames, consider Polars.
- Databricks for truly Big Data: Reserve Apache Spark/Databricks for truly Big Data operations which can benefit from distributed computing.
Establish CDC Patterns: Implement CDC mechanisms to replicate data from operational platforms such as eMap and Tarnook into the data lakehouse without impacting operational performance.
Adopt Workload-Specific Query Engines:
- Small-scale analytics (under 10GB): Consider DuckDB for interactive analytics
- Real-time analytics: Consider StarRocks for high concurrency and complex joins
- Time-series and log analytics: Consider ClickHouse for superior compression and query performance
- Federated queries: Consider Trino for querying across multiple data sources
- Large-scale ML (truly Big Data): Reserve Apache Spark/Databricks for workloads exceeding single node capabilities
Consider Adopting Apache Arrow and the Hive Metastore: Consider Arrow as the standard interchange format and HMS as the metadata store to enable usage of multiple query engines without data movement in the lakehouse.