Workflow·Databases·v1.0

Postgresql Indexing

Name: Postgresql Indexing
Author: prowler-cloud

You say

Buy it · $59 Read it before you buy $59 Written by prowler-cloud · unverified publisher

Context cost: 3.6k tokensestimated from the bundle, loaded when it triggers
Bundle: 1 file · 14.3 kBtext throughout, nothing executable
Licence: Apache-2.0paid listing
Last change: —v1.0
Servers it uses: Noneruns standalone

What it does

PostgreSQL indexing best practices for Prowler: index design, partial indexes, partitioned table indexing, EXPLAIN ANALYZE validation, concurrent operations, monitoring, and maintenance. Trigger: When creating or modifying PostgreSQL indexes, analyzing query performance with EXPLAIN, debugging slow queries, reviewing index usage statistics, reindexing, dropping indexes, or working with partitioned table indexes. Also trigger when discussing index strategies, partial indexes, or index maintenance operations like VACUUM or ANALYZE.

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.

Workflow

Runs a procedure end to end.

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.md14.3 kB · 394 lines

--- name: postgresql-indexing description: > PostgreSQL indexing best practices for Prowler: index design, partial indexes, partitioned table indexing, EXPLAIN ANALYZE validation, concurrent operations, monitoring, and maintenance. Trigger: When creating or modifying PostgreSQL indexes, analyzing query performance with EXPLAIN, debugging slow queries, reviewing index usage statistics, reindexing, dropping indexes, or working with partitioned table indexes. Also trigger when discussing index strategies, partial indexes, or index maintenance operations like VACUUM or ANALYZE. license: Apache-2.0 metadata: author: prowler-cloud version: "1.0" scope: [api] auto_invoke: - "Creating or modifying PostgreSQL indexes" - "Analyzing query performance with EXPLAIN" - "Debugging slow queries or missing indexes" - "Dropping or reindexing PostgreSQL indexes" allowed-tools: Read, Grep, Glob, Bash ---

23## When to use

25- Creating or modifying PostgreSQL indexes

26- Analyzing query plans with EXPLAIN

27- Debugging slow queries or missing index usage

28- Dropping, reindexing, or validating indexes

29- Working with indexes on partitioned tables (findings, resource_finding_mappings)

30- Running VACUUM or ANALYZE after index changes

32## Index design

34### Partial indexes: constant columns go in WHERE, not in the key

36When a column has a fixed value for the query (e.g., state = 'completed'), put it in the WHERE clause of the index, not in the indexed columns. Otherwise the planner cannot exploit the ordering of the other columns.

38```sql

39-- Bad: state in the key wastes space and breaks ordering

40CREATE INDEX idx_scans_tenant_state ON scans (tenant_id, state, inserted_at DESC);

42-- Good: state as a filter, planner uses tenant_id + inserted_at ordering

43CREATE INDEX idx_scans_tenant_ins_completed ON scans (tenant_id, inserted_at DESC)

44 WHERE state = 'completed';

45```

47### Column order matters

49Put high-selectivity columns first (columns that filter out the most rows). For composite indexes, the leftmost column must appear in the query's WHERE clause for the index to be used.

51## Validating index effectiveness

53### Always EXPLAIN (ANALYZE, BUFFERS) after adding indexes

55Never assume an index is being used. Run EXPLAIN (ANALYZE, BUFFERS) to confirm.

57```sql

58EXPLAIN (ANALYZE, BUFFERS)

59SELECT *

60FROM users

61WHERE email = 'user@example.com';

62```

64Use [Postgres EXPLAIN Visualizer (pev)](https://tatiyants.com/pev/) to visualize query plans and identify bottlenecks.

66### Force index usage for testing

68The planner may choose a sequential scan on small datasets. Toggle enable_seqscan = off to confirm the index path works, then re-enable it.

70```sql

71SET enable_seqscan = off;

73EXPLAIN (ANALYZE, BUFFERS)

74SELECT DISTINCT ON (provider_id) provider_id

75FROM scans

76WHERE tenant_id = '95383b24-da01-44b5-a713-0d9920d554db'

77 AND state = 'completed'

78ORDER BY provider_id, inserted_at DESC;

80SET enable_seqscan = on; -- always re-enable after testing

81```

83This is for validation only. Never leave enable_seqscan = off in production.

85## Over-indexing

87Every extra index has three costs that compound:

891. **Write overhead.** Every INSERT and UPDATE must maintain all indexes. Extra indexes also kill HOT (Heap-Only-Tuple) updates, which normally skip index maintenance when unindexed columns change.

912. **Planning time.** The planner evaluates more execution paths per index. On simple OLTP queries, planning time can exceed execution time by 4x when index count is high.

933. **Lock contention (fastpath limit).** PostgreSQL uses a fast path for the first 16 locks per backend. After 16 relations (table + its indexes), it falls back to slower LWLock mechanisms. At high QPS (100+), this causes LockManager wait events.

95Rules:

96- Drop unused and redundant indexes regularly

97- Be especially careful with partitioned tables (each partition multiplies the index count)

98- Use prepared statements to reduce planning overhead when index count is high

100## Finding redundant indexes

101

102Two indexes are redundant when:

103- They have the same columns in the same order (duplicates)

104- One is a prefix of the other: index (a) is redundant to (a, b), but NOT to (b, a)

105

106Column order matters. For partial indexes, the WHERE clause must also match.

107

108```sql

109-- Quick check: find indexes that share a leading column on the same table

110SELECT

111 a.indrelid::regclass AS table_name,

112 a.indexrelid::regclass AS index_a,

113 b.indexrelid::regclass AS index_b,

114 pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a,

115 pg_size_pretty(pg_relation_size(b.indexrelid)) AS size_b

116FROM pg_index a

117JOIN pg_index b ON a.indrelid = b.indrelid

118 AND a.indexrelid != b.indexrelid

119 AND a.indkey::text = (

120 SELECT string_agg(x::text, ' ')

121 FROM unnest(b.indkey[:array_length(a.indkey, 1)]) AS x

122 )

123WHERE NOT a.indisunique;

124```

125

126Before dropping: verify on all workload nodes (primary + replicas), use DROP INDEX CONCURRENTLY, and monitor for plan regressions.

127

128## Monitoring index usage

129

130### Identify unused indexes

131

132Query pg_stat_all_indexes to find indexes that are never or rarely scanned:

133

134```sql

135SELECT

136 idxstat.schemaname AS schema_name,

137 idxstat.relname AS table_name,

138 idxstat.indexrelname AS index_name,

139 idxstat.idx_scan AS index_scans_count,

140 idxstat.last_idx_scan AS last_idx_scan_timestamp,

141 pg_size_pretty(pg_relation_size(idxstat.indexrelid)) AS index_size

142FROM pg_stat_all_indexes AS idxstat

143JOIN pg_index i ON idxstat.indexrelid = i.indexrelid

144WHERE idxstat.schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')

145 AND NOT i.indisunique

146ORDER BY idxstat.idx_scan ASC, idxstat.last_idx_scan ASC;

147```

148

149Indexes with idx_scan = 0 and no recent last_idx_scan are candidates for removal.

150

151Before dropping, verify:

152- Stats haven't been reset recently (check stats_reset in pg_stat_database)

153- Stats cover at least 1 month of production traffic

154- All workload nodes (primary + replicas) have been checked

155- The index isn't used by a periodic job that runs infrequently

156

157```sql

158-- Check when stats were last reset

159SELECT stats_reset, age(now(), stats_reset)

160FROM pg_stat_database

161WHERE datname = current_database();

162```

163

164### Monitor index creation progress

165

166Do not assume index creation succeeded. Use pg_stat_progress_create_index (Postgres 12+) to watch progress live:

167

168```sql

169SELECT * FROM pg_stat_progress_create_index;

170```

171

172In psql, use \watch 5 to refresh every 5 seconds for a live dashboard view. CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY have more phases than standard operations: monitor for blocking sessions and wait events.

173

174### Validate index integrity

175

176Check for invalid indexes regularly:

177

178```sql

179SELECT c.relname AS index_name, i.indisvalid

180FROM pg_class c

181JOIN pg_index i ON i.indexrelid = c.oid

182WHERE i.indisvalid = false;

183```

184

185Invalid indexes are ignored by the planner. They waste space and cause inconsistent query performance, especially on partitioned tables where some partitions may have valid indexes and others do not.

186

187## Concurrent operations

188

189### Always use CONCURRENTLY in production

190

191Never create or drop indexes without CONCURRENTLY on live tables. Without it, the operation holds a lock that blocks all writes.

192

193```sql

194-- Create

195CREATE INDEX CONCURRENTLY IF NOT EXISTS index_name ON table_name (column_name);

196

197-- Drop

198DROP INDEX CONCURRENTLY IF EXISTS index_name;

199```

200

201DROP INDEX CONCURRENTLY cannot run inside a transaction block.

202

203### Always use IF NOT EXISTS / IF EXISTS

204

205Makes scripts idempotent. Safe to re-run without errors from duplicate or missing indexes.

206

207### Concurrent indexing can fail silently

208

209CREATE INDEX CONCURRENTLY can fail without raising an error. The result is an invalid index that the planner ignores. This is particularly dangerous on partitioned tables: some partitions get valid indexes, others don't, causing inconsistent query performance.

210

211After any concurrent index creation, always validate:

212

213```sql

214SELECT c.relname, i.indisvalid

215FROM pg_class c

216JOIN pg_index i ON i.indexrelid = c.oid

217WHERE c.relname LIKE '%your_index_name%';

218```

219

220## Reindexing invalid indexes

221

222Rebuild invalid indexes without locking writes:

223

224```sql

225REINDEX INDEX CONCURRENTLY index_name;

226```

227

228### Understanding _ccnew and_ccold artifacts

229

230When CREATE INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY is interrupted, temporary indexes may remain:

231

232| Suffix | Meaning | Action |

233|--------|---------|--------|

234| _ccnew | New index being built, incomplete | Drop it and retry REINDEX CONCURRENTLY |

235| _ccold | Old index being replaced, rebuild succeeded | Safe to drop |

236

237```sql

238-- Example: both original and temp are invalid

239-- users_emails_2019 btree (col) INVALID

240-- users_emails_2019_ccnew btree (col) INVALID

241

242-- Drop the failed new one, then retry

243DROP INDEX CONCURRENTLY IF EXISTS users_emails_2019_ccnew;

244REINDEX INDEX CONCURRENTLY users_emails_2019;

245```

246

247These leftovers clutter the schema, confuse developers, and waste disk space. Clean them up.

248

249## Indexing partitioned tables

250

251### Do NOT use ALTER INDEX ATTACH PARTITION

252

253As stated in PostgreSQL documentation, ALTER INDEX ... ATTACH PARTITION prevents dropping malfunctioning or non-performant indexes from individual partitions. An attached index cannot be dropped by itself and is automatically dropped if its parent index is dropped.

254

255This removes the ability to manage indexes per-partition, which we need for:

256- Dropping broken indexes on specific partitions

257- Skipping indexes on old partitions to save storage

258- Rebuilding indexes on individual partitions without affecting others

259

260### Correct approach: create on partitions, then on parent

261

2621. Create the index on each child partition concurrently:

263

264```sql

265CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_child_partition

266 ON child_partition (column_name);

267```

268

2692. Create the index on the parent table (metadata-only, fast):

270

271```sql

272CREATE INDEX IF NOT EXISTS idx_parent

273 ON parent_table (column_name);

274```

275

276PostgreSQL will automatically recognize partition-level indexes as part of the parent index definition when the index names and definitions match.

277

278### Prioritize active partitions

279

280For time-based partitions (findings uses monthly partitions):

281

282- Create indexes on recent/current partitions where data is actively queried

283- Skip older partitions that are rarely accessed

284- The all_partitions=False default in create_index_on_partitions handles this automatically

285

286## Index maintenance and bloat

287

288Over time, B-tree indexes accumulate bloat from updates and deletes. VACUUM reclaims heap space but does NOT rebalance B-tree pages. Periodic reindexing is necessary for heavily updated tables.

289

290### Detecting bloat

291

292Indexes with estimated bloat above 50% are candidates for REINDEX CONCURRENTLY. Check bloat with tools like pgstattuple or bloat estimation queries.

293

294### Reducing bloat buildup

295

296Three things slow degradation:

2971. **Upgrade to PostgreSQL 14+** for B-tree deduplication and bottom-up deletion

2982. **Maximize HOT updates** by not indexing frequently-updated columns

2993. **Tune autovacuum** to run more aggressively on high-churn tables

300

301### Rebuilding many indexes without deadlocks

302

303If you rebuild two indexes on the same table in parallel, PostgreSQL detects a deadlock and kills one session. To rebuild many indexes across multiple sessions safely, assign all indexes for a given table to the same session:

304

305```sql

306\set NUMBER_OF_SESSIONS 10

307

308SELECT

309 format('%I.%I', n.nspname, c.relname) AS table_fqn,

310 format('%I.%I', n.nspname, i.relname) AS index_fqn,

311 mod(

312 hashtext(format('%I.%I', n.nspname, c.relname)) & 2147483647,

313 :NUMBER_OF_SESSIONS

314 ) AS session_id

315FROM pg_index idx

316JOIN pg_class c ON idx.indrelid = c.oid

317JOIN pg_class i ON idx.indexrelid = i.oid

318JOIN pg_namespace n ON c.relnamespace = n.oid

319WHERE n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')

320ORDER BY table_fqn, index_fqn;

321```

322

323Then run each session's indexes in a separate REINDEX INDEX CONCURRENTLY call. Set NUMBER_OF_SESSIONS based on max_parallel_maintenance_workers and available I/O.

324

325## Dropping indexes

326

327### Post-drop maintenance

328

329After dropping an index, run VACUUM and ANALYZE to reclaim space and update planner statistics:

330

331```sql

332-- Full vacuum + analyze (can be heavy on large tables)

333VACUUM (ANALYZE) your_table;

334

335-- Lightweight alternative for huge tables: just update statistics

336ANALYZE your_table;

337```

338

339## Commands

340

341```sql

342-- Validate query uses an index

343EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

344

345-- Check index creation progress

346SELECT * FROM pg_stat_progress_create_index;

347

348-- Find invalid indexes

349SELECT c.relname, i.indisvalid

350FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid

351WHERE i.indisvalid = false;

352

353-- Find unused indexes

354SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))

355FROM pg_stat_all_indexes

356WHERE schemaname = 'public' AND idx_scan = 0;

357

358-- Create index safely

359CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table (columns);

360

361-- Drop index safely

362DROP INDEX CONCURRENTLY IF EXISTS idx_name;

363

364-- Rebuild invalid index

365REINDEX INDEX CONCURRENTLY idx_name;

366

367-- Post-drop maintenance

368VACUUM (ANALYZE) table_name;

369```

370

371## Context7 lookups

372

373**Prerequisite:** Install Context7 MCP server for up-to-date documentation lookup.

374

375| Library | Context7 ID | Use for |

376|---------|-------------|---------|

377| PostgreSQL | /websites/postgresql_org_docs_current | Index types, EXPLAIN, partitioned table indexing, REINDEX |

378

379**Example queries:**

380

381```text

382mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="CREATE INDEX CONCURRENTLY partitioned table")

383mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="EXPLAIN ANALYZE BUFFERS query plan")

384mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="partial index WHERE clause")

385mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="REINDEX CONCURRENTLY invalid index")

386mcp_context7_query-docs(libraryId="/websites/postgresql_org_docs_current", query="pg_stat_all_indexes monitoring")

387```

388

389> **Note:** Use mcp_context7_resolve-library-id first if you need to find the correct library ID.

390

391## Resources

392

393- **EXPLAIN Visualizer**: [pev](https://tatiyants.com/pev/)

394

In the file

SKILL.md1,884 words

Files1

LicenceApache-2.0

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.

≈230

always loaded

The name and description, so the model knows the skill exists and when to reach for it.

3,345

on trigger

The instruction body, read only when the skill fires.

1.8%

of a 200k window

Ten skills this size would take about 18% of the window before you open a file.

050k100k150k200k context window

3.6k tokens, estimated from the bundle at four bytes to the token, held for the rest of the session once it triggers. Middling. Fine to keep on in a project where you use it weekly, worth unloading in one where you never do.

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.

If this is not it

20 other skills in Databases

What is in the bundle

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

SKILL.md14.3 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 Apache-2.0 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 Indexing · Apache-2.0 · prowler-cloud

one-time

Price$59 once

LicenceApache-2.0 — 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 release of 1.x 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 Apache-2.0, set by the author and unchanged by buying it here.

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

Version1.0

Publishedno release date on file

Price$59

Referenceprowler-cloud/postgresql-indexing

Versions

v1.0 is what is on the shelf; no release here carries a date. Instructions change more often than APIs do — a skill can be rewritten entirely without anything it depends on moving.

v1.0

No earlier releases have been published to the marketplace.

Pinning

Put prowler-cloud/postgresql-indexing@1.0 in the install command to hold this exact version. Without the suffix you get whatever is current the day you install, and nothing moves under you afterwards.

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

prowler-cloud

Publishes on mcprush.

0 servers listed1 skill listednot claimed

Profile

Publisher

Servers0