Searching a Flask-SQLAlchemy database efficiently

Question

Currently, I am running the following code:

User.query.filter(or_(User.username.contains(query), User.name.contains(query)))

But naturally, when you're hitting a few million records and searching multiple columns the LIKE "%query%" starts to get expensive. Whoosh is heavily outdated, and sqlalchemy-fulltext-search does not seem to want to work with my Flask structures. Do I have any options left to consider, other than a switch to PostgreSQL?

Somewhat

What is wrong with sqlalchemy-fulltext-search? – Masonite

checkout elasticsearch for (almost)real time query and efficiently searching: elastic.co – Misdemean

There isn't much to go on here, but if you're scanning millions of records then it's critical that the relevant columns are indexed, and indexed with the right type of index. – Hereto

Answer 1

Flask_sqlalchemy is your ORM not your DBMS. You can use it with PostgreSQL as your database.
How you query your database is what matters in your case.

1-use sqlalchemy core

SQLAlchemy lets you execute raw SQL commands so you can write your query commands yourself rather than using Model.query.filter().
So one option is to write your select expressions yourself and more efficiently.

2-ELK

elastic search abstracts lots of efficient algorithms for you to use! you can rely on elasticsearch for searching your database.

Misdemean