Re: 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.
>
> I am migrating a web PHP application (called ITS-ETO) from mysql to
> postgres. The app INSERTs a row into a postgres table
> <eto_sql_tb_session_www> to manage each session with a web browser. Here is
> the DDL for the session table:
>
> CREATE TABLE its_eto.eto_sql_tb_session_www
> (
> session_www_code char(32) NOT NULL UNIQUE PRIMARY KEY,
>
> session_www_type int NOT NULL,
> session_www_state int NOT NULL,
> session_verify_code char(7) NOT NULL,
>
> session_www_serno SERIAL NOT NULL UNIQUE,
>
> session_target_serno int NULL,
> session_target_data varchar(1000) NULL,
>
> session_www_init_utc timestamp NOT NULL,
> session_www_last_utc timestamp NOT NULL,
> session_www_expiry_utc timestamp NOT NULL,
> session_www_delete_utc timestamp NOT NULL,
> session_www_hit_count int NOT NULL,
> session_www_act_seqno int NULL
>
> );
>
> CREATE INDEX ON its_eto.eto_sql_tb_session_www (session_www_serno);
>
> Using a "fuzzy" probability mechanism, some randomly-selected fraction of
> the HTTP requests that initiate a new session, also SELECT and DELETE
> expired rows from the session table. I naively assumed that the database
> server would automatically recycle the storage space dynamically released in
> this way.
>
> Now, I'm reading in this forum that in fact, postgres does not efficiently
> automatically recycle storage space released by row DELETion.

> My application is quite simple and will be supporting a modest workload,
> using a small amount of storage space, compared to the massive transaction
> rates and gigantic space usages, I'm reading about in this forum.

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. Just make sure your autovacuum settings are
at least as aggressive as the default settings.

>
> I do have the luxury of being able to shut down the application for a few
> minutes periodically e.g every 24 hours.
>
> 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.

Cheers,

Jeff