Foreign Data (PostgreSQL)

Cela fait des années que je m'intéresse au sujet des solutions de sauvegarde en continu de bases de données PostgreSQL.

Dans cette note, le terme "sauvegarde en continu" ne signifie pas Point In Time Recovery.

Jusqu'à présent, je me suis toujours concentré sur la méthode "mainstream", qui consiste principalement à effectuer un backup binaire couplé avec une sauvegarde continue du WAL. Par exemple des solutions basées sur pg_basebackup, pgBackRest ou barman.

Une autre solution consiste à déployer une seconde instance PostgreSQL en mode streaming replication.

Une troisième solution que #JaimeraisUnJour tester : mettre en place une sauvegarde incrémentale basée sur le filesystème btrfs.
Plus précisément, la commande btrfs-send. La documentation de Dalibo mentionne cette méthode de sauvegarde.

Samedi dernier, j'ai imaginé une autre méthode qui me plait beaucoup par sa relative flexibilité et sa simplicité.

Elle consisterait à sauvegarder des tables de manière granulaire à intervalle de temps régulier vers un Object Storage à l'aide d'un Foreign Data Wrapper.
Pour cela, j'ai identifié parquet_s3_fdw, basé sur le format Apache Parquet qui permet de lire et d'écrire des données sur un bucket Object Storage.

Features

• Support SELECT of parquet file on local file system or Amazon S3.
• Support INSERT, DELETE, UPDATE (Foreign modification).
• Support MinIO access instead of Amazon S3.

source

J'ai utilisé de nombreuses fois Foreign Data Wrapper pour copier de manière granulaire des données entre deux bases de données PostgreSQL.

J'ai trouvé cette méthode très pratique, en particulier la possibilité de pouvoir utiliser un "pattern" SQL de copie du type :

INSERT INTO clients_local (id, nom, email, date_derniere_maj)
SELECT 
    d.client_id, 
    d.nom_client, 
    d.email_client,
    CURRENT_TIMESTAMP
FROM 
    distant.clients_distant d
WHERE 
    d.date_modification > (SELECT MAX(date_derniere_maj) FROM clients_local)
ON CONFLICT (id) DO UPDATE 
SET 
    nom = EXCLUDED.nom,
    email = EXCLUDED.email,
    date_derniere_maj = EXCLUDED.date_derniere_maj;

#JaimeraisUnJour réaliser un POC de cette idée basée sur parquet_s3_fdw.

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

Depuis des années, j'hésite toujours entre get et fetch lors du naming de fonctions.

Je viens de demander à ChatGPT la différence entre ces deux verbes et quand est-ce qu'il est préférable d'utiliser l'un ou l'autre.

Voici sa réponse :

Pour get :

• Signification : Indique une opération simple pour récupérer ou accéder à des données.
• Contexte d'utilisation :
  • Utilisé pour des accès rapides et directs à des données qui sont déjà disponibles localement (en mémoire, en cache, etc.).
  • Souvent employé dans des fonctions "getter" ou des méthodes utilitaires simples.

Pour fetch :

• Signification : Suggère une opération potentiellement asynchrone ou coûteuse, comme récupérer des données d'une source externe.
• Contexte d'utilisation :
  • Lorsqu'une action supplémentaire est requise pour obtenir les données, comme un appel réseau ou une requête vers une API.
  • Implique souvent un effort ou une complexité plus grande que get.

J'ai essayé d'obtenir plus de ressources concernant cette déclaration.

J'ai trouvé ceci :

• Ma recheche "[naming-conventions] fetch get" sur StackOverflow:
  • naming conventions - Method names for getting data

Example: fetch implies that some entity needs to go and get something that is remote and bring it back. Dogs fetch a stick, and retrieve is a synonym for fetch with the added semantic that you may have had possession of the thing prior as well. get is a synonym for obtain as well which implies that you have sole possession of something and no one else can acquire it simultaneously.

source

• Ma recherche "fetch get" sur English Language and Usage Stack Exchange :

  • word choice - Fetch or Get from system?

• Sur MDN Web Docs, j'ai effectué des recherches de fonctions qui commencent par get ou fetch. Je constate que le verbe fetch est utilisé lorsque la fonction va chercher des données sur le réseau.

Suite à cette étude, j'ai décidé d'utiliser fetch plutôt que get pour toutes les fonctions allant chercher des données en dehors du "processus local". Quelques exemples :

• Utilisation de fetch pour nommer une fonction javascript frontend ou backend, qui effectue des requêtes REST ou GraphQL.
• Utilisation de get pour nommer une fonction PL/pgSQL qui effectue uniquement des requêtes SQL sur la base de données locale (pas de requêtes vers des Foreign Data (PostgreSQL)). Ici "locale" signifie que l'instance qui exécute la fonction PL/pgSQL est la même que celle qui contient les tables requêtées (voir cet échange).