Обсуждение: Indexes that would span partitions.

What's a good strategy for dealing with indexes (multiple) that would
span multiple partitions of a table.

For example:

Let's  say I have a table that's partitioned by group_id because I
want all the records for a group to be in the same partition. Let's
say I have a field called "expires_at" which is a date. Let's presume
the expiry date can be updated.

If I want to do something to every record that is due to be expired
the database is going to be touching practically every partition and
this seems like it could present a problem.

Is there a more efficient way to deal with something like this?

> How many partitions do you expect to have? Why are you partitioning on that column? Do you have need to drop old data
allat the same time? How many rows in each partition or in total do you expect to have?
 

In my case there is a hierarchy so the partitions are based on certain
parts of the hierarchy.  For example.

There is an item 1.2.3.4.5.6.7.8  The partition is based on 1.2  (a
function based hash scheme).   I could fine tune the number of
partitions simply by writing my function.

Each item has certain attributes that I want to index. For example all
items have an expiry_date.  I will be archiving the items when they
expire but of course all those items will not be in the same
partition.  In fact it's highly probable all partitions will have to
be visited in order to accomplish this task.

Unfortunately I will not be able to drop an entire partition and I
can't partition by expiry date because access is most often by the
item number. Also of course the expiry date can change over the
lifetime of the item.