: PragProg

More SQL Antipatterns

Avoid Common but Deadly Mistakes of Database Optimization

by: Bill Karwin

Published	2026-03-10
Internal code	bksap2
Print status	In Print
Pages	220
User level
Keywords
Related titles
ISBN	9798888652060
Other ISBN	Channel epub: 9798888652077 Channel PDF: 9798888652077 Kindle: 9798888652077 Safari: 9798888652077 Kindle: 9798888652077
BISACs

https://media.pragprog.com/covers/bksap2.jpg

Highlight

Most of us taught ourselves SQL; we learn what works on the job. This book (and its companion) contain years of accumulated experience of what works and, more importantly, what doesn’t. This volume focusses on scaling and performance. See why the solutions you find online are often unhelpful (or just plain wrong). Learn how the database handles complex queries so you can better predict what works and what doesn’t. See when to denormalize for performance, and how to do it right. Learn when to use JSON columns. Along the way, discover a bunch of SQL tricks you never knew existed.

Description

It’s easy to get something working with SQL but getting it working well requires skill and experience. Rather than learning by trial and error, let renowned database expert Bill Karwin give you the benefit of his years of experience as he shows you the mistakes people make and how to fix them.

Dig into SQL language issues rarely covered in other books. Learn advanced queries such as relational division, efficient pagination, and pivoting. Explore tradeoffs in optimization, including denormalization, JSON, and choosing SQL versus NoSQL. Turn mistakes into successes by employing SQL in application design: transactions, polling, and object-relational mapping. Finally, take a deep dive into scaling out with sharded databases.

This book contains fourteen all-new topics, making it a valuable complement to SQL Antipatterns Volume 1. In each chapter, you’ll learn how to recognize one type of common pitfall, and how to solve it. Examples use the current popular combination of PostgreSQL and Python, but the principles apply to any modern database and language. Like the first volume, this book also includes a selection of mini-antipatterns, or brief tips you can use for quick wins.

Optimize SQL the right way and graduate from journeyman to senior database developer.

Contents and Extracts

Table of Contents

SQL Language Antipatterns
- Fear of JOINs
  - Objective: Combining Matching Data in Two Tables
  - Antipattern: Fearing JOINs
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Preferring JOINs
  - Mini-Antipattern: Comma-Style Join Syntax
  - Mini-Antipattern: NATURAL JOIN
- Relational Division
  - Objective: Matching a List of Values
  - Antipattern: Searching Multiple Rows in a WHERE Clause
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Using Relational Division
  - Mini-Antipattern: Aggregation Functions in the WHERE Clause
- Dynamic Pivot
  - Objective: Turning the Table
  - Antipattern: Using a Single Query
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Using Code to Assist
  - Mini-Antipattern: Operand Must Contain One Column
- Pagination: Take It from the Top
  - Objective: Presenting Query Results in Pages
  - Antipattern: Mistaking Offset for Optimization
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Changing the Rules
  - Mini-Antipattern: LIMIT without ORDER BY
Optimization Antipatterns
- Non-Sargable Queries
  - Objective: Optimizing Complex Queries
  - Antipattern: Using Queries That Can’t Be Optimized
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Refactoring the Query
  - Mini-Antipattern: Redundant Indexes
- Premature Denormalization
  - Objective: Breaking the Rules to Improve Performance
  - Antipattern: Breaking the Rules Recklessly
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Breaking the Rules Carefully
  - Mini-Antipattern: Query Snipers
- NoSQL: The Grass Is Always Greener on the Other Side
  - Objective: Relief from the Challenges of SQL
  - Antipattern: Switching to NoSQL Is Easy
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Match Your Queries to Your NoSQL Models
  - Mini-Antipattern: N+1 Queries
- JSON: Matryoshka Dolls
  - Objective: Unlimited Flexibility
  - Antipattern: Semi-Structured Data in a Relational Database
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Be Mindful and Moderate About Flexibility
  - Mini-Antipattern: Quotes for All Occasions
- Cacheless Transactions
  - Objective: A Magic Trick to Improve Performance
  - Antipattern: The Performance Vanishing Act
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Use Sleight of Hand
  - Mini-Antipattern: Query Caching
Application Design Antipatterns
- Polling: Are We There Yet?
  - Objective: Notify of Changes to Data
  - Antipattern: Polling for Changes
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Just Wake Me Up When We Get There
  - Mini-Antipattern: Enqueuing Before Committing
- Transaction Encapsulation
  - Objective: Transactions for Multiple Models
  - Antipattern: Every Model Class Manages Its Transactions
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Simplify, Simplify
  - Mini-Antipattern: ORMs that Pluralize Table Names
- Fear of Deadlocks
  - Objective: Don’t See Deadlock Errors
  - Antipattern: Trying to Prevent All Deadlocks
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: Mitigate Deadlocks
  - Mini-Antipattern: Sharing Connections Between Threads
- Data Hoarding
  - Objective: Cope with Data Bloat
  - Antipattern: The Belief That You Need All Data
  - How to Recognize the Antipattern
  - Legitimate Uses of the Antipattern
  - Solution: An Intervention to Manage Data Retention