#JaiDécouvert la fonctionnalité "Table Partitioning" de PostgreSQL.

Je connaissais la possibilité de faire du database sharding avec PostgreSQL, en utilisant la fonctionnalité create_distributed_table de Citus — je n'ai jamais mis cela en pratique — mais je ne connaissais pas fonctionnalité native PostgreSQL de Table Partitining.

En PostgreSQL, une table partitionnée est une table divisée en plusieurs sous-tables appelées partitions, qui permettent de gérer efficacement de grandes quantités de données. Cette fonctionnalité est utile pour améliorer les performances des requêtes, simplifier l'archivage, ou encore gérer la répartition des données.

Avantages des tables partitionnées

Performances améliorées :

• Les requêtes peuvent être plus rapides grâce au pruning des partitions (PostgreSQL n'interroge que les partitions pertinentes).
• Les index sont plus légers car chaque partition peut avoir ses propres index.

Maintenance simplifiée :

• Vous pouvez archiver ou supprimer des partitions entières sans impacter le reste des données.
• Les opérations comme VACUUM ou ANALYZE sont effectuées indépendamment sur chaque partition.

ChatGPT

Cette fonctionnalité a été ajoutée dans la version 10 de PostgreSQL, en 2017.

J'ai aussi découvert qu'il est possible d'utiliser des Table Partitioning avec des Foreign Data Wrapper, par exemple, pour stocker certaines partitions sur des serveurs distants. Je pense que c'est une alternative à Citus, sans doute moins performante.

Voici une stratégie pour contourner dans une certaine mesure la limitation Hasura que j'ai décrite dans la note 2024-11-22_1703.

Remplacer :

REVOKE ALL PRIVILEGES ON TABLE foobar FROM hasurauser;

CREATE FUNCTION myfunction() RETURNS SETOF foobar
LANGUAGE sql VOLATILE SECURITY DEFINER
AS $$
     SELECT * FROM foobar
$$;

par :

REVOKE ALL PRIVILEGES ON TABLE foobar FROM hasurauser;
GRANT SELECT ON TABLE foobar TO hasurauser;

ALTER TABLE foobar ENABLE ROW LEVEL SECURITY;

CREATE POLICY deny_untrusted_user ON foobar
FOR SELECT USING (current_user != 'hasurauser');

CREATE FUNCTION myfunction() RETURNS SETOF foobar
LANGUAGE sql VOLATILE SECURITY DEFINER
AS $$
     SELECT * FROM foobar
$$;

Je viens d'apprendre que pg_dumpall de PostgreSQL n'exporte pas les passwords des utilisateurs.

Les users sont biens exporter, mais les passwords doivent être reconfigurées ensuite via la commande :

ALTER USER myuser WITH PASSWORD 'newsecret';

En lisant la release note v3.0.3 de wal-g, j'ai découvert l'extension PostgreSQL nommée OrioleDB.

OrioleDB is a new storage engine for PostgreSQL, bringing a modern approach to database capacity, capabilities and performance to the world's most-loved database platform.

OrioleDB consists of an extension, building on the innovative table access method framework and other standard Postgres extension interfaces. By extending and enhancing the current table access methods, OrioleDB opens the door to a future of more powerful storage models that are optimized for cloud and modern hardware architectures.

Le projet OrioleDB a commencé en février 2022 par un développeur de Supabase : Alexander Korotkov.

Les commentaires de ce thread Hacker News semblent très enthousiastes https://news.ycombinator.com/item?id=30462695.

Dans la page "Introductions" de la documentation, je lis :

Differentiators

The key technical differentiations of OrioleDB are as follows:

No buffer mapping and lock-less page reading

In-memory pages in OrioleDB are connected with direct links to the storage pages. This eliminates the need for in-buffer mapping along with its related bottlenecks. Additionally, in OrioleDB in-memory page reading doesn't involve atomic operations. Together, these design decisions bring vertical scalability for Postgres to the whole new level.

MVCC is based on the UNDO log concept

In OrioleDB, old versions of tuples do not cause bloat in the main storage system, but eviction into the undo log comprising undo chains. Page-level undo records allow the system to easily reclaim space occupied by deleted tuples as soon as possible. Together with page-mergins, these mechanisms eliminate bloat in the majority of cases. Dedicated VACUUMing of tables is not needed as well, removing a significant and common cause of system performance deterioration and database outages.

Copy-on-write checkpoints and row-level WAL

OrioleDB utilizes copy-on-write checkpoints, which provides a structurally consistent snapshot of data every moment of time. This is friendly for modern SSDs and allows row-level WAL logging. In turn, row-level WAL logging is easy to parallelize (done), compact and suitable for active-active multimaster (planned).

J'ai lu le billet "Rethinking PostgreSQL buffer mapping for modern hardware architectures". Je pense avoir compris que l'implémentation actuelle de PostgreSQL utilise un "buffer mapping" autrefois bien adapté aux contraintes matérielles.

J'ai compris qu'OrioleDB propose une nouvelle approche, spécialement conçue pour tirer parti des SSD rapides, ce qui lui permet d’atteindre des performances nettement supérieures à celles de l’implémentation existante.

Avec la sortie de la version 17 de PostgreSQL, de nouvelles options de sauvegarde sont désormais disponibles : l'outil pg_basebackup permet de réaliser les sauvegardes incrémentales, et un nouvel utilitaire, pg_combinebackup, permet de reconstituer une sauvegarde complète à partir de sauvegardes incrémentales.

J'ai lu les articles suivants de Robert Haas, créateur de ces nouvelles fonctionnalités :

• Incremental Backup: What To Copy?
  • #JaiDécouvert le projet ptrack.
• Incremental Backups: Evergreen and Other Use Cases

J'en ai profité aussi pour lire :

• Is pg_dump a Backup Tool?
• Why pg_dump Is Amazing

J'ai trouvé tous ces articles très intéressants, j'y ai appris beaucoup de choses.

Je me demande quel impact ces fonctionnalités auront ou ont déjà sur les outils existants comme pgBackRest, barman, et wal-g.

Autres ressources :

• 25.3.3. Making an Incremental Backup

Impact sur pgBackRest ?

Voici ce que j'ai trouvé dans le projet pgBackRest.

We are aware of what's been committed to PG17.

-- from

Je comprends d'après ce commentaire que les auteurs de pgBackRest sont bien au courant des avancées de PostgreSQL 17.

Issue : WAL summarizer in pg 17 and incremental backups in pgbackrest ?.

We already support page-level (we call it block-level) incremental since v2.46 and it works for all versions of PostgreSQL supported by pgBackRest (>= 9.4), see https://pgbackrest.org/user-guide.html#backup/block.

We are planning to use the WAL summarizer to help us pick more optimal block sizes and cross-check timestamps but we are waiting for it to be a bit more stable. Also, the WAL summarizer output uses a lot of memory and is not the best fit for large databases with a lot of changes. We have some ideas on how to make that more efficient but have not had time to pursue it yet.

D'après ce commentaire, je pense avoir compris que les nouvelles fonctionnalités de backup incrémental de PostgreSQL 17 ne sont d'aucune utilité pour pgBackRest, qui implémente déjà cette fonctionnalité de manière efficace 🤔.

Impact sur barman ?

La version 3.11.0 de barman intègre des fonctionnalités liées aux nouvelles fonctionnalités de PostgreSQL 17.

Impact sur wal-g

J'ai n'ai trouvé aucune mention de pg_combinebackup, ni de pg_basebackup incremental dans le repository de wal-g.

---

J'ai l'impression qu'il est possible d'utiliser directement pg_basebackup pour effectuer des sauvegardes incrémentales de bases de données PostgreSQL. Cependant, je crains que cette idée soit un peu naïve.

Vers la fin de 2023, j'ai commencé à implémenter un POC de pgBackRest : https://github.com/stephane-klein/backlog/issues/322. J'ai pu réaliser une simulation complète de son utilisation dans ce dépôt : poc-pgbackrest. Cependant, je n'ai pas conservé un souvenir précis des raisons pour lesquelles mon expérience utilisateur n'a pas été satisfaisante, ce qui m'a dissuadé de déployer pgBackRest en production.

Après avoir constaté que barman intègre la fonctionnalité increment de pg_basebackup, j'ai envie de tester barman.

Note de comparaison de la documentation Hasura version 2 versus PostGraphile.

J'essaie d'exposer une mutation GraphQL qui exécute et retourne de résultat d'une fonction PL/pgSQL.

Postgraphile

Voici le parcours pour découvrir comment implémenter cette fonctionnalité dans PostGraphile :

• 1. J'ouvre la page documentation : https://www.graphile.org/postgraphile/introduction/
• 2. Je vois dans la navigation de droite, "Opération", ensuite "Functions".

• 3. J'ouvre la page "Functions"
• 4. Je clique sur la page "Custom Mutations"

Et sur cette page je peux lire une explication du fonctionnement et un exemple :

Hasura

Voici le parcours pour découvrir comment implémenter cette fonctionnalité dans Hasura :

• 1. J'ouvre la documentation https://hasura.io/docs/2.0/index/

• 2. Contrairement à la navigation de la documentation de PostGraphile qui affiche directement les mots clés "Function" et "Custom Mutation", j'ai eu quelques difficultés à trouver la page qui contient ce que je cherche. Cela s'explique par le fait que Hasura propose plus de fonctionnalités que PostGraphile et plus d'abstractions.
• 3. En explorant, j'ai fini par trouver la section en ouvrant les sections "GraphQL Schema" => "Postgres".

• 4. J'ouvre la page "Extend with SQL functions"

#JaiDécouvert la fonctionnalité Tablespaces de PostgreSQL.

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Pour le moment, je n'en ai aucune utilité.

#JaiDécouvert https://github.com/dhth/schemas.

Inspect PostgreSQL schemas in the terminal.

Alexandre m'a partagé le billet Postgres webhooks with pgstream.

J'y découvre pgstream et wal2json.

Pour le moment, je ne vois pas à quoi cela pourrait bien me servir, mais je garde cet outil dans un coin de ma tête.

C'est amusant de voir que j'ai découvert hier un autre projet des mêmes auteurs que pgstream : pgroll.

#JaiDécouvert pgroll (from).

PostgreSQL zero-downtime migrations made easy.

#JaiLu en partie ce thread Hacker News de 2023.

Après avoir lu partiellement la documentation, j'ai l'impression que pgroll est simple à utiliser pour des migrations qui restent simples.
J'ai lu la section Raw SQL et #JeMeDemande si pgroll reste pratique à utiliser pour des migrations complexes, par exemple, split d'une table en plusieurs tables, merge de tables…

Je ne suis pas très motivé pour apprendre un nouveau DSL, c'est-à-dire, le format de migrations de pgroll à la place des instructions DDL (Data Definition Language) SQL (create, alter…).

Pour le moment, j'ai réussi à réaliser "à la main" des migrations en douceur : mise en place de view, de triggers… qui sont par la suite supprimés.

Je pense que pgroll serait très pratique avec une fonctionnalité Skew Protection pour un projet où les déploiements en production en journée sont fréquents et qui ne souhaite pas imposer aux utilisateurs de rafraîchir leurs pages.

#iteration du Projet GH-382 - Je cherche à convertir en SQL des query de filtre basé sur un système de "tags".

J'ai enfin analysé la Merge Request qui m'a été envoyé par un ami 🤗 : https://github.com/stephane-klein/postgres-tags-model-poc/pull/9

6 mois plus tard, j'ai fini l'implémentation de la première version du "Query string javascript parser" : https://github.com/stephane-klein/postgres-tags-model-poc/commit/f0f363b78c136e8e67a38f95b5c627d874537949

---

Pour la coloration syntaxique des fichiers Peggy sous Neovim, j'utilise avec succès https://github.com/TheGrandmother/peggy-vim : https://github.com/stephane-klein/dotfiles/commit/20cba4ba646a0793f66f9b19788920a4ff1f1838

#JaiDécouvert la commande psql nommé \ir :

\ir or \include_relative filename

The \ir command is similar to \i, but resolves relative file names differently. When executing in interactive mode, the two commands behave identically. However, when invoked from a script, \ir interprets file names relative to the directory in which the script is located, rather than the current working directory.

-- from

J'ai trouvé cette commande via Fwd: psql include file using relative path.

Cela faisait des années que j'avais besoin de cette fonctionnalité et, étrangement, je ne l'ai découverte seulement aujourd'hui 🤔.

Exemple d'utilisation https://github.com/stephane-klein/sklein-pkm-engine/blob/8938d7a2c19ed8f741bd38162882e9517c739c30/sqls/init.sql#L36

#iteration du Projet 5 - "Importation d'un vault Obsidian vers Apache Age" et plus précisément la suite de 2024-06-20_2211, 2024-06-23_1057 et 2024-06-23_2222.

Pour le projet obsidian-vault-to-apache-age-poc je souhaite créer une image Docker qui intègre les extensions pg_search et Apache Age à une image PostgreSQL.

Pour réaliser cela, je vais me baser sur ce travail préliminaire https://github.com/stephane-klein/pg_search_docker.

#JaiDécidé de créer un repository GitHub nommé apache-age-docker, qui contiendra un Dockerfile pour builder une image Docker PostgreSQL 16 qui intègre la release "Release v1.5.0 for PG16" de l'extension Postgres Apage Age.

#iteration du Projet 5 - "Importation d'un vault Obsidian vers Apache Age" et plus précisément la suite de 2024-06-20_2211 et 2024-06-23_1057.

#JaiCompris en lisant ceci que pg_search se nommait apparavant pg_bm25.

#JaiDécouvert que Tantivy — lib sur laquelle est construit pg_search — et Apache Lucene utilisent l'algorithme de scoring nommé BM25.

Okapi BM25 est une méthode de pondération utilisée en recherche d'information. Elle est une application du modèle probabiliste de pertinence, proposé en 1976 par Robertson et Jones. (from)

Je suis impressionné qu'en 2024, l'algorithme qui je pense est le plus performant utilisé dans les moteurs de recherche ait été mis au point en 1976 😮.

---

#JaiDécouvert pgfaceting - Faceted query acceleration for PostgreSQL using roaring bitmaps .

---

J'ai finallement réussi à installer pg_search à l'image Docker postgres:16 : https://github.com/stephane-klein/pg_search_docker.

J'ai passé 3h pour réaliser cette image Docker, je trouve que c'est beaucoup trop 🫣.

#iteration du Projet 5 - "Importation d'un vault Obsidian vers Apache Age" et plus précisément la suite de 2024-06-20_2211, #JeMeDemande comment créer une image Docker qui intègre l'extension pg_search ou autrement nommé ParadeDB.

---

Je lis ici :

#JePense que c'est un synonyme de pg_search mais je n'en suis pas du tout certain.

En regardant la documetation de ParadeDB, je lis :

J'en conclu que ParadeDB est un projet qui regroupe plusieurs extensions PostgreSQL : pg_search, pg_lakehouse et pg_analytics.

Pour le Projet 5, je suis intéressé seulement par pg_search.

---

#JeMeDemande si pg_search dépend de pg_vector mais je pense que ce n'est pas le cas.

---

#JeMeDemande comment créer une image Docker qui intègre l'extension pg_search ou autrement nommé ParadeDB.

J'ai commencé par essayer de créer cette image Docker en me basant sur ce Dockerfile mais j'ai trouvé cela pas pratique. Je constaté que j'avais trop de chose à modifier.

Suite à cela, je pense que je vais essayer d'installer pg_search avec PGXN.

Lien vers l'extension pg_search sur PGXN : https://pgxn.org/dist/pg_bm25/

---

Sur GitHub, je n'ai trouvé aucun exemple de Dockerfile qui inclue pgxn install pg_bm25.

---

J'ai posté https://github.com/paradedb/paradedb/issues/1019#issuecomment-2184933674.

I've seen this PGXN extension https://pgxn.org/dist/pg_bm25/

But for the moment I can't install it:

root@631f852e2bfa:/# pgxn install pg_bm25
INFO: best version: pg_bm25 9.9.9
INFO: saving /tmp/tmpvhb7eti5/pg_bm25-9.9.9.zip
INFO: unpacking: /tmp/tmpvhb7eti5/pg_bm25-9.9.9.zip
INFO: building extension
ERROR: no Makefile found in the extension root

---

J'ai posté pgxn install pg_bm25 => ERROR: no Makefile found in the extension root #1287.

---

Je me suis auto répondu :

I think I may have found my mistake.

Should I not use pgxn install but should I use pgxn download :

root@28769237c982:~# pgxn download pg_bm25
INFO: best version: pg_bm25 9.9.9
INFO: saving /root/pg_bm25-9.9.9.zip

@philippemnoel Can you confirm my hypothesis?

---

J'ai l'impression que https://pgxn.org/dist/pg_bm25/ n'est buildé que pour PostgreSQL 15.

root@4c6674286839:/# unzip pg_bm25-9.9.9.zip
Archive:  pg_bm25-9.9.9.zip
   creating: pg_bm25-9.9.9/
   creating: pg_bm25-9.9.9/usr/
   creating: pg_bm25-9.9.9/usr/lib/
   creating: pg_bm25-9.9.9/usr/lib/postgresql/
   creating: pg_bm25-9.9.9/usr/lib/postgresql/15/
   creating: pg_bm25-9.9.9/usr/lib/postgresql/15/lib/
  inflating: pg_bm25-9.9.9/usr/lib/postgresql/15/lib/pg_bm25.so
   creating: pg_bm25-9.9.9/usr/share/
   creating: pg_bm25-9.9.9/usr/share/postgresql/
   creating: pg_bm25-9.9.9/usr/share/postgresql/15/
   creating: pg_bm25-9.9.9/usr/share/postgresql/15/extension/
  inflating: pg_bm25-9.9.9/usr/share/postgresql/15/extension/pg_bm25.control
  inflating: pg_bm25-9.9.9/usr/share/postgresql/15/extension/pg_bm25--9.9.9.sql
  inflating: pg_bm25-9.9.9/META.json

Je pense que je dois changer de stratégie 🤔.

Je ne pensais pas rencontrer autant de difficultés pour installer cette extension 🤷‍♂️.

---

Ce matin, j'ai passé 1h30 sur ce sujet.

---

J'ai trouvé ce Dockerfile https://github.com/kevinhu/pgsearch/blob/48c4fee0b645fddeb7825802e5d1a4a2beb9a99b/Dockerfile#L14

Je pense pouvoir installer un package Debian présent dans la page release : https://github.com/paradedb/paradedb/releases

Nouvelle #iteration du Projet 5 - "Importation d'un vault Obsidian vers Apache Age".

Dans cette version du 20 juin j'ai implémenté :

• Importation des fichiers dans des nodes de type notes dans un graph.
• Le contenu des notes dans une table public.notes
• Les aliases dans la table public.note_aliases
• Importation des tags et leurs liaisons vers les notes dans un graph.

Au stade où j'en suis, je suis encore loin d'être en capacité de juger si le moteur de graph — Age — me sera utile ou non pour réaliser des requêtes simplement 🤔.

Prochaine fonctionnalités que je souhaite implémenter dans ce projet :

• [ ] Recherche de type fuzzy search sur les Note.title, alias et Tag.name basé sur la méthode Levenshtein du module fuzzystrmatch
• [ ] Recherche plain text sur le contenu des Notes basé sur pg_search

Dans la liste des features de pg_search je lis :

• Autocomplete
• Fuzzy search

Je pense donc intégrer pg_search avant fuzzystrmatch. Peut-être que je n'aurais pas besoin d'utiliser fuzzystrmatch.

#JeLis pour la première fois https://github.com/postgrespro/pg_probackup

J'ai en même temps découvert https://github.com/postgrespro/ptrack mais je n'ai pas pris le temps de bien comprendre son rôle.

#JeMeDemande s'il existe un meilleur moteur de recherche que https://www.postgresql.org/search/?u=%2Fdocs%2F16%2F&q=on+conflict 🤔.

J'ai fait quelques recherches, pour le moment, je n'ai rien trouvé 😟.

Je suis en train de travailler sur Projet 5 - "Importation d'un vault Obsidian vers Apache Age" et je rencontre une difficulté.

J'utilise cette configuration docker-compose.yml :

services:
  postgres:
    image: apache/age:PG16_latest
    restart: unless-stopped
    ports:
      - 5432:5432
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      PGOPTIONS: "--search_path='ag_catalog,public'"
    volumes:
      - ./volumes/postgres/:/var/lib/postgresql/data/
    healthcheck:
      test: ["CMD", "sh", "-c", "pg_isready -U $$POSTGRES_USER -h $$(hostname -i)"]
      interval: 10s
      start_period: 30s

Je ne comprends pas pourquoi, j'ai l'impression que le paramètre PGOPTIONS: "--search_path=''" ne fonctionne plus.

$ ./scripts/enter-in-pg.sh
postgres=# SHOW search_path ;
   search_path
-----------------
 "$user", public
(1 ligne)

postgres=#

La valeur de search_path devrait être ag_catalog,public.

J'ai testé avec l'image Docker image: postgres:16, j'observe le même problème.

Je suis surpris parce que je pense me souvenir que cette syntaxe fonctionnait ici en septembre 2023 🤔.

#JeMeDemande comment corriger ce problème 🤔.

---

#JaiLu docker - Can't set schema_name in dockerized PostgreSQL database - Stack Overflow

---

09:07 : #ProblèmeRésolu par https://github.com/stephane-klein/obsidian-vault-to-apache-age-poc/commit/0b1cef3a725550269583ddb514fa3fff1932e89d

#iteration Projet GH-360 - Implémenter un POC de Fuzzy Search en PostgreSQL.

---

#JaiDécouvert que l'extension fuzzystrmatch implémente plusieurs algorithmes :

• Soundex => article Wikipedia : Soundex
• Daitch-Mokotoff Soundex => article Wikipedia : Daitch–Mokotoff Soundex
• Levenshtein => article Wikipedia : Levenshtein distance
• Metaphone => article Wikipedia : Metaphone
• Double Metaphone