酒と泪とRubyとRailsと

Ruby on Rails と Objective-C は酒の肴です!

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

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

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

PostgreSQLのTable/Index ごとのサイズを知る

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 をしてあげると良さそう。

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

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

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

http://qiita.com/bwtakac…

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

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

1
ORDER BY some_time DESC NULLS LAST

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

http://qiita.com/hanoop…

Happy Hacking!

おすすめの書籍