Skip to content

SSH Key Expiration Notification Emails Timing Out

Problem

Despite attempts to resolve database timeouts when emailing SaaS users about soon-to-be expired SSH keys, we are still seeing timeouts when the cron job is triggered.

Execution time isn't ideal, but well within timeout limits for a background job when run via database-lab.

Database Lab Output

explain SELECT "keys"."id" FROM "keys" WHERE (date(expires_at AT TIME ZONE 'UTC') BETWEEN '2020-01-01' AND CURRENT_DATE AND expiry_notification_delivered_at IS NULL) ORDER BY "keys"."id" ASC LIMIT 1000;
 Limit  (cost=34727.18..34729.68 rows=1000 width=4) (actual time=1007.379..1007.725 rows=1000 loops=1)
   Buffers: shared hit=2627 read=34292 dirtied=64
   I/O Timings: read=913.101 write=0.000
   ->  Sort  (cost=34727.18..34783.99 rows=22722 width=4) (actual time=1007.376..1007.593 rows=1000 loops=1)
         Sort Key: keys.id
         Sort Method: top-N heapsort  Memory: 111kB
         Buffers: shared hit=2627 read=34292 dirtied=64
         I/O Timings: read=913.101 write=0.000
         ->  Index Scan using index_keys_on_expires_at_and_expiry_notification_undelivered on public.keys  (cost=0.43..33481.36 rows=22722 width=4) (actual time=0.333..984.231 rows=36872 loops=1)
               Index Cond: ((date(timezone('UTC'::text, keys.expires_at)) >= '2020-01-01'::date) AND (date(timezone('UTC'::text, keys.expires_at)) <= CURRENT_DATE))
               Buffers: shared hit=2624 read=34292 dirtied=64
               I/O Timings: read=913.101 write=0.000

Time: 1.009 s  
  - planning: 1.381 ms  
  - execution: 1.008 s  
    - I/O read: 913.101 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 2627 (~20.50 MiB) from the buffer pool  
  - reads: 34292 (~267.90 MiB) from the OS file cache, including disk I/O  
  - dirtied: 64 (~512.00 KiB)  
  - writes: 0