Greetings, I need to keep various information related to a parent entity (the Electronic Health Record for a single patient) and its children (partitions of the EHR, since it can get very big). It is important that I know how many partitions exist for an EHR, and I'd like to avoid a query that'll count the partitions (which'll probably go to hundreds of millions) with a parent id as criteria. Other than count, there is also significant info such as some metadata for the last added partition which must be accessed along with the count.
Can I simply adopt the naive approach of updating an EHR metadata table within a transaction in every partition addition/deletion operation? (update/decrease count, write last added partition's metadata to this table or remove it etc) Any hidden nasty surprises you can see? A better way of doing this?