Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying AI-Ready PostgreSQL 18
  • Table Of Contents Toc
AI-Ready PostgreSQL 18

AI-Ready PostgreSQL 18

By : Vibhor Kumar, Marc Linster
Buy this Book
close
close
AI-Ready PostgreSQL 18

AI-Ready PostgreSQL 18

By: Vibhor Kumar, Marc Linster
Buy this Book

Overview of this book

In today’s data-first world, businesses need applications that blend transactions, analytics, and AI to power real-time insights at scale. Mastering PostgreSQL 18 for AI-Powered Enterprise Apps is your essential guide to building intelligent, high-performance systems with the latest features of PostgreSQL 18. Through hands-on examples and expert guidance, you’ll learn to design architectures that unite OLTP and OLAP, embed AI directly into apps, and optimize for speed, scalability, and reliability. Discover how to apply cutting-edge PostgreSQL tools for real-time decisions, predictive analytics, and automation. Go beyond basics with advanced strategies trusted by industry leaders. Whether you’re building data-rich applications, internal analytics platforms, or AI-driven services, this book equips you with the patterns and insights to deliver enterprise-grade innovation. Ideal for developers, architects, and tech leads driving digital transformation, this book empowers you to lead the future of intelligent applications. Harness the power of PostgreSQL 18—and unlock the full potential of your data.
Table of Contents (28 chapters)
close
close
close
Preface
Icon
Preface
Icon
Free benefits with your book
Lock Free Chapter
1
Part 1: Introducing PostgreSQL and Setting the Stage
Icon
Part 1: Introducing PostgreSQL and Setting the Stage
2
Chapter 1: Transactional, Analytical, and AI Business Systems and Their Database Requirements
Icon
Chapter 1: Transactional, Analytical, and AI Business Systems and Their Database Requirements
Icon
Why do we have multiple types of databases?
Icon
How are transactional, analytical, and AI application requirements different?
Icon
Historical reasons for specialized solutions
Icon
Multiple specialty solutions are an architectural challenge
Icon
Summary
3
Chapter 2: The Evolution of Postgres
Icon
Chapter 2: The Evolution of Postgres
Icon
The evolution of key capabilities for transactional, analytical, and AI use cases
Icon
What makes PostgreSQL the (almost) universal database solution today
Icon
When to complement PostgreSQL
Icon
Summary
4
Chapter 3: A Sample E-Commerce Application
Icon
Chapter 3: A Sample E-Commerce Application
Icon
Technical requirements
Icon
The sample application
Icon
The microservices and their data models
Icon
Users and groups
Icon
Coding conventions
Icon
Installing the e-commerce sample application
Icon
Summary
5
Part 2: Creating Transactional Applications
Icon
Part 2: Creating Transactional Applications
6
Chapter 4: Database Requirements for High-Volume Transactional Applications
Icon
Chapter 4: Database Requirements for High-Volume Transactional Applications
Icon
Technical requirements
Icon
Why microservices need a smarter database architecture
Icon
Using PostgreSQL to create successful microservices data architectures
Icon
PostgreSQL constructs to bring it all together
Icon
Security
Icon
Summary
7
Chapter 5: Transactions, ACID Compliance, and Data Normalization
Icon
Chapter 5: Transactions, ACID Compliance, and Data Normalization
Icon
Technical requirements
Icon
ACID compliance: What is it and why does it matter?
Icon
Working with transactions in PostgreSQL
Icon
A closer look at isolation levels and their importance
Icon
Controlling isolation levels and database locks
Icon
Data modeling for high performance
Icon
Summary
8
Chapter 6: Functions and Stored Procedures
Icon
Chapter 6: Functions and Stored Procedures
Icon
Technical requirements
Icon
Functions or Procedures? What's the difference?
Icon
Procedural languages in Postgres
Icon
Summary
9
Chapter 7: Designing for High Transaction Volumes and Writing Efficient Transactional Code
Icon
Chapter 7: Designing for High Transaction Volumes and Writing Efficient Transactional Code
Icon
Technical requirements
Icon
PostgreSQL data types
Icon
PostgreSQL indexes
Icon
Guidelines for writing effective, efficient, and manageable queries
Icon
Summary
10
Chapter 8: Understanding and Monitoring Transactional Application Performance
Icon
Chapter 8: Understanding and Monitoring Transactional Application Performance
Icon
Technical requirements
Icon
Understanding PostgreSQL's state and its transactional performance
Icon
Other useful Postgres extensions
Icon
Actionable takeaways for developers
Icon
Summary
11
Part 3: Creating Analytical Applications
Icon
Part 3: Creating Analytical Applications
12
Chapter 9: Database Requirements for Analytical Use Cases
Icon
Chapter 9: Database Requirements for Analytical Use Cases
Icon
Business requirements for analytics solutions
Icon
Technical implications and how to meet them in PostgreSQL
Icon
Leveraging PostgreSQL's ecosystem
Icon
Summary
13
Chapter 10: The Analytics Data Model
Icon
Chapter 10: The Analytics Data Model
Icon
Star schemas
Icon
Data vaults
Icon
Relational online analytics processing (ROLAP)
Icon
Comparing ROLAP, star schemas, and data vaults
Icon
Summary
14
Chapter 11: Feeding the PostgreSQL Data Warehouse
Icon
Chapter 11: Feeding the PostgreSQL Data Warehouse
Icon
Technical requirements
Icon
How to use logical replication to feed the data warehouse
Icon
How to use the COPY command to feed the data warehouse
Icon
Summary
15
Chapter 12: Transforming the Data for Analytics
Icon
Chapter 12: Transforming the Data for Analytics
Icon
Technical requirements
Icon
Three ways to create an analytics-focused star schema
Icon
Leveraging partitioning
Icon
Guidance for the developer
Icon
Summary
16
Chapter 13: PostgreSQL's Analytics Functions
chevron up
Icon
Chapter 13: PostgreSQL's Analytics Functions
Icon
Technical requirements
Icon
PostgreSQL's analytics functions
Icon
Groups and aggregates
Icon
Window functions
Icon
CTEs
Icon
Summary
17
Chapter 14: Analyzing Text Documents in PostgreSQL
Icon
Chapter 14: Analyzing Text Documents in PostgreSQL
Icon
Technical requirements
Icon
Text as a key data type in advanced analytics
Icon
Text search in PostgreSQL
Icon
Guidance for developers
Icon
Summary
18
Part 4: Using PostgreSQL as an AI Platform
Icon
Part 4: Using PostgreSQL as an AI Platform
19
Chapter 15: Database Requirements for Artificial Intelligence Use Cases
Icon
Chapter 15: Database Requirements for Artificial Intelligence Use Cases
Icon
Working with natural language and unstructured data
Icon
Why is nearest neighbor search crucial for AI?
Icon
Summary
20
Chapter 16: Vectors and Indexing for AI with pgvector
Icon
Chapter 16: Vectors and Indexing for AI with pgvector
Icon
What is a vector?
Icon
Real AI vectors
Icon
What is pgvector?
Icon
Indexing for AI: Finding the needle in the haystack
Icon
Semantic search with pgvector
Icon
Using pgvector to build a recommendation engine
Icon
Summary
21
Chapter 17: Multi-Modal Reasoning: Combining AI Vectors with Standard SQL
Icon
Chapter 17: Multi-Modal Reasoning: Combining AI Vectors with Standard SQL
Icon
The power of multi-modal reasoning
Icon
Why vectors and SQL belong in the same platform
Icon
Designing high-performance hybrid queries
Icon
Real-world examples and patterns
Icon
Architecture: Why this matters
Icon
Summary
22
Chapter 18: Step-by-Step Approach to Integrating LLMs with PostgreSQL to Create Complete AI Applications
Icon
Chapter 18: Step-by-Step Approach to Integrating LLMs with PostgreSQL to Create Complete AI Applications
Icon
Technical requirements
Icon
Creating an embedding with cURL
Icon
Creating embeddings in PostgreSQL with PL/PythonU
Icon
Summary
23
Chapter 19: Production-Ready AI Embedding Pipeline Patterns
Icon
Chapter 19: Production-Ready AI Embedding Pipeline Patterns
Icon
Technical requirements
Icon
Why do we need a pipeline?
Icon
What are the requirements of an AI pipeline?
Icon
Building an asynchronous, high-performance, and reliable pipeline
Icon
Making the async pipeline near-real-time
Icon
How do I choose the right pipeline model?
Icon
On-demand versus cached query embeddings, and what happens when models change
Icon
A hard truth: Model changes create semantic drift
Icon
Summary
24
Chapter 20: PostgreSQL and MCP: The Blueprint for a Robust AI Assistant
Icon
Chapter 20: PostgreSQL and MCP: The Blueprint for a Robust AI Assistant
Icon
What is the MCP? A simple explanation
Icon
Building an AI chatbot in PostgreSQL
Icon
The dynamic SQL chatbot
Icon
Introducing the MCP: The contract between models and tools
Icon
The MCP and the core idea: One system, two truths
Icon
Operational checklists and the extension path
Icon
Summary
25
Chapter 21: Tying It All Together
Icon
Chapter 21: Tying It All Together
Icon
PostgreSQL capabilities
Icon
Key PostgreSQL 18 innovations
Icon
PostgreSQL and the AI revolution
Icon
PostgreSQL: A data platform for the future
26
Chapter 22: Unlock Access to the Code Bundle and the PDF Version
Icon
Chapter 22: Unlock Access to the Code Bundle and the PDF Version
Icon
Unlock this book's free benefits in three easy steps
27
Index
Icon
Index

13

PostgreSQL's Analytics Functions

Postgres provides a rich set of analytics functions that help users perform advanced data analysis directly within the database. They can quickly extract insights, create reports, and perform advanced calculations, such as running totals, rankings, moving averages, and complex summaries.

Ideally, the analytics functions are applied to an analytics-optimized data model, such as a star schema, that was replicated from one or more transactional systems and denormalized to support efficient querying.

Analytics functions usually do not modify data, except to produce intermediate results, and they follow the same format as simple SELECT queries but offer more powerful grouping, aggregation, subquerying, and windowing capabilities.

In this chapter, we will build on the star schema developed in Chapter 12, Transforming the Data for Analytics and walk you step by step through groups, aggregates, window functions, and common table expressions...

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
AI-Ready PostgreSQL 18
End of Section 16
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon