Administrative queries in postgressql

1)Finding the table size

In bytes:

select relpages * 8192 as size_in_bytes
  from pg_class where relname = 'mytable';

(or)

postgres=# select schemaname,relname,pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables;
 schemaname | relname | pg_size_pretty 
------------+---------+----------------
 public     | test1   | 72 kB
(1 row)

postgres=# 

In human-readable form:

select pg_size_pretty(relpages * 8192) as size
  from pg_class where relname = 'mytable';

2)Findout how many tuples and pages are used by a table
1page=8kb in postgresql.

select relname         as "table",
       reltuples       as "number of tuples",
       relpages        as "number of 8kb pages"
  from pg_class
where relname = 'my_table';
3)Find live and dead tuples of a table

select relname         as "table",
       n_live_tup      as "live tuples",
       n_dead_tup      as "dead tuples"
  from pg_stat_user_tables
where relname = 'my_table';

4)Findout when a table was last vacuumed and analysed

select relname         as "table",
       last_vacuum     as "last manual vacuum",
       last_autovacuum as "last auto vacuum",
       last_analyze    as "last manual analyze",
       last_autoanalyze as "last auto analyze"
  from pg_stat_user_tables
where relname = 'my_table';