Thread: table size
I have a table in my database which can grow very quickly. Is there some way to partition the table so that when it reaches a certain size the information in it is copied to a temporary table and the original table is free again. Or is this possible at all. If not is there any sort of perfomance update I can add to speed up the queries on such a large table. thanks D
Hi David, I'd say that if it is a new app develop it with 7.4 and use statement level triggers otherwise you could use normal triggers and perform a count each time but that will slow things down dramatically. Other option is to use cron and write a daemon/script to periodically check the size of the table. Without statement level triggers this would probably be the most efficient. Rgds, Jason On Tue, 23 Sep 2003 07:35 pm, David McLoughlin wrote: > I have a table in my database which can grow very quickly. Is > there some way to partition the table so that when it reaches a certain > size the information in it is copied to a temporary table and the > original table is free again. > > Or is this possible at all. If not is there any sort of perfomance > update I can add to speed up the queries on such a large table. > > thanks > > D > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Hi, > I have a table in my database which can grow very quickly. Is > there some way to partition the table so that when it reaches a certain > size the > information in it is copied to a temporary table and the > original table is free again. You can create a view and update the view definition periodically to add/remove tables from view definition. Your data insertion should be pointed to a new table after specific period. That way you can achieve table partitioning. Since DDLs in postgresql are transactable, it should be absolutely transparent to the application and other users. HTH Shridhar
On Tue, Sep 23, 2003 at 10:35:57 +0100, David McLoughlin <dmcloughlin@sherkin.com> wrote: > I have a table in my database which can grow very quickly. Is > there some way to partition the table so that when it reaches a certain > size the information in it is copied to a temporary table and the > original table is free again. > > Or is this possible at all. If not is there any sort of perfomance > update I can add to speed up the queries on such a large table. You can use partial indexes to only index a subset of the data. This may be good enough for your needs. You will need to create new partial indexes periodicly (and maybe remove old ones to save space).