Virtual generated columns are not required to allow an index to be used in this case without incurring the cost of materializing `to_tsvector('english', message)`. Postgres supports indexing expressions and the query planner is smart enough to identify candidate on exact matches.

I'm not sure why the author doesn't use them but it's clearly pointed out in the documentation (https://www.postgresql.org/docs/current/textsearch-tables.ht...).

In other words, I believe they didn't need a `message_tsvector` column and creating an index of the form

  CREATE INDEX idx_gin_logs_message_tsvector
  ON benchmark_logs USING GIN (to_tsvector('english', message))
  WITH (fastupdate = off);

would have allowed queries of the form

  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')

to use the `idx_gin_logs_message_tsvector` index without materializing `to_tsvector('english', message)` on disk outside of the index.

Here's a fiddle supporting it https://dbfiddle.uk/aSFjXJWz

You are correct, I missed that. In MySQL, functional indices are implemented as invisible generated virtual columns (and there is no vector index type supported yet that I'm aware of), but Postgres has a more capable approach.

TIL I wasn't aware MySQL functional indices were implemented using virtual columns [0]

[0] https://dev.mysql.com/doc/refman/8.4/en/create-index.html#cr...

I had the same question when reading the article, why not just index the expression?

Small heads up that the application link in the job description pointing at https://opencraft.com/jobs/open-source-developer/ leads to a 404.

Thank you! Fixed - we have updated our site recently, and the link had been changed in the other places, but not in that document.

There is an ongoing effort to include back the equivalent in core [here](https://github.com/django/django/pull/17554)

It doesn't make the article less interesting but this statement is wrong most of the time

> This scenario is essentially identical to one in which there's a single migration statement that requires an `AccessExclusiveLock` and performs a table rewrite, such as adding a NOT NULL column with a DEFAULT value.

Unless the `DEFAULT` value is `VOLATILE` (this is rarely the case as usually default values are at least `STABLE`) no table rewrite is necessary since Postgres 11.

https://www.postgresql.org/docs/devel/sql-altertable.html

> Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default

https://www.postgresql.org/docs/release/11.0/

Thanks, I will fix this part. As far as I can tell, it is only the example of what can cause the table rewrite that's wrong, so it would be correct if the example was to make a varchar shorter? Although I guess that's not a table rewrite, only a long validation...