فهرست مطالب

• Preface
• Acknowledgments
• Part I. Getting Started
• Chapter 1. An App to Get You Started
• What Is Rideshare?
• Active Record Schema Management Refresher
• Exploring Rideshare Dependencies
• Installing Application Dependencies
• Installing PostgreSQL on macOS
• Installing Rideshare
• Configuring PostgreSQL for Rideshare
• Configuring Database Access
• Learning PostgreSQL Terminology
• Learning SQL Terminology
• Ruby on Rails Terminology
• Conventions Used in This Book
• SQL Formatting Conventions
• Ruby and Rails Formatting Conventions
• You’re Ready
• Part II. Design and Build
• Chapter 2. Administration Basics
• Touring psql Features
• Modifying Your PostgreSQL Config File
• Getting Started with Observability
• Glancing at Current Lock Behavior
• Generating Fake Data for Experiments
• Creating Indexes Using SQL
• Rolling Back Schema Modifications
• Exploring and Experimenting Safely in Production
• Chapter 3. Building a Performance-Testing Database
• Generating Bigger Data
• Replacement Values That Are Statistically Similar
• Tracking Columns with Sensitive Information
• Comparing Direct Updates and Clone and Replace
• Starting an Email Scrubber Function
• Implementing the Scrub Email Function
• Understanding Clone and Replace Trade-Offs
• Speeding Up Inserts for Clone and Replace
• Using Direct Updates for Text Replacement
• Performing Database Maintenance
• Performing Updates in Batches
• What’s Next for Your Performance Database
• Chapter 4. Data Correctness and Consistency
• Multiple Column Uniqueness
• Fixing Constraint Violations
• Enforcing Relationships with Foreign Keys
• The Versatile Check Constraint
• Deferring Constraint Checks
• Preventing Overlaps with an Exclusion Constraint
• Creating Active Record Custom Validators
• Significant Casing and Unique Constraints
• Storing Transformations in Generated Columns
• Constraining Values with Database Enums
• Sharing Domains Between Tables
• Automating Consistency Checks in Development
• Part III. Operate and Grow
• Chapter 5. Modifying Busy Databases Without Downtime
• Identifying Dangerous Migrations
• Learning from Unsafe Migrations
• Learning to Use CONCURRENTLY by Default
• Adopting a Migration Safety Check Process
• Exploring Strong Migrations Features
• Locking, Blocking, and Concurrency Refresher
• Prevent Excessive Queueing with a Lock Timeout
• Exploring Lock Type Queues
• Setting a statement_timeout
• Avoiding Schema Cache Errors
• Backfilling Large Tables Without Downtime
• Backfilling and Double Writing
• Separating Reads and Writes for Backfills
• Specialized Tables for Backfills
• Practicing Backfilling Techniques
• Wrapping Up
• Chapter 6. Optimizing Active Record
• Preferring Active Record over SQL
• Query Logs to Connect SQL to App Code
• Common Active Record Problems
• Tooling to Find Problematic Query Patterns
• Use Eager Loading to Reduce Queries
• Eager Loading with .includes()
• Prefer Strict Loading over Lazy Loading
• Optimizing Active Record Queries
• Backgrounding Queries Using load_async
• Save a SELECT by Using RETURNING
• Restricting Queries Using a LIMIT
• Advanced Query Support in Active Record
• Using Common Table Expressions (CTE)
• Introducing Database Views for Rideshare
• Creating the Search Result Model with Scenic
• Improving Performance with Materialized Views
• Reducing Queries with Active Record Caches
• Prepared Statements with Active Record
• Replacing Slow Counts with Counter Caches
• Performing Aggregations in the Database
• Object Allocations in Active Record
• Wrapping Up
• Chapter 7. Improving Query Performance
• Active Support Instrumentation for Queries
• Capture Query Statistics in Your Database
• Using Query Statistics
• Introducing PgHero as a Performance Dashboard
• EXPLAIN Basics
• Reading Query Execution Plans
• Finding Missing Indexes
• Logging Slow Queries
• Automatically Gathering Execution Plans
• Perform Maintenance First
• What Are Index Scans?
• Tricks for Fast COUNT() Queries
• Query Plan Hints
• Using Code and SQL Analysis Tools
• Wrapping Up
• Chapter 8. Optimized Indexes for Fast Retrieval
• Generating Data for Experiments
• Single Column and Multiple Column Indexes
• Understanding Index Column Ordering
• Indexing Boolean Columns
• Filtering Rows with Partial Indexes
• Transform Values with an Expression Index
• Using GIN Indexes with JSON
• Maintaining Unstructured JSON Data
• Using BRIN Indexes
• Hash Indexes over B-Tree?
• Using Indexes for Sorting
• Using Covering Indexes
• Wrapping Up
• Chapter 9. High-Impact Database Maintenance
• Basics of Autovacuum
• Tuning Autovacuum Parameters
• Rebuilding Indexes Without Downtime
• Running Manual Vacuums
• Simulating Bloat and Understanding Impact
• Removing Unused Indexes
• Pruning Duplicate and Overlapping Indexes
• Removing Indexes on Insert-Only Tables
• Scheduling Jobs Using pg_cron
• Conducting Maintenance Tune-Ups
• Chapter 10. Reaching Greater Concurrency
• Monitoring Database Connections
• Exploring Current Activity
• Managing Idle Connections
• Setting Active Record Pool Size
• Running Out of Connections
• Working with PgBouncer
• Choosing Your PgBouncer Pooling Mode
• Identifying Connection Errors and Problems
• More Lock Monitoring with pg_locks
• Monitoring Row Locks
• Finding Lock Conflicts
• Using PgBadger for Lock Analysis
• Active Record Optimistic Locking
• Using Advisory Locks
• Lock Up on Your Way Out
• Part IV. Optimize and Scale
• Chapter 11. Scalability of Common Features
• Analyzing Schema Designs from Gems
• Understanding Queries from Tagging Gem
• LIMIT and OFFSET Pagination
• Database CURSOR Pagination
• Improved Performance with Keyset Pagination
• Wrapping Up
• Chapter 12. Working with Bulk Data
• Creating a Bulk Data Generator Rake Task
• Batching with Active Record
• Handling Upsert Violations in Active Record
• Handling Conflicts with ON CONFLICT
• Beyond Active Record with activerecord-import
• Performing SQL Multirow Operations
• Upserts with SQL MERGE
• Working with pg_dump and pg_restore
• Populating Table Data with \COPY
• Creating a File Foreign Data Wrapper (FDW)
• Wrapping Up
• Chapter 13. Scaling with Replication and Sharding
• Categorizing Query Workloads
• Enabling Physical Replication
• Creating a Replication User on the Primary
• Allowing Access for the Replication User
• Configuring the Replica Instance
• Creating the Replication Slot
• Active Record Multiple Databases Background
• Configuring Active Record Multiple Databases
• Multiple Roles with Active Record Models
• Using Automatic Role Switching
• Replication Slots and the Write Ahead Log (WAL)
• Sharding at the Application Level
• Migrating Multiple Database Schemas
• Using Horizontal Sharding for Multitenancy
• Using Subdomain-Based Routing
• Switching Shards Automatically
• Simulating Joins Across Databases
• Creating a Replica Using Logical Replication
• Customizing Replication Database Parameters
• Wrapping Up
• Chapter 14. Boosting Performance with Partitioning
• Structure of Partitioned Tables
• Ruby on Rails Partitioning Support
• Choose Declarative Partitioning
• Deciding When to Partition
• Estimating Growth of Time-Oriented Data
• Use Partitioning to Help with Archiving
• Choosing Your Partition Column
• Range Partitioning with pgslice
• Data Migration Preparation for Rideshare
• Online Data Migration
• Row Copying Operational Tips
• Partitioning Gotcha: Primary Key Definition
• Partitioning Gotcha: Logical Replication Replica Identity
• Automate Partition Creation and Monitoring
• Retiring Unneeded Partitions
• Use LIST Partitioning for Known Divisions
• Use HASH Partitioning for a Fixed Amount of Buckets
• Performance Benefits from Partitioning
• Let’s Split
• Part V. Advanced Usages
• Chapter 15. Advanced Uses and What’s Next
• Why You Shouldn’t Operate a Database Zoo
• Why You Should Just Use PostgreSQL
• Basic Analytics with PostgreSQL
• Pattern Match Searching
• Implementing Full-Text Search (FTS)
• Fuzzy Searching with tsvector
• Expanding FTS with Extensions
• Optimizing FTS with Specialized Indexes
• Using Trigrams with FTS
• Expanding FTS with Mixed Accents and Collations
• Storing and Searching Vector Embeddings
• Session Persistence and Rails Cache Without Redis
• Background Jobs Without Sidekiq
• Using Change Data Capture (CDC) and wal2json
• Zero Downtime Cutovers and Upgrades
• Closing Remarks
• A1. The Administrator’s Field Guide
• A2. Why psql?
• A3. Getting Help