Mysql vs postgresql in 2022

8

Although we were on MySQL (and later MariaDB) for many years, about a year ago migrated to postgreSQL (This was after an upgrade and move to a new VM.)

  • Full text indexing sucked with MySQL and sucked harder with MariaDB.

After upgrade, it did not work very well for us and made searching for things cumbersome. For example:

  • Searching for an IP address in the body of tickets, “192.168.1.100” would return everything matching “192” or “168” or “1” or “100.” Obviously useless.
  • Circuit numbers or another reference like “ABC-54321” returns everything with “ABC” or “54321”. Suppose you have a ton of circuit references beginning “ABC-” in install and trouble tickets, dating back many many years. Now you just found all of them. Great.

With mariadb, you had to remember to put things in single quotes to get RT to search correctly:

From RT 4.43 - Full Text Indexing

Caveats

  • Searching is done in “boolean mode.” As such, the TicketSQL query Content LIKE 'winter 2014' will return tickets with transactions that contain either word. To find transactions which contain both (but not necessarily adjacent), use Content LIKE '+winter +2014' . To find transactions containing the precise phrase, use Content LIKE '"winter 2014" .

Then there were other issues preventing it working properly with the utf8mb4 charset updates etc.

There are also now enough differences between MySQL and MariaDB for it to become annoying. (Reserved words etc.)

Postgres 8.3 and above support full-text searching natively. Set it up. Just Works.

I found this useful guide in the wiki on the process of migrating RT from MySQL to postgreSQL. I thought it was going to be a huge pain, but actually not too difficult with the RT rt-serializer tool.

I stepped though the process a couple of times with a copy of the database on a test install of RT. I encountered a couple of issues - I updated that wiki page once I’d got it working. (Thanks to whoever wrote that wiki page, it was super useful!)

2 Likes