4 ms·

> pgBackRest is probably strictly better than wal-g for our use-case, but I already knew wal-g was at least satisfactory in solving our problems (from previous

> pgBackRest is probably strictly better than wal-g for our use-case, but I already knew wal-g was at least satisfactory in solving our problems (from previous usage in implementing something Heroku-Dataclips-alike before), so I threw it on and filed an issue for doing a compare-and-contrast down the line. So far it hasn't caused too many issues, and does indeed solve our main issue: allowing truly-async replication (e.g. replication to DCs in other countries, where the link has a huge RTT, high potential packet loss, and might even go down for hours at a time due to, say, politics — without that causing any kind of master-side buffering or slowdown.) I always thought that dataclips was just them running on ZFS but honestly this would make more sense. I don't use Heroku much so I didn't see any limitations that jumped out to me as "oh they have that limitation because they're doing pgbackrest" but. > Not yet, but we've certainly been considering it. Probably something we'll do when we have a bit more breathing room (i.e. when we get out from under all the scaling we're doing right now.) Yup sounds reasonable, I certainly am not a customer (nor in a position to be one really, as I don't do any blockchain) so satisfying me brings you almost negative ROI. Scaling sounds fun in and of itself (when it isn't terrifying though) -- thanks for sharing so much about the setup! > Nothing like that per se (though it'd very much be a data-layer match for how we're doing infra management using k8s "migrations" through GitOps. Imagine SQL schema-definition k8s CRDs.) Now this is fascinating -- I'm also very much sold on k8s and SQL-schemas-as-CRDs sounds like it'd be relatively easy to implement and very high ROI. Migrations were always a sort of weird infrastructure-mixed-with-dev thing, and being able to tie the CRDs for migrations to the StatefulSet running the DB and the Deployment running the application seems like it could open up a whole new world of more intelligent deployment. cool stuff. > But that's mostly because — to our constant surprise — our PG cluster's query plans improve the more we normalize our data. We hardly build matviews or other kinds of denormalized data representations at all (and when we do, we almost always eventually figure out a partial computed-expression GIN index or something of the sort we can apply instead.) I don't know how people use open source DBs that are not postgres. I mean I do, but I can't wait until zheap removes the most common reason for people choosing MySQL to begin with. > We are planning to offer our users something like dbt, and we may or may not use dbt under the covers to manage it. (Analogous to how GitHub created their own git library to use in their Rails backend.) dbt does look pretty good, it seems like just enough structure. I do want to also bring up ddbt[0] if ya'll do some research/exploratory sprint on it. > One thing we do want to enable is avoiding the recomputation of intermediate "steps" within long chained CTE queries (imagine: taking 10 seconds to compute A, and then using it in a join to quickly select some Bs; then, in another query, taking another 10 seconds to compute A again, because now you need to select Cs instead), without keeping around any sort of persistent explicitly-materialized intermediate, or writing ugly procedural logic. https://materialize.com/ https://materialize.com/ seems promising for that. (Just wish it was a library I could shove inside PG, rather than an external system that is apparently going to grow to having its own storage engine et al.) Interesting -- would Incremental View Maintenance[2] work for this? Don't know if this is useful to you but the dbt conference I got sucked into watching most of had a talk with the materialize CEO and some other people. I don't remember it being particularly high signal (I mean let's be real it's basically a 3-way ad for the CEO involved) but it might be worth a watch[1]. > We've always mentioned to people that Tableau has first-class support for connecting to/querying Postgres. Given the type of data we deal with, that's usually all they need to know. :) I really underestimate how much Tableau is the standard (because I'm not a data person probably) -- basically between that, looker and PowerBI I'm not sure people use much else, even if it's open source/free. [0]: https://github.com/monzo/ddbt https://github.com/monzo/ddbt [1]: https://www.youtube.com/watch?v=0DDNjB4O0As https://www.youtube.com/watch?v=0DDNjB4O0As [2]: https://github.com/sraoss/pgsql-ivm https://github.com/sraoss/pgsql-ivm

derefr 5y ago
> Incremental View Maintenance It’s nice to have that as an automatic feature rather than something that you have to create for yourself with stored procedures (doing e.g. dynamic programming in the DB), but a matview is still a matview — it’s a real table taking up space on disk and getting replicated through the WAL. The thing Materialize gives you is the same thing you get from Apache Beam nee Google Cloud Dataflow at cluster scale, or from Elixir’s Flow / Java’s Akka Streams at the in-process scale on the business layer. Namely, the ability to have one stream node in a flow digraph that feeds N outputs that consume it at different rates, and to keep just enough of the intermediate stream node materialized in a buffer to keep all those readers served. Or: like the kind of ACK-truncated buffer that serves a Kafka/Redis Streams consumer group, but with all the stages in the same address-space without serialization+remoting overhead in-between. This “garbage-collected buffered materialization” is a very important property, if your A query returns a million A IDs, and you’re doing a LEFT JOIN from Bs to As that’s dropping most of the As because they don’t have a related B. You don’t want to be holding onto — and replicating! — a thousand concurrent-request copies of that million A ID list. (The million A IDs are also why you’re doing CTEs in your B/C queries in the first place. With a small list of A IDs, it’d be sensible to just round-trip the A ID list to the client, getting sent back as IN (VALUES ...) bind-params for the B/C queries. But 1. naively, those million IDs become a million bind-params, and that’s stupid, slow, and actually impossible [PG only accepts 65K bind-params per statement!]; and 2. trying to pass the fetched list as an A.id[] and using <@ will deoptimize the search.) The closest thing PG has is the ability to BEGIN a transaction; create a TEMPORARY table for that transaction; SELECT INTO the temporary table the result of a query; and then use that temporary table to run two queries. Which you have to do in PL/pgSQL (losing the benefits of “whole program” query-plan optimization you get from CTE queries); and then you still have to do something at the end to get your two queries' differently-shaped outputs back to the user. (For example, encoding every row into JSONB with an explicit embedded row-type-tag, and then double-decoding at the receiver; materializing the results of the queries into two more UNLOGGED [but not TEMPORARY] tables, fetching the results from them in two more queries, and then dropping them; etc.)
- hardwaresofton 5y ago
  > It’s nice to have that as an automatic feature rather than something that you have to create for yourself with stored procedures (doing e.g. dynamic programming in the DB), but a matview is still a matview — it’s a real table taking up space on disk and getting replicated through the WAL. Ah, yeah at the end of the day both unlogged materialized views aren't supported by postgres[0]. Also a patch that introduces temporary materialized views is stuck behind some refactoring that started in 2019[1] which is unfortunate. > (explanation of materialize) Thanks for this -- I think I get more clearly what you're getting out of materialize. I'm not sure I quite understand the stream analogy because the data being buffered here feels chunked (though I guess it could be incrementally maintained with some more smarts), but I think I understand the issue that basically ongoing queries need access to this temporarily-useful partial result. Basically a cross-session intermediate query result cache. This feels like it could be reduced to a custom table access method plugin, but if that temporary table patch gets merged it could be even easier to hack something together. [0]: https://www.postgresql-archive.org/CREATE-UNLOGGED-MATERIALIZED-VIEW-td6064079.html https://www.postgresql-archive.org/CREATE-UNLOGGED-MATERIALI... [1]: https://www.postgresql.org/message-id/CAKLmikMgkuwFKHem%2BSfBb_G7J01fzTvPJyG9dVOOgVCuakCDCA%40mail.gmail.com https://www.postgresql.org/message-id/CAKLmikMgkuwFKHem%2BSf...

derefr 5y ago

> Incremental View Maintenance It’s nice to have that as an automatic feature rather than something that you have to create for yourself with stored procedures (doing e.g. dynamic programming in the DB), but a matview is still a matview — it’s a real table taking up space on disk and getting replicated through the WAL. The thing Materialize gives you is the same thing you get from Apache Beam nee Google Cloud Dataflow at cluster scale, or from Elixir’s Flow / Java’s Akka Streams at the in-process scale on the business layer. Namely, the ability to have one stream node in a flow digraph that feeds N outputs that consume it at different rates, and to keep just enough of the intermediate stream node materialized in a buffer to keep all those readers served. Or: like the kind of ACK-truncated buffer that serves a Kafka/Redis Streams consumer group, but with all the stages in the same address-space without serialization+remoting overhead in-between. This “garbage-collected buffered materialization” is a very important property, if your A query returns a million A IDs, and you’re doing a LEFT JOIN from Bs to As that’s dropping most of the As because they don’t have a related B. You don’t want to be holding onto — and replicating! — a thousand concurrent-request copies of that million A ID list. (The million A IDs are also why you’re doing CTEs in your B/C queries in the first place. With a small list of A IDs, it’d be sensible to just round-trip the A ID list to the client, getting sent back as IN (VALUES ...) bind-params for the B/C queries. But 1. naively, those million IDs become a million bind-params, and that’s stupid, slow, and actually impossible [PG only accepts 65K bind-params per statement!]; and 2. trying to pass the fetched list as an A.id[] and using <@ will deoptimize the search.) The closest thing PG has is the ability to BEGIN a transaction; create a TEMPORARY table for that transaction; SELECT INTO the temporary table the result of a query; and then use that temporary table to run two queries. Which you have to do in PL/pgSQL (losing the benefits of “whole program” query-plan optimization you get from CTE queries); and then you still have to do something at the end to get your two queries' differently-shaped outputs back to the user. (For example, encoding every row into JSONB with an explicit embedded row-type-tag, and then double-decoding at the receiver; materializing the results of the queries into two more UNLOGGED [but not TEMPORARY] tables, fetching the results from them in two more queries, and then dropping them; etc.)

hardwaresofton 5y ago
> It’s nice to have that as an automatic feature rather than something that you have to create for yourself with stored procedures (doing e.g. dynamic programming in the DB), but a matview is still a matview — it’s a real table taking up space on disk and getting replicated through the WAL. Ah, yeah at the end of the day both unlogged materialized views aren't supported by postgres[0]. Also a patch that introduces temporary materialized views is stuck behind some refactoring that started in 2019[1] which is unfortunate. > (explanation of materialize) Thanks for this -- I think I get more clearly what you're getting out of materialize. I'm not sure I quite understand the stream analogy because the data being buffered here feels chunked (though I guess it could be incrementally maintained with some more smarts), but I think I understand the issue that basically ongoing queries need access to this temporarily-useful partial result. Basically a cross-session intermediate query result cache. This feels like it could be reduced to a custom table access method plugin, but if that temporary table patch gets merged it could be even easier to hack something together. [0]: https://www.postgresql-archive.org/CREATE-UNLOGGED-MATERIALIZED-VIEW-td6064079.html https://www.postgresql-archive.org/CREATE-UNLOGGED-MATERIALI... [1]: https://www.postgresql.org/message-id/CAKLmikMgkuwFKHem%2BSfBb_G7J01fzTvPJyG9dVOOgVCuakCDCA%40mail.gmail.com https://www.postgresql.org/message-id/CAKLmikMgkuwFKHem%2BSf...