node: Database read/write optimizations — heartwood

modified radicle-cli/examples/rad-config.md

@@ -26,6 +26,9 @@ $ rad config

     },
     "connect": [],
     "externalAddresses": [],
     "db": {
       "journalMode": "rollback"
     },
     "tor": null,
     "network": "main",
     "log": "INFO",

modified radicle-httpd/src/api/v1/profile.rs

@@ -89,6 +89,7 @@ mod routes {

                   },
                   "connect": [],
                   "externalAddresses": [],
                   "db": { "journalMode": "rollback" },
                   "tor": null,
                   "network": "main",
                   "log": "INFO",

modified radicle-node/src/runtime.rs

@@ -156,7 +156,7 @@ impl Runtime {

         let policy = config.policy;
         log::info!(target: "node", "Opening node database..");
         let db = home.database_mut()?;
         let db = home.database_mut()?.journal_mode(config.db.journal_mode)?;
         let mut stores: service::Stores<_> = db.clone().into();
         log::info!(target: "node", "Opening policy database..");

modified radicle/src/node/config.rs

@@ -7,7 +7,7 @@ use localtime::LocalDuration;

 use crate::node;
 use crate::node::policy::{Policy, Scope};
 use crate::node::{Address, Alias, NodeId};
 use crate::node::{db, Address, Alias, NodeId};
 /// Target number of peers to maintain connections to.
 pub const TARGET_OUTBOUND_PEERS: usize = 8;

@@ -260,6 +260,14 @@ pub enum TorConfig {

     Transparent,
 }
 /// Database configuration.
 #[derive(Debug, Default, Clone, serde::Serialize, serde::Deserialize)]
 #[serde(rename_all = "camelCase")]
 pub struct DbConfig {
     #[serde(default)]
     pub journal_mode: db::JournalMode,
 }
 /// Service configuration.
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 #[serde(rename_all = "camelCase")]

@@ -279,6 +287,9 @@ pub struct Config {

     /// Specify the node's public addresses
     #[serde(default)]
     pub external_addresses: Vec<Address>,
     /// Database config.
     #[serde(default)]
     pub db: DbConfig,
     /// Tor configuration.
     #[serde(default)]
     pub tor: Option<TorConfig>,

@@ -321,6 +332,7 @@ impl Config {

             listen: vec![],
             connect: HashSet::default(),
             external_addresses: vec![],
             db: DbConfig::default(),
             network: Network::default(),
             tor: None,
             relay: Relay::default(),

modified radicle/src/node/db.rs

@@ -40,6 +40,19 @@ pub enum Error {

     NoRows,
 }
 /// Database journal mode.
 #[derive(Debug, Default, Copy, Clone, serde::Serialize, serde::Deserialize)]
 #[serde(rename_all = "camelCase")]
 pub enum JournalMode {
     /// "WAL" mode. Good for concurrent reads & writes, but keeps some extra files around.
     #[serde(rename = "wal")]
     WriteAheadLog,
     /// Default "rollback" mode. Certain writes may block reads.
     #[serde(alias = "rollback")]
     #[default]
     Rollback,
 }
 /// A file-backed database storing information about the network.
 #[derive(Clone)]
 pub struct Database {

@@ -93,6 +106,19 @@ impl Database {

         Ok(Self { db: Arc::new(db) })
     }
     /// Set journal mode.
     pub fn journal_mode(self, mode: JournalMode) -> Result<Self, Error> {
         match mode {
             JournalMode::Rollback => {
                 self.db.execute("PRAGMA journal_mode = DELETE;")?;
             }
             JournalMode::WriteAheadLog => {
                 self.db.execute("PRAGMA journal_mode = WAL;")?;
             }
         }
         Ok(self)
     }
     /// Create a new in-memory database.
     pub fn memory() -> Result<Self, Error> {
         let db = sql::Connection::open_thread_safe(":memory:")?;

modified radicle/src/node/routing.rs

@@ -109,28 +109,29 @@ impl Store for Database {

         time: Timestamp,
     ) -> Result<Vec<(RepoId, InsertResult)>, Error> {
         let mut results = Vec::new();
         transaction(&self.db, |db| {
         let mut select_stmt = self
             .db
             .prepare("SELECT (timestamp) FROM routing WHERE repo = ? AND node = ?")?;
         let mut insert_stmt = self.db.prepare(
             "INSERT INTO routing (repo, node, timestamp)
              VALUES (?, ?, ?)
              ON CONFLICT DO UPDATE
              SET timestamp = ?3
              WHERE timestamp < ?3",
         )?;
         transaction(&self.db, |_| {
             for id in ids.into_iter() {
                 let mut stmt =
                     db.prepare("SELECT (timestamp) FROM routing WHERE repo = ? AND node = ?")?;
                 stmt.bind((1, id))?;
                 stmt.bind((2, &node))?;
                 let existed = stmt.into_iter().next().is_some();
                 let mut stmt = db.prepare(
                     "INSERT INTO routing (repo, node, timestamp)
                      VALUES (?, ?, ?)
                      ON CONFLICT DO UPDATE
                      SET timestamp = ?3
                      WHERE timestamp < ?3",
                 )?;
                 stmt.bind((1, id))?;
                 stmt.bind((2, &node))?;
                 stmt.bind((3, &time))?;
                 stmt.next()?;
                 select_stmt.bind((1, id))?;
                 select_stmt.bind((2, &node))?;
                 let existed = select_stmt.iter().next().is_some();
                 select_stmt.reset()?;
                 insert_stmt.bind((1, id))?;
                 insert_stmt.bind((2, &node))?;
                 insert_stmt.bind((3, &time))?;
                 insert_stmt.next()?;
                 insert_stmt.reset()?;
                 let result = match (self.db.change_count() > 0, existed) {
                     (true, true) => InsertResult::TimeUpdated,