Data Retention Strategy for Production Formance Deployment

[EyalAlphaEG]

Hi all,
I'm looking for guidance on implementing data retention for a production Formance deployment.
While I understand Formance's immutable ledger design and audit trail requirements, the underlying PostgreSQL storage will eventually face scalability challenges without proper data management.
My proposed approach:
CDC to BigQuery (or other scalable DB): Stream all events via CDC (Change Data Capture) to BigQuery for long-term analytics and compliance
PostgreSQL Partitioning: Implement partitioning on transaction tables by timestamp
Cold Storage: Move partitions older than 6 months to cheaper storage tiers and delete from Postgres

Key questions:
Is it safe to delete old transactions after archiving to BigQuery, or does this break Formance's internal consistency (hash chains, balance calculations)?
Built-in features: Does Formance have any built-in data retention/archival features or recommended approaches?

I haven't found documentation or discussions on production data retention strategies.
If there are established best practices or features I'm missing, I'd appreciate any pointers.
Using Formance Ledger v2.3.0 with PostgreSQL backend.
Thanks!

[formance-support[bot]]

Hello,
We do not recommend deleting data from the Ledger as this will break the consistency and balance calculations, etc. (as you mentioned).
However, you can completely offload all your "complex" read queries to BigQuery to take advantage of its power for this type of query.

Is there a specific reason for wanting to delete data from PostgreSQL after 6 months?

[aalloiteau]

Hi @EyalAlphaEG ,

Here are few elements to help you in your data retention strategy:

• CDC and Data Warehouse Integration

Starting with version 2.3.0, Formance introduces streaming capabilities that allow exporting logs to third-party systems such as ClickHouse or Elasticsearch. This enables running cross-ledger analytics and heavy processing queries outside of the live PostgreSQL instance, reducing load on the operational database.

We’ve also supported setups using Aurora DB replication to feed external tools in CDC mode, or continuous data exports to S3.
Notably, Amazon Aurora guarantees atomicity and consistency at the storage layer — meaning replicas always reflect fully committed transactions only, ensuring consistent downstream data for analytics or archival.

• Scaling Strategy

By default, all ledgers are created within the same bucket (_default). However, you can assign a different bucket when creating a new ledger.
Using multiple buckets allows you to distribute data across separate PostgreSQL schemas, preventing a single schema from growing excessively large and becoming a performance bottleneck.
Technically, a bucket in Formance maps directly to a PostgreSQL schema — a namespace that groups related database objects (tables, views, indexes, and functions). You can think of it as a logical “folder” within a single database, helping organize and scale ledger data.

• Segmentation and Cold Storage

For horizontal scalability, segmentation is the recommended approach. One common pattern is to segment ledgers by time period (e.g., creating a separate ledger per year).
To maintain continuous balance processing without gaps, you would migrate ending balances from the previous ledger to the new one. This effectively starts a new immutable log, ensuring clean separation between time-segmented ledgers.
Note that once segmented, historical data remains accessible only within the old ledgers — queries against the new ledger will not include past transactions.

[EyalAlphaEG]

Hi,
Thank you for the detailed response.
The suggestion to segment ledgers by time period (e.g., annually) and migrate the ending balances is an excellent approach. We plan to adopt this strategy for our data retention implementation.
Regarding CDC integration, I wanted to add a quick note for users on GCP. Google Cloud offers a built-in service (Database Migration Service / Datastream) that handles CDC from Cloud SQL for PostgreSQL directly to BigQuery. This can be configured at the database level, complementing the streaming features you mentioned without requiring specific setup within Formance itself.
Finally, I would strongly recommend adding a chapter on data retention management to the official documentation. As highlighted, this is a crucial consideration for large-scale production deployments, and the time-based segmentation strategy you outlined is a practical and robust solution that would benefit many users.
Thanks again for the guidance.

[aalloiteau]

Thanks for the feedback. We'll make sure to add some guidelines on it.
While we are consolidating official documentation, you can also rely on our knowledge base for further information on the product.