- Артикул:00-01120956
- Автор: Dimitri Fontaine
- Обложка: Мягкая обложка
- Страниц: 455
- Вес: 1137 г
Развернуть ▼
Издание на английском языке
This book is a practical guide to working with PostgreSQL, covering the fundamentals of the SQL language, system architecture, and efficient data management techniques. It covers in detail the processes of writing and optimizing SQL queries, creating indexes, data modeling, and transaction management. It also discusses PostgreSQL extensions, database modeling tools, and issues of privacy and scalability. The book includes practical case studies, expert interviews, and tips on testing, code style, and proper database management. This is a valuable resource for both beginners and experienced developers looking to improve the quality and efficiency of their PostgreSQL projects.
Content
Preface
About...
1. About the Book
2. About the Author
3. Acknowledgements
4. About the organisation of the book
II. Introduction
1. Structured Query Language
1.1. Some of the Code is Written in SQL
1.2. A First Use Case
1.3. Loading the Data Set
1.4. Application Code and SQL
1.5. A Word about SQL Injection
1.6. PostgreSQL protocol: server-side prepared statements
1.7. Back to Discovering SQL
1.8. Computing Weekly Changes
2. Software Architecture
2.1. Why PostgreSQL?
2.2. The PostgreSQL Documentation
3. Getting Ready to read this Book
III. Writing Sql Queries
4. Business Logic
4.1. Every SQL query embeds some business logic
4.2. Business Logic Applies to Use Cases
4.3. Correctness
4.4. Efficiency
4.5. Stored Procedures - a Data Access API
4.6. Procedural Code and Stored Procedures
4.7. Where to Implement Business Logic?
5. A Small Application
5.1. Readme First Driven Development
5.2. Loading the Dataset
5.3. Chinook Database
5.4. Music Catalog
5.5. Albums by Artist
5.6. Top-N Artists by Genre
6. The SQL REPL - An Interactive Setup
6.1. Intro to psql
6.2. The psqlrc Setup
6.3. Transactions and psql Behavior
6.4. A Reporting Tool
6.5. Discovering a Schema
6.6. Interactive Query Editor
7. SQL is Code
7.1. SQL style guidelines
7.2. Comments
7.3. Unit Tests
7.4. Regression Tests
7.5. A Closer Look
8. Indexing Strategy
8.1. Indexing for Constraints
8.2. Indexing for Queries
8.3. Cost of Index Maintenance
8.4. Choosing Queries to Optimize
8.5. PostgreSQL Index Access Methods
8.6. Advanced Indexing
8.7. Adding Indexes
9. An Interview with Yohann Gabory
IV. SQL Toolbox
10. Get Some Data
11. Structured Query Language
12. Queries, DML, DDL, TCL, DCL
13. Select, From, Where
13.1. Anatomy of a Select Statement
13.2. Projection (output): Select
13.3. Data sources: From
13.4. Understanding Joins
13.5. Restrictions: Where
14. Order By, Limit, No Offset
14.1. Ordering with Order By
14.2. KNN Ordering and GiST indexes
14.3. Top-N sorts: Limit
14.4. No Offset, and how to implement pagination
15. Group By, Having, With, Union All
15.1. Aggregates (aka Map/Reduce): Group By
15.2. Aggregates Without a Group By
15.3. Restrict Selected Groups: Having
15.4. Grouping Sets
15.5. Common Table Expressions: With
15.6. Distinct On
15.7. Result Sets Operations
16. Understanding Nulls
17. Understanding Window Functions
18. Understanding Relations and Joins
19. An Interview with Markus Winand
V. Data Types
20. Serialization and Deserialization
21. Some Relational Theory
22. PostgreSQL Data Types
23. Denormalized Data Types
24. PostgreSQL Extensions
25. An interview with Gregoire Hubert
VI. Data Modeling
26. Object Relational Mapping
27. Tooling for Database Modeling
28. Normalization
29. Practical Use Case: Geonames
30. Modelization Anti-Patterns
31. Denormalization
32. Not Only SQL
33. An interview with Alvaro Hernandez Tortosa
VII. Data Manipulation and Concurrency Control
34. Another Small Application
35. Insert, Update, Delete
36. Isolation and Locking
37. Computing and Caching in SQL
38. Triggers
39. Listen and Notify
40. Batch Update, MoMA Collection
41. An Interview with Kris Jenkins
VIII. PostgreSQL Extensions
42. What's a PostgreSQL Extension?
43. Auditing Changes with hstore
44. Last.fm Million Song Dataset
45. Using Trigrams For Typos
46. Denormalizing Tags with intarray
47. The Most Popular Pub Names
48. How far is the nearest pub?
49. Geolocation with PostgreSQL
50. Counting Distinct Users with HyperLogLog
51. An Interview with Craig Kerstiens
IX. Closing Thoughts
X. Index