In other article, showed few ways to get postgres database, table, and index sizes. Here is a way to show you how to get one column size used in a postgres table.

To get this estimated number, we can use 'pg_column_size', here is the quick SQL.

billing=> select pnfsid,pg_column_size(path),path from doorinfo limit 3;
pnfsid | pg_column_size | path
000091AFF22097AF4EFDB02660EA4A2B9C74 | 96 |...1.pool.root.1
0000360CDD1E7718473C96E127951FF1B106 | 96 |...2.pool.root.1
0000CFDE62EC072A49F6AA059C79BF44698B | 96 |...3.pool.root.1

As you can see it tells each row space taken in the table. Similarily, you can use use combine total, avg, and sum functions with it and create more interesting result for you.

To get one conlumn total size, average size, and space usage percentage in the whole table, use this SQL

billing_2015=> select
sum(pg_column_size(path)) as total_size,
avg(pg_column_size(path)) as average_size,
sum(pg_column_size(path)) * 100.0 / pg_relation_size('doorinfo') as percentage
from doorinfo;
total_size | average_size | percentage
23925375206 | 105.3043002098280166 | 25.7501352987863584

Pretty easy, right?

If the column you want to check is also an index of the table, make sure you count that in too, see How to get db, table, and index sizes





Comments powered by CComment