Hi! On Sun, Dec 23, 2018 at 1:00 AM George Neuner <gneuner2@xxxxxxxxxxx> wrote: > A spreadsheet is an order of magnitude simpler to implement than a > DBMS, but the combination would be an order of magnitude (or more) > harder. Even object graph databases don't offer spreadsheet > functionality, and it would be a lot easier to do there than in a > table relational system. But having that readily available would be so cool. :-) Especially because it is hard. And also, it does not necessary have to be full graph. Just one level of dependencies. Then those are recomputed. And then anything depending on now changed values get recomputed again. And so on. So no need to traverse the whole graph at once. > In PG, all *named* tables are backed on disk - even temporary tables. > Only anonymous tables of query results can exist entirely in memory > [and even they can spill onto disk when necessary]. > > With enough memory you can cache all your tables in shared buffers and > have enough extra that you never run out of work buffers and never > overflow a work buffer. But that is the best you can achieve with PG. Thanks for all this input. So I am now thinking in terms of a materialized views. For my case it would be great if materialized views could be TEMPORARY (removed at the end of session), in-memory (and if PostgreSQL runs out of buffer space for it, I would prefer an error, instead of spilling to the disk), and UNLOGGED. Any particular reasons which would prevent them to be implemented as such? I through that materialized views are just a fancy table with stored query, so that you can easily REFRESH them, instead of doing that yourself. I can then wire triggers on underlying tables to REFRESH materialized views automatically. Is there some easy way to debounce those refreshes? If I just blindly trigger REFRESH in every row trigger, this could do a lot of work. I would prefer to combine all changes for example into 100 ms batches and refresh only once per 100 ms. So in some way I would like to be able to control the level of real-time I would need. I have found a blog post [1] about this, but it seems pretty tricky and requires an outside cron job. For 100 ms batching time this feels like something better done inside PostgreSQL itself. The last question is how do I get changes in materialized views streamed to the client. It seems one option is a trigger on the materialized view which uses NOTIFY to tell the client about the change. But NOTIFY has limit on the payload size, so I cannot just send the change to the client. It seems I would need additional table to store the change and then client would get notification, read from that table, and remove the rows read. So in a way I would need my own queue for changes. Any other suggestion how to do that? I looked into PUBLICATION and SUBSCRIBE, but it seems this is only supported between servers, not server-client, and also works only on base tables, not materialized views (not sure again why, because aren't materialized views just tables). Would it be possible to use client to subscribe instead of a server? [1] https://onethingsimple.com/2017/10/sync-materialized-views-after-debounce-period/ Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m