Easier full-text search possibility (#4505)

* Easier full-text search possibility
Contributes to https://github.com/FreshRSS/FreshRSS/issues/1331
Avoid concats in searches to make text indexes easier to build

* Fix tests

* Documentation

3 files changed, 38 insertions, 0 deletions

diff --git a/docs/en/admins/01_Index.md b/docs/en/admins/01_Index.md
index 749d44af3..792434389 100644
--- a/docs/en/admins/01_Index.md
+++ b/docs/en/admins/01_Index.md
@@ -18,6 +18,7 @@ Learn how to install, update, and backup FreshRSS, as well as how to use the com
 * [Setting Up Automatic Feed Updating](08_FeedUpdates.md)
 * [Access Control](09_AccessControl.md)
 * [Apache/Nginx configuration files](10_ServerConfig.md)
+* [Database configuration](DatabaseConfig.md)
 * [Using the command line interface (CLI)](https://github.com/FreshRSS/FreshRSS/tree/edge/cli)
 * [Configuring the email address validation](05_Configuring_email_validation.md)
 * [Frequently asked questions](04_Frequently_Asked_Questions.md)
diff --git a/docs/en/admins/DatabaseConfig.md b/docs/en/admins/DatabaseConfig.md
new file mode 100644
index 000000000..aa80a3975
--- /dev/null
+++ b/docs/en/admins/DatabaseConfig.md
@@ -0,0 +1,33 @@
+# Database configuration
+
+FreshRSS supports the databases SQLite (built-in), PostgreSQL, MySQL / MariaDB.
+
+While the default installation should be fine for most cases, additional tuning can be made.
+
+## Full-text search optimisation in PostgreSQL
+
+Without changing anything in FreshRSS’ code (which is using [`ILIKE`](https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE)), it is possible to make text searches much faster by adding some indexes in PostgreSQL 9.1+ (at the cost of more disc space and slower insertions):
+
+```sql
+CREATE EXTENSION pg_trgm;
+CREATE INDEX gin_trgm_index_title ON freshrss_entry USING gin(title gin_trgm_ops);
+CREATE INDEX gin_trgm_index_content ON freshrss_entry USING gin(content gin_trgm_ops);
+```
+
+Where `freshrss_entry` needs to be adapted to the name of the *entry* of a given use, e.g., `freshrss_alice_entry`.
+
+Such an index on the `entry.title` column makes searches such as `intitle:Hello` much faster.
+General searches such as `Something` search both on `entry.title` and `entry.content` and therefore require the two indexes shown above.
+
+Likewise, if you wanted to speed up searches on the authors (`author:Alice`), you would add another index:
+
+```sql
+CREATE INDEX gin_trgm_index_author ON freshrss_entry USING gin(author gin_trgm_ops);
+```
+
+Etc. for other text fields. The list of fields can be seen in [`CREATE TABLE _entry` section](https://github.com/FreshRSS/FreshRSS/blob/edge/app/SQL/install.sql.pgsql.php).
+
+### References
+
+* [GIN: Generalized Inverted Index](https://www.postgresql.org/docs/current/gin-intro.html)
+* [`pg_trgm` module for fast text search](https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.42.8)
diff --git a/docs/en/developers/03_Backend/01_Database_schema.md b/docs/en/developers/03_Backend/01_Database_schema.md
index b2206e46a..280f3079b 100644
--- a/docs/en/developers/03_Backend/01_Database_schema.md
+++ b/docs/en/developers/03_Backend/01_Database_schema.md
@@ -1,3 +1,7 @@
 # Database Schema
 
 > **TODO**
+
+## See also
+
+* [Database configuration](../../admins/DatabaseConfig.md)