nostr-rs-relay/docs/database-maintenance.md

Database Maintenance

nostr-rs-relay uses the SQLite embedded database to minimize dependencies and overall footprint of running a relay. If traffic is light, the relay should just run with very little need for intervention. For heavily trafficked relays, there are a number of steps that the operator may need to take to maintain performance and limit disk usage.

This maintenance guide is current as of version 0.8.2. Future versions may incorporate and automate some of these steps.

Backing Up the Database

To prevent data loss, the database should be backed up regularly. The recommended method is to use the sqlite3 command to perform an "Online Backup". This can be done while the relay is running, queries can still run and events will be persisted during the backup.

The following commands will perform a backup of the database to a dated file, and then compress to minimize size:

BACKUP_FILE=/var/backups/nostr/`date +%Y%m%d_%H%M`.db
sqlite3 -readonly /apps/nostr-relay/nostr.db ".backup $BACKUP_FILE"
sqlite3 $BACKUP_FILE "vacuum;"
bzip2 -9 $BACKUP_FILE

Nostr events are very compressible. Expect a compression ratio on the order of 4:1, resulting in a 75% space saving.

Vacuuming the Database

As the database is updated, it can become fragmented. Performing a full vacuum will rebuild the entire database file, and can reduce space. Running this may reduce the size of the database file, especially if a large amount of data was updated or deleted.

vacuum;

Clearing Hidden Events

When events are deleted, the event is not actually removed from the database. Instead, a flag HIDDEN is set to true for the event, which excludes it from search results. High volume replacements from profile or other replaceable events are deleted, not hidden, in the current version of the relay.

In the current version, removing hidden events should not result in significant space savings, but it can still be used if there is no desire to hold on to events that can never be re-broadcast.

PRAGMA foreign_keys = ON;
delete from event where HIDDEN=true;

Manually Removing Events

For a variety of reasons, an operator may wish to remove some events from the database. The only way of achieving this today is with manually run SQL commands.

It is recommended to have a good backup prior to manually running SQL commands!

In all cases, it is mandatory to enable foreign keys, and this must be done for every connection. Otherwise, you will likely orphan rows in the tag table.

Deleting Specific Event

PRAGMA foreign_keys = ON;
delete from event where event_hash=x'00000000000c1271675dc86e3e1dd1336827bccabb90dc4c9d3b4465efefe00e';

Querying and Deleting All Events for Pubkey

PRAGMA foreign_keys = ON;

select lower(hex(author)) as author, count(*) as c from event group by author order by c asc;

delete from event where author=x'000000000002c7831d9c5a99f183afc2813a6f69a16edda7f6fc0ed8110566e6';

Querying and Deleting All Events of a Kind

PRAGMA foreign_keys = ON;

select printf('%7d', kind), count(*) as c from event group by kind order by c;

delete from event where kind=70202;

Deleting Old Events

In this scenario, we wish to delete any event that has been stored by our relay for more than 1 month. Crucially, this is based on when the event was stored, not when the event says it was created. If an event has a created field of 2 years ago, but was first sent to our relay yesterday, it would not be deleted in this scenario. Keep in mind, we do not track anything for re-broadcast events that we already have, so this is not a very effective way of implementing a "least recently seen" policy.

PRAGMA foreign_keys = ON;

DELETE FROM event WHERE first_seen < CAST(strftime('%s', date('now', '-30 day')) AS INT);

Delete Profile Events with No Recent Events

Many users create profiles, post a "hello world" event, and then never appear again (likely using an ephemeral keypair that was lost in the browser cache). We can find these accounts and remove them after some time.

PRAGMA foreign_keys = ON;
TODO!