Transcript
Page 1: Utilisation de PostgreSQL chez Lengow

POSTGRESQL CHEZ LENGOWQuand un poney rencontre un éléphant

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

Page 2: Utilisation de PostgreSQL chez Lengow

NOTRE METIER

2

Page 3: Utilisation de PostgreSQL chez Lengow

NOTRE METIER

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

Page 4: Utilisation de PostgreSQL chez Lengow

NOS BESOINS EN BDD

4

Page 5: Utilisation de PostgreSQL chez Lengow

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

Page 6: Utilisation de PostgreSQL chez Lengow

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

Page 7: Utilisation de PostgreSQL chez Lengow

ARCHITECTURE POSTGRESQL

7

Page 8: Utilisation de PostgreSQL chez Lengow

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

Page 9: Utilisation de PostgreSQL chez Lengow

ARCHITECTURE MATERIELLE

9

Serveur physique

Page 10: Utilisation de PostgreSQL chez Lengow

ARCHITECTURE MATERIELLE

10

Processeursmulti-coeurs

Page 11: Utilisation de PostgreSQL chez Lengow

ARCHITECTURE MATERIELLE

11

Données “chaudes” intégralement en RAM

Page 12: Utilisation de PostgreSQL chez Lengow

ARCHITECTURE MATERIELLE

12

Persistence disque sur SSD

Page 13: Utilisation de PostgreSQL chez Lengow

ET SI ÇA CRASHE ?

Page 14: Utilisation de PostgreSQL chez Lengow

RÉPLICATION ET HAUTE DISPONIBILITÉ

14Datacenter 1 Datacenter 2

WAL shipping

WA

L shipping

WA

L shippingmaster slave

slaveslave

Page 15: Utilisation de PostgreSQL chez Lengow

RÉPLICATION ET HAUTE DISPONIBILITÉ

15 @IP virtuelle RW

@IP virtuelle RO

Page 16: Utilisation de PostgreSQL chez Lengow

ARCHITECTURE LOGIQUE

16

JSONB

XMLCSVJSON…

XMLCSVJSONMarchands

Diffuseurs

Page 17: Utilisation de PostgreSQL chez Lengow

IMPLÉMENTATIONS INTÉRESSANTES

17

Page 18: Utilisation de PostgreSQL chez Lengow

18

XML ET XPATH

Extraction de données utiles dans des documents XML

directement à partir de requêtes SQL

Page 19: Utilisation de PostgreSQL chez Lengow

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>

Page 20: Utilisation de PostgreSQL chez Lengow

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>

Page 21: Utilisation de PostgreSQL chez Lengow

21

XML ET XPATH

Page 22: Utilisation de PostgreSQL chez Lengow

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

Page 23: Utilisation de PostgreSQL chez Lengow

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"/>

Page 24: Utilisation de PostgreSQL chez Lengow

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

Page 25: Utilisation de PostgreSQL chez Lengow

25

SYSTEM VERSION

Standard SQL 2011 Extension Temporal

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

de table

Page 26: Utilisation de PostgreSQL chez Lengow

26

Utilisation de triggers pour indexer les champs essentiels dans du

contenu JSON

TRIGGERS D’INDEXATION

Page 27: Utilisation de PostgreSQL chez Lengow

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

Page 28: Utilisation de PostgreSQL chez Lengow

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

Page 29: Utilisation de PostgreSQL chez Lengow

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’)

Page 30: Utilisation de PostgreSQL chez Lengow

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 …}

Page 31: Utilisation de PostgreSQL chez Lengow

ET POURPLUS TARD …

31

Page 32: Utilisation de PostgreSQL chez Lengow

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

Page 33: Utilisation de PostgreSQL chez Lengow

MERCI

MERCIDes questions ?

Contact : [email protected] [email protected]


Top Related