Vector indexes, MariaDB & pgvector, large server, dbpedia-openai dataset
This post has results from ann-benchmarks to compare MariaDB and Postgres with a larger dataset, dbpedia-openai at 100k, 500k and 1M rows. It has 1536 dimensions and uses angular (cosine) as the distance metric. By larger I mean by the standards of what is in ann-benchmarks. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.
tl;dr
- Index create time was much less for MariaDB in all cases except the result for recall >= 0.95
- For a given recall, MariaDB gets between 2.1X and 2.7X more QPS than Postgres
Benchmark
This post has much more detail about my approach in general. I ran the benchmark for 1 session. I use ann-benchmarks via my fork of a fork of a fork at this commit. The ann-benchmarks config files are here for MariaDB and for Postgres.
I used a larger server (Hetzner ax162-s) with 48 cores, 128G of RAM, Ubuntu 22.04 and HW RAID 10 using 2 NVMe devices. The database configuration files are here for MariaDB and for Postgres. Output from the benchmark is here.
I had ps and vmstat running during the benchmark and confirmed there weren't storage reads as the table and index were cached by MariaDB and Postgres.
The command lines to run the benchmark using my helper scripts are:
bash rall.batch.sh v1 dbpedia-openai-100k-angular c32r128
The command lines to run the benchmark using my helper scripts are:
bash rall.batch.sh v1 dbpedia-openai-100k-angular c32r128
bash rall.batch.sh v1 dbpedia-openai-500k-angular c32r128
bash rall.batch.sh v1 dbpedia-openai-1000k-angular c32r128
Results: QPS vs recall
These charts show the best QPS for a given recall. MariaDB gets about 2X more QPS than Postgres for a specific recall level
With 100k rows
With 500k rows
With 1M rows
Results: create index
The database configs for Postgres and MariaDB are shared above, and parallel index create is disabled by the config for Postgres and not supported yet by MariaDB. The summary is:
- index sizes are similar between MariaDB and pgvector with halfvec
- time to create the index varies a lot and it is better to consider this in the context of recall which is done in next section
Legend
- M - value for M when creating the index
- cons - value for ef_construction when creating the index
- secs - time in seconds to create the index
- size(MB) - index size in MB
Table sizes:
* Postgres is 7734M
* MariaDB is 7856M
-- pgvector -- -- pgevector --
-- float32 -- -- halfvec --
M cons secs size(MB) secs size(MB)
8 32 458 7734 402 3867
16 32 720 7734 655 3867
8 64 699 7734 627 3867
16 64 1144 7734 1029 3867
32 64 2033 7734 1880 3867
8 96 934 7734 843 3867
16 96 1537 7734 1382 3867
32 96 2730 7734 2482 3867
48 96 4039 7734 3725 3867
8 192 1606 7734 1409 3867
16 192 2778 7734 2435 3867
32 192 4683 7734 4154 3867
48 192 6830 7734 6106 3867
64 192 8601 7734 7831 3958
8 256 2028 7734 1764 3867
16 256 3609 7734 3151 3867
32 256 5838 7734 5056 3867
48 256 8224 7734 7283 3867
64 256 11031 7734 9931 3957
mariadb
M secs size(MB)
4 318 3976
5 372 3976
6 465 3976
8 717 3976
12 1550 3976
16 2887 3976
24 7248 3976
32 14120 3976
48 36697 3980
Results: best QPS for a given recall
Many benchmark results are marketed via peak performance (max throughput or min response time) but these are usually constrained optimization problems -- determine peak performance that satisfies some SLA. And the SLA might be response time or efficiency (cost).
With ann-benchmarks the constraint is recall. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16/halfvec).
With ann-benchmarks the constraint is recall. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16/halfvec).
Summary
- Postgres does not get recall=1.0 for the values of M, ef_construction and ef_search I used
- Index create time was much less for MariaDB in all cases except the result for recall >= 0.95
- For a given recall target, MariaDB gets between 2.1X and 2.7X more QPS than Postgres
Legend:
- recall, QPS - best QPS at that recall
- isecs - time to create the index in seconds
- m= - value for M when creating the index
- ef_cons= - value for ef_construction when creating the index
- ef_search= - value for ef_search when running queries
Best QPS with recall >= 1.000, pgvector did not reach the recall target
recall QPS isecs
1.000 20 36697 MariaDB(m=48, ef_search=40)
Best QPS with recall >= 0.99, MariaDB gets >= 2.2X more QPS than Postgres
recall QPS isecs
0.990 287 8224 PGVector(m=48, ef_cons=256, ef_search=40)
0.990 321 7283 PGVector_halfvec(m=48, ef_cons=256, ef_search=40)
0.992 731 7248 MariaDB(m=24, ef_search=10)
Best QPS with recall >= 0.98, MariaDB gets >= 2.7X more QPS than Postgres
recall QPS isecs
0.984 375 4683 PGVector(m=32, ef_cons=192, ef_search=40)
0.984 418 4154 PGVector_halfvec(m=32, ef_cons=192, ef_search=40)
0.981 1130 2887 MariaDB(m=16, ef_search=10)
Best QPS with recall >= 0.97, MariaDB gets >= 2.3X more QPS than Postgres
recall QPS isecs
0.974 440 6830 PGVector(m=48, ef_cons=192, ef_search=20)
0.973 483 6106 PGVector_halfvec(m=48, ef_cons=192, ef_search=20)
0.981 1130 2887 MariaDB(m=16, ef_search=10)
Best QPS with recall >= 0.96, MariaDB gets >= 2.2X more QPS than Postgres
recall QPS isecs
0.962 568 4683 PGVector(m=32, ef_cons=192, ef_search=20)
0.961 635 4154 PGVector_halfvec(m=32, ef_cons=192, ef_search=20)
0.965 1433 1550 MariaDB(m=12, ef_search=10)
Best QPS with recall >= 0.95, MariaDB gets >= 2.1X more QPS
recall QPS isecs
0.953 588 2730 PGVector(m=32, ef_cons=96, ef_search=20)
0.957 662 1382 PGVector_halfvec(m=16, ef_cons=96, ef_search=40)
0.965 1433 1550 MariaDB(m=12, ef_search=10)