Indexwijzigingen in PostgreSQL zonder onderbreking: een veilige handleiding

Waarom indexwijzigingen downtime veroorzaken (en hoe je dat voorkomt)

Indexwerk klinkt onschuldig. Je bent "alleen maar" een hulpstructuur aan het toevoegen. In PostgreSQL kan het bouwen, verwijderen of wisselen van een index echter locks veroorzaken die andere sessies blokkeren. Als de tabel druk is, stapelen die wachttijden zich op en gaat de app langzaam of lijkt gebroken.

Downtime ziet er zelden uit als een nette uitvalmelding. Het verschijnt vaker als pagina's die blijven hangen, achtergrondjobs die achterlopen en een groeiende rij verzoeken die vastzitten op de database. Iemand klikt op "Zoeken" en krijgt een time-out, terwijl supporttools en adminschermen traag aanvoelen omdat eenvoudige queries de lock niet krijgen die ze nodig hebben.

"Doe het gewoon ’s nachts" faalt om twee veelvoorkomende redenen. Veel systemen zijn nooit echt rustig (wereldwijde gebruikers, batchjobs, ETL, backups). En indexoperaties kunnen langer duren dan je denkt omdat ze veel data lezen en concurreren om CPU en schijf. Als het venster sluit terwijl de build nog loopt, moet je kiezen tussen langer wachten of het werk onderbreken.

Zero-downtime indexwijzigingen zijn geen magie. Het draait om het kiezen van de minst-blokkerende operatie, het instellen van vangrails (timeouts en schijfcontroles) en het monitoren van de database tijdens de uitvoering.

Deze playbook richt zich op praktische productieroutines:

• Geef de voorkeur aan concurrent index builds wanneer lezen en schrijven door moeten gaan.
• Monitor locks en voortgang van de build zodat je vroeg kunt reageren.
• Heb een rollback-pad als de wijziging regressies veroorzaakt of te lang duurt.

Wat het niet behandelt: diepe indexontwerptheorie, brede query‑tuning of schemarefactoren die veel data herschrijven.

Het eenvoudige lockmodel achter indexwerk

PostgreSQL gebruikt locks om data correct te houden wanneer veel sessies dezelfde tabel raken. Een lock is gewoon een regel die zegt wie nu een object mag lezen of schrijven en wie moet wachten.

Meestal merk je locks niet omdat PostgreSQL lichte modi gebruikt die normale queries toestaan. DDL is anders. Bij het aanmaken of verwijderen van een index heeft PostgreSQL voldoende controle over de tabel nodig om de catalogus en data consistent te houden. Hoe meer controle het nodig heeft, hoe meer andere sessies mogelijk gedwongen worden te wachten.

Een index bouwen versus een index gebruiken

Een index gebruiken is meestal goedkoop qua locking. SELECT-, UPDATE- en DELETE-queries kunnen indexen lezen of onderhouden terwijl andere sessies hetzelfde doen.

Een index bouwen is anders. PostgreSQL moet de tabel scannen, sleutels sorteren of hashen, en een nieuwe structuur naar schijf schrijven. Dat werk kost tijd, en tijd is wat "kleine locks" in productie tot "grote problemen" maakt.

Wat CONCURRENTLY verandert (en wat niet)

Een normale CREATE INDEX neemt een sterke lock die schrijfbewerkingen blokkeert voor de duur. CREATE INDEX CONCURRENTLY is bedoeld om normale lees- en schrijfbewerkingen door te laten terwijl de index wordt opgebouwd.

Maar "concurrent" betekent niet "zonder locks". Je krijgt nog steeds korte lock‑vensters aan het begin en einde, en de build kan mislukken of wachten als iets anders incompatibele locks heeft.

De uitkomsten die het meest tellen:

• Niet-concurrente builds kunnen inserts, updates en deletes op de tabel blokkeren.
• Concurrente builds laten meestal lezen en schrijven toe, maar kunnen vertraagd of vastlopen door langlopende transacties.
• De afrondende stappen hebben nog steeds korte locks nodig, dus zeer drukke systemen kunnen korte wachttijden zien.

Kies de juiste aanpak: concurrent of niet

Je hebt twee hoofdopties bij het wijzigen van indexen: de index normaal bouwen (snel, maar blokkerend), of bouwen met CONCURRENTLY (meestal niet-blokkerend voor applicatieverkeer, maar langzamer en gevoeliger voor langlopende transacties).

Wanneer CONCURRENTLY de juiste keuze is

Gebruik CREATE INDEX CONCURRENTLY wanneer de tabel echt verkeer bedient en je schrijfbewerkingen niet kunt pauzeren. Het is doorgaans de veiligere keuze als:

• De tabel groot genoeg is dat een normale build minuten of uren kan kosten.
• De tabel constante schrijfbewerkingen heeft, niet alleen lezen.
• Je geen echt onderhoudsvenster kunt inplannen.
• Je eerst wilt bouwen en verifiëren, en later de oude index wilt verwijderen.

Wanneer een normale indexbuild acceptabel is

Een normale CREATE INDEX kan prima zijn als de tabel klein is, het verkeer laag of je een gecontroleerd venster hebt. Het is vaak sneller en eenvoudiger uit te voeren.

Overweeg de normale aanpak als de build in staging consequent snel voltooit en je tijdelijk schrijfbewerkingen kunt stoppen (zelfs kort).

Als je uniciteit nodig hebt, beslis vroeg. CREATE UNIQUE INDEX CONCURRENTLY werkt, maar faalt als er duplicaten bestaan. In veel productiesystemen is het vinden en oplossen van duplicaten het echte project.

Preflight-checks voordat je productie aanraakt

De meeste problemen beginnen voordat het commando zelfs maar gestart is. Een paar checks helpen je twee grote verrassingen te voorkomen: onverwachte blokkering en een indexbuild die veel langer (of meer ruimte) gebruikt dan gepland.

1. Zorg dat je niet in een transactie zit. CREATE INDEX CONCURRENTLY faalt als je het na BEGIN runt, en sommige GUI-tools verpakken statements stilletjes in een transactie. Open een verse sessie en run alleen het indexcommando als je het niet zeker weet.

2. Stel verwachtingen voor tijd en schijf in. Concurrente builds duren meestal langer dan normale builds en hebben extra werkruimte nodig. Plan voor de nieuwe index plus tijdelijke overhead en bevestig dat je comfortabele vrije schijfruimte hebt.

3. Stel timeouts die bij je doel passen. Je wilt dat de build snel faalt als hij geen lock krijgt, maar je wilt niet dat de sessie middenin sterft door een agressieve statement timeout.

4. Maak een baseline. Je wilt bewijs dat de wijziging helpt en een snelle manier om regressies te signaleren. Neem een before-snapshot: de trage querytijden, een representatieve EXPLAIN (ANALYZE, BUFFERS), en een snelle kijk op CPU, IO, verbindingen en vrije schijf.

Veilige sessie-instellingen die veel teams als startpunt gebruiken (pas aan naar je regels):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Stapsgewijs: een index maken met CONCURRENTLY

Gebruik CREATE INDEX CONCURRENTLY wanneer je applicatieverkeer wilt laten doorlopen en je langere buildtijd kunt toestaan.

Bepaal eerst precies wat je bouwt:

• Wees specifiek over kolomvolgorde (het doet er toe).
• Overweeg of een partial index genoeg is. Als de meeste queries filteren op "active" rijen, kan een partial index kleiner, sneller en goedkoper in onderhoud zijn.

Een veilige uitvoering ziet er zo uit: schrijf het doel en de indexnaam op, run de build buiten een transactieblok, monitor tot het klaar is en verifieer dan dat de planner hem kan gebruiken voordat je iets anders verwijdert.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Voor voortgangsnotities (handig voor audits), noteer starttijd, eindtijd en eventuele waits die je zag. Terwijl het draait kun je pg_stat_progress_create_index vanuit een andere sessie queryen.

Validatie is niet alleen "de index bestaat." Bevestig dat de planner hem kan kiezen en bekijk daarna echte querytijden na deployment. Als de nieuwe index niet gebruikt wordt, verwijder de oude dan niet overhaast. Los de query of indexdefinitie eerst op.

Stapsgewijs: indexen vervangen of verwijderen zonder blokkade

Het veiligste patroon is eerst toevoegen, het verkeer laten profiteren van de nieuwe index en pas daarna de oude verwijderen. Zo houd je een werkende fallback.

Een oude index wisselen voor een nieuwe (veilige volgorde)

1. Maak de nieuwe index aan met CREATE INDEX CONCURRENTLY.

2. Verifieer dat hij gebruikt wordt. Check EXPLAIN op de trage queries die je belangrijk vindt en monitor indexgebruik over tijd.

3. Pas daarna, en alleen dan, drop de oude index concurrent. Als het risico hoog is, houd beide indexen een volledige business cycle voordat je iets verwijdert.

Indexen droppen: wanneer CONCURRENTLY werkt (en wanneer niet)

Voor een normale index die je zelf hebt aangemaakt, is DROP INDEX CONCURRENTLY doorgaans de juiste keuze. Twee aandachtspunten: het kan niet in een transactieblok draaien en het heeft nog steeds korte locks aan begin en einde, dus het kan vertraagd worden door langlopende transacties.

Als de index bestaat vanwege een PRIMARY KEY of UNIQUE constraint, kun je hem meestal niet direct droppen. Je moet de constraint veranderen met ALTER TABLE, wat sterkere locks kan vereisen. Behandel dat als een aparte, geplande onderhoudsactie.

Indexnamen hernoemen voor duidelijkheid

Hernoemen (ALTER INDEX ... RENAME TO ...) is meestal snel, maar vermijd het als tooling of migrations naar indexnamen verwijzen. Een veiliger gewoonte is vanaf het begin een duidelijke naam kiezen.

Als de oude index nog nodig is

Soms hebben twee querypatronen twee verschillende indexen nodig. Als belangrijke queries nog steeds op de oude vertrouwden, houd die dan. Overweeg de nieuwe index aan te passen (kolomvolgorde, partial condition) in plaats van de oude geforceerd te droppen.

Monitor locks en voortgang terwijl de index bouwt

Zelfs met CREATE INDEX CONCURRENTLY moet je in realtime kijken wat er gebeurt. De meeste verrassende incidenten komen door één van twee dingen: een blocker-sessie die je over het hoofd zag, of een langlopende transactie die de build laat wachten.

Blokkerende sessies vinden (wie blokkeert wie)

Begin met het vinden van sessies die op locks wachten:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Als je de exacte blocker nodig hebt, volg blocked_pid naar blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Bouwvoortgang en "vastgelopen" signalen in de gaten houden

PostgreSQL toont voortgang van indexbuilds. Als je lange tijd geen beweging ziet, zoek dan naar een langlopende transactie (vaak een idle-sessie die een oude snapshot vasthoudt).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Houd ook systeemdruk in de gaten: schijf‑IO, replicatievertraging en oplopende querytijden. Concurrente builds zijn vriendelijker voor uptime, maar ze lezen nog steeds veel data.

Eenvoudige regels die goed werken in productie:

• Wacht als de voortgang beweegt en de gebruikersimpact laag is.
• Annuleer en plan opnieuw als de build vastzit achter een langlopende transactie die je niet veilig kunt beëindigen.
• Pauzeer tijdens piekverkeer als IO klantgerichte queries schaadt.
• Terminate alleen als laatste redmiddel, en alleen na bevestiging wat de sessie doet.

Voor teamcommunicatie: houd updates kort: starttijd, huidige fase, wat er geblokkeerd is (indien van toepassing) en wanneer je weer kijkt.

Rollback-plan: hoe veilig terugdraaien

Indexwijzigingen blijven alleen laag-risico als je de uitgang plant voordat je start. De veiligste rollback is vaak geen dramatische ongedaanmaking. Het is simpelweg het nieuwe werk stoppen en de oude index laten staan.

Veelvoorkomende manieren waarop indexwerk faalt

De meeste productiefouten zijn voorspelbaar: de build raakt een timeout, iemand annuleert tijdens een incident, de server raakt schijfruimte kwijt, of de build concurreert genoeg met normaal verkeer dat gebruikerslatentie stijgt.

Bij CREATE INDEX CONCURRENTLY is annuleren meestal veilig voor de app omdat queries blijven draaien. De afweging is cleanup: een geannuleerde of gefaalde concurrent build kan een ongeldige index achterlaten.

Veilige cancel- en opschoonregels

Het annuleren van een concurrent build rolt niet terug als een normale transactie. PostgreSQL kan een index achterlaten die bestaat maar niet geldig is voor de planner.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Bevestig voordat je droppt wat je ziet:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Als indisvalid = false is, wordt hij niet gebruikt en is het veilig om hem te droppen.

Praktische rollback-checklist bij het vervangen van een bestaande index:

• Houd de oude index totdat de nieuwe volledig gebouwd en geldig is.
• Als de nieuwe build faalt of geannuleerd wordt, drop dan de ongeldige nieuwe index concurrent.
• Als je de oude al had gedropt, recreate hem met CREATE INDEX CONCURRENTLY om de vorige staat te herstellen.
• Als schijfdruk de fout veroorzaakte, maak eerst ruimte vrij en probeer opnieuw.
• Als timeouts de fout veroorzaakten, plan een rustiger venster in plaats van forceren.

Voorbeeld: je start een nieuwe index voor een admin-zoekopdracht, hij draait 20 minuten en er gaan diskalerts af. Annuleer de build, drop de ongeldige index concurrent en houd de oude index actief. Je kunt het later opnieuw proberen na het vrijmaken van ruimte, zonder zichtbare gebruikersstoring.

Veelgemaakte fouten die onverwachte outages veroorzaken

De meeste outages rond indexen komen niet doordat PostgreSQL "traag" is. Ze ontstaan doordat één klein detail een veilige wijziging in een blokkerende verandert.

1) Een concurrent build binnen een transactie plaatsen

CREATE INDEX CONCURRENTLY kan niet binnen een transactieblock draaien. Veel migratietools wrappen standaard elke wijziging in één transactie. Het resultaat is ofwel een harde fout (beste geval) of een rommelige deploy met retries.

Controleer voordat je de migratie runt of je tool statements zonder een buitenste transactie kan uitvoeren, of splits de migratie in een speciale niet-transactionele stap.

2) Starten tijdens piektraffic

Concurrente index builds verminderen blokkering, maar voegen nog steeds load toe: extra lezen, extra schrijven en meer druk op autovacuum. Een build starten tijdens een deploywindow waarin traffic piekt is een veelvoorkomende manier om een vertraging te creëren die als een outage aanvoelt.

Kies een rustig moment en behandel het als elke andere productieonderhoudsactie.

3) Langlopende transacties negeren

Een enkele langlopende transactie kan de cleanup-fase van een concurrent build tegenhouden. De index kan vooruitgang tonen en vervolgens lang stil blijven staan terwijl hij wacht op het verdwijnen van oude snapshots.

Maak er een gewoonte van: check op langlopende transacties voordat je start en opnieuw als de voortgang stagneert.

4) Het verkeerde object droppen (of een constraint breken)

Teams droppen soms uit het hoofd een index op naam, of verwijderen een index die een uniciteit afdwingt. Als je het verkeerde object dropt, verlies je handhaving (unique constraints) of degradeer je queryprestaties direct.

Snelle veiligheidscheck: verifieer de indexnaam in de catalogus, bevestig of hij een constraint ondersteunt, controleer schema en tabel dubbel, en houd "create new" gescheiden van "drop old." Heb het rollback-commando klaar voordat je start.

Realistisch voorbeeld: een admin-zoekopdracht versnellen

Een veelvoorkomend pijnpunt is een admin-zoekopdracht die in staging instant lijkt maar in productie kruipt. Stel: je hebt een grote tickets-tabel (tientallen miljoenen rijen) achter een intern adminpaneel, en agenten zoeken vaak op "open tickets voor één klant, nieuwste eerst."

De query ziet er zo uit:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Een volledige index op (customer_id, status, created_at) helpt, maar voegt schrijf-overhead toe voor elke ticketupdate, ook gesloten tickets. Als de meeste rijen niet open zijn, is een partial index vaak een eenvoudiger winst:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Een veilig tijdspad in productie:

• Preflight: bevestig dat de queryvorm stabiel is en dat de tabel genoeg vrije schijfruimte heeft voor een nieuwe indexbuild.
• Build: run CREATE INDEX CONCURRENTLY in een aparte sessie met duidelijke timeout-instellingen.
• Validate: run ANALYZE tickets; en bevestig dat de planner de nieuwe index gebruikt.
• Cleanup: zodra je zeker bent, drop je overbodige oudere index met DROP INDEX CONCURRENTLY.

Wat succes eruitziet:

• De admin-zoekopdracht gaat van seconden naar tientallen milliseconden voor typische klanten.
• Normale lees- en schrijfbewerkingen blijven werken tijdens de build.
• CPU en schijf‑IO stijgen tijdens de build maar blijven binnen je normale veiligheidsmarges.
• Je kunt duidelijke voor/na-cijfers laten zien: querytijd, gescande rijen en lockhistorie.

Snelle checklist en volgende stappen

Indexwerk is het veiligst als je het behandelt als een kleine productie-release: bereid voor, kijk terwijl het draait en verifieer het resultaat voordat je opschoont.

Voordat je start:

• Stel timeouts in zodat een onverwachte lock niet eeuwig hangt.
• Bevestig voldoende vrije schijfruimte voor de nieuwe indexbuild.
• Zoek naar langlopende transacties die de build kunnen vertragen.
• Kies een laag‑traffic venster en definieer wat "klaar" betekent.
• Schrijf je rollback‑plan nu op.

Terwijl het draait:

• Let op blokkeringen en lock-wachtketens.
• Volg buildvoortgang met pg_stat_progress_create_index.
• Houd app-symptomen in de gaten: foutpercentages, time-outs en trage endpoints gekoppeld aan de tabel.
• Wees klaar om te cancelen als lock-wachten stijgen of gebruikersgerichte time-outs omhoogschieten.
• Log wat er gebeurde: starttijd, eindtijd en eventuele alerts.

Als het klaar is, bevestig dat de index geldig is, draai de één of twee sleutelqueries om te zien dat plan en timing verbeteren, en verwijder pas daarna oude indexen op een niet-blokkerende manier.

Als je dit vaker doet, maak er een herhaalbare leveringsstap van: een klein runbook, een stagingrepetitie met productie-achtige data en een duidelijke eigenaar die de build bewaakt.

Als je interne tools of adminpanelen bouwt met AppMaster (appmaster.io), helpt het om databaseveranderingen zoals indexbuilds op te nemen in dezelfde release-checklist als je backend-updates: gemeten, gemonitord en met een rollback die je snel kunt uitvoeren.