Hacker News Reader

Postgres allows you to create an index on keys within a JSONB column[0]. It may be the case that Django doesn't offer support for this but I would expect you can probably get around that with raw SQL.

[0]: https://www.postgresql.org/docs/current/datatype-json.html#J...

View on HN

In addition to raw SQL, postgres JSON fields can be indexed with GinIndex https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/i...

Yeah I've not had too much luck with GINIndex. Firstly I can't rule out user error but they just didn't work well. Maybe it was the volume (and the index generation wasn't keeping up with the ingress of data —"only" around 2w/s— or it's the 50M rows...

This was WORM data so denormalising on write didn't need update triggers, and was the difference between 100ms and 10s queries. That was still too slow for us so we started caching expected data outside postgres.

Reply on news.ycombinator.com