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

On Wed, 25 Nov 2015 23:39:39 -0500, "Steve Petrie, P.Eng."
<apetrie(at)aspetrie(dot)net> wrote:

>Now, I'm reading in this forum that in fact, postgres does not efficiently
>automatically recycle storage space released by row DELETion.

Yes and no.

Space resulting from deleted rows is not given back to the OS unless
you perform a full vacuum. Autovacuum compacts the table, squeezing
out empty rows and leaving free space at the end for new insertions.

If the table is heavily used, you will reuse the free space quickly
anyway. The problem you may run into is needing to autovacuum too
often to control the size of the table.

Space from truncated or dropped tables is immediately given back to
the OS.

The issue with heavy deletion usually is performance. Deleting scans
the rows and fires any relevant triggers ... truncating or dropping
the table does not.

>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?

>So my question to this postgres forum is -- should I just remove from
>the online app the "fuzzy" probability mechanism, that DELETEs expired
>rows from the session table -- because the postgres server is not going
>to dynamically recycle the released storage space anyway?

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?

Hope this helps,
George