Vijaykumar Jain <[email protected]> writes:
> can you rule out system catalog bloat ?
I don't know! I've now run the query from
https://wiki.postgresql.org/wiki/Show_database_bloat just just on
pg_catalog, results attached
On Sat, Apr 20, 2024 at 3:52 PM Vijaykumar Jain <
[email protected]> wrote:
>
>
> On Sat, Apr 20, 2024, 5:25 PM Michal Charemza <[email protected]>
> wrote:
>
>> Hi,
>>
>> We're running PostgreSQL as essentially a data warehouse, and we have a
>> few thousand roles, which are used to grant permissions on a table-by-table
>> basis to a few thousand users, so a user would typically have say between 1
>> and 2 thousand roles. There is also quite a lot of "churn" in terms of
>> tables being created/removed, and permissions changed.
>>
>> The issue is that we're hitting a strange performance problem on
>> connection. Sometimes it can take ~25 to 40 seconds just to connect,
>> although it's often way quicker
>>
>
> can you rule out system catalog bloat ?
>
>
current_database | schemaname | tablename | tbloat | wastedbytes |
iname | ibloat | wastedibytes
-------------------+------------+--------------------+--------+-------------+-----------------------------------------------+--------+--------------
public_datasets_1 | pg_catalog | pg_attribute | 1.6 | 381648896 |
pg_attribute_relid_attnum_index | 0.7 | 0
public_datasets_1 | pg_catalog | pg_attribute | 1.6 | 381648896 |
pg_attribute_relid_attnam_index | 1.3 | 143884288
public_datasets_1 | pg_catalog | pg_shdepend | 5.5 | 183640064 |
pg_shdepend_depender_index | 56.5 | 258285568
public_datasets_1 | pg_catalog | pg_shdepend | 5.5 | 183640064 |
pg_shdepend_reference_index | 27.3 | 122470400
public_datasets_1 | pg_catalog | pg_depend | 1.8 | 45187072 |
pg_depend_reference_index | 2.8 | 69427200
public_datasets_1 | pg_catalog | pg_depend | 1.8 | 45187072 |
pg_depend_depender_index | 3.1 | 79560704
public_datasets_1 | pg_catalog | pg_namespace | 52.5 | 42598400 |
pg_namespace_nspname_index | 30.7 | 8511488
public_datasets_1 | pg_catalog | pg_namespace | 52.5 | 42598400 |
pg_namespace_oid_index | 19.5 | 5316608
public_datasets_1 | pg_catalog | pg_index | 1.5 | 29949952 |
pg_index_indexrelid_index | 0.6 | 0
public_datasets_1 | pg_catalog | pg_index | 1.5 | 29949952 |
pg_index_indrelid_index | 0.5 | 0
public_datasets_1 | pg_catalog | pg_constraint | 1.6 | 25714688 |
pg_constraint_contypid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_constraint | 1.6 | 25714688 |
pg_constraint_oid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_constraint | 1.6 | 25714688 |
pg_constraint_conname_nsp_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_constraint | 1.6 | 25714688 |
pg_constraint_conrelid_contypid_conname_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_constraint | 1.6 | 25714688 |
pg_constraint_conparentid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_class | 1.1 | 13434880 |
pg_class_tblspc_relfilenode_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_class | 1.1 | 13434880 |
pg_class_oid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_class | 1.1 | 13434880 |
pg_class_relname_nsp_index | 0.4 | 0
public_datasets_1 | pg_catalog | pg_auth_members | 1.4 | 11870208 |
pg_auth_members_role_member_index | 3.0 | 35561472
public_datasets_1 | pg_catalog | pg_auth_members | 1.4 | 11870208 |
pg_auth_members_member_role_index | 3.2 | 39198720
public_datasets_1 | pg_catalog | pg_db_role_setting | 3.6 | 9125888 |
pg_db_role_setting_databaseid_rol_index | 0.8 | 0
public_datasets_1 | pg_catalog | pg_type | 1.2 | 4972544 |
pg_type_oid_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_type | 1.2 | 4972544 |
pg_type_typname_nsp_index | 0.6 | 0
public_datasets_1 | pg_catalog | pg_description | 1.7 | 4136960 |
pg_description_o_c_o_index | 0.7 | 0
public_datasets_1 | pg_catalog | pg_inherits | 1.6 | 1212416 |
pg_inherits_parent_index | 1.8 | 835584
public_datasets_1 | pg_catalog | pg_inherits | 1.6 | 1212416 |
pg_inherits_relid_seqno_index | 1.9 | 1048576
public_datasets_1 | pg_catalog | pg_attrdef | 1.1 | 270336 |
pg_attrdef_adrelid_adnum_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_attrdef | 1.1 | 270336 |
pg_attrdef_oid_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_database | 30.0 | 237568 |
pg_database_datname_index | 16.0 | 122880
public_datasets_1 | pg_catalog | pg_database | 30.0 | 237568 |
pg_database_oid_index | 10.0 | 73728
public_datasets_1 | pg_catalog | pg_sequence | 1.2 | 73728 |
pg_sequence_seqrelid_index | 1.0 | 8192
public_datasets_1 | pg_catalog | pg_rewrite | 1.5 | 73728 |
pg_rewrite_oid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_rewrite | 1.5 | 73728 |
pg_rewrite_rel_rulename_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_proc | 1.0 | 49152 |
pg_proc_oid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_proc | 1.0 | 49152 |
pg_proc_proname_args_nsp_index | 0.4 | 0
public_datasets_1 | pg_catalog | pg_collation | 1.0 | 16384 |
pg_collation_oid_index | 0.1 | 0
public_datasets_1 | pg_catalog | pg_collation | 1.0 | 16384 |
pg_collation_name_enc_nsp_index | 0.3 | 0
public_datasets_1 | pg_catalog | pg_enum | 1.0 | 0 |
pg_enum_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_enum | 1.0 | 0 |
pg_enum_typid_label_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_enum | 1.0 | 0 |
pg_enum_typid_sortorder_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_extension | 1.0 | 0 |
pg_extension_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_extension | 1.0 | 0 |
pg_extension_name_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_default_acl | 1.0 | 0 |
pg_default_acl_oid_index | 0.8 | 0
public_datasets_1 | pg_catalog | pg_default_acl | 1.0 | 0 |
pg_default_acl_role_nsp_obj_index | 1.2 | 8192
public_datasets_1 | pg_catalog | pg_conversion | 1.0 | 0 |
pg_conversion_oid_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_conversion | 1.0 | 0 |
pg_conversion_name_nsp_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_init_privs | 1.0 | 0 |
pg_init_privs_o_c_o_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_language | 1.0 | 0 |
pg_language_name_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_language | 1.0 | 0 |
pg_language_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_conversion | 1.0 | 0 |
pg_conversion_default_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_aggregate | 0.7 | 0 |
pg_aggregate_fnoid_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_opclass | 1.0 | 0 |
pg_opclass_am_name_nsp_index | 0.7 | 0
public_datasets_1 | pg_catalog | pg_opclass | 1.0 | 0 |
pg_opclass_oid_index | 0.7 | 0
public_datasets_1 | pg_catalog | pg_operator | 1.0 | 0 |
pg_operator_oid_index | 0.4 | 0
public_datasets_1 | pg_catalog | pg_operator | 1.0 | 0 |
pg_operator_oprname_l_r_n_index | 0.6 | 0
public_datasets_1 | pg_catalog | pg_opfamily | 1.0 | 0 |
pg_opfamily_am_name_nsp_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_opfamily | 1.0 | 0 |
pg_opfamily_oid_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_cast | 1.0 | 0 |
pg_cast_source_target_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_cast | 1.0 | 0 |
pg_cast_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_range | 1.0 | 0 |
pg_range_rngtypid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_range | 1.0 | 0 |
pg_range_rngmultitypid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_amproc | 1.0 | 0 |
pg_amproc_oid_index | 1.3 | 8192
public_datasets_1 | pg_catalog | pg_amproc | 1.0 | 0 |
pg_amproc_fam_proc_index | 1.7 | 16384
public_datasets_1 | pg_catalog | pg_amop | 1.0 | 0 |
pg_amop_oid_index | 1.0 | 0
public_datasets_1 | pg_catalog | pg_amop | 1.0 | 0 |
pg_amop_opr_fam_index | 1.2 | 8192
public_datasets_1 | pg_catalog | pg_amop | 1.0 | 0 |
pg_amop_fam_strat_index | 1.2 | 8192
public_datasets_1 | pg_catalog | pg_shdescription | 1.0 | 0 |
pg_shdescription_o_c_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_tablespace | 1.0 | 0 |
pg_tablespace_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_tablespace | 1.0 | 0 |
pg_tablespace_spcname_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_trigger | 1.0 | 0 |
pg_trigger_tgconstraint_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_trigger | 1.0 | 0 |
pg_trigger_tgrelid_tgname_index | 0.6 | 0
public_datasets_1 | pg_catalog | pg_trigger | 1.0 | 0 |
pg_trigger_oid_index | 0.2 | 0
public_datasets_1 | pg_catalog | pg_ts_config | 1.0 | 0 |
pg_ts_config_cfgname_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_config | 1.0 | 0 |
pg_ts_config_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_config_map | 1.0 | 0 |
pg_ts_config_map_index | 2.0 | 16384
public_datasets_1 | pg_catalog | pg_ts_dict | 1.0 | 0 |
pg_ts_dict_dictname_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_dict | 1.0 | 0 |
pg_ts_dict_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_parser | 1.0 | 0 |
pg_ts_parser_prsname_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_parser | 1.0 | 0 |
pg_ts_parser_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_template | 1.0 | 0 |
pg_ts_template_tmplname_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_ts_template | 1.0 | 0 |
pg_ts_template_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_am | 1.0 | 0 |
pg_am_oid_index | 2.0 | 8192
public_datasets_1 | pg_catalog | pg_am | 1.0 | 0 |
pg_am_name_index | 2.0 | 8192
(83 rows)
