Ydelsesoptimering og optimering af forespørgsler i PostgreSQL · Lektion

Aflastning af læse- og analysearbejdsbelastninger

Dirigér tung rapporteringstrafik til logiske replikaer for at beskytte den primære OLTP-latens.

Lektion 2 af 413 trin

Aflastning af læse- og analysearbejdsbelastninger er en gratis Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse alle 3 lektioner i dette læringsspor gratis i deres fulde længde — derefter låser CoddyKit PRO alle lektioner op samt praktiske øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Den er en del af læringsforløbet i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvorfor overhovedet flytte læsninger?

På en travl PostgreSQL-primærserver håndterer den samme instans både OLTP-trafik (små, hurtige og forsinkelsesfølsomme skrivninger og opslag) og analytisk trafik (store sekventielle scanninger, aggregeringer og rapporteringsjoin).

Problemet er, at en enkelt tung rapporteringsforespørgsel kan mætte de delte buffere, fortrænge varme OLTP-sider og øge kødybden for I/O. Din p99-forsinkelse ved betaling fordobles pludselig, fordi nogen kørte en kvartalsrapport over omsætningen.

Mål: Hold den primære server slank til transaktionelt arbejde.
Strategi: Send tunge læse- og analytiske forespørgsler til en replika, der indeholder en kopi af dataene.

Denne lektion fokuserer på at bruge logisk replikering til at opbygge formålsbestemte læsereplikaer til analyse.

Fysisk kontra logisk replikering

PostgreSQL tilbyder to replikeringsmodeller, og det rigtige valg er den afgørende beslutning, når læsninger skal aflastes.

Fysisk (streaming-)replikering: sender WAL byte for byte. Replikaen er en nøjagtig klon på blokniveau — samme skema, samme indekser, samme bloat. Velegnet til HA og skalering af læsninger for identiske forespørgsler.
Logisk replikering: afkoder WAL til ændringer på rækkeniveau (INSERT/UPDATE/DELETE) og afspiller dem via SQL. Abonnenten er en uafhængig database — du kan tilføje andre indekser, ekstra kolonner, materialiserede aggregater eller endda bruge en anden hovedversion.

Til analytisk aflastning er logisk replikering særligt velegnet: analyseknuden kan have tunge rapportindekser, som den primære database aldrig bør betale for at vedligeholde.

Oprettelse af en publikation

På den primære database ( udgiveren ) skal du indstille wal_level = logical og oprette en publikation — et navngivet sæt tabeller, hvis ændringer skal streames.

Du kan publicere alle tabeller, et undersæt eller endda filtrere rækker og kolonner (PostgreSQL 15+). Ved analytisk aflastning publicerer du typisk præcis de fakta- og dimensionstabeller, som rapporterne har brug for.

-- 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;

Oprettelse af abonnenten

På en separat vært — din analysereplika — skal du oprette et tilsvarende skema (i det mindste de publicerede tabeller) og derefter et abonnement. Abonnementet opretter forbindelse til udgiveren og starter med en indledende datakopi efterfulgt af kontinuerlig streaming.

Da abonnenten er uafhængig, kan du give den den konfiguration, som analyser kræver: mere work_mem, mere maintenance_work_mem og indstillinger, der er velegnede til parallel behandling — uden at ændre den primære OLTP-database.

-- 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;

Tilføj kun analytiske indekser på replikaen

Her får du gevinsten ved logisk replikering. Den primære database beholder kun de slanke indekser, som OLTP har brug for. Analyseabonnenten tilføjer brede, dyre indekser, som ville gøre alle skrivninger på den primære database langsommere, men gøre rapporter hurtige på replikaen.

BRIN-indekser til enorme, tidsseriebaserede faktatabeller, hvor der kun tilføjes data.
Dækkende / partielle indekser målrettet forespørgsler fra kontrolpaneler.
Udtryksindekser til rapportspecifikke prædikater.

Vedligeholdelse af disse indekser koster analyseknuden ekstra skrivninger — men den primære database betaler aldrig for 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';

Routing af læsninger i applikationslaget

Aflastning hjælper kun, hvis trafikken rent faktisk når replikaen. Du dirigerer trafikken i applikations- eller proxy-laget:

Opdeling på forbindelsesniveau: en læse-/skrivepulje til den primære database og en skrivebeskyttet pulje til analyseknuden.
Proxybaseret: PgBouncer/Pgpool eller et servicenet dirigerer SELECT-trafik til rapporter efter bruger, skema eller forespørgselstag.

Et rent mønster er en dedikeret analytics_ro-databasebruger med tidsgrænser for instruktioner og lavere prioritet, så en løbsk rapport aldrig kan true de transaktionelle SLA'er.

-- 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');

Forudgående aggregering med materialiserede visninger

Replikaen er det perfekte sted til materialiserede visninger, der på forhånd beregner dyre aggregater. Kontrolpaneler læser derefter en lille opsummeret tabel i stedet for at gennemgå millioner af faktarækker ved hver indlæsning.

Opdatér dem på analyseknuden efter en tidsplan. Brug af REFRESH MATERIALIZED VIEW CONCURRENTLY forhindrer, at læsere låses ude under genopbygningen (det kræver et unikt indeks på visningen).

-- 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;

Forståelse af replikeringsefterslæb

Logisk replikering er som standard asynkron: abonnenten er forsinket i forhold til den primære database med et vist efterslæb. Til analyser er det næsten altid i orden — en rapport baseret på data, der er to sekunder gammel, er acceptabel. Det gælder ikke for brugergrænseflader, der skal kunne "læse deres egen skrivning".

Beslutningsregel: send eventuelt konsistente, aggregattunge læsninger til replikaen; behold læsning efter skrivning og læsninger af den nøjagtigt aktuelle tilstand på den primære database.

Overvåg efterslæbet fra udgiverens replikeringsslot — en abonnent, der er gået i stå, får WAL til at ophobe sig og kan fylde den primære databases 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';

Filtrering til det, analyserne har brug for

Du har sjældent brug for alle kolonner eller rækker på analyseknuden. PostgreSQL 15+ lader en publikation anvende rækkefiltre og kolonnelister, så det replikerede datasæt og omkostningerne ved WAL-afkodning bliver mindre.

Rækkefilter: replikér kun rækker, der er relevante for rapporteringen (f.eks. ordrer, der ikke er arkiveret).
Kolonneliste: udelad brede eller følsomme kolonner (personhenførbare oplysninger, store JSON-objekter), som rapporterne aldrig bruger.

Et mindre replikeret datasæt betyder hurtigere indledende synkronisering og mindre netværks- og afkodningsbelastning.

-- 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');

Kontrol af, at planen bruger replikaens indekser

Efter at have oprettet analytiske indekser og materialiserede visninger på abonnenten skal du bekræfte, at planlæggeren faktisk bruger dem. Kør EXPLAIN (ANALYZE, BUFFERS) på analyseknuden for dine faktiske rapportforespørgsler.

Du bør se indeksscanninger / bitmapscanninger på de rapportspecifikke indekser og helst se, at forespørgslen fra kontrolpanelet rammer den materialiserede visning i stedet for den rå faktatabel. Sammenlign buffere med shared read før og efter — faldet er den I/O, du har fjernet fra den primære database.

-- 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;

Driftsmæssige sikkerhedsforanstaltninger

En arkitektur til aflastning af læsninger er kun så god som dens fejlhåndtering. Indbyg sikkerhedsforanstaltninger:

Alarmer ved efterslæb: send en alarm, når retained_wal eller anvendelsesefterslæbet overskrider en tærskel — en død abonnent kan fylde den primære databases disk via slottet.
Sekvenser og DDL replikeres ikke: logisk replikering kopierer rækkedata, ikke sekvensværdier eller skemaændringer. Håndtér DDL bevidst på begge sider.
Opmærksomhed på konflikter: lad aldrig applikationer skrive til replikerede tabeller på abonnenten, ellers vil anvendelsen af ændringer mislykkes.
Kapacitet: analyseknuden kan være mindre med hensyn til antal forbindelser, men har brug for RAM og diskplads til de ekstra indekser og materialiserede visninger.

Hurtigt tjek: Valg af aflastningsmål

Et kontrolpanel kører en aggregering på 9 sekunder over 200 millioner ordrerækker, hver gang en leder åbner det, og det forringer svartiden for OLTP-udtjekning på den primære database. Rapporten accepterer data, der er nogle få sekunder forældede. Hvad er det bedste træk?

Besvar den i den interaktive lektion

Opsummering: Aflastning af læsninger med logisk replikering

Du har lært, hvordan du beskytter svartiden for primær OLTP ved at dirigere tung rapporttrafik til en specialbygget logisk replika.

Logisk frem for fysisk, når analyseknuden har brug for egne indekser, kolonner eller aggregater.
Publicér præcis de tabeller (og rækker/kolonner), rapporterne har brug for, med en PUBLICATION; brug dem med en SUBSCRIPTION.
Tilføj kun analytiske indekser og materialiserede visninger på abonnenten, så den primære database aldrig betaler for rapporteringsstrukturer.
Dirigér læsninger via puljer/proxyer og en begrænset skrivebeskyttet databasebruger med tidsgrænser for instruktioner.
Send kun læsninger, der tåler forældede data, og aggregatlæsninger til replikaen; behold læsning efter skrivning på den primære database.
Overvåg efterslæbet i replikeringsslottet — en abonnent, der er gået i stå, kan fylde den primære databases disk.

Resultatet er, at kontrolpanelerne får hurtig, dedikeret infrastruktur, mens dine SLA'er for transaktioner forbliver sikre.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 22
Lektioner: 88

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Aflastning af læse- og analysearbejdsbelastninger” gratis?

Ja — alle 3 lektioner i læringssporet Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, inklusive “Aflastning af læse- og analysearbejdsbelastninger”, kan læses gratis i deres fulde længde her på webstedet. Derefter låser CoddyKit PRO alle lektioner op samt interaktive øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Aflastning af læse- og analysearbejdsbelastninger”?

Dirigér tung rapporteringstrafik til logiske replikaer for at beskytte den primære OLTP-latens. Du øver dig i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Ydelsesoptimering og optimering af forespørgsler i PostgreSQL?

Der kræves ingen tidligere erfaring. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “Aflastning af læse- og analysearbejdsbelastninger”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion?

Ja. Alle Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Publications, subscriptions og replica identity
Aflastning af læse- og analysearbejdsbelastninger
Major version-opgraderinger med næsten ingen nedetid
Overvågning af replikationsforsinkelse og slot-bloat

← Tilbage til Ydelsesoptimering og optimering af forespørgsler i PostgreSQL