PostgreSQL Tips [テーブルごとのデータ量、 Reindex, NULLSLAST]


最近いくつかPostgreSQLを使っている中で、勉強になった点のメモです。

  • PostgreSQLのテーブル/Indexごとのサイズを知る
  • Reindexコマンド
  • ソート順でNULLの位置を制御する

🍮 PostgreSQLのテーブル/Indexごとのサイズを知る

DBを使っている時、それぞれのテーブル/Indexを知りたい場合に実行するコマンド。

SELECT pgn.nspname, relname, pg_size_pretty(relpages::bigint * 8 * 1024) AS size, CASE WHEN relkind =
't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) WHEN nspname =
'pg_toast' AND relkind = 'i' THEN (SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname
FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '')) ELSE (SELECT pgc.relname FROM
pg_class pgc WHERE pg.reltoastrelid = pgc.oid) END::varchar AS refrelname, CASE WHEN nspname =
'pg_toast' AND relkind = 'i' THEN (SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid =
(SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname
FROM 10), '_index', ''))) END AS relidxrefrelname, relfilenode, relkind, reltuples::bigint, relpages
FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid AND pgn.nspname NOT IN
('information_schema', 'pg_catalog') ORDER BY relpages DESC;
nspname | relname | size | refrelname | relidxrefrelname | relfilenode | relkind | reltuples | relpages
----------+-------------------------------------------------+------------+-------------------------+-------------------------+-------------+---------+-----------+----------
pg_toast | pg_toast_246538 | 1648 kB | scrape_items | | 246543 | t | 51 | 206
pg_toast | pg_toast_2618 | 376 kB | pg_rewrite | | 2838 | t | 206 | 47
pg_toast | pg_toast_246538_index | 56 kB | pg_toast_246538 | scrape_items | 246545 | i | 79 | 7
...

上記SQLの出典はこちら。

http://qiita.com/awaki…

😼 REINDEX

PostgreSQLはうまくデータを削除しないとindexに不要なデータが残ることがあるみたい。
indexに不要なデータが残っている場合は REINDEX をしてあげると良さそう。

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

🎃 本格運用ならpg_repackを使うと良さそう

肥大化したテーブルやインデックスを再編成し、さらに指定したインデックスにしたがってレコードを並び替えてくれる pg_repack を使うと良さそう。
ロックを保持し続けないのでオンラインで実行できるみたい。

http://qiita.com/bwtakac…

🗽 ソート順でNULLの位置を制御する

ソート順でNULLを下にしたい場合:

ORDER BY some_time DESC NULLS LAST

ちなみにデフォルトは NULL FASTらしいです。

http://qiita.com/hanoop…

Happy Hacking!

📚 おすすめの書籍

🖥 サーバについて

このブログでは「Cloud Garage」さんのDev Assist Program(開発者向けインスタンス無償提供制度)でお借りしたサーバで技術検証しています。 Dev Assist Programは、開発者や開発コミュニティ、スタートアップ企業の方が1GBメモリのインスタンス3台を1年間無料で借りれる心強い制度です!(有償でも1,480円/月と格安)