Postgres pour vos besoins NoSQL

Symfony Live, Paris, France - 27.3.2025

© David Buchmann

David Buchmann - david@liip.ch

PHP Engineer, Liip SA, Suisse

C'est quoi, le NoSQL?

• non-sql
• not only sql
• mais en fait: non-relational

Même EWikipedia nous propose qu'une définition négative:
«A database that focuses on data that is modeled in means other than tabular relations»

Des données JSON dans une bd relational?

• Stocker des données non-structure ou semi-structure
• Evite de dénormaliser tout, même quand on en n'a pas besoin
• Votre "NoSQL database at home"?

Repository

Repository at github.com/dbu/postgres-json

SQL JSON syntax

Définir des colonnes JSON

CREATE TABLE auction_jsonb (
    id SERIAL NOT NULL,
    title VARCHAR(255) NOT NULL,
    current_price INT DEFAULT NULL,
    item JSONB NOT NULL,
    PRIMARY KEY(id)
);
            

• Consommation de disque augmenté: Clefs répété non optimisé
• Si un valeur fait occurence dans plus que 1 / 80 lignes, une colonne séparé consomme moins d'éspace
• Query et joins risquent d'être moins optimisé qu'avec colonnes

heap.io blog

JSON vs JSONB

=> Préfèrez le JSONB sauf si vous avez absolument besoin des comportements non-standard.

JSON data ou valeur?

Filtrer par des valeurs dans le JSON

SELECT COUNT(*)
FROM auction_jsonb
WHERE item->>'author' = 'David';
            

Temps de requête en milliseconds

On ajoute un indexe

CREATE INDEX json_author
ON auction_json_indexed ((item->>'author'));
            

Temps de requête en milliseconds

Apropos: Optimiser le query planner

A 10M lignes, postgres ne voulait plus utiliser l'index, et a décidé d'executer un full table scan

-- Value <= 1.15 makes postgres use the index
ALTER TABLESPACE pg_default
SET (random_page_cost=1.1);
            

Merci à Frank Heikens sur stackoverflow pour proposer random_page_cost

On peut aussi extraire (générer) une colonne

CREATE TABLE auction_generated_fields (
    id SERIAL NOT NULL,
    author VARCHAR(255) generated always
        as (item->>'author') stored,
    item JSONB NOT NULL, PRIMARY KEY(id));
            

Temps de requête en milliseconds

Apropos: Extraire une date (merci jamie@zomglol.wtf)

CREATE FUNCTION text_to_timestamp(text) RETURNS TIMESTAMP
LANGUAGE sql IMMUTABLE AS
$$
SELECT CASE
WHEN $1 ~ '^\d{4}-\d{2}-\d{2}[ |T]\d{2}:\d{2}:\d{2}(\.\d+)?(\+00:00)?$'
    THEN CAST($1 AS timestamp without time zone)
END
$$;

CREATE TABLE auction_generated_fields (
    id SERIAL NOT NULL,
    start_date TIMESTAMP(0) generated always
        AS (text_to_timestamp(item->>'startDate')) stored,
    item JSONB NOT NULL, PRIMARY KEY(id));
            

Postgres nous propose trop des options rédontant

• item->author
• JSON_QUERY(item, '$.author')
• item #> '{author}'
• item['author']

Tous ces constructions ne partagent pas leurs indexes.
=> Choisissez lequel de ces construction vous voulez utiliser

Recherche complèxe en JSON

JSON objet contient un objet (seulement jsonb)

CREATE INDEX auction_json_gin_idx
ON auction_jsonb_gin USING GIN (item);

SELECT COUNT(*)
FROM auction_jsonb_gin
WHERE item @> '{"author": "David"}';
            

    Temps de requête en milliseconds

Generalized Inverted Index (GIN)

Accelère tous les recherches "JSON contient", mais pas d'autres requêtes

Apropos: Distribution des valeurs

La nature des données dans une colonne indexé peut mener à des differences important

Temps de requête en milliseconds, table de 10 million lignes

Performance d'écriture

Jsonb, indexes et champs générées tous demandent leurs prix

Mésuré avec des lignes synthetiques, duré en milliseconds

La consomption du stockage

• Le temps de de traitement dans Postgres augmente linéaires en fonction de la taille du JSON
• Les besoins de stockage augmentent linéaires aussi (pas d'optimisation)
• Pour des nombres des lignes élévé, le GIN demande plus de stockage que les données

Modifier le JSON

Mis-à-jour partiel: Postgres

Manipuler le JSON au lieu de le remplacer complètement

-- update, ignore if not exists
UPDATE auction_jsonb
SET item = jsonb_set(item, '{author}', '"value"', false)
WHERE id=42;

-- update or create
UPDATE auction_jsonb
SET item = jsonb_set(item, '{author}', 'value', true)
WHERE id=42;
            

Postgres remplace tout le JSON, la requête n'est pas accéleré

Mis-à-jour partiel: MySQL

Avec MySQL, on peut aussi manipuler le JSON, les fonctions sont appelé differament

-- update, ignore if not exists
JSON_REPLACE(item, '$.author', '"value"')
-- update or create
JSON_SET(item, '$.author', '"value"')
-- create, ignore if already exists
JSON_INSERT(item, '$.author', '"value"')
            

La doc MySQL postule que le moteur de MySQL peut optimiser pour éviter de recréer tout l'object JSON, mais j'ai pas réussi à mesurer une difference

Supprimer des clés en JSON

-- delete attribute
UPDATE auction_jsonb
SET item = item - 'key'
WHERE ID=42;

-- delete attribute alternate
UPDATE auction_jsonb
SET item = jsonb_set_lax(item, '{author}', null, true, 'delete_key')
WHERE ID=42;

-- delete multiple
item - array['key1', 'key2']
            

set_jsonb_lax, la fonction versatile

set_jsonb_lax(item, '{author}', null, true, 'return_target');
            

Validation avec un schema JSON

• Postgres: Il faut installer une extension
  (pg_jsonschema / postgres-json-schema)
• MySQL: Fournit la fonction JSON_SCHEMA_VALID, à spécifier dans la partie CHECK
  
  
• Si vous serialisez des objets PHP, est-ce que vous avez besoin d'une validation?
• La validation sacrifie de la fléxibilité pour favoriser la stabilité

Quelques autres elements de syntax Postgres

• || concatener array / merger des objects
• jsonb_build_object('key', 'value', 'k2', 'v2')
• jsonb('{"type":"book", "author": "David"}')
• jsonb_serialize(item)
• jsonb_each(item)

Doctrine ORM

Declarer une colonne JSON

#[ORM\Column(type: Types::JSON)]
private array $item;

#[ORM\Column(type: Types::JSON, options: ['jsonb' => true])]
private array $item;
            

Doctrine ne fournit pas des outils à définir des indices sur JSON. On les ajoute directement dans un scripte de migration.

$this->addSql('CREATE INDEX json_author ON auction_json_indexed ((item->>\'author\'))');
            

Définir une colonne généré

#[ORM\Column(
    length: 255,
    nullable: true,
    insertable: false,
    updatable: false,
    columnDefinition: "VARCHAR(255) generated always as (item->>'author') stored",
    generated: "ALWAYS"
)]
private ?string $author = null;
            

Grace à insertable: false, updatable: false, Doctrine sait qu'il faut jamais écrire ce champs dans la bd.
Attention: columnDefinition s'écrit en SQL simple, pas DQL

DQL ne connait pas les constructions JSON

Vous pouvez utiliser Doctrine DBAL pour écrire des requêtes SQL native

# config/packages/doctrine.yaml
doctrine:
  orm:
    dql:
      string_functions:
        JSON_GET_TEXT: Scienta\...\Postgresql\JsonGetText
        JSONB_CONTAINS: Scienta\...\Postgresql\JsonbContains

            

->> comme fonction DQL

$qb = $this->createQueryBuilder('a');
$qb->select('COUNT(a)')
    // Need to qualify with a. in front of item
    ->where("JSON_GET_TEXT(a.item, 'author') = :author")
    ->setParameter('author', $authorName)
;

return $qb->getQuery()->getSingleScalarResult();

@> comme fonction DQL

$qb = $this->createQueryBuilder('a');
$qb->select('COUNT(a)')
    ->where('JSONB_CONTAINS(a.item, :criteria) = true')
    ->setParameter('criteria', ['author' => $authorName], Types::JSON)
;

return $qb->getQuery()->getSingleScalarResult();
            

Doctrine/PHP performance

• Mesures jsonb correspondent à dump SQL contenant données
• Commandes préparées en DBAL ne gagnent pas beaucoup comparé au EM - sauf si on batch par ~ 1'000 insertions
• Inserer des données pour une seule ligne, differences négligibles
• Pour les entities, le plus performante était flush & clear pour 5 entities à la fois

Conclusions

Standardisation?

• Fonctionalitées similaire entre MySQL et Postgres
• SQL:2016 spécifie fonctionalité JSON sur des champs string
• SQL:2023 spécifie des types de donnée et operations pour JSON
• Postgres et MySQL offrent des types dédié pour JSON, mais pas MariaDB
• Les operateurs comme ->> ne sont pas standard
  (Postgres et MySQL , mais pas MariaDB)

• Postgres est trés capable de gérer le JSON
• Difficile de supporter Postgres et MySQL avec le même code
• Des surprises - mais ça va du même si on doit apprendre un base des données NoSQL
• Préferez le jsonb!
• Extraire les informations essentiel dans des colonnes généré

• La performance dépend des particularités de vos données
  Il faut tester avec des données réel ou au moins realiste en quantités expecté!
• Rien ne va de soit (jamais, avec les bases des données)
• Vos tables comptent moins que 10k lignes?
  Faites ce que vous voulez
• Il existe des options pour opérer Postgres en mode distribué pour augmenter les capacités. Mais avant de le faire, vérifiez la configuration de votre serveurs, et optimisez les requêtes
• Pour des recherches frequent ou copmlèxe, considerez un moteur de recherche dédié comme Elasticsearch.
• Ou bien sur, MongoDB peut être une option

Merci & Dankeschön!

github.com/dbu/postgres-json

Mastodon: @dbu@phpc.social

David Buchmann, Liip AG

Scaling/Replicating postgres

Workarounds

• Optimizing postgres configuration can get you a long way
  
  
• Clear out older data, or create a table per time interval to limit growth
  
  
• Put unrelated tables into different databases
• Move those onto separate servers to spread the load
• Doctrine can work with multiple entity managers

Read replicas

• Enable hot-standby mode
• Can be used for read only queries
• All or nothing: Need to replicate all databases of the server with all their content

Logical replicas

• Mirrors queries to replica server
• Can limit to some tables or even specific rows
• Or limit which columns of a table (since postgres 15)
• Can configure replica server to use foreign data wrappers (FDW) to query tables from upstream server
• Combining these techniques, you can scale postgres horizontally

See pgdash.io