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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
