High performance PostgreSQL for rails : reliable, scalable, maintainable, database applications / Andrew Atkinson; edited by Don N. Hagist.

Format:

Book

Author/Creator:

Atkinson, Andrew, author.

Contributor:

Hagist, Don N., editor.

Language:

English

Subjects (All):

Application software--Development.

Application software.

Web applications--Development.

Web applications.

Physical Description:

1 online resource (440 pages)

Edition:

First edition.

Place of Publication:

[Raleigh, North Carolina] : The Pragmatic Programmers, LLC, [2024]

Summary:

High Performance PostgreSQL for Rails by Andrew Atkinson is a comprehensive guide for Rails developers focused on optimizing database performance. The book bridges the knowledge gap between Ruby on Rails and PostgreSQL, equipping developers with best practices to manage data scaling challenges effectively. It offers detailed insights into database management, from schema design and data correctness to advanced techniques for query optimization and database maintenance. The book aims to empower developers to make informed architectural decisions, counter the urge to switch to alternative technologies, and enhance application performance as data demands grow. Targeted at application developers and database administrators, it serves as a valuable resource for improving data handling efficiency in production environments. Generated by AI.

Contents:

Cover

Table of Contents

Preface

Who Is This Book For?

What's Not Covered in This Book?

Ruby on Rails Skills Are in Demand

PostgreSQL Is a Popular Award Winner

Acknowledgments

Part I-Getting Started

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

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

Problem: Slow queries reducing performance

Problem: Queries getting blocked

Problem: High latency for writes

Problem: Application bugs from invalid data

Problem: Excessively high connections

Problem: Schema design choices added latency

Problem: Missing out on good practices

Problem: Scaling beyond a single instance

Problem: Limiting your use of PostgreSQL to OLTP

Problem: Old versions and old practices

A2. Why psql?

Meta-Commands

Setting psql variables

A3. Getting Help

Online Resources

Documentation and Guides

Communities of Practice

News

Jobs

Source Code

DBA Tools

Managing .pgpass files.

PostgreSQL Installation Methods.

Notes:

Description based on publisher supplied metadata and other sources.

Part of the metadata in this record was created by AI, based on the text of the resource.

Description based on print version record.

Description based on print record.

ISBN:

9798888651087

9798888651094

OCLC:

1456763720