On Thu, Aug 23, 2012 at 6:37 AM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > In response to "Martin French" <Martin.French@xxxxxxxxxxxxx>: >> > >> > I have a table with 40 million rows and haven't had any performance >> > issues yet. >> > >> > Are there any rules of thumb as to when a table starts getting too big? >> > >> > For example, maybe if the index size is 6x the amount of ram, if the >> > table is 10% of total disk space, etc? >> >> My rule here is that a table is too big when performance starts degrading beyond an acceptable level. > > The challenge there is that if you wait until performance degrades > beyond an acceptable level, you've allowed yourself to get into a > situation where clients are upset and frustrated, and fixing the > problem is difficult because there's so much data to manipulate to > rearrange things. Yes, I agree with that. > And the advice I have along those lines is to establish now what > constitutes unacceptable performance, and put some sort of monitoring > and tracking in place to know what your performance degradation looks > like and predict when you'll have to react. For example, a MRTG > graph that runs an experimental query once a day during off hours and > graphs the time it takes vs. the # of rows in the table will prove > a valuable tool that can sometimes predict exactly when you'll have > to change things before it becomes a problem. This seems inconsistent with your previous advice. By the time your experimental query shows a problem, you no longer have any maintenance windows left large enough to fix it. Unless your experimental query was a reindex or something non-production like that, in which case running it on a production server, even off-hours, doesn't seem like a good idea. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
