First rule of performance tuning: don't. See how well things run using
the simple plan you've drawn up. If performance is acceptable, you're
done.
Yes, you could keep the flag in a seperate table, but remember that
every row has a ~20 byte overhead, which is non-trivial. If you want to
go this route, I suggest keeping a table of queue entries yet to be
processed (can probably just be a table of ints). To see what you need
to process, join that table back to the main queue table. When you've
processed something, delete it's row from the 'side table'.
Another possibility is to have two tables; one is the active queue and
the other is historical. This is probably a better way to go so that
your queue process doesn't have to slog through months of data. After
you've processed an entry, just move it from the active table to the
history table.
On Tue, Nov 08, 2005 at 08:09:09AM -0800, shakahshakah@gmail.com wrote:
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461