Postgres Partition by Character Prefix
<p>Good Day,</p>

<p>I would like to check what the best way is to partition a Postgres table on a columns prefix. I have a large table (+-<strike>300</strike>  750 Million rows x 10 columns) and I would like to partition it on a prefix of column 1. 
Data looks like:</p>

<pre><code>ABCDEF1xxxxxxxx
ABCDEF1xxxxxxxy
ABCDEF1xxxxxxxz
ABCDEF2xxxxxxxx
ABCDEF2xxxxxxxy
ABCDEF2xxxxxxxz
ABCDEF3xxxxxxxx
ABCDEF3xxxxxxxz
ABCDEF4xxxxxxxx
ABCDEF4xxxxxxxy
</code></pre>

<p>Their will only ever by 10 partitions i.e. ABCDEF0...->ABCDEF9...</p>

<p>What I've currently done is make tables like:</p>

<pre><code>CREATE TABLE public.mydata_ABCDEF1 (
CHECK ( col1 like 'ABCDEF1%' )
) INHERITS (public.mydata);

CREATE TABLE public.mydata_ABCDEF2 (
CHECK ( col1 like 'ABCDEF2%' )
) INHERITS (public.mydata);
</code></pre>

<p>etc. Then the trigger with similar logic:</p>

<pre><code>IF ( NEW.col1 like 'ABCDEF1%' ) THEN 
    INSERT INTO public.mydata_ABCDEF1 VALUES (NEW.*);
ELSIF ( NEW.imsi like 'ABCDEF2%' ) THEN
    INSERT INTO public.simdata_ABCDEF2 VALUES (NEW.*);
</code></pre>

<p>I'm concerned if partitioning in this way will speed up query time? or if I should consider partitioning on substr (not sure how), or if I should make a new column with the prefix and partition on that column?</p>

<p>Any advise is appreciated.</p>