Re: Table Partitioning and Rules

"Girish Bajaj" <gbajaj(at)tietronix(dot)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