utilisation de postgresql chez lengow

POSTGRESQL CHEZ LENGOWQuand un poney rencontre un éléphant

Mickael Le Baillif, Guillaume Stoffer

Meetup 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

Processeurs

multi-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É

14

Datacenter 1 Datacenter 2

WAL shipping

WA

L s

hip

pin

g

WA

L s

hip

pin

g

RÉPLICATION

ET HAUTE

DISPONIBILITÉ

15@IP virtuelle RW

@IP virtuelle RO

ARCHITECTURE

LOGIQUE

16

JSONB

XML

CSV

JSON

…

XML

CSV

JSONMarchands

Diffuseurs

IMPLÉMENTATIONS

INTÉRESSANTES

17

XML ET

XPATH

18

Extraction de données utiles

dans des documents XML

directement à partir de

requêtes SQL

XML ET

XPATH

19 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>

XML ET

XPATH

20 La structure du fichier à envoyer aux diffuseurs est

spécifiée en XML











</group>

JS

ON

EAN Titre Marqu

e

Prix HT Prix

TTC

Frais

port[

{

"EAN": "...",

"Titre": "...",

"Marque": "...",

"Prix HT": 2,

"Prix TTC": 3,

"Frais port": 1

}

]


<Products>

<Product>

<EAN>...</EAN>

<Titre>...</Titre>

<Marque>...</Marque>

<Prix_HT>...</Prix_HT>

<Prix_TTC>...</Prix_TTC>

<Frais_port>...</Frais_port>

</Product>

</Products>

XML ET

XPATH

21

XML ET

XPATH

22











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

FROM fields

XML ET

XPATH

23











</group>

WITH fields AS



WHERE id = 915)




FROM fields

f


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



XML ET

XPATH

24











</group>

WITH fields AS



WHERE id = 915)




FROM fields

id name required

1 EAN t

2 Titre Produit t

3 Marque t

4 Prix HT t

SYSTEM

VERSION

25

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

TRIGGERS

D’INDEXATION

27 Cas traditionnel : données structurées

SKU name price stock color

99_B33W Blue Shoes 64.99 6 blue

54_A23B Umbrella 19.99 55 black

11_R22F AiePhone 45.55 1200 white

98_N26T Chair 63.00 8 null

TRIGGERS

D’INDEXATION

28 Stockage JSON : données fusionnées

dans une seule colonne

id attributes

101 {‘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 color

99_B33W Blue Shoes 64.99 6 blue

54_A23B Umbrella 19.99 55 black

11_R22F AiePhone 45.55 1200 white

98_N26T Chair 63.00 8 null

TRIGGERS

D’INDEXATION

29

catalog_198

id_catalog mapping

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

catalog_format

CREATE TRIGGER xxx after INSERT

OR UPDATE OF mapping

ON catalog_format

id attributes





create index on catalog_198

using btree(attributes->>’SKU’)

TRIGGERS

D’INDEXATION

30

create index on catalog_198

using GIN (attributes)

Va indexer toutes les propriétés d’un produit, trop

volumineux et coûteux

SELECT attributes

FROM catalog_198

WHERE attributes @> ’{"SKU”: "54_A23B”}’

catalog_198

id attributes





ET POUR

PLUS 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 : [email protected]

[email protected]

utilisation de postgresql chez lengow

Data & Analytics