I'm working with an architecture where our main transactional data is stored in MySQL, and we've moved related reference data into a normalized structure in Postgres. The catch is that our systems reading from MySQL can't directly query Postgres. We need a way to enrich MySQL records with data sourced from Postgres without duplicating large amounts of data in MySQL just to support dashboards or read-heavy views. We're primarily using AWS tools for our infrastructure, but we're also looking for cost-effective solutions, whether they're open-source or hybrid, that could help us access the enriched data cleanly.
3 Answers
If I were on your team, I’d suggest considering GraphQL. It's great for exposing data from multiple sources, like your MySQL and Postgres setup, especially for dashboards. If you're worried about size and performance, you might also think of using a Redis cache to help speed things up. Without more details, those are solid starting points!
That’s an interesting setup. For AWS, I think AWS Glue is designed for data integration between different sources, but it can be pricey. I'd also want to know how systems will access the enriched data you're storing; is it through an API, or do you have another method in mind?
I'd recommend building an API for CRUD operations. However, if you're normalizing data from MySQL to Postgres, could you not backfill MySQL with some of that normalized data? It might help avoid the need for cross-database queries.
Related Questions
How to Build a Custom GPT Journalist That Posts Directly to WordPress
Fix Not Being Able To Add New Categories With Intuitive Category Checklist For Wordpress
Get Real User IP Without Installing Cloudflare Apache Module
How to Get Total Line Count In Visual Studio 2013 Without Addons
Install and Configure PhpMyAdmin on Centos 7
How To Setup PostfixAdmin With Dovecot and Postfix Virtual Mailbox