postgres-optimization

name	postgres-optimization
description	Unconventional PostgreSQL optimization techniques
license	MIT
tier	2
allowed-tools	["read_file","write_file","run_terminal_cmd","grep"]
related	["postgres","debugging","plan-then-execute","robust-first"]
tags	["moollm","database","postgres","performance","optimization","indexing"]
inputs	{"query":{"type":"string","required":false,"description":"Query to optimize"},"table":{"type":"string","required":false,"description":"Table to analyze"}}
outputs	["OPTIMIZATION.md","EXPLAIN-ANALYSIS.txt"]
credits	{"source":{"title":"Unconventional PostgreSQL Optimizations","author":"Haki Benita","url":"https://hakibenita.com/postgres-unconventional-optimizations"}}

🐘 PostgreSQL Optimization

"Beyond 'just add an index' — creative solutions for real performance problems."

Unconventional optimization techniques for PostgreSQL that go beyond standard DBA playbooks.

Purpose

When conventional approaches fall short — query rewrites, adding indexes, VACUUM, ANALYZE — these techniques offer creative solutions:

• Eliminate impossible query scans with constraint exclusion
• Reduce index size with function-based indexes
• Enforce uniqueness with hash indexes instead of B-Trees

When to Use

• Ad-hoc query environments where users make mistakes
• Large indexes approaching table size
• Uniqueness constraints on large text values (URLs, documents)
• Timestamp columns queried at coarser granularity

---

Technique 1: Constraint Exclusion

The Problem

Check constraints prevent invalid data, but PostgreSQL doesn't use them to optimize queries by default.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username TEXT NOT NULL,
    plan TEXT NOT NULL,
    CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))
);

An analyst writes:

SELECT * FROM users WHERE plan = 'Pro';  -- Note: capital P

Despite the check constraint making this condition impossible, PostgreSQL scans the entire table.

The Solution

SET constraint_exclusion TO 'on';

With constraint exclusion enabled:

EXPLAIN ANALYZE SELECT * FROM users  plan  ;