Skip to content

Native vs Amgix Full-Text Search Performance on PostgreSQL and MariaDB

Search Latency Across SQL Backend Configurations

Amgix works with your choice of a backend: PostgreSQL, MariaDB, or Qdrant. Each of those backends comes with its own built-in Full-Text Search (FTS). In this post we will focus on how search relevance and performance compare between SQL native FTS and Amgix on the same backends.

Before we ran the tests we made a couple of assumptions:

  • Native FTS will always be faster.
  • Amgix relevance should be a little better.

We were wrong on both counts: not always and not a little.

Info

There is an earlier Amgix benchmarks document that compares WMTR and Hybrid search across supported backends.

TL;DR in Charts

Dataset PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
SciFact 0.5779 0.5329 0.6893 0.6894
ArguAna 0.1283 0.1437 0.5034 0.5035
TREC-COVID 0.4887 0.3795 0.6407 0.6407
Quora 0.5827 0.4410 0.7830 0.7830
Amazon ESCI 0.1992 0.0411 0.2576 0.2576
Dataset PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
SciFact 7.7 7.6 28.1 32.6
ArguAna 105.6 196.7 35.7 43.1
TREC-COVID 178.5 393.5 91.5 96.3
Quora 10.4 177.2 51.3 69.8
Amazon ESCI 305.8 363.4 127.0 160.4

Test Setup

Expand collapsed sections for details:

Hardware

All tests are performed on a single bare-metal machine with the following specifications:

  • CPU: AMD Ryzen™ 9 5900X × 12 cores (24 threads)
  • RAM: 64GB
  • GPU: NVIDIA GeForce RTX 5060 Ti (16GB)
  • Storage: SSD
  • OS: Ubuntu 24.04.4 LTS
Server Versions

The following server versions were used for these tests:

  • PostgreSQL: 18
  • MariaDB: 11.8
  • Amgix: 1.6.4
Server Configurations

All servers ran in Docker containers on the same machine. No CPU or memory constraints were applied to any containers.

The following parameters were set on the server instance:

shared_buffers = 4GB
work_mem = 256MB
maintenance_work_mem = 512MB
effective_cache_size = 8GB
max_parallel_workers_per_gather = 8
max_parallel_workers = 8

Table definition:

CREATE TABLE "<table-name>" (
    doc_id VARCHAR(128) PRIMARY KEY,
    text TEXT NOT NULL,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(text, ''))
    ) STORED
);

CREATE INDEX "idx_<table-name>_search"
    ON "<table-name>" USING GIN (search_vector);

Query:

SELECT doc_id,
        ts_rank(search_vector, query) AS rank
FROM "<table-name>",
        to_tsquery(
            'english',
            replace(plainto_tsquery('english', '<query>')::text, ' & ', ' | ')
        ) query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 100

The following parameters were set on the server instance:

innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
tmp_table_size=256MB
max_heap_table_size=256MB
innodb_ft_cache_size = 4G
innodb_ft_total_cache_size = 8G

Table definition:

CREATE TABLE `<table-name>` (
    doc_id VARCHAR(128) NOT NULL,
    text LONGTEXT NOT NULL,
    PRIMARY KEY (doc_id),
    FULLTEXT INDEX `ft_<table-name>_text` (text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Query:

SELECT doc_id,
        MATCH(`text`) AGAINST ('<query>' IN NATURAL LANGUAGE MODE) AS `rank`
FROM `<table-name>`
WHERE MATCH(`text`) AGAINST ('<query>' IN NATURAL LANGUAGE MODE)
ORDER BY `rank` DESC
LIMIT 100

Amgix was configured as a small cluster: one api node, one index node, and one query node.

Test collections were configured as follows:

{
    "store_content": false,
    "vectors": [
        {"name": "full_text", "type": "full_text", "index_fields": ["content"], "top_k": 10000}
    ]
}

top_k on full_text vector was changed from default 128 to the maximum of 10000 to capture all the available tokens regardless of the text length. This way we prevented any vector truncation on longer text entries. We felt it was a fairer apples-to-apples comparison as SQL FTS does not truncate terms, as far as we know.

Data Configuration

For all the datasets, title and text fields were concatenated into a single text field. This was done to avoid any fusion math differences (RRF vs sum of scores, etc.)

Queries

Queries were executed in parallel, 4 at a time, to simulate minimal concurrent search load.

Results

BEIR SciFact Dataset (5K Documents)

PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
nDCG@1 0.4467 0.3667 0.5467 0.5467
nDCG@10 0.5779 0.5329 0.6893 0.6894
Recall@10 0.7177 0.7143 0.8303 0.8303
p50 (ms) 7.7 7.6 28.1 32.6
p95 (ms) 16.5 18.9 35.5 42.1

BEIR ArguAna Dataset (8K Documents)

PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
nDCG@1 0.0448 0.0505 0.2560 0.2560
nDCG@10 0.1283 0.1437 0.5034 0.5035
Recall@10 0.2354 0.2717 0.7930 0.7930
p50 (ms) 105.6 196.7 35.7 43.1
p95 (ms) 214.6 435.9 51.9 62.1

BEIR TREC-COVID Dataset (171K Documents)

PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
nDCG@1 0.5800 0.3200 0.7800 0.7800
nDCG@10 0.4887 0.3795 0.6407 0.6407
Recall@10 0.0117 0.0105 0.0177 0.0177
p50 (ms) 178.5 393.5 91.5 96.3
p95 (ms) 419.0 769.9 111.9 113.0

BEIR Quora Dataset (523K Documents)

PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
nDCG@1 0.4697 0.2719 0.6892 0.6892
nDCG@10 0.5827 0.4410 0.7830 0.7830
Recall@10 0.7095 0.6292 0.8835 0.8835
p50 (ms) 10.4 177.2 51.3 69.8
p95 (ms) 38.1 508.9 72.7 91.5

Amazon ESCI Dataset (1.2M Documents)

PostgreSQL MariaDB Amgix (PostgreSQL) Amgix (MariaDB)
nDCG@1 0.2343 0.0408 0.3003 0.3005
nDCG@10 0.1992 0.0411 0.2576 0.2576
Recall@10 0.1830 0.0399 0.2394 0.2394
p50 (ms) 305.8 363.4 127.0 160.4
p95 (ms) 1112.7 2549.3 352.8 320.5

Discussion

Relevance

Across all the tested datasets Amgix showed higher relevancy scores. This was expected, as Amgix implements BM25 scoring and built-in SQL Full-Text Search implementations do not, or at least not fully. However, we didn't expect the differences to be so large. On ArguAna, for example, both PostgreSQL and MariaDB scored about 3 times lower than Amgix and on Amazon ESCI dataset, MariaDB scores completely collapsed. PostgreSQL FTS scored higher than MariaDB on all but one (ArguAna) dataset.

Latencies

Latency results also surprised us. We assumed that native FTS implementations will surely outperform Amgix approach. But MariaDB FTS only performed better on SciFact dataset, where the dataset is small and documents are short. PostgreSQL was faster on SciFact and Quora. Both datasets have short documents. It seems that PostgreSQL implementation is highly sensitive to the length of documents, more so than the size of the corpus. Amgix p50 was under 100ms on all but the largest 1.2M Amazon dataset, where it still delivered results way under 300ms and about 2 times faster than either SQL native FTS.

Conclusions

If relevance of keyword search is important to you, choosing an implementation with BM25 scoring is paramount. Built-in FTS implementations are not nearly as good at finding the relevant documents. Even if you are doing hybrid (keyword + semantic) search, poor keyword relevance can bring your overall hybrid relevance scores down, often below those of just semantic search alone (see Is Keyword Search Dragging Your Hybrid Relevance Down?).

If search query performance is important and you have a larger dataset with longer documents, the answer is the same: a dedicated search engine will likely outperform the native implementations, sometimes by a large margin.

Built-in SQL FTS offers convenience of staying within the same database engine, not having to install and maintain external systems and keeping your data in sync with them.

There are many dedicated search engines out there, some (not all) implement proper BM25 scoring. What is different about Amgix is that it allows you to keep your search data in the same SQL database (or different database on the same server), so you don't have to run and maintain proprietary third-party data stores. Amgix also comes with built-in asynchronous ingestion pipelines (synchronous endpoints are also available), automatic model embedding, timestamp deduplication, intelligent retry strategies and flexible cloud-native deployment options.