Large tables proxy_package_download and organization_package_download

[martinvonwittich]

In our repman instance, there are two large tables proxy_package_download and organization_package_download, which consume a lot of disk space and increase the size of our daily database dumps:

Details

repman=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 5;
                 relation                  |  size   
-------------------------------------------+---------
 public.proxy_package_download             | 1999 MB
 public.organization_package_download      | 475 MB
 public.organization_package_download_pkey | 83 MB
 public.proxy_package_idx                  | 82 MB
 public.proxy_download_date_idx            | 80 MB
(5 Zeilen)

Apparently these tables are effectively log files for package downloads?

repman=# select package, date, version, user_agent from proxy_package_download order by date desc limit 10 ;
           package           |        date         |  version   |                                      user_agent                                       
-----------------------------+---------------------+------------+---------------------------------------------------------------------------------------
 symfony/yaml                | 2024-12-03 11:27:32 | 6.4.13.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 symfony/web-profiler-bundle | 2024-12-03 11:27:32 | 6.4.14.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 symfony/stopwatch           | 2024-12-03 11:27:32 | 6.4.13.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 symfony/monolog-bundle      | 2024-12-03 11:27:32 | 3.10.0.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 symfony/monolog-bridge      | 2024-12-03 11:27:32 | 6.4.13.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 monolog/monolog             | 2024-12-03 11:27:32 | 3.8.0.0    | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 symfony/dotenv              | 2024-12-03 11:27:32 | 6.4.13.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 symfony/debug-bundle        | 2024-12-03 11:27:32 | 6.4.13.0   | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 roave/security-advisories   | 2024-12-03 11:27:32 | dev-latest | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 nyholm/psr7                 | 2024-12-03 11:27:32 | 1.8.2.0    | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
(10 Zeilen)

repman=# select id, package_id, date, version, user_agent from organization_package_download order by date desc limit 10;
                  id                  |              package_id              |    date    | version  |                                      user_agent                                       
--------------------------------------+--------------------------------------+------------+----------+---------------------------------------------------------------------------------------
 827b956e-c017-456e-9094-cb4b510a4b5b | 60998b00-8bac-45fb-895b-9325d010da30 | 2024-12-03 | 1.1.0.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 432b7937-6caf-4784-94a1-dd65fab4b9f0 | 2684e219-cd52-4f09-b064-8612b1a4a6fe | 2024-12-03 | 1.11.0.0 | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 0ddd9f5f-550a-4841-9778-600e617b8527 | 9dc2684f-46b9-4ac4-b9e2-bfb6e081599f | 2024-12-03 | 2.3.1.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 bff77e55-49d8-4fba-8aa6-f7090d2d1900 | 1cd838a8-d9d4-45af-9617-9051412221c6 | 2024-12-03 | 0.1.4.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 86668daf-a6d4-4ff3-97f8-e6c2d35c0fcb | 05ce0112-1a26-4383-be67-c52b9117c570 | 2024-12-03 | 2.1.0.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 7d979ac2-f3d2-454a-9546-72d338e15a8f | c16745f0-07b2-45c7-b982-2eb0cc759071 | 2024-12-03 | 2.8.1.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 18b87e1a-bed4-42b8-b3e7-b0a3ecc1d92f | 71cfd87d-e8ab-4f55-9d05-c9a93d913429 | 2024-12-03 | 1.0.0.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 dba8f726-9541-4ab5-a392-6ef9112e166f | 180256e9-fa32-4376-9dc2-6064ea49fb73 | 2024-12-03 | 1.1.0.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 27d30310-631a-441d-9a10-4f68817f144f | 7d2b38a8-12f9-43b7-b930-b7ff44f53c35 | 2024-12-03 | 0.1.3.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
 59633fb0-9602-4ef3-93a8-e55abd7b096f | 72264010-1dfa-417f-8988-acbea867207f | 2024-12-03 | 1.0.0.0  | Composer/2.7.7 (Linux; 5.10.0-33-amd64; PHP 8.2.26; cURL 7.74.0; Platform-PHP 8.2.10)
(10 Zeilen)

Assuming that these aren't necessary for operation, is there a way to disable this logging, or a way to delete old entries from these tables? Would it be safe to manually delete old records directly in the database, e.g. with delete from from proxy_package_download where date < now() - '1 year'::interval?