I got a very interesting question today from a developer. What is the size of a tuple in a table. Well you kinda pause for a while and think ummm I thought I knew this one. Well postgresql has a contrib module that can help you out with this. Lets take a look at pgstattuple, this contrib module will give you information like table length, number of live tuples in a table, total length of live tuples, number of dead tuples, free space in a table and much more.
All this information is extremely helpful when you want to check the stats on a specific table. Lets go ahead and get this conrib module started in our current database:
Assuming you have source install for postgresql available
$ cd postgresql-8.4.9/contrib/pgstattuple $ make $ make install
Now we need to the pgstattuple functions to be available in the database we are trying to check the tuples on. For that run the sql:
$ psql dbname -f pgstattuple.sql
That’s it we are all set to check the tuples. Connect to your database and call the function as:
test=# SELECT * FROM pgstattuple('test.account_services'); -[ RECORD 1 ]------+--------- table_len | 50683904 tuple_count | 597431 tuple_len | 47914138 tuple_percent | 94.54 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 207320 free_percent | 0.41
I got the approximate/average length of a tuple by tuple_len/tuple_count (not the smartest way to get that info but oh well it worked for me)
You can also use pgstatindex to get information on the indexes.
hdap=# SELECT * FROM pgstatindex('pg_cast_oid_index'); -[ RECORD 1 ]------+------ version | 2 tree_level | 0 index_size | 8192 root_block_no | 1 internal_pages | 0 leaf_pages | 1 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 37.54 leaf_fragmentation | 0