Mysql vs postgresql in 2022

1

Hi everyone.

At almost the end 2022 what are the best choice between postgresql and mysql for large installation of RT.

Long time ago with postgresql RT can use text search inside the ticket, something cannot do with standard mysql.

What are the recommandation currently ?

Regards.

2

I think for anyone to give a sensible comparison/recommendation answer to that they’d probably have to be running several large RT installations, each with a different database engine underneath it. Otherwise you’ll just get views from people who have only used one or the other (or Oracle!).

FWIW though, we’ve used MySQL for years and we’re approaching half a million tickets now (which I think is relatively chunky). Seems to have worked OK so far.

3

postgreSQL is seen as go to answer to perform muddled, high volume information tasks. this is what to regards to postgreSQL versus MySQL, the previous is better at taking care of remarkable info base cirmcumtances

4

Is My opinion Useful. Best Regards

5

I vote for PostgreSQL. Because latest version is not compatible with MySQL 8. Not compatible because MySQL now have reserved word Groups, but RT use this word as table name.

6

postgres is the more performant database in many cases, also for RT. But one have to administrate the system. So choose that DBMS you have knowledge or you want to build knowledge, maybe you need more personal for 24/7 support.

We do not use MySQL any more since it was bought by oracle. In MariaDB it is no reserved word. Our RT is driven by MariaDB cause the exisiting knowledge.

7

Currently I still use mysql (5.7). But I will keep in my mind to migrate to postgresql.

If I use (or still) mysql it’s not so easy to migrate from mysql to postgresql on a living application (critical like RT).

Thanks for the answer

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!)

1 Like