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

From Jeff Janes
Subject Re: using a postgres table as a multi-writer multi-updater queue
Date
Msg-id CAMkU=1wwwRCvgSRaG887SbGTA15=gQA5pigei+4gYU5ErQQrtw@mail.gmail.com
Whole thread Raw
In response to Re: using a postgres table as a multi-writer multi-updater queue  ("Steve Petrie, P.Eng." <apetrie@aspetrie.net>)
List pgsql-general
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.
>
> 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


pgsql-general by date:

Previous
From: "Steve Petrie, P.Eng."
Date:
Subject: Re: using a postgres table as a multi-writer multi-updater queue
Next
From: Francisco Olarte
Date:
Subject: Re: Convert from hex to string