On 3/5/24 13:47, Marc Millas wrote: > Salut Kimaidou, > why not a partitioned table with the department a partitioning Key ? > each year just detach the obsolete data, department by > department (ie.detach the partition, almost instantaneous) and drop or keep > the obsolete data. > No delete, quite easy to maintain. For each global index, Postgres will > create one index per each partition. and detach them when you detach a > department partition. > so when importing, first create an appropriate table, load the data, and > attach it to the main partitioned table. Postgres will > automatically recreate all necessary indexes. > Yes, a table partitioned like this is certainly a valid option - and it's much better than the view with a UNION of all the per-department tables. The optimizer has very little insight into the view, which limits how it can optimize queries. For example if the query has a condition like WHERE department = 'X' with the declarative partitioning the planner can eliminate all other partitions (and just ignore them), while with the view it will have to scan all of them. But is partitioning a good choice? Who knows - it makes some operations simpler (e.g. you can detach/drop a partition instead of deleting the rows), but it also makes other operations less efficient. For example a query that can't eliminate partitions has to do more stuff during execution. So to answer this we'd need to know how often stuff like bulk deletes / reloads happen, what queries will be executed, and so on. Both options (non-partitioned and partitioned table) are valid, but you have to try. Also, partitioned table may not support / allow some features - for example unique keys that don't contain the partition key. We're improving this in every release, but there will always be a gap. I personally would start with non-partitioned table, because that's the simplest option. And once I get a better idea how often the reloads happen, I'd consider if that's something worth the extra complexity of partitioning the data. If it happens only occasionally (a couple times a year), it probably is not. You'll just delete the data and reuse the space for new data. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
