Re: Should I partition this table?

AlexK <alkuzo@gmail.com> wrote:

> For now, all the data fits in the cache: the box has 384GB of
> RAM. But I want to be ready for later, when we have more data. It
> is easier to refactor my table now, when it is still smallish.

Makes sense.

> Children are only added to recently added parents, and they are
> all added/updated/deleted at once. These child rows represent an
> object which changes as a whole.
>
> Parents are added over time at a steady pace, with increasing ID
> values. But we frequently read history as well as recent rows.
> Also we sometimes remove, always the parent and all its child
> rows.

That suggests to me that a partition based on ranges of parent IDs
would be optimal, with a CLUSTER of each partition as it reaches a
fairly stable state.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company