3 ms·

Sorry I couldn't figure this out from the docs, but Stripe data is queried "live" from Stripe, right? The abstraction is great, but won't this lead to unexpecte

Sorry I couldn't figure this out from the docs, but Stripe data is queried "live" from Stripe, right? The abstraction is great, but won't this lead to unexpected N API calls when joining across my domain + Stripe?

kiwicopple 4y ago
Yes, the data is queried live. > unexpected N API calls when joining across my domain I'm not sure why they would be unexpected (because it should displace some other API calls). I'll hazard a guess that you're worried about fetching the same data multiple times? If that's the case, then yes, you should materialize the data into your database. The Wrapper itself handles pagination, but you'd also want to make sure you're using filters & limits in your SQL statements.
- abuehrle 4y ago
  Thanks for the reply. I'll flesh out my thought process in case it's helpful. My immediate reaction was excitement about the abstraction. An example use case is joining my users to their corresponding Stripe Customers in SQL. The kinds of queries I can reasonably write depend on implementation details of the connector. For example, if Stripe has a bulk customer lookup (list of customer IDs -> Customers), and the connector uses it, I can estimate I'd be able to query on the order of 500 Users at a time in a performant way. But if the API only supports looking up one customer at a time, that 500 User query kicks off 500 API requests, which isn't going to work. You're right -- it's not unexpected -- maybe more like a leaky abstraction.
  - kiwicopple 4y ago
    I understand now, and this is a similar problem to how some GraphQL engines work I imagine you want to do something like: select * from public.users join stripe.customers on public.users.stripe_id = stripe.customers.id limit 100; Then yes, it might make 100 consecutive calls to your stripe account. There are 3 options here: 1. Materialize your customers into your database (like I mention in the previous comment) 2. We build a "smart" FDW, so that it parses your query and fetches the Stripe data first, then performs the join. 3. Use a CTE: with customers as ( select * from stripe.customers ), users as ( select * from public.users ) select * from users join customers on users.stripe_id = customers.id
    - infogulch 4y ago
      4. Use a local http cache in front of stripe's api. This is basically "external materialization".
ako 4y ago
Materialized views enable you to "cache" the response, and only refresh it periodically.
- chrisjc 4y ago
  Wait, you're telling me that you can create materialized views using foreign tables in Postgres? Is there a way to propagate changes from the foreign data source through the FDW to Postgres? Or would it just be some kind of task polls the foreign data source pulling a delta?
  - radiowave 4y ago
    The only option that Postgres's materialized views give you is to "refresh" the view, meaning: re-run the query used to define the view, fetching an entirely new dataset, and use that to replace all the existing data in the view. There's no facility for deltas, or any other way of propagating changes. Edit: And to answer your original question, yes you can absolutely base the materialized view on data obtained from a foreign table.
  - burmecia 4y ago
    You can create a custom function doing all the delta logic and use pg_cron to schedule it to periodically materialize data using FDW.
    - chrisjc 4y ago
      Yeah, this was the approach I was imagining before someone mentioned Materialized View (proper). Once I heard those words mentioned, I began to imagine what sort of interaction with a foreign data source's operations log FDW might have. Of course, and depending on the foreign data source, you could probably even expose an operations log to Postgres via a FDW by defining it as a foreign table... Effectively opening up the possibility of eventually consistent replication. Lots of data stores' operations logs are accessible data collections/tables anyways. All of this is exciting stuff!

kiwicopple 4y ago

Yes, the data is queried live. > unexpected N API calls when joining across my domain I'm not sure why they would be unexpected (because it should displace some other API calls). I'll hazard a guess that you're worried about fetching the same data multiple times? If that's the case, then yes, you should materialize the data into your database. The Wrapper itself handles pagination, but you'd also want to make sure you're using filters & limits in your SQL statements.

abuehrle 4y ago
Thanks for the reply. I'll flesh out my thought process in case it's helpful. My immediate reaction was excitement about the abstraction. An example use case is joining my users to their corresponding Stripe Customers in SQL. The kinds of queries I can reasonably write depend on implementation details of the connector. For example, if Stripe has a bulk customer lookup (list of customer IDs -> Customers), and the connector uses it, I can estimate I'd be able to query on the order of 500 Users at a time in a performant way. But if the API only supports looking up one customer at a time, that 500 User query kicks off 500 API requests, which isn't going to work. You're right -- it's not unexpected -- maybe more like a leaky abstraction.
- kiwicopple 4y ago
  I understand now, and this is a similar problem to how some GraphQL engines work I imagine you want to do something like: select * from public.users join stripe.customers on public.users.stripe_id = stripe.customers.id limit 100; Then yes, it might make 100 consecutive calls to your stripe account. There are 3 options here: 1. Materialize your customers into your database (like I mention in the previous comment) 2. We build a "smart" FDW, so that it parses your query and fetches the Stripe data first, then performs the join. 3. Use a CTE: with customers as ( select * from stripe.customers ), users as ( select * from public.users ) select * from users join customers on users.stripe_id = customers.id
  - infogulch 4y ago
    4. Use a local http cache in front of stripe's api. This is basically "external materialization".

ako 4y ago

Materialized views enable you to "cache" the response, and only refresh it periodically.

chrisjc 4y ago
Wait, you're telling me that you can create materialized views using foreign tables in Postgres? Is there a way to propagate changes from the foreign data source through the FDW to Postgres? Or would it just be some kind of task polls the foreign data source pulling a delta?
- radiowave 4y ago
  The only option that Postgres's materialized views give you is to "refresh" the view, meaning: re-run the query used to define the view, fetching an entirely new dataset, and use that to replace all the existing data in the view. There's no facility for deltas, or any other way of propagating changes. Edit: And to answer your original question, yes you can absolutely base the materialized view on data obtained from a foreign table.
- burmecia 4y ago
  You can create a custom function doing all the delta logic and use pg_cron to schedule it to periodically materialize data using FDW.
  - chrisjc 4y ago
    Yeah, this was the approach I was imagining before someone mentioned Materialized View (proper). Once I heard those words mentioned, I began to imagine what sort of interaction with a foreign data source's operations log FDW might have. Of course, and depending on the foreign data source, you could probably even expose an operations log to Postgres via a FDW by defining it as a foreign table... Effectively opening up the possibility of eventually consistent replication. Lots of data stores' operations logs are accessible data collections/tables anyways. All of this is exciting stuff!