Præcis måling af tabel- og indeks-bloat

Der findes to grupper af metoder til måling af oppustning, hver med sine afvejninger:

• Estimeringsforespørgsler læser kun katalogstatistik (pg_class, pg_statistic). De er hurtige og kræver ingen låse, men er omtrentlige — nøjagtigheden afhænger af en aktuel ANALYZE og antagelser om kolonnebredder.
• pgstattuple scanner fysisk relationen for at tælle nøjagtige byte for aktive og døde data. Den er præcis, men I/O-tung på store tabeller.

Den praktiske arbejdsgang er: Brug billig estimering til at finde kandidater, og brug derefter pgstattuple til at bekræfte de værste, før du beslutter dig for en løsning.

pgstattuple er en contrib-udvidelse, der leveres med PostgreSQL. Den skal aktiveres pr. database, før den kan bruges.

• Aktivering kræver superbrugerrettigheder eller en rolle med CREATE på databasen.
• Dens funktioner kræver rollen pg_stat_scan_tables (eller superbrugerrettigheder) for at kunne køres mod vilkårlige relationer.

Når den er installeret, får du pgstattuple(), pgstatindex() og den lettere pgstattuple_approx().

pgstattuple('relation') udfører en fuld scanning og returnerer én række med byte-nøjagtige oplysninger om heapen.

• table_len — relationens samlede størrelse i byte.
• tuple_count / tuple_len — antal og samlet størrelse i byte for aktive tupler.
• dead_tuple_count / dead_tuple_len — døde tupler og deres størrelse i byte.
• free_space / free_percent — genanvendelig ledig plads.

Det vigtigste signal for oppustning er dead_tuple_percent plus free_percent: Tilsammen fortæller de, hvor stor en del af filen der ikke indeholder aktive data.

pgstattuple() læser hver side i relationen. På en tabel på 500 GB er det meget I/O og kan fortrænge nyttige data fra cachen.

• Den tager kun en ACCESS SHARE-lås, så den blokerer ikke læsninger eller skriveoperationer — men I/O-belastningen er reel.
• På store tabeller bør du foretrække pgstattuple_approx(), som bruger visibility map til at springe over sider, der er synlige for alle, og tager stikprøver af resten.
• approx returnerer approx_free_percent og dead_tuple_percent, som ligger tæt på de nøjagtige værdier til en brøkdel af omkostningen.

Tommelfingerregel: Estimér først, kør approx på mellemstore tabeller, og reserver den nøjagtige pgstattuple() til den endelige bekræftelse af en konkret mistænkt tabel.

Indeks bliver oppustede uafhængigt af deres tabel. Brug pgstatindex() til B-træsindeks for at få strukturelle detaljer.

• avg_leaf_density — procentdelen af bladsider, der er udfyldt med nyttige data. Sunde indeks ligger omkring 90 %; værdier, der falder mod 50 %, signalerer kraftig oppustning.
• leaf_fragmentation — hvor meget bladsiderne står ude af rækkefølge; høj fragmentering skader intervalscanninger.
• index_size og internal_pages / leaf_pages beskriver træets opbygning.

En lav avg_leaf_density er det tydeligste argument for en REINDEX (helst REINDEX ... CONCURRENTLY).

Når du slet ikke har råd til en scanning, beregner den community-udviklede forespørgsel til estimering af oppustning (fra check_postgres / pgsql-bloat-estimation) den forventede størrelse ud fra statistik og sammenligner den med den faktiske størrelse.

Dens grundidé er:

• Hent den gennemsnitlige rækkebredde fra pg_statistic (den avg_width pr. kolonne, som ANALYZE registrerede).
• Tilføj overhead for rækkeheader og justering, og dividér derefter tabelstørrelsen med det forventede antal tupler pr. side.
• Forskellen mellem forventede og faktiske sider er den estimerede oppustning.

Metoden er omtrent­lig og følsom over for forældet statistik, men kører på millisekunder i hele databasen.

Estimeringsnøjagtigheden kollapser, når inputtene er forkerte. Vær opmærksom på disse faldgruber:

• Forældet statistik: Hvis ANALYZE ikke er kørt for nylig, er avg_width og rækkeantallene forældede. Kør ANALYZE, før du stoler på estimaterne.
• Brede kolonner med variabel længde: Meget varierende bredder for text/jsonb gør gennemsnit pr. række upålidelige.
• TOAST: Store værdier, der er gemt uden for rækken, ligger i en separat TOAST-tabel; heap-estimater overser denne lagerplads fuldstændigt.
• Fillfactor: En tabel, der er oprettet med fillfactor < 100, efterlader med vilje ledig plads — det er ikke oppustning.

Kontrollér altid et overraskende estimat med pgstattuple_approx(), før du handler.

Store kolonneværdier flyttes til en skjult TOAST-tabel, som oppustes separat. En heap kan se ren ud, mens dens TOAST-relation er enorm.

• Find TOAST-relationen via pg_class.reltoastrelid.
• Kør pgstattuple() direkte på TOAST-relationens OID for at måle dens døde plads.

Tabeller med hyppigt opdaterede jsonb- eller bytea-kolonner skjuler ofte det meste af deres oppustning i TOAST.

Når du har nøjagtige tal, skal du knytte dem til en løsning:

• dead_tuple_percent høj, free_percent høj: autovacuum er bagefter — en almindelig VACUUM (eller justering af autovacuum) frigiver normalt genanvendelig plads uden at omskrive filen.
• free_percent meget høj, men tabellen bliver ikke mindre: Filen har ledig plads til sidst, som VACUUM ikke kan returnere til operativsystemet — overvej pg_repack (online) eller VACUUM FULL (låser tabellen).
• Indeksets avg_leaf_density lav: REINDEX CONCURRENTLY.

Fastlæg en tærskel (f.eks. at du kun handler over cirka 20 % oppustning og en betydelig absolut størrelse), så du ikke kører forstyrrende vedligeholdelse for ubetydelige gevinster.

Du har nu en lagdelt metode til at kvantificere oppustning, før du handler:

• Første vurdering med pg_stat_user_tables (andelen af n_dead_tup) — gratis og øjeblikkelig.
• Estimér i hele databasen med statistikbaserede forespørgsler om oppustning — hurtigt, men bekræft med en aktuel ANALYZE.
• Bekræft præcist med pgstattuple(), eller med pgstattuple_approx() på store tabeller, hvor du læser dead_tuple_percent og free_percent.
• Indeks: Brug pgstatindex(), og hold øje med avg_leaf_density og leaf_fragmentation.
• Glem ikke TOAST, og træk tilsigtet ledig plads fra på grund af fillfactor.

Først når tallene overskrider en meningsfuld tærskel, vælger du VACUUM, pg_repack, VACUUM FULL eller REINDEX — målingen styrer løsningen, aldrig omvendt.