Improve migration performance

Biggest issue is migration 223, specifically making the polygons. For test case (zevenaar):

create_buffer_polygons: ~6 seconds
create_square_polygons: ~4 seconds

Note that using either backup=False or backup=True does not make a noticable difference.

Optimized query (works in sqlite 3.33 and newer)

Potential solution (indexes may not be necessary) that works in sqlite 3.33 and newer

-- Ensure indexes are present for faster lookups
CREATE INDEX IF NOT EXISTS idx_surface_id ON v2_impervious_surface(id);
CREATE INDEX IF NOT EXISTS idx_surface_map_surface_id ON v2_impervious_surface_map(impervious_surface_id);
CREATE INDEX IF NOT EXISTS idx_surface_map_connection_node_id ON v2_impervious_surface_map(connection_node_id);
CREATE INDEX IF NOT EXISTS idx_connection_nodes_id ON v2_connection_nodes(id);

-- Optimized update query
UPDATE v2_impervious_surface
SET dwf_geom = ST_Buffer(vcn.the_geom, 1)
FROM v2_impervious_surface_map vism
JOIN v2_connection_nodes vcn ON vcn.id = vism.connection_node_id
WHERE v2_impervious_surface.id = vism.impervious_surface_id
AND v2_impervious_surface.dwf_geom IS NULL;

journal_mode

Changing journal_mode to WAL should ensure that results are not directly written to disk. Attempts so far didn't result in any changes.

Added connection.execute(text("pragma journal_mode = WAL;")) to migrations/env.py::run_migrations_online
Added connection.execute(text("pragma journal_mode = WAL;")) to functions that slow downn migration

other optimizations (to do)

https://phiresky.github.io/blog/2020/sqlite-performance-tuning/

nens / threedi-schema

Improve migration performance #78

Optimized query (works in sqlite 3.33 and newer)

journal_mode

other optimizations (to do)