Partitioning by datetime

[bjgiraudon]

Hi, I am currently running a database with ~60000 STAC Collections.

By default, partitioning in done by "collection", which has worked well in the past but is causing problems in this instance.

I quickly run way too many partitions and queries get super slow, pretty fast.
I wanted to edit partitioning, for a datetime-based approach, for example having a partition per week.
However, it seems like it's not working because pgstac still tries to create _items_XXXXX partitions when loading items into the DB.

So my question is : how can I update the database parttioning to match my needs ?

Thanks :)

[bitner]

@bjgiraudon Unfortunately, right now, hard coded partitioning by collections is very deeply ingrained in how pgstac works and the partition automation scripts. I do not see any reasonable way that we could remove that limitation without a lot of work. In order to have fully managed partition experience, we make the concession of losing some flexibility.

[bjgiraudon]

@bitner thanks for your answer.

Do you know how I could improve database transactions? My implementation is quite heavy on writing, not so much on reading. I have 60k+ collections. Right now, my workflows are stuck on write operations, presumably looking for partitions.