book

AI-Ready PostgreSQL 18

Name: AI-Ready PostgreSQL 18
ISBN: 9781806028474

by Vibhor Kumar, Marc Linster

April 2026

Intermediate to advanced

580 pages

14h 12m

English

Packt Publishing

Read now

Unlock full access

Preface
Free benefits with your book
Part 1: Introducing PostgreSQL and Setting the Stage
Chapter 1: Transactional, Analytical, and AI Business Systems and Their Database Requirements
Why do we have multiple types of databases?How are transactional, analytical, and AI application requirements different?Transactional systemsAnalytical systemsAI systemsComparing the key requirementsHistorical reasons for specialized solutionsMultiple specialty solutions are an architectural challengeSummary
Chapter 2: The Evolution of Postgres
The evolution of key capabilities for transactional, analytical, and AI use casesTransactional capabilitiesAnalytical capabilitiesAI capabilitiesWhat makes PostgreSQL the (almost) universal database solution todayWhen to complement PostgreSQLSummary
Chapter 3: A Sample E-Commerce Application
Technical requirementsThe sample applicationThe microservices and their data modelsCentralized reference dataE-commerce sitesCentralized analyticsCentralized AI databaseCommon elementsUsers and groupsCoding conventionsSpelling of identifiersNaming conventionsDatabases and schemasProcedures and functionsAPI schemaCommentsInstalling the e-commerce sample applicationSummary
Part 2: Creating Transactional Applications
Chapter 4: Database Requirements for High-Volume Transactional Applications
Technical requirementsWhy microservices need a smarter database architectureUsing PostgreSQL to create successful microservices data architecturesAchieving effective scalabilityDefining modular APIsSupporting efficient cross-service data sharing and aggregationPostgreSQL constructs to bring it all togetherSecurityAuthentication and PostgreSQL rolesAuthorization, REVOKE, GRANT, and the principle of least privilegeAuditingAAA, security, and microservicesSummary
Chapter 5: Transactions, ACID Compliance, and Data Normalization
Technical requirementsACID compliance: What is it and why does it matter?AtomicityConsistencyIsolationDurabilityWhy ACID compliance matters for the businessWorking with transactions in PostgreSQLTransaction blocksRollback: Explicitly undoing the work inside a transactionSavepoints and subtransactionsA closer look at isolation levels and their importanceRead Committed isolation levelRepeatable Read isolation levelSerializable isolation levelCost and performance impact considerationsControlling isolation levels and database locksSELECT FOR UPDATE and SELECT FOR NO KEY UPDATEAdvisory locksData modeling for high performanceData normalizationSlowly Changing Dimensions (SCDs)A quick overview of SCDsA discussion of the pros and cons of the different forms of SCDsUsing PostgreSQL to manage Type 6 SCDsApplying Type 6 to the e-commerce exampleSummary
Chapter 6: Functions and Stored Procedures
Technical requirementsFunctions or Procedures? What's the difference?SQL-only functions and proceduresFunctions and volatilityProcedural languages in PostgresTrusted vs.Untrusted procedural languagesPL/pgSQL: PostgreSQL's native procedural languageControl structuresDiagnostics to understand the most recent queryException handling and Stacked DiagnosticsTriggers: Reacting to data changes and eventsTransactions and subtransactionspg_background: Breaking out of the transaction constraintsplpgsql_check: A powerful linter for PL/pgSQL codePL/pgSQL summaryPL/Python3u: Running Python inside the databaseMapping PostgreSQL data types to Python data typesException handlingTransaction managementPL/Python3u summaryPL/Python3u and PL/pgSQL: Which one to use and when?Summary
Chapter 7: Designing for High Transaction Volumes and Writing Efficient Transactional Code
Technical requirementsPostgreSQL data typesBoolean dataNumeric dataBinary dataCharacter dataDate and timeNoSQLJSON syntaxJSON operatorsUpdating JSON documentsDeleting JSON documentsJSON_TABLEIndexing JSON documentsPerformance considerationNoSQL or tables: How to decide?ArraysArrays or tables: How to decide?RangesCustom typesDomainsComposite typesEnumeration typesUniversally unique IDsSpecialty data types from notable third-party extensionsVector dataTime series dataChoosing the right data types for your use casePostgreSQL indexesOverviewIndexing strategiesExpression indexesPartial indexMulti-column indexingIndex-only scans (covering indexes)Choosing the right indexes for your use caseGuidelines for writing effective, efficient, and manageable queriesTips and tricks to write better queriesAppending, updating, and dealing with MVCC hotspotsSummaryFurther ReadingGet this book's PDF copy, code bundle, and more

Chapter 8: Understanding and Monitoring Transactional Application Performance
Technical requirementsUnderstanding PostgreSQL's state and its transactional performanceApplying a workloadThe cumulative statistics systemUnderstanding the databasesUnderstanding tablesUnderstanding indexesUnderstanding functions and proceduresUnderstanding queriesGenerating query execution plansUsing the EXPLAIN tool to understand query plansQuery statisticsOther tools to understand transaction performanceOther useful Postgres extensionsActionable takeaways for developersSummaryGet this book's PDF copy, code bundle, and more
Part 3: Creating Analytical Applications
Chapter 9: Database Requirements for Analytical Use Cases
Business requirements for analytics solutionsTechnical implications and how to meet them in PostgreSQLOrganizing for read vs.writeFinding and retrieving analytics dataGetting OLTP data ready for OLAPScalability: Horizontal scale-outIntegrating multi-model dataControlling and auditing data accessLeveraging PostgreSQL's ecosystemSummaryGet this book's PDF copy, code bundle, and more
Chapter 10: The Analytics Data Model
Star schemasUsing star schemas to power analyticsData vaultsRelational online analytics processing (ROLAP)Comparing ROLAP, star schemas, and data vaultsSummaryGet this book's PDF copy, code bundle, and more
Chapter 11: Feeding the PostgreSQL Data Warehouse
Technical requirementsHow to use logical replication to feed the data warehouseExtract: Setting up a publicationLoad: Setting up a subscriptionHow to use the COPY command to feed the data warehouseSummary
Chapter 12: Transforming the Data for Analytics
Technical requirementsThree ways to create an analytics-focused star schemaUsing viewsUsing materialized viewsUsing tables and triggersPerformance comparisonLeveraging partitioningGuidance for the developerSummaryGet this book's PDF copy, code bundle, and more
Chapter 13: PostgreSQL's Analytics Functions
Technical requirementsPostgreSQL's analytics functionsGroups and aggregatesGroup by grouping setsGroup by rollupGroup by cubeWindow functionsCTEsRecursive CTEsSummaryGet this book's PDF copy, code bundle, and more
Chapter 14: Analyzing Text Documents in PostgreSQL
Technical requirementsText as a key data type in advanced analyticsText search in PostgreSQLBasic pattern matching with LIKERegular expression matchingFull-text search with tsvector and tsqueryUsing tsvector to convert the source dataUsing tsquery to create queriesPutting it all togetherWeighting the inputRanking the search resultsHeadliningUsing PostgreSQL FTS in combination with regular queriesFTS summaryPostgreSQL trigram-based similarity search and fuzzy matchingUnaccent: A useful helper functionGuidance for developersWhen to use which approachSummaryGet this book's PDF copy, code bundle, and more
Part 4: Using PostgreSQL as an AI Platform
Chapter 15: Database Requirements for Artificial Intelligence Use Cases
Working with natural language and unstructured dataData considerations for natural languageHow it converts to database requirementsAI applications vs.Conventional applications: An e-commerce exampleAI-powered e-commerce application (with LLMs/GenAI features)Identifying nearest neighbors in multi-dimensional search spacesWhy is nearest neighbor search crucial for AI?Database requirements for AI applications: Nearest neighbor search, embeddings, and LLM integrationManagement of embeddings and integration with LLMsWhat are embeddings?Why are embeddings and LLM integration crucial?Additional database requirements for managing embeddings and LLM integrationSummary
Chapter 16: Vectors and Indexing for AI with pgvector
What is a vector?A simple example: Fruits as vectorsReal AI vectorsWhat is pgvector?Why is this so important?Real example: What is stored as a vector?From concept to code: Finding the closest fruitStep 1: Setting up our little fruit storeStep 2: Loading the fingerprintsStep 3: Finding similar fruitIndexing for AI: Finding the needle in the haystackHow vector search worksIndex type 1: IVFFlat (the supermarket index)How to build and use an IVFFlat indexIndex type 2: HNSW (the city map index)How to build and use an HNSW indexChoosing the right index for pgvectorSemantic search with pgvectorStep-by-step approach to semantic searchThe semantic search processRealizing semantic search in PostgreSQLWhat happens internallySyntactic search vs.semantic searchUsing pgvector to build a recommendation engineTypes of recommendationsProduct-to-product recommendationsSpotify-style user preference listsSummaryGet this book's PDF copy, code bundle, and more
Chapter 17: Multi-Modal Reasoning: Combining AI Vectors with Standard SQL
The power of multi-modal reasoningWhat vectors bring to the tableWhat SQL brings to the tableMulti-modal reasoning: Vectors and SQLWhy vectors and SQL belong in the same platformLocality winsBetter consistencyBetter performanceBetter governanceDesigning high-performance hybrid queriesPrinciple 1: Filter early, rank laterPrinciple 2: Let each index do its jobPrinciple 3: Use JOIN to build multi-table intelligenceExample 1Example 2Principle 4: Use hybrid scoring modelsPrinciple 5: Push computation into the databaseReal-world examples and patternsRetail: Semantic search and inventory constraintsBanking: Fraud case similarityManufacturing: Maintenance log analysisTelecom: Customer churn analysisArchitecture: Why this mattersSimpler systems scale betterUnified governancePostgreSQL's relational model remains future-proofSummaryGet this book's PDF copy, code bundle, and more
Chapter 18: Step-by-Step Approach to Integrating LLMs with PostgreSQL to Create Complete AI Applications
Technical requirementsCreating an embedding with cURLMoving from cURL to Python: A simple embedding scriptSetting up the Python environmentCreating the Python scriptRunning the scriptCreating embeddings in PostgreSQL with PL/PythonUMaking api.openai_embed more robust: Adding retries and backoffA dedicated call_openai(...) helperSession-scoped secrets (API key and optional org)A bounded retry loopSmart retries only for transient errorsExponential backoff with jitter (the polite retry)Better error messages with response body snippetWhy robustness matters specifically inside PostgreSQLCreating embeddings for real tables and storing them in embedding tablesEmbedding a single row of the category tableEmbedding a single row of the brand tableEmbedding a single row of the product tableEmbedding a single product variant rowEmbedding multiple rows: Same pattern, now in bulkHow to run bulk embeddingWhy this approach is simple and reliable (and why that's good)Extending the same pattern to other tablesClosing the loop: using generated embeddings to drive recommendationsQuery-to-product recommendations using api.openai_embedA small but important performance habitSummaryGet this book's PDF copy, code bundle, and more
Chapter 19: Production-Ready AI Embedding Pipeline Patterns
Technical requirementsWhy do we need a pipeline?What makes the AI embedding pipeline special?What are the requirements of an AI pipeline?A mental model: Two speeds of truthOperational truth (fast, exact)Semantic truth (meaningful, but not always immediate)Three pipeline patternsSynchronous embedding (write-time)Asynchronous embedding (background scheduled)Near-real-time embedding (background, event-driven)Building an asynchronous, high-performance, and reliable pipelineDesign principle 1: Triggers should enqueue, not embedDesign principle 2: The embedding job queueA minimal asynchronous pipeline for productsThe embedding job queue tableEnqueue helper: One function for all triggersTriggers: Enqueue on insert/updateDesign principle 3: The worker, processing pending jobs in batchesStep-by-step walkthrough of the workerDesign principle 4: Smart updates for embeddingsControlled retries (pipeline-level resilience)Change detection (freshness without waste)Indirect changes: When category/brand updates should refresh productsPutting it all togetherMaking the async pipeline near-real-timeOption A: LISTEN/NOTIFY (fast wakeups)Option B: Scheduler-driven micro-batching (predictable and simple)Running the pipelineHow do I choose the right pipeline model?On-demand versus cached query embeddings, and what happens when models changeTwo types of embeddings in one systemThe on-demand baseline: simplest and correctA production-friendly query cache (minimal, but disciplined)Cache eviction: Keep it small and meaningfulClosing the loop: Search and recommendations with cached query embeddingsA hard truth: Model changes create semantic driftA versioned embedding strategy (production-safe)The hard cutover: Re-embed everythingParallel operations: Use two embedding sets during migrationWhat triggers a full rebuild?Closing the loop: Search and recommendations with cached query embeddingsSummaryGet this book's PDF copy, code bundle, and more
Chapter 20: PostgreSQL and MCP: The Blueprint for a Robust AI Assistant
What is the MCP? A simple explanationBuilding an AI chatbot in PostgreSQLThe retriever: api.sf_similar_itemsThe narrator: api.sf_answer_with_openaiThe orchestrator: api.sf_chatThe first working conversationMaking it more robust: Using embeddings.sf_get_query_embeddingThe dynamic SQL chatbotWhat does "dynamic" mean in our schemaA safe dynamic SQL executor (SELECT-only + LIMIT + Allowlist)What "safe" means in practiceA minimal "safe" query runner: api.sf_safe_selectTurning a question into SQL (the model proposes, the database enforces)How it works, step by stepThe dynamic chatbot: api.sf_dynamic_chatWhy don't we add embeddings.sf_get_query_embedding at this pointTeaching the assistant when to use semantic search vs.dynamic SQLThe two-lane mental modelA simple router: api.sf_route_chatHandling the hybrid question (the real world)A hybrid chat: Semantic candidates and SQL filtersWhy this still isn't "production-safe enough" (and why MCPh exists)Introducing the MCP: The contract between models and toolsWhat is the MCP?Why the MCP matters for database assistantsThe MCP and the core idea: One system, two truthsHow the MCP maps to what we've already builtThe practical payoff: Fewer prompts, more systemsWhere we go nextMCP-ready tool design for PostgreSQL: inputs, outputs, limits, and auditabilityWhat "MCP-ready" means for a database toolMaking api.sf_similar_items MCP-readyImprovement A: Separate "embedding computation" from "search execution"Improvement B: Make the row limit and safety explicitPlugging in embeddings.sf_get_query_embeddingMaking the narration tool MCP-readyThe final MCP-ready surface: the semantic tool and the dynamic toolThe MCP-ready semantic chat toolThe MCP-ready dynamic chat toolAdding an audit logWhy this is the correct setup for the MCPImplementing MCP tool routing for our PostgreSQL AI assistantThe MCP tool set we will route betweenA simple routing policyAdding production controls for MCP tools: Roles, timeouts, row caps, and cost guardsMCP tool contracts and safe views: Turning "function calls" into a reliable interfaceOperational checklists and the extension pathOperational checklist: Running the AI chatbot successfullyExtension path: What you can build beyond thisSummaryGet this book's PDF copy, code bundle, and more
Chapter 21: Tying It All Together
PostgreSQL capabilitiesArchitectural concepts: Polymorphic, versatile, hybridData management conceptsKey PostgreSQL 18 innovationsPostgreSQL and the AI revolutionPostgreSQL: A data platform for the future
Chapter 22: Unlock Access to the Code Bundle and the PDF Version
Unlock this book's free benefits in three easy steps
Index

Content preview from AI-Ready PostgreSQL 18

22 Unlock Access to the Code Bundle and the PDF Version

Your copy of this book includes the following exclusive benefits:

Follow this guide to unlock them. The process takes only a few minutes and only needs to be completed once.

Unlock this book's free benefits in three easy steps

Step 1

Have your purchase invoice ready for step 3. If you have a physical copy, scan it using your phone and save it as a PDF, JPG, or PNG.

For more help on finding your invoice, visit https://www.packtpub.com/unlock-benefits/help.

Note: If you bought this book directly from the Packt website, no invoice is required. After step 2, you can access your exclusive content ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781806028474

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

AI-Ready PostgreSQL 18

by Vibhor Kumar, Marc Linster

22

Unlock Access to the Code Bundle and the PDF Version

Unlock this book's free benefits in three easy steps

Step 1

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.