Skip to content

02.5-pg-catalog.md

Latest commit

 

History

History
1096 lines (900 loc) · 31.8 KB

02.5-pg-catalog.md

File metadata and controls

1096 lines (900 loc) · 31.8 KB

🔝 Retour au Sommaire

14.2.5. pg_catalog : Métadonnées système

Introduction

Le schéma pg_catalog est le "cerveau" de PostgreSQL : c'est un schéma système qui contient toutes les métadonnées sur la structure de votre base de données. Chaque objet que vous créez (table, colonne, index, fonction, contrainte, etc.) est enregistré dans des tables spéciales du pg_catalog.

Imaginez pg_catalog comme un annuaire téléphonique complet de votre base de données : il référence tout ce qui existe, comment c'est structuré, et comment les objets sont liés entre eux.

En d'autres termes : PostgreSQL stocke des informations sur lui-même dans des tables normales, que vous pouvez interroger avec du SQL standard !

Pourquoi pg_catalog est important ?

Le catalogue système vous permet de :

  • Inspecter la structure : Lister toutes les tables, colonnes, types de données
  • Vérifier les contraintes : Trouver toutes les clés primaires, étrangères, checks
  • Analyser les dépendances : Comprendre quelles vues dépendent de quelles tables
  • Automatiser : Générer des scripts de migration, documentation automatique
  • Déboguer : Comprendre pourquoi une requête ne fonctionne pas
  • Audit : Qui a créé quoi, quand, et avec quelles permissions

Concept de catalogue système

Métadonnées vs Données

  • Données : Le contenu de vos tables (clients, commandes, produits)
  • Métadonnées : Les informations sur la structure de vos données (noms de colonnes, types, contraintes)

Le pg_catalog stocke uniquement des métadonnées.

Structure hiérarchique

PostgreSQL organise les objets selon cette hiérarchie :

Instance PostgreSQL
└── Bases de données (pg_database)
    └── Schémas (pg_namespace)
        ├── Tables (pg_class)
        │   ├── Colonnes (pg_attribute)
        │   ├── Contraintes (pg_constraint)
        │   └── Index (pg_index)
        ├── Fonctions (pg_proc)
        ├── Types (pg_type)
        ├── Séquences (pg_sequence)
        └── Vues (pg_views)

Chaque niveau est documenté dans une ou plusieurs tables du pg_catalog.

OID : Object Identifier

Presque tous les objets dans PostgreSQL ont un OID (Object Identifier), un numéro unique qui l'identifie. C'est comme un numéro de série.

Exemple :

-- Obtenir l'OID d'une table
SELECT oid, relname FROM pg_class WHERE relname = 'ma_table';

Les OID sont utilisés pour faire des jointures entre les tables du catalogue.

Accéder à pg_catalog

Le schéma pg_catalog est toujours accessible, même sans le préfixer :

-- Ces deux requêtes sont identiques
SELECT * FROM pg_tables;  
SELECT * FROM pg_catalog.pg_tables;

PostgreSQL inclut automatiquement pg_catalog dans le search_path par défaut.

Lister tous les objets du catalogue :

SELECT tablename  
FROM pg_tables  
WHERE schemaname = 'pg_catalog'  
ORDER BY tablename;

Vous verrez des centaines de tables et vues système !

Les tables principales de pg_catalog

1. pg_database : Bases de données

Contient une ligne par base de données dans l'instance PostgreSQL.

Colonnes importantes :

  • datname : Nom de la base
  • datdba : OID du propriétaire (DBA)
  • encoding : Encodage (UTF8, LATIN1, etc.)
  • datcollate : Règles de tri
  • datctype : Règles de classification de caractères

Exemple - Lister toutes les bases :

SELECT
    datname AS nom_base,
    pg_encoding_to_char(encoding) AS encodage,
    datcollate AS collation,
    pg_size_pretty(pg_database_size(datname)) AS taille
FROM pg_database  
ORDER BY datname;

2. pg_namespace : Schémas

Contient tous les schémas (namespaces) de la base actuelle.

Colonnes importantes :

  • nspname : Nom du schéma
  • nspowner : OID du propriétaire

Exemple - Lister tous les schémas :

SELECT
    nspname AS nom_schema,
    pg_catalog.pg_get_userbyid(nspowner) AS proprietaire
FROM pg_namespace  
WHERE nspname NOT LIKE 'pg_%'  -- Exclure les schémas système  
  AND nspname != 'information_schema'
ORDER BY nspname;

Schémas courants :

  • public : Schéma par défaut pour les objets utilisateur
  • pg_catalog : Schéma système
  • information_schema : Vue SQL standard sur le catalogue

3. pg_class : Relations (Tables, Index, Séquences, Vues)

C'est la table la plus importante du catalogue ! Elle contient toutes les "relations" :

  • Tables normales
  • Index
  • Séquences
  • Vues
  • Vues matérialisées
  • Tables partitionnées
  • Tables externes (foreign tables)

Colonnes importantes :

  • relname : Nom de la relation
  • relnamespace : OID du schéma contenant
  • relkind : Type de relation (voir ci-dessous)
  • relowner : OID du propriétaire
  • relpages : Nombre de pages (8 Ko) utilisées
  • reltuples : Estimation du nombre de lignes
  • relhasindex : Booléen indiquant si la table a des index

Types de relations (relkind) :

  • r : Table normale (ordinary table)
  • i : Index
  • S : Séquence
  • v : Vue
  • m : Vue matérialisée
  • c : Type composite
  • t : TOAST table (stockage de grandes valeurs)
  • f : Table externe (foreign table)
  • p : Table partitionnée

Exemple - Lister toutes les tables utilisateur :

SELECT
    schemaname,
    tablename,
    tableowner
FROM pg_tables  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY schemaname, tablename;

Note : pg_tables est une vue simplifiée sur pg_class.

Exemple - Obtenir les tailles de toutes les tables :

SELECT
    schemaname || '.' || tablename AS table_complete,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS taille_totale,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS taille_table,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) -
                   pg_relation_size(schemaname || '.' || tablename)) AS taille_index
FROM pg_tables  
WHERE schemaname = 'public'  
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

4. pg_attribute : Colonnes

Contient une ligne par colonne de chaque table.

Colonnes importantes :

  • attrelid : OID de la table contenant (référence à pg_class)
  • attname : Nom de la colonne
  • atttypid : OID du type de données (référence à pg_type)
  • attnum : Position de la colonne dans la table
  • attnotnull : TRUE si NOT NULL
  • atthasdef : TRUE si DEFAULT existe

Exemple - Lister les colonnes d'une table :

SELECT
    a.attname AS nom_colonne,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS type_donnee,
    a.attnum AS position,
    a.attnotnull AS not_null,
    CASE
        WHEN a.atthasdef THEN pg_get_expr(d.adbin, d.adrelid)
        ELSE NULL
    END AS valeur_defaut
FROM pg_attribute a  
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum  
WHERE a.attrelid = 'ma_table'::regclass  
  AND a.attnum > 0  -- Exclure les colonnes système
  AND NOT a.attisdropped  -- Exclure les colonnes supprimées
ORDER BY a.attnum;

Note : Les colonnes système (attnum ≤ 0) comme ctid, xmin, xmax sont utilisées en interne par PostgreSQL.

5. pg_type : Types de données

Contient tous les types de données : built-in (INTEGER, TEXT) et personnalisés (ENUM, COMPOSITE).

Colonnes importantes :

  • typname : Nom du type
  • typnamespace : OID du schéma
  • typtype : Catégorie de type (b=base, c=composite, e=enum, etc.)
  • typlen : Longueur du type (-1 pour variable)

Exemple - Lister les types personnalisés :

SELECT
    n.nspname AS schema,
    t.typname AS nom_type,
    CASE t.typtype
        WHEN 'b' THEN 'Type de base'
        WHEN 'c' THEN 'Type composite'
        WHEN 'd' THEN 'Domaine'
        WHEN 'e' THEN 'Enum'
        WHEN 'p' THEN 'Pseudo-type'
        WHEN 'r' THEN 'Range'
        ELSE 'Autre'
    END AS categorie
FROM pg_type t  
JOIN pg_namespace n ON t.typnamespace = n.oid  
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY n.nspname, t.typname;

6. pg_constraint : Contraintes

Contient toutes les contraintes d'intégrité : PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK.

Colonnes importantes :

  • conname : Nom de la contrainte
  • conrelid : OID de la table (référence à pg_class)
  • contype : Type de contrainte (voir ci-dessous)
  • conkey : Array des numéros de colonnes concernées

Types de contraintes (contype) :

  • p : PRIMARY KEY
  • f : FOREIGN KEY
  • u : UNIQUE
  • c : CHECK
  • x : EXCLUSION
  • t : Trigger constraint (rare)

Exemple - Lister toutes les clés primaires :

SELECT
    n.nspname AS schema,
    c.relname AS table_name,
    con.conname AS constraint_name,
    pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con  
JOIN pg_class c ON con.conrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
WHERE con.contype = 'p'  
  AND n.nspname = 'public'
ORDER BY c.relname;

Exemple - Lister toutes les clés étrangères :

SELECT
    n.nspname AS schema,
    c.relname AS table_source,
    con.conname AS fk_name,
    c2.relname AS table_cible,
    pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con  
JOIN pg_class c ON con.conrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
JOIN pg_class c2 ON con.confrelid = c2.oid  
WHERE con.contype = 'f'  
  AND n.nspname = 'public'
ORDER BY c.relname;

7. pg_index : Index

Contient les métadonnées sur les index.

Colonnes importantes :

  • indexrelid : OID de l'index (référence à pg_class)
  • indrelid : OID de la table indexée
  • indisunique : TRUE si UNIQUE
  • indisprimary : TRUE si PRIMARY KEY
  • indkey : Array des numéros de colonnes indexées

Exemple - Lister tous les index d'une table :

SELECT
    i.relname AS index_name,
    am.amname AS index_type,
    idx.indisunique AS is_unique,
    idx.indisprimary AS is_primary,
    pg_get_indexdef(idx.indexrelid) AS definition
FROM pg_index idx  
JOIN pg_class i ON idx.indexrelid = i.oid  
JOIN pg_class t ON idx.indrelid = t.oid  
JOIN pg_am am ON i.relam = am.oid  
WHERE t.relname = 'ma_table'  
ORDER BY i.relname;

8. pg_proc : Fonctions et procédures

Contient toutes les fonctions, procédures stockées, et fonctions d'agrégation.

Colonnes importantes :

  • proname : Nom de la fonction
  • pronamespace : OID du schéma
  • proargnames : Noms des arguments
  • proargtypes : Types des arguments
  • prorettype : Type de retour
  • prosrc : Code source de la fonction

Exemple - Lister les fonctions utilisateur :

SELECT
    n.nspname AS schema,
    p.proname AS fonction,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS type_retour
FROM pg_proc p  
JOIN pg_namespace n ON p.pronamespace = n.oid  
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY n.nspname, p.proname;

9. pg_trigger : Triggers

Contient tous les triggers définis sur les tables.

Colonnes importantes :

  • tgname : Nom du trigger
  • tgrelid : OID de la table (référence à pg_class)
  • tgfoid : OID de la fonction trigger
  • tgtype : Type de trigger (BEFORE/AFTER, INSERT/UPDATE/DELETE)
  • tgenabled : État (enabled, disabled, replica, always)

Exemple - Lister tous les triggers :

SELECT
    n.nspname AS schema,
    c.relname AS table_name,
    t.tgname AS trigger_name,
    p.proname AS trigger_function,
    CASE
        WHEN t.tgtype & 2 = 2 THEN 'BEFORE'
        WHEN t.tgtype & 64 = 64 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END AS timing,
    array_to_string(
        ARRAY[
            CASE WHEN t.tgtype & 4 = 4 THEN 'INSERT' END,
            CASE WHEN t.tgtype & 8 = 8 THEN 'DELETE' END,
            CASE WHEN t.tgtype & 16 = 16 THEN 'UPDATE' END
        ], ' OR '
    ) AS events
FROM pg_trigger t  
JOIN pg_class c ON t.tgrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
JOIN pg_proc p ON t.tgfoid = p.oid  
WHERE NOT t.tgisinternal  -- Exclure triggers internes  
  AND n.nspname = 'public'
ORDER BY c.relname, t.tgname;

10. pg_views : Vues

Vue simplifiée sur les vues SQL définies dans la base.

Colonnes importantes :

  • schemaname : Nom du schéma
  • viewname : Nom de la vue
  • viewowner : Propriétaire
  • definition : Définition SQL de la vue

Exemple - Lister toutes les vues :

SELECT
    schemaname,
    viewname,
    viewowner,
    definition
FROM pg_views  
WHERE schemaname = 'public'  
ORDER BY viewname;

11. pg_depend : Dépendances entre objets

Cette table cruciale enregistre toutes les dépendances entre objets (vue → table, FK → table, etc.).

Colonnes importantes :

  • classid, objid : Identifient l'objet dépendant
  • refclassid, refobjid : Identifient l'objet référencé
  • deptype : Type de dépendance (n=normal, a=auto, i=internal)

Exemple - Trouver toutes les vues qui dépendent d'une table :

SELECT DISTINCT
    v.schemaname,
    v.viewname
FROM pg_views v  
WHERE v.definition LIKE '%ma_table%'  
ORDER BY v.viewname;

Exemple plus précis avec pg_depend :

SELECT
    dependent_ns.nspname AS dependent_schema,
    dependent_view.relname AS dependent_view,
    source_table.relname AS source_table
FROM pg_depend  
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid  
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid  
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid  
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace  
WHERE source_table.relname = 'ma_table'  
  AND dependent_view.relkind = 'v'
ORDER BY dependent_view.relname;

12. pg_stats : Statistiques de colonnes

Vue qui expose les statistiques collectées par ANALYZE, utilisées par le planificateur.

Colonnes importantes :

  • schemaname, tablename, attname : Identifient la colonne
  • null_frac : Fraction de valeurs NULL
  • n_distinct : Nombre de valeurs distinctes
  • most_common_vals : Valeurs les plus fréquentes
  • most_common_freqs : Fréquences de ces valeurs
  • histogram_bounds : Distribution des valeurs

Exemple - Analyser la distribution d'une colonne :

SELECT
    schemaname,
    tablename,
    attname AS colonne,
    null_frac AS pct_null,
    n_distinct AS valeurs_distinctes,
    most_common_vals AS valeurs_frequentes,
    most_common_freqs AS frequences
FROM pg_stats  
WHERE schemaname = 'public'  
  AND tablename = 'ma_table'
ORDER BY attname;

Utilité : Comprendre pourquoi le planificateur choisit un plan d'exécution particulier.

Vues simplifiées vs Tables brutes

PostgreSQL fournit des vues "user-friendly" qui simplifient l'accès au catalogue :

Vue simplifiée Table(s) brute(s) Usage
pg_tables pg_class + pg_namespace Lister les tables
pg_indexes pg_index + pg_class Lister les index
pg_views pg_class + pg_rewrite Lister les vues
pg_stats pg_statistic Statistiques lisibles
pg_roles pg_authid Rôles et utilisateurs

Préférez les vues simplifiées quand elles existent, elles sont plus faciles à lire.

Requêtes pratiques courantes

1. Lister toutes les tables avec leur taille

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS taille_totale,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS taille_donnees,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS taille_index
FROM pg_tables  
WHERE schemaname = 'public'  
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

2. Obtenir la structure complète d'une table

SELECT
    a.attnum AS position,
    a.attname AS colonne,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
    CASE
        WHEN a.attnotnull THEN 'NOT NULL'
        ELSE 'NULL'
    END AS nullable,
    CASE
        WHEN a.atthasdef THEN pg_get_expr(d.adbin, d.adrelid)
        ELSE NULL
    END AS defaut,
    col_description(a.attrelid, a.attnum) AS description
FROM pg_attribute a  
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum  
WHERE a.attrelid = 'ma_table'::regclass  
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

3. Trouver toutes les tables sans clé primaire

SELECT
    n.nspname AS schema,
    c.relname AS table_name
FROM pg_class c  
JOIN pg_namespace n ON c.relnamespace = n.oid  
WHERE c.relkind = 'r'  -- Tables normales  
  AND n.nspname = 'public'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_constraint con
    WHERE con.conrelid = c.oid
      AND con.contype = 'p'
  )
ORDER BY c.relname;

Pourquoi c'est important ? Les tables sans PK peuvent poser problème pour la réplication logique.

4. Lister toutes les clés étrangères pointant vers une table

SELECT
    n.nspname AS schema_source,
    c.relname AS table_source,
    con.conname AS fk_name,
    pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con  
JOIN pg_class c ON con.conrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
WHERE con.contype = 'f'  
  AND con.confrelid = 'ma_table'::regclass
ORDER BY c.relname;

Cas d'usage : Avant de supprimer une table, vérifier quelles autres tables la référencent.

5. Trouver les index inutilisés

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS nombre_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille
FROM pg_stat_user_indexes  
WHERE idx_scan = 0  
  AND indexrelid NOT IN (
    SELECT indexrelid
    FROM pg_index
    WHERE indisprimary OR indisunique
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Action : Envisager de supprimer les index jamais utilisés pour économiser de l'espace et accélérer les écritures.

6. Lister les colonnes d'un type spécifique

-- Trouver toutes les colonnes de type TEXT
SELECT
    n.nspname AS schema,
    c.relname AS table,
    a.attname AS colonne
FROM pg_attribute a  
JOIN pg_class c ON a.attrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
JOIN pg_type t ON a.atttypid = t.oid  
WHERE t.typname = 'text'  
  AND n.nspname = 'public'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

7. Identifier les tables les plus fragmentées

SELECT
    schemaname,
    tablename,
    n_live_tup AS lignes_vivantes,
    n_dead_tup AS lignes_mortes,
    round(100.0 * n_dead_tup / GREATEST(n_live_tup, 1), 2) AS pct_fragmentation
FROM pg_stat_user_tables  
WHERE n_dead_tup > 1000  
ORDER BY pct_fragmentation DESC  
LIMIT 20;

8. Générer le DDL d'une table

PostgreSQL ne stocke pas le DDL original, mais on peut le reconstruire :

-- Obtenir la définition d'un index
SELECT pg_get_indexdef(oid)  
FROM pg_class  
WHERE relname = 'mon_index';  

-- Obtenir la définition d'une contrainte
SELECT pg_get_constraintdef(oid)  
FROM pg_constraint  
WHERE conname = 'ma_contrainte';  

-- Obtenir la définition d'une vue
SELECT definition  
FROM pg_views  
WHERE viewname = 'ma_vue';

9. Trouver les tables avec le plus de colonnes

SELECT
    n.nspname AS schema,
    c.relname AS table_name,
    count(*) AS nombre_colonnes
FROM pg_attribute a  
JOIN pg_class c ON a.attrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
WHERE c.relkind = 'r'  
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND n.nspname = 'public'
GROUP BY n.nspname, c.relname  
ORDER BY count(*) DESC  
LIMIT 20;

Note : Plus de 50 colonnes peut indiquer un problème de modélisation.

10. Audit : Qui a créé quoi

Les objets dans le catalogue n'ont pas de timestamp de création, mais on peut voir le propriétaire :

SELECT
    'TABLE' AS type_objet,
    schemaname || '.' || tablename AS nom_objet,
    tableowner AS proprietaire
FROM pg_tables  
WHERE schemaname = 'public'  
UNION ALL  
SELECT  
    'VIEW',
    schemaname || '.' || viewname,
    viewowner
FROM pg_views  
WHERE schemaname = 'public'  
UNION ALL  
SELECT  
    'FUNCTION',
    n.nspname || '.' || p.proname,
    pg_get_userbyid(p.proowner)
FROM pg_proc p  
JOIN pg_namespace n ON p.pronamespace = n.oid  
WHERE n.nspname = 'public'  
ORDER BY type_objet, nom_objet;

Cas d'usage en production

Scénario 1 : "Documentation automatique de la base"

-- Script pour documenter toutes les tables
SELECT
    t.tablename,
    obj_description((t.schemaname || '.' || t.tablename)::regclass) AS table_description,
    (
        SELECT json_agg(
            json_build_object(
                'colonne', a.attname,
                'type', pg_catalog.format_type(a.atttypid, a.atttypmod),
                'nullable', NOT a.attnotnull,
                'description', col_description(a.attrelid, a.attnum)
            )
        )
        FROM pg_attribute a
        WHERE a.attrelid = (t.schemaname || '.' || t.tablename)::regclass
          AND a.attnum > 0
          AND NOT a.attisdropped
        ORDER BY a.attnum
    ) AS colonnes
FROM pg_tables t  
WHERE t.schemaname = 'public'  
ORDER BY t.tablename;

Scénario 2 : "Migration vers une nouvelle version : vérification de compatibilité"

-- Trouver les fonctions utilisant plpgsql
SELECT
    n.nspname || '.' || p.proname AS fonction,
    l.lanname AS langage
FROM pg_proc p  
JOIN pg_namespace n ON p.pronamespace = n.oid  
JOIN pg_language l ON p.prolang = l.oid  
WHERE n.nspname = 'public'  
  AND l.lanname = 'plpgsql'
ORDER BY fonction;

-- Vérifier les types de données obsolètes
SELECT DISTINCT
    n.nspname AS schema,
    c.relname AS table,
    a.attname AS colonne,
    t.typname AS type
FROM pg_attribute a  
JOIN pg_class c ON a.attrelid = c.oid  
JOIN pg_namespace n ON c.relnamespace = n.oid  
JOIN pg_type t ON a.atttypid = t.oid  
WHERE n.nspname = 'public'  
  AND t.typname IN ('abstime', 'reltime')  -- Types obsolètes
  AND a.attnum > 0
  AND NOT a.attisdropped;

Scénario 3 : "Génération de scripts de sauvegarde sélectifs"

-- Lister les tables de plus de 1 GB pour sauvegarde séparée
SELECT
    schemaname || '.' || tablename AS table_complete,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS taille,
    format(
        'pg_dump -t %I.%I -f %s.sql',
        schemaname,
        tablename,
        tablename
    ) AS commande_dump
FROM pg_tables  
WHERE schemaname = 'public'  
  AND pg_total_relation_size(schemaname || '.' || tablename) > 1073741824
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

Scénario 4 : "Analyse des dépendances avant suppression"

-- Avant de supprimer une table, voir ce qui dépend d'elle
WITH RECURSIVE deps AS (
    -- Objet initial
    SELECT
        c.oid,
        c.relname,
        0 AS depth
    FROM pg_class c
    WHERE c.relname = 'ma_table'

    UNION ALL

    -- Objets dépendants
    SELECT
        c.oid,
        c.relname,
        d.depth + 1
    FROM deps d
    JOIN pg_depend dep ON d.oid = dep.refobjid
    JOIN pg_class c ON dep.objid = c.oid
    WHERE dep.deptype = 'n'  -- Dépendance normale
      AND d.depth < 5  -- Limiter la profondeur
)
SELECT DISTINCT
    depth AS niveau,
    relname AS objet_dependant
FROM deps  
WHERE depth > 0  
ORDER BY depth, relname;

Scénario 5 : "Audit de sécurité : permissions sur les tables"

SELECT
    n.nspname AS schema,
    c.relname AS table_name,
    c.relacl AS permissions_brutes,
    pg_catalog.array_to_string(c.relacl, E'\n') AS permissions
FROM pg_class c  
JOIN pg_namespace n ON c.relnamespace = n.oid  
WHERE c.relkind = 'r'  
  AND n.nspname = 'public'
ORDER BY c.relname;

Note : Le format de relacl est cryptique. Utilisez \dp dans psql pour une vue plus lisible.

Information Schema vs pg_catalog

PostgreSQL offre deux façons d'accéder aux métadonnées :

1. pg_catalog (natif PostgreSQL)

  • Avantages :
    • Complet : Accès à TOUT
    • Spécifique PostgreSQL : Toutes les features avancées
    • Performance : Accès direct aux tables système
  • Inconvénients :
    • Syntaxe complexe (OIDs, jointures multiples)
    • Non standard SQL

2. information_schema (standard SQL)

  • Avantages :
    • Standard SQL : Portable entre SGBD
    • Syntaxe simple
    • Lisible pour les débutants
  • Inconvénients :
    • Incomplet : Ne couvre pas toutes les features PostgreSQL
    • Parfois moins performant (vues complexes)

Recommandation :

  • Utilisez information_schema pour les besoins simples et portables
  • Utilisez pg_catalog pour les besoins avancés spécifiques PostgreSQL

Exemple - information_schema.columns :

SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns  
WHERE table_schema = 'public'  
ORDER BY table_name, ordinal_position;

Fonctions utilitaires du catalogue

PostgreSQL fournit des fonctions pour faciliter l'accès au catalogue :

Fonctions de taille

-- Taille d'une table (sans index)
SELECT pg_relation_size('ma_table');  
SELECT pg_size_pretty(pg_relation_size('ma_table'));  

-- Taille totale (table + index + TOAST)
SELECT pg_total_relation_size('ma_table');

-- Taille de tous les index d'une table
SELECT pg_indexes_size('ma_table');

-- Taille d'une base de données
SELECT pg_database_size('ma_base');

Fonctions de conversion

-- OID → Nom de table
SELECT oid::regclass FROM pg_class WHERE relname = 'ma_table';  
SELECT 'ma_table'::regclass::oid;  -- Nom → OID  

-- OID → Nom d'utilisateur
SELECT pg_get_userbyid(10);

-- Encodage numérique → Nom
SELECT pg_encoding_to_char(6);  -- 6 = UTF8

Fonctions de définition

-- Obtenir la définition SQL complète
SELECT pg_get_viewdef('ma_vue');  
SELECT pg_get_functiondef('ma_fonction'::regproc);  
SELECT pg_get_indexdef('mon_index'::regclass);  
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'ma_contrainte';

Fonctions de description

-- Commentaires sur objets
SELECT obj_description('ma_table'::regclass);  
SELECT col_description('ma_table'::regclass, 1);  -- Colonne numéro 1  

-- Ajouter des commentaires
COMMENT ON TABLE ma_table IS 'Table des clients';  
COMMENT ON COLUMN ma_table.email IS 'Adresse email unique';

Bonnes pratiques

1. Utilisez les vues simplifiées quand possible

-- ✅ Bon
SELECT * FROM pg_tables WHERE schemaname = 'public';

-- ❌ Complexe sans raison
SELECT c.relname  
FROM pg_class c  
JOIN pg_namespace n ON c.relnamespace = n.oid  
WHERE n.nspname = 'public' AND c.relkind = 'r';

2. Documentez vos objets avec COMMENT

COMMENT ON TABLE produits IS 'Catalogue des produits disponibles à la vente';  
COMMENT ON COLUMN produits.prix IS 'Prix unitaire HT en euros';

Ces commentaires sont visibles via obj_description() et col_description().

3. Utilisez ::regclass pour la conversion OID

-- ✅ Lisible
SELECT * FROM pg_locks WHERE relation = 'ma_table'::regclass;

-- ❌ Moins lisible
SELECT * FROM pg_locks WHERE relation = (SELECT oid FROM pg_class WHERE relname = 'ma_table');

4. Ne modifiez JAMAIS directement les tables du catalogue

🚨 DANGER : Les tables de pg_catalog ne doivent JAMAIS être modifiées avec INSERT/UPDATE/DELETE.

-- ❌ JAMAIS FAIRE ÇA
UPDATE pg_class SET relname = 'nouveau_nom' WHERE relname = 'ancien_nom';

✅ Utilisez toujours les commandes DDL :

ALTER TABLE ancien_nom RENAME TO nouveau_nom;

5. Attention aux performances sur grandes bases

Sur des bases avec des milliers de tables, certaines requêtes sur le catalogue peuvent être lentes. Ajoutez des filtres :

-- ✅ Filtrer par schéma
SELECT * FROM pg_tables WHERE schemaname = 'public';

-- ❌ Lire tout le catalogue
SELECT * FROM pg_tables;

6. Utilisez les statistiques pour l'optimisation

-- Vérifier si les statistiques sont à jour
SELECT
    schemaname,
    tablename,
    last_analyze,
    last_autoanalyze,
    n_mod_since_analyze
FROM pg_stat_user_tables  
WHERE n_mod_since_analyze > n_live_tup * 0.1  -- > 10% modifié  
ORDER BY n_mod_since_analyze DESC;

Scripts utiles pour l'administration

Script de health check complet

-- Vue de santé globale de la base
SELECT
    'Tables sans PK' AS check_type,
    count(*) AS nombre
FROM pg_tables t  
WHERE schemaname = 'public'  
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint c
    JOIN pg_class cl ON c.conrelid = cl.oid
    WHERE cl.relname = t.tablename AND c.contype = 'p'
  )
UNION ALL  
SELECT  
    'Index inutilisés',
    count(*)
FROM pg_stat_user_indexes  
WHERE idx_scan = 0 AND NOT indisprimary AND NOT indisunique  
UNION ALL  
SELECT  
    'Tables fragmentées (>20%)',
    count(*)
FROM pg_stat_user_tables  
WHERE n_dead_tup::float / GREATEST(n_live_tup, 1) > 0.2  
UNION ALL  
SELECT  
    'Statistiques obsolètes (>10%)',
    count(*)
FROM pg_stat_user_tables  
WHERE n_mod_since_analyze::float / GREATEST(n_live_tup, 1) > 0.1;

Limitations et considérations

1. Le catalogue est spécifique à une base

Chaque base de données a son propre catalogue. Pour voir les objets d'une autre base, connectez-vous à cette base.

2. Certaines informations nécessitent des droits

Les utilisateurs normaux ne voient que leurs propres objets. Les superutilisateurs voient tout.

3. Le catalogue évolue entre versions PostgreSQL

La structure du catalogue change légèrement à chaque version majeure. Vérifiez la documentation de votre version.

4. Performances sur très grandes bases

Sur des bases avec des dizaines de milliers d'objets, parcourir le catalogue peut être lent. Utilisez des filtres et des index.

Résumé des tables clés

Table Contenu Usage principal
pg_database Bases de données Lister les bases, tailles
pg_namespace Schémas Organiser les objets
pg_class Tables, index, séquences, vues Lister tous les objets relationnels
pg_attribute Colonnes Structure des tables
pg_type Types de données Types built-in et personnalisés
pg_constraint Contraintes (PK, FK, CHECK) Intégrité référentielle
pg_index Index Optimisation des requêtes
pg_proc Fonctions/procédures Logique métier
pg_trigger Triggers Automatisation
pg_depend Dépendances entre objets Analyse d'impact
pg_stats Statistiques de colonnes Optimisation du planificateur

Conclusion

Le pg_catalog est le cœur de PostgreSQL : c'est la source de vérité pour toute la structure de votre base de données. Maîtriser le catalogue vous permet de :

  • ✅ Comprendre la structure : Quelles tables, colonnes, contraintes existent
  • ✅ Automatiser : Générer des scripts, documentation, migrations
  • ✅ Déboguer : Comprendre pourquoi une requête échoue ou est lente
  • ✅ Optimiser : Identifier les index manquants, tables fragmentées
  • ✅ Auditer : Qui possède quoi, quelles permissions sont accordées

Points clés à retenir :

  • Les vues simplifiées (pg_tables, pg_indexes) sont plus faciles
  • information_schema est standard mais moins complet
  • Utilisez les fonctions utilitaires (pg_size_pretty, pg_get_indexdef)
  • Ne modifiez JAMAIS directement le catalogue
  • Documentez vos objets avec COMMENT

Le catalogue système est une mine d'or d'informations. Plus vous le maîtriserez, plus vous serez efficace dans l'administration et l'optimisation de PostgreSQL.

Prochaines étapes : Explorez information_schema pour une approche plus standard, et apprenez à utiliser les fonctions système pour automatiser vos tâches d'administration.

⏭️ Nouveauté PG 18 : Statistiques de VACUUM et ANALYZE dans pg_stat_all_tables