Re: Need guidance on partioning

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(dot)zeeshan(at)gmail(dot)com>
wrote:

> Hi Srinivasan
>
> You can use the following documentation to learn of PG Table Partitioning.
>
> https://www.postgresql.org/docs/current/ddl-partitioning.html
>
>
> On Wed, May 22, 2024 at 5:04 PM srinivasan s <srinioracledba7(at)gmail(dot)com>
> 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.
>>
>>
>>
>>
>>