履歴テーブルから最新の1件を取ってくる方法

　例えば次のようなテーブルがあったとする。

-- PostgreSQL
CREATE TABLE history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    data TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- MySQL
CREATE TABLE history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    data TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO history (user_id, data, created_at) VALUES
(1, 'First entry of user1',  '2024-01-01 10:00:00'),
(1, 'Second entry of user1', '2024-01-02 09:30:00'),
(2, 'First entry of user2',  '2024-01-01 11:00:00'),
(2, 'Second entry of user2', '2024-01-02 08:45:00'),
(2, 'Third entry of user2',  '2024-01-03 07:15:00'),
(3, 'First entry of user3',  '2024-01-01 12:15:00');

id	user_id	data	created_at
1	1	First entry of user1	2024-01-01 10:00:00.000000
2	1	Second entry of user1	2024-01-02 09:30:00.000000
3	2	First entry of user2	2024-01-01 11:00:00.000000
4	2	Second entry of user2	2024-01-02 08:45:00.000000
5	2	Third entry of user2	2024-01-03 07:15:00.000000
6	3	First entry of user3	2024-01-01 12:15:00.000000

　対象ユーザ1名で取り出す場合は次のqueryでよい。

SELECT *
    FROM history
WHERE user_id = 2
ORDER BY created_at DESC
LIMIT 1;

id	user_id	data	created_at
5	2	Third entry of user2	2024-01-03 07:15:00.000000

　しかし、全体で取得する場合やuserを複数取得したい場合にループで取得する必要があるため、N+1になる。 この問題に対する解決策を明記する。

Window関数

　このような場合、一般的にはWindow関数を使って取得することになる。

-- MySQLでもPostgreSQLでも動く
SELECT *
FROM (SELECT history.*,
             ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS ranking
      FROM history) latest
WHERE ranking = 1;

id	user_id	data	created_at	ranking
2	1	Second entry of user1	2024-01-02 09:30:00.000000	1
5	2	Third entry of user2	2024-01-03 07:15:00.000000	1
6	3	First entry of user3	2024-01-01 12:15:00.000000	1

　Window関数によってuserの数だけN+1でループする必要があったqueryがテーブルスキャン1回で済む。

DISTINCT ON（PostgreSQL拡張）

　PostgreSQLには DISTINCT ON という拡張構文がある。 これを使うとよりシンプルにlatestな行を取得することができる。 以下のSQLはWindow関数の例と同義のコードになる。

SELECT DISTINCT ON (user_id) *
FROM history
ORDER BY user_id, created_at DESC;

id	user_id	data	created_at
2	1	Second entry of user1	2024-01-02 09:30:00.000000
5	2	Third entry of user2	2024-01-03 07:15:00.000000
6	3	First entry of user3	2024-01-01 12:15:00.000000

　DISTINCT ONはPostgreSQLにしかない機能であるためベンダーロックインのリスクがあるものの、非常に使いやすく強力な構文なので覚えておいて損はない。 こちらもWindow関数と同様にhistoryのテーブルスキャン1回となる。

historyテーブルの肥大が問題になったとき

　historyテーブルは履歴を積み上げていくと非常に巨大になる可能性はある。

• 不要になった過去のデータを削除する
• パーティショニングを利用し、水平分割を行う
• 最新の情報を持ったlatestテーブルを作る

　データの削除ができるなら定期的に削除するほうが良い。 そしてデータを削除できるのであれば created_at でWHEREが出来るはずだし、INDEXを活用した絞り込みができるはずだ。 多くの場合はこれで解決するので最優先で検討する。

　それでも不十分な場合に残りの2つを検討すること。

パーティショニングを活用する

　MySQLにもPostgreSQLにもパーティショニングの機能がある。 実績のある機能なので調べれば情報はたくさん出てくる。 PostgreSQLの例として富士通の書いているドキュメントを紹介する。

www.fujitsu.com

　例えば月、週、日などの単位でパーテーションを作成することでテーブルスキャンの対象を減らすことでパフォーマンス向上を狙う。 ただし、アクティブではないユーザなど、古いhistoryを参照する場合はパフォーマンスの劣化になる場合がある。

　データは削除することができないが、直近のアクティブユーザのlatestなhistoryしか参照しない場合などでは効果的である。

latestテーブルを作る

　古いhistoryのlatestも必要な場合、パーテーションのスキャンが必要で結局スロークエリになることが多い。 そこで対策としてはlatestテーブル、つまり計算結果のcacheを作る。

　これは本当に 最終手段 であることを覚えておいてほしい。 cacheを使わなくてすむなら使わなくて良いが、どうしてもパフォーマンスが改善しない場合は以下のブログを読んだうえで続きを読んでほしい。

soudai.hatenablog.com

-- PostgreSQL
CREATE TABLE latest_history (
    user_id INTEGER PRIMARY KEY,
    history_id INTEGER NOT NULL,
    data TEXT,
    created_at TIMESTAMP NOT NULL
);

-- MySQL
CREATE TABLE latest_history (
    user_id INT NOT NULL,
    history_id INT NOT NULL,
    data TEXT,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (user_id)
);

　このテーブルの例はhistoryとINNER JOINすることで最新のstatusを取得することができる。 どうしてもhistoryとのJOINを避けたい場合はlatest_historyにstatusなどhistoryの情報を非正規化して持たせても良い。

UPSERTを使う

　履歴データにINSERTされたタイミングでlatest_historyテーブルを更新する。 user_idをunique keyとして、データが無ければINSERT、あればUPDATEとすることでlatestの状態を保持することができる。 この処理をUPSERTと呼ぶ。

　UPSERTはMySQLにもPostgreSQLにもあるが、それぞれ少しづつ違うので自分の環境に合わせて下記の構文を調べてほしい。

• MySQLの場合は INSERT ON ... DUPLICATE KEY UPDATE 構文 と REPLACE構文 がある
  • INSERT ON DUPLICATE KEY UPDATE 構文はINSERTに失敗したらUPDATE
  • REPLACE構文はDELETEしてINSERT
• PostgreSQLの場合は INSERT INTO ... ON CONFLICT構文 と MERGE INTO ... USING ... ON 構文 がある
  • INSERT INTO ... ON CONFLICT構文はMySQLと同様にINSERTに失敗した時にUPDATE
  • MERGE文は挿入データを挿入先テーブルと結合して、重複したときの処理と重複しなかったときの処理を指定できる
    • 重複したときをUPDATE、重複しなかったときにINSERTを指定すればUPSERTになる
  • MERGE文はPostgreSQL15から

　これを履歴テーブルにデータをINSERTする際にlatestテーブルに都度行うことで最新の一件のデータを保持するテーブルを作れる。 推奨方法としてはアプリケーション側で履歴データにINSERTする処理の際に合わせて実行するのが良い。

トリガーを使う

　どうしてもアプリケーションを直せない、MySQLとPostgreSQLのTransaction分離レベルの違いなどで*1でファントムリードの問題があって丁寧にロックをとっても互換性を担保できない。 などの非常にごく一部の特定の場合はhistoryテーブルにINSERT時のイベントトリガーでUPSERTを実行することでlatest_historyテーブルを作成することができる

　これは最終手段の最終手段なので積極的に活用することはオススメしないが、アプリケーションを変更することなくlatestを保存することが出来るメリットがある。 実際にあるユースケースとしては複数のアプリケーションからhistoryテーブルにINSERTが実行されるため、latest_historyを作ることが難しい場合などに活用される。

-- PostgreSQL
-- トリガー関数定義
CREATE OR REPLACE FUNCTION upsert_latest_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO latest_history (user_id, history_id, data, created_at)
    VALUES (NEW.user_id, NEW.id, NEW.data, NEW.created_at)
    ON CONFLICT (user_id)
    DO UPDATE SET history_id  = EXCLUDED.history_id,
                  data        = EXCLUDED.data,
                  created_at  = EXCLUDED.created_at;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガー作成
CREATE TRIGGER trg_upsert_latest_history
AFTER INSERT ON history
FOR EACH ROW
EXECUTE FUNCTION upsert_latest_history();

-- MySQL
-- トリガー関数定義
DELIMITER //

CREATE TRIGGER trg_upsert_latest_history
AFTER INSERT ON history
FOR EACH ROW
BEGIN
    INSERT INTO latest_history (user_id, history_id, data, created_at)
    VALUES (NEW.user_id, NEW.id, NEW.data, NEW.created_at)
    ON DUPLICATE KEY UPDATE 
        history_id = VALUES(history_id),
        data = VALUES(data),
        created_at = VALUES(created_at);
END;
//

DELIMITER ;

-- PostgreSQL
INSERT INTO public.history (id, user_id, data, created_at) 
VALUES
(DEFAULT, 1, 'Force entry of user1', '2024-12-10 11:50:40.000000')

historyテーブル

id	user_id	data	created_at
1	1	First entry of user1	2024-01-01 10:00:00.000000
2	1	Second entry of user1	2024-01-02 09:30:00.000000
3	2	First entry of user2	2024-01-01 11:00:00.000000
4	2	Second entry of user2	2024-01-02 08:45:00.000000
5	2	Third entry of user2	2024-01-03 07:15:00.000000
6	3	First entry of user3	2024-01-01 12:15:00.000000
8	1	Force entry of user1	2024-12-10 11:50:40.000000

latest_historyテーブル

user_id	history_id	data	created_at
1	8	Force entry of user1	2024-12-10 11:50:40.000000

まとめ

　基本的にはWindow関数を使えばよく、SQL自体はChatGPTなどにDDLを覚えさせて、質問すれば教えてくれる。 PostgreSQLユーザはDISTINCT ONを覚えておくとちょっとした時に助かることがある。

　それを理解した上で、どうしてもパフォーマンスに問題があれば、それぞれのアプローチを検討すること。

*1:isucon14など