テーブルが多すぎると体に悪い
Too many tables are bad for you (cybertec-postgresql.com)
要約
データベースにおいて、テーブルの数が多すぎるとパフォーマンスや管理上の問題を引き起こす可能性があります。テーブルの増加は、クエリの複雑化、インデックス管理の負担増、スキーマの可読性低下などを招き、結果としてシステムの応答性や保守性を損なう恐れがあります。そのため、適切な設計と定期的な見直しが重要となります。
全文翻訳
テーブルが多すぎると問題が発生する
[画像: The voice of the Bastard DBA From Hell can be heard through the door: "You've got too many tables? Well, why don't you join them? — Doesn't help? Actually, I think I know another remedy..."]
© Laurenz Albe 2026 (see here for more background)
目次
- 顧客が観測した問題
- PostgreSQLでメモリ使用量が高くなる一般的な原因
- テーブルが多すぎるとメモリ問題がどのように発生するか
- テーブルが多すぎる例
- テーブルが多すぎることによるメモリ問題の軽減策
- テーブルが多すぎると長時間実行されるステートメントがどのように発生するか
- 長時間実行されるメタデータクエリの軽減策の試み
- テーブルが多すぎるとpg_upgradeが遅くなる理由
- テーブルが多すぎることによる影響のまとめ
最近、ある顧客のデータベースの問題調査を手伝いました。その問題は、データベース内のテーブルが多すぎることが原因であることが判明しました。これは多くのユーザーにとって驚きかもしれないので、このことについて書く価値があると思いました。
顧客が観測した問題
互いに関連があるかもしれないし、ないかもしれない、2つの問題がありました。
- Linuxのメモリ不足(OOM)キラーが時々PostgreSQLをクラッシュさせる
- 長時間実行されるクエリがCPUを占有し、アプリケーションの遅延を引き起こす
OOM問題の調査の最初のステップは、常にメモリオーバーコミットを無効にすることです。当初は、マシン上で実行されている他のソフトウェアがメモリ不足を引き起こしていると疑っていましたが、メモリコンテキストダンプはPostgreSQLが原因であることを示していました。
PostgreSQLでメモリ使用量が高くなる一般的な原因
PostgreSQLでメモリ使用量が高くなる一般的な原因はいくつかあります。
shared_buffers: PostgreSQLがサーバー起動時に割り当てる、固定サイズの共有メモリページキャッシュ。work_mem: バックエンドプロセスがSQLステートメントを処理するために使用するプライベートメモリ(パラメータwork_memを参照)。- 動的共有メモリ領域: PostgreSQLが並列クエリワーカーとバックエンドプロセス間でデータを交換するために使用するもの。
- 大きなバイナリデータ、JSON値、またはPostGISジオメトリをSQLステートメントがデTOASTして処理する必要がある場合。
しかし、このケースでは、メモリを占有していたのは別のものでした。
テーブルが多すぎるとメモリ問題がどのように発生するか
メモリオーバーコミットを無効にした後、ログファイルにメモリコンテキストダンプが記録され、メモリ不足でフォークに失敗したautovacuumワーカーからのログエントリも記録されました。当初、メモリコンテキストダンプには興味深いものは何も表示されませんでした。それらは原因ではなく、メモリ枯渇の犠牲者からのものでした。それゆえ、当初はPostgreSQL外部の原因を疑っていたのです。しかし、その後、以下のようなメモリコンテキストダンプが得られました。
| | |
| --- | --- |
| 1 2 3 4 5 6 7 8 9 10 | TopMemoryContext: 355016 total in 9 blocks; 203824 free (326 chunks); 151192 used TopTransactionContext: 8192 total in 1 blocks; 7736 free (1 chunks); 456 used Record information cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used ... CacheMemoryContext: 457826384 total in 15437 blocks; 468640 free (9 chunks); 457357744 used relation rules: 32768 total in 7 blocks; 3880 free (0 chunks); 28888 used: raster_overviews index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: pg_toast_2619_index index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: spatial_ref_sys_pkey index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: pg_toast_18156029_index ... |
実質的なメモリ使用量を示していたのはCacheMemoryContextのみでした。これはsrc/backend/utils/mmgr/READMEで次のように説明されています。
CacheMemoryContext --- relcache、catcache、および関連モジュールの永続ストレージ。これはリセットまたは削除されることもないため、TopMemoryContextと区別する必要は実際にはありません。しかし、デバッグ目的で区別を維持することは価値があるようです。
catcacheはメタデータテーブル(カタログ)エントリのキャッシュであり、relcacheはリレーション(テーブル、ビュー、インデックス、およびPostgreSQLがpg_classに格納するその他のすべて)の記述をキャッシュします。これらのキャッシュには固定サイズやサイズ制限はありません。むしろ、データベース接続が過去に使用したすべてのメタデータを追跡します。PostgreSQLデータベースのメタデータの量とリレーションの数は限られているため、これらのキャッシュが大きくなりすぎることはないという仮定に基づいています。
では、このバックエンドプロセスがCacheMemoryContextでほぼ500MBのRAMを蓄積したのでしょうか?その答えは、データベースに数万のテーブルが含まれており、長期間稼働するデータベース接続がそれらに触れるほど、キャッシュは大きくなり続けたということです。現在、500MBのRAMを占有する単一のデータベース接続は、おそらく最新のマシンをダウンさせることはないでしょうが、このような長期間稼働する接続が多数ある場合、最終的にはすべてのメモリを使い果たす可能性があります。
テーブルが多すぎる例
簡単な実験で問題を検証するのは容易です。その実験を試したい場合は、専用のデータベースを作成することをお勧めします。そうすれば、単純なDROP DATABASEですべてのテーブルを削除できます。以下は、それぞれ20列を持つ20,000のテーブルを作成するステートメントです。
| | |
| --- | --- |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | DO $$DECLARE i integer; BEGIN FOR i IN 1..20000 LOOP EXECUTE format( E'CREATE TABLE %I (\n' ' id bigint GENERATED ALWAYS AS IDENTITY\n' ' PRIMARY KEY,\n' ' col2 text,\n' ' col3 integer,\n' ' col4 timestamp,\n' ' col5 boolean,\n' ' col6 double precision,\n' ' col7 boolean,\n' ' col8 varchar(42),\n' ' col9 numeric,\n' ' col10 xml,\n' ' col11 bytea,\n' ' col12 bit varying(8),\n' ' col13 inet,\n' ' col14 date,\n' ' col15 bigint,\n' ' col16 smallint,\n' ' col17 uuid,\n' ' col18 timestamp with time zone,\n' ' col19 real,\n' ' col20 jsonb\n' ')', 'tab_' || i ); /* avoid running out of shared memory in the lock table */ IF i % 100 = 0 THEN COMMIT; END IF; END LOOP; END;$$;
その後、CacheMemoryContextのサイズを見てみましょう。
| | |
| --- | --- |
| 1 2 3 4 5 6 7 | SELECT pg_size_pretty(total_bytes) FROM pg_get_backend_memory_contexts() WHERE name = 'CacheMemoryContext'; pg_size_pretty ---------------- 4128 kB |
キャッシュのサイズは中程度です。次に、これらのテーブルすべてをSELECT文で利用してみましょう。
| | |
| --- | --- |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DO $$DECLARE i integer; BEGIN FOR i IN 1..20000 LOOP EXECUTE format( 'TABLE %I', 'tab_' || i ); /* again, to prevent exhausting the lock table */ IF i % 100 = 0 THEN COMMIT; END IF; END LOOP; END;$$;
TABLE SQLステートメントは、データベーステーブルのSELECT * FROMのショートカットです。その後、CacheMemoryContextは増大しました。
| | |
| --- | --- |
| 1 2 3 4 5 6 7 | SELECT pg_size_pretty(total_bytes) FROM pg_get_backend_memory_contexts() WHERE name = 'CacheMemoryContext'; pg_size_pretty ---------------- 221 MB |
テーブルが多すぎることによるメモリ問題の軽減策
最善の解決策は、そもそもテーブルが多すぎないようにすることです。そのような再設計が不可能な場合は、問題を少なくとも軽減するための他の方法があります。
- 適度なサイズのコネクションプールを使用する。データベース接続数を制限することで、全体的なメモリ使用量が自動的に制限されます。
- データベースセッションの一部が長期間アイドル状態になる場合は、
idle_session_timeout(PostgreSQL v14で導入)を設定して、それらがCacheMemoryContextを大量に蓄積する前に定期的に終了させることができます。これは、アプリケーションまたはコネクションプールが切断されたデータベース接続を正常に処理できる場合にのみ可能なオプションです。
テーブルが多すぎると長時間実行されるステートメントがどのように発生するか
顧客のデータベースで長時間実行されていたステートメントは、PostGISのメタデータビューであるraster_overviewsの単純なクエリでした。通常、メタデータクエリに多くの処理時間がかかるとは予想しないでしょうが、それはメタデータテーブルが通常中程度のサイズであるためです。問題のビューは、カタログテーブルpg_class、pg_attribute、pg_type、pg_namespace、およびpg_constraintを使用していました。このテストデータベースでのこれらのテーブルのサイズを見てみましょう。
| | |
| --- | --- |
| 1 2 3 4 5 6 7 8 9 10 11 | SELECT relname, reltuples FROM pg_class WHERE relname IN ('pg_class', 'pg_attribute', 'pg_type', 'pg_namespace', 'pg_constraint'); relname | reltuples ---------------+----------- pg_type | 40621 pg_attribute | 943161 pg_class | 100415 pg_constraint | 40194 pg_namespace | 4 |
これらのカウントに驚いた場合は、作成したテーブルが実際には属性を持つさらに多くのリレーションやその他のオブジェクトを作成したことを思い出してください。
- 主キーインデックス
- TOASTテーブル
- TOASTインデックス
- ID列用のシーケンス
- 各テーブルの複合型
- 複合型の各配列データ型
長時間実行されるメタデータクエリの軽減策の試み
PostgreSQL自体が実行するメタデータクエリを高速化するためにできることはほとんどありません。しかし、ビュー定義を単純化することでraster_overviewsを改善することが可能であるとわかりました。それは正しい方向への一歩でしたが、残念ながら問題のクエリを高速化するには十分ではありませんでした。
本当に、この問題に対処する唯一の良い方法は、単一のデータベースのテーブルやその他のデータベースオブジェクトの数を減らすことです。
テーブルが多すぎるとpg_upgradeが遅くなる理由
pg_upgradeは、PostgreSQLクラスターを後続のメジャーリリースにアップグレードする標準的な方法です。これはpg_dumpallとリストアよりもはるかに高速で、論理レプリケーションよりもはるかにシンプルです。データファイルをそのままコピーまたは再利用します。コミットログとMultiXactセグメントに関するいくつかのマジックを除けば、アップグレードの残りの部分は、バイナリアップグレードモードでのデータベースメタデータのダンプとリストアで構成されます。そのメタデータダンプとリストアの期間は、データベースオブジェクトの数に比例します。テーブルが多い場合、通常は数分で完了するアップグレードプロセスが、不快なほど遅くなり、ダウンタイムが長くなる可能性があります。
その問題に対する軽減策はありません。オブジェクトが多い場合は、テスト移行を実行して、ダウンタイムがどれくらいになるかの目安を把握してください。なお、ラージオブジェクトもデータベースオブジェクトとしてカウントされます。これは、ラージオブジェクトを絶対に使用すべきではないいくつかの理由の1つです。
テーブルが多すぎることによる影響のまとめ
おそらく驚くかもしれませんが、単一のデータベースにテーブルが多すぎると、メモリとパフォーマンスの問題が発生する可能性があります。背景情報を提供し、診断ツールを共有し、苦痛を軽減するためのアプローチを示しました。おそらくいつかPostgreSQLは、大規模なメタデータキャッシュを処理するためのよりスマートな方法を持つようになるでしょう。しかし、使用方法を乱用と呼ぶことができるユースケースを最適化することよりも、興味深いことがあることは理解できます。
この記事はテーブルが多すぎるという特殊なケースを扱いましたが、単一のデータベースにスキーマが多すぎる場合や、クラスターにデータベースやロールが多すぎる場合にも同様の困難を経験することが予想されます。PostgreSQLメタデータテーブルのサイズを適度に保つようにしてください!