Обсуждение: 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).