On Thu, Dec 20, 2012 at 4:39 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Charles Gomes <charlesrg@xxxxxxxxxxx> writes: >> Using rules would be totally bad as I'm partitioning daily and after one year having 365 lines of IF won't be fun to maintain. > > You should probably rethink that plan anyway. The existing support for > partitioning is not meant to support hundreds of partitions; you're > going to be bleeding performance in a lot of places if you insist on > doing that. A couple of points: 1: In my experience hundreds is OK performance wise, but as you approach thousands you fall off a cliff, and performance is terrible. So at the 3 to 4 year mark daily partition tables will definitely be having problems. 2: A good way around this is to have partitions for the last x days, last x weeks or months before that, and x years going however far back. This keeps the number of partitions low. Just dump the oldest day into a weekly partition, til the next week starts, then dump the oldest week into monthly etc. As long as you have lower traffic times of day or enough bandwidth it works pretty well. Or you can just use daily partitions til things start going boom and fix it all at a later date. It's probably better to be proactive tho. 3: Someone above mentioned rules being faster than triggers. In my experience they're WAY slower than triggers but maybe that was just on the older pg versions (8.3 and lower) we were doing this on. I'd be interested in seeing some benchmarks if rules have gotten faster or I was just doing it wrong. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
