cyberangles guide

PostgreSQL for Developers: Writing Efficient SQL Queries

As a developer, you’ve likely experienced the frustration of a slow application—even when the codebase is well-architected and the backend is optimized. More often than not, the culprit lies in inefficient SQL queries. PostgreSQL, a powerful open-source relational database, is renowned for its scalability and flexibility, but it’s not a magic bullet. Poorly written queries can cripple performance, leading to slow response times, increased resource usage, and unhappy users. This blog is your guide to mastering efficient SQL in PostgreSQL. We’ll dive deep into how PostgreSQL processes queries, tools to analyze performance, and actionable strategies to optimize everything from simple SELECTs to complex joins. Whether you’re building a small app or scaling a enterprise system, these techniques will help you write SQL that’s fast, scalable, and maintainable.

Table of Contents

  1. Understanding PostgreSQL Query Execution
    1.1 How PostgreSQL Processes a Query
    1.2 The Role of the Query Planner
  2. Using EXPLAIN and EXPLAIN ANALYZE
    2.1 Basics of EXPLAIN
    2.2 Interpreting EXPLAIN Output
    2.3 Practical Example: Analyzing a Slow Query
  3. Indexing Strategies for Faster Queries
    3.1 Common Index Types in PostgreSQL
    3.2 When to Use (and Avoid) Indexes
    3.3 Functional and Covering Indexes
    3.4 Index Maintenance
  4. Crafting Efficient Query Structures
    4.1 Select Only What You Need: Avoid SELECT *
    4.2 Optimize WHERE Clauses
    4.3 Joins: Choose the Right Type and Order
    4.4 Subqueries vs. CTEs: When to Use Each
    4.5 Handle NULLs Effectively
  5. Aggregation and Grouping Best Practices
    5.1 Efficient GROUP BY Queries
    5.2 Window Functions for Advanced Analysis
  6. Data Types and Constraints: The Foundation
    6.1 Choose the Right Data Type
    6.2 Leverage Constraints for Better Plans
  7. Batch Operations and Bulk Data Handling
    7.1 Use COPY for Bulk Inserts
    7.2 Batch Updates and Deletes
  8. Connection and Transaction Management
    8.1 Keep Transactions Short
    8.2 Use Prepared Statements
    8.3 Connection Pooling
  9. Advanced Optimization Techniques
    9.1 Materialized Views for Read-Heavy Workloads
    9.2 Partition Large Tables
    9.3 Identify Slow Queries with pg_stat_statements
  10. Common Pitfalls to Avoid
  11. Conclusion
  12. References

1. Understanding PostgreSQL Query Execution

Before optimizing queries, it’s critical to understand how PostgreSQL processes them. This knowledge will help you anticipate bottlenecks and write queries the database can execute efficiently.

1.1 How PostgreSQL Processes a Query

When you run a SQL query, PostgreSQL follows a multi-step pipeline:

  1. Parsing: The input SQL is validated for syntax and converted into an abstract syntax tree (AST).
  2. Analysis/Rewriting: The AST is checked for semantic validity (e.g., table/column existence) and rewritten (e.g., expanding views into base tables).
  3. Planning: The query planner generates multiple execution plans and selects the one with the lowest estimated cost (based on statistics like row counts and data distribution).
  4. Execution: The chosen plan is executed, and results are returned.

1.2 The Role of the Query Planner

The query planner is PostgreSQL’s “brain.” It uses statistics (stored in pg_statistic) to estimate costs (e.g., I/O, CPU) for each potential plan. For example, it might choose between a sequential scan (scanning all rows) and an index scan (using an index to fetch rows) based on how many rows it expects to return.

Outdated or missing statistics can lead the planner to pick suboptimal plans. Always run ANALYZE after bulk data changes to update statistics!

2. Using EXPLAIN and EXPLAIN ANALYZE

To optimize queries, you first need to see how PostgreSQL executes them. The EXPLAIN command is your most powerful tool here.

2.1 Basics of EXPLAIN

EXPLAIN shows the execution plan the planner would use for a query, including:

  • Operations (e.g., sequential scan, index scan, nested loop join).
  • Estimated row counts and costs.
  • Order of operations.

Example:

EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';

Output might look like:

Index Scan using users_email_idx on users  (cost=0.15..8.17 rows=1 width=42)
  Index Cond: (email = '[email protected]'::text)

2.2 Interpreting EXPLAIN Output

Key metrics to focus on:

  • Cost: Shown as (cost=startup_cost..total_cost rows=estimated_rows width=row_width). Lower total cost is better.
  • Rows: Estimated rows vs. actual rows (use EXPLAIN ANALYZE to get actuals). A large discrepancy suggests outdated stats.
  • Scan Types:
    • Seq Scan: Scans the entire table (slow for large tables).
    • Index Scan: Uses an index to find rows (fast if few rows are fetched).
    • Bitmap Index Scan: Combines index and sequential scan (good for medium row counts).

2.3 Practical Example: Analyzing a Slow Query

Suppose you have a slow query:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND total > 1000;

Run EXPLAIN ANALYZE to see why:

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date >= '2023-01-01' AND total > 1000;

If the output shows a Seq Scan with rows=100000, the planner estimates 100k rows—too many for an index scan. Adding an index on (order_date, total) might help.

3. Indexing Strategies for Faster Queries

Indexes are like “database cheat codes”—they speed up read queries by allowing PostgreSQL to skip scanning entire tables. But not all indexes are created equal!

3.1 Common Index Types in PostgreSQL

Index TypeUse CaseExample
B-treeEquality (=) and range (<, >, BETWEEN) queries.CREATE INDEX idx_users_email ON users(email);
GINArrays, JSONB, or full-text search.CREATE INDEX idx_products_tags ON products USING GIN(tags);
GiSTGeospatial data (PostGIS), or custom data types.CREATE INDEX idx_locations_geo ON locations USING GiST(geo_point);
BRINLarge tables with ordered data (e.g., timestamps).CREATE INDEX idx_orders_date ON orders USING BRIN(order_date);

3.2 When to Use (and Avoid) Indexes

Use indexes for:

  • Columns in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
  • Tables with frequent reads and infrequent writes.

Avoid over-indexing:

  • Indexes slow down INSERT, UPDATE, and DELETE (each write must update indexes).
  • Small tables (sequential scans are faster than index lookups).
  • Columns with low cardinality (e.g., a status column with 2-3 values).

3.3 Functional and Covering Indexes

  • Functional Indexes: Index expressions, not just columns. Use when queries apply functions to columns.
    Example:

    -- Instead of: WHERE LOWER(email) = '[email protected]' (can’t use email index)
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

  • Covering Indexes: Include all columns needed by the query, avoiding table lookups.
    Example:

    -- Query: SELECT id, name FROM users WHERE email = '...'
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (id, name);

3.4 Index Maintenance

Over time, indexes can become fragmented (e.g., after deletes/updates). Use:

  • REINDEX: Rebuilds an index (locks the table; use CONCURRENTLY to avoid downtime): 
    REINDEX INDEX CONCURRENTLY idx_users_email;
  • pg_stat_user_indexes: Monitor index usage to drop unused indexes: 
    SELECT schemaname, relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0; -- Unused indexes

4. Crafting Efficient Query Structures

Even with good indexes, poorly structured queries can be slow. Let’s break down best practices.

4.1 Select Only What You Need: Avoid SELECT *

SELECT * fetches all columns, including large ones (e.g., TEXT, JSONB), increasing I/O and memory usage. Instead, explicitly list columns:

Bad:

SELECT * FROM products WHERE category = 'electronics';

Good:

SELECT id, name, price FROM products WHERE category = 'electronics';

4.2 Optimize WHERE Clauses

  • Avoid functions on indexed columns: This prevents index usage.
    Bad: WHERE EXTRACT(YEAR FROM order_date) = 2023
    Good: WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'

  • Use IN for multiple values, but limit size: IN (1,2,3) is fine; IN (1..10000) may be slow. Use = ANY(ARRAY[...]) for large lists.

  • Avoid OR; use UNION instead:
    Bad: WHERE status = 'active' OR created_at > '2023-01-01'
    Good:

    SELECT * FROM users WHERE status = 'active'
UNION ALL
SELECT * FROM users WHERE created_at > '2023-01-01';

4.3 Joins: Choose the Right Type and Order

  • Join Types:

    • INNER JOIN: Only returns matching rows (fastest, as it reduces result size early).
    • LEFT JOIN: Returns all rows from the left table (use only when needed).

  • Join Order: PostgreSQL optimizes join order, but help it by:

    • Joining smaller tables first (reduces rows for subsequent joins).
    • Avoiding CROSS JOIN (cartesian product) unless intentional.

4.4 Subqueries vs. CTEs: When to Use Each

  • Subqueries: Best for simple, single-use logic. PostgreSQL often inlines them into the main query.
    Example:

    SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE country = 'US');

  • CTEs (WITH clauses): Use for readability or when materializing results (e.g., complex calculations).
    Note: In PostgreSQL ≤12, CTEs are always materialized (slower for small datasets). Use MATERIALIZED/NOT MATERIALIZED in PostgreSQL 12+:

    WITH active_users AS MATERIALIZED (
  SELECT id FROM users WHERE status = 'active'
)
SELECT * FROM orders o JOIN active_users u ON o.user_id = u.id;

4.5 Handle NULLs Effectively

NULL behaves differently than other values (e.g., NULL = NULL is NULL, not TRUE). Use:

  • IS NULL/IS NOT NULL instead of = NULL.
  • COALESCE to replace NULL with a default: 
    SELECT name, COALESCE(phone, 'N/A') AS phone FROM users;

5. Aggregation and Grouping Best Practices

5.1 Efficient GROUP BY Queries

  • Limit grouped columns: Only group by necessary columns to reduce work.
  • Filter early with WHERE: Use WHERE to filter rows before grouping (faster than HAVING for pre-aggregation filters).
    Example: 
    -- Bad: Filters after grouping
SELECT category, COUNT(*) FROM products GROUP BY category HAVING price > 100;

-- Good: Filters before grouping
SELECT category, COUNT(*) FROM products WHERE price > 100 GROUP BY category;

5.2 Window Functions for Advanced Analysis

Window functions (e.g., ROW_NUMBER(), RANK()) compute results across a “window” of rows without grouping. They’re often faster than subqueries for ranking/aggregating.

Example: Get the top 3 products per category by sales:

SELECT category, product, sales
FROM (
  SELECT 
    category, 
    product, 
    sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

6. Data Types and Constraints: The Foundation

6.1 Choose the Right Data Type

Using the correct data type reduces storage, speeds up queries, and avoids errors:

  • Text vs. VARCHAR: Use TEXT unless you need a length constraint (no performance difference).
  • Numeric vs. Float: NUMERIC for exact values (currency), FLOAT for approximate (scientific data).
  • Date/Time: Use DATE, TIME, or TIMESTAMP instead of VARCHAR (supports indexing and range queries).

6.2 Leverage Constraints for Better Plans

Constraints help the query planner make smarter decisions:

  • NOT NULL: Columns with NOT NULL have better statistics (planner knows no NULLs exist).
  • CHECK: Enforces business rules (e.g., price > 0) and can enable plan optimizations.

7. Batch Operations and Bulk Data Handling

7.1 Use COPY for Bulk Inserts

For loading large datasets (e.g., CSV imports), COPY is 10–100x faster than INSERT because it bypasses transactional overhead.

Example:

COPY users (id, name, email) FROM '/path/to/users.csv' WITH (FORMAT CSV, HEADER);

7.2 Batch Updates and Deletes

Avoid large single UPDATE/DELETE statements—they lock tables and cause long transactions. Batch them:

Example: Delete 10k rows at a time:

DO $$
DECLARE
  deleted_rows INT := 1;
BEGIN
  WHILE deleted_rows > 0 LOOP
    DELETE FROM old_logs WHERE created_at < '2023-01-01' LIMIT 10000;
    GET DIAGNOSTICS deleted_rows = ROW_COUNT;
    COMMIT; -- Release locks between batches
    PERFORM pg_sleep(0.1); -- Avoid overwhelming the DB
  END LOOP;
END $$;

8. Connection and Transaction Management

8.1 Keep Transactions Short

Long transactions hold locks, block other queries, and increase rollback risk. Commit early and avoid unnecessary work in transactions:

Bad:

BEGIN;
-- Do unrelated work (e.g., API calls)
UPDATE users SET status = 'active' WHERE id = 1;
COMMIT; -- Transaction stays open during API calls

Good:

-- Do unrelated work first
BEGIN;
UPDATE users SET status = 'active' WHERE id = 1;
COMMIT; -- Short transaction

8.2 Use Prepared Statements

Prepared statements reduce parsing overhead for repeated queries (e.g., in loops). Most ORMs (e.g., Django, SQLAlchemy) use them automatically, but you can write them manually:

PREPARE update_user_status(int, text) AS
  UPDATE users SET status = $2 WHERE id = $1;

EXECUTE update_user_status(1, 'active');

8.3 Connection Pooling

Each database connection consumes memory. Use connection pooling (e.g., PgBouncer) to reuse connections and limit overhead:

  • Set max_connections in PostgreSQL to a reasonable value (default 100; adjust based on server resources).

9. Advanced Optimization Techniques

9.1 Materialized Views for Read-Heavy Workloads

For complex, read-only queries (e.g., reports), precompute results with materialized views:

CREATE MATERIALIZED VIEW daily_sales_summary AS
  SELECT DATE(order_date), SUM(total) AS sales FROM orders GROUP BY DATE(order_date);

-- Refresh periodically
REFRESH MATERIALIZED VIEW daily_sales_summary;

9.2 Partition Large Tables

Split large tables into smaller “partitions” (e.g., by date, region) to speed up queries. Use PostgreSQL’s built-in partitioning:

-- Partition by range on order_date
CREATE TABLE orders (
  id INT,
  order_date DATE,
  total NUMERIC
) PARTITION BY RANGE (order_date);

-- Create monthly partitions
CREATE TABLE orders_2023_01 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

9.3 Identify Slow Queries with pg_stat_statements

The pg_stat_statements extension tracks query performance:

-- Enable (add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements')
CREATE EXTENSION pg_stat_statements;

-- Find slow queries (longest execution time)
SELECT queryid, query, total_time, calls 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

10. Common Pitfalls to Avoid

  • Overusing DISTINCT: Only use when duplicates exist (adds sorting overhead).
  • Ignoring EXPLAIN: Always analyze plans for slow queries.
  • Using SELECT COUNT(*) on large tables: COUNT(*) is fast (PostgreSQL optimizes it), but COUNT(column) is slow (checks for NULLs).
  • Not indexing foreign keys: Foreign keys should almost always be indexed to speed up joins.

11. Conclusion

Writing efficient SQL in PostgreSQL is a mix of understanding how the database works, using the right tools (like EXPLAIN), and following best practices. By focusing on indexing strategically, optimizing query structure, managing transactions, and leveraging advanced features like partitioning, you can ensure your applications scale smoothly.

Remember: Performance is iterative. Monitor queries with pg_stat_statements, test changes in staging, and always validate optimizations with real-world data.

12. References