Preface

Almost all modern business systems rely on databases. Some use databases for business transactions, while others use them to manage social networks and their relationships, or to collect data from the Internet of Things (IoT). Often, databases are at the heart of systems that analyze profitability or develop forecasts. Recent developments in AI use databases to store embeddings—encodings of highly dimensional spaces that represent semantic networks of nearest neighbors—and to derive new insights or generate knowledge. In practice, this means the database no longer stores only facts and events; it can also store "meaning" alongside them, enabling semantic search, similarity matching, and recommendations directly where the transactional truth lives. Increasingly, databases also serve as the execution point for AI workflows: generating embeddings, managing embedding refresh pipelines when data changes, and grounding natural language experiences in query results that can be audited and governed.

While these AI advancements are recent, the evolution of application demands has been ongoing. In the past, applications were either transactional or analytical. Most modern applications are no longer limited to being transactional, analytical, or AI-based—they incorporate all three uses of data to meet the needs of global businesses that transact, analyze, and adapt continuously. This "adapt" dimension is where AI changes the shape of the application: users expect intent-based discovery ("show me items like this"); systems must respond to changing data without losing semantic correctness; and organizations must apply governance, so AI outputs remain anchored in trusted data rather than being improvised.

This book is for developers and architects and teaches how to leverage PostgreSQL, a leading database that is increasingly the tool of choice for creating applications with transactional, analytical, and AI capabilities. You will acquire the knowledge needed to develop data-based systems that excel at transactional performance, and you will understand how to leverage that data for analytical and generative AI purposes. For each use case, we will share the specific requirements, design principles, latest PostgreSQL capabilities, and ecosystem tools specific to that use case. On the AI side, we go beyond storing vectors and show how to operationalize them: how to generate embeddings for your business entities, how to keep them fresh as data evolves, and how to combine semantic similarity with relational constraints such as price, current status, and attributes. We also show how to translate database output into clear natural language in a grounded way, so the assistant explains what was found based on evidence rows, rather than inventing facts. Finally, we introduce the idea of connecting language models to the database through explicit contracts, so AI can operate safely with governed access, auditable actions, and predictable behavior.

A comprehensive, realistic e-commerce database application is used to illustrate its capabilities and give you the opportunity for hands-on work across all three scenarios. The schema, code, and data can all be downloaded from GitHub. The same dataset is used to demonstrate AI patterns from end to end: from building embeddings for products and related entities, to using vector similarity for search and recommendations, to designing production-shaped pipelines (synchronous and asynchronous) that generate and refresh embeddings reliably. You will also see how natural language questions can be routed into safe retrieval paths—semantic retrieval, governed query execution, and hybrid approaches that blend meaning with strict business rules—while keeping the database as the source of truth.

By the end of this book, you will have gained the knowledge necessary to develop Postgres-based applications that excel at transactions, analytics, and generative AI. You will also have a practical blueprint for building AI features that are not only impressive but dependable: meaning stored as data, retrieval grounded in SQL, and AI behavior constrained by design so it can be deployed with confidence.

Who this book is for

This book is for developers of modern data-driven applications who prefer hands-on examples to understand and learn new concepts. The book teaches how to use PostgreSQL as the database for high-volume mission-critical business applications that combine transactions, analytics, and AI. It is especially relevant for builders who want to add AI "meaning" to relational data—semantic search, similarity matching, and recommendations—without introducing a separate vector database or a fragile integration layer. You will learn how to generate and manage embeddings as part of your application workflow, how to keep them fresh as data changes, and how to combine semantic retrieval with strict relational constraints such as pricing, "current" rows, and variant attributes. We also explore how to design database-facing AI assistants that answer with evidence, operate under guardrails, and integrate with tool-based AI contracts so they remain secure, observable, and predictable in production.

The book builds on PostgreSQL 16 Administration Cookbook by Gianni Ciolli, Boris Mejias, Jimmy Angelakos, Vibhor Kumar, and Simon Riggs (2023) and Learn PostgreSQL by Luca Ferrari and Enrico Pirozzi (2023). The book assumes a working knowledge of SQL. Familiarity with basic application development will be helpful, but AI-specific experience is not required; we introduce the AI concepts using the same practical, database-first approach as the rest of the book. Readers who are comfortable with functions, schema design, and query patterns will find the AI sections approachable because the "unit of progress" is still SQL, just with vectors and controlled tool calls added.

Infrastructure teams trying to deploy and operate high-performance, highly available PostgreSQL clusters are better served by PostgreSQL 16 Administration Cookbook and Mastering PostgreSQL 17 by Hans-Jürgen Schönig (2024). While this book includes production-minded patterns such as batching, background job queues, safety guardrails for generated SQL, and operational checklists, it does not aim to be a full operations manual for high-availability architecture, backup/restore strategy, or fleet-level platform automation.

What this book covers

The book is designed to be read in a modular fashion. Part 1, Introducing PostgreSQL and Setting the Stage sets the context and provides the basic introduction. It is intended for all readers.

Part 2, Creating Transactional Applications focuses on transactional use cases, Part 3, Creating Analytical Applications focuses on analytics, and Part 4, Using PostgreSQL as an AI Platform dives into AI. Parts 2–4 can be read sequentially or as standalone pieces.

Part 1 sets the stage, starting with an overview of requirements, a history of PostgreSQL, and the sample application that we will use throughout the book. The following are the chapters contained in this part:

Chapter 1, Transactional, Analytical, and AI Business Systems and Their Database Requirements, reviews the technical and notational requirements for the different use cases. After a brief discussion of the differences between transactional, analytical, and AI systems, we explain the historical reasons why they have traditionally been handled by different data management systems.

Chapter 2, The Evolution of PostgreSQL, reviews the history of PostgreSQL since its inception in 1986 and highlights the key features that have been added to Postgres to make it the #1 database for developers and allow it to become the data platform for modern applications that integrate transactions, analytics, and AI.

Chapter 3, A Sample E-Commerce Application, describes a retail-focused e-commerce database that we will use throughout the book to illustrate capabilities and architectural recommendations.

Part 2 focuses on transactional applications. The following are the chapters contained in this part:

Chapter 4, Database Requirements for High-Volume Transactional Applications, dives into the specific database requirements for high-volume transactional systems, including modular data services, transactions, consistent performance, security, and auditing.

Chapter 5, Transactions, ACID Compliance, and Data Normalization, dives into ACID compliance, transaction isolation levels, how to control them in PostgreSQL, their impact on performance, and how to create normalized data models to achieve optimal transactional performance.

Chapter 6, Functions and Stored Procedures, explains how to create functions and stored procedures in PostgreSQL, how to control transactions, how to create autonomous transactions, how to write them in PL/PGSQL—PostgreSQL's native procedural language—and how to use Python for the same purpose.

Chapter 7, Designing for High-Transaction Volumes and Writing Efficient Transactional Code, reviews data types, operators, and indexes, and provides guidance on which data types to use and how to write high-performance queries.

Chapter 8, Understanding and Monitoring Transactional Application Performance, digs into PostgreSQL's statistics for tables, indexes, functions, and procedures. The chapter also explores the EXPLAIN tool and shows how tools such as pgAdmin and pgBadger can be used.

Part 3 focuses on analytical use cases. The following are the chapters contained in this part:

Chapter 9, Database Requirements for Analytical Use Cases, explains how the needs for analytical systems differ from those of transactional systems, and introduces different organizational principles for analytical data.

Chapter 10, The Analytics Data Model, explores in detail the way to optimize data models for analytics using data vaults, star schemas, and relational online analytical processing. The chapter ends with guidance for the developer on when to choose which approach.

Chapter 11, Feeding the PostgreSQL Data Warehouse, explains how native PostgreSQL tools can be used to replicate data from a transactional environment to an analytical environment, and how developers can ensure both environments stay in sync.

Chapter 12, Transforming the Data for Analytics, describes how we can use views, materialized views, or triggers to transform normalized data coming from the transactional system into denormalized data optimized for analytical use cases.

Chapter 13, PostgreSQL's Analytical Functions, dives into groups, aggregates, rollups, cubes, CTEs, and window functions to teach the developer the vast richness of PostgreSQL's built-in analytical capabilities.

Chapter 14, Analyzing Text Documents in PostgreSQL, introduces another facet of analytics: how PostgreSQL handles large text documents using SQL tools such as LIKE, and PostgreSQL-specific tools such as regular expressions, full-text search, and advanced trigram search algorithms.

Part 4 explains how to use PostgreSQL as an AI platform. The following are the chapters contained in this part:

Chapter 15, Database Requirements for Artificial Intelligence Use Cases, explains how the needs of AI applications differ from those of transactional and analytics systems, for example, when working with natural language as input and output, or when dealing with semantics and meaning rather than SQL data.

Chapter 16, Vectors and Indexing for AI with pgvector, introduces vectors in general, AI vectors in PostgreSQL, how they can be indexed, how to use vectors for a simple AI-based search engine, and how to extend the search into a recommendation engine.

Chapter 17, Multi-Modal Reasoning: Combining AI Vectors with Standard SQL, is about integrating multi-modal reasoning in PostgreSQL, combining AI- and SQL-based processing on a single platform to minimize complexity and operational overhead.

Chapter 18, Step-by-Step Approach to Integrating LLMs with PostgreSQL to Create Complete AI Applications, explains how to integrate PostgreSQL with an LLM to generate AI embeddings, run simple AI queries, and build a Spotify-style recommendation engine.

Chapter 19, Production-Ready AI Embedding Pipeline Patterns, teaches you how to convert the basic LLM integration architecture from Chapter 18 into a robust production-ready environment that can handle network latency, API failures, and LLM upgrades.

Chapter 20, PostgreSQL and MCP: The Blueprint for a Robust AI Assistant, moves from building a simple chatbot to creating an AI assistant. The chapter reviews the differences between retrieval-augmented generation (RAG) and the Model Context Protocol (MCP). It explains how to create reliable, auditable, and secure AI applications that leverage PostgreSQL.

Chapter 21, Tying it All Together, reiterates why PostgreSQL is the ideal database platform for modern enterprises that need real-time, data-driven decision-making. Combining transactional, analytical, and AI processing under an open source, ACID-compliant umbrella simplifies operations, reduces time to market, and provides a versatile deployment model.

To get the most out of this book

We highly recommend following the examples in the book using the e-commerce application that we provide. We recommend a local Docker container hosted in Docker Desktop, a local PostgreSQL 18 installation, or a dedicated instance on a platform-as-a-service provider.

A Docker container containing all the extensions needed for this book is provided in the GitHub repository.

Download the example code files

This book includes a complete downloadable code bundle containing all the example projects and files used throughout the chapters. We recommend downloading the bundle so you can follow along smoothly and experiment with the examples.

Use the bundle as a practical starting point. Modify it, extend it, and apply what you learn by creating your own variations as you progress through the chapters.

Get the code bundle

If you bought the book directly from Packt:

1. Go to packtpub.com
2. Click your profile picture and select Your Orders
3. Find this book and click Download Code

If you bought this book from Amazon or any other channel partner:

1. Go to packtpub.com/unlock or scan the following QR code:

   

2. Search for this book
3. Sign up or log in to your free Packt account
4. Upload your proof of purchase and download the code bundle locally

Usage note: You're free to use and modify this code for personal learning and non-commercial projects.

Conventions used

We use a few simple conventions throughout the book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, URLs, and user input. For example: "Execute the CREATE TABLE command."

A block of code is set as follows:

CREATE TABLE product.country_of_origin (
    brand_id INTEGER PRIMARY KEY REFERENCES brand (id),
    --- three digit ISO 3166-1 A-3
    alpha3_code VARCHAR(3) NOT NULL,
    name TEXT
);

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

CREATE TABLE product.country_of_origin (
    brand_id INTEGER PRIMARY KEY REFERENCES brand (id),
    --- three digit ISO 3166-1 A-3
    alpha3_code VARCHAR(3) NOT NULL,
    name TEXT
);

Any command-line input or output is written as follows:

 id |  name  |          description          |      distance      
----+--------+-------------------------------+---------------------
  2 | Banana | Very sweet, yellow, soft      |                   0
  3 | Mango  | Very sweet, orange, very soft | 0.14999999677141504
  6 | Peach  | Sweet, pinkish, very soft     | 0.2872280991242216
  5 | Orange | Sweet-tart, orange, firm      | 0.4716990528119823
 10 | Cherry | Sweet, red, firm              | 0.48476795438810844
...
  9 |Potato  | Starchy, brown, firm.         | 1.2708265064660649

Bold: Indicates a new term, an important word, or words that you see on the screen. For instance, words in menus or dialog boxes appear in the text like this. For example: "Each data service should use its own PostgreSQL database as the basic building block. This makes scaling and isolation straightforward."

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book or have any general feedback, please email us at customercare@packt.com and mention the book's title in the subject of your message.

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you reported this to us. Please visit http://www.packt.com/submit-errata, click Submit Errata, and fill in the form.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit http://authors.packt.com/.