pkg/sorted: survey of the KeyValue backends #1832

When this survey started, Perkeep had seven sorted.KeyValue implementations (plus an unused eighth), from a time when Go had few embedded or networked key/value options and we tried several. This issue surveys what each one is and its state today, so that follow-up changes (removing, fixing, or bringing a backend up to parity) can each make their own case and link here. As changes land, I'll check off the findings they resolve and list the changes at the bottom.

Backends

Line numbers are at f890de4.

Findings

• The DBMS backends get no test coverage. On a current Docker (containerd 2.1+), the MySQL, Postgres and Mongo tests, including all 20 _MySQL/_Postgres/_Mongo index tests, are skipped. The pinned Postgres and Mongo test images use a manifest format containerd no longer accepts, and with Docker 29, dockertest can't find the MySQL container's IP. Tracked in CI: test the MySQL and Postgres backends against real servers in GitHub Actions #1833; fixed by pkg/test/dockertest, .github: test MySQL and Postgres against real servers in CI #1837.
• The Mongo backend can't talk to a current MongoDB. mgo's socket.go only writes opcodes 2001–2007 (OP_UPDATE, OP_INSERT, OP_QUERY, OP_GET_MORE, OP_DELETE, OP_KILL_CURSORS), never OP_MSG, and MongoDB 6.0 removed those opcodes (starting in 5.1). Removed in all: remove MongoDB support #1835.
• Only SQLite has batched reindex (sorted.Reindexer) and crash-safe detection of an interrupted reindex (NeedWipeError).
• No caller uses TransactionalReader, other than kvtest.
• Every backend drops oversized writes. All of them (sqlkv for MySQL/Postgres/SQLite, leveldb, kv, memory, and SQLite's reindex session) check sorted.CheckSizes (MaxKeySize 767, MaxValueSize 63000, inherited from MySQL/InnoDB limits) and, for a write over the limit, log it and skip it: Set returns nil and batches skip the mutation, so the caller never sees an error. See sorted: key of 1979 bytes too large #1812 for a case where this drops index rows.
• Even with a DBMS index, some stores are leveldb files. With a local blobPath, the thumbnail cache and cloud sync queues use kvFileType() (pkg/serverinit/genconfig.go:358), which falls back to sorted.DefaultKVFileType = "leveldb" (pkg/sorted/kv.go:35). diskpacked's default index type is also leveldb (diskpacked.go:109).
• The docs don't help anyone choose. doc/server-config.md:119 just lists the options. :214 still says SQLite is hard to get working on Windows because of mattn/go-sqlite3, though we use pure-Go modernc.org/sqlite now, and :232 says Mongo is "not as well tested as the MySQL one". doc/status.md:30 and doc/overview.md:143 still list App Engine. (docs mostly fixed in doc: update index backend guidance and Windows SQLite support #1842, except for helping users choose)
• Other direct users: cmd/pk-put's have/stat caches use goleveldb directly, not pkg/sorted (see cmd/pk: use sqlite for havecache? #1773 about moving the havecache to SQLite). cmd/pk dbinit supports mysql, postgres and sqlite and mongo. dev/kvcopy (dev/kvcopy: add a tool to copy or dump sorted.KeyValue stores #1827) can copy any store into any other, which gives users of a backend that changes or goes away a migration path.

Changes

Each change that addresses a finding above should link this issue; I'll list it here and check off what it resolves.

• all: remove MongoDB support #1835: removed MongoDB support: the mongo index and blob storage, the mongo config option, pk dbinit --dbtype=mongo, and the gopkg.in/mgo.v2 dependency.
• pkg/sorted: remove unused buffer package #1836: removed the unused pkg/sorted/buffer package.
• pkg/test/dockertest, .github: test MySQL and Postgres against real servers in CI #1837: MySQL and Postgres tests run against real servers in CI (CI: test the MySQL and Postgres backends against real servers in GitHub Actions #1833).
• pkg/sorted: implement the memory KeyValue with google/btree #1840: reimplemented the memory backend on google/btree, removing its dependency on goleveldb, and documented on Find that an iterator may or may not observe writes made after Find returns (backends differ).
• pkg/sorted/postgres, cmd/pk: use pgx instead of lib/pq #1843: switched the Postgres driver from lib/pq to jackc/pgx/v5 (via its database/sql adapter), for the postgres index, pk dbinit and dockertest.
• pkg/sorted/kvfile: deprecate the "kv" sorted type #1851: deprecated the kv sorted type and the kvIndexFile config field: opening a kv store logs a warning, kv is dropped from the docs and the suggested index options, and the publisher's thumbnail cache uses sorted.DefaultKVFileType instead of kv. Removal and migration with dev/kvcopy are tracked in pkg/sorted/kvfile: the "kv" sorted type is deprecated; removal no earlier than 2027-04-13 #1850.

Related: #126 and #46 (making the Mongo and Postgres indexers efficient), #790 and #1093 (LevelDB Sync and file descriptors), #258 (Postgres connection count), #523 (better pkg/sorted tests), #786 (a kv "unexpected EOF"), #1829 (startup performance).