utilisation de postgresql chez lengow

POSTGRESQL CHEZ LENGOWQuand un poney rencontre un éléphant

Mickael Le Baillif, Guillaume StofferMeetup PostgreSQL Nantes #22016-04-26

NOTRE METIER

2

NOTRE METIER

3Lengow : Un écosystème dédié au e-commerce

NOS BESOINS EN BDD

4

NOS BESOINS EN BDD

Un très grand volume de données estimé à 250 milliards de produits indexés par an

Une très grande diversité des sources en entrée (langage, format, structure)

5

NOS BESOINS EN BDD

Une très grande diversité des diffuseurs en sortie (format attendu exotique)

La possibilité de manipuler un catalogue marchand en profondeur en toute sécurité

6

ARCHITECTURE POSTGRESQL

7

QU’Y A-T-IL SOUS LE CAPOT ?

ARCHITECTURE MATERIELLE

9

Serveur physique

ARCHITECTURE MATERIELLE

10

Processeursmulti-coeurs

ARCHITECTURE MATERIELLE

11

Données “chaudes” intégralement en RAM

ARCHITECTURE MATERIELLE

12

Persistence disque sur SSD

ET SI ÇA CRASHE ?

RÉPLICATION ET HAUTE DISPONIBILITÉ

14Datacenter 1 Datacenter 2

WAL shipping

WA

L shipping

WA

L shippingmaster slave

slaveslave

RÉPLICATION ET HAUTE DISPONIBILITÉ

15 @IP virtuelle RW

@IP virtuelle RO

ARCHITECTURE LOGIQUE

16

JSONB

XMLCSVJSON…

XMLCSVJSONMarchands

Diffuseurs

IMPLÉMENTATIONS INTÉRESSANTES

17

18

XML ET XPATH

Extraction de données utiles dans des documents XML

directement à partir de requêtes SQL

19

XML ET XPATH

La structure du fichier à envoyer aux diffuseurs est spécifiée en XML

<?xml version="1.0" encoding="UTF-8"?><group name="Product" foreach="product" main_products_node="true">

<csv_param name="delimiter" value="|" /> <csv_param name="quotechar" value="" />

<field id="1" type="string" use="required" name="EAN"/> <field id="2" type="string" use="required" name="Titre"/> <field id="3" type="string" use="required" name="Marque"/> <field id="4" type="string" use="required" name="Prix HT"/> <field id="5" type="string" use="required" name="Prix TTC"/> <field id="6" type="string" use="required" name="Frais port"/></group>

20

XML ET XPATH

La structure du fichier à envoyer aux diffuseurs est spécifiée en XML

<?xml version="1.0" encoding="UTF-8"?><group name="Product" foreach="product" main_products_node="true">

<csv_param name="delimiter" value="|" /> <csv_param name="quotechar" value="" />

<field id="1" type="string" use="required" name="EAN"/> <field id="2" type="string" use="required" name="Titre"/> <field id="3" type="string" use="required" name="Marque"/> <field id="4" type="string" use="required" name="Prix HT"/> <field id="5" type="string" use="required" name="Prix TTC"/> <field id="6" type="string" use="required" name="Frais port"/></group>CS

VJS

ON

XML

EAN Titre Marque

Prix HT

Prix TTC

Frais port [

{ "EAN": "...", "Titre": "...", "Marque": "...", "Prix HT": 2, "Prix TTC": 3, "Frais port": 1 }]

<?xml version="1.0" encoding="UTF-8"?><Products> <Product> <EAN>...</EAN> <Titre>...</Titre> <Marque>...</Marque> <Prix_HT>...</Prix_HT> <Prix_TTC>...</Prix_TTC> <Frais_port>...</Frais_port> </Product></Products>

21

XML ET XPATH

22

XML ET XPATH

<?xml version="1.0" encoding="UTF-8"?><group name="Product" foreach="product" main_products_node="true">

<csv_param name="delimiter" value="|" /> <csv_param name="quotechar" value="" />

<field id="1" type="string" use="required" name="EAN"/> <field id="2" type="string" use="required" name="Titre"/> <field id="3" type="string" use="required" name="Marque"/> <field id="4" type="string" use="required" name="Prix HT"/> <field id="5" type="string" use="required" name="Prix TTC"/> <field id="6" type="string" use="required" name="Frais port"/></group>

WITH fields AS (SELECT unnest(xpath('//field', schema_definition)) AS f FROM channel.structure WHERE id = 915)SELECT unnest(xpath('@id', f)) AS id, unnest(xpath('@name', f)) AS name, unnest(xpath('@use', f))::text = 'required' AS requiredFROM fields

23

XML ET XPATH

<?xml version="1.0" encoding="UTF-8"?><group name="Product" foreach="product" main_products_node="true">

<csv_param name="delimiter" value="|" /> <csv_param name="quotechar" value="" />

<field id="1" type="string" use="required" name="EAN"/> <field id="2" type="string" use="required" name="Titre"/> <field id="3" type="string" use="required" name="Marque"/> <field id="4" type="string" use="required" name="Prix HT"/> <field id="5" type="string" use="required" name="Prix TTC"/> <field id="6" type="string" use="required" name="Frais port"/></group>

WITH fields AS (SELECT unnest(xpath('//field', schema_definition)) AS f FROM channel.structure WHERE id = 915)SELECT unnest(xpath('@id', f)) AS id, unnest(xpath('@name', f)) AS name, unnest(xpath('@use', f))::text = 'required' AS requiredFROM fields

f<field id="1" type="string" use="required" name="EAN"/>

<field id="2" type="string" use="required" name="Titre Produit"/>

<field id="3" type="string" use="required" name="Marque"/>

<field id="4" type="string" use="required" name="Prix HT"/>

24

XML ET XPATH

<?xml version="1.0" encoding="UTF-8"?><group name="Product" foreach="product" main_products_node="true">

<csv_param name="delimiter" value="|" /> <csv_param name="quotechar" value="" />

<field id="1" type="string" use="required" name="EAN"/> <field id="2" type="string" use="required" name="Titre"/> <field id="3" type="string" use="required" name="Marque"/> <field id="4" type="string" use="required" name="Prix HT"/> <field id="5" type="string" use="required" name="Prix TTC"/> <field id="6" type="string" use="required" name="Frais port"/></group>

WITH fields AS (SELECT unnest(xpath('//field', schema_definition)) AS f FROM channel.structure WHERE id = 915)SELECT unnest(xpath('@id', f)) AS id, unnest(xpath('@name', f)) AS name, unnest(xpath('@use', f))::text = 'required' AS requiredFROM fields

id name required1 EAN t2 Titre Produit t3 Marque t4 Prix HT t

25

SYSTEM VERSION

Standard SQL 2011 Extension Temporal

Tables Procédure versioning() Colonne type tstzrange Synergie avec l'héritage

de table

26

Utilisation de triggers pour indexer les champs essentiels dans du

contenu JSON

TRIGGERS D’INDEXATION

27

TRIGGERS D’INDEXATION

Cas traditionnel : données structurées

SKU name price stock color99_B33W Blue Shoes 64.99 6 blue54_A23B Umbrella 19.99 55 black11_R22F AiePhone 45.55 1200 white98_N26T Chair 63.00 8 null

28

TRIGGERS D’INDEXATION

Stockage JSON : données fusionnées dans une seule

colonne

id attributes101 {‘SKU’: ’99_B33W’, ‘name’: ‘Blue Shoes’, ’price’: 64.99 …}106 {‘SKU’: ’54_A23B’, ‘name’: ‘Umbrella’, ’price’: 19.99 …}115 {‘SKU’: ’11_R22F’, ‘name’: ‘AiePhone’, ’price’: 45.55 …}132 {‘SKU’: ’98_N26T’, ‘name’: ‘Chair’, ’price’: 63 …}

SKU name price stock color99_B33W Blue Shoes 64.99 6 blue54_A23B Umbrella 19.99 55 black11_R22F AiePhone 45.55 1200 white98_N26T Chair 63.00 8 null

29

TRIGGERS D’INDEXATION

catalog_198

id_catalog mapping

198 {‘id’: ‘SKU’, ‘prix’: ‘price’, ‘titre’: ‘name’, … }

catalog_format

CREATE TRIGGER xxx after INSERT OR UPDATE OF mappingON catalog_format

id attributes101 {‘SKU’: ’99_B33W’, ‘name’: ‘Blue Shoes’, ’price’: 64.99 …}106 {‘SKU’: ’54_A23B’, ‘name’: ‘Umbrella’, ’price’: 19.99 …}115 {‘SKU’: ’11_R22F’, ‘name’: ‘AiePhone’, ’price’: 45.55 …}132 {‘SKU’: ’98_N26T’, ‘name’: ‘Chair’, ’price’: 63 …}

create index on catalog_198using btree(attributes->>’SKU’)

30

TRIGGERS D’INDEXATION

create index on catalog_198using GIN (attributes)

Va indexer toutes les propriétés d’un produit, trop volumineux et coûteux

SELECT attributesFROM catalog_198WHERE attributes @> ’{"SKU”: "54_A23B”}’

catalog_198

id attributes101 {‘SKU’: ’99_B33W’, ‘name’: ‘Blue Shoes’, ’price’: 64.99 …}106 {‘SKU’: ’54_A23B’, ‘name’: ‘Umbrella’, ’price’: 19.99 …}115 {‘SKU’: ’11_R22F’, ‘name’: ‘AiePhone’, ’price’: 45.55 …}132 {‘SKU’: ’98_N26T’, ‘name’: ‘Chair’, ’price’: 63 …}

ET POURPLUS TARD …

31

PROJETS FUTURS

▸ Création d'index asynchrone avec PGQ▹ Ne pas bloquer lors du changement

de mapping d’un catalogue

▸ Parallélisation des requêtes sur nos 4 serveurs (Citus DB ?)▹ Chaque serveur peut scanner ¼ de

la table

▸ TABLESAMPLE + cache▹ Estimer très rapidement un résultat

sur un échantillon, puis lancer le calcul complet qui sera mis en cache

32

MERCI

MERCIDes questions ?

Contact : mickael.le.baillif@gmail.com guillaume.stoffer@gmail.com