Thread: Partition with check constraint with "like"

On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> I am trying to create partitions on the table based on first letter of the column record  value using inherit
relation& check constraint.
 

You'll get much better performance out of native partitioning than you
will with the old inheritance method of doing it.

>  EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS ('
||TG_TABLE_NAME||');';
 

This is a bad idea. There's a lock upgrade hazard here that could end
up causing deadlocks on INSERT.  You should just create all the tables
you need beforehand.

I'd recommend you do this using RANGE partitioning. For example:

create table mytable (a text not null) partition by range (a);
create table mytable_a partition of mytable for values from ('a') to
('b'); -- note the upper bound of the range is non-inclusive.
create table mytable_b partition of mytable for values from ('b') to ('c');
insert into mytable values('alpha'),('bravo');

explain select * from mytable where a = 'alpha';
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on mytable_a mytable  (cost=0.00..27.00 rows=7 width=32)
   Filter: (a = 'alpha'::text)
(2 rows)

The mytable_b is not scanned.

David

On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote:
>  Thank you. This is a great help. 
> But "a" have some records with alpha and numeric. 

So then you should make one or more partitions FROM ('1')TO('9').

> example :
> insert into mytable values('alpha'),('bravo');
> insert into mytable values('1lpha'),('2ravo');
> 
> 
>     On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley <dgrowleyml@gmail.com> wrote:  
>  
>  On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> > I am trying to create partitions on the table based on first letter of the column record  value using inherit
relation& check constraint.
 
> 
> You'll get much better performance out of native partitioning than you
> will with the old inheritance method of doing it.
> 
> >  EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS ('
||TG_TABLE_NAME||');';
 
> 
> This is a bad idea. There's a lock upgrade hazard here that could end
> up causing deadlocks on INSERT.  You should just create all the tables
> you need beforehand.
> 
> I'd recommend you do this using RANGE partitioning. For example:
> 
> create table mytable (a text not null) partition by range (a);
> create table mytable_a partition of mytable for values from ('a') to
> ('b'); -- note the upper bound of the range is non-inclusive.
> create table mytable_b partition of mytable for values from ('b') to ('c');
> insert into mytable values('alpha'),('bravo');
> 
> explain select * from mytable where a = 'alpha';
>                             QUERY PLAN
> -------------------------------------------------------------------
>  Seq Scan on mytable_a mytable  (cost=0.00..27.00 rows=7 width=32)
>   Filter: (a = 'alpha'::text)
> (2 rows)
> 
> The mytable_b is not scanned.

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
 chr
-----
 {
(1 row)


> same way for 9

postgres=# select chr(ascii('9')+1) ;
 chr
-----
 :
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

On Sat, 22 May 2021 at 04:38, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> I am trying to create partitions on the table which have around 2BIL records and users will always look for the
"name",its not possible to create a partition with a list, so we are trying to create a partition-based first letter of
thename column. name column has a combination of alpha numeric values. 

Going by the description of your use case, I think HASH partitioning
might be a better option for you. It'll certainly be less painful to
initially set up and maintain.

Here's an example:

create table mytable (a text) partition by hash(a);
create table mytable0 partition of mytable for values with(modulus 10,
remainder 0);
create table mytable1 partition of mytable for values with(modulus 10,
remainder 1);
create table mytable2 partition of mytable for values with(modulus 10,
remainder 2); --etc

Change the modulus to the number of partitions you want and ensure you
create a partition for each modulus. In this case, it would be 0 to 9.

David

On Sat, 22 May 2021 at 10:59, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> ERROR: empty range bound specified for partition "mytable_z" DETAIL: Specified lower bound ('Z') is greater than or
equalto upper bound ('['). SQL state: 42P17
 

It looks like '[' does not come after 'Z' in your collation.

David