From: Greg Stark <gsstark@mit.edu>
To: Girish Bajaj <gbajaj@tietronix.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Partitioning and Rules
Date: 18 Jul 2003 02:15:28 -0400
Message-ID: <87he5kjr7j.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
"Girish Bajaj" <gbajaj@tietronix.com> writes:
> I cant possibly index all the cols in the table. So I thought Id best manage
> the data by splitting up the table into multiple partitions and eventually
> depending on application logic, only scan those tables that are necessary to
> scan sequentially instead of the whole big table.
But that's only going to help if one of the columns they're searching on is
the last name column isn't it?
I'm a fan of partitioned tables but you have to consider what advantage you're
trying to achieve to understand if it will actually be helpful for you:
Partitioned tables only really improve query performance if virtually all
queries use a common constraint. The canonical example is accounting tables
being partitioned based on fiscal year. Virtually all the queries--even ones
doing massive batch queries best served by sequential scans--will only scan
the current fiscal year.
In your case unless you can impose a constraint on the UI that users always
perform their queries on a single letter of the alphabet at a time and only
see results for people whose last names match that letter, it's not really a
great match as far as query performance.
The other advantage of partitioned tables is space management; it allows
placing each partition on a separate physical storage space. However without
native support in Postgres doing it via rules is going to be a headache. I
would think you would be better off striping the disks together and storing it
as a single large table. That's the only clean approach Postgres really allows
at this point anyways.
Finally, if I WAS going to partition based on the first letter of a text
string, which I doubt I would, I would probably create 26 partitions right off
the bat. Not try to make up arbitrary break points. If those arbitrary
breakpoints turn out to be poorly chosen it'll be a complex manual job to move
them. Whereas if you just have 26 partitions some will be large and some small
and you can move partitions between physical storage freely to balance things.
--
greg
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-sql@postgresql.org
Cc: gsstark@mit.edu, gbajaj@tietronix.com
Subject: Re: Table Partitioning and Rules
In-Reply-To: <87he5kjr7j.fsf@stark.dyndns.tv>
* 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