'Unnaturally large Postgres database size
I'm not very experienced with Postgres but I have a relatively small database (less than ~300 rows total) but it takes up more than 150 MB of storage. It grew to this from ~11 MB without much addition of new data. I was wondering what might be the cause of this and how can I fix this issue?
I ran the following queries to get an understanding of what might be happening, but being new to Postgres I cannot really find a proper fix.
SELECT SUM(pg_database_size(pg_database.datname)) / (1024 * 1024) as size_mb
FROM pg_database;
| size_mb |
| -------------------- |
| 162.0249948501586914 |
select table_schema, table_name,
pg_size_pretty(pg_total_relation_size('"'||table_schema||'"."'||table_name||'"')) as size
from information_schema.tables;
The result:
pg_class and pg_statistic take up 95 MB and 43 MB respectively.
| table_schema | table_name | size |
| ------------------ | ------------------------------------- | ---------- |
| pg_catalog | pg_statistic | 43 MB |
| pg_catalog | pg_type | 208 kB |
| pg_catalog | pg_foreign_table | 16 kB |
| pg_catalog | pg_authid | 80 kB |
| pg_catalog | pg_shadow | 0 bytes |
| pg_catalog | pg_statistic_ext_data | 16 kB |
| pg_catalog | pg_roles | 0 bytes |
| pg_catalog | pg_settings | 0 bytes |
| pg_catalog | pg_file_settings | 0 bytes |
| pg_catalog | pg_hba_file_rules | 0 bytes |
| pg_catalog | pg_config | 0 bytes |
| pg_catalog | pg_shmem_allocations | 0 bytes |
| pg_catalog | pg_prepared_statements | 0 bytes |
| pg_catalog | pg_largeobject | 8192 bytes |
| pg_catalog | pg_user_mapping | 24 kB |
| pg_catalog | pg_stat_progress_vacuum | 0 bytes |
| pg_catalog | pg_replication_origin_status | 0 bytes |
| pg_catalog | pg_subscription | 24 kB |
| pg_catalog | pg_attribute | 848 kB |
| pg_catalog | pg_proc | 1216 kB |
| pg_catalog | pg_class | 95 MB |
| pg_catalog | pg_attrdef | 80 kB |
| pg_catalog | pg_constraint | 136 kB |
| pg_catalog | pg_inherits | 16 kB |
| pg_catalog | pg_index | 112 kB |
| pg_catalog | pg_operator | 232 kB |
| pg_catalog | pg_opfamily | 80 kB |
| pg_catalog | pg_opclass | 80 kB |
| pg_catalog | pg_am | 72 kB |
| pg_catalog | pg_amop | 192 kB |
| pg_catalog | pg_amproc | 128 kB |
| pg_catalog | pg_language | 80 kB |
| pg_catalog | pg_largeobject_metadata | 8192 bytes |
| pg_catalog | pg_aggregate | 72 kB |
| pg_catalog | pg_statistic_ext | 32 kB |
| pg_catalog | pg_rewrite | 696 kB |
| pg_catalog | pg_trigger | 96 kB |
| pg_catalog | pg_event_trigger | 48 kB |
| pg_catalog | pg_description | 496 kB |
| pg_catalog | pg_cast | 80 kB |
| pg_catalog | pg_stat_progress_cluster | 0 bytes |
| pg_catalog | pg_stat_progress_create_index | 0 bytes |
| pg_catalog | pg_stat_progress_basebackup | 0 bytes |
| pg_catalog | pg_user_mappings | 0 bytes |
| pg_catalog | pg_enum | 56 kB |
| pg_catalog | pg_namespace | 80 kB |
| pg_catalog | pg_conversion | 96 kB |
| pg_catalog | pg_depend | 1240 kB |
| pg_catalog | pg_database | 80 kB |
| pg_catalog | pg_db_role_setting | 32 kB |
| pg_catalog | pg_tablespace | 80 kB |
| pg_catalog | pg_auth_members | 72 kB |
| pg_catalog | pg_shdepend | 112 kB |
| pg_catalog | pg_shdescription | 64 kB |
| pg_catalog | pg_ts_config | 72 kB |
| pg_catalog | pg_ts_config_map | 88 kB |
| pg_catalog | pg_ts_dict | 80 kB |
| pg_catalog | pg_ts_parser | 72 kB |
| pg_catalog | pg_ts_template | 72 kB |
| pg_catalog | pg_extension | 80 kB |
| pg_catalog | pg_foreign_data_wrapper | 24 kB |
| pg_catalog | pg_foreign_server | 24 kB |
| pg_catalog | pg_policy | 48 kB |
| pg_catalog | pg_replication_origin | 24 kB |
| pg_catalog | pg_default_acl | 48 kB |
| pg_catalog | pg_init_privs | 72 kB |
| pg_catalog | pg_seclabel | 16 kB |
| pg_catalog | pg_shseclabel | 16 kB |
| pg_catalog | pg_collation | 80 kB |
| pg_catalog | pg_partitioned_table | 16 kB |
| pg_catalog | pg_range | 56 kB |
| pg_catalog | pg_transform | 16 kB |
| pg_catalog | pg_sequence | 24 kB |
| pg_catalog | pg_publication | 40 kB |
| pg_catalog | pg_publication_rel | 16 kB |
| pg_catalog | pg_subscription_rel | 8192 bytes |
| pg_catalog | pg_group | 0 bytes |
| pg_catalog | pg_user | 0 bytes |
| pg_catalog | pg_policies | 0 bytes |
| pg_catalog | pg_rules | 0 bytes |
| pg_catalog | pg_views | 0 bytes |
| pg_catalog | pg_tables | 0 bytes |
| pg_catalog | pg_matviews | 0 bytes |
| pg_catalog | pg_indexes | 0 bytes |
| pg_catalog | pg_sequences | 0 bytes |
| pg_catalog | pg_stats | 0 bytes |
| pg_catalog | pg_stats_ext | 0 bytes |
| pg_catalog | pg_publication_tables | 0 bytes |
| pg_catalog | pg_locks | 0 bytes |
| pg_catalog | pg_cursors | 0 bytes |
| pg_catalog | pg_available_extensions | 0 bytes |
| pg_catalog | pg_available_extension_versions | 0 bytes |
| pg_catalog | pg_prepared_xacts | 0 bytes |
| pg_catalog | pg_seclabels | 0 bytes |
| pg_catalog | pg_statio_user_tables | 0 bytes |
| pg_catalog | pg_stat_all_indexes | 0 bytes |
| pg_catalog | pg_timezone_abbrevs | 0 bytes |
| pg_catalog | pg_timezone_names | 0 bytes |
| pg_catalog | pg_stat_sys_indexes | 0 bytes |
| pg_catalog | pg_stat_all_tables | 0 bytes |
| pg_catalog | pg_stat_xact_all_tables | 0 bytes |
| pg_catalog | pg_stat_sys_tables | 0 bytes |
| pg_catalog | pg_stat_xact_sys_tables | 0 bytes |
| pg_catalog | pg_stat_user_tables | 0 bytes |
| pg_catalog | pg_stat_xact_user_tables | 0 bytes |
| pg_catalog | pg_statio_all_tables | 0 bytes |
| pg_catalog | pg_statio_sys_tables | 0 bytes |
| pg_catalog | pg_stat_user_indexes | 0 bytes |
| pg_catalog | pg_statio_all_indexes | 0 bytes |
| pg_catalog | pg_statio_sys_indexes | 0 bytes |
| pg_catalog | pg_statio_user_indexes | 0 bytes |
| pg_catalog | pg_statio_all_sequences | 0 bytes |
| pg_catalog | pg_statio_sys_sequences | 0 bytes |
| pg_catalog | pg_statio_user_sequences | 0 bytes |
| pg_catalog | pg_stat_activity | 0 bytes |
| pg_catalog | pg_stat_replication | 0 bytes |
| pg_catalog | pg_stat_slru | 0 bytes |
| pg_catalog | pg_stat_wal_receiver | 0 bytes |
| pg_catalog | pg_stat_subscription | 0 bytes |
| pg_catalog | pg_stat_ssl | 0 bytes |
| pg_catalog | pg_stat_gssapi | 0 bytes |
| pg_catalog | pg_replication_slots | 0 bytes |
| pg_catalog | pg_stat_database | 0 bytes |
| pg_catalog | pg_stat_database_conflicts | 0 bytes |
| pg_catalog | pg_stat_user_functions | 0 bytes |
| pg_catalog | pg_stat_xact_user_functions | 0 bytes |
| pg_catalog | pg_stat_archiver | 0 bytes |
| pg_catalog | pg_stat_bgwriter | 0 bytes |
| pg_catalog | pg_stat_progress_analyze | 0 bytes |
| information_schema | columns | 0 bytes |
| information_schema | information_schema_catalog_name | 0 bytes |
| information_schema | collations | 0 bytes |
| information_schema | applicable_roles | 0 bytes |
| information_schema | administrable_role_authorizations | 0 bytes |
| information_schema | column_privileges | 0 bytes |
| information_schema | attributes | 0 bytes |
| information_schema | collation_character_set_applicability | 0 bytes |
| information_schema | character_sets | 0 bytes |
| information_schema | check_constraint_routine_usage | 0 bytes |
| information_schema | check_constraints | 0 bytes |
| information_schema | column_column_usage | 0 bytes |
| information_schema | column_domain_usage | 0 bytes |
| information_schema | column_udt_usage | 0 bytes |
| information_schema | constraint_column_usage | 0 bytes |
| extensions | pg_stat_statements | 0 bytes |
| information_schema | constraint_table_usage | 0 bytes |
| information_schema | domain_constraints | 0 bytes |
| information_schema | domain_udt_usage | 0 bytes |
| information_schema | domains | 0 bytes |
| information_schema | enabled_roles | 0 bytes |
| information_schema | sql_implementation_info | 48 kB |
| information_schema | key_column_usage | 0 bytes |
| information_schema | parameters | 0 bytes |
| information_schema | referential_constraints | 0 bytes |
| information_schema | role_column_grants | 0 bytes |
| information_schema | sql_parts | 48 kB |
| information_schema | routine_privileges | 0 bytes |
| information_schema | role_routine_grants | 0 bytes |
| information_schema | routines | 0 bytes |
| information_schema | schemata | 0 bytes |
| information_schema | tables | 0 bytes |
| information_schema | sequences | 0 bytes |
| information_schema | sql_sizing | 48 kB |
| information_schema | sql_features | 104 kB |
| information_schema | table_constraints | 0 bytes |
| information_schema | transforms | 0 bytes |
| information_schema | table_privileges | 0 bytes |
| information_schema | role_table_grants | 0 bytes |
| information_schema | udt_privileges | 0 bytes |
| information_schema | triggered_update_columns | 0 bytes |
| information_schema | triggers | 0 bytes |
| information_schema | usage_privileges | 0 bytes |
| information_schema | role_udt_grants | 0 bytes |
| information_schema | role_usage_grants | 0 bytes |
| information_schema | user_defined_types | 0 bytes |
| information_schema | view_column_usage | 0 bytes |
| information_schema | view_routine_usage | 0 bytes |
| information_schema | view_table_usage | 0 bytes |
| information_schema | foreign_tables | 0 bytes |
| information_schema | views | 0 bytes |
| information_schema | data_type_privileges | 0 bytes |
| information_schema | _pg_user_mappings | 0 bytes |
| information_schema | element_types | 0 bytes |
| information_schema | _pg_foreign_table_columns | 0 bytes |
| information_schema | column_options | 0 bytes |
| information_schema | _pg_foreign_data_wrappers | 0 bytes |
| information_schema | foreign_data_wrapper_options | 0 bytes |
| information_schema | user_mapping_options | 0 bytes |
| information_schema | foreign_data_wrappers | 0 bytes |
| information_schema | _pg_foreign_servers | 0 bytes |
| information_schema | foreign_server_options | 0 bytes |
| information_schema | foreign_servers | 0 bytes |
| information_schema | _pg_foreign_tables | 0 bytes |
| information_schema | user_mappings | 0 bytes |
| information_schema | foreign_table_options | 0 bytes |
| auth | refresh_tokens | 160 kB |
| auth | audit_log_entries | 336 kB |
| auth | instances | 16 kB |
| auth | schema_migrations | 24 kB |
| public | card_tag | 56 kB |
| public | users | 32 kB |
| public | cards | 144 kB |
| public | collections | 32 kB |
| public | tags | 32 kB |
| storage | buckets | 24 kB |
| storage | migrations | 40 kB |
| storage | objects | 32 kB |
| auth | identities | 104 kB |
| realtime | schema_migrations | 24 kB |
| realtime | subscription | 56 kB |
| auth | users | 176 kB |
Does this help narrow down the possible cause?
UPDATE 1:
Ran the following queries:
vacuum verbose pg_statistic;
vacuum verbose pg_class;
Output:
vacuuming "pg_catalog.pg_class"
launched 2 parallel vacuum workers for index cleanup (planned: 2)
"pg_class": found 0 removable, 358975 nonremovable row versions in 8160 out of 8160 pages
vacuuming "pg_catalog.pg_statistic"
"pg_statistic": found 0 removable, 122120 nonremovable row versions in 5330 out of 5330 pages
vacuuming "pg_toast.pg_toast_2619"
"pg_toast_2619": found 0 removable, 98 nonremovable row versions in 22 out of 22 pages
SELECT pg_stat_statements_reset();
SELECT pg_stat_reset();
The database size hasn't decreased. However, I had added two new rows into one of my tables and the size increased from 162 MB to now 163.67 MB.
UPDATE 2:
select count(*) from pg_class
| count |
|---|
| 469 |
UPDATE 3:
Table schemas:
cards
| column_name | data_type | character_maximum_length | column_default | is_nullable |
| ------------- | --------- | ------------------------ | -------------- | ----------- |
| id | bigint | | | NO |
| user_id | uuid | | | NO |
| excerpt | text | | | YES |
| note | text | | | YES |
| collection_id | bigint | | | YES |
| created_at | date | | now() | NO |
| tags | ARRAY | | | NO |
| fts | tsvector | | | YES |
tags
| column_name | data_type | character_maximum_length | column_default | is_nullable |
| ----------- | --------- | ------------------------ | -------------- | ----------- |
| id | bigint | | | NO |
| user_id | uuid | | | NO |
| name | text | | | NO |
| colour | character | 6 | | NO |
collections
| column_name | data_type | character_maximum_length | column_default | is_nullable |
| ----------- | --------- | ------------------------ | -------------- | ----------- |
| id | bigint | | | NO |
| name | text | | | NO |
| link | text | | | YES |
| user_id | uuid | | | NO |
| author | text | | | YES |
card_tag
| column_name | data_type | character_maximum_length | column_default | is_nullable |
| ----------- | --------- | ------------------------ | -------------- | ----------- |
| card_id | bigint | | | NO |
| tag_id | bigint | | | NO |
| user_id | uuid | | | NO |
users
| column_name | data_type | character_maximum_length | column_default | is_nullable |
| ----------- | --------- | ------------------------ | -------------- | ----------- |
| id | uuid | | | NO |
| email | text | | | YES |
| customer_id | text | | | YES |
UPDATE 4:
fts column in cards table is generated as:
ALTER TABLE cards
ADD COLUMN fts tsvector generated always as (to_tsvector('english', excerpt || ' ' || note)) stored;
fts is also indexed.
create index cards_fts on cards using gin (fts);
UPDATE 5:
vacuum verbose pg_class;
vacuuming "pg_catalog.pg_class"
launched 1 parallel vacuum worker for index cleanup (planned: 1)
"pg_class": found 0 removable, 358987 nonremovable row versions in 8160 out of 8160 pages
select pg_column_size(to_tsvector('english', excerpt || ' ' || note)) from cards;
| pg_column_size |
| -------------- |
| 80 |
| 196 |
| 68 |
| 514 |
| 198 |
| 32 |
| 32 |
| 78 |
| 186 |
| 62 |
| 370 |
| 34 |
| 436 |
| 540 |
| 314 |
| 560 |
| 20 |
| 46 |
| 472 |
| 136 |
| 124 |
| 84 |
| 22 |
| 592 |
| 22 |
| 318 |
| 54 |
| 516 |
| 54 |
| 250 |
| 106 |
| 76 |
| 296 |
| 450 |
| 38 |
| 34 |
| 178 |
| |
| 170 |
UPDATE 6:
select relname ,pg_stat_get_live_tuples(c.oid) AS live_tuples ,pg_stat_get_dead_tuples(c.oid) AS dead_tuples from pg_class c order by dead_tuples desc
| relname | live_tuples | dead_tuples |
| ----------------------------------------------- | ----------- | ----------- |
| pg_class | 469 | 358518 |
| pg_attribute | 0 | 126 |
| pg_depend | 0 | 10 |
| pg_type | 0 | 7 |
| pg_shdepend | 225 | 6 |
| users | 1 | 4 |
| refresh_tokens | 6 | 3 |
| card_tag | 5 | 2 |
| pg_db_role_setting | 7 | 1 |
| cards | 3 | 1 |
| pg_index | 0 | 1 |
| identities | 1 | 1 |
| pg_authid | 18 | 1
UPDATE 7:
select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname in ('pg_class','cards', 'tags', 'collections', 'card_tag', 'users') );
| pid | state | usename | query | query_start |
| ----- | ------ | -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------- |
| 79331 | active | supabase_admin |
-- source: dashboard
-- user: 20248
-- date: 2022-04-08T15:02:13.664Z
select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname in ('pg_class','cards', 'tags', 'collections', 'card_tag', 'users') );
| 2022-04-08 15:02:14.123713+00 |
select version()
| version |
|---|
| PostgreSQL 13.3 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|