From: Kevin Kempter <cs_dba@consistentstate.com>
To: pgsql-performance@postgresql.org
Subject: partitioning performance question
Date: Sat, 09 Jun 2012 11:58:44 -0600
Message-ID: <4FD38ED4.3020003@consistentstate.com> (raw)
Hi All;
We have a client that has a table where large blobs (bytea) are stored.
the table has a key column that is numbers (like 112362) but
unfortunately it's a varchar column so the blobs are accessed via
queries like:
select * from bigtable where keycol = '217765'
The primary reason we want to partition the table is for maintenance,
the table is well over 1.2 Terabytes in size and they have never
successfully vacuumed it. However I don't want to make performance even
worse. The table does have a serial key, I'm thinking the best options
will be to partition by range on the serial key, or maybe via the keycol
character column via using an in statement on the check constraints,
thus allowing the planner to actually leverage the above sql. I suspect
doing a typecast to integer in the check constraints will prove to be a
bad idea if the keycol column remains a varchar.
Thoughts?
Here's the table:
Table "problemchild"
Column | Type |
Modifiers
-----------+--------------------------+--------------------------------------------------------------------
keycol | character varying |
blob_data | bytea |
removed_date | timestamp with time zone |
alt_key | bigint | not null default
nextval('problemchild_alt_key_seq'::regclass)
Indexes:
"pc_pkey" PRIMARY KEY, btree (alt_key)
"key2" btree (keycol)
Thanks in advance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cs_dba@consistentstate.com
Subject: Re: partitioning performance question
In-Reply-To: <4FD38ED4.3020003@consistentstate.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox