On 06/26/2014 05:31 PM, Tim Uckun wrote:
1. If there is a very large set of data in the table that needs to be moved this will be slow and might throw locks which would impact the performance of the inserts and the updates.
Well, the locks would only affect the rows being moved. If this is primarily insert-based, the only performance hit would be from the extra IO. If you move records frequently enough, these small batches should not be a problem.
Though I strongly suggest you build a tier library that creates a partition for every day/week/month so the target movement tables themselves don't get overly large.
2. Constantly deleting large chunks of data might cause vacuum problems.
This goes back to PostgreSQL and MVCC. If your interval moves roughly the same amount of data each time, the table will no longer grow past that point. In the movement function, just have it run a manual vacuum/analyze which is not restricted by autovacuum rules. It'll use more IO, but will finish faster and check growth more consistently.
I wrote a tier library I'm working on getting permission to open source that does exactly this. It supports tier granularity up to 1 day, and will age out old data into the partitions beyond that threshold. You could probably tool something similar to be more aggressive if you want to keep that 15-minute (or shorter) expiration interval.
I haven't used it myself, but pg_partman might have what you need: http://pgxn.org/dist/pg_partman/doc/pg_partman.html -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
