Streaming database data in production setting

[cdeterman]

I have seen in the documentation the use of the stream module to facilitate data flows. This all makes sense with the iter_sql method but I am curious how this is thought to operate in a production setting. Is the connection reconnected on some sort of frequency with a modified query for more recent data or if the connection is expected to remain and somehow perpetually read data? I imagine it is the former but I wasn’t sure what others have done.

[MaxHalford]

Good question. For regular relational databases, it's a good idea to re-run the query with some kind of timestamp filter. But that is a bit hacky: relational database tables are not meant to be streamed over.

There are two options which make more sense. The first is to use something like Materialize, which is a streaming database. See their docs for a clear example. Note that you could also listen to a Kafka topic.

Another option is to leverage change data capture: it's a way to "listen" to updates to a database table and process those changes. IIRC there are several tools out there to run change data capture on popular relational databases.

I hope that helps!