CODEWITHSUNDEEP
postgresql
Deepesh Goel
Deepesh Goel

Reputation: 363

How to get each row size of a particular table in postgresql..?

Is it possible to get each row size of a particular table in postgres?

For example a table has 50 rows and total table size is 10gb but i want to know each row size rather than complete table size.

Upvotes: 36

Views: 49963

Answers (4)

chaotic3quilibrium
chaotic3quilibrium

Reputation: 5924

I needed to get amounts across several table sizing dimensions.

Here's the Postgres SQL query I used to obtain that, where I replace every instance (x4) of "target_table_name" with the table I am measuring:

SELECT 'target_table_name' AS table_name
     , COUNT(*) AS row_count
     , (SELECT pg_column_size(tr.*)
        FROM target_table_name AS tr
        LIMIT 1) AS row_size_in_bytes
     , SUM(pg_column_size(t)) / 1024 AS row_count_x_row_size_in_kbytes
     , pg_total_relation_size('target_table_name') / 1024 AS table_size_in_kbytes
FROM target_table_name as t
;

It returns something like this:

table_name row_count row_size_in_bytes row_count_x_row_size_in_kbytes table_size_in_kbytes
target_table_name 5093960 32 159186 292152

It's worth noting the large size difference between the last two columns. This means that estimations based on the row's size are a very poor indication of actual memory and disk space utilized.

Upvotes: 0

Awadesh
Awadesh

Reputation: 4058

If you want to calculate the size of a specific row or with the where clause returning only 1 row, try below -

select pg_column_size(table_name.*) as rowsize_in_bytes from table_name where id = '698119855102848';

Upvotes: 4

Kaushal Sachan
Kaushal Sachan

Reputation: 1243

Function to calculate row size of postgress table row is something like this

SELECT sum(pg_column_size(t.*)) as filesize, count(*) as filerow FROM TABLE_NAME as t;

replace TABLE_NAME with your table name; to add condition t.COLUMN_NAME = 'YOUR_VALUE'

Upvotes: 61

user330315
user330315

Reputation:

There is no function that calculates the size of a row, only a function that calculates the size of a column value. So you can do something like this:

select pg_column_size(column_1) + 
       pg_column_size(column_2) + 
       pg_column_size(column_3) as row_size
from the_table;

This could be automated using dynamic SQL and a PL/pgSQL function if you need this.

Upvotes: 11

Related Questions