By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

table size

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #3

P: n/a
On Tue, Sep 23, 2003 at 10:35:57 +0100,
David McLoughlin <dm*********@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).

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.