PostgreSQL: Hva jeg skulle ønske noen hadde fortalt meg i starten

Dette er manuset fra en presentasjon jeg holdt på TDC2025.

Denne presentasjonen fokuserer på konkrete tips for å unngå stressende situasjoner når man kjører på postgres i produksjon. Jeg har brent meg kraftig på noen ting som enkelt kan unngås. Jeg vil motivere tipsene, og så forklare hvordan man tar det i bruk. Jeg liker å tro at jeg hadde vært yngre og sprekere om noen hadde holdt akkurat denne presentasjonen for meg for omtrent 10 år siden, så her er det bare å følge med.

Tålmodighetsprøven

Jeg har hørt at det er lurt å ha litt publikumsdeltakelse når man presenterer på konferanse, så jeg har designet en tålmodighetsprøve til dere. Unnskyld.

Jeg vil at dere skal se for dere at det er siste fredag i måneden, rett før arbeidsdagen er over. Det eneste som gjenstår før dere kan dra fra jobb er å levere timeliste. Når jeg går til neste slide vil den vise en spinner mens timelista laster. Vanligvis går dette fort, men ikke i dag.

Så vil jeg at dere skal rekke opp hånda hvis dere mister tålmodigheten og får lyst til å prøve å laste sida på nytt. Klar, ferdig, kjør!

10 sekunder er lang tid

Omtrent halvparten av rommet rekker opp hånda før 10 sekunder har gått. Det var mindre enn jeg forventet, men mer enn nok til å illustrere poenget.

Det var to poeng med tålmodighetsprøven. For det første, så er det ikke så ofte at jeg får måle tålmodigheten til en sal full av mennesker på denne måten. For det andre, så er det viktig at vi vet at mennesker har timeouts.

Det har ikke en fabrikkny postgres-server. Når vi setter i gang en spørring, så jobber postgres med den til resultatet er klart, uansett om det tar 1 millisekund eller 1 år. Det betyr at alle dere som rakk opp hånda har minst 2 spørringer som kjører i databasen nå. Resten av dere har jo også én!

Tipper databaseserveren til timeføringssystemet begynner å få litt vondt, håper ingen trenger den til noe viktig...

Velg dine feil med omhu

I en databaseserver som ikke har nok ressurser, så hoper arbeidet seg fort opp. Operativsystemet forsøker å fordele ressurser rettferdig mellom alle spørringene som trenger dem. Alt begynner å ta lang tid, også spørringer som vanligvis går veldig fort.

På applikasjonssiden kan vi kanskje begynne å se feil oppstå. For eksempel kan vi gå tom for databasetilkoblinger i connection pools, file descriptors, RAM eller andre ressurser som vi trenger. Det er mange mulige symptomer, og siden alt går seint, er det vanskelig å feilsøke.

Nå havner vi fort i en situasjon hvor vi begynner å behandle symptomer i stedet for rotårsak. Kanskje vi øker størrelsen på en connection pool - noe som gjør at vi slipper til flere samtidige timelistespørringer, og forverrer problemet vårt.

Heldigvis finnes det en enkel innstilling vi kan sette opp for å velge en helt annen måte å feile på!

Ocarina of (statement_)time(out)

Jeg synes det viktigste vi kan gjøre når vi setter opp en helt ny applikasjon på postgres er å sette en statement_timeout. Da kan vi få databaseserveren til å terminere treige spørringer, i stedet for å la dem bruke opp alle ressursene.

statement_timeout gjelder for hver enkelt spørring individuelt, ikke for en hel transaksjon.

På applikasjonssiden får vi en exception som gir oss en stacktrace. Den vil stå som en laserpeker og lyse på den treige spørringen. Databasen står og roper; "Der er den, det er den spørringen som gjør vondt!"

Feilsøking blir mye lettere med én gang. Det blir lett. Vi kommer tilbake til hvordan vi kan finne gode timeout-verdier for systemer som allerede har produksjonstrafikk. For helt nye systemer, så kan vi velge en ganske lav verdi, for eksempel ett sekund eller lavere.

Konfigurasjon med URL-parametre

jdbc:postgresql://...?statement_timeout=1s

Vi kan konfigurere de fleste innstillinger i postgres som URL-parametre i tilkoblingsstrengen. Dette fungerer i de fleste språk som har en postgres-driver, og formatet og navnene er like på tvers av språk.

Konfigurasjon med SQL

hikariConfig.setConnectionInitSql(
   "SET statement_timeout='1s';"
)

Hvis vi ikke kan justere tilkoblingsstrengen, så er det heldigvis lett å endre konfigurasjon også med SQL. Som oftest har vi mulighet til å legge inn en snutt SQL som vil bli kjørt hver gang en ny databasetilkobling opprettes. På JVMen, der hvor jeg kommer fra, så bruker vi ofte HikariCP og den har en connectionInitSql som er perfekt for slike ting som dette.

Innstillinger som vi setter i URLen, eller på denne måten, gjelder for hele levetida til en databasetilkobling.

Konfigurasjon for én transaksjon

statement.execute(
    "SET LOCAL statement_timeout='10s';"
)

Det er mulig å overstyre slik konfigurasjon for én enkelt transaksjon også. Da kan vi bruke SET LOCAL, slik at konfigurasjonen gjelder bare til transaksjonen vår fullfører, eller ruller tilbake.

Vi kan bruke dette til å kjøre enkelte spørringer med lengre timeout, dersom vi har noen som vi vet trenger lang tid. Men her kan det også være smart å kjøre slike spørringer i en egen connection pool. Det gir oss muligheten til å begrense hvor mange av dem som kjører samtidig, slik at vi passer på å ha nok systemressurser igjen til de spørringene som må kjøre fort.

Det er ofte rapporteringsspørringer som trenger slik spesialbehandling, og noen ganger kan vi til og med kjøre dem mot en read-only replica av databaseserveren, sånn at vi ikke påvirker spørringer som må gå fort.

Idle in transaction

Dette bildet er jeg veldig fornøyd med. Den har to tikkende bomber i seg. Den ene er en faktisk bombe, ved Link sin høyre fot. Den andre er bare en metaforisk bombe, men den er verre, fordi den kan eksplodere et helt annet sted enn vi venter.

En databasetilkobling som er idle in transaction er en tilkobling som applikasjonen har startet med, og så glemt. Applikasjonen driver på med noe annet, men transaksjonen er enda åpen.

Dette er litt som å slenge en grandis i ovnen, for så å legge seg på sofaen og ta en blund. Det kan jo gå bra dét, men det kan også gå galt.

Det typiske her er at vi har kjørt noen spørringer, og så begynt å gjøre et HTTP-kall eller sende en epost. Det som er skummelt med denne situasjonen er at vår aktive transaksjon kan holde på låser eller ressurser som en annen transaksjon trenger. Da må den andre transaksjonen vente på oss. Hvis den også holder på noe som andre transaksjoner trenger igjen, så kan vi få en dominoeffekt hvor veldig mange tilkoblinger slutter å jobbe.

Denne situasjonen er vanskelig å feilsøke uten å koble seg opp til databasen for å kjøre noe SQL. Det er veldig stressende å kjøre SQL mot en syk database i produksjon, så det ønsker vi å unngå.

Komfyrvakt for databasetilkoblinger

SET idle_in_transaction_session_timeout='5s';

Heldigvis så har postgres en innebygget komfyrvakt. Den heter idle_in_transaction_session_timeout. Dersom vi setter opp dette, så unngår vi dominoeffekten når en eller annen transaksjon henger seg opp. Postgres terminerer tilkoblingen, slik at den ikke kan blokkere samtidige transaksjoner.

Dessverre gir ikke dette oss flotte, forklarende stacktraces. Feilen blir først synlig i applikasjonen neste gang databasetilkoblingen blir brukt. Da peker stacktracen på den spørringen vi skulle kjøre neste gang, og problemet inntraff før vi begynte å kjøre den. Derfor er det også viktig å sette korte timeouts på alle operasjoner vi har tenkt til å gjøre mens vi holder en databasetransaksjon åpen!

Bonustips for postgres 17

SET transaction_timeout='5s';

For de heldige som har oppgradert til postgres 17 allerede, så finnes det en transaction_timeout som løser begge problemene som gjør at vi trenger statement_timeout og idle_in_transaction_session_timeout. Da kan postgres terminere tilkoblinger som holder på for lenge, uansett årsak, med 1 innstilling.

Det er nok også litt lettere for oss å ha en god intuisjon for timeout-verdien til en transaksjon, enn for én enkelt spørring. Sånn sett er denne kanskje lettere å bruke.

Tryggere migreringer

Migreringer er de scriptene vi bruker til å endre på struktur og data i databasen vår. For eksempel ved å legge til kolonner, indekser eller tabeller. Noen systemer får så mye trafikk at det krever kløkt å kjøre slike endringer ut, uten nedetid. Vi kan konfigurere oss ut av noen slike problemer. La oss snakke om låsekøen!

Låsekøen

Alle SQLer vi kjører trenger å låse tabeller i databasen, så alle tabeller har en kø av låser. De fleste låsene er av den søte, lille typen.

Databaseserveren kan gi slike låser til mange transaksjoner samtidig. En transaksjon trenger en slik lås for å gjøre SELECT mot en tabell. Figuren viser også en stor og skummel lås, typisk noe som vi trenger for å gjøre ALTER TABLE. Slike låser kan kun gis dersom ingen andre transaksjoner har noen som helst lås på tabellen.

Derfor slipper ikke de små, søte SELECT-låsene forbi. Det betyr at med en gang vi kjører en ALTER TABLE, så kan vi komme i skade for å blokkere produksjonsstrafikk, som må vente på oss til vi har fått vår lås fra databaseserveren.

Derfor kan det være skummelt å kjøre migreringer mot tabeller som er brukt av treige transaksjoner.

Det finnes en timeout for dette!

SET lock_timeout='1s';

Ved å sette lock_timeout når vi kjører migreringer, så kan vi kontrollere hvor lang tid vanlig produksjonstrafikk kan måtte vente på at migreringene får tatt låsene som de trenger. Det kan være fornuftig å sette denne til en lavere verdi enn statement_timeout, slik at migreringene gir opp før vanlig trafikk.

Dette vil føre til at noen migreringer feiler med en lock_timeout. Dette er ikke så farlig. Postgres har transaksjonelle migreringer - endringer vi klarte å få til, blir rullet tilbake og vi kan prøve hele migreringen på nytt, som én enhet. Det gjør ikke så mye om vi må prøve flere ganger, så lenge brukerne våre ikke merker noe.

Se all SQLen én gang til

Det er denne vi trenger når vi skal sette opp timeouts i et system som allerede er i produksjon, men vi ikke aner hva timeout-verdiene burde være.

Denne gir oss muligheten til å finne gjennomsnittlig kjøretid, maksimal kjøretid, variasjon og mye annet for alle spørringene som har blitt kjørt mot databasen noen gang.

Skru det på

create extension pg_stat_statements;

For å ta det i bruk, må vi kjøre en create extension i databasen.

Resette statistikken

select pg_stat_statements_reset();

Siden både spørringer og ytelse endrer seg over tid, så må vi av og til resette statistikken. Da kastes all statistikken som er samlet til nå, og innsamlingen begynner på nytt. Dette er ikke en farlig operasjon, og det er nødvendig å gjøre det en gang i blant. Statistikken kan for eksempel bli påvirket veldig mye av nedetid.

Finne spørringer som trenger lang tid

select calls, max_exec_time, query
from pg_stat_statements 
where max_exec_time > 1000; -- unit is ms

pgstatstatements gir oss et view som vi kan bruke til å finne SQLer som bruker lang tid. Eller å finne ut av hvor lang tid spørringene typisk tar.

Denne spørringen henter alle SQLer som har brukt mer enn 1 sekund minst én gang. Tallene herfra gir oss et godt utgangspunkt for å sette timeouts. Det er mye bedre å ha en lang timeout enn ingen timeout, du kan gjerne legge inn en liten buffer! Vi trenger timeouts for å forhindre at et lite antall brukere kan ta ned databaseserveren vår, det er helt nødvendig for å lage robuste systemer.

Oppsummering: Velg dine feil med omhu

Bruk timeouts! Disse er verdt å se på:

• Tålmodighetsprøven: statement_timeout
• Komfyrvakt: idle_in_transaction_session_timeout
• For postgres 17: transaction_timeout
• For migreringer: lock_timeout

Det vi ikke rekker

Det var mange andre ting jeg også gjerne ville vise, men det rekker vi ikke å se på i detalj.

Jeg har laget et verktøy som heter eugene som kan hjelpe til med å lage tryggere migreringer. Det kan analysere eller kjøre SQL, og gi gode tips til tryggere måter å oppnå samme effekt på.

Ellers blogger jeg ofte om postgres og SQL på arktekk-bloggen. Her finnes det for eksempel tips om hvordan man kan passe på at man har indekser som man trenger.

Den offisielle dokumentasjonen er veldig god. Jeg har lest denne fra perm til perm mange ganger, og jeg lærer noe nytt hver gang. Den er mye mer tilgjengelig enn man skulle tro.

Her er det spesielt to sider jeg kan anbefale å se på. auto_explain er en extension som automatisk kan logge query plans for spørringer som er treigere enn en terskelverdi. Dette er akkurat det du vil ha dersom du plages med spørringer som er treige bare noen ganger.

pgstatactivity er et view som viser aktiviteten til alle tilkoblingene som er i databaseserveren akkurat nå. Dette er litt som top eller ps for databaseserveren din, og det er akkurat dette du trenger om trafikken i databasen plutselig har låst seg.

Takk for meg! Ta gjerne tak i meg om du har glede av å snakke SQL, eller om det er noe du lurer på.