Problem

The developer articles (META-INF/tl-docs) have nothing on how an application configures its persistency layer. Decisions with a large effect are only discoverable by reading engine sources and web-fragment configs. In the E-Home application (TopLogic 8.0.0-alpha10, H2), finding out why deleting a few hundred objects took 20 s (#29876) needed a profile, H2 query statistics, EXPLAIN ANALYZE and the H2 sources. The fix turned out to be one line of configuration that no article mentions.

What was found (to be covered by the article)

• **Branch support is on by default.** SchemaConfiguration.hasMultipleBranches() (multiple-branches, DEFAULT_MULTIPLE_BRANCHES_ATTRIBUTE = true) gives every table a BRANCH column as the first column of the primary key and of the reference indexes, e.g. HAS_STRUCTURE_CHILD_SOURCE (BRANCH, SOURCE_ID, META_ATTRIBUTE_ID, REV_MAX). An application that never creates a branch carries a constant column at the head of every index.
• **Switching it off:** tl-core ships WEB-INF/conf/top-logic.singleBranch.config.xml. It sets multiple-branches="false" on the Default type system of ApplicationTypes and adds webinf://kbase/singleBranchMeta.xml, which makes _branch a computed attribute (TrunkStorage) and removes it from the primary key. An application enables it by listing the file in its metaConf.txt. Neither the file nor the option is mentioned in any article, and no shipped application seems to use it.
• **Effect (measured):** on H2, an IN list is used for an index seek only on the first index column (org.h2.index.IndexCursor.canUseIndexFor). With BRANCH first, the referrer queries of a deletion ((BRANCH, rev, SOURCE_ID) IN (...), see #29876) read the whole association table. With the single-branch configuration, the E-Home test suite deleted the same data in 2.3 s / 0.9 s / 0.8 s instead of 21.1 s / 7.5 s / 8.8 s, and the whole suite ran in 85 s instead of 163 s, imports included.
• **Consequence for existing data:** the switch changes the table layout. A database created with branches does not fit the new configuration, and it is unclear whether a migration exists.

Requested

An article (e.g. persistency-configuration) that an application developer reads before the first deployment:

1. Single branch vs. multiple branches: what branches are used for, what they cost (schema, index layout, query plans, especially on H2), and that the decision should be made before the first database is created.
2. How to switch: top-logic.singleBranch.config.xml in metaConf.txt, and what it changes.
3. Whether and how an existing database can be migrated between the two (or that it cannot).
4. The other parts of the type-system configuration (ApplicationTypes, type-systems, declarations / schemas, `TypeProvider`s): which settings change the database schema and therefore need a migration or a fresh database.
5. Known DB-specific pitfalls of the generated SQL (e.g. the H2 IN-list rule above, the MySQL OR-query note in Expressions.createMonomorphicQueriesForType).

Possibly the default should also be reconsidered: new applications rarely need branches, and with the current default they pay for them from the start.

Lösung

A chapter persistency of the developer documentation, shipped by tl-core (com.top_logic/src/main/java/META-INF/tl-docs/persistency/), readable with the read_doc tool of the tl-mcp server and in the Documentation window of a React application:

• persistency/index: what the chapter covers and the decisions to make before the first database of an application is created.
• persistency/type-systems: the type-system configuration (ApplicationTypes, type-system, declarations, schemas, providers, data), how engine modules and the application contribute to the Default type system (metaConf.txt, the archetype's <app>.conf.config.xml + kbase/<App>Meta.xml), how a model attribute ends up in a table column or in FLEX_DATA, the schema stored in TL_PROPERTIES that the knowledge base boots on, and which change is applied automatically at startup (new table types, model upgrade) and which needs a migration file (WEB-INF/kbase/migration/<module>/*.migration.xml, e.g. new columns, dropped tables, column ↔ flex moves).
• persistency/branches: what branches are for and that the engine itself creates only trunk; the exact cost of multiple-branches (branch column first in the primary key and the indexes via DefaultIndexColumnsStrategy, _BRC reference columns, BRANCH in FLEX_DATA and the journal tables, query plans); the single-branch switch (top-logic.singleBranch.config.xml in metaConf.txt) and what it changes; the DefaultIndexColumnsStrategy options (branch-after-custom-columns, exclude-branch-from-non-unique-indices) for applications that keep branches; and that no conversion of an existing database between the two layouts exists (the automatic startup migration only creates tables for new types and ignores the flag; a replay migration recreates the tables from the configured schema but is untested for this purpose), so the decision is made before the first database is created.
• persistency/database-notes: DB-specific behaviour of the generated SQL, each with the code location: the H2 IN-list rule (#29876), the MySQL OR-query note (Expressions.createMonomorphicQueriesForType), the FLEX_DATA index column order (PostgreSQL), the sorted commits for MySQL (DefaultDBContext), the IN-list chunk size per dialect (DBHelper.getMaxSetSize).

No engine code changes. The question whether multiple-branches should default to false for new applications is not decided here; it is left to a separate ticket.