Amount of data in database for certain field (column)

Question

Is it possible to get information about amount of data in one field (column) in mysql or any other db?

What sort of information? Storage space used? Row count? Average value? — ceejayoz, Jan 23 '12 at 19:24
What kind of field? Most fields have a size associated with their data type. — Oded, Jan 23 '12 at 19:24
What do you mean by 'amount of data'? How many bytes a table takes up? Or how many rows there are? Or... ? — Josh, Jan 23 '12 at 19:25
RTFM? http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html — Marc B, Jan 23 '12 at 19:33

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

The actual on disc space requirement of a column for the whole table is generally hard to determine. It depends on a number of factors.

About PostgreSQL in particular

One of those factors is data alignment. I wrote more about that in a related answer.
Another one is compression. Text that is longer than a few dozen bytes will be toasted. I.e. compressed and stored in a in a separate TOAST table.
Indexes add to total space requirement.

Answer by example

I use a real life table with 21k locations named location for the purpose. It has 20 columns, but name is the biggest one. To demonstrate the overhead a table carries, I created a temporary table with just the one column name in addition. (Overhead for a temp table is basically the same as for a plain table - I tested.)

CREATE TEMP TABLE x AS SELECT name FROM location;

Then I used some of PostgreSQL's database object size functions to create this demo:

SELECT pg_size_pretty(pg_table_size('loc'::regclass)) AS tbl_size
      ,pg_size_pretty(pg_relation_size('loc'::regclass)) AS rel_size
      ,(SELECT pg_size_pretty(sum(pg_column_size(loc))) from loc) AS sum_col_size

      ,pg_size_pretty(pg_table_size('x'::regclass)) AS tbl_size
      ,pg_size_pretty(pg_relation_size('x'::regclass)) AS rel_size
      ,(SELECT pg_size_pretty(sum(pg_column_size(loc))) from x) AS sum_col_size

Result:

tbl_size | rel_size | sum_col_size | x_tbl_size | x_rel_size | x_sum_col_size
---------+----------+--------------+------------+------------+---------------
3160 kB  | 3128 kB  | 432 kB       | 1104 kB    | 1096 kB    | 432 kB

But maybe I over-interpreted the question and you only want the storage size for basic data types? That's listed with every chapter in the fine manual here.

score 0 · Accepted Answer · answered Jan 23 '12 at 20:51

If it's a varchar type, this will tell you the number of bytes used by the column:

select sum(length(column_name)) + count(*) as total_bytes from table_name;

For varchar, you add 1 byte per row (ie add count(*)).

If it's char, simply multiply count(*) by the column width.

Amount of data in database for certain field (column)

2 Answers2

About PostgreSQL in particular

Answer by example

Linked