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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
