Can you really get details about quantity of data in a single area (column) in mysql or other db?

The particular on disc space dependence on a column for the entire table is usually difficult to determine. It is dependent on numerous factors.

About PostgreSQL particularly

  • Among individuals factors is data alignment. I authored much more about that inside a related answer.

  • A different one is compression. Text that's more than a couple of dozen bytes is going to be toasted. I.e. compressed and saved inside a inside a separate TOAST table.

  • Indexes increase total space requirement.

Answer by example

I personally use a genuine existence table with 21k locations named location with the objective. It's 20 posts, but name may be the greatest one. To show the overhead a table carries, I produced a brief table with only the main one column name additionally. (Overhead for any temp table is essentially exactly the same for an ordinary table - I examined.)

CREATE TEMP TABLE x AS SELECT name FROM location;

I Quickly used a number of PostgreSQL's database object size functions to produce 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-construed the question and also you only want the storage size for fundamental data types? That's listed with every chapter within the fine manual here.

Whether it's a varchar type, this will explain the amount of bytes utilized 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(*)).


Whether it's char, simply multiply count(*) through the column width.