Re: using a postgres table as a multi-writer multi-updater queue - Mailing list pgsql-general

Thanks to George for the helpful comments.  My remarks are below.

"George Neuner" <gneuner2@comcast.net> wrote in message
news:gvad5bllba9slstdhkn6ql2jbplgd78p98@4ax.com...
> On Wed, 25 Nov 2015 23:39:39 -0500, "Steve Petrie, P.Eng."
> <apetrie@aspetrie.net> wrote:
>
>
>

>>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.
>
> Is there a logical problem with letting the sequence wrap around?
>

I can't think of any reason why letting the sequence wrap would be a logical
problem, but for operational simplicity I prefer to reset the sequence
number back to one, during each brief daily postgres server shutdown window.

I don't relish the challenge of mentally grappling with gigantic session row
sequence numbers, when I'm investigating some future operational anomaly.

>
> I'm not sure I understand the reason for "fuzzy" deletion.  There are
> a number of timestamps in your data ... is it not possible to delete
> deterministically based on one of them?
>

You are correct. The timestamps are there in the session row, and the PHP
app's session row deletion process does use them to select rows for
deletion. There is also a decrementing integer "hit count" limit column
(presently initialized == 25) that kills a session when its "budget" of HTTP
requests is used up.

I should have clarified, that I use the the term "fuzzy" to refer to the
probability mechanism, that hooks a small burst of session row deletion
activity, to each one of a randomly-selected portion (presently 1/5) of the
HTTP requests that cause a new session row to be INSERTed into the session
table.

This means that on average, only every 5th HTTP request that creates a new
session row, will also incur the session deletion workload. When the session
row deletion process occurs, its (aggressive) limit for deletion workload is
2X as many expired rows as needed on average, to keep up with the rate of
session row creation (so the 2X DELETE limit presently == 10 rows).

The idea is to make the process of DELETing expired session rows,
automatically scale its activity, to closely and aggressively match the rate
of session row creation. A heavy burst of new sessions being created will
work proportionately more aggressively to DELETE expired session rows. This
way, the (HTTP request-driven) PHP app will be self-tuning its own session
table space recycling. And there is no process (e.g. cron-driven),
external to the PHP app itself, that is doing session row deletion.

Based on what I've learned from this forum (but before I have studied
AUTOVACUUM in any detail) my thinking is to include an AUTOVACUUM command
(outside of any SQL transaction block) in the HTTP request-driven PHP app,
immediately following any time the PHP app completes a session row DELETE
command.

Or maybe the AUTOVACUUM request should occur less frequently?

>
> Hope this helps,
> George
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Steve Petrie, P.Eng.

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