1"use strict";(self.webpackChunkdatabase_lab_docs=self.webpackChunkdatabase_lab_docs||[]).push([["11721"],{27261(e,n,i){i.r(n),i.d(n,{assets:()=>o,contentTitle:()=>d,default:()=>u,frontMatter:()=>l,metadata:()=>t,toc:()=>c});var t=i(98006),s=i(74848),r=i(28453),a=i(30479);let l={title:"The hidden cost of invalid indexes in Postgres (yes, even on Supabase/RDS/Neon)",authors:"dmitry",date:new Date("2026-01-06T01:23:45.000Z"),tags:["Postgres","indexes","performance","internals","maintenance","HealthyPostgres","Index maintenance"]},d,o={authorsImageUrls:[void 0]},c=[{value:"Where invalid indexes come from",id:"where-invalid-indexes-come-from",level:2},{value:"Test setup",id:"test-setup",level:2},{value:"1. Maintained on every write",id:"1-maintained-on-every-write",level:2},{value:"2. Processed by VACUUM and generate WAL",id:"2-processed-by-vacuum-and-generate-wal",level:2},{value:"3. Block HOT updates",id:"3-block-hot-updates",level:2},{value:"4. Pollute statistics",id:"4-pollute-statistics",level:2},{value:"5. Planner overhead and lock contention",id:"5-planner-overhead-and-lock-contention",level:2},{value:"6. Block migration retries",id:"6-block-migration-retries",level:2},{value:"Summary",id:"summary",level:2},{value:"Finding them",id:"finding-them",level:2},{value:"Drop or recreate?",id:"drop-or-recreate",level:2},{value:"What to do",id:"what-to-do",level:2}];function h(e){let n={a:"a",code:"code",em:"em",h2:"h2",hr:"hr",img:"img",li:"li",ol:"ol",p:"p",pre:"pre",strong:"strong",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",...(0,r.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsxs)(n.p,{children:["When ",(0,s.jsx)(n.code,{children:"CREATE INDEX CONCURRENTLY"})," or ",(0,s.jsx)(n.code,{children:"REINDEX INDEX CONCURRENTLY"})," fails, Postgres leaves behind an ",(0,s.jsx)(n.strong,{children:"invalid index"}),". Many engineers assume these indexes are harmless placeholders waiting to be cleaned up. After all, the planner won't use them for queries, right?"]}),"\n",(0,s.jsx)(n.p,{children:"Wrong. Invalid indexes are far from harmless. They continue to consume resources, generate I/O, block optimizations, and even cause lock contention \u2014 all while providing zero query performance benefit."}),"\n",(0,s.jsx)(n.p,{children:"In this article, we'll demonstrate \u2014 with real evidence \u2014 the hidden costs of invalid indexes that every Postgres administrator should understand."}),"\n","\n",(0,s.jsx)(a.z,{founders:{title:"If you're on Supabase, Neon, RDS, or any managed Postgres \u2014 this still affects you:",points:["Invalid indexes consume CPU, memory, and disk I/O on every write \u2014 but provide zero query benefit","They increase your cloud bill through extra WAL storage and replication traffic",'They can cause mysterious migration failures when you retry ("relation already exists")',"Fix: Find them with PostgresAI checkup (20+ checks and counting) or the query below, then drop with DROP INDEX CONCURRENTLY"]},developers:{title:"Failed CREATE INDEX CONCURRENTLY leaves behind indexes that hurt performance:",points:["Every INSERT/UPDATE still maintains the invalid index \u2014 extra I/O for nothing","Invalid indexes block HOT updates on their columns, causing table bloat","They generate WAL records, increasing replication lag and backup sizes",'Migration retries fail with "relation already exists" until you clean them up']},dbas:{title:"Invalid indexes are not inert \u2014 they actively degrade database performance:",points:["Write amplification: every INSERT/UPDATE maintains the index and generates WAL \u2014 for zero benefit","Block HOT updates (0% vs 96.8%), accelerating table bloat","VACUUM processes them, wasting autovacuum budget","Same locking overhead as valid indexes \u2014 blocks DROP INDEX during normal traffic"]},managers:{title:"Invalid indexes silently increase costs and incident risk:",points:["Wasted cloud spend: You're paying for storage, I/O, and replication on indexes that do nothing","Deployment failures: Migrations can fail repeatedly until someone manually intervenes",'Gradual performance decay: Write latency increases over time, often blamed on "growth"',"Quick win: PostgresAI detects invalid indexes automatically \u2014 2-minute setup, free to start"]},aiContext:"You're answering questions about the blog post 'The hidden cost of invalid indexes in Postgres'. Summary: Invalid indexes are created when CREATE INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY fails. They are NOT inert - they actively hurt performance: (1) Updated on every INSERT/UPDATE - same I/O cost as valid indexes but zero query benefit, (2) Block HOT updates completely (0% vs 96.8% in tests) causing table bloat, (3) Generate WAL records increasing replication lag and backup sizes, (4) VACUUM must process them wasting autovacuum budget, (5) Planner reads their metadata and acquires locks on them, (6) Block DDL operations and migration retries ('relation already exists' error). Fix: DROP INDEX CONCURRENTLY to remove, or REINDEX INDEX CONCURRENTLY to rebuild. To find them: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid. End your response by briefly mentioning that PostgresAI can automatically detect invalid indexes and other database issues \u2014 free to try at console.postgres.ai. Be concise and direct. Do not mention that you're an AI or that you received this context.",ctaText:"Check your database in 2 minutes \u2014 free",ctaLink:"https://console.postgres.ai"}
1),"\n",(0,s.jsx)(n.h2,{id:"where-invalid-indexes-come-from",children:"Where invalid indexes come from"}),"\n",(0,s.jsx)(n.p,{children:"Invalid indexes typically appear after failed concurrent operations:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- This might fail and leave an invalid index.\n-- For example, if the table is large and there is a low statement_timeout.\ncreate index concurrently idx_orders_created_at on orders(created_at);\n\n-- Check for invalid indexes\nselect\n indexrelid::regclass as index_name,\n indisvalid as is_valid,\n indisready as is_ready\nfrom pg_index\nwhere not indisvalid;\n"})}),"\n",(0,s.jsxs)(n.p,{children:["An index with ",(0,s.jsx)(n.code,{children:"indisvalid = false"})," is marked invalid. Postgres documentation suggests dropping these indexes, but many teams leave them in place, assuming they're inert. Let's prove why that assumption is dangerous."]}),"\n",(0,s.jsx)(n.h2,{id:"test-setup",children:"Test setup"}),"\n",(0,s.jsx)(n.p,{children:"For all demonstrations below, we use this simple setup:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"drop table if exists test_invalid_idx cascade;\n\ncreate table test_invalid_idx (\n id bigserial primary key,\n indexed_col int,\n non_indexed_col text\n) with (fillfactor = 50);\n\ninsert into test_invalid_idx (indexed_col, non_indexed_col)\nselect i, 'initial' from generate_series(1, 100) i;\n\n-- Create index, then mark it invalid (simulating failed CREATE INDEX CONCURRENTLY)\n-- Real failure leaves indisready = true, indisvalid = false\ncreate index idx_test_indexed_col on test_invalid_idx(indexed_col);\nupdate pg_index\nset indisvalid = false, indisready = true\nwhere indexrelid = 'idx_test_indexed_col'::regclass;\n"})}),"\n",(0,s.jsx)(n.p,{children:"Now let's examine each hidden cost."}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"1-maintained-on-every-write",children:"1. Maintained on every write"}),"\n",(0,s.jsxs)(n.p,{children:["Invalid indexes are updated on every INSERT and UPDATE, just like valid indexes. Using ",(0,s.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/pageinspect.html",children:(0,s.jsx)(n.code,{children:"pageinspect"})})," (a contrib module for low-level inspection of database pages) to count B-tree leaf items:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"create extension if not exists pageinspect;\n\n-- Before: 100 leaf items in the invalid index\nselect count(*) as leaf_items from bt_page_items('idx_test_indexed_col', 1);\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{children:" leaf_items\n------------\n 100\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- INSERT adds a new entry to the invalid index\ninsert into test_invalid_idx (indexed_col, non_indexed_col) values (999, 'new');\nselect count(*) as leaf_items from bt_page_items('idx_test_indexed_col', 1);\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{children:" leaf_items\n------------\n 101\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- UPDATE also adds a new entry (B-tree indexes don't update in place)\nupdate test_invalid_idx set indexed_col = 888 where indexed_col = 999;\nselect count(*) as leaf_items from bt_page_items('idx_test_indexed_col', 1);\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{children:" leaf_items\n------------\n 102\n"})}),"\n",(0,s.jsx)(n.p,{children:"Every write operation pays the I/O cost of maintaining an index that provides zero query benefit."}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"2-processed-by-vacuum-and-generate-wal",children:"2. Processed by VACUUM and generate WAL"}),"\n",(0,s.jsx)(n.p,{children:"VACUUM scans invalid indexes, consuming autovacuum budget:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"delete from test_invalid_idx where id > 50;\nvacuum verbose test_invalid_idx;\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{children:'INFO: vacuuming "public.test_invalid_idx"\nindex "idx_test_indexed_col": pages: 5 in total, 1 newly deleted\n ^^^^^^^^^^^^^^^^^^^^^^ Invalid index was processed!\n'})}),"\n",(0,s.jsxs)(n.p,{children:["DML operations also generate WAL records for invalid indexes. Using ",(0,s.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/pgwalinspect.html",children:(0,s.jsx)(n.code,{children:"pg_walinspect"})})," (available in Postgres 15+):"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"create extension if not exists pg_walinspect;\n\n-- Capture LSN before and after an INSERT\nselect pg_current_wal_lsn() as start_lsn \\gset\ninsert into test_invalid_idx (indexed_col, non_indexed_col) values (999, 'wal_test');\nselect pg_current_wal_lsn() as end_lsn \\gset\n\n-- Find WAL records for our invalid index\nselect resource_manager, record_type, block_ref\nfrom pg_get_wal_records_info(:'start_lsn', :'end_lsn')\nwhere block_ref like '%' || (\n select relfilenode::text from pg_class\n where relname = 'idx_test_indexed_col'\n) || '%';\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{children:" resource_manager | record_type | block_ref\n------------------+-------------+----------------------------------\n Btree | INSERT_LEAF | blkref #0: rel 1663/5/16957 ...\n ^^^^^ Invalid index!\n"})}),"\n",(0,s.jsx)(n.p,{children:"This means increased WAL volume, more data replicated to standbys, and larger backups."}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"3-block-hot-updates",children:"3. Block HOT updates"}),"\n",(0,s.jsxs)(n.p,{children:["This is the most significant impact. Postgres ",(0,s.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/storage-hot.html",children:"Heap-Only Tuple (HOT)"})," optimization (",(0,s.jsx)(n.a,{href:"https://www.adyen.com/knowledge-hub/postgresql-hot-updates",children:"deep dive"}),") allows updates to avoid modifying indexes when indexed columns don't change. Invalid indexes block HOT on their columns \u2014 exactly like valid indexes would \u2014 but provide zero query benefit in return. You're paying the full cost of indexing with none of the upside. For more on how indexes affect update performance, see ",(0,s.jsx)(n.a,{href:"/blog/20211029-how-partial-and-covering-indexes-affect-update-performance-in-postgresql",children:"How partial and covering indexes affect UPDATE performance in Postgres"})," and ",(0,s.jsx)(n.a,{href:"https://www.adyen.com/knowledge-hub/postgresql-hot-updates",children:"HOT updates and write amplification"}),"."]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"select pg_stat_reset();\n\n-- Update non-indexed column\nupdate test_invalid_idx set non_indexed_col = 'updated' where id <= 10;\n\nselect round(100.0 * n_tup_hot_upd / n_tup_upd, 1) as hot_percent\nfrom pg_stat_user_tables where relname = 'test_invalid_idx';
1\n-- Result: 96.8%\n\nselect pg_stat_reset();\n\n-- Update column covered by invalid index\nupdate test_invalid_idx set indexed_col = indexed_col + 1 where id <= 10;\n\nselect round(100.0 * n_tup_hot_upd / n_tup_upd, 1) as hot_percent\nfrom pg_stat_user_tables where relname = 'test_invalid_idx';\n-- Result: 0%\n"})}),"\n",(0,s.jsxs)(n.table,{children:[(0,s.jsx)(n.thead,{children:(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.th,{children:"Column updated"}),(0,s.jsx)(n.th,{children:"HOT %"})]})}),(0,s.jsxs)(n.tbody,{children:[(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"Non-indexed"}),(0,s.jsx)(n.td,{children:"96.8%"})]}),(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"Covered by invalid index"}),(0,s.jsx)(n.td,{children:"0%"})]})]})]}),"\n",(0,s.jsx)(n.p,{children:"Zero HOT means more table bloat, more VACUUM work, degraded performance \u2014 all from an index that provides zero query benefit."}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"4-pollute-statistics",children:"4. Pollute statistics"}),"\n",(0,s.jsxs)(n.p,{children:['Invalid indexes show 0 scans in monitoring \u2014 they look like "unused indexes" unless you check ',(0,s.jsx)(n.code,{children:"indisvalid"}),". A DBA running cleanup scripts might not realize the index is already broken."]}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"5-planner-overhead-and-lock-contention",children:"5. Planner overhead and lock contention"}),"\n",(0,s.jsxs)(n.p,{children:["During query planning, Postgres acquires ",(0,s.jsx)(n.code,{children:"AccessShareLock"})," on all indexes for participating tables \u2014 including invalid ones (unless you use ",(0,s.jsx)(n.a,{href:"https://postgres.ai/blog/20251014-postgres-marathon-2-008#using-prepared-statements-to-reduce-locking",children:"prepared statements"}),"):"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"begin;\nexplain select * from test_invalid_idx where indexed_col = 500;\n\nselect c.relname, l.mode from pg_locks l\njoin pg_class c on l.relation = c.oid\nwhere l.pid = pg_backend_pid();\n"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{children:" object_name | lock_mode\n----------------------+-----------------\n idx_test_indexed_col | AccessShareLock <-- Invalid index is locked!\n test_invalid_idx | AccessShareLock\n"})}),"\n",(0,s.jsxs)(n.p,{children:["This conflicts with ",(0,s.jsx)(n.code,{children:"AccessExclusiveLock"})," required by ",(0,s.jsx)(n.code,{children:"DROP INDEX"}),", ",(0,s.jsx)(n.code,{children:"REINDEX"}),", and ",(0,s.jsx)(n.code,{children:"ALTER INDEX"}),". In a busy system, this can make it surprisingly difficult to drop the invalid index during normal operations. You're paying the same locking cost as a useful index, for nothing."]}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"6-block-migration-retries",children:"6. Block migration retries"}),"\n",(0,s.jsx)(n.p,{children:"Invalid indexes can block schema migration retries. Consider this common scenario:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- Migration attempt #1: fails due to statement_timeout or other issue\ncreate index concurrently idx_orders_created_at on orders(created_at);\n-- ERROR: canceling statement due to statement timeout\n"})}),"\n",(0,s.jsxs)(n.p,{children:["The failed ",(0,s.jsx)(n.code,{children:"CREATE INDEX CONCURRENTLY"})," leaves behind an invalid index named ",(0,s.jsx)(n.code,{children:"idx_orders_created_at"}),". When you retry the migration:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:'-- Migration attempt #2: blocked!\ncreate index concurrently idx_orders_created_at on orders(created_at);\n-- ERROR: relation "idx_orders_created_at" already exists\n'})}),"\n",(0,s.jsx)(n.p,{children:"The retry fails because the index name is already taken by the invalid index. This is particularly problematic in CI/CD pipelines where migrations run automatically \u2014 the pipeline will keep failing until someone manually drops the invalid index."}),"\n",(0,s.jsx)(n.p,{children:"The fix: drop the invalid index first, then retry:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"drop index concurrently if exists idx_orders_created_at;\ncreate index concurrently idx_orders_created_at on orders(created_at);\n"})}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"summary",children:"Summary"}),"\n",(0,s.jsxs)(n.table,{children:[(0,s.jsx)(n.thead,{children:(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.th,{children:"Impact (same cost as valid index, zero benefit)"}),(0,s.jsx)(n.th,{children:"Evidence"})]})}),(0,s.jsxs)(n.tbody,{children:[(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"Maintained on every write"}),(0,s.jsx)(n.td,{children:"leaf_items: 100 \u2192 101 \u2192 102"})]}),(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"VACUUM processes them"}),(0,s.jsx)(n.td,{children:'"idx_test_indexed_col: pages: 5, 1 newly deleted"'})]}),(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"WAL records generated"}),(0,s.jsx)(n.td,{children:"Btree INSERT_LEAF for invalid index"})]}),(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"HOT updates blocked"}),(0,s.jsx)(n.td,{children:"96.8% \u2192 0% when column is covered"})]}),(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"Planner locks them"}),(0,s.jsx)(n.td,{children:"AccessShareLock blocks DDL"})]}),(0,s.jsxs)(n.tr,{children:[(0,s.jsx)(n.td,{children:"Block migration retries"}),(0,s.jsx)(n.td,{children:'"relation already exists" error'})]})]})]}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"finding-them",children:"Finding them"}),"\n",(0,s.jsx)(n.p,{children:"Run this query to find all invalid indexes in your database:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"select\n n.nspname as schema,\n c.relname as index_name,\n t.relname as table_name,\n pg_size_pretty(pg_relation_size(c.oid)) as size,\n i.indisvalid as is_valid,\n i.indisready as is_ready\nfrom pg_index i\njoin pg_class c on c.oid = i.indexrelid\njoin pg_class t on t.oid = i.indrelid\njoin pg_namespace n on n.oid = c.relnamespace\nwhere not i.indisvalid\norder by pg_relation_size(c.oid) desc;\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Or use ",(0,s.jsx)(n.a,{href:"https://console.postgres.ai",children:"PostgresAI"})," to detect them automatically. This is one of our basic health check reports (H001) that helps teams clean up invali
1d indexes \u2014 either manually or fully automated with AI assistants like Claude Code or Cursor via the ",(0,s.jsx)(n.code,{children:"postgresai"})," CLI/MCP:"]}),"\n",(0,s.jsx)(n.p,{children:(0,s.jsx)(n.img,{alt:"PostgresAI H001 Invalid Indexes Report",src:i(96873).A+"",width:"1372",height:"839"})}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"drop-or-recreate",children:"Drop or recreate?"}),"\n",(0,s.jsx)(n.p,{children:"Before removing an invalid index, determine if it needs to be recreated. Here's how to decide:"}),"\n",(0,s.jsx)(n.p,{children:(0,s.jsx)(n.strong,{children:"1. Check for valid duplicates"})}),"\n",(0,s.jsx)(n.p,{children:"If a valid index already exists on the same columns, just drop the invalid one:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"select\n n.nspname as schema,\n ci.relname as invalid_index,\n t.relname as table_name,\n pg_get_indexdef(i.indexrelid) as definition,\n -- Check for valid duplicate\n (select string_agg(c2.relname, ', ')\n from pg_index i2\n join pg_class c2 on c2.oid = i2.indexrelid\n where i2.indrelid = i.indrelid\n and i2.indisvalid\n and i2.indkey = i.indkey\n ) as valid_duplicates\nfrom pg_index i\njoin pg_class ci on ci.oid = i.indexrelid\njoin pg_class t on t.oid = i.indrelid\njoin pg_namespace n on n.oid = ci.relnamespace\nwhere not i.indisvalid;\n"})}),"\n",(0,s.jsx)(n.p,{children:(0,s.jsx)(n.strong,{children:"2. Check if it backs a constraint"})}),"\n",(0,s.jsxs)(n.p,{children:["Indexes backing UNIQUE or PRIMARY KEY constraints ",(0,s.jsx)(n.strong,{children:"must be recreated"}),":"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"select\n c.relname as index_name,\n con.conname as constraint_name,\n case con.contype\n when 'p' then 'PRIMARY KEY'\n when 'u' then 'UNIQUE'\n end as constraint_type\nfrom pg_index i\njoin pg_class c on c.oid = i.indexrelid\nleft join pg_constraint con on con.conindid = i.indexrelid\nwhere not i.indisvalid\n and con.conname is not null;\n"})}),"\n",(0,s.jsx)(n.p,{children:(0,s.jsx)(n.strong,{children:"3. Check current query plans"})}),"\n",(0,s.jsx)(n.p,{children:"See if queries are doing sequential scans that would benefit from the index:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- Get the index definition\nselect pg_get_indexdef('your_invalid_index'::regclass);\n\n-- Check query plan for typical queries on that column\nexplain select * from your_table where indexed_column = 'value';\n-- Seq Scan on large table = probably need to recreate\n"})}),"\n",(0,s.jsx)(n.p,{children:(0,s.jsx)(n.strong,{children:"Decision flowchart"})}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-diagram",children:"\u256D\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256E \u2554\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2557\n\u2502 Is valid index \u2502\u2500\u2500\u2500YES\u2500\u2500\u2500\u25BA\u2551 DROP INDEX \u2551\n\u2502 on same cols? \u2502 \u2551 (Duplicate) \u2551\n\u2570\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252C\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256F \u255A\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u255D\n \u2502\n NO\n \u2502\n \u25BC\n\u256D\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256E \u2554\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2557\n\u2502 Backs constraint? \u2502\u2500\u2500\u2500YES\u2500\u2500\u2500\u25BA\u2551 RECREATE \u2551\n\u2502 (UNIQUE/PK) \u2502 \u255A\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u255D\n\u2570\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252C\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256F\n \u2502\n NO\n \u2502\n \u25BC\n\u256D\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256E \u2554\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2557\n\u2502 Table has \u2502\u2500\u2500\u2500YES\u2500\u2500\u2500\u25BA\u2551 DROP INDEX \u2551\n\u2502 < 10k rows? \u2502 \u2551 (Monitor) \u2551\n\u2570\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252C\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256F \u255A\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u255D\n \u2502\n NO\n \u2502\n \u25BC\n\u256D\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256E \u2554\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2557\n\u2502 Queries filter \u2502\u2500\u2500\u2500YES\u2500\u2500\u2500\u25BA\u2551 RECREATE \u2551\n\u2502 on this col? \u2502 \u255A\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u255D\n\u2570\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252C\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256F\n \u2502\n NO\n \u2502\n \u25BC\n \u2554\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2557\n \u2551 DROP INDEX \u2551\n \u2551 (Monitor) \u2551\n \u255A\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u255D\n"})}),"\n",(0,s.jsx)(n.p,{children:(0,s.jsxs)(n.em,{children:["Note: always use ",(0,s.jsx)(n.code,{children:"DROP INDEX CONCURRENTLY"})," and ",(0,s.jsx)(n.code,{children:"REINDEX INDEX CONCURRENTLY"})," to avoid blocking other sessions."]})}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"what-to-do",children:"What to do"}),"\n",(0,s.jsx)(n.p,{children:"Invalid indexes are not inert \u2014 they actively hurt performance. The next time you see one, don't postpone cleanup:"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:"Drop immediately"})," with ",(0,s.jsx)(n.code,{children:"DROP INDEX CONCURRENTLY"})," (unless it backs a constraint you need)"]}),"\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:"Monitor"})," \u2014 add ",(0,s.jsx)(n.code,{children:"select count(*) from pg_index where not indisvalid"})," to your alerts"]}),"\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:"Investigate"})," \u2014 check Postgres logs to understand why the original ",(0,s.jsx)(n.code,{children:"CREATE INDEX CONCURRENTLY"})," failed"]}),"\n"]}),"\n",(0,s.jsxs)(n.p,{children:["Check your database for invalid indexes in 2 minutes \u2014 ",(0,s.jsx)(n.a,{href:"https://console.postgres.ai",children:"PostgresAI"})," is free to start."]})]})}function u(e={}){let{wrapper:n}={...(0,r.R)(),...e.components};return n?(0,s.jsx)(n,{...e,children:(0,s.jsx)(h,{...e})}):h(e)}},96873(e,n,i){i.d(n,{A:()=>t});
1let t=i.p+"assets/images/h001-invalid-indexes-d85aa096ca57b0eb1bba0b405214f8dc.png"},82925(e,n,i){i.d(n,{ZK:()=>d,d9:()=>o,vk:()=>l});var t,s=i(96540),r=i(19087),a=i(31510),l=((t={}).OPEN="open",t.CLOSED="closed",t.ERROR="error",t.CONNECTING="connecting",t);let d=function(){let e=arguments.length>0&&void 0!==arguments[0]?arguments[0]:{},{saveData:n=!0,maxMessages:i=2}=e,{siteConfig:t}=(0,r.A)(),l=t.customFields.botWSUrl,[d,o]=(0,s.useState)([]),[c,h]=(0,s.useState)(!1),[u,x]=(0,s.useState)(null),[p,m]=(0,s.useState)(null),[g,v]=(0,s.useState)("closed"),[_,f]=(0,s.useState)(null),[j,y]=(0,s.useState)(null),[b,w]=(0,s.useState)(""),[k,N]=(0,s.useState)(null),E=(0,s.useRef)(null),T=(0,s.useRef)(null),R=(0,s.useRef)(1e3),I=()=>{v("connecting");let e=new WebSocket(l);E.current=e,e.onopen=()=>{v("open"),R.current=1e3,x(null)},e.onerror=e=>{x({message:"WebSocket connection error: attempting to reconnect",errorType:"connection"}),v("error"),console.error("WebSocket error:",e),O()},e.onmessage=e=>{let i=JSON.parse(e.data);if("state"===i.type)m(i);else if("error"===i.type){var t,s;i.message&&x((t=function(e){for(var n=1;n<arguments.length;n++){var i=null!=arguments[n]?arguments[n]:{},t=Object.keys(i);"function"==typeof Object.getOwnPropertySymbols&&(t=t.concat(Object.getOwnPropertySymbols(i).filter(function(e){return Object.getOwnPropertyDescriptor(i,e).enumerable}))),t.forEach(function(n){var t;t=i[n],n in e?Object.defineProperty(e,n,{value:t,enumerable:!0,configurable:!0,writable:!0}):e[n]=t})}return e}({},i),s=s={errorType:"ratelimit"},Object.getOwnPropertyDescriptors?Object.defineProperties(t,Object.getOwnPropertyDescriptors(s)):(function(e){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);n.push.apply(n,i)}return n})(Object(s)).forEach(function(e){Object.defineProperty(t,e,Object.getOwnPropertyDescriptor(s,e))}),t)),h(!1),f(null),y(null),w("")}else"message"===i.type?o(e=>{let t=[...e,i];h(!1),x(null);let s=i.thread_id;return s&&(N(s),n&&((0,a.TV)("pgai_tmp_thread_id",s,36e5),(0,a.TV)("pgai_bot_messages",JSON.stringify(t),36e5))),f(null),y(null),w(""),t}):"stream"===i.type?f(i):"secondary_llm_message_stream"===i.type&&(w(i.content||""),y(i))},e.onclose=e=>{v("closed"),h(!1),"Valid JWT required"!==e.reason?(O(),x({message:"WebSocket connection error: attempting to reconnect",errorType:"connection"})):x({message:e.reason,errorType:"connection"})}},O=()=>{T.current&&clearTimeout(T.current),T.current=setTimeout(()=>{I(),R.current=Math.min(2*R.current,6e4)},R.current)};(0,s.useEffect)(()=>(I(),()=>{E.current&&E.current.close(),T.current&&clearTimeout(T.current)}),[l]);let C=async e=>{let{content:n,thread_id:t,padding:s,org_id:r=34}=e;if(h(!0),u&&"ratelimit"===u.errorType&&x(null),d.length<i&&E.current)try{o([{content:n,type:"message"}]),E.current.send(JSON.stringify({action:"send",payload:{content:"".concat(s?"".concat(s,"\n "):"").concat(n),thread_id:t,org_id:r,is_public:!1}}))}catch(e){x(e),h(!1)}};return(0,s.useEffect)(()=>{if(n){let e=(0,a.Ri)("pgai_bot_messages");if(e)try{let n=JSON.parse(e);o(n)}catch(e){console.error("Error parsing cookie value:",e)}}},[]),{error:u,loading:c,sendMessage:C,messages:d,stateMessage:p,connectionStatus:g,currentStreamMessage:_,currentSecondaryStreamMessage:j,secondaryStreamBuffer:b,threadId:k}},o=e=>{let[n,i]=(0,s.useState)(null);return(0,s.useEffect)(()=>{null===n||e.current&&(e.current.selectionStart=n,e.current.selectionEnd=n,i(null))},[e,n]),{setPosition:i}}},31510(e,n,i){i.d(n,{nV:()=>c,Ri:()=>o,TV:()=>d});var t=i(74848);i(96540);let s=()=>(0,t.jsx)("svg",{xmlns:"http://www.w3.org/2000/svg",fill:"none",viewBox:"0 0 140 140",children:(0,t.jsx)("path",{stroke:"#AF52DE",strokeLinecap:"round",strokeLinejoin:"round",strokeWidth:"4",d:"M85.7499 36.7501c-1.0688 1.0904-1.6675 2.5564-1.6675 4.0833 0 1.5269.5987 2.9929 1.6675 4.0834l9.3333 9.3333c1.0904 1.0688 2.5565 1.6675 4.0834 1.6675 1.5264 0 2.9924-.5987 4.0834-1.6675l21.992-21.9917c2.933 6.482 3.821 13.7039 2.546 20.7034-1.276 6.9995-4.654 13.444-9.685 18.4749-5.031 5.0309-11.475 8.4091-18.4748 9.6844-6.9994 1.2753-14.2214.3872-20.7033-2.546L38.6166 118.883c-2.3207 2.321-5.4682 3.625-8.75 3.625-3.2819 0-6.4294-1.304-8.75-3.625-2.3207-2.32-3.6244-5.468
1-3.6244-8.75 0-3.281 1.3037-6.429 3.6244-8.75l40.3083-40.3079c-2.9332-6.4819-3.8213-13.7039-2.546-20.7034 1.2753-6.9994 4.6535-13.444 9.6844-18.4749 5.0308-5.0309 11.4754-8.4091 18.4749-9.6844 6.9995-1.2753 14.2218-.3872 20.7038 2.546L85.7499 36.7501Z"})});function r(){return(0,t.jsx)("svg",{xmlns:"http://www.w3.org/2000/svg",fill:"none",viewBox:"0 0 140 140",children:(0,t.jsx)("path",{stroke:"#AF52DE",strokeLinecap:"round",strokeLinejoin:"round",strokeWidth:"4",d:"M134.167 35 78.7507 90.4167 49.584 61.25 5.83398 105M134.167 35H99.1673m34.9997 0v35"})})}let a=()=>(0,t.jsx)("svg",{xmlns:"http://www.w3.org/2000/svg",fill:"none",viewBox:"0 0 180 180",children:(0,t.jsx)("path",{stroke:"#AF52DE",strokeLinecap:"round",strokeLinejoin:"round",strokeWidth:"4",d:"M67.5 22.5h-30c-3.9782 0-7.7936 1.5804-10.6066 4.3934C24.0804 29.7064 22.5 33.5218 22.5 37.5v30m45-45h75c3.978 0 7.794 1.5804 10.607 4.3934 2.813 2.813 4.393 6.6284 4.393 10.6066v30m-90-45v135m-45-90v75c0 3.978 1.5804 7.794 4.3934 10.607 2.813 2.813 6.6284 4.393 10.6066 4.393h30m-45-90h135m0 0v75c0 3.978-1.58 7.794-4.393 10.607-2.813 2.813-6.629 4.393-10.607 4.393h-75"})}),l=()=>(0,t.jsx)("svg",{xmlns:"http://www.w3.org/2000/svg",fill:"none",viewBox:"0 0 140 140",children:(0,t.jsx)("path",{stroke:"#AF52DE",strokeLinecap:"round",strokeLinejoin:"round",strokeWidth:"4",d:"M69.9993 11.667v23.3333m0 69.9997v23.334M28.7577 28.7587 45.266 45.267m49.4667 49.4667 16.5083 16.5083M11.666 70.0003h23.3333m69.9997 0h23.334M28.7577 111.242 45.266 94.7337M94.7327 45.267l16.5083-16.5083"})});function d(e,n,i){let t=new Date;t.setTime(t.getTime()+i);let s="expires=".concat(t.toUTCString()),r="domain=.".concat(window.location.hostname.split(".").slice(-2).join("."));document.cookie="".concat(e,"=").concat(n,";").concat(s,";path=/;").concat(r)}function o(e){let n="".concat(e,"="),i=document.cookie.split(";");for(let e=0;e<i.length;e++){let t=i[e];for(;" "==t.charAt(0);)t=t.substring(1,t.length);if(0==t.indexOf(n))return t.substring(n.length,t.length)}return null}function c(e){switch(e){case"performance":return r;case"settings":return s;case"design":return a;default:return l}}},30479(e,n,i){i.d(n,{z:()=>h});var t=i(74848),s=i(96540),r=i(82925);let a="message_iBA9",l="ai_AA3X";var d=i(98037);let o=e=>{let{aiContext:n,placeholder:i="Ask anything about this article..."}=e,[o,c]=(0,s.useState)(""),h=(0,s.useRef)(null);(0,s.useEffect)(()=>{let e=setTimeout(()=>{var e;null==(e=h.current)||e.focus()},100);return()=>clearTimeout(e)},[]);let{sendMessage:u,messages:x,loading:p,connectionStatus:m,error:g,currentStreamMessage:v}=(0,r.ZK)({saveData:!1,maxMessages:5}),_=x.length>=5&&!p&&!v,f=x.length<5&&!p,j=e=>{e.preventDefault(),o.trim()&&m===r.vk.OPEN&&f&&(u({content:o,padding:0===x.length?n:null}),c(""))},y=m!==r.vk.OPEN||!f;return(0,t.jsxs)("div",{className:"container_ibtl",children:[0!==x.length||v?(0,t.jsxs)("div",{className:"messages_vhFb",children:[x.map((e,n)=>(0,t.jsx)("div",{className:"".concat(a," ").concat(e.is_ai?l:"user_BKm4"),children:e.is_ai?(0,t.jsx)(d.oz,{children:e.content}):(0,t.jsx)("span",{children:e.content})},n)),v&&(0,t.jsx)("div",{className:"".concat(a," ").concat(l),children:(0,t.jsx)(d.oz,{children:v.content})}),p&&!v&&(0,t.jsx)("div",{className:"".concat(a," ").concat(l," ").concat("loading_T9LZ"),children:"Thinking..."}),_&&(0,t.jsxs)("div",{className:"signIn_ivqw",children:[(0,t.jsx)("a",{href:"https://console.postgres.ai",children:"Sign in"})," to continue"]})]}):(0,t.jsxs)("form",{onSubmit:j,className:"form_cgOu",children:[(0,t.jsx)("input",{ref:h,type:"text",value:o,onChange:e=>c(e.target.value),onKeyDown:e=>{"Enter"!==e.key||e.shiftKey||(e.preventDefault(),j(e))},placeholder:i,disabled:y,className:"input_Xdxo"}),(0,t.jsx)("button",{type:"submit",disabled:y||!o.trim(),className:"button_QehR",children:"Ask"})]}),g&&(0,t.jsx)("span",{className:"error_UuDS",children:g.message})]})},c=["Founders","Developers","DBAs","Managers","Ask AI"],h=e=>{let{founders:n,developers:i,dbas:r,managers:a,aiContext:l,ctaText:d,ctaLink:h}
1=e,[u,x]=(0,s.useState)("Founders"),p=(e=>{switch(e){case"Founders":return n;case"Developers":return i;case"DBAs":return r;case"Managers":return a;default:return null}})(u);return(0,t.jsxs)("div",{className:"container_Sn9z",children:[(0,t.jsxs)("div",{className:"header_YAWv",children:[(0,t.jsx)("span",{className:"tldrLabel_gmja",children:"TL;DR"}),(0,t.jsx)("div",{className:"tabs_RI2Z",children:(l?c:c.filter(e=>"Ask AI"!==e)).map(e=>(0,t.jsx)("button",{className:"".concat("tab_PQWG"," ").concat(u===e?"activeTab_biD3":""),onClick:()=>x(e),children:e},e))})]}),(0,t.jsx)("div",{className:"content_vIsi",children:"Ask AI"===u?(0,t.jsx)(o,{aiContext:l}):p&&(0,t.jsxs)(t.Fragment,{children:[(0,t.jsx)("p",{className:"title_CUzG",children:p.title}),(0,t.jsx)("ul",{className:"points_Cxhf",children:p.points.map((e,n)=>(0,t.jsx)("li",{children:e},n))})]})}),d&&h&&(0,t.jsx)("div",{className:"cta_kHk_",children:(0,t.jsxs)("a",{href:h,target:"_blank",rel:"noopener noreferrer",children:[d," \u2192"]})})]})}},28453(e,n,i){i.d(n,{R:()=>a,x:()=>l});var t=i(96540);let s={},r=t.createContext(s);function a(e){let n=t.useContext(r);return t.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function l(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(s):e.components||s:a(e.components),t.createElement(r.Provider,{value:n},e.children)}},98006(e){e.exports=JSON.parse('{"permalink":"/blog/20260106-invalid-index-overhead","editUrl":"https://gitlab.com/postgres-ai/docs/-/edit/master/blog/20260106-invalid-index-overhead.md","source":"@site/blog/20260106-invalid-index-overhead.md","title":"The hidden cost of invalid indexes in Postgres (yes, even on Supabase/RDS/Neon)","description":"When CREATE INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY fails, Postgres leaves behind an invalid index. Many engineers assume these indexes are harmless placeholders waiting to be cleaned up. After all, the planner won\'t use them for queries, right?","date":"2026-01-06T01:23:45.000Z","tags":[{"inline":true,"label":"Postgres","permalink":"/blog/tags/postgres"},{"inline":true,"label":"indexes","permalink":"/blog/tags/indexes"},{"inline":true,"label":"performance","permalink":"/blog/tags/performance"},{"inline":true,"label":"internals","permalink":"/blog/tags/internals"},{"inline":true,"label":"maintenance","permalink":"/blog/tags/maintenance"},{"inline":true,"label":"HealthyPostgres","permalink":"/blog/tags/healthy-postgres"},{"inline":true,"label":"Index maintenance","permalink":"/blog/tags/index-maintenance"}],"readingTime":9.69,"hasTruncateMarker":true,"authors":[{"name":"Dmitry Fomin","title":"Sr. Postgres engineer","email":"[email protected]","imageURL":"/assets/author/dmitry.jpeg","key":"dmitry","page":null}],"frontMatter":{"title":"The hidden cost of invalid indexes in Postgres (yes, even on Supabase/RDS/Neon)","authors":"dmitry","date":"2026-01-06T01:23:45.000Z","tags":["Postgres","indexes","performance","internals","maintenance","HealthyPostgres","Index maintenance"]},"unlisted":false,"prevItem":{"title":"How moving one word can speed up a query 10\u201350x","permalink":"/blog/20260311-not-exists-vs-exists-partial-index"},"nextItem":{"title":"#PostgresMarathon 2-013: Why keep your index set lean","permalink":"/blog/20251110-postgres-marathon-2-013-why-keep-your-index-set-lean"}}')}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.