Why do we have multiple types of databases?

Not all database requirements are the same. Traditionally, we associated databases with ACID transactional workloads, where Atomicity (A), Consistency (C), Isolation (I) and Durability (D) were of primary importance to support transactional workloads like credit card processing, order management, or human resources data.As data has become increasingly important to drive business decisions, and data volumes have grown tremendously, a new class of databases that are focused on analytical processing emerged, where the priority is on working with very large volumes of data and answering complicated questions. At the same time, data-driven decision making started to incorporate data in many other forms, not just tables. Documents, images, and data from the IoT were added. These new requirements of high volume, high variety, and high velocity data led to the introduction of specialized data warehouse and data lake solutions, that are less concerned...

How are transactional, analytical, and AI application requirements different?

Application requirements vary widely — whether it's processing financial transactions, analyzing large volumes of data, or powering AI models. Transactional, analytical, and AI workloads each place distinct demands on database systems, from low latency and consistency to scalability and the need to scan large amounts of complex data. Read on to get a quick introduction on how each of these application types differs in its requirements.Part II (transactional applications), Part III (analytical applications) and Part IV (AI applications) dive into greater detail.

Historical reasons for specialized solutions

Many database products are specialized for one data type, or one data access and IO method. Traditionally, transactional database systems like Oracle, MySQL, or PostgreSQL used a row-based approach as that is best suited for updating or inserting a small number of records – but is less suitable for working with very large data sets or data ranges in analytical applications.Analytics-specific databases, such as Snowflake, BigQuery, Redshift, or Apache Parquet leverage a columnar approach as it significantly improves read performance for de-normalized data, allows for columnar compression, and works well with large data sets – but it is less suitable for transaction processing. Similarly, specialized AI databases, like Weviate or Pinecone, are designed only for vector processing to achieve Approximate Nearest Neighbor (ANN) search.Specialized solutions are relevant as they initially outperform generalists, especially in very large...

Multiple specialty solutions are an architectural challenge

It is tempting to use a best-of-breed approach and create a hybrid data architecture that uses best of breed for transactions (e.g., Postgres), analytics (e.g., Databricks), and AI (e.g., Pinecone), and establishes data pipelines that keep the specialized databases in sync.

The data pipeline architecture allows to take advantage of the strength of different database solutions, but also has significant disadvantages:

• Storing data in multiple places drives up cost and complicates operations like backup and recovery, and can lead to compliance issues due to data duplication. Data can also easily get out of sync, leading to inconsistency and potential data integrity problems
• Simple data pipelines use a batch copy approach, which leads to delays in data availability (e.g., the data warehouse is 'mostly' yesterday's data...

Summary

This chapter provided an overview of the key database workloads: transactional, analytical and AI, described their requirements and demonstrated how PostgreSQL has evolved over the last 10+ years to be able to cover many of these needs.The next chapter will give a brief overview of the history of Postgres, how its transactional, analytical, and AI features evolved in the last few years, and describe why it has become the #1 choice for modern business applications that leverage transactions, analytics, and AI.From there, we will take a deeper dive into transactional use cases and how to build high-performance transactional systems using the most recent PostgreSQL capabilities.Starting with Chapter 9, we analyze the needs for analytical solutions and show how developers can create analytical solutions in Postgres, including cubes, stars, rollups and text search.Chapter 15 starts the focus on AI, vector processing, management of embeddings, and a step-by-step approach to integrating...