Dynamic similarity threshold based on string length for Translation Memory lookups

[tomkolp]

Describe the problem

When using Weblate with a large Translation Memory database, TM lookups are very slow for short strings, even on a powerful server.

Server specs:

• 27 vCPU, 39 GB RAM, NVMe storage
• PostgreSQL 16, shared_buffers = 12.5 GB, work_mem = 128 MB
• Weblate bleeding (latest)

Translation Memory: 3,392,954 records

String length distribution:

Length	Count	% of total
< 30 chars	1,492,878	44%
30–100 chars	1,016,353	30%
> 100 chars	883,731	26%

Average string length: 85 characters

EXPLAIN ANALYZE results (pg_trgm.similarity_threshold = 0.5):

String	Length	Trigram candidates	Execution time
"test string"	11 chars	260,406	7,766 ms
"You have completed the quest"	28 chars	157,210	3,046 ms
"Aldmeri Dominion"	16 chars	24,298	189 ms

Short strings are 40× slower than long strings. At 50% similarity threshold, a 10-character string needs very few matching trigrams to qualify, resulting in hundreds of thousands of false positives rechecked against the heap.

Workarounds attempted with no improvement:

• work_mem increased to 128 MB
• REINDEX CONCURRENTLY memory_source_trgm
• VACUUM ANALYZE memory_memory
• effective_io_concurrency = 200

Solution brainstorm

Allow configuring similarity threshold as a function of source string length in settings.py:

Option A — length-based tiers:

SIMILARITY_THRESHOLD_SHORT = 0.85   # strings < 30 chars
SIMILARITY_THRESHOLD_MEDIUM = 0.65  # strings 30–100 chars
SIMILARITY_THRESHOLD_LONG = 0.50    # strings > 100 chars
Option B — formula-based:
# threshold = max(MIN_SIMILARITY, 0.95 - (length / 300))
This way:
Short strings (UI labels, "Yes"/"No", button names) → high threshold (80–90%), only near-exact matches are useful anyway
Long strings (quest descriptions, dialogue) → lower threshold (50–60%), useful to catch sentences with a few changed words
Describe alternatives you have considered
Raising the global MINIMUM_SIMILARITY threshold uniformly — degrades quality for long strings where lower similarity is genuinely useful (catching rephrased sentences)
PostgreSQL partial indexes per length range — PostgreSQL optimizer does not guarantee which index is chosen, unreliable
Disabling shared Translation Memory — not acceptable, we intentionally share TM across game localization projects that share significant vocabulary
Screenshots
No response
Additional context
No response

[nijel]

#18666 should have already addressed that. Can you please test that? By a coincidence, it was merged one minute after you reported this 😄.

[tomkolp]

Performance results after optimization

Changes made:

• Removed all user private Translation Memory: 906,569 records
• Removed shared Translation Memory that remained after disabling the "Contribute to shared TM" option across all projects: 1,265,433 records
• Total records removed from memory_memory: 2,171,002
• Total TM size reduced from 1,681 MB to 702 MB

Benchmark comparison (EXPLAIN ANALYZE, pg_trgm.similarity_threshold = 0.5):

String	Length	Trigram candidates (before)	Execution time (before)	Trigram candidates (after)	Execution time (after)
"test string"	11 chars	260,406	7,766 ms	89,668	6,198 ms
"You have completed the quest"	28 chars	157,210	3,046 ms	53,660	5,469 ms
"Aldmeri Dominion"	16 chars	24,298	189 ms	8,480	69 ms

Important note: These benchmark results were measured after upgrading to a new version of Weblate. However, a significant performance improvement was already observed after the cleanup alone, before the upgrade — confirming that the TM database size was the primary bottleneck, not the Weblate version itself.

The benchmark numbers also do not fully reflect the real-world improvement experienced by translators. The test queries use generic English strings that are not representative of actual ESO localization content. In real usage, translators work with specific game strings (proper nouns, location names, skill descriptions) that are longer and more unique — similar to the "Aldmeri Dominion" test case, which improved by 63% (189 ms → 69 ms). The actual perceived speed improvement for translators is significantly greater than what the generic benchmark strings suggest.

The short generic strings ("test string", "You have completed the quest") remain slow due to the fundamental trigram candidate explosion problem described above — which is exactly the issue this feature request aims to address.

---

Additional finding: undocumented residual shared Translation Memory

During the investigation, we discovered that shared Translation Memory entries persist in the database even after disabling "Contribute to shared translation memory" in all projects. There is no documentation warning about this behavior, nor any guidance on how to safely identify and remove this residual data.

In our case, 1,265,433 shared TM records remained in the memory_memory table with project_id IS NULL AND user_id IS NULL, long after the feature was disabled across all projects. These records were duplicates of entries already present in the project-scoped TM (project_id = 5), yet they continued to be searched on every TM lookup — significantly degrading performance.

We also found that 906,569 user-private TM records accumulated over time with no straightforward way to manage or remove them from the admin interface.

What we suggest:

• Add documentation explaining that shared TM entries are not automatically cleaned up when the feature is disabled
• Add a warning in the admin UI when shared TM entries exist for projects that have disabled contribute_shared_tm
• Provide a documented, safe procedure for identifying and removing residual shared and user-private TM entries
• Consider adding a bulk cleanup option in the admin TM management panel (/manage/memory/) for shared and per-user entries

[nijel]

But you benchmark with pg_trgm.similarity_threshold = 0.5 what Weblate will never use. It was tweaking it before and since #18666 the threshold is even lower higher for short strings.

[tomkolp]

The fix helped. After applying the change, books load significantly faster.
A test with a much lower similarity threshold also confirms an improvement for short strings.
I have added additional tests at higher thresholds below to show further reductions in candidate counts and faster lookups for short phrases.

docker exec weblate-docker-database-1 psql -U weblate -c "SET pg_trgm.similarity_threshold = 0.85; EXPLAIN ANALYZE SELECT * FROM memory_memory WHERE source % 'test string' LIMIT 10;"
SET
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=472.88..511.39 rows=10 width=237) (actual time=1469.533..1469.535 rows=0 loops=1)
   ->  Bitmap Heap Scan on memory_memory  (cost=472.88..942.72 rows=122 width=237) (actual time=1469.531..1469.532 rows=0 loops=1)
         Recheck Cond: (source % 'test string'::text)
         Rows Removed by Index Recheck: 2105
         Heap Blocks: exact=1197
         ->  Bitmap Index Scan on memory_source_trgm  (cost=0.00..472.85 rows=122 width=0) (actual time=68.949..68.950 rows=2105 loops=1)
               Index Cond: (source % 'test string'::text)
 Planning Time: 5.542 ms
 Execution Time: 1470.276 ms
(9 rows)


docker exec weblate-docker-database-1 psql -U weblate -c "SET pg_trgm.similarity_threshold = 0.65; EXPLAIN ANALYZE SELECT * FROM memory_memory WHERE source % 'You have completed the quest' LIMIT 10;"
SET
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=775.49..814.00 rows=10 width=237) (actual time=249.765..2950.115 rows=6 loops=1)
   ->  Bitmap Heap Scan on memory_memory  (cost=775.49..1245.32 rows=122 width=237) (actual time=249.763..2950.106 rows=6 loops=1)
         Recheck Cond: (source % 'You have completed the quest'::text)
         Rows Removed by Index Recheck: 10271
         Heap Blocks: exact=6173
         ->  Bitmap Index Scan on memory_source_trgm  (cost=0.00..775.46 rows=122 width=0) (actual time=244.492..244.492 rows=10277 loops=1)
               Index Cond: (source % 'You have completed the quest'::text)
 Planning Time: 5.912 ms
 Execution Time: 2950.930 ms
(9 rows)


docker exec weblate-docker-database-1 psql -U weblate -c "SET pg_trgm.similarity_threshold = 0.75; EXPLAIN ANALYZE SELECT * FROM memory_memory WHERE source % 'Aldmeri Dominion' LIMIT 10;"
SET
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=562.37..600.88 rows=10 width=237) (actual time=44.434..45.229 rows=10 loops=1)
   ->  Bitmap Heap Scan on memory_memory  (cost=562.37..1032.20 rows=122 width=237) (actual time=44.432..45.226 rows=10 loops=1)
         Recheck Cond: (source % 'Aldmeri Dominion'::text)
         Rows Removed by Index Recheck: 13
         Heap Blocks: exact=23
         ->  Bitmap Index Scan on memory_source_trgm  (cost=0.00..562.34 rows=122 width=0) (actual time=44.265..44.266 rows=1235 loops=1)
               Index Cond: (source % 'Aldmeri Dominion'::text)
 Planning Time: 5.517 ms
 Execution Time: 45.866 ms

[nijel]

I have added additional tests at higher thresholds below to show further reductions in candidate counts and faster lookups for short phrases.

This is expected; the higher the similarity, the fewer trigram matches there are. The goal is to find as high similarity to have a minimum of non-matching strings while retaining all useful matches. The tricky part is that the trigram matching works differently than the similarity useful for the translation process; that's why the logic to calculate the trigram similarity is non-trivial, and even small changes to that need to be carefully evaluated. I believe that #18666 made the step in the right direction. We might need additional tweaking, but let this change spread to more users once it hits release so that we might have additional input.