Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Large tables proxy_package_download and organization_package_download #690

Open
martinvonwittich opened this issue Dec 3, 2024 · 0 comments

Comments

@martinvonwittich
Copy link

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:

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant