Creating and dropping partition in PostgreSQL "on the fly"?

Question

In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative partitioning for largest tables. Tables partitioned by forecast identifier. Partition creating and dropping is made "on the fly" when users create or delete forecasts. However, I doubt if creating partitions in this way is good idea.

EDITED. We do not use creating partition by BEFORE INSERT trigger. We create or drop section on backend after user start creating or deleting forecast on frontend of our web-application.

EDITED 2. Backend in our web-application is web-server, that works with PostgreSQL 12 database.

Answer 1

Usually it is the best thing to create partitions for the data you expect ahead of time and handle the error when unexpected data arrive.

There is also the option of a default partition that receives data that belongs into no other partition, but that is usually only a good idea if you have a fixed number of partitions, because conflicting data in the default partition prevents creation of a new partition.