PostgreSQL 10 est sorti début octobre 2017, il y a presque un an.
L'une des nouvelles «fonctionnalités» les plus intéressantes est le partitionnement déclaratif inconditionnel. Mais que se passe-t-il si vous n'êtes pas pressé de passer à 10k? Amazon, par exemple, n'est pas pressé et n'a
introduit la prise en charge de PostgreSQL 10 que dans les derniers jours de février 2018.
Ensuite, le bon vieux partitionnement par héritage vient à la rescousse. Je suis l'architecte logiciel du département financier d'une société de taxi, donc tous les exemples seront liés au voyage d'une manière ou d'une autre (nous laisserons les problèmes d'argent pour une autre fois).
Depuis que nous avons commencé à réécrire notre système financier en 2015, lorsque je viens de rejoindre la société, il n'a été question d'aucune partition déclarative. Ainsi, à ce jour, la technique décrite ci-dessous a été utilisée avec succès.
La raison originale de la rédaction de cet article était que la plupart des exemples de partitionnement dans PostgreSQL que j'ai rencontrés étaient très basiques. Voici un tableau, voici une colonne que nous regardons, et peut-être même savoir à l'avance quelles valeurs y sont. Il semblerait que tout soit simple. Mais la vraie vie fait ses propres ajustements.
Dans notre cas, nous partitionnons les tables en deux colonnes, dont l'une contient les dates de voyage. C'est ce cas que nous allons considérer.
Commençons par ce à quoi ressemble notre table:
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
Pour chaque locataire, le tableau contient des millions de lignes par mois. Heureusement, les données entre les locataires ne se recoupent jamais, et les demandes les plus difficiles se font au bout d'un ou deux mois.
Pour ceux qui n'ont pas exploré le fonctionnement des partitions PostgreSQL (chanceux d'Oracle, bonjour!), Je vais décrire brièvement le processus.
Pour cela, PostgreSQL s'appuie sur deux de ses «fonctionnalités»: la capacité d'hériter des tables, l'héritage des tables et les conditions vérifiées.
Commençons par l'héritage. En utilisant le mot-clé INHERITS, nous indiquons que la table que nous créons hérite de tous les champs de la table héritée. Cela crée également une relation entre les deux tables: en faisant une requête du parent, nous obtenons également toutes les données des enfants.
Les conditions vérifiées complètent l'image en s'assurant que les données ne se croisent pas. Ainsi, l'optimiseur PostgreSQL peut couper une partie des tables enfants en s'appuyant sur les données de la requête.
Le premier écueil de cette approche semble assez évident: toute demande doit contenir tenant_id. Et néanmoins, si vous ne vous en souvenez pas constamment, tôt ou tard vous écrirez vous-même du SQL personnalisé dans lequel vous oubliez de spécifier ce tenant_id. En conséquence - l'analyse de toutes les partitions et de la base de données qui ne fonctionne pas.
Mais revenons à ce que nous voulons réaliser. Au niveau de l'application, j'aimerais la transparence - nous écrivons toujours dans la même table, et déjà la base de données choisit où exactement placer ces données.
Pour ce faire, nous utilisons la procédure stockée suivante:
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN
La première chose à laquelle vous devez faire attention est l'utilisation de TG_TABLE_NAME. Puisqu'il s'agit d'un déclencheur, PostgreSQL remplit un certain nombre de variables auxquelles nous pouvons accéder. La liste complète se
trouve ici .
Dans notre cas, nous voulons obtenir le nom parent de la table sur laquelle le déclencheur a fonctionné. Dans notre cas, ce seront des balades. Nous utilisons une approche similaire dans plusieurs microservices, et cette partie peut être transférée pratiquement sans modifications.
PERFORM est utile si nous voulons appeler une fonction qui ne renvoie rien. Typiquement, dans les exemples, ils essaient de mettre toute la logique dans une seule fonction, mais nous essayons d'être prudents.
USING NEW indique que dans cette requête, nous utilisons les valeurs de la chaîne que nous avons essayé d'ajouter.
$1.* étendra toutes les valeurs de nouvelle ligne. En fait, cela peut être traduit en
NEW.* . Ce qui se traduit par
NEW.ID, NEW.TENANT_ID, …La procédure suivante, que nous appelons avec
PERFORM , créera une nouvelle partition, si elle n'existe pas déjà. Cela se produira une fois par période pour chaque locataire.
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
Comme décrit précédemment, nous utilisons
INHERITS pour créer une table similaire à parent et
CHECK pour déterminer quelles données doivent y aller.
RAISE NOTICE imprime simplement une chaîne à la console. Si nous
INSERT maintenant
INSERT partir de psql, nous pouvons voir si la partition a été créée.
Nous avons un nouveau problème.
INHERITS n'hérite pas des index. Pour ce faire, nous avons deux solutions:
Créez des index en utilisant l'héritage:
Utilisez
CREATE TABLE LIKE puis
ALTER TABLE INHERITSOu créez des index de manière procédurale:
CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN
Il est très important de ne pas oublier l'indexation des tables enfants, car même après le partitionnement, chacune d'entre elles aura des millions de lignes. Les index sur le parent ne sont pas nécessaires dans notre cas, car le parent restera toujours vide.
Enfin, nous créons un déclencheur qui sera appelé lors de la création d'une nouvelle ligne:
CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row();
Il y a une autre subtilité à laquelle on prête rarement attention. Le partitionnement est préférable dans les colonnes où les données ne changent jamais. Dans notre cas, cela fonctionne: le trajet ne change jamais le tenant_id et created_at. Si ce n'est pas le cas, PostreSQL ne nous renverra pas une partie des données. Nous lui avons alors promis de VÉRIFIER que toutes les données étaient valides.
Il existe plusieurs solutions (sauf pour l'évidence - ne pas muter les données pour lesquelles nous partitionnons):
Au lieu de
UPDATE nous faisons toujours
DELETE+INSERT au niveau de l'application
Nous ajoutons un déclencheur de plus sur
UPDATE qui transférera les données vers la partition correcte
Une autre mise en garde mérite d'être examinée: comment indexer correctement les colonnes contenant des dates. Si nous utilisons AT TIME ZONE dans les requêtes, il ne faut pas oublier qu'il s'agit en fait d'un appel de fonction. Et cela signifie que notre index doit être basé sur les fonctions. J'ai oublié. En conséquence, la base est à nouveau morte de la charge.
Le dernier aspect à considérer est la façon dont les partitions interagissent avec divers cadres ORM, que ce soit ActiveRecord dans Ruby ou GORM dans Go.
Les partitions dans PostgreSQL reposent sur le fait que la table parent sera toujours vide. Si vous n'utilisez pas ORM, vous pouvez revenir en toute sécurité à la première procédure stockée et modifier RETOUR NOUVEAU; sur RETOUR NULL;. Ensuite, la ligne de la table parent ne sera tout simplement pas ajoutée, ce qui est exactement ce que nous voulons.
Mais le fait est que la plupart des ORM utilisent la clause RETURNING avec INSERT. Si nous renvoyons NULL de notre déclencheur, l'ORM paniquera, croyant que la ligne n'a pas été ajoutée. Il est ajouté, mais pas là où l'ORM regarde.
Il existe plusieurs façons de contourner cela:
- N'utilisez pas ORM pour INSERT
- Patch ORM (ce qui est parfois conseillé dans le cas d'ActiveRecord)
- Ajoutez un autre déclencheur, qui supprimera la ligne du parent.
La dernière option n'est pas souhaitable, car pour chaque opération, nous en effectuerons trois. Néanmoins, il est parfois inévitable, car nous le considérerons séparément:
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();
La dernière chose que nous devons faire est de tester notre solution. Pour ce faire, nous générons un certain nombre de lignes:
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$;
Et voyons comment se comporte la base de données:
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';
Si tout allait bien, nous devrions voir le résultat suivant:
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)
Malgré le fait que chaque locataire possède des centaines de milliers de lignes, nous ne sélectionnons que dans la tranche de données souhaitée. Succès!
J'espère que cet article était intéressant pour ceux qui ne connaissaient pas encore ce qu'est le partitionnement et comment il est implémenté dans PostgreSQL. Mais ceux pour qui ce sujet n'est plus nouveau ont néanmoins appris quelques astuces intéressantes.
UPD:
Comme
bigtrot l'a correctement observé, toute cette magie de la rue ne fonctionnera pas si le paramètre
CONSTRAINT_EXCLUSION est désactivé.
Vous pouvez le vérifier en utilisant la commande
show CONSTRAINT_EXCLUSION
Le paramètre a trois valeurs: on, off et partition
La configuration de partition est plus optimale si vous aimez soudainement utiliser CHECK CONSTRAINTS non seulement pour les partitions, mais aussi pour la normalisation des données.