PostgreSQL件数指定完全ガイド！LIMIT・OFFSET・FETCHの使い方とページネーション実装

「全件取得したらメモリ不足に…」 「ページネーションってどう実装するの？」 「上位10件だけ効率的に取得したい」

データベースから必要な分だけ取得することは、 パフォーマンスとユーザビリティの両方で超重要！

PostgreSQLには件数を制限する方法が 5種類以上あるって知ってましたか？

この記事を読めば、 ページネーションから高速サンプリングまで、 あらゆる件数指定をマスターできます！

---

基本的な件数指定：LIMIT

LIMITの基本構文

-- 最も簡単な件数指定
SELECT * FROM users 
LIMIT 10;

-- ORDER BYと組み合わせて上位N件
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 5;

-- 実践例：最新の投稿5件を取得
SELECT 
    id,
    title,
    created_at
FROM posts 
ORDER BY created_at DESC 
LIMIT 5;

LIMITとOFFSETの組み合わせ

-- 11件目から20件目を取得（ページネーション）
SELECT * FROM users 
ORDER BY id
LIMIT 10 OFFSET 10;

-- OFFSET計算式
-- ページ番号 = 1, 2, 3...
-- 1ページあたりの件数 = 10
-- OFFSET = (ページ番号 - 1) * 1ページあたりの件数

-- 3ページ目を取得する例
SELECT * FROM products 
ORDER BY id
LIMIT 10 OFFSET 20;  -- (3-1) * 10 = 20

LIMITの注意点

-- ⚠️ ORDER BYなしのLIMITは結果が不定
SELECT * FROM users LIMIT 5;  -- 毎回違う結果の可能性

-- ✅ 必ずORDER BYと組み合わせる
SELECT * FROM users 
ORDER BY id 
LIMIT 5;

-- ⚠️ LIMITに変数は直接使えない
-- SELECT * FROM users LIMIT $1;  -- プレースホルダーはOK
-- SELECT * FROM users LIMIT (SELECT 5);  -- サブクエリは使えない

---

SQL標準準拠：FETCH句

FETCH FIRSTの使い方

-- SQL:2008標準の書き方
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

-- LIMITと同じ結果
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;

-- OFFSETと組み合わせ
SELECT * FROM products
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

-- WITH TIESオプション（同率を含める）
SELECT 
    name,
    score
FROM exam_results
ORDER BY score DESC
FETCH FIRST 3 ROWS WITH TIES;
-- スコアが同じ場合、3件以上返される可能性あり

PERCENT指定

-- 上位10%を取得
SELECT * FROM sales
ORDER BY amount DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

-- 実行計画で全件カウントが必要なので注意

---

高度なページネーション実装

基本的なページネーション

-- ページネーション用のストアドプロシージャ
CREATE OR REPLACE FUNCTION get_users_page(
    page_number INT,
    page_size INT DEFAULT 10
)
RETURNS TABLE(
    id INT,
    name VARCHAR,
    email VARCHAR,
    total_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    WITH counted AS (
        SELECT COUNT(*) OVER() as total_count
        FROM users
    )
    SELECT 
        u.id,
        u.name,
        u.email,
        c.total_count
    FROM users u
    CROSS JOIN (SELECT total_count FROM counted LIMIT 1) c
    ORDER BY u.id
    LIMIT page_size 
    OFFSET (page_number - 1) * page_size;
END;
$$ LANGUAGE plpgsql;

-- 使用例：3ページ目を取得
SELECT * FROM get_users_page(3, 20);

カーソルベースページネーション（高速）

-- OFFSETを使わない高速ページネーション
-- 最後に取得したIDを基準にする

-- 初回取得
SELECT id, name, created_at
FROM users
WHERE id > 0
ORDER BY id
LIMIT 10;

-- 次のページ（最後のID: 10）
SELECT id, name, created_at
FROM users
WHERE id > 10
ORDER BY id
LIMIT 10;

-- 複合条件でのカーソル
SELECT id, name, created_at
FROM posts
WHERE (created_at, id) > ('2024-01-15 10:00:00', 1234)
ORDER BY created_at, id
LIMIT 10;

Keyset Pagination（キーセットページネーション）

-- より複雑だが高速で安定したページネーション
CREATE OR REPLACE FUNCTION keyset_pagination(
    last_id INT DEFAULT 0,
    last_created TIMESTAMP DEFAULT '1900-01-01'
)
RETURNS TABLE(
    id INT,
    title VARCHAR,
    created_at TIMESTAMP
) AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.title, p.created_at
    FROM posts p
    WHERE (p.created_at, p.id) > (last_created, last_id)
    ORDER BY p.created_at, p.id
    LIMIT 20;
END;
$$ LANGUAGE plpgsql;

-- インデックスも作成
CREATE INDEX idx_posts_created_id ON posts(created_at, id);

---

TOP-N分析パターン

各グループの上位N件

-- 各カテゴリーの売上上位3商品
WITH ranked_products AS (
    SELECT 
        category,
        product_name,
        sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY sales_amount DESC
        ) as rank
    FROM products
)
SELECT * FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank;

-- LATERAL結合を使う方法（PostgreSQL 9.3+）
SELECT 
    c.category_name,
    p.product_name,
    p.sales_amount
FROM categories c
CROSS JOIN LATERAL (
    SELECT * FROM products
    WHERE category_id = c.id
    ORDER BY sales_amount DESC
    LIMIT 3
) p;

動的な件数指定

-- 設定テーブルから件数を取得
CREATE TABLE config (
    key VARCHAR(50),
    value INT
);

INSERT INTO config VALUES ('page_size', 20);

-- 動的に件数を指定
CREATE OR REPLACE FUNCTION get_products_dynamic()
RETURNS SETOF products AS $$
DECLARE
    limit_count INT;
BEGIN
    SELECT value INTO limit_count 
    FROM config 
    WHERE key = 'page_size';
    
    RETURN QUERY
    SELECT * FROM products
    ORDER BY id
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

---

サンプリング手法

ランダムサンプリング

-- 方法1：ORDER BY RANDOM()（小規模データ向け）
SELECT * FROM users
ORDER BY RANDOM()
LIMIT 100;

-- 方法2：TABLESAMPLE（大規模データ向け、PostgreSQL 9.5+）
-- SYSTEM方式（高速だが偏りあり）
SELECT * FROM large_table
TABLESAMPLE SYSTEM (1)  -- 約1%をサンプリング
LIMIT 1000;

-- BERNOULLI方式（均等だが遅い）
SELECT * FROM large_table
TABLESAMPLE BERNOULLI (1)  -- 約1%をサンプリング
LIMIT 1000;

-- 方法3：確率的サンプリング
SELECT * FROM users
WHERE RANDOM() < 0.01  -- 約1%の確率で選択
LIMIT 1000;

層化サンプリング

-- 各グループから均等にサンプリング
WITH stratified AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY RANDOM()) as rn
    FROM products
)
SELECT * FROM stratified
WHERE rn <= 10  -- 各カテゴリーから10件
ORDER BY category, rn;

---

パフォーマンス最適化

インデックスの活用

-- ソート済みインデックスで高速化
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- カバリングインデックスでさらに高速化
CREATE INDEX idx_posts_created_covering 
ON posts(created_at DESC) 
INCLUDE (id, title);

-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

大規模データでのOFFSET問題

-- ❌ 遅い：大きなOFFSET
SELECT * FROM huge_table
ORDER BY id
LIMIT 10 OFFSET 1000000;  -- 100万件スキップ

-- ✅ 速い：WHERE句で絞り込み
SELECT * FROM huge_table
WHERE id > 1000000
ORDER BY id
LIMIT 10;

-- パフォーマンス比較
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM test_table LIMIT 10 OFFSET 100000;
-- vs
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM test_table WHERE id > 100000 LIMIT 10;

---

実践的な使用例

APIのページネーション実装

-- REST API用の関数
CREATE OR REPLACE FUNCTION api_get_items(
    p_page INT DEFAULT 1,
    p_limit INT DEFAULT 20,
    p_sort_by TEXT DEFAULT 'id',
    p_sort_order TEXT DEFAULT 'ASC'
)
RETURNS JSON AS $$
DECLARE
    v_offset INT;
    v_total BIGINT;
    v_result JSON;
BEGIN
    -- オフセット計算
    v_offset := (p_page - 1) * p_limit;
    
    -- 総件数取得
    SELECT COUNT(*) INTO v_total FROM items;
    
    -- データ取得とJSON化
    SELECT json_build_object(
        'data', json_agg(row_to_json(t)),
        'pagination', json_build_object(
            'page', p_page,
            'limit', p_limit,
            'total', v_total,
            'pages', CEIL(v_total::FLOAT / p_limit)
        )
    ) INTO v_result
    FROM (
        SELECT * FROM items
        ORDER BY 
            CASE WHEN p_sort_order = 'ASC' THEN
                CASE p_sort_by
                    WHEN 'id' THEN id::TEXT
                    WHEN 'name' THEN name
                    ELSE id::TEXT
                END
            END ASC,
            CASE WHEN p_sort_order = 'DESC' THEN
                CASE p_sort_by
                    WHEN 'id' THEN id::TEXT
                    WHEN 'name' THEN name
                    ELSE id::TEXT
                END
            END DESC
        LIMIT p_limit
        OFFSET v_offset
    ) t;
    
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT api_get_items(2, 10, 'name', 'DESC');

無限スクロール実装

-- タイムスタンプベースの無限スクロール
CREATE OR REPLACE FUNCTION get_feed(
    p_last_timestamp TIMESTAMP DEFAULT NULL,
    p_limit INT DEFAULT 20
)
RETURNS TABLE(
    id INT,
    content TEXT,
    created_at TIMESTAMP,
    has_more BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    WITH feed_items AS (
        SELECT 
            f.id,
            f.content,
            f.created_at
        FROM feed f
        WHERE p_last_timestamp IS NULL 
           OR f.created_at < p_last_timestamp
        ORDER BY f.created_at DESC
        LIMIT p_limit + 1  -- 1件多く取得
    )
    SELECT 
        fi.id,
        fi.content,
        fi.created_at,
        (COUNT(*) OVER() > p_limit) as has_more
    FROM feed_items fi
    LIMIT p_limit;  -- 表示は指定件数まで
END;
$$ LANGUAGE plpgsql;

バッチ処理での分割取得

-- 大量データを分割して処理
DO $$
DECLARE
    batch_size INT := 1000;
    offset_val INT := 0;
    record_count INT;
BEGIN
    LOOP
        -- バッチ処理
        WITH batch AS (
            SELECT * FROM large_table
            ORDER BY id
            LIMIT batch_size
            OFFSET offset_val
        )
        INSERT INTO processed_table
        SELECT * FROM batch;
        
        GET DIAGNOSTICS record_count = ROW_COUNT;
        
        EXIT WHEN record_count = 0;
        
        offset_val := offset_val + batch_size;
        
        -- 進捗表示
        RAISE NOTICE 'Processed % records', offset_val;
        
        -- コミット（必要に応じて）
        COMMIT;
    END LOOP;
END $$;

---

よくある間違いと対策

間違い1：ORDER BYなしのLIMIT

-- ❌ 結果が不定
SELECT * FROM users LIMIT 5;

-- ✅ 常に同じ結果
SELECT * FROM users ORDER BY id LIMIT 5;

間違い2：COUNT(*)とLIMITの組み合わせ

-- ❌ LIMITが効かない
SELECT COUNT(*) FROM users LIMIT 10;  -- 全件カウント

-- ✅ サブクエリで制限
SELECT COUNT(*) FROM (
    SELECT * FROM users LIMIT 10
) sub;

間違い3：大きなOFFSET

-- ❌ パフォーマンス問題
SELECT * FROM posts 
LIMIT 20 OFFSET 100000;

-- ✅ IDベースで高速化
SELECT * FROM posts 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;

---

データベース設計のベストプラクティス

ページネーション用のビュー

-- ページネーション情報を含むビュー
CREATE VIEW v_products_with_total AS
SELECT 
    *,
    COUNT(*) OVER() as total_count,
    CEIL(COUNT(*) OVER()::FLOAT / 20) as total_pages
FROM products;

-- 使用例
SELECT * FROM v_products_with_total
ORDER BY id
LIMIT 20 OFFSET 40;

ページネーションメタデータテーブル

-- メタデータ管理
CREATE TABLE pagination_cache (
    table_name VARCHAR(50),
    total_count BIGINT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- トリガーで自動更新
CREATE OR REPLACE FUNCTION update_pagination_cache()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE pagination_cache
    SET total_count = (SELECT COUNT(*) FROM products),
        last_updated = CURRENT_TIMESTAMP
    WHERE table_name = 'products';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

---

まとめ：用途別の推奨方法

シンプルな上位N件：

SELECT * FROM table ORDER BY column LIMIT 10;

ページネーション（小規模）：

SELECT * FROM table LIMIT 10 OFFSET 20;

ページネーション（大規模）：

SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10;

ランダムサンプリング：

SELECT * FROM table TABLESAMPLE SYSTEM (1);

グループごとの上位N件：

WITH RankedCTE AS (...) SELECT * WHERE rank <= N;

---

件数指定をマスターすれば、 パフォーマンスとUXが劇的に向上します！

状況に応じて最適な方法を選んで、 効率的なデータ取得を実現しましょう。

---

件数指定やページネーションで困ったことがあれば、ぜひ質問してください。最適な実装方法を一緒に考えましょう！