Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> 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 <vijaykumarjain.github@xxxxxxxxx> wrote:
On Sat, Apr 20, 2024, 5:25 PM Michal Charemza <michal@xxxxxxxxxxxxx> 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 quickercan 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)
