The Art of PostgreSQL

457 Pages·2021·1.72 MB·English

by  Dimitri Fontaine

Contents
I Preface xi
About… xiii
1 AbouttheBook . . . . . . . . . . . . . . . . . . . . . . . . . xiii
2 AbouttheAuthor . . . . . . . . . . . . . . . . . . . . . . . . xiv
3 Acknowledgements . . . . . . . . . . . . . . . . . . . . . . . xiv
4 Abouttheorganisationofthebooks . . . . . . . . . . . . . . . xv
II Introduction 1
1 Structured Query Language 2
1.1 SomeoftheCodeisWritteninSQL . . . . . . . . . . . . . . . 3
1.2 AFirstUseCase . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.3 LoadingtheDataSet . . . . . . . . . . . . . . . . . . . . . . . 4
1.4 ApplicationCodeandSQL . . . . . . . . . . . . . . . . . . . 5
1.5 AWordaboutSQLInjection . . . . . . . . . . . . . . . . . . 9
1.6 PostgreSQLprotocol: server-sidepreparedstatements . . . . . 10
1.7 BacktoDiscoveringSQL . . . . . . . . . . . . . . . . . . . . 12
1.8 ComputingWeeklyChanges . . . . . . . . . . . . . . . . . . . 15
2 Software Architecture 18
2.1 WhyPostgreSQL? . . . . . . . . . . . . . . . . . . . . . . . . 20
2.2 ThePostgreSQLDocumentation . . . . . . . . . . . . . . . . 22
3 Getting Ready to read this Book 23
j
Contents iii
III Writing Sql Queries 25
4 Business Logic 27
4.1 EverySQLqueryembedssomebusinesslogic . . . . . . . . . . 27
4.2 BusinessLogicAppliestoUseCases . . . . . . . . . . . . . . . 29
4.3 Correctness . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
4.4 E(cid:3000)(cid:3001)(cid:2989)ciency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
4.5 StoredProcedures—aDataAccessAPI . . . . . . . . . . . . . 36
4.6 ProceduralCodeandStoredProcedures . . . . . . . . . . . . . 38
4.7 WheretoImplementBusinessLogic? . . . . . . . . . . . . . . 39
5 A Small Application 41
5.1 ReadmeFirstDrivenDevelopment . . . . . . . . . . . . . . . 41
5.2 LoadingtheDataset . . . . . . . . . . . . . . . . . . . . . . . 42
5.3 ChinookDatabase . . . . . . . . . . . . . . . . . . . . . . . . 43
5.4 MusicCatalog . . . . . . . . . . . . . . . . . . . . . . . . . . 45
5.5 AlbumsbyArtist . . . . . . . . . . . . . . . . . . . . . . . . . 46
5.6 Top-NArtistsbyGenre . . . . . . . . . . . . . . . . . . . . . 46
6 The SQL REPL — An Interactive Setup 52
6.1 Introtopsql . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
6.2 ThepsqlrcSetup . . . . . . . . . . . . . . . . . . . . . . . . . 53
6.3 TransactionsandpsqlBehavior . . . . . . . . . . . . . . . . . 54
6.4 AReportingTool . . . . . . . . . . . . . . . . . . . . . . . . 56
6.5 DiscoveringaSchema . . . . . . . . . . . . . . . . . . . . . . 57
6.6 InteractiveQueryEditor . . . . . . . . . . . . . . . . . . . . . 58
7 SQL is Code 60
7.1 SQLstyleguidelines . . . . . . . . . . . . . . . . . . . . . . . 60
7.2 Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
7.3 UnitTests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
7.4 RegressionTests . . . . . . . . . . . . . . . . . . . . . . . . . 68
7.5 ACloserLook . . . . . . . . . . . . . . . . . . . . . . . . . . 69
8 Indexing Strategy 71
8.1 IndexingforConstraints . . . . . . . . . . . . . . . . . . . . . 72
8.2 IndexingforQueries . . . . . . . . . . . . . . . . . . . . . . . 73
8.3 CostofIndexMaintenance . . . . . . . . . . . . . . . . . . . 74
8.4 ChoosingQueriestoOptimize . . . . . . . . . . . . . . . . . 74
j
Contents iv
8.5 PostgreSQLIndexAccessMethods . . . . . . . . . . . . . . . 74
8.6 AdvancedIndexing . . . . . . . . . . . . . . . . . . . . . . . . 77
8.7 AddingIndexes . . . . . . . . . . . . . . . . . . . . . . . . . . 77
9 An Interview with Yohann Gabory 81
IV SQL Toolbox 86
10 Get Some Data 88
11 Structured Query Language 89
12 Queries, DML, DDL, TCL, DCL 91
13 Select, From, Where 93
13.1 AnatomyofaSelectStatement . . . . . . . . . . . . . . . . . . 93
13.2 Projection(output): Select . . . . . . . . . . . . . . . . . . . . 93
13.3 Datasources: From . . . . . . . . . . . . . . . . . . . . . . . . 100
13.4 UnderstandingJoins . . . . . . . . . . . . . . . . . . . . . . . 101
13.5 Restrictions: Where . . . . . . . . . . . . . . . . . . . . . . . 102
14 Order By, Limit, No Offset 105
14.1 OrderingwithOrderBy . . . . . . . . . . . . . . . . . . . . . 105
14.2 kNNOrderingandGiSTindexes . . . . . . . . . . . . . . . . 107
14.3 Top-Nsorts: Limit . . . . . . . . . . . . . . . . . . . . . . . . 109
14.4 NoO(cid:3000)fset,andhowtoimplementpagination . . . . . . . . . . 111
15 Group By, Having, With, Union All 114
15.1 Aggregates(akaMap/Reduce): GroupBy . . . . . . . . . . . . 114
15.2 AggregatesWithoutaGroupBy . . . . . . . . . . . . . . . . . 117
15.3 RestrictSelectedGroups: Having . . . . . . . . . . . . . . . . 118
15.4 GroupingSets . . . . . . . . . . . . . . . . . . . . . . . . . . 119
15.5 CommonTableExpressions: With . . . . . . . . . . . . . . . 122
15.6 DistinctOn . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
15.7 ResultSetsOperations . . . . . . . . . . . . . . . . . . . . . . 127
16 Understanding Nulls 131
16.1 Three-ValuedLogic . . . . . . . . . . . . . . . . . . . . . . . 131
j
Contents v
16.2 NotNullConstraints . . . . . . . . . . . . . . . . . . . . . . 133
16.3 OuterJoinsIntroducingNulls . . . . . . . . . . . . . . . . . . 134
16.4 UsingNullinApplications . . . . . . . . . . . . . . . . . . . 135
17 Understanding Window Functions 137
17.1 WindowsandFrames . . . . . . . . . . . . . . . . . . . . . . 137
17.2 PartitioningintoDi(cid:3000)ferentFrames . . . . . . . . . . . . . . . . 139
17.3 AvailableWindowFunctions . . . . . . . . . . . . . . . . . . 140
17.4 WhentoUseWindowFunctions . . . . . . . . . . . . . . . . 142
18 Understanding Relations and Joins 143
18.1 Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
18.2 SQLJoinTypes . . . . . . . . . . . . . . . . . . . . . . . . . . 145
19 An Interview with Markus Winand 148
V Data Types 152
20 Serialization and Deserialization 154
21 Some Relational Theory 156
21.1 AttributeValues,DataDomainsandDataTypes . . . . . . . . 157
21.2 ConsistencyandDataTypeBehavior . . . . . . . . . . . . . . 158
22 PostgreSQL Data Types 162
22.1 Boolean . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
22.2 CharacterandText . . . . . . . . . . . . . . . . . . . . . . . . 165
22.3 ServerEncodingandClientEncoding . . . . . . . . . . . . . . 169
22.4 Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
22.5 FloatingPointNumbers . . . . . . . . . . . . . . . . . . . . . 174
22.6 SequencesandtheSerialPseudoDataType . . . . . . . . . . . 174
22.7 UniversallyUniqueIdenti(cid:3001)(cid:2989)er: UUID . . . . . . . . . . . . . . 175
22.8 ByteaandBitstring . . . . . . . . . . . . . . . . . . . . . . . . 177
22.9 Date/TimeandTimeZones . . . . . . . . . . . . . . . . . . . 177
22.10 TimeIntervals . . . . . . . . . . . . . . . . . . . . . . . . . . 181
22.11 Date/TimeProcessingandQuerying . . . . . . . . . . . . . . 182
22.12 NetworkAddressTypes . . . . . . . . . . . . . . . . . . . . . 187
22.13 Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
j
Contents vi
23 Denormalized Data Types 193
23.1 Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
23.2 CompositeTypes . . . . . . . . . . . . . . . . . . . . . . . . . 199
23.3 XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
23.4 JSON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
23.5 Enum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
24 PostgreSQL Extensions 206
25 An interview with Grégoire Hubert 208
VI Data Modeling 211
26 Object Relational Mapping 213
27 Tooling for Database Modeling 215
27.1 HowtoWriteaDatabaseModel . . . . . . . . . . . . . . . . . 216
27.2 GeneratingRandomData . . . . . . . . . . . . . . . . . . . . 219
27.3 ModelingExample . . . . . . . . . . . . . . . . . . . . . . . . 221
28 Normalization 227
28.1 DataStructuresandAlgorithms . . . . . . . . . . . . . . . . . 227
28.2 NormalForms . . . . . . . . . . . . . . . . . . . . . . . . . . 230
28.3 DatabaseAnomalies . . . . . . . . . . . . . . . . . . . . . . . 231
28.4 ModelinganAddressField . . . . . . . . . . . . . . . . . . . . 232
28.5 PrimaryKeys . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
28.6 SurrogateKeys . . . . . . . . . . . . . . . . . . . . . . . . . . 235
28.7 ForeignKeysConstraints . . . . . . . . . . . . . . . . . . . . . 237
28.8 NotNullConstraints . . . . . . . . . . . . . . . . . . . . . . 238
28.9 CheckConstraintsandDomains . . . . . . . . . . . . . . . . . 238
28.10 ExclusionConstraints . . . . . . . . . . . . . . . . . . . . . . 239
29 Practical Use Case: Geonames 240
29.1 Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
29.2 Countries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
29.3 AdministrativeZoning . . . . . . . . . . . . . . . . . . . . . . 248
29.4 GeolocationData . . . . . . . . . . . . . . . . . . . . . . . . . 251
29.5 GeolocationGiSTIndexing . . . . . . . . . . . . . . . . . . . 254
j
Contents vii
29.6 ASamplingofCountries . . . . . . . . . . . . . . . . . . . . . 256
30 Modelization Anti-Patterns 258
30.1 EntityAttributeValues . . . . . . . . . . . . . . . . . . . . . . 258
30.2 MultipleValuesperColumn . . . . . . . . . . . . . . . . . . . 261
30.3 UUIDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
31 Denormalization 265
31.1 PrematureOptimization . . . . . . . . . . . . . . . . . . . . . 266
31.2 FunctionalDependencyTrade-O(cid:3000)fs . . . . . . . . . . . . . . . 266
31.3 DenormalizationwithPostgreSQL . . . . . . . . . . . . . . . 267
31.4 MaterializedViews . . . . . . . . . . . . . . . . . . . . . . . . 268
31.5 HistoryTablesandAuditTrails . . . . . . . . . . . . . . . . . 270
31.6 ValidityPeriodasaRange . . . . . . . . . . . . . . . . . . . . 272
31.7 Pre-ComputedValues . . . . . . . . . . . . . . . . . . . . . . 273
31.8 EnumeratedTypes . . . . . . . . . . . . . . . . . . . . . . . . 273
31.9 MultipleValuesperAttribute . . . . . . . . . . . . . . . . . . 274
31.10 TheSpareMatrixModel . . . . . . . . . . . . . . . . . . . . . 274
31.11 Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
31.12 OtherDenormalizationTools . . . . . . . . . . . . . . . . . . 276
31.13 DenormalizewihCare . . . . . . . . . . . . . . . . . . . . . . 276
32 Not Only SQL 278
32.1 SchemalessDesigninPostgreSQL . . . . . . . . . . . . . . . . 279
32.2 DurabilityTrade-O(cid:3000)fs . . . . . . . . . . . . . . . . . . . . . . 282
32.3 ScalingOut . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
33 An interview with Álvaro Hernández Tortosa 286
VII Data Manipulation and Concurrency Control 291
34 Another Small Application 293
35 Insert, Update, Delete 297
35.1 InsertInto . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
35.2 InsertInto…Select . . . . . . . . . . . . . . . . . . . . . . . . 298
35.3 Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
35.4 InsertingSomeTweets . . . . . . . . . . . . . . . . . . . . . . 303
j
Contents viii
35.5 Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
35.6 TuplesandRows . . . . . . . . . . . . . . . . . . . . . . . . . 307
35.7 DeletingAlltheRows: Truncate . . . . . . . . . . . . . . . . 307
35.8 DeletebutKeepaFewRows . . . . . . . . . . . . . . . . . . . 308
36 Isolation and Locking 309
36.1 TransactionsandIsolation . . . . . . . . . . . . . . . . . . . . 310
36.2 AboutSSI . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
36.3 ConcurrentUpdatesandIsolation . . . . . . . . . . . . . . . . 312
36.4 ModelingforConcurrency . . . . . . . . . . . . . . . . . . . . 314
36.5 PuttingConcurrencytotheTest . . . . . . . . . . . . . . . . . 315
37 Computing and Caching in SQL 319
37.1 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
37.2 MaterializedViews . . . . . . . . . . . . . . . . . . . . . . . . 321
38 Triggers 324
38.1 TransactionalEventDrivenProcessing . . . . . . . . . . . . . 325
38.2 TriggerandCountersAnti-Pattern . . . . . . . . . . . . . . . 327
38.3 FixingtheBehavior . . . . . . . . . . . . . . . . . . . . . . . . 328
38.4 EventTriggers . . . . . . . . . . . . . . . . . . . . . . . . . . 330
39 Listen and Notify 332
39.1 PostgreSQLNoti(cid:3001)(cid:2989)cations . . . . . . . . . . . . . . . . . . . . 332
39.2 PostgreSQLEventPublicationSystem . . . . . . . . . . . . . 333
39.3 Noti(cid:3001)(cid:2989)cationsandCacheMaintenance . . . . . . . . . . . . . . 335
39.4 LimitationsofListenandNotify . . . . . . . . . . . . . . . . 340
39.5 ListenandNotifySupportinDrivers . . . . . . . . . . . . . . 340
40 Batch Update, MoMA Collection 342
40.1 UpdatingtheData . . . . . . . . . . . . . . . . . . . . . . . . 343
40.2 ConcurrencyPatterns . . . . . . . . . . . . . . . . . . . . . . 345
40.3 OnCon(cid:3002)(cid:3006)ictDoNothing . . . . . . . . . . . . . . . . . . . . . 346
41 An Interview with Kris Jenkins 348
j
Contents ix
VIII PostgreSQL Extensions 352
42 What’s a PostgreSQL Extension? 354
42.1 InsidePostgreSQLExtensions . . . . . . . . . . . . . . . . . . 356
42.2 InstallingandUsingPostgreSQLExtensions . . . . . . . . . . 357
42.3 FindingPostgreSQLExtensions . . . . . . . . . . . . . . . . . 358
42.4 APrimeronAuthoringPostgreSQLExtensions . . . . . . . . 359
42.5 AShortListofNoteworthyExtensions . . . . . . . . . . . . . 359
43 Auditing Changes with hstore 365
43.1 Introductiontohstore . . . . . . . . . . . . . . . . . . . . . . 365
43.2 Comparinghstores . . . . . . . . . . . . . . . . . . . . . . . . 366
43.3 AuditingChangeswithaTrigger . . . . . . . . . . . . . . . . 366
43.4 TestingtheAuditTrigger . . . . . . . . . . . . . . . . . . . . 368
43.5 FromhstoreBacktoaRegularRecord . . . . . . . . . . . . . 370
44 Last.fm Million Song Dataset 372
45 Using Trigrams For Typos 378
45.1 Thepg_trgmPostgreSQLExtension . . . . . . . . . . . . . . . 378
45.2 Trigrams,SimilarityandSearches . . . . . . . . . . . . . . . . 379
45.3 CompleteandSuggestSongTitles . . . . . . . . . . . . . . . . 383
45.4 TrigramIndexing . . . . . . . . . . . . . . . . . . . . . . . . . 384
46 Denormalizing Tags with intarray 386
46.1 AdvancedTagIndexing . . . . . . . . . . . . . . . . . . . . . 386
46.2 Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
46.3 User-De(cid:3001)(cid:2989)nedTagsMadeEasy . . . . . . . . . . . . . . . . . . 390
47 The Most Popular Pub Names 392
47.1 APubNamesDatabase . . . . . . . . . . . . . . . . . . . . . 392
47.2 NormalizingtheData . . . . . . . . . . . . . . . . . . . . . . 394
47.3 GeolocatingtheNearestPub(k-NNsearch) . . . . . . . . . . . 395
47.4 IndexingkNNSearch . . . . . . . . . . . . . . . . . . . . . . 396
48 How far is the nearest pub? 398
48.1 TheearthdistancePostgreSQLcontrib . . . . . . . . . . . . 398
48.2 PubsandCities . . . . . . . . . . . . . . . . . . . . . . . . . . 399
48.3 TheMostPopularPubNamesbyCity . . . . . . . . . . . . . 402
j
Contents x
49 Geolocation with PostgreSQL 405
49.1 GeolocationDataLoading . . . . . . . . . . . . . . . . . . . . 405
49.2 FindinganIPAddressintheRanges . . . . . . . . . . . . . . 409
49.3 GeolocationMetadata . . . . . . . . . . . . . . . . . . . . . . 410
49.4 EmergencyPub . . . . . . . . . . . . . . . . . . . . . . . . . . 411
50 Counting Distinct Users with HyperLogLog 413
50.1 HyperLogLog . . . . . . . . . . . . . . . . . . . . . . . . . . 413
50.2 Installingpostgresql-hll . . . . . . . . . . . . . . . . . . . . . 414
50.3 CountingUniqueTweetVisitors . . . . . . . . . . . . . . . . 415
50.4 LossyUniqueCountwithHLL . . . . . . . . . . . . . . . . . 418
50.5 GettingtheVisitsintoUniqueCounts . . . . . . . . . . . . . 419
50.6 SchedulingEstimatesComputations . . . . . . . . . . . . . . . 422
50.7 CombiningUniqueVisitors . . . . . . . . . . . . . . . . . . . 424
51 An Interview with Craig Kerstiens 425
IX Closing Thoughts 428
X Index 430