device_inbox never gets emptied

[ara4n]

it's up to 85M rows or so on matrix.org, which seems insane. similarly *_stream never seem to get purged, or device_federation_inbox/outbox

[richvdh]

The main problem is that it's hard to tell when we can delete to-device messages. They don't have an expiry time on them.

[ukcb]

I made a small test script for private use with postgresql. Let's see if it works. :-)

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use DateTime;

my $db_name = 'synapse';
my $db_host = '127.0.0.1';
my $db_port = '5432';
my $db_user = 'synapse';
my $db_pass = 'synapse';

my $time_to_keep_in_days = 7;

my $table_device_inbox = 'device_inbox';
my $table_time_indexer = 'device_inbox_timestamp_indexer';


my $datetime        = DateTime->today;
my $timestamp_today = $datetime->epoch;
$datetime->subtract( days => $time_to_keep_in_days );
my $timestamp_obsolete = $datetime->epoch;


my $dbh = DBI->connect( "dbi:Pg:dbname=$db_name;host=$db_host;port=$db_port",
    $db_user, $db_pass,
    { AutoCommit => 1, RaiseError => 1, PrintError => 1, PrintWarn => 0 } )
  or die $DBI::errstr;

$dbh->do("CREATE TABLE IF NOT EXISTS $table_time_indexer
            ( stream_id BIGINT,
              timestamp BIGINT,
              PRIMARY KEY (stream_id)
            )") or die $dbh->errstr;

my $sth = $dbh->prepare("SELECT stream_id FROM $table_device_inbox")
  or warn $dbh->errstr;
$sth->execute() or warn $dbh->errstr;

my $pool = {};
while ( my $ref = $sth->fetchrow_hashref() ) {
    $pool->{ $ref->{'stream_id'} } = 1;
}

$sth = $dbh->prepare("SELECT stream_id, timestamp FROM $table_time_indexer")
  or warn $dbh->errstr;
$sth->execute() or warn $dbh->errstr;

my $index = {};
while ( my $res = $sth->fetchrow_hashref() ) {
    $index->{ $res->{'stream_id'} } = $res->{'timestamp'};
}


foreach my $key ( keys $pool->%* ) {
    if ( $index->%{$key} ) {
        if ( $index->%{$key} < $timestamp_obsolete ) {
            delete_key_from_tables($key);
        }
    }
    else {
        insert_key_into_indexer($key);
    }
}


sub insert_key_into_indexer {
    my $key = shift;

    return if ( $key !~ / \A \d+ \z /smx );

    # insert key into table $table_time_indexer
    $sth = $dbh->prepare("INSERT INTO $table_time_indexer
                            ( stream_id, timestamp )
                          VALUES
                            ( ?, ? )
                         ") or warn $dbh->errstr;

    $sth->execute( $key, $timestamp_today ) or warn $dbh->errstr;
}


sub delete_key_from_tables {
    my $key = shift;

    return if ( $key !~ / \A \d+ \z /smx );

    # delete key in table $table_time_indexer
    $sth = $dbh->prepare("DELETE FROM $table_time_indexer
                          WHERE stream_id = ?
                         ") or warn $dbh->errstr;

    $sth->execute($key) or warn $dbh->errstr;

    # delete key in table $table_device_inbox
    $sth = $dbh->prepare("DELETE FROM $table_device_inbox
                          WHERE stream_id = ?
                         ") or warn $dbh->errstr;

    $sth->execute($key) or warn $dbh->errstr;
}

# eof

[ukcb]

It would be better if the table device_inbox already had a timestamp. That would make purging up a lot easier. Technically, that should not be a problem.