We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background process that removes old rows from the table. The background process removes up to 1000 lines during one transaction:
delete from temporarydata where id in ( select id from temporarydata where (created + ttl) <= 1553755330 limit 1000 )
1553755330 in the example is current number of seconds since UNIX epoch and
created contains seconds since UNIX epoch the data was added and the
ttl contains the number seconds the data should be kept alive.
Technically this does work but there are around 2M lines in the temporary data and the subselect gets pretty slow because the sum requires doing sequential scan over the table to find all matching rows. This causes extra background load on the database.
> explain (analyze,verbose,timing,buffers) select id from temporarydata where (created + ttl) <= 1553755330 limit 1000 Limit (cost=0.00..402.34 rows=1000 width=16) (actual time=6735.811..6735.811 rows=0 loops=1) Output: id Buffers: shared hit=3068 read=230500 -> Seq Scan on public.temporarydata (cost=0.00..262980.99 rows=653622 width=16) (actual time=6735.809..6735.809 rows=0 loops=1) Output: id Filter: ((temporarydata.created + temporarydata.ttl) <= 1553755330) Rows Removed by Filter: 1916405 Buffers: shared hit=3068 read=230500 Planning time: 0.402 ms Execution time: 6735.849 ms
I'd prefer just adding a new index that can always contain the sum of
created + ttl that PostgreSQL were able to use for this query automatically. Is this possible with high performance?
(I'm considering rewriting the application code to save
expires instead of
ttl. Then I compute logical
ttl as difference of those values. I think the application does not emit heavy queries on