Performance: Consider replacing Elasticsearch with MySQL FULLTEXT for low-resource servers

[dmetzner]

Problem

Elasticsearch 7.17 (EOL since May 2024) consumes ~1GB RAM minimum (512MB JVM heap + overhead). On a server with "very low hardware limits," this is a significant resource drain for what amounts to project/user search.

Options

Option A: Optimize current Elasticsearch

• Reduce heap to 256MB (risky, may OOM)
• Upgrade to 8.x for better memory management
• Use circuit breakers more aggressively

Option B: Replace with MySQL FULLTEXT

• MariaDB 10.11 supports FULLTEXT indexes
• Add FULLTEXT index on program.name, program.description
• Eliminate Elasticsearch dependency entirely
• Saves ~1GB RAM

Option C: External search service

• Algolia free tier (10K requests/month)
• Meilisearch (lighter than Elasticsearch, ~50MB RAM)
• Typesense (similar to Meilisearch)

Recommendation

For a platform with limited hardware, Option B (MySQL FULLTEXT) is the pragmatic choice unless search quality is a hard requirement. Meilisearch (Option C) is a good middle ground.

Severity

Medium — 1GB RAM savings on limited hardware is significant.

[dmetzner]

Research: Elasticsearch Usage Analysis & Replacement Options

1. Current Elasticsearch Usage

Indexed Entities

Two indexes are configured in config/packages/fos_elastica.php:

1. app_program (Program entity) — 16 indexed fields:

   • Text: name, description, flavor, id, getTagsString, getExtensionsString, getUsernameString
   • Boolean: private, visible, debug_build, auto_hidden
   • Numeric: language_version (float), downloads (int), rand (int), popularity (float)
   • Date: uploaded_at, last_modified_at

2. app_user (User entity) — 4 indexed fields:

   • Text: id, username
   • Boolean: profile_hidden, verified

Search Query Complexity

The queries are simple keyword searches with boolean filters — no faceted search, no geo queries, no aggregations, no fuzzy matching, no synonyms, no autocomplete/suggest.

Project search (ProjectSearchService::projectSearchQuery):

• QueryString with wildcard suffix on each word (e.g., cat* game*)
• Fields: id, name^3 (boosted), description, getUsernameString, getTagsString, getExtensionsString
• AND operator (all words must match)
• Filters: private=false, visible=true, optionally debug_build=false, language_version <= X, flavor=X
• Uses English stop words and lowercase analysis

User search (UserManager::userSearchQuery):

• QueryString with wildcard suffix
• Fields: id, username^3 (boosted)
• AND operator
• Filters: verified=true, profile_hidden=false

Project listing (ProgramRepository, behind GET_projects_elastica feature flag — currently disabled):

• getProjects() and getTrendingProjects() have Elastica code paths but the feature flag GET_projects_elastica is false in config
• These are filtered listing queries (no full-text search), just boolean filters + sorting + pagination

Integration Points

Component	ES Dependency	Purpose
ProjectSearchService	fos_elastica.finder.app_program	Full-text project search
UserManager	fos_elastica.finder.app_user	Full-text user search
ProgramRepository	fos_elastica.finder.app_program	Project listing (feature-flagged OFF)
SearchApi	Via SearchApiFacade	/api/search endpoint
ProjectsApi	Via ProjectsApiLoader	/api/projects/search endpoint
UserApi	Via UserApiLoader	/api/users/search endpoint
ResetCommand	fos:elastica:reset + fos:elastica:populate	Index reset during dev
DataFixturesContext	fos:elastica:reset	Behat test setup

Library: friendsofsymfony/elastica-bundle 7.0.x

• Docker: Elasticsearch 8.17.0 with ES_JAVA_OPTS=-Xms512m -Xmx512m (512MB-1GB RAM)
• Auto-sync via Doctrine event listeners (FOSElasticaBundle handles this automatically)

2. MySQL FULLTEXT Feasibility Assessment

MariaDB 10.11 (InnoDB) supports FULLTEXT indexes natively.

Required FULLTEXT Indexes

-- Program table
ALTER TABLE program ADD FULLTEXT ft_program_search (name, description);
-- Possibly a separate index for tags/extensions if stored as columns

-- User table  
ALTER TABLE user ADD FULLTEXT ft_user_search (username);

Can Current Queries Be Replicated?

ES Feature Used	MySQL FULLTEXT Equivalent	Feasible?
Multi-field keyword search	MATCH(name, description) AGAINST('+cat* +game*' IN BOOLEAN MODE)	Yes
Field boosting (name^3)	Use weighted MATCH scores: MATCH(name) AGAINST(...) * 3 + MATCH(description) AGAINST(...)	Yes, but manual
Wildcard suffix (word*)	Boolean mode supports word* natively	Yes
AND operator	Boolean mode +word1* +word2*	Yes
Boolean filters (private, visible, etc.)	WHERE clauses (already have indexes for these)	Yes
Range filters (language_version, dates)	Standard SQL WHERE clauses	Yes
English stop words	MariaDB has built-in stop word list (ft_stopword_file)	Yes
Lowercase matching	Case-insensitive by default in MariaDB	Yes
Searching tags/extensions strings	Need computed/virtual column or JOIN-based search	Moderate effort

Challenge: Tags & Extensions Search

Currently, getTagsString() and getExtensionsString() are PHP methods that concatenate related entity data into strings for ES indexing. For MySQL FULLTEXT:

• Option A: Add a denormalized search_text column that concatenates name + description + username + tags + extensions, updated via Doctrine lifecycle events. Single FULLTEXT index on this column.
• Option B: Use JOINs with separate FULLTEXT indexes on the tag and extension tables.
• Option C: Use a generated/virtual column (MariaDB supports this but FULLTEXT on virtual columns is limited).

Option A is simplest and mirrors what ES already does (denormalized index).

Performance Considerations

• MariaDB FULLTEXT is adequate for the dataset size. Catroweb is a community platform, not a web-scale search engine. Even with tens of thousands of projects, FULLTEXT performs well.
• The existing composite indexes (program_listing_idx, program_popularity_idx) already cover the boolean filter columns, so the WHERE clause filtering is efficient.
• FULLTEXT search in boolean mode with wildcards is O(n) on the index, not O(n) on the table — it's fast.
• No need for near-real-time indexing concerns since Doctrine flushes synchronously.

3. Alternative Evaluation

Option A: MySQL/MariaDB FULLTEXT (Recommended)

Aspect	Assessment
RAM	0 MB additional (uses existing MariaDB)
Complexity	Low — SQL queries replace Elastica queries
Migration effort	Small-medium (2-3 days)
PHP SDK	Native Doctrine DQL/DBAL
Maintenance	Zero — no separate service to maintain
Feature parity	Full for current use case
Limitations	No fuzzy matching, no relevance tuning as flexible as ES

Option B: Meilisearch

Aspect	Assessment
RAM	~50-100 MB for small datasets
Complexity	Medium — new service, SDK integration
Migration effort	Medium (3-5 days)
PHP SDK	meilisearch/meilisearch-php (official, well-maintained)
Maintenance	New Docker service, version upgrades
Features	Typo tolerance, faceting, instant search, excellent UX
Limitations	Overkill for current simple search needs

Option C: Typesense

Aspect	Assessment
RAM	~50-100 MB for small datasets
Complexity	Medium — similar to Meilisearch
Migration effort	Medium (3-5 days)
PHP SDK	typesense/typesense-php (official)
Maintenance	New Docker service
Features	Similar to Meilisearch, slightly lower resource usage
Limitations	Smaller ecosystem than Meilisearch

Option D: TNTSearch (PHP-based)

Aspect	Assessment
RAM	Minimal (file-based index)
Complexity	Low-medium
Migration effort	Medium (3-4 days)
PHP SDK	teamtnt/tntsearch
Maintenance	Index files on disk, need rebuilds
Features	Fuzzy search, boolean operators
Limitations	Less battle-tested, disk I/O for index, no active Symfony bundle

4. Recommendation: MySQL FULLTEXT

Replace Elasticsearch with MariaDB FULLTEXT search. Rationale:

1. The search queries are simple. No faceting, no geo, no aggregations, no fuzzy matching, no autocomplete. Just keyword search with boolean filters — exactly what FULLTEXT was designed for.

2. Zero additional infrastructure. MariaDB is already running. No new Docker service, no new port, no new version to track, no new service to monitor.

3. Saves ~512MB-1GB RAM. This is the primary motivation and it's fully achieved.

4. Minimal migration effort. The search is cleanly isolated in ProjectSearchService and UserManager::search/searchCount. The ProgramRepository Elastica code is already behind a disabled feature flag.

5. FOSElasticaBundle can be fully removed. No other features (aggregations, percolation, etc.) are used.

Migration Plan

Phase 1: Add FULLTEXT infrastructure

• Add a search_text denormalized column to program table (concatenation of name, description, username, tags, extensions)
• Add Doctrine lifecycle listener to keep search_text in sync on persist/update
• Add FULLTEXT index on program.search_text and program.name
• Add FULLTEXT index on user.username
• Create Doctrine migration

Phase 2: Replace search services

• Rewrite ProjectSearchService to use DQL/DBAL with MATCH ... AGAINST
• Rewrite UserManager::search/searchCount to use DQL/DBAL with MATCH ... AGAINST
• Implement field boosting via weighted MATCH scores (name^3 equivalent)
• Remove ProgramRepository Elastica code paths (already disabled by feature flag)

Phase 3: Remove Elasticsearch

• Remove friendsofsymfony/elastica-bundle from composer.json
• Remove config/packages/fos_elastica.php
• Remove Elasticsearch from docker-compose.dev.yaml and docker-compose.test.yaml
• Remove ELASTICSEARCH_URL from .env files and Dockerfile
• Remove fos:elastica:reset/populate from ResetCommand and DataFixturesContext
• Update Behat step I wait for the search index to be updated (no longer needed, or make it a no-op)
• Remove GET_projects_elastica feature flag

Phase 4: Test

• All existing Behat search tests should pass with the new implementation
• Performance test with production-scale data

Estimated Effort: 2-3 days

The search is well-isolated, the queries are simple, and MariaDB FULLTEXT directly supports every feature currently used.

---

If future needs grow (typo tolerance, instant search, faceted browsing), Meilisearch would be the next step up — but that bridge can be crossed when needed. For now, the simplest solution that eliminates 1GB of RAM overhead is the right call.

[dmetzner]

I agree with the general direction here, but I think the recommendation is a bit too confident as written.

After checking the current codebase, Elasticsearch does look replaceable in principle: the search logic is fairly isolated in ProjectSearchService and UserManager, and the Elastica-based project listing path in ProgramRepository is already behind the GET_projects_elastica feature flag, which is currently disabled.

That said, I would soften the conclusion from "replace Elasticsearch with MySQL FULLTEXT" to "evaluate whether MySQL FULLTEXT can replace Elasticsearch without unacceptable behavior changes."

A few reasons:

1. The current search surface is broader than just program.name and program.description.
   Project search currently covers project id, name, description, owner username, tags, and extensions. User search also covers both id and username.

2. Existing tests rely on behavior that is not obviously covered by a straight FULLTEXT swap.
   The Behat scenarios currently expect:

   • partial project ID matches, including suffix-like queries such as -etqx
   • tag and extension search
   • one-character Cyrillic user search
   • ranking where a name hit is prioritized over a description hit

3. That means a pure MATCH ... AGAINST implementation may not be enough.
   Even if FULLTEXT handles the main keyword cases well, we would likely still need fallback logic for IDs and short queries, plus some denormalized search field or additional synchronization for tags/extensions/owner name.

So my opinion is:

• The issue is valid and worth pursuing.
• Elasticsearch likely is overkill for Catroweb’s current search needs.
• But the migration should be framed as a spike/prototype first, not yet as a settled implementation choice with “full parity” and “2-3 days” confidence.

I’d suggest adjusting the recommendation to one of these:

• "Run a spike replacing the current search with MariaDB FULLTEXT plus targeted fallbacks for IDs/short queries, and compare against current Behat coverage."
• or, if preserving current search behavior matters more than absolute minimum RAM, "evaluate Meilisearch as a lower-memory replacement with fewer behavioral compromises."

One factual detail to update in the issue text: this repo currently uses Elasticsearch 8.17.0 in Docker, not 7.17.