Dans un précédent article, nous avons présenté les plages de valeurs. Nous allons voir qu’avec ce type de données, il est possible, entre autres, de mettre en place un contrôle sur les chevauchements de données. Pour cela, faisons un peu de pratique avec la mise en place du modèle de données d’une application de location de vélos, nécessitant une gestion de chevauchement des locations et une recherche de plages de disponibilité.

Nous comparons la mise en oeuvre et les performances d’une plage de dates et la gestion d’intervalles avec l’utilisation de dates de début et de fin utilisant les types scalaires traditionnels.

Plusieurs stratégies existent :

1. Deux colonnes : start_datetime et end_datetime

La forme la plus classique consiste à stocker les bornes dans deux colonnes. Le problème : aucune contrainte SQL native n’empêche deux lignes de se chevaucher en combinant deux colonnes. Il faut alors recourir à un déclencheur vérifiant, à l’insertion ou la mise à jour, qu’aucune autre réservation n’entre en conflit.

2. Colonnes range + contrainte d’exclusion

Une solution plus élégante, disponible depuis PostgreSQL 9.2, consiste à utiliser un type range (tsrange, tstzrange, daterange) et une contrainte d’exclusion avec un index GiST.

3. PostgreSQL 18+ : la clause WITHOUT OVERLAPS

Depuis PostgreSQL 18, il est désormais possible de déclarer cette logique directement dans la clé primaire ou une contrainte unique. Pour cela, on utilise la clause WITHOUT OVERLAPS pour la dernière colonne de la contrainte d’unicité.

Et en pratique

Créons trois tables correspondant aux trois stratégies :

• rents_exclude : contient des locations de vélos utilisant le type plage de dates et la contrainte d’exclusion ;
• rents_wo_overlaps : les mêmes locations utilisant le type plage de dates et la clause WITHOUT OVERLAPS ;
• rents_2_dates : les mêmes locations de vélos que dans la table rents_exclude mais en utilisant deux dates.

Avec deux dates

Voici comment nous pouvons gérer des locations avec une date de début et une date de fin utilisant des types timestamptz.

Nous créons une table contenant l’id de location, les dates et heures de début de la location, de fin de la location et l’identifiant du vélo.

CREATE TABLE rents_2_dates (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    start_datetime timestamptz,
    end_datetime timestamptz,
    bike_id INT,
    CONSTRAINT end_date_sup_start_date_chk CHECK (end_datetime > start_datetime)
);

CREATE INDEX rent2dates_start_datetime_idx ON rents_2_dates USING btree(start_datetime);
CREATE INDEX rent2dates_end_datetime_idx ON rents_2_dates USING btree(end_datetime);
CREATE INDEX rent2dates_bike_id_idx ON rents_2_dates USING btree(bike_id);

Nous utilisons une contrainte CHECK pour vérifier que end_datetime est supérieure à start_datetime.

Nous créons ensuite un déclencheur de contrôle sur les commandes INSERT et UPDATE, et sa fonction en langage PL/pgSQL, assurant l’intégrité des données. Dans le cas présent, il s’agit d’empêcher le chevauchement de périodes de location pour un même vélo :

CREATE OR REPLACE FUNCTION check_rent_datetime_validity()  RETURNS TRIGGER AS
$rents_2_dates$
DECLARE counting_of_existing_rentals TEXT;
DECLARE number_of_rents INTEGER;
BEGIN
    counting_of_existing_rentals := '
    SELECT COUNT(*) AS count_rent
        FROM rents_2_dates
        WHERE (($1 >= start_datetime  AND $1 < end_datetime   )
                OR ($2 > start_datetime AND $2 < end_datetime  ) )
        AND bike_id = $3 ';
    IF (TG_OP = 'UPDATE') THEN
        EXECUTE counting_of_existing_rentals || ' AND id <> $4'
        INTO number_of_rents
        USING
        new.start_datetime
        ,new.end_datetime
        ,new.bike_id
        ,new.id;
    ELSIF (TG_OP = 'INSERT') THEN
        EXECUTE counting_of_existing_rentals
        INTO number_of_rents
        USING
        new.start_datetime
        ,new.end_datetime
        ,new.bike_id;
    END IF;
    IF number_of_rents > 0 THEN
        RAISE EXCEPTION 'Cette plage horaire n''est pas disponible pour bike_id % pour la période % - % ', new.bike_id, new.start_datetime, new.end_datetime ;
        RETURN NULL;
    END IF;
    RETURN NEW;
END;
$rents_2_dates$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_rent_datetime_validity
  BEFORE INSERT OR UPDATE
  ON rents_2_dates
  FOR EACH ROW
  EXECUTE PROCEDURE check_rent_datetime_validity();

Maintenant que le déclencheur de contrôle est prêt, passons à quelques tests.

Insérons une location pour le vélo d’id 7, le 2025-09-01 de 09:00 à 11:00 et de 12:00 à 18:00 :

\set test_day '''2025-09-01'''::date

INSERT INTO rents_2_dates
  ( start_datetime
    ,end_datetime
    ,bike_id)
VALUES
  (:test_day + interval '09 hour'
    ,:test_day + interval '11 hour'
    , 7
),
  (:test_day + interval '12 hour'
    ,:test_day + interval '18 hour'
    , 7
)
 RETURNING *;

┌─────────┬────────────────────────┬────────────────────────┬─────────┐
│   id    │     start_datetime     │      end_datetime      │ bike_id │
├─────────┼────────────────────────┼────────────────────────┼─────────┤
│ 1       │ 2025-09-01 09:00:00+02 │ 2025-09-01 11:00:00+02 │       7 │
│ 2       │ 2025-09-01 12:00:00+02 │ 2025-09-01 18:00:00+02 │       7 │
└─────────┴────────────────────────┴────────────────────────┴─────────┘

Avec l’outil psql, vous pouvez définir des variables lors de la session avec \set. Pour les tests suivants, nous allons affecter test_day avec la valeur '2025-09-01'::date.

Procédons alors au test d’insertion dans une plage horaire déjà occupée. Tentons d’insérer une location pour ce même bike_id de 10:00 à 15:00 :

INSERT INTO rents_2_dates
  ( start_datetime
    , end_datetime
    , bike_id )
VALUES
  (:test_day + interval '10 hour'
    ,:test_day + interval '15 hour'
    , 7
);
ERROR:  Cette plage horaire n''est pas disponible pour bike_id 7 pour la période 
2025-09-01 10:00:00+02 - 2025-09-01 15:00:00+02
CONTEXT:  PL/pgSQL function check_rent_datetime_validity() line 31 at RAISE

La fonction de contrôle remonte une erreur nous indiquant que la plage horaire n’est pas disponible et la transaction est annulée.

Après le test d’insertion, nous faisons un autre test, de mise à jour cette fois, en élargissant la période de la location 2 (12:00 à 18:00) à 10:00-18:00 empiétant sur la location 1 (09:00 à 11:00).

UPDATE rents_2_dates
  SET start_datetime = :test_day + interval '10 hour'
  WHERE id = 2;

ERROR:  Cette plage horaire n''est pas disponible pour bike_id 7 pour la période 
2025-09-01 10:00:00+02 - 2025-09-01 18:00:00+02
CONTEXT:  PL/pgSQL function check_rent_datetime_validity() line 31 at RAISE

Même phénomène que précédemment, le contrôle de la contrainte géré par la fonction annule la mise à jour et renvoie un message d’erreur avec le label défini.

Plage de dates et contraintes d’exclusion

Utilisant le même principe, nous créons une table en remplaçant les deux colonnes date heure de début et de fin par une colonne de type range, ici un TSTZRANGE :

CREATE EXTENSION btree_GiST;
DROP TABLE IF EXISTS rents_exclude;
CREATE TABLE rents_exclude (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
    , rent_dates TSTZRANGE
    , bike_id INT
    , EXCLUDE USING GiST (rent_dates WITH &&, bike_id with =)
);

L’index créé est le suivant :

"rents_exclude_rent_dates_bike_id_excl" 
   EXCLUDE USING GiST (rent_dates WITH &&, bike_id WITH =)

Nous avons un index multicolonne dont l’ordre est rent_dates, bike_id.

Pourquoi devons-nous installer l’extension btree_GiST ? La liste des classes d’opérateurs pour un index GiST est assez réduite :

 index_method |  opclass_name  | indexed_type  | is_default 
--------------+----------------+---------------+------------
 gist         | box_ops        | box           | t
 gist         | circle_ops     | circle        | t
 gist         | multirange_ops | anymultirange | t
 gist         | point_ops      | point         | t
 gist         | poly_ops       | polygon       | t
 gist         | range_ops      | anyrange      | t
 gist         | tsquery_ops    | tsquery       | t
 gist         | tsvector_ops   | tsvector      | t

L’extension btree_GiST fournit des classes d’opérateurs GiST supplémentaires équivalentes à celui du Btree pour les types de données numériques, temporelles et texte. Ces opérateurs sont donc utilisables par les index, ce qui permet d’affiner la contrainte d’exclusion. Dans notre cas, en plus de la contrainte de chevauchement && sur la plage de date, on y rajoutera la contrainte de bike_id.

Passons à quelques tests d’insertion et validons le comportement d’exclusion. Prenons le vélo 7 n’ayant pas d’entrées dans la table rent_exclude.

Insérons deux locations : une le 2025-09-01 de 09:00 à 11:00 et une de 12:00 à 18:00.

\set test_day '''2025-09-01'''::date

INSERT INTO rents_exclude ( rent_dates, bike_id )
VALUES
  ( TSTZRANGE (:test_day + interval '09 hour',:test_day + interval '11 hour'), 7 ),
  ( TSTZRANGE (:test_day + interval '12 hour' ,:test_day + interval '18 hour'), 7 )
RETURNING *;

┌─────────┬─────────────────────────────────────────────────────┬─────────┐
│   id    │                     rent_dates                      │ bike_id │
├─────────┼─────────────────────────────────────────────────────┼─────────┤
│ 1       │ ["2025-09-01 09:00:00+02","2025-09-01 11:00:00+02") │       7 │
│ 2       │ ["2025-09-01 12:00:00+02","2025-09-01 18:00:00+02") │       7 │
└─────────┴─────────────────────────────────────────────────────┴─────────┘

Insérons une location pour le vélo 7 sur une période déjà occupée (12:00 à 18:00) :

\set test_day '''2025-09-01'''::date
INSERT INTO rents_exclude(rent_dates, bike_id)
VALUES( TSTZRANGE ( :test_day + interval '11 hour' ,:test_day + interval '17 hour' ) ,7 );

ERROR: conflicting key value violates exclusion constraint "rents_bike_id_rent_dates_excl"
DETAIL: Key (rent_dates, bike_id)=(7, ["2025-09-01 11:00:00+01","2025-09-01 17:00:00+01")) 
conflicts with existing key 
(rent_dates, bike_id=(7, ["2025-09-01 12:00:00+01","2025-09-01 18:00:00+01")).

Comme on le constate, une tentative d’insertion a échoué dans un créneau déjà occupé pour le même bike_id en raison de la contrainte d’exclusion mise en place.

Après le test d’insertion, nous faisons un autre test, de mise à jour cette fois, en élargissant la période de la location 2 (12:00 à 18:00) à 10:00-18:00 en empiétant sur la location 1 (09:00 à 11:00).

UPDATE  rents_exclude 
set rent_dates = TSTZRANGE(:test_day + interval '10 hour',:test_day + interval '18 hour')
where id  = 2;

ERROR: conflicting key value violates exclusion constraint "rents_rent_dates_bike_id_excl"
DETAIL: Key (rent_dates, bike_id)=(["2025-09-01 10:00:00+02","2025-09-01 18:00:00+02"), 7) 
conflicts with existing key 
(rent_dates, bike_id)=(["2025-09-01 09:00:00+02","2025-09-01 11:00:00+02"), 7).

La transaction est annulée et un message d’erreur nous indique une violation de contrainte, car le créneau est déjà occupé pour le même bike_id.

Plage de dates et la clause WITHOUT OVERLAPS

À présent, utilisons la nouvelle fonctionnalité “WITHOUT OVERLAPS”.

Si cette option WITHOUT OVERLAPS est spécifiée pour la dernière colonne, celle-ci est alors vérifiée pour les chevauchements plutôt que pour l’égalité. Dans ce cas, les autres colonnes de la contrainte autorisent les doublons, à condition qu’ils ne se chevauchent pas dans la colonne WITHOUT OVERLAPS.

CREATE EXTENSION btree_GiST;
CREATE TABLE rents_wo_overlaps (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    rent_dates TSTZRANGE,
    bike_id INT,
    UNIQUE (bike_id, rent_dates  WITHOUT OVERLAPS )
);

Dans ce cas, l’index est le suivant :

    "rents_wo_overlaps_bike_id_rent_dates_key" 
    UNIQUE (bike_id, rent_dates WITHOUT OVERLAPS)

L’ordre des colonnes est différent de celui créé pour la table rents_exclude, nous avons bike_id, rent_dates.

Comme pour la contrainte d’exclusion un index de type GiST est créé avec les mêmes types de contraintes. Le comportement est donc le même que pour la table rents_exclude.

Performance et taille sur le disque

Dans cette partie, nous allons contrôler les temps d’insertions avec des volumes conséquents, le volume des index et analyser les temps d’exécution des requêtes.

Performance en insertion

La création du jeu de données s’est effectuée de la façon suivante :

• insertion de 1000 vélos de catégorie aléatoire ;
• création de trois locations par jour pour chaque vélo sur deux années calendaires.

Ce qui produit une table avec 2 190 000 entrées.

Le jeu de test est produit et inséré sur un ordinateur de bureau avec la configuration par défaut de PostgreSQL.

Vous pouvez trouver l’ensemble du jeu de test sur notre gitlab.

Tout d’abord, comparons les vitesses d’insertions :

• Insertion dans la table rents_2_dates, où le contrôle d’intégrité des données se fait par une fonction déclencheur :

set track_functions = 'pl';
select pg_stat_reset();
INSERT INTO rents_2_dates;
INSERT 0 2190000
Time: 244937,342 ms (04:04,937)

• Insertion dans la table rents_exclude, où le contrôle d’intégrité des données se fait par contrainte d’exclusion :

INSERT INTO rents_exclude;
INSERT 0 2190000
Time: 148393,870 ms (02:28,394)

• Insertion dans la table rents_wo_overlaps, où le contrôle d’intégrité des données se fait par contrainte d’exclusion et clause WITHOUT OVERLAPS :

INSERT INTO rents_wo_overlaps;
INSERT 0 2190000
Time: 119230,806 ms (01:59,231)

Les opérations d’insertion sont plus performantes avec le type range, dans la mesure où le contrôle d’intégrité est assuré directement par la contrainte d’exclusion. En revanche, dans la table rents_2_date, ce contrôle est effectué par un déclencheur qui vérifie pour chaque ligne insérée ou mise à jour, l’absence de tout chevauchement avec les enregistrements existants.

Il est possible d’obtenir certaines informations sur le temps d’exécution de la fonction grâce à la vue pg_stat_user_functions. Pour cela, il faut activer le paramètre track_functions dans la configuration de l’instance.

Dans notre cas, nous avons activé le traçage sur les fonctions PL/pgSQL dans la transaction d’insertion des données via la commande :

set track_functions = 'pl';

Après l’import des données, voici les informations :

 select funcname, calls , (self_time || ' milliseconds')::interval as self_time from pg_stat_user_functions ;
┌──────────────────────────────┬─────────┬─────────────────┐
│           funcname           │  calls  │    self_time    │
├──────────────────────────────┼─────────┼─────────────────┤
│ check_rent_datetime_validity │ 2190000 │ 00:03:51.925302 │
└──────────────────────────────┴─────────┴─────────────────┘

Le temps total d’insertion est de 04 min 05s. 03 min 52s sont occupées par le traitement de la fonction, soit plus de 90% du temps.

Volume des index

L’index GiST est une structure arborescente équilibrée comme le Btree contenant des paires clé, pointeur, mais les clés GiST sont plus complexes qu’un Btree, ce qui amène un volume plus important.

D’un côté, les tables rents_exclude et rents_wo_overlaps disposent d’un seul index GiST spécifique pour le range :

  "rents_pkey" PRIMARY KEY, btree (id)
  "rents_bike_id_rent_dates_excl" EXCLUDE USING GiST (rent_dates WITH &&, bike_id WITH =)

De l’autre, la table rents_2_dates avec trois index de type Btree :

  "rents_2_dates_pkey" PRIMARY KEY, btree (id)
  "rent2dates_end_datetime_idx" btree (end_datetime)
  "rent2dates_start_datetime_idx" btree (tart_datetime)
  "rent2dates_bike_id_idx" btree (stat_datetime)

On trouve ci-dessous un tableau donnant par table le volume des données et le volume des index :

┌─────────────────────────────────────┬────────────┬──────────────┬────────────┐
│             table_name              │ table_size │ indexes_size │ total_size │
├─────────────────────────────────────┼────────────┼──────────────┼────────────┤
│ "ams_rent_bike"."rents_exclude"     │ 126 MB     │ 222 MB       │ 348 MB     │
│ "ams_rent_bike"."rents_2_dates"     │ 126 MB     │ 97 MB        │ 223 MB     │
│ "ams_rent_bike"."rents_wo_overlaps" │ 126 MB     │ 229 MB       │ 354 MB     │
└─────────────────────────────────────┴────────────┴──────────────┴────────────┘

Voyons maintenant le volume de chaque index :

┌───────────────────┬──────────────────────────────────────────┬────────────┬────────┬─────────────────────┐
│     rel_name      │                index_name                │ type_index │  size  │       columns       │
├───────────────────┼──────────────────────────────────────────┼────────────┼────────┼─────────────────────┤
│ rents_wo_overlaps │ rents_wo_overlaps_bike_id_rent_dates_key │ GiST       │ 182 MB │ rent_dates, bike_id │
│ rents_exclude     │ rents_exclude_rent_dates_bike_id_excl    │ GiST       │ 173 MB │ rent_dates, bike_id │
│ rents_2_dates     │ rent2dates_start_datetime_idx            │ btree      │ 17 MB  │ start_datetime      │
│ rents_2_dates     │ rent2dates_end_datetime_idx              │ btree      │ 17 MB  │ end_datetime        │
│ rents_2_dates     │ rent2dates_bike_id_idx                   │ btree      │ 16 MB  │ bike_id             │
└───────────────────┴──────────────────────────────────────────┴────────────┴────────┴─────────────────────┘

Dans notre cas, nous comparons le volume de la somme des trois index btree liés au stockage sur 2 colonnes avec le volume de l’index GiST de la contrainte d’exclusion et avec l’index GisT de la contrainte d’unicité (qui utilise WITHOUT OVERLAPS). Comme nous l’avons indiqué plus haut, la clé dans ce dernier cas contient à la fois les données de la plage de date et le bike_id.

Le volume est différent entre les index GiST car l’ordre des colonnes n’est pas le même. On s’aperçoit également que chaque index GiST est 3x plus volumineux que la somme des 3 index Btree.

Performance de recherche

Voyons à présent une mise en situation. Nous souhaitons connaître les disponibilités des vélos sur une journée donnée.

Vous trouverez les requêtes dans les liens suivants :

• available_with_range_exclude.sql
• available_with_range_without_overlaps.sql
• available_with_2_dates.sql

Les structures des requêtes sont assez proches les unes des autres ; leur but est de contrôler le bon usage des index et de comparer les temps des réponses.

En ce qui concerne les performances, l’avantage est donné à l’utilisation de deux colonnes.

La différence de temps entre rents_exclude et rents_wo_overlaps est dû a l’ordre des colonnes dans l’index.

Vous pouvez trouver les résultats des EXPLAIN (ANALYSE, buffers) ici :

• available_period_with_2_dates.txt
• available_period_with_range_exclude.txt
• available_period_with_range_without_overlaps.txt

Prenons un autre exemple, la liste des réservations pour une liste de vélos sur une période donnée :

\set search_range TSTZRANGE('''2025-09-01'''::date + interval '''09 hour''' ,'''2025-09-01'''::date + interval ''' 20 hour''')

EXPLAIN (ANALYSE,BUFFERS)
 SELECT * FROM rents r
 WHERE r.rent_dates && :search_range AND r.bike_id < 100;

\set vstart_datetime '''2025-09-01'''::date + interval '''09 hour'''
\set vend_datetime '''2025-09-01'''::date + interval '''20 hour'''

EXPLAIN (ANALYSE,BUFFERS)
SELECT
  *
  FROM rents_2_dates r
  WHERE
  (
      (start_datetime BETWEEN :vstart_datetime AND  :vend_datetime)
  OR
     (end_datetime BETWEEN :vstart_datetime AND :vend_datetime)
  )
  AND r.bike_id < 100
;

Dans ce cas, la lecture des plans d’exécutions nous indique que, pour l’interrogation des données dans la table rents_2_dates, PostgreSQL parcourt trois index :

• rent2dates_start_datetime_idx
• rent2dates_end_datetime_idx
• rent2dates_bike_id_idx

Ces informations sont visibles dans le fichier rents_for_period_some_bikes_2_dates.txt.

Pour la recherche dans la table rents_exclude, il n’y a qu’un parcours d’index :

• rents_bike_id_rent_dates_excl

Ces informations sont visibles dans le fichier rents_for_period_some_bikes_range_exclude.txt.

De plus, nous pouvons constater que moins de blocs de données sont lus.

Pour la recherche dans la table rents_wo_overlaps, il n’y a qu’un parcours d’index :

• rents_wo_overlaps_bike_id_rent_dates_key

Ces informations sont visibles dans le fichier rents_for_period_some_bikes_range_exclude.txt.

Dans notre exemple avec une clause where bike_id < 100, le résultat est le suivant :

En regardant de plus près le plan d’exécution, une piste d’amélioration sur la requête avec deux dates serait d’ajouter un index sur les trois colonnes start_datetime, end_datetime, bike_id.

CREATE INDEX rent2dates_start_end_bike_id_idx ON rents_2_dates USING btree(start_datetime,end_datetime,bike_id);

Effectivement le temps d’exécution est amélioré, car la requête répond en 1.7ms. Néanmoins, dans ce cas, l’index ajoute un volume de 85Mo.

Voir le fichier rents_for_period_some_bikes_2_date_optim.txt.

Idem pour la table rents_wo_overlaps en créant un index GiST (rent_dates, bike_id).

Voici le plan d’execution.

CREATE INDEX rents_wo_overlaps_rents_date_bike_id ON rents_wo_overlaps USING GiST (rent_dates, bike_id)

Le temps d’exécution est de 0.5ms contre 4ms, mais nous rajoutons un index de 112 Mo.

Après l’ajout des index, nous avons les temps suivants :

Conclusion

La gestion des chevauchements temporels est un sujet qui peut devenir un vrai casse-tête lorsqu’il s’agit de modéliser des réservations, des plannings ou des périodes de validité. Le type range facilite la manipulation des données.

Il apporte notamment un avantage indéniable : le contrôle de l’intégrité des données, entièrement géré par la base de données. Même si l’utilisation du type TSTZRANGE ne semble pas naturelle de prime abord, nous pouvons observer qu’une fois mis en place, ce type et les index GiST proposent un ensemble concis et élégant à l’utilisation.

La nouvelle fonctionnalité WITHOUT OVERLAPS apporte une écriture simplifiée mais impose que la contrainte de non-chevauchement se trouve sur la dernière colonne. Dans notre cas, nous avons dû créer un autre index composite avec une meilleure sélectivité sur les premières colonnes.

En ce qui concerne les tests de performance en insertion, l’avantage est en faveur du type range. La contrainte d’exclusion étant intégrée, elle rend l’opération 2,4 fois plus rapide.

Le volume sur disque d’un index GiST est deux à trois fois plus volumineux que la somme des trois index Btree de notre exemple.

Pour l’exécution des requêtes, nous avons testé deux profils de requête : un complexe (CTE, jointure, etc.) et un plus simple (un WHERE dans la table de location). On se rend compte qu’il n’y a pas de grandes différences sur les temps d’exécution.