Query to find the total number of active versus archived contracts

Questions
,
1

What SQL query can I run on the Canton database to find out the total number of contracts (across all parties) that are active versus archived? We need to find this for some performance testing and want to avoid having to stream all contracts into the Java app as different parties to count the total number of contracts.

2 
p1=# select count(*) from ledger_api.participant_events_create;
 count
-------
     7
(1 row)

p1=# select count(*) from ledger_api.participant_events_consuming_exercise;
 count
-------
     2
(1 row)

Above is on the participant database, called p1 here. 7 is the total number of create events, 2 the total number or consuming exercises, which means there are currently 7-2 = 5 active contracts.

Note that pruning will remove events and the schemas are not a public API so these queries are not guaranteed to work in the future. But they do work for Daml 2.6.

1 Like
3

Thanks @bernhard.

We looked into the possibility of using the following alternate query to directly give us the number of active contracts:

select count(*) from active_contracts

And the following for total contracts:

select count(*) from contracts

And hence the number of archived contracts could be computed by subtracting the first from the second?