The poor man's version of Materialize that I implemented is the following:
Step 1) Find all "paths" between tables
* Use the postgres information schema to get all relations
* Use npm library graph-cycles to see if there are any graph cycles. If so.. some relations go on a blacklist.
* Use npm library topopsort to sort the graph
* Traverse the graph and find all possible paths from and to tables
* Generate SQL queries to look up "affected" other tables. Input: table name + id Output: list of table names + ids
Step 2) Track changes in database using a transactional outbox
* Create outbox table fields: id, timestamp, event, table_name, table id, jsonb payload
* After running migrations have a script that ensures every table has triggers on insert,update,delete that would insert a row on each of these events in the outbox
Step 3) Compute ( You want to do reporting for a certain table ( target table ) which has relationships with other tables and for this table you want a "materialized" view )
* Have a script on a loop that takes all the outbox entries since last time processed
* Use the queries from step1 to find out which ids of "target table" are affected
* Then only materialize / compute those ids
* Store in Elasticsearch ( in our case )
This is not a solution if you are Facebook scale. But as a small SAAS company with not too many transactions this works brilliantly.
And with more tenants you can just scale this up by sharding the inbox.
Bonus points: have elasticsearch (ingest node) and a postgres replica on the same host as the "compute" script. So if you have a lot of queries calculating the "dependencies" you get better performance.
Kafka Connect can do all this for you if you configure it properly. You would use a postgres "source" connector called Debezium that tracks all changes via postgres replication. All row changes then flow in realtime to Kafka topics. Keeping the data updated in real time in elastic search is also another off-the-shelf Kafka Connector (a "sink" connector)
What you are describing is having the data in elasticsearch in the same format as the data in postgresql. Which is easy.
You could also for instance create a script and use a postgresql logical replication connection ( just like Debezium ) stream the changes into elasticsearch. Without having a full Kafka connect setup. And all the training an maintenance that comes with it.
What I am describing is, before storing the data in Elasticsearch computing the data in a materialized format. So it's more efficient for Elasticsearch to work with. And no longer having any needs for joins.
So instead of recomputing the materialized data every time in it's entirety you want to be more smart about it.
The root table of the materialized data depends on maybe 5 other tables. So if data in those 5 other tables change you need to know if they have a relationship with a row in the "root table". And then only re-materialize those rows.
Materialize does this by having it's own SQL language where you define your materialized view. Which compiles to clever algorithms and uses an execution engine to get to this result.
What I am doing is just having a lookup graph + queries to see what tables, id entries are invalidated. And I re-materialize using normal sql and some extra processing using a nodejs script to make it more optimal for Elasticsearch.
The biggest problem we've encountered with existing tools in the Kafka ecosystem (and the homegrown solutions that we've seen) is that nearly all of them sacrifice consistency. Debezium and most other Kafka Connect plugins will produce duplicate records upon restart, for example, that are very difficult to correctly deduplicate downstream. Things look right when you first turn on the plugin, but a week later when your Kafka Connect cluster restarts, a bit of incorrectness seeps in.
Materialize, by contrast, has been explicitly designed to preserve the consistency present in your upstream system. Our PostgreSQL source, for example, ensures that transactions committed to PostgreSQL appear atomically in Materialize, even when those transactions span multiple tables. See our "consistency guarantees" docs for some more information [0]. We have some additional features coming down the pipe, too, like allowing you to guarantee that your queries against Materialize reflect the latest data in your upstream sources [1].
Failures while communicating to the external systems (the kv store and elastic in your example) are usually where this falls down. It's easy to build a system that's consistent ~90% of the time, but if you want to build a system where things like failures during snapshot write or failures during export to elastic are handled properly it starts getting complex (you will need to find ways to recover and retract data, or build smarts into the consumer to query around aborts, or find a way to do a 2PC-esque dance with the external system a la Kafka's transaction support, etc.). Getting to full consistency isn't easy.
This has been my experience too. Instead of going the logical replication route I tend to leverage the transactional outbox to achieve consistency in the application layer instead.
So when I transact data into tables I immediately fetch the latest outbox id.
And then when query from Elasticsearch I first fetch what the last outbox id of the processed data is.
This way I know if the transaction was already processed into Elasticsearch or not. Repeat. Until outbox id of Elasticsearch is equal or higher than the outbox id of the mutation.
This way I don't have to use logical replication, no k/v store and I can just use a script that fetches and processes the latest outbox changes on a loop.
Looked in the source of Materialize and it looks like this is exactly what they are doing.
They are using Debezium + Kafka for receiving the WAL changes. And using send the processed WAL offsets back using a Kafka topic to Debezium + Postges.
This way they can achieve consistency
It's very hard for Kafka Connect plugins to maintain consistency in all scenarios - both because of the semantics of some upstream databases, and because of the guarantees the connect API itself offers. Hopefully KIP-618 will eliminate more of the edge cases though.
We do something similar, but in 2), instead of using the outbox pattern, we make use (in several different settings) of integers that are guaranteed to increment in commit order, then each consumer can track where their cursor is on the feed of changes. This requires some more care to get that sequence number generated in a safe way, but it means that publishers of changes don't need one outbox per consumer or similar.
Then you can have "processes" that query for new data in an input table, and update aggregates/derived tables from that simply by "select * ... where ChangeSequenceNumber > @MaxSequenceNumberFromPreviousExecution"...
The idea here implemented for Microsoft SQL for the OLTP case:
edit: added details
edit: redacted my phone number
edit: big mistake to add phone number
edit: I think illic is right, probably not me
edit: removed details
If it was you, why the hell on earth would you bother exposing your personal phone number on the internet and asking google to call you on this post? Like, seriously...
Wouldn't you rather call them directly on the hotline...?
That's a phone number from Bulgaria, and it looks like it should be part of the Vivacom GSM network, so I guess it's his personal mobile phone number or a scam.
> why the hell on earth would you bother exposing your personal phone number on the internet
Why not? It's not like you're gonna receive death threats exactly. I've had my personal phone number on my public website and in the footer of every outgoing email for 15+ years, never had any problems, spam or otherwise.
That reminds me of the time when I plugged a network cable back into an Active Directory domain controller. At the exact same time as the RJ-45 plug would have made the little "click", a door slammed shut and a polished steel tanker truck drove by the window, shining a bright light into my eyes.
I had just plugged into a cable into the most important server in the organisation and I saw a bright flash and hear a bang.
All was well, it was just a coincidence, and a good reminder that sometimes shit happens and it's not us, it's just timing.
This isn't true, but considering every Google outage is a one in a billion rube goldberg domino machine, it could be true. Put this comment in the post mortem!
I'd wager a guess that you set up some weird 'expression'? coupled with some bug in the IAM service, maybe some stale resources that you were deleting at the same time?
I'd then assume once expression is evaluated the services end up busy looping / proxies throwing out internal errors and taking out capacity.
Still, you shouldn't be able to cause downtime to more then a few servers in the extremely unlikely case I am anywhere close.
PS:
- I haven't used googles IAM so guessing after a few min of reading docs.
- you are incredibly unlikely to have triggered this at google's scale.
Just permissions. The "IAM" can be safely dropped. It's exactly what you think it'd be: restrictions and privileges.
"IAM" is basically the name for a specific model of doing it.
Unless something really crazy happened, this user is unlikely to be correct. Accounts are supposed to be firewalled/sandboxed in a way that you can't contagion across to someone else's let alone systemwide.
It's possible (some sweeping script on a powerful connection that smashes just the right things or some exploit to break the sandboxing), just probably not likely - especially unintentionally.
> Last thing you want is a conversation with google's lawyer
Google more or less want people find their weaknesses so they can patch and secure them. A person accidentally triggering a global outage is not something that would cause that person to get lawyers on them. Especially not something that only affects his or hers GCP-project.
So that Microsoft can collect tax on Linux and that SUSE Linux will be the safe choice for people that dont want to be sued by Microsoft ? I dont trust it
Sadly, the Quicktime streams are not working on my Vista PC. The audio works, but not the video. And they QuickTime crashes. Man, it makes me realize how outdated QuickTime is these days....
Step 1) Find all "paths" between tables
* Use the postgres information schema to get all relations * Use npm library graph-cycles to see if there are any graph cycles. If so.. some relations go on a blacklist. * Use npm library topopsort to sort the graph * Traverse the graph and find all possible paths from and to tables * Generate SQL queries to look up "affected" other tables. Input: table name + id Output: list of table names + ids
Step 2) Track changes in database using a transactional outbox
* Create outbox table fields: id, timestamp, event, table_name, table id, jsonb payload * After running migrations have a script that ensures every table has triggers on insert,update,delete that would insert a row on each of these events in the outbox
Step 3) Compute ( You want to do reporting for a certain table ( target table ) which has relationships with other tables and for this table you want a "materialized" view )
* Have a script on a loop that takes all the outbox entries since last time processed * Use the queries from step1 to find out which ids of "target table" are affected * Then only materialize / compute those ids * Store in Elasticsearch ( in our case )
This is not a solution if you are Facebook scale. But as a small SAAS company with not too many transactions this works brilliantly. And with more tenants you can just scale this up by sharding the inbox.
Bonus points: have elasticsearch (ingest node) and a postgres replica on the same host as the "compute" script. So if you have a lot of queries calculating the "dependencies" you get better performance.
sorry for my terrible explanation