[Git][snapshot-team/snapshot][master] 2 commits: Create a "manually" maintained materialized view for node metadata

Baptiste Beauplat pushed to branch master at snapshot / snapshot

Commits:

5aa020c0

by Philipp Kern at 2025-02-07T01:11:50+01:00

Create a "manually" maintained materialized view for node metadata

It's necessary to materialize node_with_ts to speed up queries
massively, but materialized views of this size (6 GB+) come with a hefty
penalty for the WAL - which hurts in both synchronization and backup
space. Instead add a manually maintained table that is updated whenever
a node is touched. This will still generate a lot of churn by design
(after all, it will be executed for every node that is still alive at
the current import time), but much less than regenerating the whole
view.

2620e7f1

by Baptiste Beauplat at 2025-02-11T18:54:36+01:00

Merge branch 'snapshot-db-fixes'

Signed-off-by: Baptiste Beauplat <lyknode@debian.org>

Changes:

db/upgrade_26.py

 +#!/usr/bin/python3
 +#
 +# Copyright (c) 2024 Philipp Kern
 +#
 +# Permission is hereby granted, free of charge, to any person obtaining a copy
 +# of this software and associated documentation files (the "Software"), to deal
 +# in the Software without restriction, including without limitation the rights
 +# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 +# copies of the Software, and to permit persons to whom the Software is
 +# furnished to do so, subject to the following conditions:
 +#
 +# The above copyright notice and this permission notice shall be included in
 +# all copies or substantial portions of the Software.
 +#
 +# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 +# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 +# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 +# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 +# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 +# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 +# SOFTWARE.
++
 +def upgrade(db):
 +    db.execute("""
 +        CREATE TABLE node_with_ts_materialized AS
 +          SELECT node.node_id,
 +            node.parent,
 +            node.first,
 +            node.last,
 +            mirrorrun_first.run AS first_run,
 +            mirrorrun_first.archive_id,
 +            mirrorrun_last.run AS last_run
 +          FROM node
 +            JOIN mirrorrun mirrorrun_first ON mirrorrun_first.mirrorrun_id = node.first
 +            JOIN mirrorrun mirrorrun_last ON mirrorrun_last.mirrorrun_id = node.last;
 +        CREATE UNIQUE INDEX node_with_ts_materialized_node_id
 +          ON node_with_ts_materialized USING btree (node_id);
 +        CREATE INDEX node_with_ts_materialized_parent
 +          ON node_with_ts_materialized USING btree (parent);
 +        """)
++
 +    db.execute("UPDATE config SET value='26' WHERE name='db_revision' AND value='25'")
++
 +# vim:set et:
 +# vim:set ts=4:
 +# vim:set shiftwidth=4:

snapshot

@@ -117,6 +117,7 @@ class SnapshotDB
  	def initialize(conf, logger)
  		@dbh = PG.connect(conf['connectstring'])
  		@logger = logger
 +		@updated_node_ids = []
  	end
  	def get_primarykey_name(table);
@@ -129,6 +130,7 @@ class SnapshotDB
  		@dbh.exec("BEGIN")
  	end
  	def commit()
 +		_materialize_updated_nodes()
  		@dbh.exec("COMMIT")
  	end
@@ -180,10 +182,13 @@ class SnapshotDB
  			results = insert(table, values, [pk_name])
  			values[pk_name] = results[pk_name]
  		end
 +		if table == "node"
 +			enqueue_node_id(values["node_id"])
 +		end
  		values
  	end
 -	def update(table, set, where, returning=nil)
 +	def _update(table, set, where, returning=nil)
  		setclause = set.each_key.collect { |k, index| "#{k}=$#{index + 1}" }.join(",")
  		whereclause = where.each_key.collect { |k, index| where[k].nil? ? "#{k} IS NULL" : "#{k}=$#{index + set.each_key.length + 1}" }.join(" AND ")
@@ -200,7 +205,11 @@ class SnapshotDB
  	end
  	def update_one(table, set, where)
 -		r = update(table, set, where)
 +		r = _update(table, set, where)
++
 +		if table == "node"
 +			enqueue_node_id(where["node_id"])
 +		end
  		raise "Did not update exactly one row in update_one" unless r.cmd_tuples() ==1
  		return r
@@ -229,6 +238,39 @@ class SnapshotDB
  			return row
  		end
  	end
++
 +	def enqueue_node_id(node_id)
 +		@updated_node_ids.append(node_id)
 +	end
++
 +	def _materialize_updated_nodes
 +		if @updated_node_ids.length == 0
 +			return
 +		end
 +		node_placeholders = (1..@updated_node_ids.length).map { |i| "$#{i}" }.join(', ')
 +		execute("WITH upd AS (
 +			SELECT node.node_id,
 +				node.parent,
 +				node.first,
 +				node.last,
 +				mirrorrun_first.run AS first_run,
 +				mirrorrun_first.archive_id,
 +				mirrorrun_last.run AS last_run
 +			FROM node
 +				JOIN mirrorrun mirrorrun_first ON mirrorrun_first.mirrorrun_id = node.first
 +				JOIN mirrorrun mirrorrun_last ON mirrorrun_last.mirrorrun_id = node.last
 +			WHERE node.node_id IN (#{node_placeholders})
 +		)
 +		INSERT INTO node_with_ts_materialized
 +			SELECT * FROM upd
 +		ON CONFLICT (node_id) DO UPDATE
 +			SET parent = EXCLUDED.parent,
 +				first = EXCLUDED.first,
 +				last = EXCLUDED.last,
 +				first_run = EXCLUDED.first_run,
 +				archive_id = EXCLUDED.archive_id,
 +				last_run = EXCLUDED.last_run", *@updated_node_ids)
 +	end
  end
  class FSNode