Re: db size and tables size difference

Hi All,
looking in more details on OS partitions sizwe and each table space
corresponding to OS partitions will add up to close to 6,5GB for db size
same result as SQL table size.
The issue may be with pg_size_pretty() results, I don't have details
knowledge of this function.
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB

Isabella

Isabella Ghiurea wrote:
>
> Hi All,
> Tom, please see bellow are the results for the modified query with
> ORDER BY
>
> select pg_size_pretty(pg_database_size('db1')); ---->
> pg_size_pretty
> ----------------
> 12 GB
>
> SELECT 'the table size without table space restrictions';
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
> LIMIT 1000;
>
> ------------------------------------------------------+------------
> caom.spatialentity | 3216 MB
> caom.artifact | 2150 MB
> caom.plane | 677 MB
> caom.positionsample | 219 MB
> caom.simpleobservation | 202 MB
> caom.artifact_i1 | 171 MB
> caom.spatialentity_i1 | 162 MB
> caom.temporalentity | 86 MB
> caom.plane_psi2 | 86 MB
> caom.spectralentity | 73 MB
> caom.metric | 70 MB
> caom.plane_energy_i1 | 67 MB
> caom.plane_time_i1 | 58 MB
> caom.plane_position_i2 | 48 MB
> caom.polarizationentity | 33 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1 | 23 MB
> caom.metric_i2 | 18 MB
> caom.metric_i1 | 18 MB
> caom.plane_i1 | 15 MB
> caom.plane_position_i3 | 15 MB
> caom.plane_polar_i1 | 15 MB
> caom.plane_time_i2 | 15 MB
> caom.plane_energy_i2 | 15 MB
> caom.plane_i2 | 15 MB
> caom.simpleobservation_i1 | 12 MB
> caom.temporalentity_i1 | 9496 kB
> caom.spectralentity_i1 | 4384 kB
> caom.polarizationentity_i1 | 4368 kB
> caom.harvestskip | 2056 kB
> pg_catalog.pg_depend | 1008 kB
> pg_catalog.pg_proc | 880 kB
> caom.harveststate | 856 kB
> pg_catalog.pg_attribute | 648 kB
> caom.positionhole | 584 kB
> pg_catalog.pg_statistic | 576 kB
> caom.plane_phi2 | 496 kB
> caom.harvestskip_i1 | 480 kB
> pg_catalog.pg_proc_proname_args_nsp_index | 328 kB
> pg_catalog.pg_operator | 296 kB
> pg_catalog.pg_description | 280 kB
> pg_catalog.pg_depend_depender_index | 264 kB
> pg_catalog.pg_depend_reference_index | 264 kB
> pg_catalog.pg_rewrite | 256 kB
> pg_catalog.pg_attribute_relid_attnam_index | 240 kB
> pg_toast.pg_toast_2618 | 160 kB
> pg_catalog.pg_type | 144 kB
> pg_catalog.pg_class | 136 kB
> pg_catalog.pg_amop | 128 kB
> pg_catalog.pg_operator_oprname_l_r_n_index | 112 kB
> pg_catalog.pg_description_o_c_o_index | 96 kB
> pg_toast.pg_toast_2619 | 96 kB
> pg_catalog.pg_constraint | 80 kB
> pg_catalog.pg_conversion | 80 kB
> pg_catalog.pg_amproc | 72 kB
> pg_catalog.pg_attribute_relid_attnum_index | 72 kB
> pg_catalog.pg_proc_oid_index | 72 kB
> pg_catalog.pg_opclass | 72 kB
> pg_catalog.pg_trigger | 56 kB
> pg_catalog.pg_type_typname_nsp_index | 56 kB
> pg_catalog.pg_index | 56 kB
> information_schema.sql_features | 48 kB
> pg_catalog.pg_cast | 48 kB
> pg_catalog.pg_class_relname_nsp_index | 48 kB
> pg_catalog.pg_database | 48 kB
> pg_catalog.pg_authid | 48 kB
> pg_catalog.pg_ts_config_map | 48 kB
> pg_catalog.pg_opfamily | 48 kB
> pg_catalog.pg_language | 40 kB
> pg_catalog.pg_shdepend | 40 kB
> pg_catalog.pg_ts_parser | 40 kB
> pg_catalog.pg_ts_template | 40 kB
> pg_catalog.pg_auth_members | 40 kB
> pg_catalog.pg_ts_config | 40 kB
> pg_catalog.pg_ts_dict | 40 kB
> pg_catalog.pg_operator_oid_index | 40 kB
> pg_catalog.pg_tablespace | 40 kB
> pg_catalog.pg_namespace | 40 kB
> pg_catalog.pg_am | 40 kB
> pg_toast.pg_toast_45054 | 32 kB
> pg_catalog.pg_conversion_name_nsp_index | 32 kB
> pg_catalog.pg_shdescription | 32 kB
> tap_schema.tables | 32 kB
> pg_catalog.pg_statistic_relid_att_index | 32 kB
> pg_catalog.pg_amop_fam_strat_index | 32 kB
> tap_schema.schemas | 32 kB
> pg_catalog.pg_amproc_fam_proc_index | 32 kB
> pg_catalog.pg_ts_config_map_index | 32 kB
> tap_schema.columns | 32 kB
> caom.plane_phi1 | 32 kB
> pg_catalog.pg_amop_oid_index | 32 kB
> pg_catalog.pg_amop_opr_fam_index | 32 kB
> pg_catalog.pg_opclass_am_name_nsp_index | 32 kB
> caom.deletedplane | 24 kB
> pg_catalog.pg_pltemplate | 24 kB
> pg_catalog.pg_attrdef | 24 kB
> tap_schema.keys | 24 kB
> caom.deletedspatialentity | 24 kB
> caom.deletedpolarizationentity | 24 kB
> caom.compositeobservation | 24 kB
> caom.deletedtemporalentity | 24 kB
> caom.deletedartifact | 24 kB
> caom.deletedspectralentity | 24 kB
> pg_catalog.pg_aggregate | 24 kB
> information_schema.sql_implementation_info | 16 kB
> information_schema.sql_languages | 16 kB
> pg_catalog.pg_enum | 16 kB
> pg_catalog.pg_ts_config_oid_index | 16 kB
> pg_catalog.pg_trigger_oid_index | 16 kB
> tap_schema.schemas_pkey | 16 kB
> pg_catalog.pg_constraint_conrelid_index | 16 kB
> pg_catalog.pg_conversion_default_index | 16 kB
> pg_catalog.pg_namespace_oid_index | 16 kB
> pg_catalog.pg_namespace_nspname_index | 16 kB
> pg_catalog.pg_cast_source_target_index | 16 kB
> pg_catalog.pg_constraint_contypid_index | 16 kB
> information_schema.sql_packages | 16 kB
> pg_catalog.pg_aggregate_fnoid_index | 16 kB
> pg_catalog.pg_rewrite_oid_index | 16 kB
> pg_toast.pg_toast_2618_index | 16 kB
> pg_catalog.pg_trigger_tgconstrname_index | 16 kB
> pg_catalog.pg_trigger_tgrelid_tgname_index | 16 kB
> information_schema.sql_parts | 16 kB
> pg_catalog.pg_authid_rolname_index | 16 kB
> pg_catalog.pg_authid_oid_index | 16 kB
> pg_catalog.pg_auth_members_role_member_index | 16 kB
> pg_catalog.pg_auth_members_member_role_index | 16 kB
> information_schema.sql_sizing | 16 kB
> tap_schema.keys_pkey | 16 kB
> tap_schema.columns_pkey | 16 kB
> caom.deletedplane_i1 | 16 kB
> pg_catalog.pg_database_datname_index | 16 kB
> pg_catalog.pg_database_oid_index | 16 kB
> caom.deletedpolarizationentity_i1 | 16 kB
> pg_catalog.pg_shdescription_o_c_index | 16 kB
> pg_catalog.pg_language_name_index | 16 kB
> pg_catalog.pg_language_oid_index | 16 kB
> pg_catalog.pg_index_indrelid_index | 16 kB
> caom.timesample | 16 kB
> pg_catalog.pg_am_name_index | 16 kB
> pg_toast.pg_toast_2619_index | 16 kB
> pg_catalog.pg_constraint_conname_nsp_index | 16 kB
> pg_catalog.pg_index_indexrelid_index | 16 kB
> pg_catalog.pg_am_oid_index | 16 kB
> pg_catalog.pg_pltemplate_name_index | 16 kB
> pg_catalog.pg_shdepend_depender_index | 16 kB
> pg_catalog.pg_shdepend_reference_index | 16 kB
> pg_catalog.pg_tablespace_oid_index | 16 kB
> pg_catalog.pg_tablespace_spcname_index | 16 kB
> pg_toast.pg_toast_45054_index | 16 kB
> caom.deletedtemporalentity_i1 | 16 kB
> pg_catalog.pg_ts_dict_oid_index | 16 kB
> caom.deletedspectralentity_i1 | 16 kB
> caom.deletedspatialentity_i1 | 16 kB
> caom.deletedartifact_i1 | 16 kB
> pg_catalog.pg_constraint_oid_index | 16 kB
> pg_catalog.pg_opfamily_am_name_nsp_index | 16 kB
> pg_catalog.pg_opfamily_oid_index | 16 kB
> pg_catalog.pg_opclass_oid_index | 16 kB
> pg_catalog.pg_amproc_oid_index | 16 kB
> pg_catalog.pg_class_oid_index | 16 kB
> pg_catalog.pg_rewrite_rel_rulename_index | 16 kB
> pg_catalog.pg_ts_config_cfgname_index | 16 kB
> tap_schema.tables_pkey | 16 kB
>
>
> Thank you
> Isabella
>
>
> Hi All,
>
> Please, see more info my env: PG 8.3.6 on RHE5-64bits.
>
> 1. there are more than one schemas, but the size of the tables is close to
> 30-40kB, see some samples
> schemaname | tablename | size_pretty | total_size_pretty
> ------------+-------------+-------------+-------------------
> tap_schema | tables | 8192 bytes | 32 kB
> tap_schema | columns | 8192 bytes | 32 kB
> tap_schema | schemas | 8192 bytes | 32 kB
> tap_schema | keys | 8192 bytes | 24 kB
> tap_schema | key_columns | 8192 bytes | 8192 bytes
>
> 2. There are no BLOB's data type in db at this time :
> cvodb=# select * from pg_largeobject;
> loid | pageno | data
>
> ------+--------+------
> (0 rows)
>
>
> 3. As Tom suggested , I excluded the table space restriction and
> changed to pg_total_relation_size my original SQL :
>
>
> SELECT 'the table size without table space restrictions';
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> LIMIT 20;
> the table size without table space restrictions
> (1 row)
>
> relation | size
> ---------------------------+---------
> caom.spatialentity | 3216 MB
> caom.artifact | 2150 MB
> caom.plane | 677 MB
> caom.artifact_i1 | 171 MB
> caom.simpleobservation | 202 MB
> caom.spatialentity_i1 | 162 MB
> caom.positionsample | 219 MB
> caom.plane_psi2 | 86 MB
> caom.temporalentity | 86 MB
> caom.spectralentity | 73 MB
> caom.plane_energy_i1 | 67 MB
> caom.plane_time_i1 | 58 MB
> caom.plane_position_i2 | 48 MB
> caom.metric | 70 MB
> caom.polarizationentity | 33 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1 | 23 MB
> caom.metric_i2 | 18 MB
> caom.metric_i1 | 18 MB
> caom.plane_position_i3 | 15 MB
> (20 rows)
>
>
> 4. Where are the rest of 5,5 GB been used ? How can I get the system
> catalog correct size ?
>
>
>
> Thank you,
> Isabella
>
> Tom Lane wrote:
>>
>> Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> writes:
>> > I'm trying to understand why there are GB's difference when checking
>> > for db size using pg_size_pretty() and querying for tables + indexes
>> > size. .
>>
>> You are not counting everything --- the total DB size is clearly 12GB,
>> so the question is where are the other 5.5GB? Your first query shows
>> that schema caom accounts for 6+GB, but the second one does not prove
>> that schema caom contains all the big hogs. My guesses are:
>>
>> 1. Toast tables for tables that aren't in caom --- you used
>> pg_relation_size not pg_total_relation_size, and excluded toast
>> tables, so you are missing those.
>>
>> 2. pg_largeobject ... got any large objects?
>>
>> 3. Bloat in other system catalogs. 5GB of catalog bloat would be
>> pretty awful, but maybe that's what it is.
>>
>> Try that last query without the namespace restrictions.
>>
>
>
>>
>> regards, tom lane
>
> Hi Pg Admin list.
> I'm trying to understand why there are GB's difference when checking
> for db size using pg_size_pretty() and querying for tables + indexes
> size. .
> The sum of tables +index sizes is showing as aprox 6.5GB and
> pg_size_pretty(dbname) is coming as 12GB, this are the results after a
> full vacuum and reindexdb, also the sum of OS db files size is ~ 6.5GB.
> Any tips what I'm missing : are some "unallocated" db pages or anything
> else ?
>
> select pg_size_pretty(pg_database_size('db1'));
> pg_size_pretty
> ----------------
> 12 GB
> (1 row)
> *** Check for tables size :
> SELECT schemaname, tablename,
> pg_size_pretty(size) AS size_pretty,
> pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *,
> pg_relation_size(schemaname||'.'||tablename) AS
> size,pg_total_relation_size(schemaname||'.'||tablename) AS
> total_size FROM pg_tables) AS TABLES where schemaname='caom'
> ORDER BY total_size DESC;
> schemaname | tablename | size_pretty | total_size_pretty
> ------------+-----------------------------+-------------+-------------------
> caom | spatialentity | 3053 MB | 3216 MB
> caom | artifact | 1979 MB | 2150 MB
> caom | plane | 413 MB | 677 MB
> caom | positionsample | 110 MB | 219 MB
> caom | simpleobservation | 165 MB | 202 MB
> caom | temporalentity | 77 MB | 86 MB
> caom | spectralentity | 68 MB | 73 MB
> caom | metric | 33 MB | 70 MB
> caom | polarizationentity | 29 MB | 33 MB
> caom | harvestskip | 1576 kB | 2056 kB
> caom | harveststate | 840 kB | 856 kB
> caom | positionhole | 48 kB | 584 kB
>
>
>
>
> *** OR : check for the biggest tables+index size:
> SELECT ' Top 20 biggest tables and indexes'
> ;
> SELECT nspname || '.' || relname AS "relation",
> pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> AND nspname !~ '^pg_toast'
> AND pg_relation_size(nspname || '.' || relname)>0
> ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> LIMIT 20;
> (1 row)
>
> relation | size
> ---------------------------+---------
> caom.spatialentity | 3053 MB
> caom.artifact | 1979 MB
> caom.plane | 413 MB
> caom.artifact_i1 | 171 MB
> caom.simpleobservation | 165 MB
> caom.spatialentity_i1 | 162 MB
> caom.positionsample | 110 MB
> caom.plane_psi2 | 86 MB
> caom.temporalentity | 77 MB
> caom.spectralentity | 68 MB
> caom.plane_energy_i1 | 67 MB
> caom.plane_time_i1 | 58 MB
> caom.plane_position_i2 | 48 MB
> caom.metric | 33 MB
> caom.polarizationentity | 29 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1 | 23 MB
> caom.metric_i2 | 18 MB
>
> Thank you
> Isabella
>
>
>

--
View this message in context: http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531314.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.