SQL at Scale- Performance and Design

Master Database Design, PostgreSQL Performance, and Production Engineering

Writing SQL queries is one thing. Understanding how databases execute those queries, manage concurrent users, protect information, and remain reliable under production workloads is another.

SQL at Scale: Performance and Design takes you beyond everyday SQL and into the engineering principles behind professional relational database systems. This book focuses on PostgreSQL, database architecture, query optimization, data integrity, and the operational techniques needed to design and maintain databases in demanding environments.

Designed for developers and data practitioners who already understand SQL fundamentals, it does not assume previous experience with PostgreSQL internals or performance tuning.

• Understand the differences between embedded databases and client/server database architecture.
• Move from SQLite concepts to PostgreSQL and production database systems.
• Design relational schemas and apply database normalization.
• Create entity-relationship models and translate them into relational structures.
• Work with advanced PostgreSQL data types, including arrays, JSON, ranges, and full-text search vectors.
• Understand indexes and develop effective indexing strategies.
• Read and interpret query execution plans using EXPLAIN and EXPLAIN ANALYZE.
• Optimize queries and investigate database performance problems.
• Understand transactions, concurrency, locking, and isolation.
• Use stored procedures and triggers to enforce database logic.
• Implement database security and access control.
• Work with JSON and semi-structured data.
• Manage large datasets through partitioning and archiving.
• Monitor PostgreSQL and understand VACUUM, ANALYZE, backups, and recovery.
• Explore connection management and connection pooling with PgBouncer.

The book continues the Elmridge Medical Center project, now viewed as a growing organization with demanding database workloads, concurrent connections, performance challenges, and evolving data requirements.

Through realistic engineering scenarios, you will examine slow queries, indexing decisions, lock contention, database security, maintenance operations, and architectural trade-offs. The final project brings the concepts together in a complete enterprise-oriented PostgreSQL schema.

• SQL developers ready to deepen their database engineering skills.
• Backend developers working with relational databases.
• Data engineers and database practitioners.
• PostgreSQL learners interested in performance and architecture.
• Developers preparing to work with larger, production-oriented database systems.

A reliable database is not simply a collection of tables and SQL statements. It requires sound data modeling, carefully designed constraints, appropriate indexing, efficient query execution, secure access, and disciplined administration.

Learn to understand the database beneath the query - and develop the engineering judgment required to build systems that remain correct, secure, and efficient at scale.

SQL at Scale: Performance and Design is the third book in the SQL Foundations Series by Adam Wysocki.