Mastering PostgreSQL Accelerate Your Weekend Projects and Seamlessly Scale to Millions (Denis Magda, Jimmy Angelakos) (z-library.sk, 1lib.sk, z-lib.sk)

Page 1

databases a learning path curated by MASTERING POSTGRESQL Accelerate Your Weekend Projects and Seamlessly Scale to Millions

Page 2

Mastering PostgreSQL Accelerate Your Weekend Projects and Seamlessly Scale to Millions A learning path curated by Supabase from a selection of Manning books Copyright 2026 Manning Publications To order or learn more about these books go to www.manning.com

Page 3

©2026 by Manning Publications Co. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps. Manning Publications Co. 20 Baldwin Road PO Box 761 Shelter Island, NY 11964

Page 4

This eBook contains selected chapters from Manning titles Just Use Postgres! and PostgreSQL Mistakes and How to Avoid Them. Save 45% on these books — available as eBook and pBook. Enter supabase45 in the Promotional Code box when you checkout. Only at manning.com Just Use Postgres! All the database you need by Denis Magda Foreword by Josh Long Afterword by Vlad Mihalcea PostgreSQL Mistakes and How to Avoid Them by Jimmy Angelakos Foreword by Gianni Ciolli

Page 5

introduction introduction Postgres is more than a relational database, it's a powerful general-purpose platform that blends SQL elegance with practical features for modern applications. These books guide developers, DBAs, and data practitioners through the depth and breadth of Post- gres capabilities as well as ideas and pitfalls that matter most when building reliable, high-performance apps with Postgres. You’ll learn not only how to use Postgres, but how to use it well to its full extent: applying contemporary SQL techniques, leveraging built-in full-text capabilities, choosing appropriate data types, and avoiding common table and index design mistakes that cost performance and correctness. 4

Page 6

contents contents 1 Modern SQL 7 1.1 What is modern SQL? 8 1.2 Loading the music service dataset 9 1.3 Common table expressions 11 1.4 Recursive queries 20 1.5 Window functions 28 2 Postgres for full-text search 36 2.1 Basics of full-text search in Postgres 37 2.2 Preparing data for text search 44 2.3 Performing full-text search 52 2.4 Ranking search results 57 2.5 Highlighting search results 65 2.6 Indexing lexemes 69 5

Page 7

contents 3 Improper data type usage 76 3.1 TIMESTAMP (WITHOUT TIME ZONE) 76 3.2 TIME WITH TIME ZONE 79 3.3 CURRENT_TIME 80 3.4 CHAR(n) 81 3.5 VARCHAR(n) 83 3.6 MONEY 86 3.7 SERIAL data type 88 3.8 XML 91 4 Table and index mistakes 95 4.1 Table inheritance 95 4.2 Neglecting table partitioning 99 4.3 Partitioning by multiple keys 104 4.4 Using the wrong index type 107 6

Page 8

Modern sQL 1 Modern SQL This chapter covers Why it’s important to learn modern SQL  Using common table expressions  Processing hierarchical or tree-like data  Using window functions to perform calculations  SQL was invented in the early 1970s, predating the creation of Altair, the first personal computer. Despite being invented decades ago, SQL, like many human languages, continues to evolve to meet the demands of the modern world. Most people, however, are still familiar with SQL as defined by the SQL-92 standard, which fully completed the original relational model and idea. But much has changed since the introduction of that standard. The scope of relational databases has expanded far beyond the relational model, and these changes have been reflected in subsequent versions of the language. Modern SQL introduces new capabilities that not only make the language more readable and easier to follow but also simplify complex calculations and enable working with unstructured and semi- structured data. Let’s explore the modern SQL capabilities of Postgres as we build a music streaming service that thousands of people use to listen to their favorite songs and artists. We’ll see how modern SQL makes it easy to analyze user preferences, song popularity, and the other data the service tracks. Page 1 of 109 1Modern SQL This section is taken from Chapter 3 of Just Use Postgres!, by Denis Magda 7

Page 9

Modern sQL 1.1 What is modern SQL? The term modern SQL was coined by Markus Winand relatively recently. Markus is a well- known database expert who puts effort into raising awareness and adoption of the latest SQL capabilities added to the specification after the SQL-92 standard. Markus describes the significance of these changes on his website dedicated to the modern SQL (https://modern- sql.com/) as follows: Since 1999, SQL is not limited to the relational model anymore. Back then, ISO/IEC 9075 (the “SQL standard”) added arrays, objects, and recursive queries. In the meantime, the SQL standard has grown five times bigger than SQL-92. In other words: relational SQL is only about 20% of modern SQL. Overall, under modern SQL, we can assume a category of features and capabilities that were introduced after the SQL-92 standard and allow us to use Postgres beyond the classic relational model and its traditional use cases. The modern SQL supports new data types, functions, and other capabilities that let us work not only with structured data but also with unstructured and semi-structured data: Data types for unstructured data—The relational model is based on structured data and normalization. Data must be properly structured, split into atomic pieces, and related to each other. However, today’s SQL databases support arrays, JSON objects, and custom composite types, allowing us to store and process unstructured data efficiently. Capabilities for modern workloads—SQL has a reputation for being verbose, especially when writing nontrivial queries. However, modern SQL capabilities such as common table expressions (CTEs) and window functions allow us to perform complex operations and transformations with concise and readable queries. Additionally, features like recursive queries and property graphs simplify working with hierarchical, tree-like, and graph structures. Considering the benefits of modern SQL capabilities, why isn’t it in every developer’s toolbox yet? There are at least a few reasons: Stickiness of gained knowledge—Some developers learned SQL many years ago and mastered the SQL-92 version of the language for various data processing tasks. Even if their SQL queries are verbose or less efficient, the tasks are still solvable. As a result, many people continue doing things the way they originally learned. Object-relational mapping (ORM) frameworks—Some developers fully rely on ORM frameworks as a layer between their application and the database. They trust the ORM framework to generate SQL queries, believing it knows the best way to query or manipulate data. However, ORM frameworks are designed for generic workloads and might not recognize when to use efficient window functions instead of cumbersome self-joins in your specific use cases. Page 2 of 109 8

Page 10

Modern sQL The goal of this chapter is to make modern SQL part of your developer toolbox. We’ll learn how to take advantage of CTEs, recursive queries, and window functions in Postgres. In chapters 5 and 6, we’ll continue exploring modern SQL capabilities by learning to work with semi-structured and text data. If you’d like to go beyond these topics, be sure to visit Markus Winand’s Modern SQL website (https://modern- sql.com/). 1.2 Loading the music service dataset We’ll explore the modern SQL capabilities by working through an example of a music streaming service where users can listen to their favorite songs and artists. The database schema is minimalistic and looks like figure 1.1. Figure 1.1 A simple database schema for a music streaming service The songs and users tables are self-explanatory, storing information about the users subscribed to the streaming service and the songs available for listening. The plays table tracks the songs users listen to. When a user plays a new song, a new record is added to the plays table with the following details: The id column is a unique identifier for the current play session. The user_id and song_id columns reference the respective user and song records. The play_start_time column stores the timestamp of the time when the user started listening to the song. The play_duration column tracks how long the song was played by the user. Page 3 of 109 9

Page 11

Modern sQL The previous_play column is either set to NULL or holds the id of the previous play session. This helps track a sequence of songs played one after another without interruption. NOTE If you’d like to gain practical experience while reading the chapter, connect to the Postgres instance you started in chapter 1 using the docker exec -it postgres psql -U postgres command. Follow these steps to preload the dataset into the Postgres instance in Docker: 1. Clone the book’s repository with listings and sample data: 2. Copy the music streaming dataset to your Postgres container: 3. Preload the dataset by connecting to the container and using the \i meta- command of psql to execute the copied SQL scripts: 4. Once the dataset is preloaded, connect to Postgres and check the created tables with the \Dt Streaming.* command: The output will be as follows: git clone https://github.com/dmagda/just-use-postgres-book cd just-use-postgres-book/ docker cp data/streaming/. postgres:/home/. docker exec -it postgres psql -U postgres -c "\i /home/streaming_ddl.sql" docker exec -it postgres psql -U postgres -c "\i /home/streaming_data.sql" docker exec -it postgres psql -U postgres \dt streaming.* List of relations Schema | Name | Type | Owner streaming | plays | table | postgres streaming | songs | table | postgres streaming | users | table | postgres (3 rows) Page 4 of 109 10

Page 12

Modern sQL With the dataset in place, let’s move forward and begin our journey into modern SQL with CTEs. 1.3 Common table expressions CTEs allow us to break down large or complex queries into smaller, more manageable pieces. They are defined using the WITH clause in the following format: The WITH clause defines a named CTE that can be referenced later in the primary statement. The CTE evaluates the auxiliary statement, which is a regular SQL query that either SELECTs existing data or modifies it with INSERT, UPDATE, DELETE, or MERGE statements. The result of the execution is available to the primary statement that is executed next. The primary statement can be a SELECT query or a data modification command such as INSERT or UPDATE that uses the data produced by the CTE. Overall, you can think of a CTE as a temporary table or view generated and used solely by the given query. Let’s return to our imaginary music streaming service and learn more about CTEs by seeing them in action. 1.3.1 Selecting data with CTEs Suppose we need to track the most popular songs played on the service within a specific time range. The following listing shows how such a report can be generated using a CTE. WITH cte_name AS ( auxiliary_statement ) primary_statement Listing 1.1 Tracking the most popular songs WITH plays_cte AS ( SELECT s.title, s.duration FROM streaming.plays p JOIN streaming.songs s ON p.song_id = s.id WHERE p.play_start_time::DATE BETWEEN '2024-09-15' AND '2024-09-16' AND p.play_duration = s.duration ) SELECT title, COUNT(*) AS play_count FROM plays_cte GROUP BY title ORDER BY play_count DESC; Page 5 of 109 11

Page 13

Modern sQL The query defines the plays_cte CTE that executes the auxiliary statement returning all the songs played by users to the end (p.play_duration = s.duration) between 2024-09- 15 and 2024-09-16. The songs table stores information about the compositions, and the plays table tracks how long the songs were played by the users. The primary statement calculates the rank by using the result produced by the CTE (SELECT … FROM plays_cte) and counting the total number of times each song was played by users (COUNT(*) AS play_count). The query returns the rank of the most popular songs as follows: NOTE Depending on the complexity of the query, Postgres may decide to fold a CTE into the primary statement or transform the original query into a more efficient form. For instance, the query in listing 1.1 uses a CTE for educational purposes but can be rewritten as a single SELECT statement. Postgres recognizes this and will rewrite or optimize the query accordingly. You can verify this by checking the execution plan with the EXPLAIN ANALYZE statement. Just as you can easily read and modify clearly written application logic, queries using CTEs can also be adjusted seamlessly. Suppose the music streaming service needs to track the least popular songs within a specific time range. This ranking can be helpful when algorithms need to decide what to add or remove from users’ recommendations. The next query shows how the rank can be calculated with a few adjustments to the previous query. title | play_count Song D | 4 Song A | 3 Song G | 2 (3 rows) Listing 1.2 Finding the least popular songs WITH plays_cte AS ( SELECT s.title, s.duration, p.play_duration FROM streaming.plays p JOIN streaming.songs s ON p.song_id = s.id WHERE p.play_start_time::DATE BETWEEN '2024-09-15' AND '2024-09-16' AND p.play_duration < (s.duration / 2) ) SELECT title, MIN(play_duration) AS min_play_duration FROM plays_cte GROUP BY title ORDER BY min_play_duration ASC LIMIT 3; Page 6 of 109 12

Page 14

Modern sQL As before, the plays_cte CTE populates a list of songs with their played duration between 2024-09-15 and 2024-09-16. However, this time the CTE returns songs that were listened to for less than half of their original duration (p.play_duration < (s.duration / 2)). These songs qualify as less popular on our streaming service. The primary statement queries plays_cte to find the three songs that were played the least (MIN(play_duration)). The least popular songs are as follows, with min_play_duration measured in seconds: 1.3.2 Using multiple CTEs in a query So far, we’ve looked at queries with a single CTE. However, a single query can define multiple CTEs, where each subsequent CTE can reference the ones defined before it (except for the first CTE, which has no preceding CTEs). Suppose we find the query in listing 1.2 a bit unfair for just-released songs, and we want to modify it so that a song is added to the least-popular rank only if three or more users listened to it and didn’t find it interesting. This can be achieved by using the following version of the query. title | min_play_duration Song E | 32 Song F | 78 Song J | 91 (3 rows) Page 7 of 109 13

Page 15

Modern sQL The plays_cte CTE remains unchanged and looks exactly the same as in listing 1.2. It generates a list of songs that were played for less than half of their duration. The user_play_counts CTE queries the plays_cte CTE and counts how many times each song was played by unique users. The primary statement ranks the least popular songs by querying the results of the user_play_counts CTE and returns only those songs that were listened to by at least three users (WHERE user_count >= 3). The output of the query is as follows: This query clearly achieves our goal, and its logic is easy to read and follow. However, based on this query, Postgres needs to execute three separate SELECT statements to produce the final result. Wouldn’t it be more efficient and performant to use nested queries (subqueries) or consolidate them into a single SELECT statement? The answer is: it depends. We can’t know for certain until we compare the execution plans. But one thing you can be confident about, though, is that Postgres doesn’t always execute queries the way they’re written. A SQL query is simply a request to do some action or produce a result, and it’s up to the database to decide how to execute that request. Listing 1.3 Finding least-popular songs across three or more users WITH plays_cte AS ( SELECT s.title, s.duration, p.play_duration, p.user_id FROM streaming.plays p JOIN streaming.songs s ON p.song_id = s.id WHERE p.play_start_time::DATE BETWEEN '2024-09-15' AND '2024-09-16' AND p.play_duration < (s.duration / 2) user_play_counts AS ( SELECT title, duration, COUNT(DISTINCT user_id) AS user_count, MIN(play_duration) AS min_play_duration, COUNT(*) AS total_play_count FROM plays_cte GROUP BY title, duration ) SELECT title, duration, min_play_duration, total_play_count FROM user_play_counts WHERE user_count >= 3 ORDER BY min_play_duration ASC LIMIT 3; title | duration | min_play_duration | total_play_count Song E | 150 | 32 | 3 (1 row) Page 8 of 109 14

Page 16

Modern sQL If we want to understand how a query is actually executed by Postgres, we can look at the query execution plan using the EXPLAIN statement. We’ll cover EXPLAIN in more detail in chapter 4, but, in the meantime, let’s add the EXPLAIN(analyze, costs off, timing off) command to the beginning of the query from listing 1.3 and execute it again. Once we do this, Postgres will produce the following execution plan: Even though we define the plays_cte CTE at the very beginning of our SQL query, it doesn’t appear in the execution plan. Instead, Postgres folds plays_cte into the user_play_counts and evaluates the latter: Hash Join phase—The database joins the plays and songs tables, returning only the rows that satisfy the search condition. QUERY PLAN Limit (actual rows=1 loops=1) -> Sort (actual rows=1 loops=1) Sort Key: user_play_counts.min_play_duration Sort Method: quicksort Memory: 25kB -> Subquery Scan on user_play_counts (actual rows=1 loops=1) -> GroupAggregate (actual rows=1 loops=1) Group Key: s.title, s.duration Filter: (count(DISTINCT p.user_id) >= 3) Rows Removed by Filter: 5 -> Sort (actual rows=9 loops=1) Sort Key: s.title, s.duration, p.user_id Sort Method: quicksort Memory: 25kB -> Hash Join (actual rows=9 loops=1) Hash Cond: (s.id = p.song_id) Join Filter: (p.play_duration ➥ < (s.duration / 2)) Rows Removed by Join Filter: 27 -> Seq Scan on songs s ➥ (actual rows=10 loops=1) -> Hash (actual rows=36 loops=1) Buckets: 1024 Batches: 1 ➥ Memory Usage: 10kB -> Seq Scan on plays p ➥ (actual rows=36 loops=1) Filter: (( ➥ (play_start_time)::date >= '2024-09-15'::date) AND ➥ ((play_start_time)::date <= '2024-09-16'::date)) Page 9 of 109 15

Page 17

Modern sQL GroupAggregate phase—Because there are no secondary indexes, Postgres sorts the result of the Hash Join phase and then groups the rows according to the condition from the primary statement that at least three users must have listened to the song (COUNT(DISTINCT p.user_id) >= 3). Subquery Scan on user_play_counts phase—Postgres materializes the user_play_counts CTE and iterates through the final result, calculating the rank of the songs. Thus, even if the CTE version of a query appears longer and seems more complex to execute, that doesn’t necessarily mean it will be less efficient. In the case of the query from listing 1.3, Postgres traverses the plays and songs tables once, filtering, sorting, and grouping the data based on the conditions defined in the two CTEs and the primary statement. So, when deciding between using nested queries or CTEs for a particular task, choose the option that makes the flow of your SQL query clearer and more readable for you and your team. In most cases, Postgres can efficiently fold CTEs into each other and the primary statement, allowing you to benefit from the improved readability of CTEs without compromising performance. NOTE If a CTE is referenced more than once from the primary statement or other subsequent CTEs, Postgres will materialize it by evaluating it once and storing the result for future references. You can also enforce materialization by explicitly adding MATERIALIZED to the CTE definition as follows: WITH cte_name AS MATERIALIZED. On the other hand, using NON MATERIALIZED has the opposite effect, instructing Postgres to reevaluate the CTE every time it is referenced, regardless of how many times it’s called. 1.3.3 Modifying data with CTEs In addition to querying data with CTEs, it’s also possible to create data-modifying CTEs. These CTEs use INSERT, UPDATE, DELETE, or MERGE statements within the WITH clause, making the new/modified/deleted records available to the primary statement. For example, let’s assume that our music streaming service uses the following data- modifying CTE to update the play duration of a song while users are listening to it, and then checks if the song’s rank has changed after each update. Page 10 of 109 16

Page 18

Modern sQL The query performs the following steps: The CTE updates the plays table by setting play_duration to 200 seconds for the play session with id = 30. If 200 seconds is the total duration of the song, it indicates that the user listened to the entire song, which may cause the song to move up in rank. The CTE uses the RETURNING clause to return song_id and its updated play_duration to the primary statement. The primary statement checks whether play_duration from the CTE matches the song’s total duration. If so, it means that the song’s rank has changed. If you execute this data-modifying CTE, the result should be as follows: TIP If you need to return all the columns of the change set, use the RETURNING * clause. The RETURNING clause is optional and can be omitted in a data-modifying CTE if the primary statement doesn’t need to process the result of the CTE’s execution. Another interesting point about data-modifying CTEs is that they are executed even if the primary statement doesn’t reference them. This behavior differs from CTEs with SELECT statements, which are evaluated only if explicitly referenced by the primary query. Listing 1.4 Data-modifying CTE WITH updated_play AS ( UPDATE streaming.plays SET play_duration = 200 WHERE id = 30 RETURNING song_id, play_duration ) SELECT s.title, s.duration, CASE WHEN up.play_duration = s.duration THEN 'Moved Up the Rank' ELSE 'Rank Not Changed' END AS rank_change_status FROM updated_play up JOIN streaming.songs s ON s.id = up.song_id; title | duration | rank_change_status Song A | 200 | Moved Up the Rank (1 row) Page 11 of 109 17

Page 19

Modern sQL As with CTEs that use SELECT statements, a single query can include multiple CTEs that either modify or query data. However, in this case, all CTEs are executed concurrently, and the changes made by data-modifying CTEs are not visible to other CTEs within the same query. Let’s see how concurrent execution works in practice by updating and querying the play_duration of the play session with id = 12 from two different CTEs: Currently, this play session corresponds to the song with song_id = 3, and the user has already listened to 118 seconds of the song: Let’s use the following CTE to update the play_duration of that play session and check whether this change is visible to the subsequent CTE. SELECT song_id, play_duration FROM streaming.plays WHERE id = 12; song_id | play_duration 3 | 118 (1 row) Listing 1.5 Querying and modifying CTEs executed concurrently WITH updated_play AS ( UPDATE streaming.plays SET play_duration = 150 WHERE id = 12 RETURNING song_id, play_duration current_play_duration AS ( SELECT song_id, (play_duration = 150) as is_change_visible_to_cte FROM streaming.plays WHERE id = 12 ) SELECT is_change_visible_to_cte, (play_duration = 150) as is_change_visible_to_primary FROM updated_play up JOIN current_play_duration cp ON up.song_id = cp.song_id; Page 12 of 109 18

Page 20

Modern sQL The updated_play CTE changes play_duration for the session to 150 seconds. The current_play_duration CTE reads the current value of the same play session and sets the is_change_visible_to_cte column to true if play_duration is 150 seconds. The primary statement then queries the results of both CTEs, indicating whether the changes made by the data-modifying CTE were visible to the subsequent CTE and to the primary statement. The output of the query looks as follows: As we can see, the is_change_visible_to_cte column is set to false, indicating that the changes made by the updated_play CTE were not visible to the current_play_duration CTE. This occurs because both CTEs were executed concurrently on the same snapshot of data. If we want the current_play_duration CTE to run after updated_play and see its changes, we need to have current_play_duration reference updated_play directly, as shown next. This time, the is_change_visible_to_cte column is set to true, meaning the current_play_duration CTE was able to see the changes made by updated_play by querying it directly: is_change_visible_to_cte | is_change_visible_to_primary f | t (1 row) Listing 1.6 Querying and modifying CTEs executed sequentially WITH updated_play AS ( UPDATE streaming.plays SET play_duration = 160 WHERE id = 12 RETURNING id, song_id, play_duration current_play_duration AS ( SELECT song_id, (play_duration = 160) as is_change_visible_to_cte FROM updated_play WHERE id = 12 ) SELECT is_change_visible_to_cte, (play_duration = 160) as is_change_visible_to_primary FROM updated_play up JOIN current_play_duration cp ON up.song_id = cp.song_id; Page 13 of 109 19