Re: using a postgres table as a multi-writer multi-updater queue

Thanks to Jeff for the helpful response. My remarks are below.

----- Original Message -----
From: "Jeff Janes" <jeff.janes@gmail.com>
To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>
Cc: "Tim Uckun" <timuckun@gmail.com>; "Merlin Moncure" <mmoncure@gmail.com>;
"John R Pierce" <pierce@hogranch.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>
Sent: Thursday, November 26, 2015 2:07 AM
Subject: Re: [GENERAL] using a postgres table as a multi-writer
multi-updater queue


> On Wed, Nov 25, 2015 at 8:39 PM, Steve Petrie, P.Eng.
> <apetrie@aspetrie.net> wrote:
>>
>>> You don't ever want to delete from such a table so you need to set up
>>> something which allows you to truncate the tables when you no longer
>>> need
>>> them.
...
> Truncation is far more efficient than deletion + vacuuming.  If you
> are running on the edge of your hardware's capabilities, this
> efficiency is important.  But if you are not on the edge, then it is
> not worth worrying about.

This is what I was hoping to learn -- because I doubt my app workload will
ever approach the edge of hardware capability.

> Just make sure your autovacuum settings are
> at least as aggressive as the default settings.
>

I'll keep that in mind. And by a happy coincidence, in another recent forum
thread (26 Nov 2015, subject "Re: [GENERAL] Re: [webmaster] How to
commit/vacuum a batch of delete statements in a postgresql function") there
is advice from Adrian Klaver to about the need to execute VACUUM outside of
a transaction block.

>> My plan was always, to avoid eventual exhaustion of the SERIAL sequence
>> number integer value series, by swapping in during the periodic app
>> shutdown, a freshly truncated postgres <eto_sql_tb_session_www> table.
>
> I'd make the serial column and bigserial, and then forget about it.

I considered using bigint, but decided against doing so for three reasons.
1. int rep is already going to be way more precision than is needed, 2.
avoid the extra resource consumption incurred by bigint as compared to int,
and 3. keep strictly 32-bit compatible with my 32-bit Win XP / PHP
development system. In fact the PHP app is programmed to terminate
abnormally on detecting a postgres SERIAL sequence number that exceeds the
maximum positive value of a strictly 32-bit signed integer.

>
> Cheers,
>
> Jeff

Steve Petrie, P.Eng.

ITS-ETO Consortium
Oakville, Ontario, Canada
apetrie@aspetrie.net