charettes

Joined 8 years ago

Karma

Submissions

Member since

Sep 17

View on Hacker News

Recent submissions

Comment · 8 months ago

There are valid reasons for not using an ORM but the points made in this article are plain false.

>“Roughly” because Django ORM doesn’t support the JSONB `?` operator.

The `has_key` [lookup](https://docs.djangoproject.com/en/6.0/topics/db/queries/#has...) does exactly that.

> And if you need real SQL intervals, Django pushes you towards raw expressions or `Func()` wrappers.

It's possible to use a very similar construct to SQL Alchemy here by using the `Now` [function](https://docs.djangoproject.com/en/6.0/ref/models/database-fu...) (it uses `STATEMENT_TIMESTAMP` which is likely more correct than `NOW()` here alternatively there is `TransactionNow`) by doing `Now() - timedelta(days=30)`.

The result is the following `filter` call

  filter(
      metadata__tags__has_key="python",
      created_at__gte=(
        Now() - timedelta(days=30)
      ),
   )

which translates to the following SQL

  ("app_video"."metadata" -> 'tags') ? 'python'
  AND "app_video"."created_at" >= (
    STATEMENT_TIMESTAMP() - '30 days'::interval
  )

which can be confirmed in [this playground](https://dryorm.xterm.info/hn-47110310)

Comment · 1 year ago

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...

Comment · 1 year ago

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

Comment · 1 year ago

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

Comment · 2 years ago

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

Comment · 2 years ago

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/