5 ms·

Appreciate the detailed description of what works for your warehousing solution! > (Our situation of our primary-source data being easily and reliably re-fetch

Appreciate the detailed description of what works for your warehousing solution! > (Our situation of our primary-source data being easily and reliably re-fetched is pretty unique to our vertical; but you can put pretty much any system into the same shape by first writing your primary-source data to an event store / durable unbounded-size MQ [e.g. Kafka] as you receive it; and then ETLing it into your DB from there. Forget DB disaster recovery; just keep the MQ data safe, and you'll always be able to replay the process that translates an MQ event-stream into a DB state.) I'm familiar with this almost-event-sourcing model. AFAIK is what banks and financial firms do (at scales I can't imagine and with technology they'll never open source) and is the only way to do rock-solid lose-nothing architecture that is relatively easy to reason about and flexible. I have no need to do this but Debezium w/ NATS + Jetstream/Liftbridge would be my pick for this, since I'm a sucker for slightly newer tooling that boasts at being "cloud native" (i.e. easy for me to administer). AFAIK the boring choice might be Debezium + Kafka with some RAIDx drives below it all just in case. > We do have hot standbys, but they're async (using wal-g — master writes WAL segments to object store; replicas immediately fetch WAL segments from object store, as quickly as they can manage.) They're for query load scaling, not disaster recovery. Read only replicas for load scaling is pretty common, was surprised to hear wal-g though -- pg has an absolutely dizzying array of replication options[0], and I don't think I landed on wal-g as the "best" choice for me (with a heavily application-centric viewpoint) and this gives me reasonf or some pause. I think I mostly had it down to barman or pgbackrest. The object-store-capable options always caught my eye (and what put backrest over the top for me IIRC), will go back and revisit. Does covalent maintain an engineering blog? because sounds like ya'll are doing stuff I'd love to read about and cargo cult^H^H critically consider. Weirdly enough I've just been down a bit of a rabbit hole on the data engineering world and have been thoroughly impressed by dbt[1]. The concept is simple, but combined with a powerful database like pg it holds a lot of promise in my mind. I'm mostly excited by the idea of exposing the power of postgres and it's many extensions and features to a SQL-first view of the world that dbt encourages, and building some sort of "data platform" that is nothing more than enshrining and exposing the best ways to do things with that stack. Is dbt or a similar tool a part of your stack in any way? Listening to people who sort of... distribute hype around data engineering, you'd think Snowflake is the next greatest thing since sliced bread, but the pattern of debezium + dbt + a big ol' well-configured and replicated postgres cluster feels like more than enough. I'm aware of course that Snowflake is really just a management layer (essentially, they're an Redshift-aaS provider for any cloud) but it feels like that gets lost in discussion. Sounds like you all have a well considered data pipeline (so the snowflake part is done for you), wondering what the other links in the chain look like. Any Metabase or Superset at the end of the pipeline? [EDIT] Want to note the recent presentation from CERN on the topic of replication that most recently influenced me[2] (skip to the last 2 slides if you want the TL;DR) [0]: https://wiki.postgresql.org/wiki/Binary_Replication_Tools https://wiki.postgresql.org/wiki/Binary_Replication_Tools [1]: https://docs.getdbt.com https://docs.getdbt.com [2]: https://www.slideshare.net/AnastasiaLubennikova/advanced-backup-methods-postgrescern https://www.slideshare.net/AnastasiaLubennikova/advanced-bac...

derefr 5y ago
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.) > Does covalent maintain an engineering blog? 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.) > Is dbt or a similar tool a part of your stack in any way? 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.) 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.) 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.) 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.) > Any Metabase or Superset at the end of the pipeline? 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. :)
- hardwaresofton 5y ago

derefr 5y ago

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.) > Does covalent maintain an engineering blog? 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.) > Is dbt or a similar tool a part of your stack in any way? 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.) 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.) 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.) 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.) > Any Metabase or Superset at the end of the pipeline? 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. :)

hardwaresofton 5y ago