Overview
Appendix A. Five optimization tips
This appendix distills the book’s hands-on spirit into a concise set of optimization habits for real-world Postgres work. It encourages learning by observing how the database actually executes queries, avoiding premature tuning, and building an optimization mindset you refine through experience. The tips revolve around understanding execution plans, using the right indexes in the right measure, reducing connection overhead with pooling, and fetching only the data you truly need—practical steps that can deliver meaningful performance gains without adding undue complexity.
First, master EXPLAIN: compare estimated versus actual results (via analyze) and review memory buffer stats to see whether work stays in RAM or spills to disk; mismatches in estimates often signal stale statistics or suboptimal plans. Postgres 18 surfaces buffer info by default, making this even easier. Second, be deliberate with indexing. Use EXPLAIN to validate the need for an index, fix underindexing that forces full scans (for example, a GIN index can accelerate common jsonb predicates), and avoid overindexing that bloats storage, slows writes, and complicates planning. Aim for a small, well-chosen set of indexes that can serve multiple query patterns.
Third, reduce connection churn with pooling. Because Postgres runs a process per connection, client-side and server-side poolers cut process-creation overhead and let you cap concurrency to stay within CPU and memory budgets. Fourth, query only what you need: replacing SELECT * with explicit columns trims I/O, network transfer, and deserialization costs—especially for wide rows or variable-size types. Note the exception: count(*) is optimized to count rows without reading column data, whereas count(column) does read values. Keep an eye on ORM-generated SQL to ensure it aligns with these principles.
A.5 Use the computational capabilities of the database
A database like Postgres is more than just a simple storage container where you put data in and query it later. It’s a powerful computational platform at your disposal. As we see throughout the book, Postgres can easily sort, group, aggregate, and perform other data manipulations using its built-in functions, common table expressions, window functions, and more. You can even run your own business logic in the database by implementing custom functions, stored procedures, and triggers.
So, study and take advantage of the computational capabilities Postgres offers. If you need to sort a query result, use the ORDER BY clause instead of sorting the fetched data on the application side. If you need to group data based on certain criteria, you likely don’t require custom application logic; the GROUP BY clause will probably do the job. And if you need to perform a compute- or data-intensive business operation that heavily relies on data stored in Postgres, consider implementing a database function (or stored procedure) that runs entirely in the database. The more you can offload to the database, the less you have to handle on the application side, potentially making your logic cleaner and resource utilization across the stack more efficient.
FAQ
What does EXPLAIN (analyze, costs off) show, and why is it used in the book?
It runs the query and shows actual runtimes and row counts (analyze), while hiding estimated costs to keep output compact (costs off). It’s used for readability in the book, not as a universal best practice.How is EXPLAIN different from EXPLAIN ANALYZE, and why compare estimated vs. actual costs?
EXPLAIN shows only estimated plan costs without executing the query. EXPLAIN ANALYZE executes the plan and reports actual timings and rows. Comparing the two helps detect bad estimates (e.g., stale statistics) and guides optimization.When should I include buffers in EXPLAIN output, and what does “Buffers: shared hit=…” mean?
Include buffers (EXPLAIN … buffers on) when investigating long-running queries to see memory vs. disk usage. “shared hit=101” means all needed pages were served from cache (memory). Low hit ratios suggest disk I/O bottlenecks—consider allocating more RAM. Starting with Postgres 18, buffer info is included by default with EXPLAIN ANALYZE.How do I decide whether to create an index?
Always start with EXPLAIN to see the chosen plan and access methods. Don’t guess. If the plan already performs well or uses an existing index effectively, a new index may not be needed.What is underindexing, and how can I spot and fix it?
Underindexing is when queries are slow due to missing indexes. Signs include full table scans (“Seq Scan”) on large tables for frequently filtered columns. Fix by adding an appropriate index (e.g., a GIN index on a jsonb column used in filters), which can switch the plan to “Bitmap Index Scan/Bitmap Heap Scan.”What is overindexing, and why is it harmful?
Overindexing means having too many indexes, causing: - Extra storage and memory usage - Write amplification on inserts/updates/deletes (every index must be maintained) - More planning overhead (more options to consider) Aim for a minimal, purposeful set of indexes that serve multiple common queries.Can one index speed up multiple different filters?
Yes. For example, a single GIN index on a jsonb column can accelerate various predicates on different fields within that JSON (e.g., filtering by type, size, or both), and the planner can reuse the same index.Why should I use connection pooling with Postgres?
Postgres creates a dedicated OS process per client connection. Opening/closing many connections adds overhead. Connection pooling reuses existing connections, reducing overhead and letting you control concurrent query count to keep resource usage in check.What’s the difference between client-side and server-side connection pooling?
Client-side pooling is built into your app’s library/framework and reuses connections for that app. Server-side pooling (e.g., PgBouncer, Pgpool, Odyssey) sits in front of the database and helps when many apps/microservices would otherwise overwhelm Postgres with processes. Managed services often include a server-side pooler.Why avoid SELECT * and what’s the exception with count(*)?
SELECT * can fetch unnecessary columns, increasing database work, network transfer, and application deserialization—especially with variable-size types (text, jsonb, vector). Instead, select only needed columns. Exception: count(*) is optimized—Postgres just counts rows (often via Index Only Scan) without fetching column data; count(column) reads the column values.
Just Use Postgres! ebook for free
