PostgreSQL Tips [Data Size per Table, Reindex, NULLSLAST]

While using PosgreSQL, this is my note which I learned.

  • Show data size per table/index in PostgreSQL
  • REINDEX command
  • Control sort with NULL

Show data size per table/index in PostgreSQL

While using PostgreSQL DB, sometimes you may want to know which table/index is using capacity.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
...

Source of the above SQL is here:

Monitoring Postgres

REINDEX

PostgreSQL seems to have unnecessary data remaining index unless you delete record well.
If there is such kind of data, it is better to run REINDEX command, like this:

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

Excellent tool pg_repack

It would be nice to use pg_repack which rebuilding a bloated table / index and rearranging records order to a specified index.
It can run online because it does not keep locking.

http://qiita.com/bwtakac…

Control a position of NULL in sort order

Control a position of NULL in sort order:

1
ORDER BY some_time DESC NULLS LAST

By the way, the default of PostgreSQL is NULLS FAST

http://qiita.com/hanoop…

Happy Hacking!