book

Just Use Postgres!

Name: Just Use Postgres!
Author: Denis Magda
ISBN: 9781633435698

by Denis Magda

November 2025

Beginner to intermediate

400 pages

11h 43m

English

Manning Publications

Read now

Unlock full access

Just use Postgres! All the database you need
copyright
contents
dedication
foreword
preface
acknowledgments
about this book
about the author
about the cover illustration

Part 1 Postgres as a relational database
1 Meeting Postgres
1.1 Why is Postgres so popular?1.2 “Just use Postgres!” explained1.3 Starting Postgres in Docker1.4 Connecting with psql1.5 Generating mock data1.6 Running basic queries
2 Standard RDBMS capabilities
2.1 Creating the database structure2.1.1 Creating databases2.1.2 Creating schemas2.1.3 Creating tables2.2 Querying and manipulating data2.3 Data integrity2.3.1 Constraints2.3.2 Foreign keys2.4 Transactions2.4.1 Implicit transactions2.4.2 Explicit transactions2.4.3 Multiversion concurrence control2.5 Joins2.6 Functions and triggers2.6.1 Functions: A practical example2.6.2 Triggers: A practical example2.7 Views2.8 Roles and access control
3 Modern SQL
3.1 What is modern SQL?3.2 Loading the music service dataset3.3 Common table expressions3.3.1 Selecting data with CTEs3.3.2 Using multiple CTEs in a query3.3.3 Modifying data with CTEs3.4 Recursive queries3.4.1 Querying hierarchical data3.4.2 Using arguments in recursion3.5 Window functions
4 Indexes
4.1 Why are indexes so popular?4.2 Overview of Postgres index types4.3 Loading the multiplayer game dataset4.4 Learning to use the EXPLAIN statement4.5 Single-column indexes4.5.1 Single-column B-tree indexes4.5.2 Single-column hash indexes4.6 Composite indexes4.6.1 Considering an additional single-column index4.6.2 Creating a composite index4.6.3 Caveats of composite indexes4.7 Covering indexes4.8 Partial indexes4.9 Functional and expression indexes
Part 2 Core Postgres beyond relational
5 Postgres and JSON
5.1 Storing JSON data5.2 Loading the pizza order dataset5.3 JSON in Postgres: Striking the balance5.4 Querying JSON data5.4.1 Extracting fields with the -> and ->> operators5.4.2 Using the ? operator to check for the presence of a key5.4.3 Comparing objects with the @> operator5.4.4 Using JSON path expressions5.5 Modifying JSON data5.6 Indexing JSON data5.6.1 Using an expression index with a B-tree5.6.2 Using GIN indexes
6 Postgres for full-text search
6.1 Basics of full-text search in Postgres6.1.1 Tokenization and normalization6.1.2 Full-text search configurations6.2 Preparing data for text search6.2.1 Generating lexemes with the to_tsvector function6.2.2 Storing tsvector lexemes in the database6.3 Performing full-text search6.3.1 Using plainto_tsquery for simple queries6.3.2 Using to_tsquery for advanced filtering6.4 Ranking search results6.5 Highlighting search results6.6 Indexing lexemes6.6.1 Using GIN indexes6.6.2 Using GiST indexes
Part 3 Extensions and the broader ecosystem
7 Postgres extensions
7.1 The roots of Postgres extensibility7.2 Getting started with extensions7.2.1 Exploring available extensions7.2.2 Installing and using extensions7.3 Essential extensions for developers7.4 Postgres-compatible solutions
8 Postgres for generative AI
8.1 How to use Postgres with gen AI8.1.1 Postgres and LLMs8.1.2 Postgres and embedding models8.2 Starting Postgres with pgvector8.3 Generating embeddings8.3.1 Generating embeddings for movies8.3.2 Loading the final dataset into Postgres8.4 Performing vector similarity search8.4.1 Using cosine distance for similarity search8.4.2 Changing the search phrase for better results8.5 Indexing embeddings8.5.1 Using IVFFlat indexes8.5.2 Using the HNSW index8.6 Implementing RAG8.6.1 Preparing the environment for the prototype8.6.2 Interacting with the LLM8.6.3 Retrieving context for LLM8.6.4 Using RAG to answer questions
9 Postgres for time series
9.1 How Postgres works with time-series data9.2 Starting Postgres with TimescaleDB9.3 Loading the time-series data9.4 Exploring TimescaleDB hypertables9.5 Analyzing time-series data9.5.1 Using the time_bucket function9.5.2 Using the time_bucket_gapfill function9.6 Using continuous aggregates9.6.1 Creating and using aggregates9.6.2 Refreshing aggregates9.7 Indexing time-series data9.7.1 Using a B-tree index9.7.2 Using BRIN indexes
10 Postgres for geospatial data
10.1 How Postgres works with geodata10.1.1 Built-in Postgres capabilities10.1.2 Extended capabilities with PostGIS10.2 Starting Postgres with PostGIS10.3 Loading the OpenStreetMap dataset10.3.1 Exploring tables with points10.3.2 Exploring tables with ways10.3.3 Exploring table with polygons10.4 Visualizing geospatial data10.5 Querying geospatial data10.5.1 Working with points10.5.2 Working with polygons10.5.3 Working with line segments10.6 Indexing geospatial data10.6.1 Understanding GiST structure10.6.2 Using a GiST index
11 Postgres as a message queue
11.1 When to use Postgres as a message queue11.2 Building a custom message queue11.3 Using a custom queue11.4 Using LISTEN and NOTIFY11.5 Queue implementation considerations11.5.1 LISTEN and NOTIFY considerations11.5.2 Indexing considerations11.5.3 Partitioning considerations11.5.4 Messages processing failover considerations11.6 Starting Postgres with pgmq11.7 Using pgmq11.7.1 Creating and using a visitors queue11.7.2 Using message visibility timeouts
appendix A Five optimization tips
A.1 Master using EXPLAINA.2 Know when to use indexesA.3 Use connection poolingA.4 Query only what you really needA.4.1 General caseA.4.2 The count(*) caseA.5 Use the computational capabilities of the database
appendix B When not to use Postgres
afterword

Content preview from Just Use Postgres!

5 Postgres and JSON

This chapter covers

Storing JSON data in Postgres
Querying JSON data using built-in operators and functions
Updating specific fields in existing JSON objects
Optimizing JSON data access with GIN and B-tree indexes

Postgres introduced initial support for JSON back in 2012. Since then, JSON-specific capabilities and improvements have been added on a regular basis. Today, Postgres offers mature and comprehensive JSON support so that it can easily function as a document database, storing and processing JSON objects of various complexity.

Let’s explore Postgres’s JSON capabilities as we build an application for a pizza chain with hundreds of locations nationwide. We’ll learn how to store customer order details in JSON ...

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: 9781633435698Publisher Support Other Publisher Website Purchase Link

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Just Use Postgres!

by Denis Magda