Thread: Partition Help

Hi,

I am trying to setup partitions and as a test, I was able to follow the
example in the Postgres docs using the date as a condition.

Now I am trying to partition on a column with the data type character
varying. I want to partition based on an invoice ID consisting on letters
and numbers like I-10, I-11. I create my function:

IF ('invoice' <= 'I-10000') THEN INSERT INTO myschema.mywork VALUES (NEW.*)
ELSE IF ('invoice' >= 'I-10001' AND <= 'I-20000') THEN INSERT INTO
myschema.mywork VALUES (NEW.*)

ELSE RAISE EXCEPTION...

Is it possible to perform this type of operation with a mix of letters and
numbers? So far any attempts to insert based on these conditions raises the
exception.

Thanks.



--
View this message in context: http://postgresql.nabble.com/Partition-Help-tp5847235.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 4/29/15 10:05 AM, akshunj wrote:
> IF ('invoice' <= 'I-10000') THEN INSERT INTO myschema.mywork VALUES (NEW.*)
> ELSE IF ('invoice' >= 'I-10001' AND <= 'I-20000'

That's going to fall apart with invoice I-100000.

If you're going to go this route, depend on how IF ELSIF operates and
don't try to use closed-ended operations:

IF invoice <= 'I-10000' THEN
ELSIF invoice <= 'I-20000' THEN
ELSIF invoice <== 'I-30000' THEN
ELSE ...
END IF;

That's still going to surprise you when you start getting 6 digit
invoice numbers but at least it'll do something sane and not drop your
data on the floor.

All that said, I suspect you're over-thinking this. Partitions with
10000 invoices are almost certainly way too small. Really, unless you're
talking 100M rows or more, or certain other usage patterns, it's
unlikely that partitioning is going to help you.

For reference, I've run systems that had pretty bad data design and
*horrible* abuse by the application, doing peak workloads > 10,000TPS.
That's on a 3TB database where the largest rowcount was over 100M.
Nothing was partitioned. Granted, it was running on servers with 512GB
of RAM, but those aren't exactly insanely expensive.

In other words, always remember the first rule of performance
optimization: don't. :)
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Hi Jim,

I'm not going the partitioning route because I want to, I'm just out of
options at this point. As this table gets bigger, performance just gets
worse over time. I wanted to try partitioning to see if it helps.

Thanks for the tip, looking at the function again what you suggest makes
perfect sense.



--
View this message in context: http://postgresql.nabble.com/Re-Partition-Help-tp5847286p5847361.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Melvin, thanks. Syntax was indeed the problem there. Any idea how to pass the
original query to the child table? My insert has 113 parameters passed in,
but based on the constraint violations I am seeing, it seems they are not
making. I suspect that: VALUE (NEW.*) does not pass in the original query,
only the column I used for the comparison?



--
View this message in context: http://postgresql.nabble.com/Re-Partition-Help-tp5847286p5847360.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.