Re: Partition with check constraint with "like"

just out of curiosity,
what would a typical query be ?

select * from t1 where name = somename ? == equality match // if yes,
hash partitioning may be helpful to a have reasonably balanced distribution
or
select * from t1 where name like 'some%'; ---- what would be the
distribution of rows for such queries. i mean it can return 1 row or all
rows or anything in between.

that may result in unbalanced partitioning.

then why partition at all ? 2B rows, if i go with 100KB size per row. that
would be around 200GB.

also, queries may benefit from trigram matching.
Index Columns for `LIKE` in PostgreSQL | Niall Burkley's Developer Blog
<https://niallburkley.com/blog/index-columns-for-like-in-postgres/>

<https://niallburkley.com/blog/index-columns-for-like-in-postgres/>

On Fri, 21 May 2021 at 22:08, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:

> Hi David,
>
> Hi,
>
> 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 the name column. name column has a
> combination of alpha numeric values.
>
>
>
> > postgres=# select chr(ascii('z')+1) ;
> > chr
> > -----
> > {
> > (1 row)
>
> I tried as below, I'm able to create a partition table for 'Z', but it's
> not identifying partition table.
>
>
> postgres=# select chr(ascii('Z')+1) ;
> chr
> -----
> [
> (1 row)
>
> create table mytable_z of mytable for values from ('Z') to ('Z[');
> CREATE TABLE
>
> insert into mytable values(4,'ZAR83NB');
>
> ERROR: no partition of relation "mytable" found for row DETAIL: Partition
> key of the failing row contains (name) = (ZAR83NB). SQL state: 23514
>
>
>
>
>
> On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <
> dgrowleyml(at)gmail(dot)com> wrote:
>
>
> On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)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
>
>
>

--
Thanks,
Vijay
Mumbai, India