Re: Need guidance on partioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks Kashif, I posted the message after I went through the official documentation and some other blogs. Seeking guidance from any experts who already implemented Similar things.

On Wed, May 22, 2024 at 5:49 PM Kashif Zeeshan <kashi.zeeshan@xxxxxxxxx> wrote:
Hi Srinivasan

You can use the following documentation to learn of PG Table Partitioning. 



On Wed, May 22, 2024 at 5:04 PM srinivasan s <srinioracledba7@xxxxxxxxx> wrote:
Hello everyone,

I hope you are all doing well.

I am seeking guidance on how to implement partitioning in PostgreSQL.

We have a large table that currently does not have any partitioning, and we have two requirements for removing old data from this table. We are looking to create a new table with partitioning.

1. The first requirement is to delete all records from the table that are older than 18 months. I believe we can achieve this by using range partitioning on the timestamp column.
RANGE Partition is a good option here. 

2. The second requirement is to remove data from the table when a user leaves the organization. We have the account ID and user ID in the same table.

Could someone please offer guidance on selecting the appropriate partitioning method (range, sub-partition, or composite)?
It all depends on your data structure, please go through the documentation and then analyze your table/data structure to find the appropriate method. 

Additionally, not all queries use the timestamp column in the WHERE condition. Is it mandatory to use the partition key in the WHERE condition to benefit from partitioning? Can we create a composite index that combines the partition key column with other columns used in the WHERE clause? Would this be beneficial?
Following is the limitaiton
 To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

Thank you.





[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux