Mini Map
Search
⌘ K
Log In
The Art of PostgreSQL
0%
1.
Part I: Preface
Introduction to the Course
Course Overview
Architecture Required
2.
Introduction to PostgreSQL
PostgreSQL
Some Useful Commands
Quiz: Introduction to PostgreSQL
3.
Part II: Introduction to Structured Query Language
Introduction to SQL
Utilizing SQL
The First Use Case
A Word About SQL Injection
PostgreSQL Protocol: Server-Side Prepared Statements
Back to Discovering SQL
Computing Weekly Changes
SQL Style Guidelines
Challenge: Compute Fortnightly Changes
Solution Review: Compute Fortnightly Changes
4.
Part III: Writing SQL Queries
Introduction to Writing SQL Queries
5.
Business Logic
Business Logic in the Database
Business Logic in Application Code
Correctness
Efficiency
Stored Procedures: A Data Access API
Quiz: SQL and Business Logic
Challenge: Implement Business Logic
Solution Review: Implement Business Logic
6.
A Small Application
Introduction to a Small Application
Loading and Querying the Database
Python Application for Chinook Database
Challenge: Top-N Factbook Entries
Solution Review: Top-N Factbook Entries
7.
The SQL REPL: An Interactive Setup
Introduction to SQL REPL
Getting Started with psql
Query Rewriting and Validating
Challenge: Testing in PostgreSQL
Solution Review: Testing in PostgreSQL
Regression Testing
Challenge: Regression Testing in PostgreSQL
Solution Review: Regression Testing in PostgreSQL
8.
Indexing in PostgreSQL
Indexing Strategy
Index Access Methods
Advanced Indexing
Quiz: Loading Database, REPL, Testing, and Indexing
9.
Part IV: SQL Toolbox
Introduction to SQL Toolbox
10.
SQL Tools
Sub-languages of SQL
Anatomy of a Select Statement
Processing Function and Data Sources
Joins and Restrictions
11.
Managing Output in SQL
Ordering with the Order By
Top-N Sorts: Limit and Pagination
Aggregates
Restrict Selected Groups: Having
Grouping Sets
Common Table Expressions
Result Sets Operations
Challenge: Managing the Output in SQL
Solution Review: Managing the Output in SQL
12.
Understanding Nulls
Three-Valued Logic
Null in Applications
Outer Joins Introduce Nulls
Quiz: Queries, Output of Queries, and Nulls in SQL
Challenge: Understanding Nulls
Solution Review: Understanding Nulls
13.
Window Functions
Windows and Frames
Partitioning into Different Frames
Using Window Functions
14.
Relations and Joins
Understanding Relations
SQL Join Types
Quiz: Window Functions, Relations, and Joins
15.
Part V: Data Types
Introduction to Data Types
16.
Serialization and Deserialization
Serialization in SQL
Some Relational Theory
Attribute Values, Data Domains, and Data Types
Consistency and Data Type Behavior
Challenge: Generate Specific Numbers
Solution Review: Generate Specific Numbers
17.
PostgreSQL Data Types 101
Introduction to PostgreSQL Data Types
Boolean
Character and Text
Server Encoding and Client Encoding
Numbers
Sequences and the Serial Pseudo Data Type
UUID, Bytea, and Bitstring
Date/Time and Time Zones
Time Intervals
Date/Time Processing and Querying
Network Address Types
Ranges
18.
Denormalized Data Types
Introduction to Denormalized Data Types
Arrays
GIN Indexing
Composite Types
XML
JSON
Enum
Challenge: JSON Data Type
Solution Review: JSON Data Type
19.
Part VI: Data Modeling
Introduction to Data Modeling
20.
Object Relational Mapping and Tooling for Database Modeling
Object Relational Mapping
Tooling for Database Modeling
Writing a Database Model
Modeling Example
Quiz: Data Types and Data Modeling
21.
Normalization
Data Structures and Algorithms
Normal Forms and Anomalies
Normalization Example
Primary and Surrogate Keys
Constraints
22.
Practical Use Case: Geonames
Loading the GeoNames
Features
Countries
Administrative Zoning
Geolocation
Indexing and Lookup
A Sampling of Countries
Challenge: Create a Histogram
Solution Review: Create a Histogram
23.
Modelization Anti-Patterns
Entity Attribute Values
Multiple Values per Column
UUIDs
Quiz: Normalization, GeoNames Database, and Anti-Patterns
24.
Denormalization
Introduction to Denormalization
Premature Optimization and Functional Dependency Trade-Offs
Denormalization with PostgreSQL
Materialized Views
History Tables and Audit Trails
More About Denormalization
Partitioning
Denormalize with Care
Challenge: Generating Series
Solution Review: Generating Series
25.
Not Only SQL
Introduction to Not Only SQL
Schemaless Design in PostgreSQL
Durability Trade-Offs
Scaling Out
Quiz: Denormalization and NOSQL
26.
Part VII: Data Manipulation and Concurrency Control
Introduction to Data Manipulation
27.
Another Small Application
Introduction to Data Manipulation and Concurrency Control
Data Manipulation and Concurrency Control: Insert
Data Manipulation and Concurrency Control: Update
Data Manipulation and Concurrency Control: Delete
Challenge: Data Manipulation
Solution Review: Data Manipulation
28.
Isolation and Locking
Introduction to Isolation and Locking
Transactions and Isolation
Concurrent Updates and Isolation
Modeling for Concurrency
Putting Concurrency to the Test
29.
Computing and Caching in SQL
Introduction to Computing and Caching in SQL
Views
Materialized Views
Quiz: Data Manipulation, Concurrency, Isolation, and Caching
Challenge: Creating a View
Solution Review: Creating a View
30.
Triggers
Introduction to Triggers
Transactional Event-Driven Processing
Trigger and Counters Anti-Pattern
Fixing the Behavior
31.
Listen and Notify
Introduction to Listen and Notify
PostgreSQL Event Publication System
Notifications and Cache Maintenance
Limitations and Support of Listen and Notify
32.
Batch Update, MoMA Collection
Loading the Data
Updating the Data
Concurrency Patterns and Conflict
Quiz: Triggers, Notifications Feature, and Batch Update
33.
Part VIII: PostgreSQL Extensions
Introduction to PostgreSQL Extensions
34.
What’s a PostgreSQL Extension?
PostgreSQL Extensions
Inside PostgreSQL Extensions
Installing and Using PostgreSQL Extensions
35.
Auditing Changes with hstore
Introduction to hstore
Auditing Changes with a Trigger
Testing the Audit Trigger
From hstore Back to a Regular Record
Quiz: Extensions and Auditing Changes
36.
Million Song Dataset
Loading the Data
Discover the Data Model
37.
Using Trigrams for Typos
The pg_trgm Extension
Trigrams, Similarity, and Searches
Complete and Suggest Song Titles
Trigram Indexing
Challenge: Find the Words Similarity Score
Solution Review: Find the Words Similarity Score
38.
Denormalizing Tags with intarray
Advanced Tag Indexing
Searches and User-Defined Tags
Quiz: Trigrams and Denormalizing Tags
39.
The Most Popular Pub Names
Loading the Data
Geolocating the Nearest Pub
Finding the Nearest Pub
Pubs and Cities
The Most Popular Pub Names
40.
Geolocation with PostgreSQL
Geolocation Data Loading
Finding an IP Address in the Ranges
Geolocation Metadata
Emergency Pub
Challenge: Search for the Nearest Circuits
Solution Review: Search for the Nearest Circuits
41.
Counting Distinct Users with HyperLogLog
Introduction to HyperLogLog
Counting Unique Tweet Visitors
Lossy Unique Count with hll
Getting the Visits into Unique Counts
Scheduling Estimates Computations
Quiz: Geolocation and HyperLogLog
42.
Appendix
PostgreSQL: Setup on the Local Machine
The PostgreSQL extension: How to Find It
A Short List of Noteworthy Extensions
Home
Courses
The Art of PostgreSQL
Quiz: Denormalization and NOSQL
Test your knowledge of denormalization and Not Only SQL.
We'll cover the following...
...