ProHoster > Blog > administration > Antipatterns PostgreSQL : navigation dans le registre

Antipatterns PostgreSQL : navigation dans le registre

Aujourd'hui, il n'y aura pas de cas complexes ni d'algorithmes sophistiqués en SQL. Tout sera très simple, au niveau de Captain Obvious - faisons-le consulter le registre des événements triés par temps.

Autrement dit, il y a un signe dans la base de données events, et elle a un champ ts - l'heure exacte à laquelle on souhaite afficher ces enregistrements de manière ordonnée :

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Il est clair que nous n’aurons pas une douzaine d’enregistrements là-bas, nous aurons donc besoin d’une certaine forme de navigation dans les pages.

#0. "Je suis le pogromiste de ma mère"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Ce n'est presque pas une blague - c'est rare, mais on le trouve à l'état sauvage. Parfois, après avoir travaillé avec ORM, il peut être difficile de passer au travail « direct » avec SQL.

Mais passons à des problèmes plus courants et moins évidents.

#1. COMPENSER

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

D'où vient le chiffre 26 ? Il s'agit du nombre approximatif d'entrées pour remplir un écran. Plus précisément, 25 enregistrements affichés, plus 1, signalant qu'il y a au moins autre chose plus loin dans l'échantillon et qu'il est logique de passer à autre chose.

Bien entendu, cette valeur ne peut pas être « cousue » dans le corps de la requête, mais passée via un paramètre. Mais dans ce cas, le planificateur PostgreSQL ne pourra pas s'appuyer sur le fait qu'il devrait y avoir relativement peu d'enregistrements - et choisira facilement un plan inefficace.

Et tandis que dans l'interface de l'application, la visualisation du registre est implémentée comme une commutation entre les « pages » visuelles, personne ne remarque rien de suspect pendant longtemps. Exactement jusqu'au moment où, dans la lutte pour la commodité, UI/UX décide de refaire l'interface pour qu'elle soit « à défilement sans fin », c'est-à-dire que toutes les entrées de registre sont dessinées dans une seule liste que l'utilisateur peut faire défiler de haut en bas.

Et donc, lors du prochain test, vous êtes pris duplication de dossiers dans le registre. Pourquoi, parce que la table a un index normal (ts), sur quoi repose votre requête ?

Justement parce que tu n'en as pas tenu compte ts n'est pas une clé unique dans ce tableau. En fait, et ses valeurs ne sont pas uniques, comme n'importe quel « temps » dans des conditions réelles - par conséquent, le même enregistrement dans deux requêtes adjacentes « saute » facilement de page en page en raison d'un ordre final différent dans le cadre du tri de la même valeur clé.

En fait, il y a aussi un deuxième problème caché ici, qui est beaucoup plus difficile à remarquer : certaines entrées ne seront pas affichées du tout! Après tout, les dossiers « en double » ont pris la place de quelqu’un d’autre. Une explication détaillée avec de belles images peut être trouvée lire ici.

Extension de l'index

Un développeur rusé comprend que la clé d'index doit être rendue unique, et le moyen le plus simple est de l'étendre avec un champ évidemment unique, pour lequel PK est parfait :

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Et la requête mute :

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Passer aux « curseurs »

Quelque temps plus tard, un DBA vient vers vous et est « heureux » que vos demandes ils chargent le serveur comme un diable avec leurs règles OFFSET, et en général, il est temps de passer à navigation à partir de la dernière valeur affichée. Votre requête mute à nouveau :

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Vous avez poussé un soupir de soulagement jusqu'à ce qu'il vienne...

#3. Index de nettoyage

Parce qu'un jour, votre administrateur de base de données a lu article sur la recherche d'index inefficaces et j'ai réalisé que L'horodatage « pas le dernier » n'est pas bon. Et je suis revenu vers vous - maintenant avec la pensée que cet indice devrait encore redevenir (ts DESC).

Mais que faire du problème initial du « saut » d'enregistrements entre les pages ?.. Et tout est simple : vous devez sélectionner des blocs avec un nombre d'enregistrements non fixe !

De manière générale, qui nous interdit de lire non pas « exactement 26 », mais « pas moins de 26 » ? Par exemple, pour que dans le bloc suivant il y ait enregistrements avec des significations évidemment différentes ts - alors il n'y aura pas de problème pour "sauter" les enregistrements entre les blocs !

Voici comment y parvenir :

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Que se passe t-il ici?

  1. Nous passons 25 enregistrements « vers le bas » et obtenons la valeur « limite » ts.
  2. S'il n'y a déjà rien, remplacez la valeur NULL par -infinity.
  3. Nous soustrayons tout le segment de valeurs entre la valeur reçue ts et le paramètre $1 transmis depuis l'interface (la « dernière » valeur rendue précédente).
  4. Si un bloc est renvoyé avec moins de 26 enregistrements, c'est le dernier.

Ou la même photo :
Antipatterns PostgreSQL : navigation dans le registre

Parce que maintenant nous avons l’échantillon n’a pas de « début » spécifique, alors rien ne nous empêche « d'étendre » cette requête dans la direction opposée et de mettre en œuvre un chargement dynamique de blocs de données à partir du « point de référence » dans les deux sens - vers le bas et vers le haut.

Note:

  1. Oui, dans ce cas, nous accédons à l'index deux fois, mais tout se fait « purement par index ». Par conséquent, une sous-requête n’aboutira qu’à à une analyse d'index uniquement supplémentaire.
  2. Il est bien évident que cette technique ne peut être utilisée que lorsqu'on a des valeurs ts ne peut traverser que par hasard, et peu d'entre eux. Si votre cas typique est « un million d’enregistrements à 00:00:00.000 », vous ne devriez pas faire cela. Je veux dire, vous ne devriez pas permettre qu’un tel cas se produise. Mais si cela se produit, utilisez l'option avec un index étendu.

Source: habr.com

Ajouter un commentaire