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...@yahoo.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 < > dgrowle...@gmail.com> wrote: > > > On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj...@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 > > > -- Thanks, Vijay Mumbai, India
