Soft deletion probably isn't worth it

Re:Soft deletion probably isn't worth it by firloop on Tuesday July 19, 2022 @03:17PM (#32156580)

Views can really bite you performance wise, at least with Postgres. If you add a WHERE against a query on a view, Postgres (edit: often) won't merge in your queries' predicates with the predicates of the view, often leading to large table scans.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by dafelst on Tuesday July 19, 2022 @03:19PM (#32156604)

IIRC Postgres has supported predicate push down on trivial views like this for over a decade now, and possibly even more complex views these days (I haven't kept up with the latest greatest changes).

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by firloop on Tuesday July 19, 2022 @03:21PM (#32156621)

Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by sarchertech on Tuesday July 19, 2022 @03:54PM (#32157068)

I haven’t had any problems with this at all and I’ve been using joins in my views for years.

Are you using CTEs in your views?

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by bavell on Tuesday July 19, 2022 @05:56PM (#32158494)

Postgres docs on CTEs: https://www.postgresql.org/docs/current/queries-with.html

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by tomnipotent on Tuesday July 19, 2022 @05:53PM (#32158470)

Postgres 12 fixed the CTE issue.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by AdrianB1 on Tuesday July 19, 2022 @06:03PM (#32158571)

There is no impact with views in MS SQL. You can also have indexed views and filtered indexes, so you can have even better performance.

[ Parent | on HN ]