Introduction to PostgreSQL for the data professional (Ryan Booz, Grant Fritchey) (z-library.sk, 1lib.sk, z-lib.sk)

Introduction to PostgreSQL for the Data Professional First Edition By Ryan Booz & Grant Fritchey Published by Red Gate Books 2024

Copyright © 2024 by Grant Fritchey and Ryan Booz Title: Introduction to PostgreSQL for the data professional. Authors: Grant Fritchey and Ryan Booz Technical Reviewer: Robert Treat Copy Editor: Louis Davidson Edition: Preview Edition Publication Year: 2024 All rights reserved. No part of this publication may be reproduced, distributed, or transmitted in any form or by any means, including photocopying, recording, or other electronic or mechanical methods, without the prior written permission of the publisher, except in the case of brief quotations embodied in critical reviews and certain other non-commercial uses permitted by copyright law. For permission requests, write to the publisher, addressed “Attention: Permissions Coordinator,” at the address below. Publisher: Red Gate Books Cavendish House, Cambridge Business Park Cambridge, CB4 0XB United Kingdom ISBN: 978-1-3999-9678-5 Library of Congress Cataloging-in-Publication Data Introduction to PostgreSQL for the data professional. / Grant Fritchey and Ryan Booz. Includes bibliographical references and index. ISBN 978-1-3999-9678-5 1. PostgreSQL 2. Database Management 3. SQL Printed in United States of America

i Table of Contents About the Authors ............................................................................................................................ x About the Tech Editor ..................................................................................................................... xi Forward ............................................................................................................................................ xii Preface ............................................................................................................................................ xiv Chapter 1: An Introduction to PostgreSQL .................................................................................... 1 What’s in a Name ............................................................................................................................ 2 How It All Began .............................................................................................................................. 3 Going Open-Source ..................................................................................................................... 4 PostgreSQL Governance ............................................................................................................ 5 Commitfest ...................................................................................................................................... 7 Why PostgreSQL and Why Now ................................................................................................... 10 Conclusion..................................................................................................................................... 11 Chapter 2: PostgreSQL Basics and Differences ......................................................................... 13 Feature Comparison ..................................................................................................................... 13 Extensibility ................................................................................................................................ 14 Built-in Job Scheduler................................................................................................................ 15 Query Hints ................................................................................................................................ 16 Query Plan Cache ..................................................................................................................... 16 Execution Plan Viewer............................................................................................................... 17 Terminology Differences ............................................................................................................... 18 Cluster vs. Instance ................................................................................................................... 18 Role vs User .............................................................................................................................. 18 Tuple vs. Row ............................................................................................................................ 19 Literal Object Qualifiers ............................................................................................................. 19 COPY vs. BULK INSERT .......................................................................................................... 20 TOAST ....................................................................................................................................... 20 SQL Differences ............................................................................................................................ 20

ii Data Types ................................................................................................................................ 21 Conclusion .................................................................................................................................... 30 Chapter 3: Installing PostgreSQL ................................................................................................. 31 Minimum Hardware ...................................................................................................................... 31 Where To Get the Bits .................................................................................................................. 32 Linux Install ................................................................................................................................... 34 Windows Install ............................................................................................................................. 36 Containers .................................................................................................................................... 43 Conclusion .................................................................................................................................... 44 Chapter 4: PostgreSQL Tools ....................................................................................................... 45 psql ............................................................................................................................................... 45 Installing psql ............................................................................................................................ 46 Connecting to PostgreSQL from psql ....................................................................................... 48 Using psql ................................................................................................................................. 48 pgAdmin ........................................................................................................................................ 51 Installing pgAdmin ..................................................................................................................... 52 Using pgAdmin .......................................................................................................................... 54 DBeaver ........................................................................................................................................ 59 Installing DBeaver ..................................................................................................................... 59 Using DBeaver .......................................................................................................................... 60 Azure Data Studio ........................................................................................................................ 65 Installing Azure Data Studio...................................................................................................... 66 Working With Azure Data Studio .............................................................................................. 68 Conclusion .................................................................................................................................... 72 Chapter 5: Sever Configuration .................................................................................................... 73 Memory Settings ........................................................................................................................... 73 Modify Configuration Settings in PostgreSQL .......................................................................... 75 Caching Data Pages ..................................................................................................................... 75 Query Memory .............................................................................................................................. 76

iii Number of Connections ................................................................................................................ 78 Ongoing Maintenance and Backup/Restores ............................................................................... 78 Query Plan Settings ...................................................................................................................... 80 Random Page Cost ................................................................................................................... 80 Effective Cache Size ................................................................................................................. 81 Just-In-Time Compilation........................................................................................................... 82 Conclusion..................................................................................................................................... 82 Chapter 6: Roles and Privileges .................................................................................................... 83 Host-based Authentication ............................................................................................................ 83 Cluster, Databases, and Roles ..................................................................................................... 86 Roles.......................................................................................................................................... 87 Users and Groups ..................................................................................................................... 87 Role Attributes ........................................................................................................................... 88 The Superuser Role .................................................................................................................. 90 Role Privileges .............................................................................................................................. 91 GRANT and REVOKE ............................................................................................................... 93 The PUBLIC Role ...................................................................................................................... 97 Object Ownership ...................................................................................................................... 98 Conclusion ............................................................................................................................... 105 Chapter 7: Creating Databases ................................................................................................... 107 What Is a Database ..................................................................................................................... 107 Creating a Database ................................................................................................................... 109 Modifying a Database ................................................................................................................. 113 Removing a Database ................................................................................................................. 116 Database Templates ................................................................................................................... 117 Tablespace .................................................................................................................................. 120 Conclusion................................................................................................................................... 122 Chapter 8: Extensions .................................................................................................................. 123 The History of Extensions ........................................................................................................... 123

iv What Are Extensions? ................................................................................................................ 124 Available Extensions .................................................................................................................. 126 Installing Extensions ............................................................................................................... 128 Updating an Extension ............................................................................................................ 129 Dropping an Extension ............................................................................................................ 130 Making Extensions Available for Use ......................................................................................... 130 Extension Registries ............................................................................................................... 131 Pre-made Docker Containers ................................................................................................. 132 Linux Package Managers ....................................................................................................... 133 Cloud-Hosted Databases ........................................................................................................ 134 Two Words of Caution ................................................................................................................ 134 Extensions Across Environments ........................................................................................... 135 Extension Backup and Restore .............................................................................................. 135 Extensions to Try ........................................................................................................................ 136 pg_stat_statements ................................................................................................................. 136 postgis ..................................................................................................................................... 137 pg_hint_plan ........................................................................................................................... 137 pg_cron ................................................................................................................................... 138 postgres_fdw ........................................................................................................................... 139 pg_partman ............................................................................................................................. 140 pg_trgm ................................................................................................................................... 141 hypopg .................................................................................................................................... 142 Vector and AI Extensions ........................................................................................................... 142 pgvector .................................................................................................................................. 143 pgai ......................................................................................................................................... 143 azure_ai .................................................................................................................................. 144 Conclusion .................................................................................................................................. 144 Chapter 9: Core Object Types in PostgreSQL .......................................................................... 145 SQL Examples ............................................................................................................................ 145

v Object Ownership ........................................................................................................................ 146 Core Object Types ...................................................................................................................... 146 Databases ............................................................................................................................... 146 Schema ................................................................................................................................... 147 Tables ...................................................................................................................................... 147 Sequences ............................................................................................................................... 152 Indexes .................................................................................................................................... 153 Functions ................................................................................................................................. 158 Procedures .............................................................................................................................. 159 View ......................................................................................................................................... 160 Materialized View .................................................................................................................... 161 Foreign Table .......................................................................................................................... 161 Trigger ..................................................................................................................................... 162 Types ....................................................................................................................................... 162 Domains .................................................................................................................................. 163 Conclusion................................................................................................................................... 163 Chapter 10: Introducing PL/pgSQL ............................................................................................. 165 Purpose of a Procedural Language ............................................................................................ 166 Basic PL/pgSQL Syntax .............................................................................................................. 166 Blocks ...................................................................................................................................... 167 Variables .................................................................................................................................. 168 Error Handling ......................................................................................................................... 169 Procedural Language .................................................................................................................. 171 IF/THEN ................................................................................................................................... 172 CASE ....................................................................................................................................... 173 Loops ....................................................................................................................................... 174 Creating Procedural Objects ....................................................................................................... 179 Functions ................................................................................................................................. 179 Procedures .............................................................................................................................. 183

vi Cursors.................................................................................................................................... 186 Conclusion .................................................................................................................................. 189 Chapter 11: Query Tuning and Indexes ..................................................................................... 191 EXPLAIN ..................................................................................................................................... 191 Estimated Query Plans ........................................................................................................... 192 EXPLAIN Options ................................................................................................................... 193 Adding run-time statistics ........................................................................................................ 193 BUFFERS ............................................................................................................................... 195 Query Cost and Actual Time ................................................................................................... 198 Primary Node Types ................................................................................................................... 199 Scan Nodes ............................................................................................................................ 199 Join Nodes .............................................................................................................................. 200 Other Nodes ............................................................................................................................ 201 Additional Areas to Troubleshoot ............................................................................................... 201 First things first – work_mem .................................................................................................. 202 External Sort Disk ................................................................................................................... 202 Indexes ....................................................................................................................................... 205 B-Tree Deduplication .............................................................................................................. 206 Functional Indexes .................................................................................................................. 208 Partial Indexes ........................................................................................................................ 209 Composite Indexes ................................................................................................................. 210 Covering Indexes .................................................................................................................... 213 Logging Query Plans .................................................................................................................. 214 Auto Explain ............................................................................................................................ 214 Reading Execution Plans from the Log .................................................................................. 217 More Resources ......................................................................................................................... 217 Chapter 12: Backup and Restore................................................................................................ 219 Restore Strategy ......................................................................................................................... 220 Database Backups ..................................................................................................................... 221

vii SQL Dump ............................................................................................................................... 222 File System Backup ................................................................................................................. 225 Base Backups .......................................................................................................................... 225 Comparing Backup Mechanisms ............................................................................................. 227 Restoring a Database ................................................................................................................. 228 Restoring to a Point in Time ........................................................................................................ 231 WAL Archiving ......................................................................................................................... 232 Recovery To a Point In Time ................................................................................................... 233 Conclusion................................................................................................................................... 235 Chapter 13: MVCC, VACUUM, and ANALYZE ............................................................................ 237 MVCC in PostgreSQL ................................................................................................................. 238 Row Versions .............................................................................................................................. 239 Row Versions in Practice ............................................................................................................ 240 Row Visibility ........................................................................................................................... 241 Transaction ID Space .............................................................................................................. 242 The Vacuum Process .................................................................................................................. 243 VACUUM and autovacuum ..................................................................................................... 243 Dead Tuples and Table Bloat .................................................................................................. 245 Freezing Live Tuples ............................................................................................................... 247 ANALYZE and autoanalyze ........................................................................................................ 247 Configuration and Maintenance Tasks ....................................................................................... 249 maintenance_work_mem ........................................................................................................ 249 Reindexing ............................................................................................................................... 250 Fill Factor ................................................................................................................................. 251 Heap-Only Tuples .................................................................................................................... 252 Conclusion................................................................................................................................... 254 Chapter 14: Replication and HA .................................................................................................. 255 Cloud Hosted Databases ............................................................................................................ 255 Replication................................................................................................................................... 256

viii Physical Replication ................................................................................................................ 256 Logical Replication .................................................................................................................. 258 Replication Slots ..................................................................................................................... 258 Setting Up Streaming Replication............................................................................................... 260 Setting Up Logical Replication.................................................................................................... 267 Conclusion .................................................................................................................................. 275 Chapter 15: Monitoring PostgreSQL .......................................................................................... 277 Knowledge Drives Decisions ...................................................................................................... 278 Collecting Server Information ..................................................................................................... 279 Error Logs ............................................................................................................................... 279 Cumulative Statistics System.................................................................................................. 283 Query Performance Metrics ........................................................................................................ 290 Query Metrics at Execution ..................................................................................................... 290 Monitoring Query Metrics ........................................................................................................ 292 Conclusion .............................................................................................................................. 295 Chapter 16: PostgreSQL on the Cloud ...................................................................................... 297 Why the Cloud? .......................................................................................................................... 298 Why Platform as a Service (PaaS)? ........................................................................................... 299 PaaS Services ............................................................................................................................ 300 AWS ........................................................................................................................................ 300 Azure Database for PostgreSQL ............................................................................................ 303 Google Cloud Platform ............................................................................................................ 305 Conclusion .................................................................................................................................. 308 Chapter 17: Where To Go For More Learning ........................................................................... 309 PostgreSQL Documentation ....................................................................................................... 310 Books on PostgreSQL ................................................................................................................ 313 The Art of PostgreSQL ............................................................................................................ 313 Database Administration: The Complete Guide to DBA Practices and Procedures .............. 313 PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries .............. 314

ix PostgreSQL Events ..................................................................................................................... 314 User Groups and Meetups ...................................................................................................... 315 Local Events ............................................................................................................................ 316 International Events ................................................................................................................. 317 Online Resources ........................................................................................................................ 319 Aggregations ........................................................................................................................... 319 Podcasts .................................................................................................................................. 320 Blogs ........................................................................................................................................ 321 Webinars ................................................................................................................................. 321 Conclusion................................................................................................................................... 322 Index ............................................................................................................................................ 325

x About the Authors Ryan Booz Ryan is an Advocate at Redgate focusing on PostgreSQL. Ryan has been working as a PostgreSQL advocate, developer, DBA and product manager for more than 22 years, primarily working with time-series data on PostgreSQL and the Microsoft Data Platform. Ryan is a long-time DBA, starting with MySQL and Postgres in the late 90s. He spent more than 15 years working with SQL Server before returning to PostgreSQL full-time in 2018. He's at the top of his game when he's learning something new about the data platform or teaching others about the technology he loves. Grant Fritchey Grant Fritchey is a Data Platform MVP and AWS Community Builder with over 30 years’ experience in IT, including time spent in support and development. Grant works with multiple data platforms including PostgreSQL and SQL Server, as well as multiple cloud platforms. He has also developed in Python, C#, and Java. Grant writes books for Apress and Redgate. Grant presents at conferences and user groups, large and small, all over the world. He joined Redgate Software as a product advocate in January 2011.

xi About the Tech Editor Robert Treat Robert Treat is a seasoned database professional with nearly three decades of experience working on mission critical, data intensive systems. A passionate open-source advocate and contributor, he is probably best known for his work with Postgres, where he has been recognized as a Major Contributor. His career has traversed a diverse set of organizations including DoorDash, Etsy, Amazon, National Geographic, and WebMD, where he has worked in both leadership and practitioner roles, honing his expertise in databases management and operations. As an independent community advocate, he dedicates his time to fostering open-source initiatives, speaking on a wide variety of topics including open source, devops, and scalable web operations, with a goal of empowering others and driving innovation and operability within the field.

xii Foreword It is with great pleasure that I introduce this comprehensive guide to PostgreSQL, authored by two very respected figures in the database community, Grant Fritchey and Ryan Booz. Together, they have produced many blog posts and books about databases for years and years. As you read this book, this is very evident, making it a valuable resource. I have read this book as closely as I have any other book because I served as the copy editor for most of the book. I have known one of the authors for over 15 years, and the other for 2 now, but I have learned so much about PostgreSQL (and SQL Server and other topics) from them both over these years. Grant Fritchey, known for his extensive work with SQL Server, has over the past few years, added PostgreSQL to his skill set and brings a wealth of knowledge and practical insights about both to this topic. His experience is not just theoretical; it is grounded in real-world applications and challenges, making his contributions both relevant and actionable. Grant has been a part of the database community for many years now and has always been a wonderful teacher and all-around nice person to deal with. I met Ryan Booz through working with him at Redgate and he has taught me a lot about how PostgreSQL works (and as a SQL Server expert myself, there are some very interesting differences that aren’t always as obvious as you might expect, which is a part of why this book was written!) Everything I have said about how great it is to work with Grant goes exactly the same for Ryan. Ryan has been instrumental in educating the community through his regular online seminar series, "PostgreSQL 101," and his numerous articles on Simple-Talk.com. His passion for PostgreSQL and his ability to break down complex concepts into understandable terms have made him a trusted voice in the community. This book is a testament to their hard work and dedication. It provides a very nice introduction to PostgreSQL and includes hints throughout on how it is similar and different from other RDBMS, covering everything from installation and configuration to advanced performance tuning and optimization. Whether you are new to PostgreSQL or

xiii looking to deepen your understanding, this book offers valuable insights and practical advice that will help you succeed. I will go so far as to say that this book was one of the easiest I have ever had the pleasure to edit, and that includes all my own books as well. I am confident that readers will find this book to be an essential addition to their technical library and am proud to admit that here in this foreword. Louis Davidson (Simple Talk Editor)

xiv Preface There is no denying that PostgreSQL is growing in popularity. You can look at the history of its growth on the DB-Engines Ranking web site if there’s any doubt. As more and more organizations begin to manage some, or all, of their data in PostgreSQL, a growing number of people are going to have to know how PostgreSQL works. We’re writing this book for you. Whether you’re a Database Administrator (DBA) who has to learn how to maintain a whole new data platform, a developer looking for new and better ways to manage information persistence, or even someone fresh in the IT field looking to expand your skill set, this book is for you. However, that said, we do assume a certain amount of knowledge of databases in general. We have comparisons to other data platforms, frequently Microsoft SQL Server, but others as well, to help establish context. So, this book is more a “beginners in PostgreSQL” rather than a “beginners in databases” in general style book. Your authors have a very large amount of accumulated knowledge of databases, database management, and database development. We tried to add as much of that into the book as we can. There’s guidance for best use of PostgreSQL in a lot of the chapters, not simply descriptions of how things work. We know, based on our own blunders and learning curve, that why you’re doing something matters as much as how. So, we put that into the book as well. Since this is very much an introductory book for PostgreSQL, the best way to get the maximum value from the book is to follow the flow of the book. This is especially true because we use code and structures introduced in earlier chapters, later in the book, so skipping around could cause confusion. However, most chapters stand on their own, for the most part. While it is certainly possible to skip around, you may hit snags when you run the sample code. Speaking of sample code, you’ll generally see it looking like this:

xv Listing X-1. This is sample code SELECT cola FROM sometable; The code will be introduced in some manner, you’ll see it in a different format, and you’ll see the Listing caption with a chapter and the number of the listing within the chapter. This makes it possible to refer back to code within a chapter. You will also see code that’s shown in-line with the text, just like sometable here. You’ll note that this has a different font, to help set it apart from the rest of the text. Speaking of Notes, and Warnings, you’ll see this throughout the book: NOTE/WARNING/CAUTION: These are points that we don’t want to get lost in the text because they’re important. This way, you can spot them easily. We broke the chapters down into sections that will have headings to let you know what they’re about. Hopefully this helps navigation as you read the book. We are both excited to share this book with you. There are several reasons for this. While writing a book is somewhat difficult, and very time consuming, when you’re done, there’s a real sense of accomplishment. Also, both of us have run into issues while learning PostgreSQL and thought to ourselves, if only someone had pointed this out for me. Well, we’ve tried to do that throughout the book in order to help you on your journey. Finally, and most important, we both really enjoy being able to help others. We enjoy it even more when it’s helping others get going on the PostgreSQL data platform, because we’ve had a lot of fun exploring this space. Our goal is to help you on that journey too. Thanks for reading.

1 1 An Introduction to PostgreSQL PostgreSQL is unique among other popular databases both in its foundations and in the way that it is developed and maintained. For users primarily experienced with commercial relational databases, this can sometimes be a point of intrigue… and confusion. PostgreSQL was, has been, and will continue to be a true open-source database run by the community. This means that no one person, group of people, or organization owns PostgreSQL. Anybody can use it without paying a license, and anybody can derive new work and other databases with the code as they see fit. (Spoiler alert: hundreds of companies and organizations have created database variants using the PostgreSQL source code over the years.) If you are new to PostgreSQL and are planning to use it for upcoming projects, it’s useful that you understand some of the background that makes PostgreSQL what it is today. This history gives you background on how the project works, how the community has evolved, and what you can expect for support and feature updates in the future. Plus, it’s just an interesting project that many of us have come to rely on and it’s fascinating to understand the differences from what you know today with whatever RDBMS you’re using.