Hi: On Wed, Apr 30, 2014 at 7:40 PM, Elanchezhiyan Elango <elanelango@xxxxxxxxx> wrote: > Francisco, > Thanks for the partitioning idea. I used to have the tables partitioned. But > now that I have moved to a schema where data is split across about ~90 > tables I have moved away from partitioning. But it's something I have to > reconsider at least for the high traffic tables. I've discovered paritioning lends itself to some nifty tricks on high traffic tables, specially when data is partitioned by a timestamp key correlated with insertion times ( in our case these are CDRs ). Aside from the easiness of dropping the old partitions and not having to vaccum them, I've been testing something with a couple of tricks. Like dropping indexes on old partitions ( which are no longer queried frequently and whose queries normally always get a full table scan ), or moving no longer updates partitions to a different archived schema ( they still get queried, inheritance takes care of this, but this unclutters my table listings and lets me do a backup optimization. I instruct pg_dump to dump the non-updating schema only whenever I change it and all but the updating one more frequently ( these is for pg_dump backups, for which I keep a rotating series to protect again pilot / software errors and to restore them in auxiliary servers fr testing / data mining, for disk failure we have a separate replication / log archiving setup ). These division also aided me when I had to do a 9.1-9.3 updgrade, as we only needed to dump/restore the updating schemas in the downtime window and then redid the archived one after it. Of course I need a full set of scripts to maintiain this, and if you decide to make one of this you'll need an even bigger one. Also, what puzzles me is your insert/update/delete pattern of access. When I've found this I normally have just insert / delete. It seems like you are doing cumulative hourly stats but updating them in place. If this is your case I've found myself doing it ( daily stats updated in place, to have the current day view growing on 'real' time ) and switched to an schema where I inserted into an aux table, which was queried using sum()/avg(), and added and moved the totals once the day was done to another table. It was easier on vacuums, as the daily table just needed a daily vaccum after the big delete, and not even a full one, as the space was going to be reused ( this was before partitioning, and I used a view to query a union of the totals table plus an agregated query of the incremental one, it worked quite well ) Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
