Re: Partitioning options

Hi, Justin. The example link has self-contained DDL to create the partitions (in flat vs composite mode for comparison) and then making the FK's on each showing the marked speed difference for the same net number of partitions (1200 flat vs 80x15 = 1200 composite):

Re: Partitioning options

On Sun, Feb 18, 2024 at 5:20 PM Alec Lazarescu wrote: > "Would probably look at a nested partitioning" > > I'm not the original poster, but I have a schema with nested > (composite) partitions and I do run into some significant > inefficiencies compared to flat partitions in various schema

Re: Partitioning options

"Would probably look at a nested partitioning" I'm not the original poster, but I have a schema with nested (composite) partitions and I do run into some significant inefficiencies compared to flat partitions in various schema metadata operations (queries to get the list of tables, creating

Re: Partitioning options

Hi Marc, Nested partitioning still allows for simple data deletion by dropping the table that falls in that date range. Probably thinking of partitioning by multicolomn rules which is very complex to set up On Fri, Feb 9, 2024, 10:29 AM Marc Millas wrote: > > > > On Thu, Feb 8, 2024 at 10:25 

Re: Partitioning options

On Thu, Feb 8, 2024 at 10:25 PM Justin wrote: > Hi Sud, > > Would not look at HASH partitioning as it is very expensive to add or > subtract the number of partitions. > > Would probably look at a nested partitioning using customer ID using > range or list of IDs then by transaction date, Its

Re: Partitioning options

Hi Sud, Would not look at HASH partitioning as it is very expensive to add or subtract the number of partitions. Would probably look at a nested partitioning using customer ID using range or list of IDs then by transaction date, Its easy to add partitions and balance the partitions segments.

Re: Partitioning options

> > Out of curiosity, As OP mentioned that there will be Joins and also > filters on column Customer_id column , so why don't you think that > subpartition by customer_id will be a good option? I understand List > subpartition may not be an option considering the new customer_ids gets > added

Re: Partitioning options

On 2/8/24 1:43 PM, veem v wrote: On Thu, 8 Feb 2024 at 20:08, Greg Sabino Mullane > wrote: Should we go for simple daily range partitioning on the transaction_date column? This one gets my vote. That and some good indexes. Hello Greg,

Re: Partitioning options

On Thu, 8 Feb 2024 at 20:08, Greg Sabino Mullane wrote: > On Thu, Feb 8, 2024 at 12:42 AM sud wrote: > ... > >> The key transaction table is going to have ~450 Million transactions per >> day and the data querying/filtering will always happen based on the >> "transaction date" column. >> > ...

Re: Partitioning options

On Thu, Feb 8, 2024 at 12:42 AM sud wrote: ... > The key transaction table is going to have ~450 Million transactions per > day and the data querying/filtering will always happen based on the > "transaction date" column. > ... > Should we go for simple daily range partitioning on the

Partitioning options

Hi , We have a system which stores customers' transactions. There are a total of ~100K customers currently and the list will increase in future but not drastically though(maybe ~50K more or so). The number of transactions per day is ~400million. and we want to persist them in our postgres database