Devoxx 2023 - Profitez de PostgreSQL pour passer à la vitesse supérieure (Emmanuel REMY)

https://www.youtube.com/watch?v=Zfdx2URaEOk

Excellent talk sur quelques fonctionnalités “hors SQL” de postgres.

J’aime beaucoup le choix du sujet, en tant que dev nous n’avons pas toujours le réflexe de s’intéresser à ce genre de choses ni d’y penser pour résoudre un problème.

Le choix des fonctionnalités présentées est également plutôt pertinent avec des fonctionnalités pouvant servir dans pas mal de cas.

Et surtout, ça montre une fois de plus que postgres est vraiment un formidable outil pour les développeurs, on ne le dira jamais assez :)

Type custom (ip, cidr, )

Les types existants (intégrés ou via extensions) sont peut-être le cas le plus spécifique, mais à garder en tête pour certaines applis.

Les domaines peuvent être intéressants pour ajouter du métier dans la structure des tables (avec des types plus parlants) et pour limiter la duplication de code :

CREATE DOMAIN tel_type AS TEXT CHECK (value ~ '^\+336\d{8}$');

select '+337'::tel_type;
-- ERROR: value for domain tel_type violates check constraint "tel_type_check"

select '+33612345678'::tel_type;
--+33612345678

Génération de donnée

Très pratique pour avoir des données de tests, ça peut avantageusement remplacer des scripts dans le cadre de benchmark ou autre.

-- de 5 à 100 avec un step de 3
select generate_series(5, 100, 3);

-- de j-10 à aujourd'hui, avec un step de 1 jour
select generate_series(current_date - interval '10 days', current_date, interval '1 day');

L’extension anon permet soit de générer des données soit de remplacer dynamiquement (ou non) des données existantes.

-- pour tester facilement :
-- docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=abcdef registry.gitlab.com/dalibo/postgresql_anonymizer:stable

select anon.init();

-- génération de données à la volée. à insérer dans une table par ex
select
	anon.fake_last_name() as lastname,
	anon.fake_first_name() as firstname,
	anon.fake_email() as email,
	case when (id % 3 = 0) then 'FINANCE'
		 when (id % 3 = 1) then 'DEV'
		 when (id % 3 = 2) then 'COMM'
	end as service
from generate_series(1, 10) id;

Pas mal de cas d’usage potentiels : tests, benchmarks, …

Partition de données

Cas d’usage moins fréquents et plutôt dans le cadre d’optimisation, mais sur de grosses tables c’est toujours bon d’avoir ça en tête.

Partition par LIST pour regrouper les données au sens métier

-- création de la table maitre, qui ne contient pas de données
create table customers(id serial, name text, email text, service text)
partition by list(service);

-- création des tables par partition
create table customers_finance partition of customers for values in ('FINANCE');
create table customers_dev partition of customers for values in ('DEV');
create table customers_comm partition of customers for values in ('COMM');

-- insert some data
insert into customers (name, email, service)
select
  anon.fake_last_name(),
  anon.fake_email(),
  case when (id % 3 = 0) then 'FINANCE'
	   when (id % 3 = 1) then 'DEV'
	   when (id % 3 = 2) then 'COMM'
  end
from generate_series(1, 1000) id

-- scan les 3 tables
explain select * from customers

-- scan uniquement customers_dev
explain select * from customers where service = 'DEV'

Partition par HASH pour répartir uniformément les données

create table customers(id serial, name text, email text, service text)
partition by hash(id);

Il est possible de configurer le stockage des différentes partitions pour en mettre certaines sur un SSD et d’autres non par exemple.

create tablespace ssd location '/var/tmp/pg';

create table customers_dev partition of customers for values in ('DEV') tablespace ssd;

Anonymisation des données

Il est parfois difficile de maintenir des bases de démo à jour lorsque certaines fonctionnalitées dépendent de la date du jour.

Un dashboard listant les actions à faire avant X jours va rapidement finir entièrement en alerte par exemple.

On pourrait grâce à cela imaginer créer un rôle pour lequel les données sensibles sont anonymisées directement sur la copie d’une base à jour.

-- configure le security label

-- valeur en dur
security label for anon on column public.customers.name
  is 'MASKED WITH VALUE $$NO_NAME$$';

-- null
security label for anon on column public.customers.id
  is 'MASKED WITH VALUE NULL';

-- anonymisée
security label for anon on column public.customers.email
  is 'MASKED WITH VALUE anon.pseudo_email(customers.name)';

-- créer un role 'analyst'
create role analyst with login;
alter role analyst with password 'adsoftware';
grant select on all tables in schema public to analyst;

-- config
security label for anon on role analyst is 'MASKED';

-- lance l'anonymisation dynamique
select anon.start_dynamic_masking();

-- changer de connexion pour utiliser le rôle 'analyst'
select * from customers;
-- NULL, 'NO_NAME', 'rileyjoshua@washington-lang.com', 'DEV'
-- ...

-- arret du mask
select anon.stop_dynamic_masking();

-- suppression
select anon.remove_masks_for_all_columns();

Row level security

Permet de ne retourner que les lignes que l’utilisateur à le droit de voir.

Peut simplifier la gestion multi-tenant plutôt que de le traiter de manière logicielle dans le back par exemple.

L’exemple donné fait matcher le nom d’un rôle avec une colonne d’une table.

On peut donc avec un role ‘role_nukacola’ qui n’a accès qu’aux lignes dont la colonne company contient ‘nukacola’ par exemple.

-- création de données de test
create table addresses as
select
	id as client_id,
	case when (id % 3 = 0) then 'FINANCE - ADRESSE'
		when (id % 3 = 1) then 'DEV - ADRESSE'
		when (id % 3 =2 ) then 'COMM - ADRESSE'
	end as address
from generate_series(1, 1000) id;

-- test, on voit tout
select c.name, c.service, a.address
from customers c
inner join addresses a on c.id = a.client_id

-- on créer un role spécifique
create role soc_security;
grant all on all tables in schema public to soc_security;

-- active le rls sur la table customers
alter table customers enable row level security;

-- à partir de ce moment, le role soc_security ne voit plus de lignes
set role soc_security;
select * from customers

reset role;

create policy open_bar on customers using (true)
-- soc_security voit toutes les lignes

-- cas plus utile : on se base sur le nom du role (après le underscore) qu'on fait matcher avec la colonne service
create policy sp_access_by_service on customers
	as restrictive
	for select to soc_security
	using (service = upper(substring(current_user from '.*_(.*)$')))

-- on matchera donc sur "DEV"
create role soc_dev;
grant soc_security to soc_dev

set role soc_dev

-- ne voit que les lignes service = 'DEV'
select * from customers

Exemple avec la table addresses : on se base sur la table customers et donc la policy déjà créée

create policy sp_access_addresses_by_service on addresses
  for select to soc_security
  using(
    exists(
      select 1 from customers
      where customers.id = addresses.client_id
    )
  )

set role soc_dev
-- ne voit que les lignes 'DEV'
select * from addresses

Cron

Pratique et plus simple qu’appeler pg à travers un script bash avec la gestion des dépendances et des secrets que ça peut impliquer.

Attention, l’extension n’a pas l’air disponible sur AWS pour des raisons qu’on imagine assez bien.

create table data (value int, event_time timestamptz);

-- création d'un job
select * from cron.schedule('* * * * *', '$$insert into data values (random() * 1000, now()$$');

-- liste des jobs
select * from cron.job;

-- désactivation
update cron.job set active='f' where jobid=22;

-- suppression
select * from cron.unschedule(22);

Ecrire des fonctions dans d’autres langages

Possibilité d’utiliser Rust Python, JS, Java, Rust, … pour créer des fonctions.

create or replace function lcase(s TEXT) returns text strict language plrust as $$
	let lcase = s.to_lowercase();
	Ok(some(lcase)
$$;

Foreign data wrapper

Fonctionnalité permettant d’avoir des tables externes à la base comme des fichiers CSV par exemple.

Pour l’avoir vu utilisé ça peut être pratique pour des gens très orienté SQL mais ça donne des choses pas toujours simple à reproduire, tester, …

On préfèrera en général valider et intégrer les données via du soft

Support du JSON

Le support du JSON est disponible depuis pas mal de temps donc je ne rentre pas dans les détails mais Emmanuel rappelle à juste titre que c’est ce qui a fait décoller postgres en 2014 et que c’est vraiment une fonctionnalité importante.

De notre côté la possibilité d’avoir du relationel avec un pointe de noSQL lorsque c’est adapté à vraiment fait pencher la balance du côté de PG et limité les cas ou le noSQL était envisagé.

Envie de faire de l’event sourcing ? une table avec le payload en JSONB et vous avez le meilleur des deux mondes.

create table events (
	id uuid primary key,
	at timestamptz not null,
	aggregate_type text not null,
	aggregate_id uuid not null,
	event_payload jsonb not null
)

à noter une présentation de la syntaxe “array” pour l’accès aux champs dont tout le monde n’aura peut-être pas connaissance et qui peut faciliter la vie suivant ses goûts.

-- syntaxe classique
select jdoc->'tel'->0 from ma_table;

-- syntaxe 'array'
select jdoc['tel'][0] from ma_table;