1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yukishi24(ゆきし)

PostgreSQL学習ロードマップ

1
Posted at

はじめに

VirtualBox 上の Linux で実際に手を動かしながら、無料の日本語リソースだけで体系的に学ぶ

「PostgreSQL を勉強したいけれど、何から手をつけていいかわからない」「断片的な記事を読んでも全体像が見えない」——そんな悩みを持っている方に向けて、完全無料の日本語リソースだけで PostgreSQL を体系的に学習するためのロードマップをまとめました。

本記事の特徴は次の通りです。

  • 学習環境は VirtualBox 上に構築した Linux を前提(本番に近い環境で学べる)
  • 紹介するリソースは すべて無料・すべて日本語
  • 単なる SQL の使い方ではなく、バックアップ・ストアドプロシージャ・API連携 まで網羅
  • 各ステージに 具体的な学習目標とサンプルコード を記載

想定読者は以下のような方です。

  • データベースをちゃんと勉強したいと思っている駆け出し〜中堅エンジニア
  • 業務で MySQL は使ったことがあるが PostgreSQL は初めての方
  • API バックエンドを作りたいけれど DB 周りに自信がない方
  • OSS-DB 試験の受験を考えている方

それでは、ロードマップを見ていきましょう。

全体像:8ステージで PostgreSQL 有識者を目指す

ステージ テーマ 期間目安
1 RDB と SQL の基礎理解 1週間
2 VirtualBox + Linux 環境構築 1週間
3 PostgreSQL のインストールと基本操作 1週間
4 実践 SQL(JOIN、サブクエリ、ウィンドウ関数) 2〜3週間
5 設計・運用(インデックス、トランザクション) 2〜3週間
6 バックアップとリストア 1〜2週間
7 ストアドプロシージャ / PL/pgSQL 2〜3週間
8 プログラミング言語からの利用(API開発) 2〜3週間

トータルで約3〜4ヶ月を想定しています。週末プログラマーでも、毎週コツコツ進めれば半年以内にはひと通り終わる分量です。

ステージ 1:RDB と SQL の基礎理解

このステージのゴール

  • リレーショナルデータベース(RDB)とは何かを自分の言葉で説明できる
  • 主キー、外部キー、正規化の概念を理解している
  • SELECT、INSERT、UPDATE、DELETE の基本構文が書ける
  • PostgreSQL が他の DB(MySQL、Oracle、SQL Server)と何が違うか説明できる

学ぶべきこと

このステージは「インストール前に頭で理解しておくこと」です。いきなり手を動かすより、まず概念を整理しておくと後の学習がスムーズになります。

1. データベースの基本概念

  • テーブル、行(レコード)、列(カラム)の関係
  • 主キー(PRIMARY KEY)と外部キー(FOREIGN KEY)
  • 正規化(第1〜第3正規形)
  • ACID 特性(Atomicity / Consistency / Isolation / Durability)

2. SQL の基本構文

-- データの取得
SELECT name, age FROM users WHERE age >= 20;

-- データの追加
INSERT INTO users (name, age) VALUES ('田中', 25);

-- データの更新
UPDATE users SET age = 26 WHERE name = '田中';

-- データの削除
DELETE FROM users WHERE name = '田中';

3. PostgreSQL の特徴

  • BSD 系ライセンスで完全無料、商用利用も自由
  • 標準 SQL への準拠度が高い
  • JSONB、配列型、ユーザー定義型などリッチなデータ型
  • MVCC(多版型同時実行制御)による高い並列性能
  • 拡張機能(extension)が豊富(PostGIS、pgvector など)

参考リソース

ステージ 2:VirtualBox + Linux 環境構築

このステージのゴール

  • VirtualBox に Ubuntu(または Rocky Linux)をインストールできる
  • ホスト OS から SSH で仮想マシンに接続できる
  • スナップショット機能を使って状態を保存・復元できる

なぜ VirtualBox + Linux なのか

「Windows に直接 PostgreSQL を入れればいいのでは?」と思うかもしれません。しかし、実務の PostgreSQL は 99% Linux 上で動いています。本番環境に近い形で学習することで、実務にそのまま転用できる知識が身につきます。

加えて、VirtualBox には次のメリットがあります。

  • スナップショットで設定変更前の状態に戻せる(壊しても怖くない)
  • 環境を丸ごと削除できるので、ホスト OS を汚さない
  • 複数の Linux 環境を並行して試せる

推奨スペック

項目 推奨
ゲストOS Ubuntu 22.04 LTS / 24.04 LTS、または Rocky Linux 9
メモリ 2GB 以上
ストレージ 20GB(可変サイズ)
ネットワーク NAT + ホストオンリーアダプタの 2 枚構成

ネットワークを 2 枚構成にする理由は、NAT でインターネット接続(パッケージ取得用)を確保しつつ、ホストオンリーで SSH 接続できるようにするためです。これは実務でもよく使う構成です。

作業の流れ

  1. VirtualBox を公式サイトからダウンロード・インストール
  2. Ubuntu の ISO をダウンロード
  3. VirtualBox で新規仮想マシンを作成し、ISO をマウントして起動
  4. Ubuntu のインストールウィザードに従って進める
  5. インストール後、sudo apt update && sudo apt upgrade で最新化
  6. SSH サーバ(openssh-server)をインストールして、ホストから接続確認
  7. ここでスナップショットを取る(クリーンな状態を保存)

参考リソース

ステージ 3:PostgreSQL のインストールと基本操作

このステージのゴール

  • Linux 上に PostgreSQL をインストールし、起動・停止ができる
  • psql コマンドで接続し、データベースとテーブルを作成できる
  • postgresql.conf と pg_hba.conf の役割を理解している
  • リモートから PostgreSQL に接続できる

インストール手順(Ubuntu の場合)

# パッケージリストの更新
sudo apt update

# PostgreSQL のインストール
sudo apt install postgresql postgresql-contrib

# サービスの状態確認
sudo systemctl status postgresql

# postgres ユーザーに切り替えて psql を起動
sudo -i -u postgres
psql

よく使う psql メタコマンド

psql には SQL 以外に「メタコマンド」と呼ばれる管理用コマンドがあります。これは実務で頻繁に使うので、早めに体に染み込ませましょう。

コマンド 意味
\l データベース一覧
\c db_name データベースに接続
\dt テーブル一覧
\d table_name テーブル定義の確認
\du ユーザー(ロール)一覧
\df 関数一覧
\q psql を終了
\? メタコマンドのヘルプ

設定ファイルの理解

PostgreSQL の動作を決める 2 つの重要な設定ファイルがあります。

postgresql.conf — PostgreSQL 全体の動作設定

  • リッスンするアドレス(listen_addresses)
  • ポート番号(port、デフォルト 5432)
  • メモリ設定(shared_buffers、work_mem)
  • ログ設定

pg_hba.conf — クライアント認証設定

  • どのホストから、どのユーザーが、どのデータベースに、どの認証方式で接続できるか

リモート接続できるようにするには、両方を編集する必要があります。例えば、ホスト OS から仮想マシンの PostgreSQL に接続したい場合は次のように設定します。

# postgresql.conf
listen_addresses = '*'

# pg_hba.conf
host    all    all    192.168.56.0/24    md5

設定変更後は sudo systemctl restart postgresql で再起動を忘れずに。

参考リソース

ステージ 4:実践 SQL

このステージのゴール

  • 複雑な JOIN を使いこなせる
  • サブクエリと CTE(WITH 句)を使い分けられる
  • ウィンドウ関数で「グループ内ランキング」「累計」などが書ける
  • JSONB、配列、日付型を実務で使えるレベルになる

このステージが最も時間がかかる

ここが一番のヤマ場です。SQL は奥が深く、書けば書くほど発見があります。毎日少しずつでも手を動かすことが大切です。

JOIN の種類を体で覚える

-- INNER JOIN: 両方にあるデータだけ
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: 左のテーブルは全件、右はマッチしたもの
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 注文がないユーザーを抽出する典型パターン
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

CTE(WITH句)で読みやすいクエリを書く

サブクエリを多用するとクエリが読みづらくなります。CTE を使うと SQL がぐっと読みやすくなります。

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month, total
FROM monthly_sales
WHERE total > 100000
ORDER BY month;

ウィンドウ関数の威力

ウィンドウ関数は SQL の中で最も強力な機能の 1 つです。「グループごとに集計しつつ、元の行も残す」ことができます。

-- 商品カテゴリごとの売上ランキング
SELECT
    category,
    product_name,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
FROM products;

-- 累計売上(ランニングトータル)
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

PostgreSQL 独自のデータ型を使う

PostgreSQL の強みの 1 つがリッチなデータ型です。特に JSONB は実務で頻出します。

-- JSONB 型のカラムを定義
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO events (data) VALUES
    ('{"user": "tanaka", "action": "login", "ip": "192.168.1.1"}'),
    ('{"user": "suzuki", "action": "purchase", "amount": 5000}');

-- JSONB の中身を検索
SELECT data->>'user' AS user_name
FROM events
WHERE data->>'action' = 'login';

参考リソース

ステージ 5:設計・運用

このステージのゴール

  • 適切なテーブル設計ができる
  • インデックスの効果と副作用を理解している
  • トランザクション分離レベルを説明できる
  • EXPLAIN で実行計画を読み解ける

インデックスは諸刃の剣

インデックスは検索を高速化しますが、書き込み時には逆にコストになります。「とりあえず全カラムにインデックスを張る」のは典型的なアンチパターンです。

PostgreSQL には複数のインデックス種別があります。

種類 用途
B-tree デフォルト。等価・範囲検索に強い
Hash 等価検索専用
GIN 全文検索、JSONB、配列の検索
GiST 地理空間データ、範囲型
BRIN 大規模で順序性のあるデータ(時系列など)

EXPLAIN で実行計画を読む

クエリが遅いとき、まず EXPLAIN ANALYZE で実行計画を見ます。

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 100;

出力例:

Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=1 width=64) (actual time=0.025..0.027 rows=1 loops=1)
  Index Cond: (user_id = 100)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

「Index Scan」が出ていればインデックスが効いています。「Seq Scan」(全件走査)が出ているなら、インデックスを検討する価値があります。

トランザクションと分離レベル

PostgreSQL のデフォルト分離レベルは READ COMMITTED です。実務では用途に応じて変えることがあります。

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 何らかの処理
COMMIT;

参考リソース

ステージ 6:バックアップとリストア

このステージのゴール

  • 論理バックアップ(pg_dump)と物理バックアップ(pg_basebackup)の違いを説明できる
  • バックアップを取得し、別環境にリストアできる
  • PITR(Point In Time Recovery)の仕組みを理解している
  • バックアップ戦略を立てられる

バックアップは「取れる」だけでは半人前

エンジニアあるあるですが、バックアップを取っただけで安心してはいけません。リストアを試したことがないバックアップは、いざというときに役に立たない可能性があります。必ずリストアまでセットで練習しましょう。

論理バックアップ:pg_dump

pg_dump は最もよく使うバックアップコマンドです。データベース単位で SQL ファイルやアーカイブ形式で出力できます。

# プレーンテキスト形式(SQL文として出力)
pg_dump -U postgres -d mydb > mydb_backup.sql

# カスタム形式(圧縮されて pg_restore でリストア)
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

# ディレクトリ形式(並列ダンプが可能)
pg_dump -U postgres -d mydb -Fd -j 4 -f mydb_backup_dir

主な形式の違い:

形式 オプション 特徴
プレーン -Fp(デフォルト) SQL テキスト。psql でリストア
カスタム -Fc 圧縮済み。pg_restore でリストア
tar -Ft tar アーカイブ
ディレクトリ -Fd 並列ダンプ・並列リストアが可能

リストア

# プレーンテキストのリストア
psql -U postgres -d mydb_new < mydb_backup.sql

# カスタム形式のリストア
pg_restore -U postgres -d mydb_new mydb_backup.dump

# 並列リストア(ディレクトリ形式の場合)
pg_restore -U postgres -d mydb_new -j 4 mydb_backup_dir

全データベースを丸ごとバックアップ:pg_dumpall

ロールやテーブルスペースなどクラスタ全体を含めてバックアップしたいときは pg_dumpall を使います。

pg_dumpall -U postgres > cluster_backup.sql

PITR(Point In Time Recovery)

論理バックアップは「バックアップを取った時点」までしか戻せません。「障害発生直前まで戻したい」というニーズには PITR が必要です。

PITR は次の 2 つを組み合わせて実現します。

  1. ベースバックアップ(pg_basebackup で取得)
  2. WAL アーカイブ(更新ログを継続的に保存)

WAL(Write Ahead Log)は、PostgreSQL がコミット前に必ず書き出すログです。これをアーカイブしておけば、ベースバックアップから任意の時点まで「前進リカバリ」ができます。

# postgresql.conf の設定例
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/backup/wal/%f'

バックアップ戦略の例

規模 戦略
個人開発 毎晩 pg_dump を cron で実行
中規模業務 週次の pg_basebackup + 日次の pg_dump
大規模本番 pg_basebackup + WAL アーカイブで PITR を構成

参考リソース

ステージ 7:ストアドプロシージャ / PL/pgSQL

このステージのゴール

  • PL/pgSQL の基本構文を理解する
  • ファンクションとプロシージャの違いを説明できる
  • 引数、戻り値、変数、制御構造を使いこなせる
  • 例外処理(EXCEPTION)を書ける
  • トリガと組み合わせた使い方ができる

ストアドプロシージャとは

データベース内に保存された一連の処理のことです。アプリケーション側ではなく DB 側でロジックを実行できるので、次のようなメリットがあります。

  • パフォーマンス向上:ネットワーク往復が減る
  • 再利用性:複数のアプリから同じ処理を呼べる
  • セキュリティ:アプリには実行権限だけ与え、テーブルへの直接アクセスを禁止できる

PostgreSQL ではPL/pgSQL という専用の手続き型言語を使ってストアドプロシージャを書きます。

ファンクションとプロシージャの違い

PostgreSQL 11 から、従来の「ファンクション」に加えて「プロシージャ」が追加されました。

項目 ファンクション(FUNCTION) プロシージャ(PROCEDURE)
戻り値 必須 なし
呼び出し方 SELECT func(); CALL proc();
トランザクション制御 不可 可能(COMMIT/ROLLBACK)
用途 値の計算、結果セットの返却 一連の処理、データ更新

最初のファンクション

CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 呼び出し
SELECT add_numbers(3, 5);  -- 8

最初のプロシージャ

CREATE OR REPLACE PROCEDURE hello_world()
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Hello, World!';
END;
$$;

-- 呼び出し
CALL hello_world();

変数、制御構造、例外処理

CREATE OR REPLACE FUNCTION get_user_status(p_user_id integer)
RETURNS text AS $$
DECLARE
    v_age integer;
    v_status text;
BEGIN
    -- SELECT INTO で変数に値を入れる
    SELECT age INTO v_age FROM users WHERE id = p_user_id;

    -- IF文
    IF v_age IS NULL THEN
        RETURN 'ユーザーが見つかりません';
    ELSIF v_age < 20 THEN
        v_status := '未成年';
    ELSIF v_age < 65 THEN
        v_status := '成人';
    ELSE
        v_status := '高齢者';
    END IF;

    RETURN v_status;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'エラーが発生しました: %', SQLERRM;
        RETURN 'エラー';
END;
$$ LANGUAGE plpgsql;

LOOP と CURSOR で複数行を処理

CREATE OR REPLACE PROCEDURE update_all_prices(p_rate numeric)
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id, price FROM products LOOP
        UPDATE products
        SET price = rec.price * p_rate
        WHERE id = rec.id;
    END LOOP;
END;
$$;

CALL update_all_prices(1.1);  -- 全商品を 10% 値上げ

トリガとの組み合わせ

トリガは「テーブルへの操作をきっかけに自動的にファンクションを実行する仕組み」です。よく使われる例として「更新日時の自動記録」があります。

-- トリガ用のファンクション
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガの定義
CREATE TRIGGER trg_users_modified
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_at();

これで、users テーブルの行が更新されるたびに modified_at が自動的に現在時刻に更新されます。

参考リソース

ステージ 8:プログラミング言語からの利用(API開発)

このステージのゴール

  • アプリケーションから PostgreSQL に接続する仕組みを理解する
  • Python + FastAPI で REST API を作れる
  • ORM(SQLAlchemy)の基本を押さえる
  • マイグレーションツール(Alembic)が使える

なぜ Python + FastAPI なのか

このステージでは Python + FastAPI を採用します。理由は次の通りです。

  • 日本語の入門記事が圧倒的に多い
  • Swagger UI が自動生成されるので、作った API をすぐにブラウザで試せる
  • 型ヒントが活用されていて、初心者でも安全にコードが書ける
  • 学習用としても、本番用としても通用する

全体の構成

[ブラウザ / curl] 
       ↓ HTTP リクエスト
[FastAPI(Uvicorn)] 
       ↓ SQLAlchemy ORM
[PostgreSQL(VirtualBox上のLinux)]

必要なライブラリ

pip install fastapi uvicorn sqlalchemy psycopg2-binary
ライブラリ 役割
fastapi Web フレームワーク本体
uvicorn ASGI サーバ(FastAPI を動かす)
sqlalchemy ORM(SQL を Python オブジェクトとして扱う)
psycopg2-binary PostgreSQL の Python ドライバ

最小構成の例

database.py — DB 接続設定

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql://myuser:mypassword@192.168.56.10:5432/mydb"

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

models.py — テーブル定義(ORM モデル)

from sqlalchemy import Column, Integer, String
from database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    age = Column(Integer)

main.py — API エンドポイント

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from database import engine, get_db, Base
from models import User

Base.metadata.create_all(bind=engine)

app = FastAPI()

@app.get("/users/{user_id}")
def get_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.id == user_id).first()
    if user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return {"id": user.id, "name": user.name, "age": user.age}

@app.post("/users/")
def create_user(name: str, age: int, db: Session = Depends(get_db)):
    user = User(name=name, age=age)
    db.add(user)
    db.commit()
    db.refresh(user)
    return user

起動と動作確認

uvicorn main:app --reload --host 0.0.0.0 --port 8000

ブラウザで http://localhost:8000/docs にアクセスすると、Swagger UI が自動生成されているのがわかります。ここから直接 API を叩いて動作確認できるのが FastAPI の最大の魅力です。

マイグレーション(Alembic)

実務では、テーブル定義の変更履歴を管理する必要があります。それを担うのが Alembic です。

pip install alembic
alembic init alembic

alembic.ini の sqlalchemy.url を PostgreSQL の接続情報に書き換えて、

# モデルの変更を検出して migration ファイルを自動生成
alembic revision --autogenerate -m "create users table"

# 実際にDBに反映
alembic upgrade head

参考リソース

学習を成功させる 10 のコツ

  1. 必ず手を動かす — 読むだけでは身につかない。VirtualBox 上で実際に psql を叩く
  2. スナップショットを活用 — 設定を壊しても怖くないから、思い切って試せる
  3. 自分のテーマで DB を作る — 家計簿、読書記録、ゲーム管理など、興味のある題材を選ぶ
  4. 公式ドキュメントを引く習慣 — わからない構文に出会ったら、まず日本語公式ドキュメント
  5. EXPLAIN ANALYZE を早く使い始める — クエリの裏側を意識する習慣がつく
  6. バックアップは必ずリストアまで試す — 取れただけでは半人前
  7. ストアドプロシージャは Hello World から — いきなり複雑なものに手を出さない
  8. API は Swagger UI で動作確認 — 視覚的に確認できると理解が早い
  9. エラーメッセージをちゃんと読む — PostgreSQL のエラーメッセージは親切
  10. Qiita や Zenn にアウトプット — 教えることが最高の学習

ロードマップ完走後の発展先

  • OSS-DB Silver / Gold 試験 — PostgreSQL ベースの認定資格(教材は無料)
  • PostGIS — 地理空間データ拡張
  • 論理レプリケーション、ストリーミングレプリケーション — 高可用性構成
  • pgvector — ベクトル検索拡張、AI/RAG 用途で熱い
  • パフォーマンスチューニングの実践 — pg_stat_statements、autovacuum 調整
  • Docker / Kubernetes での PostgreSQL 運用 — クラウドネイティブ時代の運用
  • PostgreSQL のソースコードを読む — 真の有識者を目指すなら

おわりに

PostgreSQL は学べば学ぶほど奥の深いデータベースです。本ロードマップでは「有識者」を目指すために、SQL の使い方だけでなく、運用、内部実装、アプリ連携まで幅広くカバーしました。

すべてを一気にマスターする必要はありません。自分のペースで、興味のあるところから掘り下げていくのが一番です。本記事がその道しるべになれば幸いです。

質問やフィードバックがあれば、コメント欄で気軽にどうぞ。皆さんの PostgreSQL 学習がうまくいくことを願っています!

参考にした主なサイト一覧

作成日:2026年4月8日

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?