Thread: mirroring table
Hi all..how can i mirroring table in postgresql? Let say i have a transaction table updated everyday, i want to mirror that table so that the other table only store the latest updated day records.. I hope you can understand my question.. Thanks in advance -- View this message in context: http://www.nabble.com/mirroring-table-tp18961792p18961792.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
searchelite wrote: > Hi all..how can i mirroring table in postgresql? > Let say i have a transaction table updated everyday, i want to mirror that > table so that the other table only store the latest updated day records.. > I hope you can understand my question.. It sounds like what you want is to have a table that contains a full history of records, plus another table that contains only the records from the first table that were inserted/updated today. If that is what you mean, there are several ways to do it, with different advantages and disadvantages. You can treat your smaller table as a materialized view, where you use triggers to update it and run a batch job (with cron or similar) every night to clear it. Another way is to make it a normal view, possibly with rules in place to make it updatable. You could use a partial index on the primary key that's restricted to tuples inserted/updated more recently than a given date to improve performance of the view. For that matter, you could even use table partitioning to do it, though this would involve a bit of manual (or scripted) maintenance. Anyway, this is all speculation if I've missed the point of your question. If I'm mistaken about what you meant, perhaps you could explain in a little more detail? -- Craig Ringer
Craig Ringer wrote: > > It sounds like what you want is to have a table that contains a full > history of records, plus another table that contains only the records > from the first table that were inserted/updated today. > > If that is what you mean, there are several ways to do it, with > different advantages and disadvantages. > > You can treat your smaller table as a materialized view, where you use > triggers to update it and run a batch job (with cron or similar) every > night to clear it. > > Another way is to make it a normal view, possibly with rules in place to > make it updatable. You could use a partial index on the primary key > that's restricted to tuples inserted/updated more recently than a given > date to improve performance of the view. > > For that matter, you could even use table partitioning to do it, though > this would involve a bit of manual (or scripted) maintenance. > > Anyway, this is all speculation if I've missed the point of your > question. If I'm mistaken about what you meant, perhaps you could > explain in a little more detail? > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > Thanks for the reply Craig..this is answered my question... thanks again -- View this message in context: http://www.nabble.com/mirroring-table-tp18961792p19119465.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.