PostgreSQL

#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.

Je découvre une nouvelle limitation de Hasura par rapport à PostGraphile.

Hasura permet d'exécuter des fonctions PostgreSQL seulement si leur type de retour est une table. De plus, cette table doit être tracked par Hasura.

Return type: MUST be SETOF <table-name> OR <table-name> where <table-name> is already tracked.

Return type workaround: If the required SETOF table doesn't already exist or your function needs to return a custom type i.e. row set, you can create and track an empty table with the required schema to support the function.

-- from

D'autre part, Hasura doit avoir des permissions d'accès à cette table utilisée en retour de fonction.

Par exemple, Hasura ne supporte pas ce type de configuration :

REVOKE ALL PRIVILEGES ON TABLE foobar FROM hasurauser;

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

Cette limitation, parmi d'autres, renforce ma préférence pour PostGraphile plutôt que Hasura dans un contexte d'utilisation avec PostgreSQL — PostGraphile supporte uniquement PostgreSQL.

Je me suis lancé dans une recherche d'outil d'anonymisation de dump PostgreSQL, j'ai trouvé ceci :

• https://postgresql-anonymizer.readthedocs.io/en/latest/
• https://github.com/TantorLabs/pg_anon
• Greenmask - https://github.com/GreenmaskIO/greenmask (from)
• https://www.neosync.dev/ (from)
• https://github.com/digitalmint/datanymizer/tree/digitalmint (from)
• https://github.com/nixys/nxs-data-anonymizer (from) Je n'ai pas compris comment cet outil fonctionne en interne.

Ma première impression : Greenmask est l'outil qui me semble le plus intéressant pour mes besoins.

---

Pour Clickhouse, j'ai découvert https://clickhouse.com/docs/en/operations/utilities/clickhouse-obfuscator (from).

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';

#JaiDécouvert que wal-g ne se limite pas au support de PostgreSQL : il prend également en charge MySQL, MS SQL Server, et ajoutera bientôt le support de MongoDB et Redis.

WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

-- from

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.

Je rassemble ici quelques notes au sujet de projet Hasura.

À l'origine, Hasura était uniquement un moteur GraphQL open-source qui se branchait directement sur une base de données PostgreSQL. Le projet a commencé en 2018, bien que le site web soit plus ancien — 2015.
D'après le dépôt GitHub, les premiers développeurs d'Hasura sont Shahidh K Muhammed, Vamshi Surabhi, Aravind Shankar et Rakesh Emmadi, tous basés à Bangalore, en Inde.

En 2019, dans un cadre professionnel, j'ai choisi d'utiliser un autre moteur GraphQL : PostGraphile.

Début 2020, j'avais également identifié Hasura et Supabase comme alternatives.

J'avais choisi d'utiliser PostGraphile pour plusieurs raisons :

• Supabase était encore un jeune projet, lancé en octobre 2019.
• Hasura était codé en Haskell, un langage que je ne maîtrise pas. En revanche, PostGraphile, développé en JavaScript, m'inspirait plus confiance, car je savais que j'avais les compétences nécessaires si je devais intervenir sur son code source, par exemple, pour corriger un bug.
• D'autre part, PostGraphile n'était pas financé par des Venture capital, ce qui m'inspirait bien plus confiance sur son avenir que Supabase et Hasura.
• J'apprécie énormément la façon de travailler de Benjie. J'apprécie sa manière d'organiser ses projets, ses documentations et ses choix techniques. Je pense que notre doctrine est assez similaire.

Quatre plus tard, je constate que PostGraphile a choisi de rester concentré sur un seul objectif : être un moteur GraphQL, tandis que Supabase et Hasura, bénéficiant d'un financement par des Venture capital, ont diversifié leurs offres.

Alors que PostGraphile se limite au support de PostgreSQL, Hasura peut se connecter à Mysql, MongoDB, Clickhouse, Elasticsearch…
Et d'après la documentation, Hasura permet d'exposer, en plus d'une API GraphQL, une API REST (RESTified Endpoints).

#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é.

En 2019, j'ai rencontré un problème lors de l'exécution de pg_dumpall sur une base de données PostgreSQL hébergée sur AWS RDS. À l'époque, ce problème était "la goutte d'eau" qui m'avait empressé de migrer de RDS vers une instance PostgreSQL self hosted avec une simple image Docker dans un docker-compose.yml, mais je digresse, ce n'est pas le sujet de cette note.

Aujourd'hui, j'ai fait face à nouveau à ce problème, mais cette fois, j'ai décidé de prendre le temps pour bien comprendre le problème et d'essayer de le traiter.

Pour cela, j'ai implémenté et publié un playground nommé rds-playground.

Je peux le dire maintenant, j'ai trouvé une solution à mon problème 🙂.

Ce playground contient :

• Un exemple de déploiement d'une base de données AWS RDS avec Terraform.
• Un script qui permet d'importer avec succès la base de données AWS RDS vers une instance locale de PostgreSQL, en incluant les rôles.

Au départ, je pensais que le problème venait d'un problème de configuration des rôles du côté de AWS RDS ou alors que je n'utilisais pas le bon user. J'ai ensuite compris que c'était une fausse piste.

J'ai ensuite découvert ce billet : "Using pg_dumpall with AWS RDS Postgres".

For those interested, RDS Postgres (by design) doesn't allow you to read pg_authid, which was earlier necessary for pg_dumpall to work.

J'ai compris que pour exécuter un pg_dumpall sur une instance RDS, il est impératif d'utiliser l'option --no-role-passwords.

Autre subtilité : sur une instance RDS, le rôle SUPERUSER est attribué au rôle rlsadmin, tandis que cette option est supprimé du rôle postgres.

ALTER ROLE postgres WITH NOSUPERUSER INHERIT CREATEROLE
CREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';

Par conséquent, j'ai décidé d'utiliser le même nom d'utilisateur superuser pour l'instance locale PostgreSQL :

services:
  postgres:
    image: postgres:13.15
    environment:
      POSTGRES_USER: rdsadmin
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD: password
      ...

Pour aller plus loin, je vous invite à suivre le README.md de rds-playground.