PostgreSQL Size increasing despite retention flag

[raag-bitwave]

Description

CloudSQL PostgreSQL database seems to be increasing every day despite having retention setup, pls find the helm chart config below:

We also have importer.config.hedera.mirror.importer.startDate set up of value 2024-07-15T00:00:00.000000000Z

importer:
  config:
    hedera:
      mirror:
        importer:
          downloader:
            cloudProvider: GCP
          parser:
            record:
              entity:
                notify:
                  enabled: true
                redis:
                  enabled: false
          retention:
            enabled: true
            batchPeriod: 6h
            frequency: 6h
            period: 14d

If I check the logs for RetentionJob I can see it's running fine but takes 16 hours to complete... I checked last 7 days and all retention jobs take anywhere from 10 to 17 hours to complete.

2024-08-07T12:58:51.262Z  INFO scheduling-2 c.h.m.i.r.RetentionJob Finished pruning tables in 16.99 h: {assessed_custom_fee=563, contract_action=0, contract_log=68968, contract_result=80970, contract_state_change=0, contract_transaction=175291, contract_transaction_hash=80970, crypto_allowance_history=59, crypto_transfer=517019831, custom_fee_history=0, entity_history=4055, entity_stake_history=0, entity_transaction=0, ethereum_transaction=944, live_hash=0, network_freeze=0, network_stake=0, nft_allowance_history=0, nft_history=1043, node_stake=0, prng=16, reconciliation_job=0, record_file=38599, sidecar_file=5851, staking_reward_transfer=3720, token_account_history=295, token_allowance_history=1639, token_history=0, token_transfer=96034, topic_message=129082762, topic_message_lookup=0, transaction=129318745, transaction_signature=79} 

The node is deployed on a GCP kubernetes cluster with node pool having instance type n1-standard-4 and the cloudSQL database is using db-custom-4-15360 instance

Steps to reproduce

Added helm chart value above for importer and details for the instance types.

Additional context

Here is a screenshot of last 7 day metrics of the database.

Hedera network

mainnet

Version

0.109.0

Operating system

Linux

[steven-sheehy]

Retention job is known to be slow and there's not really a way to optimize it with the current schema. Deletes are just extremely slow in PostgreSQL without the use of partitions.

Even if you delete, it doesn't mean that your cloud provider will shrink the storage. Google Cloud SQL does not reduce your volume size after deleting data. There are ways to reclaim it though. If you're saying the database itself and not just the volume is reporting a large size then maybe you need to vacuum some tables manually.

[raag-bitwave]

Does the mirror node auto vacuum on a frequency?

[xin-hedera]

Does the mirror node auto vacuum on a frequency?

For some frequently utilized tables they are configured to auto vacuum more frequently than the default.

Note auto vacuum will not free up disk space. Only full vacuum does so.

[raag-bitwave]

So the main concern is that the database used bytes size is increasing despite the retention job set to only keep 14 days worth of data. I could update the auto vacuum manually on some of these tables to run more frequently but would that help?

Is there anything else I could try to prevent the database from increasing too much every day?

[xin-hedera]

This is our current autovacuum setting for some tables: https://github.com/hashgraph/hedera-mirror-node/blob/main/hedera-mirror-importer/src/main/resources/db/migration/v1/R__autovacuum_insert_only_tables.sql

Yes, you can try to vacuum them, especially, crypto_transfer, topic_message, and transaction, more frequently. Combined with retention, hope that can slow down the database size growth.