My Postgres Trail

PostgreSQL : Get total pages of an object

SELECT pg_relation_size(<object_id>, 'main')/8192);

The pg_relation_size gives the disk space occupied by the object. Since each page is of 8kB, the pg_relation_size size is divided by 8192 to give the page count.

Vim - Highlight OverLength

Add the following code to /etc/vimrc if we want to highlight the characters that go beyond column 80.

highlight OverLength ctermbg=red ctermfg=white guibg=#592929

match OverLength /\%81v.\+

PostgreSQL : Data Object sizes

Size of User Tables

=# SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) as "Total Size",
pg_size_pretty(pg_relation_size(relid)) as "Relation Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC
;
Table | Total Size | Relation Size | External Size
------------------+------------+---------------+---------------
load_test | 1883 MB | 1776 MB | 107 MB
pgbench_accounts | 1121 MB | 961 MB | 161 MB
pgbench_history | 8656 kB | 8656 kB | 0 bytes
pgbench_tellers | 80 kB | 40 kB | 40 kB
pgbench_branches | 24 kB | 8192 bytes | 16 kB
(5 rows)

Size of User Indexes

=# SELECT
indexrelname as "Index",
pg_size_pretty(pg_total_relation_size(indexrelid)) as "Size",
pg_size_pretty(pg_total_relation_size(indexrelid) - pg_relation_size(indexrelid)) as "External Size"
FROM pg_catalog.pg_statio_user_indexes ORDER BY pg_total_relation_size(indexrelid) DESC;
Index | Size | External Size
-----------------------+--------+---------------
pgbench_accounts_pkey | 161 MB | 0 bytes
load_testidx | 107 MB | 0 bytes
pgbench_tellers_pkey | 40 kB | 0 bytes
pgbench_branches_pkey | 16 kB | 0 bytes
(4 rows)

Functions

pg_relation_size(oid) - returns disk space occupied by the table or index of the given oid.

pg_total_relation_size(oid) - returns total disk space of the table of given oid, including indexes and toasted data

Clearing OS cache

Run the following command as root

echo 3 > /proc/sys/vm/drop_caches

Adding libpq in Contrib module

To include libpq functions in the contrib module the following header should be included:

#include "libpq-fe.h"

However, it throws the following error:

error: libpq-fe.h: No such file or directory

This can be solved by including the following in Makefile of that contib

PG_CPPFLAGS = -I$(libpq_srcdir)

This includes the option -I../../src/interfaces/libpq during make and hence the header file becomes accessible.

gdb commands

Future breakpoints

Set break point on future shared library:

set breakpoint pending on

Debug fork

To hold the forked process by gdb and not allow it to run independently:

set detach-on-fork off

Vim - Change case

Open the document in vim, press v and move to select text and then press one of the following:

U - converts to upper case
u - converts to lower case
~ - toggles the case of selected

To change text of entire file to lower case run the following

ggVGu

replace u with U for uppercase and ~ to toggle.