Showdown: MySQL 8 vs PostgreSQL 10 https://blog.dumper.io/showdown-mysql-8-vs-postgresql-10/
@Gargron we should migrate to sqlite3 in a ramdisk...
@Gargron I have not looked at the code on that, but do you update your tsvector only on updates on one of those 3 fields or do you rely on the rails ORM for that ?
I would guess that those 3 fields don't change that often no ?
On that note, as an experience point, I do have a tsv index on the statuses table, it is updated by a trigger that checks if old.text != new.text and even if the trigger is one of the most called function overall ( almost a tie with the timestamp one ), that check allows for a very light overhead.
I would not do a new table for that, although I see why you think about it, and it's not completely unreasonable.
I would rather either trigger base it or just do it in code bypassing rails orm.
You may also want to study the gem pg_search: https://www.viget.com/articles/implementing-full-text-search-in-rails-with-postgres/
I took a quick look but for the need here, a raw query was best..
@gled We used pg_search for account search at the start, I removed that dependency
Also, well, you have to switch to the SQL format for db/schema file to use triggers, don't you?
@Gargron
I'm interested in your feedback about the removal ( I'll have to consider that gem for another project soon ).
No you don't, well afaik ofc, just create your trigger in a migration and it should be fine. ( Although I have created it directly in db here, I have installed it through a migration on another instance ).
The trigger looks like that ( from the top of my head ):
CREATE TRIGGER updatetsv BEFORE UPDATE ON statuses FOR EACH ROW WHEN (old.text IS DISTINCT FROM new.text) EXECUTE PROCEDURE update_tsv_statuses();
@gled I had a vague understanding of postgres internals but nice to have it all laid down like this. The UPDATEs thing is a bummer, UPDATEs on the accounts column are always slow because of the full-text index on username/domain/display_name
A potential solution is creating a new table to only store the index and a back-reference, it would only be updated if display_name is changed. (This was also the proposed solution for statuses, except that psql doesn't have stemming)