Re: Partition Help

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