From: | "shakahshakah(at)gmail(dot)com" <shakahshakah(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | newbie design question re impact of VACUUM |
Date: | 2005-11-08 16:09:09 |
Message-ID: | 1131466149.590544.159870@g43g2000cwa.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
After looking at "Chapter 22. Routine Database Maintenance Tasks"
(http://www.postgresql.org/docs/8.1/interactive/maintenance.html) I
started wondering about what (if any) consideration to give to to
VACUUM issues in the following context.
As a background, I'll be using Postgres in part as a processing queue
for a 40-column stream of information (~ 250 bytes/row) with a
sustained input rate of 20 rows/sec. This queue will be processed
periodically (every few minutes), design constraints are to (1) only
process each row once, and (2) keep the processed rows around for a
period of time (say a month or so).
My first (naive?) idea was to add a boolean "was_processed" column to
the table (defaulted to false) and UPDATE it to true as part of (1).
After reading Chapter 22, though, it seems that even a minor UPDATE
like that copies the row and requires VACUUMing. Given that, my basic
question is whether row width is a consideration in UPDATE or VACUUM
performance, and if so if it is generally accepted practice to design
around it? For example, if I were to make a child table to effectively
hold the "was_processed" flag I could probably avoid UPDATEs entirely,
but I'm not sure how to value that in this context.
Thanks in advance for and help/info/pointers.
From | Date | Subject | |
---|---|---|---|
Next Message | codeWarrior | 2005-11-08 16:14:43 | Re: odbc in postgresql and php |
Previous Message | Tom Lane | 2005-11-08 16:07:05 | Re: Beyond the 1600 columns limit on windows |