PostgreSQL : Guide Complet pour Débutants et Experts

21 décembre 2025 | | Chargement... PostgreSQL, SQL, Database

Introduction

PostgreSQL est une base de données relationnelle open-source réputée pour sa robustesse, ses performances et sa conformité aux standards SQL. Utilisée par des entreprises comme Apple, Instagram, Reddit et Spotify, PostgreSQL offre des fonctionnalités avancées tout en restant accessible aux débutants. Plongeons dans ce guide complet !

Pourquoi PostgreSQL ?

Avantages de PostgreSQL

  • Open Source : Gratuit, licence permissive (PostgreSQL License)
  • Conformité SQL : Respecte scrupuleusement le standard SQL
  • Extensibilité : Extensions, types personnalisés, langages procéduraux
  • Performance : Optimisations avancées, index multiples
  • Fiabilité : ACID compliant, transactions robustes
  • JSON natif : Support excellent de JSON et JSONB
  • Full-text search : Recherche textuelle intégrée
  • Types avancés : Arrays, hstore, range types, geometric types

Installation et Configuration

Installation sur Ubuntu/Debian

# Installer PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# Vérifier le statut
sudo systemctl status postgresql

# Se connecter en tant que superutilisateur postgres
sudo -u postgres psql

Installation sur macOS

# Avec Homebrew
brew install postgresql@16

# Démarrer PostgreSQL
brew services start postgresql@16

# Se connecter
psql postgres

Créer un Utilisateur et une Base de Données

-- Créer un utilisateur
CREATE USER myuser WITH PASSWORD 'securepassword';

-- Créer une base de données
CREATE DATABASE myapp OWNER myuser;

-- Donner tous les privilèges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

-- Se connecter à la nouvelle base
\c myapp

Fondamentaux SQL avec PostgreSQL

Créer des Tables

-- Table users
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table posts
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  views INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table comments
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index pour améliorer les performances
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_posts_published ON posts(published) WHERE published = true;

Opérations CRUD

-- CREATE : Insérer des données
INSERT INTO users (email, username, password_hash)
VALUES ('john@example.com', 'johndoe', 'hashed_password_123');

INSERT INTO posts (user_id, title, content, published)
VALUES (1, 'Mon premier article', 'Contenu de l''article...', true);

-- READ : Sélectionner des données
SELECT * FROM users WHERE email = 'john@example.com';

SELECT u.username, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.published = true
ORDER BY p.created_at DESC
LIMIT 10;

-- UPDATE : Modifier des données
UPDATE posts
SET views = views + 1
WHERE id = 1;

UPDATE users
SET updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- DELETE : Supprimer des données
DELETE FROM comments WHERE post_id = 5;
DELETE FROM posts WHERE id = 5;

Requêtes Avancées

Jointures Complexes

-- Récupérer les posts avec le nombre de commentaires
SELECT
  p.id,
  p.title,
  u.username AS author,
  COUNT(c.id) AS comment_count,
  p.views
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.post_id = c.id
WHERE p.published = true
GROUP BY p.id, p.title, u.username, p.views
ORDER BY p.created_at DESC;

-- Utilisateurs les plus actifs
SELECT
  u.username,
  COUNT(DISTINCT p.id) AS post_count,
  COUNT(DISTINCT c.id) AS comment_count,
  COUNT(DISTINCT p.id) + COUNT(DISTINCT c.id) AS total_activity
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY u.id, u.username
ORDER BY total_activity DESC
LIMIT 10;

Sous-requêtes et CTEs

-- CTE (Common Table Expression) pour requêtes plus lisibles
WITH popular_posts AS (
  SELECT
    id,
    title,
    user_id,
    views
  FROM posts
  WHERE published = true AND views > 100
),
active_authors AS (
  SELECT
    u.id,
    u.username,
    COUNT(pp.id) AS popular_post_count
  FROM users u
  JOIN popular_posts pp ON u.id = pp.user_id
  GROUP BY u.id, u.username
)
SELECT * FROM active_authors
WHERE popular_post_count >= 3
ORDER BY popular_post_count DESC;

-- Sous-requête pour trouver les posts au-dessus de la moyenne
SELECT title, views
FROM posts
WHERE views > (
  SELECT AVG(views) FROM posts WHERE published = true
)
ORDER BY views DESC;

Window Functions

-- Classement des posts par auteur
SELECT
  username,
  title,
  views,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY views DESC) AS rank_in_author,
  RANK() OVER (ORDER BY views DESC) AS global_rank
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE published = true;

-- Calculer la moyenne mobile des vues sur 7 jours
SELECT
  created_at::date AS date,
  COUNT(*) AS posts_created,
  AVG(COUNT(*)) OVER (
    ORDER BY created_at::date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7days
FROM posts
GROUP BY created_at::date
ORDER BY date DESC;

Types de Données Avancés

JSON et JSONB

-- Table avec colonne JSONB
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  metadata JSONB
);

-- Insérer des données JSON
INSERT INTO products (name, metadata)
VALUES (
  'Laptop',
  '{"brand": "Dell", "specs": {"cpu": "i7", "ram": 16, "storage": 512}, "tags": ["electronics", "computers"]}'
);

-- Requêtes sur JSON
SELECT * FROM products
WHERE metadata->>'brand' = 'Dell';

SELECT * FROM products
WHERE metadata->'specs'->>'ram' = '16';

SELECT * FROM products
WHERE metadata @> '{"tags": ["electronics"]}';

-- Index GIN sur JSONB pour performance
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

Arrays

-- Table avec colonne tableau
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  tags TEXT[]
);

-- Insérer avec array
INSERT INTO articles (title, tags)
VALUES ('Introduction à PostgreSQL', ARRAY['database', 'postgresql', 'sql']);

-- Requêtes sur arrays
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['database'];
SELECT * FROM articles WHERE tags && ARRAY['sql', 'nosql'];  -- Overlap

Transactions et Concurrence

Transactions ACID

-- Transaction simple
BEGIN;

INSERT INTO users (email, username, password_hash)
VALUES ('jane@example.com', 'janedoe', 'hashed_pass');

INSERT INTO posts (user_id, title, content)
VALUES (LASTVAL(), 'First Post', 'My first article!');

COMMIT;  -- ou ROLLBACK en cas d'erreur

-- Transaction avec savepoint
BEGIN;

INSERT INTO users (email, username, password_hash)
VALUES ('bob@example.com', 'bob', 'pass123');

SAVEPOINT after_user;

INSERT INTO posts (user_id, title, content)
VALUES (LASTVAL(), 'Test', 'Content');  -- Erreur possible

ROLLBACK TO SAVEPOINT after_user;  -- Annuler seulement le post

COMMIT;  -- L'utilisateur est créé, pas le post

Niveaux d'Isolation

-- Read Committed (par défaut)
BEGIN ISOLATION LEVEL READ COMMITTED;
-- Voit les changements commitées par d'autres transactions

-- Repeatable Read
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- Voit un snapshot au début de la transaction

-- Serializable (le plus strict)
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Comme si les transactions s'exécutaient séquentiellement

Optimisation et Performance

Index Efficaces

-- Index B-tree (par défaut)
CREATE INDEX idx_users_email ON users(email);

-- Index partiel (seulement certaines lignes)
CREATE INDEX idx_posts_published ON posts(created_at)
WHERE published = true;

-- Index composé
CREATE INDEX idx_posts_user_published ON posts(user_id, published);

-- Index GIN pour full-text search
CREATE INDEX idx_posts_content_fts ON posts
USING GIN (to_tsvector('english', content));

-- Recherche full-text
SELECT title, ts_rank(to_tsvector('english', content), query) AS rank
FROM posts, to_tsquery('english', 'postgresql & database') query
WHERE to_tsvector('english', content) @@ query
ORDER BY rank DESC;

EXPLAIN et ANALYZE

-- Voir le plan d'exécution
EXPLAIN SELECT * FROM posts WHERE user_id = 1;

-- Exécuter et mesurer le temps réel
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1;

-- Analyser une requête complexe
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

VACUUM et Maintenance

-- VACUUM pour récupérer l'espace
VACUUM posts;

-- VACUUM FULL pour compacter complètement (bloque la table)
VACUUM FULL posts;

-- ANALYZE pour mettre à jour les statistiques
ANALYZE posts;

-- VACUUM ANALYZE (combinaison recommandée)
VACUUM ANALYZE posts;

-- Vérifier les statistiques d'une table
SELECT schemaname, tablename, last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
WHERE tablename = 'posts';

Fonctionnalités Avancées

Fonctions Personnalisées

-- Fonction PL/pgSQL
CREATE OR REPLACE FUNCTION increment_post_views(post_id INTEGER)
RETURNS VOID AS $$
BEGIN
  UPDATE posts SET views = views + 1 WHERE id = post_id;
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT increment_post_views(5);

-- Fonction qui retourne une table
CREATE OR REPLACE FUNCTION get_user_stats(user_id INTEGER)
RETURNS TABLE(post_count BIGINT, comment_count BIGINT, total_views BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(DISTINCT p.id),
    COUNT(DISTINCT c.id),
    COALESCE(SUM(p.views), 0)
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  LEFT JOIN comments c ON u.id = c.user_id
  WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT * FROM get_user_stats(1);

Triggers

-- Trigger pour mettre à jour updated_at automatiquement
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- Trigger pour l'audit
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(50),
  operation VARCHAR(10),
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    row_to_json(OLD),
    row_to_json(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();

Sécurité

Gestion des Privilèges

-- Créer un utilisateur en lecture seule
CREATE USER readonly WITH PASSWORD 'readonly_pass';
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Créer un utilisateur avec droits limités
CREATE USER app_user WITH PASSWORD 'app_pass';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO app_user;
GRANT USAGE, SELECT ON SEQUENCE posts_id_seq TO app_user;

-- Row Level Security (RLS)
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY posts_user_policy ON posts
FOR ALL
USING (user_id = current_user_id());

Conclusion

PostgreSQL est bien plus qu'une simple base de données relationnelle. Avec ses types avancés, son support JSON, ses fonctionnalités de recherche full-text et son extensibilité, PostgreSQL s'adapte à pratiquement tous les cas d'usage. Que vous construisiez une petite application ou un système distribué à l'échelle d'Instagram, PostgreSQL a les outils pour réussir !

Astuce Pro : Utilisez des ORMs comme Prisma, TypeORM ou Sequelize pour interagir avec PostgreSQL dans vos applications, mais comprenez toujours le SQL généré en dessous. Un bon développeur maîtrise à la fois l'abstraction et les fondamentaux !

← Retour au blog

© Copyrights jordaneyeno. | Politique de Confidentialité | Politique des Cookies | Conditions d'Utilisation | Mentions Légales