Publish Sign in
Expertise·Databases

PostgreSQL Patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Quick reference for common patterns, index types, data types, and anti-pattern detection. Based on…

You say
Buy it · $59 Read it before you buy $59 Written by affaan-m · unverified publisher
Context cost
1.1k tokensestimated from the bundle, loaded when it triggers
Bundle
1 file · 4.2 kBtext throughout, nothing executable
Licence
MITpaid listing
Last change
—no release on file
Servers it uses
Noneruns standalone

What it does

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Quick reference for common patterns, index types, data types, and anti-pattern detection. Based on Supabase best practices.

Installed, it changes the agent in these ways.

What this skill changes about the agent is not written down here yet. The listing was collected from its source, and the description is in its own SKILL.md.

Expertise

Domain judgement the base model does not have.

databasepostgresql
Filed under

Databases

The skill itself

This is the whole product. A skill is instructions the model reads, so there is nothing behind the listing you cannot see first — the front matter loads with every session, and the body below it loads when the skill triggers.

SKILL.md4.2 kB · 162 lines
--- name: postgres-patterns description: > PostgreSQL database patterns for query optimization, schema design, indexing, and security. Quick reference for common patterns, index types, data types, and anti-pattern detection. Based on Supabase best practices. metadata: origin: ECC credit: Supabase team (MIT License) ---
12# PostgreSQL Patterns
13
14Quick reference for PostgreSQL best practices. For detailed guidance, use the database-reviewer agent.
15
16## When to Activate
17
18- Writing SQL queries or migrations
19- Designing database schemas
20- Troubleshooting slow queries
21- Implementing Row Level Security
22- Setting up connection pooling
23
24## Quick Reference
25
26### Index Cheat Sheet
27
28| Query Pattern | Index Type | Example |
29|--------------|------------|---------|
30| WHERE col = value | B-tree (default) | CREATE INDEX idx ON t (col) |
31| WHERE col > value | B-tree | CREATE INDEX idx ON t (col) |
32| WHERE a = x AND b > y | Composite | CREATE INDEX idx ON t (a, b) |
33| WHERE jsonb @> '{}' | GIN | CREATE INDEX idx ON t USING gin (col) |
34| WHERE tsv @@ query | GIN | CREATE INDEX idx ON t USING gin (col) |
35| Time-series ranges | BRIN | CREATE INDEX idx ON t USING brin (col) |
36
37### Data Type Quick Reference
38
39| Use Case | Correct Type | Avoid |
40|----------|-------------|-------|
41| IDs | bigint | int, random UUID |
42| Strings | text | varchar(255) |
43| Timestamps | timestamptz | timestamp |
44| Money | numeric(10,2) | float |
45| Flags | boolean | varchar, int |
46
47### Common Patterns
48
49**Composite Index Order:**
50```sql
51-- Equality columns first, then range columns
52CREATE INDEX idx ON orders (status, created_at);
53-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
54```
55
56**Covering Index:**
57```sql
58CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
59-- Avoids table lookup for SELECT email, name, created_at
60```
61
62**Partial Index:**
63```sql
64CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
65-- Smaller index, only includes active users
66```
67
68**RLS Policy (Optimized):**
69```sql
70CREATE POLICY policy ON orders
71 USING ((SELECT auth.uid()) = user_id); -- Wrap in SELECT!
72```
73
74**UPSERT:**
75```sql
76INSERT INTO settings (user_id, key, value)
77VALUES (123, 'theme', 'dark')
78ON CONFLICT (user_id, key)
79DO UPDATE SET value = EXCLUDED.value;
80```
81
82**Cursor Pagination:**
83```sql
84SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
85-- O(1) vs OFFSET which is O(n)
86```
87
88**Queue Processing:**
89```sql
90UPDATE jobs SET status = 'processing'
91WHERE id = (
92 SELECT id FROM jobs WHERE status = 'pending'
93 ORDER BY created_at LIMIT 1
94 FOR UPDATE SKIP LOCKED
95) RETURNING *;
96```
97
98### Anti-Pattern Detection
99
100```sql
101-- Find unindexed foreign keys
102SELECT conrelid::regclass, a.attname
103FROM pg_constraint c
104JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
105WHERE c.contype = 'f'
106 AND NOT EXISTS (
107 SELECT 1 FROM pg_index i
108 WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
109 );
110
111-- Find slow queries
112SELECT query, mean_exec_time, calls
113FROM pg_stat_statements
114WHERE mean_exec_time > 100
115ORDER BY mean_exec_time DESC;
116
117-- Check table bloat
118SELECT relname, n_dead_tup, last_vacuum
119FROM pg_stat_user_tables
120WHERE n_dead_tup > 1000
121ORDER BY n_dead_tup DESC;
122```
123
124### Configuration Template
125
126```sql
127-- Connection limits (adjust for RAM)
128ALTER SYSTEM SET max_connections = 100;
129ALTER SYSTEM SET work_mem = '8MB';
130
131-- Timeouts
132ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
133ALTER SYSTEM SET statement_timeout = '30s';
134
135-- Monitoring
136CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
137
138-- Security defaults
139REVOKE ALL ON SCHEMA public FROM public;
140
141SELECT pg_reload_conf();
142```
143
144## Related
145
146- Agent: database-reviewer - Full database review workflow
147- Skill: backend-patterns - API and backend patterns
148- Skill: database-migrations - Safe schema changes
149
150## When to Use This Skill
151
152- Writing SQL queries
153- Designing database schemas
154- Optimizing query performance
155- Implementing Row Level Security
156- Troubleshooting database issues
157- Setting up PostgreSQL configuration
158
159---
160
161*Based on Supabase Agent Skills (credit: Supabase team) (MIT License)*
162
In the file
SKILL.md642 words
Files1
LicenceMIT
Why you can read it

Nothing in a skill executes. The client loads the text and the model follows it, so a skill can be audited the way a runbook is — by reading it.

What it costs in context

Skills are not billed by the call. They are paid for in context: every token the instructions occupy is a token your code, your diff and your conversation cannot use. Here is what this one takes and when it takes it.

≈80
always loaded
The name and description, so the model knows the skill exists and when to reach for it.
970
on trigger
The instruction body, read only when the skill fires.
0.53%
of a 200k window
Ten skills this size would take about 5% of the window before you open a file.
050k100k150k200k context window

1.1k tokens, estimated from the bundle at four bytes to the token, held for the rest of the session once it triggers. Small enough to keep loaded permanently without thinking about it.

Servers bill, skills cost

A server charges by the month. A skill charges once per session, in context, and then keeps charging it for as long as the session lives.

Before and after

The same question, put to the same model twice: once as it comes, and once with these instructions loaded.

No worked example has been published for this skill yet.

Adoption
Installsnone yet
Ratingno reviews yet

The procedure it runs

The procedure has not been published here. It is in the skill’s own SKILL.md, which its author has not sent to the marketplace yet.

Prose, not code

These steps are written for a model to follow, not executed by a runtime. It can still be told to skip one, and it will say so when it does.

Servers it uses

None. This skill calls no MCP servers at all.

Everything it needs is in the instructions, so it works in a project with nothing connected — the model reads the file and changes how it works with what it can already reach.

It writes no files and reaches no network. All it changes is how the model reasons and writes.

What it asks for
Writes filesno
Network accessno

Read from the allowed-tools line of this skill’s own SKILL.md. A skill grants no permissions of its own — it can only ask for tools your client already has.

What it will not do

Every skill is narrow, and the useful ones say where they stop. These are the jobs this one is the wrong tool for.

What this skill is not for has not been published here. Nothing is implied by that: it is a section the author has not filled in.

What is in the bundle

1 file, 4.2 kB on disk. A bundle is text throughout: the instructions the model reads, plus the templates it fills in.

  • SKILL.md4.2 kB
What is not in it

No dependencies and nothing executable: a skill is text the agent reads, so the bundle is 1 file you can review in full before installing. The MIT licence covers the templates and examples as well as the instructions.

Install

Installing copies the bundle into your project. Nothing runs at install time — the files sit on disk until the model reads them.

$59 once
PostgreSQL Patterns · MIT · affaan-m
one-time
Price$59 once
LicenceMIT — the author’s, unchanged by this purchase
Paid throughStripe, once, on the card you add at the checkout
Keeps workingfor good — the files are yours once they are on disk
Updatesevery update its author ships, delivered through this account

You can read the whole bundle before paying — the SKILL.md above is the product, not a preview of it. What the money buys is the delivery: the folder packaged and handed to your machine by key, every update its author ships, and our support if it does not do what this listing says. The terms of use are MIT, set by the author and unchanged by buying it here.

Sign in to buyCreate an account

Payment runs through Stripe, on a page like this one rather than a redirect. Once there is an account it joins the same mcprush invoice as everything else you run, so there is never a second card to enter.

Which clients pick it up on their own

A skill is a folder of text. A client with a skills folder reads it without being told; everywhere else the same text works, it is just handed to the model rather than found.

Claude Code.claude/skills/
Claude Desktop
ChatGPT
Cursor.cursor/skills/
VS Code.github/skills/
Codex CLI.agents/skills/
Gemini CLI.gemini/skills/
Grok.grok/skills/
Zed.agents/skills/
Windsurf.windsurf/skills/
Agent SDK.claude/skills/
HTTP / API
This release
Versionnot versioned
Publishedno release date on file
Price$59
Referenceaffaan-m/postgresql-patterns

Versions

Its author publishes no version number, so there is nothing here to pin to: what you install is the folder as it stands today. Instructions change more often than APIs do — a skill can be rewritten entirely without anything it depends on moving.

v
  • No earlier releases have been published to the marketplace.
Pinning

Nothing to pin to: this skill carries no version number of its own. What you install is what the folder holds on the day you install it.

Reviews

—no reviews yet · no installs yet

Nobody has reviewed this skill yet. The rating is the mean of the reviews written here, so there is none until somebody writes the first.

Who can post

Only accounts that have had the skill installed for fourteen days, so a review is written after living with it rather than after reading it. Publishers may reply once.

Who wrote it

AF
affaan-m

Publishes on mcprush.

0 servers listed55 skills listednot claimed
Profile
Publisher
Servers0