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