Idea: fast path for importing into an empty tree

[dsblank]

The problem

Importing a file into a fresh, empty gramps-web-api tree goes through the
same object-by-object commit path as importing into a populated one:
legalize_id()'s per-object gramps_id/handle collision queries, whatever
per-object schema-validation cost the importer pays, and one Postgres round
trip per object per write. All of that exists to protect a populated
tree from collisions during a merge -- but on an empty tree there is
nothing to collide with, so it's pure overhead.

And, the preview (dry_run) already imports it once into a memory-based SQLite database.

What if we got rid of the overhead, and re-used the SQLite database?

Why would we consider this? Importing into an empty database is a common operation for gramps-web-api. How much time would this save? Some real numbers with a prototype (new path is described in this post, old path is the current method):

File	Objects	Old path	New path	Speedup
example.gramps (XML)	10,534	81.1s	13.9s	5.8x
jsonl export	26,540	108.6s	24.8s	4.4x
100k-person .gramps (XML)	~847,862	in progress (~15h estimated)	12m 56s	~70x (projected, could be better)

In other words, for a 100k person tree, it reduces import from over 2 hours, down to 13 minutes.

The idea

When the target tree is empty (db_handle.get_total() == 0), fork
run_import():

1. Parse the file into a throwaway in-memory scratch Gramps DB (plain
   SQLite, :memory:) instead of the real tree -- the same scratch DB
   dry_run_import() already builds for preview counts, just kept instead
   of discarded.
2. Bulk-copy the resulting rows straight into the real tree: raw
   (handle, json_data) per object table plus the reference table, no
   gramps_id/handle reconciliation (nothing to reconcile against).
3. Run rebuild_secondary() once (existing, already-tested Gramps
   machinery) to backfill given_name/surname/title/etc. -- reused
   rather than reimplemented, and it already knows how to handle
   backend-specific column naming (e.g. SharedPostgreSQL's desc ->
   desc_ reserved-word rename) without this code needing to know about
   it.
4. Where the backend supports it (SharedPostgreSQL.Connection.bulk_insert,
   built on psycopg2's execute_values), the copy itself is real batched
   INSERTs, not one round trip per row. This turned out to matter more
   than the collision-check savings alone -- see numbers below.
5. An ANALYZE runs on the just-written tables immediately after the bulk
   insert, before rebuild_secondary()'s point lookups run against them --
   otherwise a bulk INSERT finishes faster than autovacuum's normal cycle
   and the planner picks bad query plans (sequential scans) against tables
   it still thinks are empty.

Backends without a bulk_insert() hook (plain sqlite/postgresql) fall back
to the existing row-by-row execute() loop -- correct, just without the
extra speedup on top.

Correctness safeguards

• Metadata (default person, researcher, ...): some of it
  (set_default_person_handle) persists immediately via _set_metadata();
  some (set_researcher, bookmarks, custom type/attribute registries) only
  lands in an in-memory attribute normally flushed by
  _set_all_metadata() at close() time -- which is a no-op for a
  :memory: DB. Fixed for default-person/researcher by explicitly
  flushing scratch's metadata before and after parsing, diffing against a
  pre-parse baseline, and propagating the diff -- plus, for researcher
  specifically, updating real_db.owner directly so the real tree's own
  later close()-time flush doesn't clobber it. Bookmarks and the ~17
  custom type/attribute registries go through the identical deferred
  mechanism and have the identical gap, not yet fixed (lower severity --
  mostly UI-autocomplete conveniences, not broken references).
• Concurrency: the empty-tree check is a snapshot with no lock by
  default -- two concurrent imports into the same tree could both pass it.
  Closed with a Postgres advisory lock keyed on the tree's treeid, held
  from a re-check of emptiness through the whole copy. Verified with two
  genuinely concurrent requests (threaded dev server): one completes, the
  other correctly detects the tree is no longer empty and fails loudly
  instead of silently corrupting anything.
• Partial failure: since the tree is confirmed empty right before the
  copy starts, any exception during the copy triggers delete_all_objects()
  (the same function backing the existing "Delete all items" feature)
  before re-raising -- so a failure leaves the tree exactly as it started,
  not a half-populated, half-reindexed mess.

Numbers

All comparisons below are old-path-forced vs. new-path, both run against a
freshly truncated database.

File	Objects	Old path	New path	Speedup
example.gramps (XML)	10,534	81.1s	13.9s	5.8x
jsonl export	26,540	108.6s	24.8s	4.4x
100k-person .gramps (XML)	~847,862	in progress (~15h estimated)	12m 56s	~70x (projected, could be better)

New-path breakdown for the 847,862-object file:

parse into scratch:    149.15s
row copy (INSERTs):     34.00s
ANALYZE:                 5.22s
rebuild_secondary:     161.69s
bulk_copy total:       201.00s
search reindex + misc: ~426s   (unlogged remainder, same cost either path)
────────────────────────────────
total:                 776.4s  (12m 56s)

Memory

Both paths show substantial memory growth for this file: old path
plateaued around ~2.1GB RSS, new path peaked around ~2.7GB over its
13-minute run. Not yet established as a general bound for either path;
only characterized on this one 847k-object file.

Not yet addressed

• Bookmarks / custom type & attribute registries (see above)
• No automated tests -- everything validated so far is manual, against
  real files, in a live dev environment
• Only exercised against SharedPostgreSQL + two formats (.gramps,
  .jsonl) -- GEDCOM 5.5.1, CSV, GeneWeb, ProGen untested via this path;
  GEDCOM 7 doesn't take this path at all (only wired into the generic
  plugin-loop branch of run_import(), not the GEDCOM7-specific branch)
• Plain sqlite/built-in postgresql backends untested (should be safe
  by construction -- fails over to the existing row-by-row path -- but
  "should be" isn't "verified")
• Memory isn't bounded anywhere -- no size-based guard exists on either
  path if a file is too large for available memory

Open questions

• Is this an acceptable idea? You see any issues?
• Should bulk_insert() stay a duck-typed, backend-specific hook
  (hasattr(dbapi, "bulk_insert"), currently only implemented on
  SharedPostgreSQL) the way it is now, or does this belong as a formal
  method on DbWriteBase upstream in gramps core, with a default
  implementation other backends inherit?

[DavidMStraub]

I think it would be nice to have, but having to maintain this for each DB backend we support increases the drift from Gramps core and the maintenance burden after Gramps core version bumps. Of course, a bulk_insert method on DbWriteBase would be the dream. I suggest we try getting it into 6.2 - if we fail, we can reconsider implementing it here. What do you think?

[dsblank]

I suggest we try getting it into 6.2 - if we fail, we can reconsider implementing it here. What do you think?

There are some things here that are gramps-web-api specific, but yes, moving bulk_insert lower in the stack would be desirable. Currently the fast-path imports 100k records in 13 minutes, and the normal-path is still running at 15 hours! Something is very exponential.

[DavidMStraub]

Are you sure it's the actual import or is it just the search Index (or semantic search index)?

[dsblank]

I'm pretty sure it is importxml because the fast-path would also have to do those bits as well, and it is fast. I think if I import the 100k XML into desktop gramps, it would take exactly as long, but I will test that hypothesis.

[DavidMStraub]

Right, I wouldn't be surprised if importxml contained bits that are exponentially slow on Postgres because they loop over individual inserts or selects.

[dsblank]

I have a better understanding of all of the slow parts of an import. I made two PRs:

1. Some minor fixes: Speed up full search reindex: throttle progress reporting, bulk-load event backlinks #958 I don't think these are going to have a huge effect on a job that took 20+ hours before, but these are fairly easy to review
2. a major refactor to handle the special case of importing into an empty tree: Empty-tree fast import: bulk_copy, inline secondary columns, no double-parse #959

They are both drafts currently, and 2 depends (slightly) on 1.

After all of this, sifts (and its two _add() calls) is 50% of the total time. I don't think that part can be reduced.

800k objects imported in a few minutes. Not bad.