"Nathan Boley" <npboley@gmail.com> writes:
> Because people can be smarter about the data partitioning.
>
> Consider a table of users. Some are active, most are not. The active
> users account for nearly all of the users table access, but I still
> (occasionally) want to access info about the inactive users.
> Partitioning users into active_users and inactive_users allows me to
> tell the database (indirectly) that the active users index should stay
> in memory, while the inactive users can relegated to disk.
(Someone's going to mumble something about partial indexes here.)
The 50,000 ft view of partitioning is it:
a) Lets the database do some work in query plan time instead of at run-time.
So yes, an index would let you skip scanning parts of the table but you
still have to do a few comparisons and page accesses on your index at
run-time. On a partitioned table you do that same work (and it's harder)
but at plan time.
b) Lets you partition based on a key which isn't indexed at all. Consider in
the above scenario if you then run a query across *all* active users. Even
partial indexes won't be very fast but a partitioned table can do a
sequential scan of a single partition.
c) Makes loading pre-organized segments of data and dropping segments O(1)
which is makes the data much more manageable.
It's really (c) which is the killer app for partitioned tables. (a) and (b)
are usually just nice side-shows.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!