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

On 11/29/2015 01:59 AM, Steve Petrie, P.Eng. wrote:
> Thanks to Jeff for the helpful response. My remarks are below.
>
> ----- Original Message ----- From: "Jeff Janes" <jeff(dot)janes(at)gmail(dot)com>
> To: "Steve Petrie, P.Eng." <apetrie(at)aspetrie(dot)net>
> Cc: "Tim Uckun" <timuckun(at)gmail(dot)com>; "Merlin Moncure"
> <mmoncure(at)gmail(dot)com>;
> "John R Pierce" <pierce(at)hogranch(dot)com>; "PostgreSQL General"
> <pgsql-general(at)postgresql(dot)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(at)aspetrie(dot)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.

Just to be clear my reference was to someone running VACUUM manually.
Jeff's reference was to the autovacuum daemon which runs VACUUM and
ANALYZE as a background processes:

http://www.postgresql.org/docs/9.4/interactive/routine-vacuuming.html#AUTOVACUUM

>
>>> 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(at)aspetrie(dot)net
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com