PostgreSQL 10 wurde Anfang Oktober 2017, vor fast einem Jahr, veröffentlicht.
Eine der interessantesten neuen „Funktionen“ ist die bedingungslos deklarative Partitionierung. Aber was ist, wenn Sie es nicht eilig haben, auf 10.000 zu aktualisieren? Amazon zum Beispiel hat es nicht eilig und hat
die PostgreSQL 10-
Unterstützung erst in den letzten Tagen des Februar 2018 eingeführt.
Dann kommt eine gute alte Partitionierung durch Vererbung zur Rettung. Ich bin der Softwarearchitekt der Finanzabteilung eines Taxiunternehmens, daher beziehen sich alle Beispiele auf Reisen auf die eine oder andere Weise (wir lassen die Geldprobleme für ein anderes Mal).
Seit wir 2015 mit dem Umschreiben unseres Finanzsystems begonnen haben, als ich gerade in das Unternehmen eingetreten bin, war von einer deklarativen Partitionierung keine Rede. Bis heute wurde die unten beschriebene Technik erfolgreich eingesetzt.
Der ursprüngliche Grund für das Schreiben dieses Artikels war, dass die meisten Partitionierungsbeispiele in PostgreSQL, auf die ich gestoßen bin, sehr einfach waren. Hier ist eine Tabelle, hier ist eine Spalte, die wir uns ansehen, und vielleicht wissen wir sogar im Voraus, welche Werte darin enthalten sind. Es scheint, dass alles einfach ist. Aber das wirkliche Leben nimmt seine eigenen Anpassungen vor.
In unserem Fall teilen wir Tabellen in zwei Spalten auf, von denen eine Reisedaten enthält. Diesen Fall werden wir betrachten.
Beginnen wir mit dem Aussehen unserer Tabelle:
create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb
Für jeden Mandanten enthält die Tabelle Millionen von Zeilen pro Monat. Glücklicherweise überschneiden sich die Daten zwischen den Mietern nie, und die schwierigsten Anfragen werden innerhalb von ein oder zwei Monaten gestellt.
Für diejenigen, die sich nicht mit der Funktionsweise von PostgreSQL-Partitionen befasst haben (Glück von Oracle, hallo!), Werde ich den Prozess kurz beschreiben.
PostgreSQL stützt sich dabei auf zwei seiner „Funktionen“: die Fähigkeit, Tabellen, Tabellenvererbung und überprüfte Bedingungen zu erben.
Beginnen wir mit der Vererbung. Mit dem Schlüsselwort INHERITS geben wir an, dass die von uns erstellte Tabelle alle Felder der geerbten Tabelle erbt. Dadurch wird auch eine Beziehung zwischen den beiden Tabellen hergestellt: Wenn Sie eine Abfrage vom übergeordneten Element durchführen, erhalten wir auch alle Daten von den untergeordneten Tabellen.
Überprüfte Bedingungen ergänzen das Bild, indem sie sicherstellen, dass sich Daten nicht überschneiden. Somit kann der PostgreSQL-Optimierer einen Teil der untergeordneten Tabellen abschneiden, indem er sich auf die Daten aus der Abfrage stützt.
Die erste Gefahr dieses Ansatzes liegt auf der Hand: Jede Anfrage muss tenant_id enthalten. Wenn Sie sich jedoch nicht ständig daran erinnern, schreiben Sie früher oder später selbst benutzerdefiniertes SQL, in dem Sie vergessen, diese tenant_id anzugeben. Als Ergebnis ein Scan aller Partitionen und einer nicht funktionierenden Datenbank.
Aber zurück zu dem, was wir erreichen wollen. Auf Anwendungsebene möchte ich Transparenz - wir schreiben immer in dieselbe Tabelle, und bereits die Datenbank wählt aus, wo genau diese Daten abgelegt werden sollen.
Dazu verwenden wir die folgende gespeicherte Prozedur:
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN
Das erste, worauf Sie achten sollten, ist die Verwendung von TG_TABLE_NAME. Da dies ein Auslöser ist, füllt PostgreSQL einige Variablen für uns, auf die wir zugreifen können. Die vollständige Liste finden
Sie hier .
In unserem Fall möchten wir den Namen des übergeordneten Elements der Tabelle abrufen, an der der Trigger gearbeitet hat. In unserem Fall werden es Fahrten sein. Wir verwenden einen ähnlichen Ansatz in mehreren Mikrodiensten, und dieser Teil kann praktisch unverändert übertragen werden.
PERFORM ist nützlich, wenn wir eine Funktion aufrufen möchten, die nichts zurückgibt. In der Regel versuchen sie in Beispielen, die gesamte Logik in einer Funktion zusammenzufassen, aber wir versuchen, vorsichtig zu sein.
USING NEW gibt an, dass wir in dieser Abfrage die Werte aus der Zeichenfolge verwenden, die wir hinzufügen
USING NEW .
$1.* erweitert alle Zeilenumbruchwerte. Tatsächlich kann dies in
NEW.* übersetzt werden
NEW.* . Was bedeutet
NEW.ID, NEW.TENANT_ID, …Die folgende Prozedur, die wir mit
PERFORM aufrufen, erstellt eine neue Partition, falls diese noch nicht vorhanden ist. Dies geschieht einmal pro Periode für jeden Mieter.
CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS $BODY$ DECLARE sql text; BEGIN
Wie bereits beschrieben, verwenden wir
INHERITS , um eine Tabelle ähnlich der übergeordneten zu erstellen, und
CHECK , um zu bestimmen, welche Daten dorthin gelangen sollen.
RAISE NOTICE druckt nur eine Zeichenfolge auf die Konsole. Wenn wir jetzt
INSERT von psql ausführen, können wir sehen, ob die Partition erstellt wurde.
Wir haben ein neues Problem.
INHERITS erbt keine Indizes. Dazu haben wir zwei Lösungen:
Erstellen Sie Indizes mithilfe der Vererbung:
Verwenden Sie
CREATE TABLE LIKE und dann
ALTER TABLE INHERITSOder erstellen Sie prozedural Indizes:
CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN
Es ist sehr wichtig, die Indizierung von untergeordneten Tabellen nicht zu vergessen, da jede von ihnen auch nach der Partitionierung Millionen von Zeilen enthält. Indizes für Eltern werden in unserem Fall nicht benötigt, da Eltern immer leer bleiben.
Schließlich erstellen wir einen Trigger, der aufgerufen wird, wenn eine neue Zeile erstellt wird:
CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row();
Es gibt noch eine andere Subtilität, auf die man selten achtet. Partitionierung ist am besten in Spalten, in denen sich die Daten nie ändern. In unserem Fall funktioniert dies: Die Reise ändert niemals tenant_id und created_at. Wenn dies nicht der Fall ist, wird PostreSQL keinen Teil der Daten an uns zurückgeben. Wir haben ihm dann versprochen, zu überprüfen, ob alle Daten gültig sind.
Es gibt verschiedene Lösungen (mit Ausnahme der offensichtlichen - mutieren Sie nicht die Daten, für die wir partitionieren):
Anstelle von
UPDATE wir auf Anwendungsebene immer
DELETE+INSERTWir fügen einen weiteren Trigger für
UPDATE der Daten an die richtige Partition überträgt
Eine weitere erwägenswerte Einschränkung ist die korrekte Indizierung von Spalten mit Datumsangaben. Wenn wir AT TIME ZONE in Abfragen verwenden, dürfen wir nicht vergessen, dass dies tatsächlich ein Funktionsaufruf ist. Und das bedeutet, dass unser Index funktionsbasiert sein sollte. Ich habe vergessen. Infolgedessen ist die Basis von der Last wieder tot.
Der letzte erwägenswerte Aspekt ist, wie Partitionen mit verschiedenen ORM-Frameworks interagieren, sei es ActiveRecord in Ruby oder GORM in Go.
Partitionen in PostgreSQL basieren auf der Tatsache, dass die übergeordnete Tabelle immer leer ist. Wenn Sie ORM nicht verwenden, können Sie sicher zur ersten gespeicherten Prozedur zurückkehren und RETURN NEW ändern. auf RETURN NULL; Dann wird die Zeile in der übergeordneten Tabelle einfach nicht hinzugefügt, was genau das ist, was wir wollen.
Tatsache ist jedoch, dass die meisten ORMs die RETURNING-Klausel mit INSERT verwenden. Wenn wir NULL von unserem Trigger zurückgeben, gerät der ORM in Panik und glaubt, dass die Zeile nicht hinzugefügt wurde. Es wird hinzugefügt, aber nicht, wo ORM sucht.
Es gibt verschiedene Möglichkeiten, dies zu umgehen:
- Verwenden Sie ORM nicht für INSERTs
- Patch ORM (was manchmal bei ActiveRecord empfohlen wird)
- Fügen Sie einen weiteren Trigger hinzu, der die Zeile vom übergeordneten Element entfernt.
Die letzte Option ist unerwünscht, da für jede Operation drei ausgeführt werden. Trotzdem ist es manchmal unvermeidlich, weil wir es separat betrachten werden:
CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN delete from only rides where id = NEW.ID; RETURN null; END; $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_row_trigger AFTER INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE delete_parent_row();
Als letztes müssen wir unsere Lösung testen. Dazu generieren wir eine bestimmte Anzahl von Zeilen:
DO $script$ DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d']; BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP; END $script$;
Und mal sehen, wie sich die Datenbank verhält:
explain select * from rides where tenant_id = 'tenant_a' and created_at AT TIME ZONE 'UTC' > '20171102' and created_at AT TIME ZONE 'UTC' <= '20171103';
Wenn alles richtig gelaufen ist, sollten wir folgendes Ergebnis sehen:
Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone)) (5 rows)
Trotz der Tatsache, dass jeder Mandant Hunderttausende von Zeilen hat, wählen wir nur aus dem gewünschten Datenabschnitt aus. Erfolg!
Ich hoffe, dieser Artikel war interessant für diejenigen, die noch nicht mit der Partitionierung und ihrer Implementierung in PostgreSQL vertraut waren. Aber diejenigen, für die dieses Thema nicht mehr neu ist, haben dennoch ein paar interessante Tricks gelernt.
UPD:
Wie
bigtrot richtig beobachtet,
funktioniert all diese Straßenmagie nicht, wenn die Einstellung
CONSTRAINT_EXCLUSION deaktiviert ist.
Sie können dies mit dem Befehl überprüfen
show CONSTRAINT_EXCLUSION
Die Einstellung hat drei Werte: Ein, Aus und Partition
Die Partitionskonfiguration ist optimaler, wenn Sie CHECK CONSTRAINTS plötzlich nicht nur für Partitionen, sondern auch zur Datennormalisierung verwenden möchten.