PostgreSQL introduceerde declaratieve partitionering in versie 10 om het omslachtige op erfelijkheid gebaseerde partitioneringsmodel te vervangen. De queryplanner voert statische partition afslanking uit tijdens de planningsfase door querypredicaten te vergelijken met partitiongrenzen, maar vereist dat expressies evalueerbaar zijn tot constanten op het moment van planning om te bepalen welke partitions kunnen worden geëlimineerd.
De kern architectonische beperking is dat STABLE functies, zoals now() of current_timestamp, niet worden geëvalueerd tijdens de planning omdat hun resultaten kunnen verschillen tussen planning en uitvoering of zelfs tijdens de uitvoering van de query. Bijgevolg behandelt de planner predicaten die deze functies bevatten als zwarte dozen, en kan niet bewijzen dat bepaalde partitions geen overeenkomstige rijen kunnen bevatten, wat een scan van alle partitions afdwingt.
De oplossing bestaat uit het herschrijven van het predicaat om IMMUTABLE functies of letterlijke constanten te gebruiken, of te vertrouwen op runtime partition afslanking beschikbaar in PostgreSQL 11 en later. Door enable_partition_pruning op on te zetten, evalueert de executor de resultaten van STABLE functies tegen partitiongrenzen op het moment van uitvoering, en slaat irrelevante partitions dynamisch over na de initiële planningsfase.
Een financieel analysekantoor partitioneerde een trades-table op TIMESTAMPTZ op de execution_time kolom met behulp van dagelijkse intervallen om terabytes aan tick data te beheren. Analisten vroegen vaak recente activiteit met WHERE execution_time >= now() - interval '1 hour', maar observeerden dat deze queries leed aan catastrofale prestatieverval, waarbij alle 365 dagelijkse partitions werden gescand in plaats van alleen de laatste.
De eerste benadering was om de applicatielaag de timestampgrens te laten berekenen en deze als een letterlijke constante in te voegen. Dit maakte onmiddellijke statische afslanking mogelijk en verlaagde de querytijd van 45 seconden naar 80 milliseconden. Echter, dit brak bestaande SQL-queries ingebed in derde partij BI-tools die niet gemakkelijk konden worden aangepast.
De tweede benadering hield in dat er een aangepaste onveranderlijke functie werd gecreëerd die een vaste timestamp retourneerde op basis van de huidige datum. Dit werd afgewezen omdat het verkeerde resultaten zou opleveren als de database transactie open bleef over de middernachtgrens, waardoor het STABLE contract dat now() binnen een transactie biedt, zou worden geschonden. Een dergelijke schending zou kunnen leiden tot stilzwijgend onjuiste queryresultaten als de planner een verouderd partitionbereik zou cachen.
De gekozen oplossing maakte gebruik van de runtime partition afslanking mogelijkheden van PostgreSQL 12. De databasebeheerders zorgden ervoor dat enable_partition_pruning was ingeschakeld en herstructureerden de applicatie om voorbereide instructies te gebruiken met de tijdgrens die als parameter werd doorgegeven in plaats van samengevoegd in de SQL-string. Dit stelde de executor in staat om partitions dynamisch af te slanken met de werkelijke parameterwaarde op het moment van uitvoering, wat bijna optimale prestaties opleverde zonder veranderingen in de SQL-tekstgeneratie in legacy rapportagetools te vereisen.
Hoe verschilt het uitsluitingsmechanisme van constraints van PostgreSQL voor geërfde tabellen van native partition afslanking, en waarom vereist de eerste expliciete GUC-configuratie terwijl de laatste dat niet doet?
Constraint uitsluiting was de partitioneringsmethode die vóór declaratieve partitionering werd gebruikt, vertrouwend op CHECK-constraints op kindtabellen om te bewijzen dat tabellen geen relevante rijen konden bevatten. Omdat het evalueren van deze constraints tegen elke geërfde tabel tijdens de planning kostbaar is wanneer er honderden tabellen bestaan, wordt het gecontroleerd door de constraint_exclusion parameter, die standaard op partition staat (alleen controleren wanneer te queryen via erfelijkheid). Native partition afslanking gebruikt gespecialiseerde datastructuren in de planner die de partition hiërarchie direct begrijpen, wat het sneller maakt en altijd ingeschakeld is, zonder dat er GUC-aanpassing nodig is voor correcte functionaliteit.
Wanneer een rij wordt bijgewerkt om deze tussen partitions te verplaatsen door de partition sleutel te wijzigen, waarom voert PostgreSQL intern een DELETE en INSERT uit in plaats van een in-place update, en wat zijn de implicaties voor triggers?
Omdat elke partition een aparte heap-relatie is met aparte fysieke opslag, vereist het wijzigen van de partition sleutel dat de tuple van het ene bestand naar het andere wordt verplaatst. PostgreSQL implementeert deze overgang door de rij uit de bronpartition te verwijderen en deze in de bestemmingspartition in te voegen. Dit betekent dat rijniveau BEFORE DELETE, AFTER DELETE, BEFORE INSERT, en AFTER INSERT triggers allemaal worden geactiveerd tijdens wat eruit ziet als een enkele UPDATE-bewerking. Daarnaast streamt logische replicatie dit als twee afzonderlijke WAL-invoeren (verwijder en voeg toe), wat conflicten op abonnees kan veroorzaken als de replica-identiteit niet correct is geconfigureerd.
Welke specifieke locking en validatie overhead ontstaat er bij het toevoegen van een nieuwe partition aan een tabel die een DEFAULT partition heeft, en hoe kan men een volledige scan van de default partition vermijden?
Bij het toevoegen van een nieuwe partition aan een op bereik of lijst gepartitioneerde tabel met een DEFAULT-partition, moet PostgreSQL de hele DEFAULT-partition scannen om te verifiëren dat er geen rijen zijn die aan de nieuwe, meer specifieke partition toebehoren. Deze validatiescan verwerft een ACCESS EXCLUSIVE lock op de gepartitioneerde tabel en kan uren duren voor grote default partitions. Om dit te vermijden, moet men de DEFAULT-partition ontkoppelen voordat men de nieuwe partition toevoegt, en vervolgens de DEFAULT-partition pas weer aansluiten nadat men heeft verzekerd dat er geen conflicterende rijen bestaan, of alternately, gebruik CREATE TABLE ... PARTITION OF om een nieuwe lege partition te creëren en data te migreren met INSERT ... SELECT met een WHERE clausule die filtert voor het specifieke partition bereik, waardoor de validatiescan van de inhoud van de default partition wordt omzeild.