EclipseFdn
Are we configured correctly for storing files?
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?
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?
Results from a sample of access logs examining the use of /file API: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/4797.
Next step, sample the file_resource table: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/4941
Opened https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/issues/5808 to check the current postgres disk space usage.
