High Memory usage when the net._http_response table is too big #216
Description
Problem
When having:
postgres=# \dt+ net.*
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------------+-------+----------------+-------------+---------------+---------+-------------
net | _http_response | table | supabase_admin | unlogged | heap | 1104 MB |
net | http_request_queue | table | supabase_admin | unlogged | heap | 464 kB |
(2 rows)
postgres=# select count(*) from net._http_response;
count
--------
442627
(1 row)
The pg_net worker consumes a lot of memory:
ps -e -o pid,ppid,cmd,%mem,%cpu --sort=-%mem | grep postgres
948 787 postgres: main: pg_net 0.13 55.0 0.5
The pg_net.ttl is respected and working. This is likely because the query we execute:
WITH\
rows AS (\
SELECT ctid\
FROM net._http_response\
WHERE created < now() - $1\
ORDER BY created\
LIMIT $2\
)\
DELETE FROM net._http_response r\
USING rows WHERE r.ctid = rows.ctid",Uses an ORDER BY which is not indexed.
This happens on pg_net 0.13.0.
Solution
Reproduce the problem and check if an index on _http_response.created solves it.