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 = |
Source of the above SQL is here:
🐝 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.
🍣 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
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!!