بنقرة واحدة
postgres-optimization
Unconventional PostgreSQL optimization techniques
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
القائمة
Unconventional PostgreSQL optimization techniques
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
استنادا إلى تصنيف SOC المهني
Portable tokens of capability, identity, and access
The grammar rules that make MOOLLM's file system object-oriented. Plural directory names declare element type; UPPERCASE marker files declare interface exports (COM-style, minus the UUIDs); directories are implementation classes exporting every interface whose marker file sits at their root.
Mother skill for platform-descriptor sister skills. Defines what a BIOME is — a bounded region of an ecosystem (coexisting, exchanging, never isolated) for one platform you operate — and what files, subdirectories, and cross-biome bridges every daughter biome inherits.
A skill is documentation that learned to do things.
GNU Emacs as a stateful Lisp machine for agents — daemon, moo-* protocol, emacs.py router, emacs:// URLs, spoken grammar, play-learn-lift.
Schemapedia — schema plugins, families, gateways, formats.yml, mechanism_relations; self-object kernel; delegates to sibling skills.
| 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"}} |
"Beyond 'just add an index' — creative solutions for real performance problems."
Unconventional optimization techniques for PostgreSQL that go beyond standard DBA playbooks.
When conventional approaches fall short — query rewrites, adding indexes, VACUUM, ANALYZE — these techniques offer creative solutions:
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.
SET constraint_exclusion TO 'on';
With constraint exclusion enabled:
EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
Execution Time: 0.008 ms
PostgreSQL recognizes the condition contradicts the constraint and skips the scan entirely.
| Environment | Recommendation |
|---|---|
| OLTP production | Leave as 'partition' (default) |
| BI / Data Warehouse | Set to 'on' |
| Ad-hoc query tools | Set to 'on' |
| Reporting databases | Set to 'on' |
You have a sales table with timestamps:
CREATE TABLE sale (
id INT PRIMARY KEY,
sold_at TIMESTAMPTZ NOT NULL,
charged INT NOT NULL
);
Analysts query by day:
SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged)
FROM sale
WHERE sold_at BETWEEN '2025-01-01 UTC' AND '2025-02-01 UTC'
GROUP BY 1;
You add a B-Tree index on sold_at — 214 MB for a 160 MB table. The index is almost half the table size!
Index only what queries need:
CREATE INDEX sale_sold_at_date_ix
ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);
| Index | Size |
|---|---|
sale_sold_at_ix (full timestamp) | 214 MB |
sale_sold_at_date_ix (date only) | 66 MB |
The function-based index is 3x smaller because:
Function-based indexes require exact expression match:
-- Uses the index ✓
WHERE date_trunc('day', sold_at AT TIME ZONE 'UTC')::date
BETWEEN '2025-01-01' AND '2025-01-31'
-- Does NOT use the index ✗
WHERE (sold_at AT TIME ZONE 'UTC')::date
BETWEEN '2025-01-01' AND '2025-01-31'
ALTER TABLE sale ADD sold_at_date DATE
GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC'));
Now queries use the virtual column:
SELECT sold_at_date, SUM(charged)
FROM sale
WHERE sold_at_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;
Benefits:
Limitation: PostgreSQL 18 doesn't support indexes directly on virtual columns (yet).
You have a table with large URLs:
CREATE TABLE urls (
id INT PRIMARY KEY,
url TEXT NOT NULL,
data JSON
);
You add a unique B-Tree index:
CREATE UNIQUE INDEX urls_url_unique_ix ON urls(url);
| Size |
|---|
| Table: 160 MB |
| B-Tree index: 154 MB |
The index is almost as large as the table because B-Tree stores actual values in leaf blocks.
Use an exclusion constraint with a hash index:
ALTER TABLE urls
ADD CONSTRAINT urls_url_unique_hash
EXCLUDE USING HASH (url WITH =);
| Index | Size |
|---|---|
| B-Tree | 154 MB |
| Hash | 32 MB |
The hash index is 5x smaller because it stores hash values, not the actual URLs.
INSERT INTO urls (id, url) VALUES (1000002, 'https://example.com');
-- ERROR: conflicting key value violates exclusion constraint
EXPLAIN ANALYZE SELECT * FROM urls WHERE url = 'https://example.com';
Index Scan using urls_url_unique_hash on urls
Execution Time: 0.022 ms -- Faster than B-Tree's 0.046 ms!
| Feature | B-Tree Unique | Hash Exclusion |
|---|---|---|
| Foreign key reference | ✓ | ✗ |
ON CONFLICT (column) | ✓ | ✗ |
ON CONFLICT ON CONSTRAINT | ✓ | ✓ (DO NOTHING only) |
ON CONFLICT DO UPDATE | ✓ | ✗ |
MERGE | ✓ | ✓ |
Instead of INSERT ... ON CONFLICT DO UPDATE:
MERGE INTO urls t
USING (VALUES (1000004, 'https://example.com')) AS s(id, url)
ON t.url = s.url
WHEN MATCHED THEN UPDATE SET id = s.id
WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.id, s.url);
Check index sizes:
\di+ table_*
Compare index to table size:
SELECT
relname AS name,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname LIKE 'your_table%'
ORDER BY pg_relation_size(oid) DESC;
Check constraint_exclusion setting:
SHOW constraint_exclusion;
Is the query scanning impossibly?
├── Yes → Enable constraint_exclusion
└── No
↓
Is index nearly as large as table?
├── Yes, timestamp column → Function-based index on date
├── Yes, large text column → Hash exclusion constraint
└── No → Standard B-Tree is fine
| Command | Action |
|---|---|
ANALYZE [table] | Analyze query performance |
CHECK-CONSTRAINTS | Evaluate constraint exclusion opportunity |
LOWER-CARDINALITY | Find function-based index opportunities |
HASH-UNIQUE | Evaluate hash index for large values |
COMPARE-INDEXES | Compare index sizes and performance |
| Direction | Skill | Relationship |
|---|---|---|
| ← | debugging | Query debugging leads here |
| → | plan-then-execute | Systematic optimization |