utilisation de postgresql chez lengow
TRANSCRIPT
POSTGRESQL CHEZ LENGOWQuand un poney rencontre un éléphant
Mickael Le Baillif, Guillaume Stoffer
Meetup PostgreSQL Nantes #22016-04-26
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
RÉPLICATION
ET HAUTE
DISPONIBILITÉ
14
Datacenter 1 Datacenter 2
WAL shipping
WA
L s
hip
pin
g
WA
L s
hip
pin
g
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
<?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>
JS
ON
EAN Titre Marqu
e
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>
XML ET
XPATH
22
<?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 required
FROM fields
XML ET
XPATH
23
<?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 required
FROM 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"/>
XML ET
XPATH
24
<?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 required
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
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 …}
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
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 …}
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