Mastering PostgreSQL in Application Development Learn to use SQL to easily solve complex problems! Dimitri Fontaine September 2017 © 2017, Dimitri Fontaine, All rights reserved. Mastering PostgreSQL in Application Development 1. Mastering PostgreSQL in Application Development 2. 1 Preface 1. 1.1 About the Book 2. 1.2 About the Author 3. 1.3 Acknowledgements 3. 2 Introduction 1. 2.1 Some of the Code is Written in SQL 2. 2.2 A First Use Case 3. 2.3 Software Architecture 4. 2.4 Getting Ready to read this Book 4. 3 Writing Sql Queries 1. 3.1 Business Logic 2. 3.2 A Small Application 3. 3.3 The SQL REPL — An Interactive Setup 4. 3.4 SQL is Code 5. 3.5 Indexing Strategy 6. 3.6 An Interview with Yohann Gabory 5. 4 SQL Toolbox 1. 4.1 Get Some Data 2. 4.2 Structured Query Language 3. 4.3 Queries, DML, DDL, TCL, DCL 4. 4.4 Select, From, Where 5. 4.5 Order By, Limit, No Offset 6. 4.6 Group By, Having, With, Union All 7. 4.7 Understanding Nulls 8. 4.8 Understanding Window Functions 9. 4.9 Understanding Relations and Joins 10. 4.10 An Interview with Markus Winand 6. 5 Data Types 1. 5.1 Serialization and Deserialization 2. 5.2 Some Relational Theory 3. 5.3 PostgreSQL Data Types 4. 5.4 Denormalized Data Types 5. 5.5 PostgreSQL Extensions 6. 5.6 An interview with Grégoire Hubert 7. 6 Data Modeling 1. 6.1 Object Relational Mapping 2. 6.2 Tooling for Database Modeling 3. 6.3 Normalization 4. 6.4 Practical Use Case: Geonames 5. 6.5 Modelization Anti-Patterns 6. 6.6 Denormalization 7. 6.7 Not Only SQL 8. 6.8 An interview with Álvaro Hernández Tortosa 8. 7 Data Manipulation and Concurrency Control 1. 7.1 Another Small Application 2. 7.2 Insert, Update, Delete 3. 7.3 Isolation and Locking 4. 7.4 Computing and Caching in SQL 5. 7.5 Triggers 6. 7.6 Listen and Notify 7. 7.7 Batch Update, MoMA Collection 8. 7.8 An Interview with Kris Jenkins 9. 8 Closing Thoughts Mastering PostgreSQL in Application Development 1 Preface As a developer, Mastering PostgreSQL in Application Development is the book you need to read in order to get to the next level of proficiency. After all, a developer’s job encompasses more than just writing code. Our job is to produce results, and for that we have many tools at our disposal. SQL is one of them, and this book teaches you all about it. PostgreSQL is used to manage data in a centralized fashion, and SQL is used to get exactly the result set needed from the application code. An SQL result set is generally used to fill in-memory data structures so that the application can then process the data. So, let's open this book with a quote about data structures and application code: Data dominates. If you've chosen the right data structures and organized things well, the algorithms will almost always be self- evident. Data structures, not algorithms, are central to programming. — Rob Pike 1.1 About the Book This book is intended for developers working on applications that use a database server. The book specifically addresses the PostgreSQL RDBMS: it actually is the world's most advanced Open Source database, just like it says in the tagline on the official website. By the end of this book you’ll know why, and you’ll agree! I wanted to write this book after having worked with many customers who were making use of only a fraction of what SQL and PostgreSQL are capable of delivering. In most cases, developers I met with didn't know what’s possible to achieve in SQL. As soon as they realized — or more exactly, as soon as they were shown what's possible to achieve —, replacing hundreds of lines of application code with a small and efficient SQL query, then in some cases they would nonetheless not know how to integrate a raw SQL query in their code base. To integrate a SQL query and think about SQL as code, we need to solve what is already solved when using other programming languages: versioning, automated testing, code reviewing, and deployment. Really, this is more about the developer's workflow than the SQL code itself… In this book, you will learn best practices that help with integrating SQL into your own workflow, and through the many examples provided, you’ll see all the reasons why you might be interested in doing more in SQL. Primarily, it means writing fewer lines of code. As Dijkstra said, we should count lines of code as lines spent, so by learning how to use SQL you will be able to spend less to write the same application! The practice is pervaded by the reassuring illusion that programs are just devices like any others, the only difference admitted being that their manufacture might require a new type of craftsmen, viz. programmers. From there it is only a small step to measuring “programmer productivity” in terms of “number of lines of code produced per month”. This is a very costly measuring unit because it encourages the writing of insipid code, but today I am less interested in how foolish a unit it is from even a pure business point of view. My point today is that, if we wish to count lines of code, we should not regard them as "lines produced" but as “lines spent”: the current conventional wisdom is so foolish as to book that count on the wrong side of the ledger. On the cruelty of really teaching computing science, Edsger Wybe Dijkstra, EWD1036 1.2 About the Author Dimitri Fontaine is a PostgreSQL Major Contributor, and has been using and contributing to Open Source Software for the better part of the last twenty years. Dimitri is also the author of the pgloader data loading utility, with fully automated support for database migration from MySQL to PostgreSQL, or from SQLite, or MS SQL…and more. Dimitri has taken on roles such as developer, maintainer, packager, release manager, software architect, database architect, and database administrator at different points in his career. In the same period of time, Dimitri also started several companies (which are still thriving) with a strong Open Source business model, and he has held management positions as well, including working at the executive level in large companies. Dimitri runs a blog at http://tapoueh.org with in-depth articles showing advanced use cases for SQL and PostgreSQL. 1.3 Acknowledgements First of all, I'd like to thank all the contributors to the book. I know they all had other priorities in life, yet they found enough time to contribute and help make this book as good as I could ever hope for, maybe even better! I'd like to give special thanks to my friend Julien Danjou who's acted as a mentor over the course of writing of the book. His advice about every part of the process has been of great value — maybe the one piece of advice that I most took to the heart has been “write the book you wanted to read”. I’d also like to extend my thanks to the people interviewed for this book. In order of appearance, they are Yohann Gabory from the French book “Django Avancé”, Markus Winand from http://use-the-index-luke.com and http://modern-sql.com, Grégoire Hubert author of the PHP POMM project, Álvaro Hernández Tortosa who created ToroDB, bringing