Subject of the issue

When using the PostgreSQL exporter with the delete-task enabled, the pruning mechanism successfully removes old transactions from the txn table but fails to remove corresponding records from the txn_participation table.

Over time, this causes the txn_participation table to grow indefinitely, retaining orphaned references to transactions that have already been pruned. This leads to significant database bloat and undermines the storage-saving goal of the pruning configuration.

Your environment

• Conduit 1.9.0
• Algod 4.4.1-stable
• Network: mainnet
• Running on Kubernetes with a GCP Cloud SQL database

Steps to reproduce

1. Configure Conduit with the postgresql exporter. Enable delete-task with a specific round retention (e.g., rounds: 1000).
2. Let it run for a while, so that it prunes old data
3. Check the database:

-- Indexer is currently running, this is the expected heads minus rounds
select min(round) from txn;
   min
----------
 34463594
(1 line)

-- Old txn_participation data is still present
select min(round) from txn_participation;
  min
-------
 62440
(1 line)

Expected behaviour

Both txn and txn_participation tables shall be pruned

Actual behaviour

Only txn table is pruned, txn_participation grows past configured retention point.

Analysis

The Conduit PostgreSQL exporter relies on the DeleteTransactions function from the algorand/indexer library. This function currently only executes a delete against the txn table.

https://github.com/algorand/conduit/blob/master/conduit/plugins/exporters/postgresql/util/prune.go#L95

The DeleteTransactions only prunes the txn table, not txn_participation.

https://github.com/algorand/indexer/blob/main/idb/postgres/postgres.go#L2918

I'm not sure what you'd like to do - make a separate DeleteTransactionParticipations function or update DeleteTransactions to also prune txn_participation?