Posted inデータベース

pg_cron: OSのcronを使わずにデータベース内でPostgreSQLタスクをスケジュール実行する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

ついに堪忍袋の緒が切れた問題

3年前、私はSaaSプロダクトのPostgreSQLクラスタを管理していました。2つのアプリケーションサーバーにcronジョブが散在していました。1つは毎晩VACUUM ANALYZEを実行し、もう1つは90日以上経過したレコードをコールドストレージテーブルにアーカイブしていました。

3つ目はテーブルの行数がしきい値を超えたときにアラートを送信していました。ブルーグリーンデプロイメント環境に移行するまではすべて正常に動作していましたが、新しいサーバーのcronジョブの更新を誰かが忘れてしまいました。VACUUMジョブは稼働停止したサーバーで動き続け、誰も2週間気づきませんでした。そして重要なクライアントデモの直前に、テーブルの膨張がクエリパフォーマンスを壊滅させました。

私はさまざまなプロジェクトでMySQL、PostgreSQL、MongoDBを使ってきました。それぞれに本物の強みがあります。しかしPostgreSQLが拡張機能で実現できること—他のデータベースでは到底かなわないこと—が、私が何度も戻ってくる理由です。pg_cronを発見した瞬間、「cronジョブはアプリサーバーに置く」というパターン全体が、ようやく断ち切れる悪い習慣に感じられました。

根本原因：なぜOSのcronはこの用途に適していないのか

OSからVACUUMやアーカイブクエリをスケジュールすることは、インフラストラクチャ層でデータベースの関心事を管理することを意味します。このミスマッチは、時間とともに積み重なる実際の問題を生み出します：

  • 可視性の欠如：ジョブが実行されたか、失敗したか、スキップされたかについてPostgreSQL内に記録が残りません。サーバーログやcronデーモンのメール出力を探し回ることになります。
  • デプロイメントのズレ：サーバーを移行したり、水平スケールしたり、コンテナに移行したりすると、誰かが明示的に覚えていない限り、OSのcronジョブはサイレントに壊れます。
  • 認証情報の拡散：すべてのcronスクリプトには、サーバー上のシェルスクリプトや環境ファイルにデータベース認証情報を含む接続文字列が必要です。
  • トランザクション認識の欠如：OSレベルのスクリプトはタイマーで起動し、データベースの状態—現在の負荷、アクティブなロック、前回の実行が完了したかどうか—を知ることができません。

この責任をデータベース内に移すことで、4つの問題すべてを一度に解決できます。

オプションの比較

オプション1：OSレベルのcron

シンプルなシングルサーバー構成では問題なく動作します。スケール、移行、コンテナ化をすると崩れます。PostgreSQLは何かが実行されたかどうかをまったく把握できず、データベース内でジョブ履歴を追跡するクリーンな方法がありません。

オプション2：アプリケーションスケジューラー（Celery Beat、APSchedulerなど）

OSのcronよりは優れています—ジョブはコードで管理され、アプリケーションと一緒にバージョン管理されます。しかしデータベースエンジンの外からデータベースメンテナンスを実行していることに変わりなく、別途稼働し続け、デプロイされ、監視される必要があるコンポーネントを追加しています。

オプション3：pg_cron

バックグラウンドワーカーとしてデータベースプロセス内でスケジュールされたSQLジョブを実行するPostgreSQL拡張機能です。ジョブはテーブルに格納され、実行履歴はクエリ可能で、外部依存関係はゼロです。データベース固有のタスクには、これが正しいレイヤーです。

pg_cronのインストールと設定

Ubuntu/DebianでPostgreSQL 16を使用する場合：

sudo apt install postgresql-16-cron

RHEL/Rocky/AlmaLinuxの場合：

sudo dnf install pg_cron_16

postgresql.confを編集してPostgreSQLが起動時に拡張機能を読み込むように設定します：

# /etc/postgresql/16/main/postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'your_database_name'

PostgreSQLを再起動してから、対象データベース内に拡張機能を作成します：

sudo systemctl restart postgresql
CREATE EXTENSION pg_cron;

バックグラウンドワーカーが起動し、cronスキーマが利用可能になります。セットアップ完了です。

実践的なユースケース

1. 書き込みが多いテーブルへの自動VACUUM

PostgreSQLのautovacuumはほとんどのテーブルを問題なく処理します。大量削除や一括更新を伴う書き込みが多いテーブルは別の話です—autovacuumのコストベーススロットリングがクリーンアップするよりも速く、デッドタプルを生成する可能性があります。そのようなテーブルには、予測可能なオフピーク時間にスケジュールされたVACUUMの方が信頼性が高いです。

-- ordersテーブルのVACUUM ANALYZEを毎日午前2時に実行
SELECT cron.schedule(
  'vacuum-orders',
  '0 2 * * *',
  'VACUUM ANALYZE orders;'
);

cron構文は標準POSIXです：分、時、日、月、曜日。ジョブが登録されたら、SQLから直接確認と履歴チェックができます：

-- スケジュールされたすべてのジョブを一覧表示
SELECT jobid, jobname, schedule, command FROM cron.job;

-- 最近の実行履歴を確認
SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

cron.job_run_detailsこそがpg_cronを運用上で有用にするものです。ジョブは成功しましたか？どのくらい時間がかかりましたか？先週火曜日の午前2時にサイレントエラーが発生しましたか？それらすべてがSQLクエリで確認できます。ログファイルを掘り返す必要はありません。

2. スケジュールに従った古いデータのアーカイブ

eventsテーブルがあり、90日以上経過したデータはevents_archiveに移動したいとします。ロジックをPL/pgSQL関数にラップしてスケジュールします：

CREATE OR REPLACE FUNCTION archive_old_events() RETURNS void AS $$
BEGIN
  INSERT INTO events_archive
  SELECT * FROM events
  WHERE created_at < NOW() - INTERVAL '90 days';

  DELETE FROM events
  WHERE created_at < NOW() - INTERVAL '90 days';

  RAISE NOTICE '90日以上経過したイベントを % にアーカイブしました', NOW();
END;
$$ LANGUAGE plpgsql;

-- 毎週日曜日の午前3時に実行
SELECT cron.schedule(
  'archive-old-events',
  '0 3 * * 0',
  'SELECT archive_old_events();'
);

シェルスクリプトでは実現できないことがあります：INSERTが成功してもDELETEが失敗した場合、何もコミットされません。半分アーカイブされたテーブルも、追跡すべき孤立レコードもありません。関数はPostgreSQLのトランザクションシステム内で実行されるため、完全に成功するか完全にロールバックされます。

3. pg_notifyを使った内部通知の送信

PostgreSQLにはNOTIFYとLISTENを通じた組み込みのpub/subシステムがあります。条件をチェックして、リスニング中のアプリケーションプロセスにチャネル通知を送信するジョブをスケジュールします—外部メッセージキューは不要です：

CREATE OR REPLACE FUNCTION check_pending_order_alert() RETURNS void AS $$
DECLARE
  row_count bigint;
BEGIN
  SELECT COUNT(*) INTO row_count
  FROM orders
  WHERE status = 'pending';

  IF row_count > 10000 THEN
    PERFORM pg_notify(
      'alerts',
      json_build_object(
        'type', 'high_pending_orders',
        'count', row_count,
        'timestamp', NOW()
      )::text
    );
  END IF;
END;
$$ LANGUAGE plpgsql;

-- 15分ごとにチェック
SELECT cron.schedule(
  'check-pending-orders',
  '*/15 * * * *',
  'SELECT check_pending_order_alert();'
);

アプリケーションは永続接続でLISTEN alerts;をサブスクライブし、通知が届くたびに反応します。KafkaやRedisをスタックに引き込まずに、データベース駆動型のしきい値アラートが必要な場合に有用です。

日常的なジョブ管理

-- すべてのジョブをスケジュールとともに一覧表示
SELECT jobid, jobname, schedule, active FROM cron.job;

-- ジョブを削除せずに無効化
UPDATE cron.job SET active = false WHERE jobname = 'archive-old-events';

-- ジョブを完全に削除
SELECT cron.unschedule('archive-old-events');
-- またはジョブIDで指定
SELECT cron.unschedule(3);

本番環境で実際に機能するプラクティス

複数のPostgreSQLデプロイメントでpg_cronを長期間運用すると、静かに機能するジョブと静かに壊れるジョブを分けるパターンが見えてきます：

  • すべてを関数でラップし、生のSQL文字列は使わない。インラインSQLではなくSELECT my_function();をスケジュールすることで、スケジューラー外でのテスト、マイグレーションでのバージョン管理が可能になり、午前3時に何かが失敗したときのデバッグが格段に楽になります。
  • cron.job_run_detailsの失敗にアラートを設定する。失敗したジョブや異常に長時間実行されているジョブにフラグを立てる監視クエリを追加してください。pg_cronの失敗は、アプリケーションエラーと同じように扱いましょう—1週間後の手動ログチェックではなく、アラートで対応します。
  • autovacuumと戦わず、補完する。pg_cronのVACUUMジョブは、予測可能な時間に既知の一括書き込みパターンを持つテーブルに最も効果的です。それ以外の日常的なメンテナンスはautovacuumに任せましょう。
  • べき等な関数を書く。アーカイブとクリーンアップ関数は、1回実行しても2回実行しても同じ結果になるべきです。ジョブは重複したり、再起動時に再試行されることがあります。
  • cron.database_nameの扱いに注意する。pg_cronはpostgresql.confで指定されたデータベースでジョブを実行します。複数のデータベースにまたがるジョブが必要な場合は、pg_cron 1.4で導入されたcron.schedule_in_database()を使用してください。

OSのcronから切り替えた後、最も印象的だったのはシンプルなことでした：データベースのメンテナンスがついにデータベース内に収まったということです。誰かが更新を忘れるかもしれないサーバー上のシェルスクリプトの中にではなく、移行中に見落とされるデプロイメントチェックリストの中にでもなく。ジョブ履歴はSQLクエリで確認でき、ジョブはデータベースと共に移動し、何か問題が起きたときは、1時間ログを掘り返した後ではなく、数秒で気づけます。

Share:
Tags: