Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
1.8 Fulltext Search Support for Postgres
#1
MyBB only has rudimentary postgres support. This takes a significant step at making it a first class citizen. 

Patch: http://pastebin.irixnet.org/view.php?id=klTaH5Jl

Scope of Testing: 

Tested on a clean MyBB 1.8.x install. 

Might require more extensive teaching. I don't have any Postgres systems to test on in production. 

Patch touches the following:
  1. inc/functions_search.php    - Two new functions appended
  2. search.php                  - Dispatch logic updated (2 locations)
  3. inc/db/PostgresPdoDbDriver.php - Five methods updated/added

New Setting (optional):
  pgsql_fts_config - Text search configuration name (default: 'english')
                    Set to 'simple', 'german', 'french', etc. for
                    non-English forums.

Required Database Indices:
  CREATE INDEX idx_posts_message_fts ON mybb_posts
    USING gin(to_tsvector('english', message));
  CREATE INDEX idx_posts_subject_fts ON mybb_posts
    USING gin(to_tsvector('english', subject));
  CREATE INDEX idx_threads_subject_fts ON mybb_threads
    USING gin(to_tsvector('english', subject));

User Input              MySQL Boolean FT    PgSQL tsquery
hello world            | +hello +world            | 'hello' & 'world'
hello or world          | hello world              | 'hello' | 'world'
hello and world        | +hello +world            | 'hello' & 'world'
not spam                | -spam                    | !'spam'
hello -spam            | +hello -spam            | 'hello' & !'spam'
"exact phrase"          | +"exact phrase"          | ('exact' <-> 'phrase')
test*                  | +test*                  | 'test':*
"quick fox" and lazy    | +"quick fox" +lazy      | ('quick' <-> 'fox') & 'lazy'
cat or dog and fish    | cat +dog +fish          | 'cat' | 'dog' & 'fish'

Optionally I can port this to 1.9 -- but I'd rather first have people test it.
Reply
#2
Great work! Any idea what the core developers think about this? Would they be open to merging it?
MyBB Türkçe Destek Sitesi: https://mybb.tr

[Image: mysksxl.gif]

Please! Do not request support via private message unless I give permission. If you want paid consulting, you can contact us.
Reply
#3
Dunno. I wrote it for 1.8 just to get more eyes/testing. I can rebase it for 1.9 if there's demand
Reply
#4
This will likely never be implemented for 1.8 as it is mostly feature froze. Feel free to post a PoC PR for 1.9

I personally would like a more in-depth refactoring of the search system for 1.10

From a quick look, I'm concerned that it seems to support only a single language while MyBB is designed as a multilingual platform.

Maybe @arcalys may offer better insight into this topic.

Regards.
Send me a DM or PM if you want to sponsor any update or addition to my plugins.

[Image: signature.png]

Discord omar.gonzalez (Omar G.#6117); Telegram at @omarugc;
Reply
#5
(2026-02-23, 08:45 AM)Omar G. Wrote: This will likely never be implemented for 1.8 as it is mostly feature froze. Feel free to post a PoC PR for 1.9

It wouldn't get much real-world testing if I wrote it for 1.9

It should be trivial to change for 1.9 -- it doesn't touch anything template-wise
(2026-02-23, 08:45 AM)Omar G. Wrote: From a quick look, I'm concerned that it seems to support only a single language while MyBB is designed as a multilingual platform.

Incorrect:

// Determine the text search configuration
+ $ts_config = 'english';
+ if(!empty($mybb->settings['pgsql_fts_config']))
+ {
+ $ts_config = preg_replace("#[^a-z0-9_]#i", '', $mybb->settings['pgsql_fts_config']);
+ if(empty($ts_config))
+ {
+ $ts_config = 'english';
+ }
+ }

It defaults to EN if there's no language set.
Reply
#6
You may provide a PoC PR for 1/8 then even if it doesn't get merged it may increase your reach for people to test.

(2026-02-23, 03:44 PM)Raion Wrote: Incorrect:

// Determine the text search configuration
+ $ts_config = 'english';
+ if(!empty($mybb->settings['pgsql_fts_config']))
+ {
+ $ts_config = preg_replace("#[^a-z0-9_]#i", '', $mybb->settings['pgsql_fts_config']);
+ if(empty($ts_config))
+ {
+ $ts_config = 'english';
+ }
+ }

It defaults to EN if there's no language set.

It is assuming the language being used for search or the content language is in a single (board set) language.

In a forum, content may be posted in multiple languages, that is how some boards operate and that is how I think MyBB is designed to assume. How does your patch handle this?

What happens if the setting is set to English and I type Spanish in the search bar and I know for a fact there is content in that language that should match ?

I'm asking because I obviously didn't test your patch, I'm just commenting on the superficial review of it.
Send me a DM or PM if you want to sponsor any update or addition to my plugins.

[Image: signature.png]

Discord omar.gonzalez (Omar G.#6117); Telegram at @omarugc;
Reply
#7
The ts_search option controls what stop words are used for a fulltext query. 

With english, words like "the", "a", "is" are dropped from the index and ignored in queries. The Spanish config drops Spanish stop words like "el", "de", "los" instead.

Same thing with stems (e.g. plurals etc)

This is moreorless identical to how the MySQL fulltext search system works. 

The recommendation for multilingual boards would be to set pgsql_fts_config = simple, which gives you language-agnostic fulltext search that works identically to how MySQL fulltext works for non-stemmed matches. You lose English stemming, but you gain correct behavior across all languages.

As for PRs, I don't use/have GitHub. So I can't submit a PR myself. I do not like Microsoft having data.
Reply
#8
On the multilingual aspect: using a single pgsql_fts_config per board (e.g. english) works for single-language forums. Setting it to simple avoids language-specific stopwords and stemming, so it won’t break with mixed-language content. But that’s more of a compatibility fallback than true multilingual support.

In PostgreSQL, more robust multilingual approaches usually involve:
  • combining configs in a single tsvector (e.g. weighted english + french).
  • storing language per row and generating the tsvector using that language.
Or explicitly using simple (optionally with unaccent) and accepting the tradeoff of no stemming. So simple is valid - but it’s a quality tradeoff, not equivalent to full multilingual handling.
-----
I’m also slightly concerned about implementing a custom tsquery parser in PHP when PostgreSQL already provides functions to turn user input into tsquery safely and robustly (e.g. websearch_to_tsquery, plainto_tsquery, phraseto_tsquery).

Instead of manually transforming user input into a handcrafted tsquery string, it would likely be cleaner and safer to rely on PostgreSQL’s built-in parsing, for example:

search_vector @@ websearch_to_tsquery('english', user_input)

That avoids duplicating parsing logic in PHP, reduces edge cases and malformed-query risks, and aligns better with PostgreSQL’s intended full-text search model

I’m definitely in favor of adding proper FTS support for PostgreSQL, I even opened an issue about it a couple of months ago. I just think this might benefit from a slightly deeper design discussion so we can phase it in cleanly and take advantage of PostgreSQL’s native capabilities.
Reply
#9
This was my first time attempting this
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)