Hacker News Reader

I wonder if this could be solved with partitioning on the deleted_at column; you’d only need two partitions: one for live data (deleted_at is null) and one for deleted data. Then, in Postgres at least, you could just query the live partition directly when you want to skip deleted records.

View on HN

You can just build an index with a condition, without even.partitioning the table.

Except, the index doesn't help with avoiding the WHERE clause

Yeah, but Ecto does. You can implement the Repo.prepare_query/3 callback to make sure the query has WHERE deleted_at IS NOT NULL. In fact, that's the exact example in the documentation:

https://hexdocs.pm/ecto/Ecto.Repo.html#c:prepare_query/3

Now I have two problems

You don't have to reply to a comment if you don't really have anything else to say. What's the point of saying "Now I have two problems" without saying what the new problem is? So what is it? It can't be that an ORM has been added to the mix, since Ecto isn't an ORM.

The problem is you have to manage indexes in the database and keep that in sync with each application that queries it. Ecto is one of those applications.

This is a very interesting idea. I didn't know you could query a partition directly!

Reply on news.ycombinator.com