Prestandaoptimering och frågeoptimering i PostgreSQL · Lektion

Avlasta läs- och analysarbetsbelastningar

Dirigera tung rapporteringstrafik till logiska repliker för att skydda OLTP-latensen på primärservern.

Lektion 2 av 413 steg

Avlasta läs- och analysarbetsbelastningar är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 2 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Varför avlasta läsningar över huvud taget?

På en belastad PostgreSQL-primary hanterar samma instans både OLTP-trafik (små, snabba och latenskänsliga skrivningar samt punktläsningar) och analytisk trafik (stora sekventiella skanningar, aggregationer och rapportkopplingar).

Problemet är att en enda tung rapportfråga kan mätta delade buffertar, tränga undan heta OLTP-sidor och öka I/O-köernas djup. Din p99-latens för utcheckning fördubblas plötsligt eftersom någon körde en kvartalsrapport över intäkterna.

Mål: håll primary-instansen slimmad för transaktionsarbete.
Strategi: dirigera tunga läs- och analysfrågor till en replik som innehåller en kopia av data.

Den här lektionen fokuserar på att använda logisk replikering för att skapa ändamålsenliga läsrepliker för analys.

Fysisk kontra logisk replikering

PostgreSQL erbjuder två replikeringmodeller, och att välja rätt är det viktigaste beslutet för att avlasta läsningar.

Fysisk (strömmande) replikering: skickar WAL byte för byte. Replikan är en exakt klon på blocknivå — samma schema, samma index och samma bloat. Utmärkt för HA och för att skala läsningar av identiska frågor.
Logisk replikering: avkodar WAL till ändringar på radnivå (INSERT/UPDATE/DELETE) och spelar upp dem via SQL. Abonnenten är en oberoende databas — Ni kan lägga till andra index, extra kolumner, materialiserade sammanställningar eller till och med använda en annan huvudversion.

För analytisk avlastning är logisk replikering särskilt bra: analysnoden kan ha tunga rapporteringsindex som primärnoden aldrig behöver betala för att underhålla.

Konfigurera en publication

På primärnoden ( publisher ) måste Ni ange wal_level = logical och skapa en publication — en namngiven uppsättning tabeller vars ändringar ska strömmas.

Ni kan publicera alla tabeller, ett urval eller till och med filtrera rader och kolumner (PostgreSQL 15+). För analytisk avlastning publicerar Ni vanligtvis exakt de fakta- och dimensionstabeller som rapporterna behöver.

-- On the primary: declare what gets replicated
-- (requires wal_level = logical in postgresql.conf)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders, order_items, customers
  WITH (publish = 'insert, update, delete');

-- Inspect existing publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

Skapa subscribern

På en separat värd — Er analysreplika — skapar Ni ett matchande schema (åtminstone de publicerade tabellerna) och därefter en subscription. Subscriptionen ansluter till publishern och startar en inledande datakopiering följd av kontinuerlig strömning.

Eftersom subscribern är oberoende kan Ni ge den den konfiguration som analyserna behöver: mer work_mem, mer maintenance_work_mem och inställningar anpassade för parallell körning — utan att röra OLTP-primärnoden.

-- On the analytics node: subscribe to the primary's publication
CREATE SUBSCRIPTION analytics_sub
  CONNECTION 'host=primary.db port=5432 dbname=shop user=repl password=secret'
  PUBLICATION analytics_pub
  WITH (copy_data = true, streaming = on);

-- Watch initial sync + streaming status
SELECT subname, srrelid::regclass AS rel, srsubstate
FROM pg_subscription_rel
JOIN pg_subscription ON oid = srsubid;

Lägg till enbart analytiska index på replikan

Det är här den logiska replikeringen ger utdelning. Primärnoden behåller bara de slimmade index som OLTP behöver. Analysnoden lägger till breda, kostsamma index som skulle göra varje skrivning på primärnoden långsammare, men som gör rapporterna snabba på replikan.

BRIN-index för enorma append-only-faktatabeller med tidsserier.
Täckande / partiella index anpassade för frågor från dashboards.
Uttrycksindex för rapportspecifika predikat.

Att underhålla dessa här kostar analysnoden extra skrivningar — men primärnoden betalar aldrig för det.

-- These indexes live ONLY on the analytics subscriber
CREATE INDEX brin_orders_created
  ON orders USING brin (created_at) WITH (pages_per_range = 32);

CREATE INDEX idx_orders_report
  ON orders (customer_id, created_at)
  INCLUDE (total_amount, status)
  WHERE status <> 'cancelled';

Dirigera läsningar i applikationslagret

Avlastning hjälper bara om trafiken faktiskt når replikan. Ni dirigerar trafiken i applikations- eller proxylagret:

Uppdelning på anslutningsnivå: en läs-/skrivpool till primärnoden och en skrivskyddad pool till analysnoden.
Proxybaserat: PgBouncer/Pgpool eller ett servicemesh dirigerar rapporteringstrafik med SELECT utifrån användare, schema eller frågetagg.

Ett tydligt mönster är en särskild databasroll, analytics_ro, med tidsgränser för satser och lägre prioritet, så att en rapport som skenar aldrig kan hota de transaktionella SLA:erna.

-- Give reporting sessions a safety harness on the replica
ALTER ROLE analytics_ro SET statement_timeout = '120s';
ALTER ROLE analytics_ro SET work_mem = '256MB';
ALTER ROLE analytics_ro SET default_transaction_read_only = on;

-- Confirm where a session actually landed (run on each node)
SELECT pg_is_in_recovery() AS is_replica, current_setting('work_mem');

Föraggregera med materialiserade vyer

Replikan är den perfekta platsen för materialiserade vyer som beräknar kostsamma sammanställningar i förväg. Dashboards läser då en liten sammanfattad tabell i stället för att genomsöka miljontals faktarader vid varje laddning.

Uppdatera dem enligt ett schema på analysnoden. Om Ni använder REFRESH MATERIALIZED VIEW CONCURRENTLY blockeras inte läsare under ombyggnaden (det kräver ett unikt index på vyn).

-- Lives on the analytics replica only
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       count(*)            AS order_count,
       sum(total_amount)   AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);

-- Scheduled refresh that does not block dashboard readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Förstå replikeringens eftersläpning

Logisk replikering är som standard asynkron: subscribern ligger efter primärnoden med viss eftersläpning. För analyser är detta nästan alltid acceptabelt — en rapport baserad på data som är två sekunder gammal fungerar bra. För UI-flöden med ”läs det Ni nyss skrev” gör det inte det.

Beslutsregel: skicka så småningom konsekventa, aggregatintensiva läsningar till replikan; behåll läsningar efter skrivning och läsningar som kräver exakt aktuellt tillstånd på primärnoden.

Övervaka eftersläpningen från publisherns replikeringsslot — en stannad subscriber gör att WAL ansamlas och kan fylla primärnodens disk.

-- On the primary: how far behind is each logical slot?
SELECT slot_name,
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Filtrera ned till det analyserna behöver

Ni behöver sällan alla kolumner eller rader på analysnoden. PostgreSQL 15+ låter en publication använda radfilter och kolumnlistor, vilket minskar den replikerade datamängden och kostnaden för WAL-avkodning.

Radfilter: replikera bara rader som är relevanta för rapporteringen (t.ex. order som inte har arkiverats).
Kolumnlista: uteslut breda eller känsliga kolumner (personuppgifter och stora JSON-objekt) som rapporterna aldrig använder.

Ett mindre replikerat datamängdsavtryck innebär snabbare inledande synkronisering och mindre nätverks- och avkodningsöverhead.

-- Publish only relevant rows and a subset of columns (PG 15+)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders (id, customer_id, created_at, total_amount, status)
    WHERE (status <> 'draft' AND created_at >= '2024-01-01');

Verifiera att planen använder replikans index

Efter att Ni har byggt analytiska index och materialiserade vyer på subscribern ska Ni bekräfta att frågeplaneraren faktiskt använder dem. Kör EXPLAIN (ANALYZE, BUFFERS) på analysnoden för Era verkliga rapportfrågor.

Ni bör se indexskanningar eller bitmap-skanningar på de rapportspecifika indexen och helst att dashboard-frågan använder den materialiserade vyn i stället för den råa faktatabellen. Jämför buffertar med shared read före och efter — minskningen är den I/O som Ni har flyttat bort från primärnoden.

-- Run on the analytics replica, not the primary
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT day, revenue
FROM daily_revenue
WHERE day >= current_date - interval '30 days'
ORDER BY day;

Operativa skyddsräcken

En arkitektur för läsavlastning är bara så bra som dess felhantering. Bygg in skyddsräcken:

Varningar vid eftersläpning: larma när retained_wal eller apply-eftersläpningen överskrider ett tröskelvärde — en död subscriber kan fylla primärnodens disk via slotten.
Sekvenser och DDL replikeras inte: logisk replikering kopierar raddata, inte sekvensvärden eller schemaändringar. Hantera DDL medvetet på båda sidor.
Var medveten om konflikter: låt aldrig applikationer skriva till replikerade tabeller på subscribern, annars slutar apply att fungera.
Kapacitet: analysnoden kan vara mindre när det gäller antal anslutningar, men behöver RAM och disk för sina extra index och materialiserade vyer.

Snabbkontroll: välj mål för avlastningen

En dashboard kör en 9 sekunder lång aggregering över 200M orderrader varje gång en chef öppnar den, och den försämrar svarstiden för OLTP-kassan på primärnoden. Rapporten tolererar data som är några sekunder gammal. Vad är det bästa steget?

Besvara frågan i den interaktiva lektionen

Sammanfattning: avlasta läsningar med logisk replikering

Ni har lärt Er att skydda OLTP-svarstiden på primärnoden genom att dirigera tung rapporteringstrafik till en specialiserad logisk replika.

Logisk framför fysisk när analysnoden behöver egna index, kolumner eller sammanställningar.
Publicera exakt de tabeller (och rader/kolumner) som rapporterna behöver med en PUBLICATION; konsumera dem med en SUBSCRIPTION.
Lägg till enbart analytiska index och materialiserade vyer på subscribern, så att primärnoden aldrig behöver betala för rapporteringsstrukturer.
Dirigera läsningar via pooler/proxyer och en begränsad skrivskyddad roll med tidsgränser för satser.
Skicka bara läsningar som tål inaktuell data och är aggregatbaserade till replikan; behåll läsningar efter skrivning på primärnoden.
Övervaka replikeringsslotens eftersläpning — en stannad subscriber kan fylla primärnodens disk.

Resultatet: dashboards får snabb, dedikerad infrastruktur och Era transaktionella SLA:er förblir säkra.

Gratis att börja

Lär dig SQL med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 22
Lektioner: 88

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Avlasta läs- och analysarbetsbelastningar” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”Avlasta läs- och analysarbetsbelastningar”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad lär jag mig i ”Avlasta läs- och analysarbetsbelastningar”?

Dirigera tung rapporteringstrafik till logiska repliker för att skydda OLTP-latensen på primärservern. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?

Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 2 av 4.

Hur lång tid tar lektionen ”Avlasta läs- och analysarbetsbelastningar”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?

Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Publikationer, prenumerationer och replikidentitet
Avlasta läs- och analysarbetsbelastningar
Uppgradering till större version med nästan noll driftstopp
Övervaka replikeringsfördröjning och slot-bloat

← Tillbaka till Prestandaoptimering och frågeoptimering i PostgreSQL