Re: Partitioning and unique key

On Tue, 2024-09-03 at 10:39 +0530, veem v wrote: > As you rightly said "they will make it more difficult to detach a partition." > , > we are really seeing a longer time when detaching parent table partitions. > It runs forever sometimes. So do you mean it's because we have primary key > defined t

Re: Partitioning and unique key

On Tue, 3 Sept 2024 at 01:14, Laurenz Albe wrote: > > You can keep the primary key defined on both columns if it is good enough > for you. > But it will give you lower guarantees of uniqueness: with that primary > key, there could > be two rows with a different timestamp, but the same "txn_id", a

Re: Partitioning and unique key

On Mon, 2024-09-02 at 21:39 +0530, veem v wrote: > On Mon, 2 Sept 2024 at 19:13, Laurenz Albe wrote: > > On Sun, 2024-09-01 at 01:32 +0530, veem v wrote: > > > due to postgres limitations we are unable to have this unique constraint > > > or primary key > > > only on the transaction_id column, we

Re: Partitioning and unique key

On Mon, 2 Sept 2024 at 19:13, Laurenz Albe wrote: > On Sun, 2024-09-01 at 01:32 +0530, veem v wrote: > > due to postgres limitations we are unable to have this unique constraint > or primary key > > only on the transaction_id column, we have to include > transaction_timestamp with it as > > a com

Re: Partitioning and unique key

On Sun, 2024-09-01 at 01:32 +0530, veem v wrote: > due to postgres limitations we are unable to have this unique constraint or > primary key > only on the transaction_id column, we have to include transaction_timestamp > with it as > a composite key. So I want to understand from experts if there

Re: Partitioning and unique key

On Sun, 1 Sept 2024 at 11:38, veem v wrote: > > On Sun, 1 Sept 2024 at 10:03, veem v wrote: > >> >> On Sun, 1 Sept 2024 at 09:13, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Saturday, August 31, 2024, veem v wrote: >>> iii)And then alter the datatype of th

Re: Partitioning and unique key

On Sun, 1 Sept 2024 at 10:03, veem v wrote: > > On Sun, 1 Sept 2024 at 09:13, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Saturday, August 31, 2024, veem v wrote: >> >>> >>> >>> iii)And then alter the datatype of the partition key transaction_date to >>> DATE in one shot at

Re: Partitioning and unique key

On Sun, 1 Sept 2024 at 09:13, David G. Johnston wrote: > On Saturday, August 31, 2024, veem v wrote: > >> >> >> iii)And then alter the datatype of the partition key transaction_date to >> DATE in one shot at the table level(which should be fast as its having more >> granularity as compare to exi

Re: Partitioning and unique key

On Saturday, August 31, 2024, veem v wrote: > > 1) if it's technically possible to have a unique key on only the > transaction_id column having the partition key on the > transaction_timestamp, because the table is going to be queried/purged > based on the transaction_timestamp? > There is prese

Re: Partitioning and unique key

On Sun, 1 Sept 2024 at 03:58, Adrian Klaver wrote: > > The model is at odds with itself and untenable. If the tables hold > multiple rows for a given transaction_id then you cannot have a > PK/Unique constraint on that column. Seems there is a decided lack of > any planning. The only way I can s

Re: Partitioning and unique key

On 8/31/24 13:02, veem v wrote: Hello, We have our transaction tables daily range partitioned based on transaction_timestamp column which is timestamptz data type and these are having composite primary key on (transaction_id, transaction_timestamp). And we were using an "insert on conflict" f

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

> > I have a table that is capturing what is, basically, time series data Time series data usually is concerned with "recent" data, and has a subsequent drop off date. This is ideal for partitioning by timestamp - not only do your queries only need to hit a few of the total tables, but you can si

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): https://www.postgresql.org/messa

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 metada

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Sun, Feb 18, 2024 at 10:32 PM Darryl Green wrote: > 1) Is my attempt to improve performance of insert AND (as the number of > sources goes up, as it has) querying which is invariably by id but it is > easy to include "and src = x" in the majority of usage (the other common > case is to query a

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 22:07, Darryl Green wrote: > > On Mon, 19 Feb 2024 at 14:23, David Rowley wrote: > > > > On Mon, 19 Feb 2024 at 16:32, Darryl Green wrote: > > > 2) It would be nice to be able to specify the id as pk on the table being > > > partitioned (as it was in the non-partitioned d

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Monday, February 19, 2024, David G. Johnston wrote: > On Monday, February 19, 2024, Darryl Green wrote: > >> >> > It may be possible to still have it work by doing a speculative record >> > in the index for the target table then go and check all of the other >> > indexes before marking the sp

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Monday, February 19, 2024, Darryl Green wrote: > > > It may be possible to still have it work by doing a speculative record > > in the index for the target table then go and check all of the other > > indexes before marking the speculative entry as valid. > > It is always valid - except in the

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 17:31, Peter Eisentraut wrote: > > On 19.02.24 04:32, Darryl Green wrote: > > I note that in Postgresql 16 identity column handling in partitioned > > tables has been aligned to the view that the partitioned table as a > > whole is a single relation (and so a unique identity

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 14:23, David Rowley wrote: > > On Mon, 19 Feb 2024 at 16:32, Darryl Green wrote: > > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partit

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

> > 1) Is my attempt to improve performance of insert AND (as the number of > sources goes up, as it has) querying which is invariably by id but it is > easy to include "and src = x" in the majority of usage (the other common > case is to query across all sources, asking for a set is unusual) a > r

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On 19.02.24 04:32, Darryl Green wrote: I note that in Postgresql 16 identity column handling in partitioned tables has been aligned to the view that the partitioned table as a whole is a single relation (and so a unique identity across partitions). This makes sense. The change that I think yo

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 16:32, Darryl Green wrote: > 2) It would be nice to be able to specify the id as pk on the table being > partitioned (as it was in the non-partitioned definition of the table) once > to document and enforce that the partitions simply inherit the id pk. This > would seem o

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Sunday, February 18, 2024, Darryl Green wrote: > > I note that in Postgresql 16 identity column handling in partitioned > tables has been aligned to the view that the partitioned table as a whole > is a single relation (and so a unique identity across partitions). This > makes sense. > Where

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 foreig

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 e

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 slowl

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, Ou

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 transaction

Re: partitioning

On Tue, 24 Oct 2023 at 10:39, Torsten Förtsch wrote: > Then I added this constraint to the small table: > > ALTER TABLE original_small_table > ADD CONSTRAINT partition_boundaries > CHECK((false, '-infinity')<=(is_sold, purchase_time) > AND (is_sold, purchase_time)<(false, 'infinity')) > NOT VA

Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 21:07, Michael Lewis wrote: > On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> ... use binary split for large partitions, to avoid large row movements. >> > > Would you expound on this? > if we have range partitions based on

Re: Partitioning a table by integer value (preferably in place)

On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > ... use binary split for large partitions, to avoid large row movements. > Would you expound on this?

Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 14:13, Pól Ua Laoínecháin wrote: > Thanks again for your questions - they gave me pause for thought and I > will try to apply them in future partitioning scenarios. (Unfortunatly > :-) ) there is no magic number of partitions for, say, a given size of > table - otherwise it

Re: Partitioning a table by integer value (preferably in place)

Dear Ron, > > Just a quick follow-up - could I script the creation of 1000 > > partitions using bash > Sure. That's what scripting languages are for. Thank you so much for your helpful and expansive answer. No wonder everyone talks about how friendly and welcoming the PostgreSQL community is!

Re: Partitioning a table by integer value (preferably in place)

Hi Vijay, and thanks for replying, >> I have a 400GB joining table (one SMALLINT and the other INTEGER - >> Primary Keys on other tables) with 1000 fields on one side and 10M on >> the other, so 10,000M (or 10Bn) records all told. > My queries: >> results with the existing setup? Does it look

Re: Partitioning a table by integer value (preferably in place)

Hi all - I resolved my issue - and took a 25 minute query down to 5 seconds. > I have a 400GB joining table (one SMALLINT and the other INTEGER - > What I would like to do is to partition by the SMALLINT (1 - 1000) > value - which would give 1,000 tables of 400MB each. I found this site very help

Re: Partitioning a table by integer value (preferably in place)

> > > > I have a 400GB joining table (one SMALLINT and the other INTEGER - > Primary Keys on other tables) with 1000 fields on one side and 10M on > the other, so 10,000M (or 10Bn) records all told. > My queries: > > Do you have any explain analyze,buffers results with the existing setup? Does

Re: Partitioning a table by integer value (preferably in place)

On 8/9/21 9:14 AM, Pól Ua Laoínecháin wrote: Hi again all, Just a quick follow-up - could I script the creation of 1000 partitions using bash Sure.  That's what scripting languages are for. or PL/pgSQL? I think (rightly/wrongly?) that this may be the best solution? I've found samples on t

Re: Partitioning a table by integer value (preferably in place)

Hi again all, Just a quick follow-up - could I script the creation of 1000 partitions using bash or PL/pgSQL? I think (rightly/wrongly?) that this may be the best solution? I've found samples on the web, but they are for partitioning by date - a quick sample by integer would be gratefully receive

Re: concurrent re-partitioning of declarative partitioned tables

On Wed, 2 Dec 2020 at 15:59, Michael Lewis wrote: > You can not have overlapping partitions that are both attached. > Not directly, no. That's why I'm considering the _partition_channel hack. Why do you want to merge partitions that you are "done with" instead of > just leaving them partitioned

Re: concurrent re-partitioning of declarative partitioned tables

On Wed, 2 Dec 2020 at 16:07, David G. Johnston wrote: > On Mon, Nov 30, 2020 at 8:36 AM Nick Cleaton wrote: > >> I want to set up a large table on postgresql 12.4, using declarative >> partitioning to partition by record creation date. I'd like to have recent >> records in small partitions but o

Re: concurrent re-partitioning of declarative partitioned tables

On Mon, Nov 30, 2020 at 8:36 AM Nick Cleaton wrote: > I want to set up a large table on postgresql 12.4, using declarative > partitioning to partition by record creation date. I'd like to have recent > records in small partitions but old records in a few larger partitions, so > I want merges. The

Re: concurrent re-partitioning of declarative partitioned tables

You can not have overlapping partitions that are both attached. Why do you want to merge partitions that you are "done with" instead of just leaving them partitioned by day? Why are you partitioning at all? Are you confident that you need partitions for performance & that the trade-offs are worth

concurrent re-partitioning of declarative partitioned tables

I want to set up a large table on postgresql 12.4, using declarative partitioning to partition by record creation date. I'd like to have recent records in small partitions but old records in a few larger partitions, so I want merges. The merges should be concurrent, in the sense that they lock out

Re: Partitioning large table (140GB)

You can also use extensions like pg_partman or pg_pathman. The last one allows to partition tables smoothly on live environments. > On 20. Nov 2019, at 11:49, Ravi Krishna wrote: > >> In our production, we use postgres 9.5 with streaming replication >> using repmgr, there is a large table of 1

Re: Partitioning large table (140GB)

>In our production, we use postgres 9.5 with streaming replication >using repmgr, there is a large table of 140GB size which receives >lots of inserts, >Is it possible to partition this table in this version of postgres? >and if so, please, can someone let me know the best way to accomplish >thi

Re: Partitioning an existing table - pg10.6

I have not personally used this, but the write-up seems solid to minimize downtime to help you to shift data gradually. Be sure you understand the limitations of partitioning, particularly when you are still on 10x not yet on v11 where updates will shift a row to a new partition if the partition ke

Re: Partitioning an existing table - pg10.6

Hello, Trying to do what I suggested, I understood it doesn't work ;o( Logical replication can only work between two distinct databases, and it seems that the replicated table name is the same as its source ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-

Re: Partitioning an existing table - pg10.6

Hello, I didn’t test it myself but maybe using logical réplication could help ... See https://www.postgresql.org/docs/10/logical-replication.html Operations - create parttable - Feed it using réplication - when sync : stop app, stop réplication, rename tables - maybe you can réplicate from part

Re: Partitioning an existing table - pg10.6

On 3/7/19 10:01 π.μ., Ayub M wrote: Hello, I am using postgres 10.6 and have a huge table with ~30m recs which needs to be partitioned. Whats the best strategy for it with minimal downtime? 1. Rename existing table and create new partitioned tables with orig name and keep inserting data from t

Re: Partitioning with range types

On 06/15/2018 09:59 AM, Jeremy Finzel wrote: On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 06/15/2018 08:26 AM, Jeremy Finzel wrote: Several months ago we had some detailed discussions about whether to use separate date colu

Re: Partitioning with range types

On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver wrote: > On 06/15/2018 08:26 AM, Jeremy Finzel wrote: > >> Several months ago we had some detailed discussions about whether to use >> separate date columns to indicate a date range, or to use the daterange >> data type. We opted for the latter bec

Re: Partitioning with range types

On 06/15/2018 08:26 AM, Jeremy Finzel wrote: Several months ago we had some detailed discussions about whether to use separate date columns to indicate a date range, or to use the daterange data type.  We opted for the latter because this type is specifically designed for this use case - a tabl