3 ms·

Take a standard M-M join where an `Item` can belong to many `Lists`. select l.name, i.name from list as l inner join list_items as li on li.list_id = l

Take a standard M-M join where an `Item` can belong to many `Lists`. select l.name, i.name from list as l inner join list_items as li on li.list_id = l.id inner join items as i on i.id = li.item_id where items.completed = true Imagine it's like a collaborative bookmarking app. Your options are basically: 1. Polling - re-run query periodically 2. Monitor changes to tables using triggers or CDC -- 2a. Re-run entire query when any table is modified with DML and diff against previous result -- 2b. Only re-run query if the changes would effect the result (e.g. if we add an `item` with `item.completed = false` then we know that our above query will never need updating.) -- 2c. Do an efficient diff using some relational algebra magic. See Postgres IVM link I posted. I posted the same example [here][1] with some more details. [1]: https://math.stackexchange.com/questions/4112326/how-can-i-use-relational-algebra-to-determine-when-changes-to-a-relation-invalid https://math.stackexchange.com/questions/4112326/how-can-i-u...

eurasiantiger 5y ago
A graph database would be a good fit for many-to-many data. In that biosphere, OrientDB has the concept of Live Queries.
gt565k 5y ago
What exactly are you trying to solve/fix? If you want to improve performance in an application, structure it in a way that lets you cache the result set in-memory at the app layer. Assuming the only way to update data is through the same app, refresh/rehydrate the related cache when records are updated/deleted/inserted. You'll only hit the database the first time, and then again when the cache is cleared in your app layer logic. This is more or less how Active Record ORM caching in Rails works. You gotta key off of something so it is very application specific but lets take your example a step further. The items and list are for a specific user. Whenever an item gets added to a list or a list gets added to an item, nuke the cache key for 'user/$ID/list_items' or whatever you want to call it. Whenever you read, you hit the same cache key in-memory, so your reads are always avoiding a database roundtrip. You can do the same logic for deletes/updates, etc... and structure based on the hierarchy of your data or your tree dependency of records

eurasiantiger 5y ago

A graph database would be a good fit for many-to-many data. In that biosphere, OrientDB has the concept of Live Queries.

gt565k 5y ago

What exactly are you trying to solve/fix? If you want to improve performance in an application, structure it in a way that lets you cache the result set in-memory at the app layer. Assuming the only way to update data is through the same app, refresh/rehydrate the related cache when records are updated/deleted/inserted. You'll only hit the database the first time, and then again when the cache is cleared in your app layer logic. This is more or less how Active Record ORM caching in Rails works. You gotta key off of something so it is very application specific but lets take your example a step further. The items and list are for a specific user. Whenever an item gets added to a list or a list gets added to an item, nuke the cache key for 'user/$ID/list_items' or whatever you want to call it. Whenever you read, you hit the same cache key in-memory, so your reads are always avoiding a database roundtrip. You can do the same logic for deletes/updates, etc... and structure based on the hierarchy of your data or your tree dependency of records