PostgreSQL Mistakes and How to Avoid Them

PostgreSQL Mistakes and How to Avoid Them....1

brief contents....8

contents....9

foreword....13

preface....15

acknowledgments....17

about this book....19

Who should read this book....20

How this book is organized: A road map....20

About the code....21

liveBook discussion forum....22

Author online....22

about the author....23

about the cover illustration....24

1 Why PostgreSQL matters—and why talking about mistakes does too....25

1.1 Why learning about PostgreSQL matters....25

1.2 Why talking about PostgreSQL mistakes matters....26

1.3 What you will learn....27

1.4 Typical kinds of PostgreSQL mistakes....28

1.4.1 Coming with expectations from other databases....28

1.4.2 Misunderstanding PostgreSQL....28

1.4.3 Misunderstanding the documentation....29

1.4.4 Using relics from the SQL Standard....29

1.4.5 Not following best practices....29

1.5 How this book works....29

1.5.1 Mental models....30

1.5.2 Example mistake....31

1.6 Sample database: Frogge Emporium....35

Summary....35

2 Bad SQL usage....36

2.1 Using NOT IN to exclude....36

2.1.1 Performance implications....39

2.1.2 Alternative....40

2.2 Selecting ranges with BETWEEN....41

2.3 Not using CTEs....43

2.4 Using uppercase identifiers....46

2.5 Dividing INTEGERs....48

2.6 COUNTing NULL values....51

2.7 Querying indexed columns with expressions....52

2.8 Upserting NULLs in a composite unique key....54

2.9 Selecting and fetching all the data....57

2.10 Not taking advantage of checkers/linters or large language models....59

2.10.1 Code checkers/linters....60

2.10.2 Large language models....63

Summary....65

3 Improper data type usage....67

3.1 TIMESTAMP (WITHOUT TIME ZONE)....67

3.2 TIME WITH TIME ZONE....70

3.3 CURRENT_TIME....71

3.4 CHAR(n)....71

3.5 VARCHAR(n)....73

3.6 MONEY....75

3.7 SERIAL data type....77

3.8 XML....79

Summary....81

4 Table and index mistakes....82

4.1 Table inheritance....82

4.2 Neglecting table partitioning....86

4.3 Partitioning by multiple keys....90

4.4 Using the wrong index type....92

Summary....98

5 Improper feature usage....99

5.1 Selecting SQL_ASCII as the encoding....99

5.2 CREATE RULE....105

5.3 Relational JSON....107

5.4 Putting UUIDs everywhere....110

5.5 Homemade multi-master replication....113

5.6 Homemade distributed systems....118

Summary....121

6 Performance bad practices....122

6.1 Default configuration in production....123

6.2 Improper memory allocation....125

6.3 Having too many connections....128

6.4 Having idle connections....132

6.4.1 What is MVCC?....132

6.4.2 The problem with idle connections....132

6.5 Allowing long-running transactions....134

6.5.1 Idle in transaction....134

6.5.2 Long-running queries in general....136

6.6 High transaction rate....137

6.6.1 XID wraparound....137

6.6.2 Burning through lots of XIDs....138

6.7 Turning off autovacuum/autoanalyze....139

6.8 Not using EXPLAIN (ANALYZE)....141

6.9 Locking explicitly....142

6.10 Having no indexes....143

6.11 Having unused indexes....145

6.12 Removing indexes used elsewhere....145

Summary....146

7 Administration bad practices....148

7.1 Not tracking disk usage....148

7.1.1 Deleting the Write-Ahead Log....150

7.1.2 What can eat up your disk space?....150

7.1.3 What can you do?....151

7.2 Logging to PGDATA....151

7.3 Ignoring the logs....154

7.3.1 Bad configuration....154

7.3.2 Performance issues....155

7.3.3 Locks....155

7.3.4 Corruption....156

7.3.5 Security....156

7.4 Not monitoring the database....157

7.5 No tracking of statistics over time....159

7.6 Not upgrading Postgres....161

7.7 Not upgrading your system....163

Summary....165

8 Security bad practices....166

8.1 Specifying psql -W or - -password....167

8.2 Setting listen_addresses = '*'....168

8.3 trust-ing in pg_hba.conf....169

8.4 Database owned by a superuser....170

8.5 Setting SECURITY DEFINER carelessly....173

8.6 Choosing an insecure search path....174

Summary....176

9 High availability bad practices....177

9.1 Not taking backups....178

9.2 No Point-in-Time Recovery....180

9.3 Backing up manually....182

9.4 Not testing backups....184

9.5 Not having redundancy....186

9.6 Using no HA tool....188

Summary....190

10 Upgrade/migration bad practices....191

10.1 Not reading all release notes....191

10.2 Performing inadequate testing....195

10.3 Succumbing to encoding chaos....198

10.4 Not using proper BOOLEANs....200

10.5 Mishandling differences in data types....202

Summary....203

11 PostgreSQL, best practices, and you: Final insights....204

11.1 What type of user are you?....204

11.1.1 The dabbler....205

11.1.2 The cautious steward....206

11.1.3 The oblivious coder....206

11.1.4 The freefaller....207

11.2 Be proactive: Act early....208

11.3 All right, so you inherited a bad database....209

11.3.1 “Historical reasons”....209

11.3.2 What now?....209

11.3.3 First things first....210

11.4 Treat Postgres well, and it will treat you well....211

Summary....212

appendix A Frogge Emporium database....215

A.1 Frogge Emporium database schema....215

A.2 Frogge Emporium database data....218

appendix B Cheat sheet....220

index....227

Symbols....227

Numerics....227

A....227

B....227

C....227

D....228

E....228

F....228

G....228

H....228

I....228

J....229

K....229

L....229

M....229

N....229

O....229

P....229

Q....230

R....230

S....230

T....231

U....231

V....231

W....231

X....231

PostgreSQL Mistakes and How to Avoid Them - back....234

The best mistakes to learn from are ones made by other people! In PostgreSQL Mistakes and How To Avoid Them you’ll explore dozens of common PostgreSQL errors so you can easily avoid them in your own projects, learning proactively why certain approaches fail and others succeed.

In PostgreSQL Mistakes and How To Avoid Them you’ll learn how to:

• Avoid configuration and operation issues
• Maximize PostgreSQL utility and performance
• Fix bad SQL practices
• Solve common security and administration issues
• Ensure smooth migration and upgrades
• Diagnose and fix a bad database

About the technology

Fixing mistakes in PostgreSQL databases can be time-consuming and risky—especially when you’re making live changes to an in-use system. Fortunately, you can learn from the mistakes other Postgres pros have already made! This incredibly practical book lays out how to find and avoid the most common, dangerous, and sneaky errors you’ll encounter using PostgreSQL.

About the book

PostgreSQL Mistakes and How To Avoid Them identifies Postgres problems in key areas like data types, features, security, and high availability. For each mistake you’ll find a real-world narrative that illustrates the pattern and provides concrete recommendations for improvement. You’ll especially appreciate the illustrative code snippets, schema samples, mind maps, and tables that show the pros and cons of different approaches.

What's inside

• Diagnose configuration and operation issues
• Fix bad SQL code
• Address security and administration issues
• Ensure smooth migration and upgrades

About the reader

For PostgreSQL database administrators and application developers.