Re: Partitioning of a dependent table not based on date

On 12/1/2014 11:14 AM, Herouth Maoz wrote:
> I am currently in the process of creating a huge archive database that
> contains data from all of our systems, going back for almost a decade.
>
> Most of the tables fall into one of two categories:
>
> 1. Static tables, which are rarely updated, such as lookup tables or
> user lists. I don't intend to partition these, I'll just refresh them
> periodically from production.
> 2. Transaction tables, that have a timestamp field, for which I have the
> data archived in COPY format by month. Of course a monolithic table over
> a decade is not feasible, so I am partitioning these by month.
>
> (I don't mean "transaction" in the database sense, but in the sense that
> the data represents historical activity, e.g. message sent, file
> downloaded etc.)
>
> I have one table, though, that doesn't fall into this pattern. It's a
> many-to-one table relating to one of the transaction tables. So on one
> hand, it doesn't have a time stamp field, and on the other hand, it has
> accumulated lots of data over the last decade so I can't keep it
> unpartitioned.
>

Lets stop here. One big table with lots of rows (and a good index)
isn't a problem. As long as you are not table scanning everything,
there isn't a reason to partition the table.

Lots and lots of rows isnt a problem except for a few usage patterns:
1) delete from bigtable where (some huge percent of the rows)
2) select * from bigtable where (lots and lots of table scanning and
cant really index)

If your index is selective enough, you'll be fine.

-Andy