Vijaykumar Jain <vijaykumarjain.git...@gmail.com> 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.git...@gmail.com> wrote:

>
>
> On Sat, Apr 20, 2024, 5:25 PM Michal Charemza <mic...@charemza.name>
> 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)

Reply via email to