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

The 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 created and expires instead of ttl where expires is created+ttl. Then I compute logical ttl as difference of those values. I think the application does not emit heavy queries on ttl alone.)

  • 1
    You need no subselect and can DELETE FROM t WHERE (a+b)=x; – Kondybas Apr 11 '19 at 7:35
  • 1
    @Kondybas Not if he wants the LIMIT – jjanes Apr 11 '19 at 12:03
  • @jjanes You are absolutely right, I've mixed MY and PG syntax. DELETE .. LIMIT look very inuitive and I can't get why that syntax isn't implemented in the PG – Kondybas Apr 11 '19 at 12:12
  • 1
    @Kondybas It isn't a syntax issue (that would be trivial), it is the semantics. Last time we tried, they couldn't get DELETE..LIMIT or UPDATE..LIMIT to play nicely with partitioning/inheritance. Hopefully we try again and get it done, as I think the absence is really a wart on PostgreSQL. – jjanes Apr 11 '19 at 18:21

I think refactoring the table to store the expiration would be a good idea. If you don't want to do that, then you can make an expression index on temporarydata ((created + ttl)).

It might take some encouragement to get it to use this index, however, as the statistics system might not provide good enough estimates for it naturally. Adding an ORDER BY to your subselect should provide this encouragement:

where (created + ttl) <= 1553755330 order by (created + ttl) limit 1000

(Plus, it just seems to make sense that you would want to delete the most overdue first. Indeed, I don't know why you want the LIMIT at all.)

  • The limit is there to remove just a small amount of lines at a time to spread the load from removing the lines over longer time span. I guess I could just remove all old lines in a single transaction if I could specify that this background transaction should be run on idle priority and should not trash caches if at all possible. – Mikko Rantalainen Apr 13 '19 at 8:40
  • If I run the delete often enough, there should probably never be a huge amount of records expiring at any one time (unless I have huge amounts all created simultaneously). If I've have fallen behind and need to do it this way to get out of the hole, that is one thing. But if I needed the LIMIT as a steady-state thing, I'd worry I was just papering over other problems. – jjanes May 15 '19 at 13:36
  • Creating artificially small transactions with LIMIT may also be required if the creation of new rows is very bursty and you want to reduce the transaction length used to delete stuff. Then you need to use small LIMIT per transaction and repeatedly start a new transaction until all old rows have been deleted. This will use more time overall but will reduce spiking in the system load and avoid creating long transactions. – Mikko Rantalainen May 17 '19 at 11:14

If you have used the same TTL for every record you can avoid functional indexing by simple moving the TTL to the right side of comparison (thanks to jjanes for correction):

  FROM temporarydata
                 FROM temporarydata
                WHERE created <= 1553755330 - ttl
                LIMIT 1000

Optimizer will calculate the difference only once and then will use it against the indexed created column.

If you need the different TTL for different records you can store not the created -- ttl pair but precalculated created -- expires one

And you can use functional indexing:

CREATE INDEX expires ON temporarydata ((created+ttl));
  • Different data has different ttl value so I need to do something per row. Does PostgreSQL automatically use the functional indexing without changing the original query? – Mikko Rantalainen Apr 11 '19 at 9:11
  • @MikkoRantalainen Correct. You can refer to the official documentation for details: postgrespro.com/docs/postgresql/11/indexes-expressional Func.indexing has pros and contras especially on mass inserts/updates. – Kondybas Apr 11 '19 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.