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!

🖥 VULTRおすすめ

「VULTR」はVPSサーバのサービスです。日本にリージョンがあり、最安は512MBで2.5ドル/月($0.004/時間)で借りることができます。4GBメモリでも月20ドルです。最近はVULTRのヘビーユーザーになので、「ここ」から会員登録してもらえるとサービス開発が捗ります！

酒と泪とRubyとRailsと