Database cleanup

General
1

I have inherited an anchore installation that has been in place for a year, and the DB directory has grown to roughly 90GB in size. There is no archival storage defined and no option for archiving older scanning artifacts to object storage. Is there a guide available for manually deleting old artifacts from the db to clear storage.

2

Greetings!

We support configuring an object store and also provide a migration path, see the following:

We have options for configuring image archival as well (once you have configured an object store for analysis archive):

With or without an object store we have the ability to define an image lifecycle to expire data after certain criteria:

Make sure you have enable_data_egress set to true per the following:

If you can tell me what version you are on and what versions you were on previously (deployment_history table) I can provide some additional cleanup options.

Lastly, we, Anchore Support, are here for you so do not hesitate to reach out to us via anchore.zendesk.com!

1 Like
3

Currently we are running on 5.18.0. Will get the version history for you. I’ve updated the following settings in the docker compose file and redeployed:

  - ANCHORE_ENTERPRISE_REPORTS_ENABLE_DATA_EGRESS=true
  - ANCHORE_ENTERPRISE_REPORTS_DATA_EGRESS_WINDOW=0

for both the reports and reports_worker services. I previously had configured a lifecycle policy to delete images after 30 days:

[root@RH8-Harbor-Anchor-xfer anchore]# ./anchorectl system artifact-lifecycle-policy list
:check_mark: Fetched artifact-lifecycle-policies
Items:

  • action: delete
    createdAt: “2025-01-29T17:01:48Z”
    description: “”
    enabled: true
    name: Test
    policyConditions:
    • artifactType: image
      daysSinceAnalyzed: 30
      evenIfExistsInRuntimeInventory: true
      includeBaseImages: true
      version: 1
      updatedAt: “2025-01-29T17:01:48Z”
      uuid: 317a1344-306b-4b98-b074-3ef2e7cb1bc1
4

postgres=# select * from deployment_history;
upgrade_id | from_database_version | to_database_version | from_service_version | to_service_version | outcome | db_upgrade_duration | created_at
--------------------------------------±----------------------±--------------------±---------------------±-------------------±--------±--------------------±---------------------------
2af6efb9-cb76-44ca-9c2a-3837506eeb4c | | 560 | | 5.6.0 | success | 0 | 2024-06-20 22:13:28.489765
6153d019-fa4b-4ab0-9add-7af04831f288 | 560 | 570 | 5.6.0 | 5.7.0 | success | 4.940665245056152 | 2024-07-25 11:35:32.332782
4ebe6190-aafe-4a56-ae0d-85b2edcb2d1b | 570 | 580 | 5.7.0 | 5.8.1 | success | 0.7599983215332031 | 2024-08-22 17:32:41.701066
1c438ada-df36-4e99-a8fe-fedc67119c0b | 580 | 5100 | 5.8.1 | 5.10.0 | success | 0.8989436626434326 | 2024-10-08 15:15:50.952466
6711331e-9fa6-430a-8306-68a9d4ad69cb | 5100 | 5110 | 5.10.0 | 5.11.0 | success | 1.396428108215332 | 2024-11-04 19:20:42.032794
ec95b297-787b-4fd4-a0db-dcf4cad641ef | 5110 | 5120 | 5.11.0 | 5.12.0 | success | 0.9373011589050293 | 2024-12-05 20:33:35.432666
06ad8e0a-7900-49f9-a059-2dbbd8e273f9 | 5120 | 5130 | 5.12.0 | 5.13.1 | success | 0.9706704616546631 | 2025-01-30 19:11:33.486193
a7b066c0-5776-42bc-ae49-e598e242828b | 5130 | 5150 | 5.13.1 | 5.15.0 | success | 1.0685653686523438 | 2025-03-04 15:58:06.699248
b8b4cfeb-93fa-4020-8761-c626a6d67b27 | 5150 | 5170 | 5.15.0 | 5.17.0 | success | 1.4876534938812256 | 2025-05-07 14:14:38.809304
c6b1f271-62f8-42b1-89dc-86f52c8fca9c | 5170 | 5180 | 5.17.0 | 5.18.0 | success | 2.225947856903076 | 2025-06-03 16:18:05.087039
(10 rows)

postgres=#

5

If you are using our Kubernetes Inventory agent - given that you have been on versions prior than v5.11 and now you are v5.17 if the following tables have significant amounts of data in them you may (take a backup first) consider truncating them as they are no longer in use:
reports_kubernetes_containers, reports_kubernetes_namespaces, reports_kubernetes_nodes, reports_kubernetes_pods, reports_ecs_containers, reports_ecs_services, reports_ecs_tasks, reports_vulns_by_ecs_container, reports_vulns_by_kubernetes_container, reports_vulns_by_kubernetes_namespace

If you are using kubernetes inventory it may make sense to set evenIfExistsInRuntimeInventory: false in your lifecycle policy to keep images around that kubernetes inventory actively sees running in your environment.

Lastly… if you do not use the packages gate, verify trigger in policy then you may disable package DB load - Ref: Gate: packages | Anchore Enterprise Product Documentation

Package DB load can be disabled as follows (set on all services or just policyEngine):

extraEnv:
  - name: ANCHORE_POLICY_ENGINE_ENABLE_PACKAGE_DB_LOAD
    value: "false"

Once the setting has been configured the following table may be truncated (take a backup first):

TRUNCATE image_package_db_entries;

You can also look at the size of the image_package_db_entries table ahead of time to gauge the space savings. In addition to the space consumed if you frequently build and scan images with the same image tag it will create a lot of IO on the table via DELETE FROM then INSERT INTO.

Let me know if that helps!

6

The biggest user of space is object_store_documents

public | object_store_document_metadata | table | postgres | permanent | heap | 640 kB |
public | object_store_documents | table | postgres | permanent | heap | 72 GB |
public | policy_bundle | table | postgres | permanent | heap | 16 kB
|

Total size of the DB is 72 GB.

postgres-# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description
-----------±---------±---------±-----------±-----------±-----------±----------------±----------------------±--------±-----------±-------------------------------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | | 78 GB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +| 7313 kB | pg_default | unmodifiable empty database
| | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +| 7393 kB | pg_default | default template for new databases
| | | | | | | postgres=CTc/postgres | | |
(3 rows)

7

The object store & object store migration would greatly reduce the size of the object_store_documents table although the table would still be used (not nearly as much though):

Prior to executing the migration I would suggest ensuring you have a good backup just in case.

I encourage you to leverage our support portal via ZenDesk (https://support.anchore.com) for faster response times in the event you need assistance during this process and also in general for a more customized experience. In this way I am also able to get on a call with you if that is helpful.

8

postgres=# \c postgres
You are now connected to database “postgres” as user “postgres”.
postgres=# SELECT bucket, db_version, COUNT(*), SUM(size) FROM object_store_document_metadata GROUP BY bucket, db_version ORDER BY bucket;
bucket | db_version | count | sum
--------------------------------±-----------±------±----------
analysis_data | 5180 | 6 | 171687618
grypedb | 5180 | 2 | 527144447
image_content_data | 5180 | 6 | 60942603
manifest_data | 5180 | 6 | 20906
parent_manifest_data | 5180 | 5 | 20083
policy_bundles | 570 | 4 | 35716
policy_bundles | 580 | 3 | 20722
policy_bundles | 560 | 2 | 11778
policy-engine-evaluation-cache | 5180 | 6 | 600380
policy_evaluations | 5120 | 24 | 668622
policy_evaluations | 5110 | 17 | 597226
policy_evaluations | 5150 | 18 | 607180
policy_evaluations | 5130 | 38 | 1187504
policy_evaluations | 5180 | 23 | 1034436
policy_evaluations | 5100 | 6 | 181044
policy_evaluations | 5170 | 14 | 676209
policy_evaluations | 570 | 24 | 561203
policy_evaluations | 580 | 58 | 1449777
syft_sbom | 5180 | 6 | 91111619
(19 rows)

postgres=#