3 ms·
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
by fiddlerwoaroof 2y ago
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.
- nine_k 2y agoYou can just build an index with a condition, without even.partitioning the table.
- fiddlerwoaroof 2y agoExcept, the index doesn't help with avoiding the WHERE clause
- dqv 2y agoYeah, 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 https://hexdocs.pm/ecto/Ecto.Repo.html#c:prepare_query/3
- fiddlerwoaroof 2y agoNow I have two problems
- dqv 2y agoYou 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.
- hamandcheese 2y agoThe 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.
- hamandcheese 2y agoThis is a very interesting idea. I didn't know you could query a partition directly!