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.

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:

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:

ORDER BY some_time DESC NULLS LAST

By the way, the default of PostgreSQL is NULLS FAST

http://qiita.com/hanoop…

Happy Hacking!

🖥 Recommended VPS Service

VULTR provides high performance cloud compute environment for you. Vultr has 15 data-centers strategically placed around the globe, you can use a VPS with 512 MB memory for just $ 2.5 / month ($ 0.004 / hour). In addition, Vultr is up to 4 times faster than the competition, so please check it => Check Benchmark Results!!