Are we configured correctly for storing files?

[kineticsquid]

Origin of this is https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/4157.

Our deployment Postgres instance has grown to 17G. Here's a summary of the table sizes:

table_name                      | pg_size_pretty | pg_relation_size
-----------------------------------------------------+----------------+------------------
file_resource                                       | 16 GB          |       6921715712
jobrunr_jobs                                        | 1711 MB        |       1048633344
extension_version                                   | 178 MB         |         90038272
migration_item                                      | 50 MB          |         36921344
azure_download_count_processed_item                 | 7056 kB        |          6193152
extension                                           | 10 MB          |          4415488
user_data                                           | 3784 kB        |          3366912
personal_access_token                               | 1464 kB        |           819200
persisted_log                                       | 456 kB         |           270336
namespace                                           | 688 kB         |           270336
namespace_membership                                | 576 kB         |           204800
jobrunr_recurring_jobs                              | 224 kB         |            98304
extension_review                                    | 256 kB         |            98304
spring_session_attributes                           | 3432 kB        |            49152
namespace_social_links                              | 72 kB          |            40960
jobrunr_metadata                                    | 120 kB         |            40960
admin_statistics_publishers_by_extensions_published | 48 kB          |            24576
spring_session                                      | 10 MB          |            16384
admin_statistics_top_most_downloaded_extensions     | 40 kB          |            16384
admin_statistics_top_namespace_extensions           | 8192 bytes     |             8192
admin_statistics_top_most_active_publishing_users   | 8192 bytes     |             8192
shedlock                                            | 56 kB          |             8192
admin_statistics_extensions_by_rating               | 8192 bytes     |             8192
admin_statistics_top_namespace_extension_versions   | 8192 bytes     |             8192
admin_statistics                                    | 40 kB          |             8192
signature_key_pair                                  | 48 kB          |             8192
jobrunr_backgroundjobservers                        | 912 kB         |             8192
jobrunr_migrations                                  | 24 kB          |             8192
flyway_schema_history                               | 48 kB          |             8192
jobrunr_jobs_stats                                  | 0 bytes        |                0

Given that we're storing files separately, the file_resource table seems excessively large. Are we configured correctly?

[kkistm]

I would also say that jobrunr_jobs looks too big. Is it a relevant information for the service? Should it be archived to a table in another scheme, for example, and archived there regularly?

[kineticsquid]

Results from a sample of access logs examining the use of /file API: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/4797.

[kineticsquid]

Next step, sample the file_resource table: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/4941

[amvanbaren]

Opened https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/5808 to check the current postgres disk space usage.