17 ms·

I'm a database guy, so the question I get from clients is, "We're thinking about breaking up our monolith into a bunch of microservices, and we want to use best

I'm a database guy, so the question I get from clients is, "We're thinking about breaking up our monolith into a bunch of microservices, and we want to use best-of-breed persistence layers for each microservice. Some data belongs in Postgres, some in DynamoDB, some in JSON files. Now, how do we do reporting?" Analysts expect to be able to connect to one system, see their data, and write queries for it. They were never brought into the microservices strategy, and now they're stumped as to how they're supposed to quickly get data out to answer business questions or show customers stuff on a dashboard. The only answers I've seen so far are either to build really complex/expensive reporting systems that pull data from every source in real time, or do extract/transform/load (ETL) processes like data warehouses do (in which the reporting data lags behind the source systems and doesn't have all the tables), or try to build real time replication to a central database - at which point, you're right back to a monolith. Reporting on a bunch of different databases is a hard nut to crack.

andy_ppp 7y ago
> Reporting on a bunch of different databases is a hard nut to crack. It's not necessarily a bad idea though :-/
- keithb- 7y ago
  I thought that's what solutions like Calcite[1] were for: running queries across disparate data sources. Yes, you still need to have adapters for each source to normalize access semantics. No, not all sources will have the same schema. But if you're trying to combine Postgres and DynamoDB into a single query, you would narrow your view to something that exists in both places, e.g. customer keys, meta data, etc. Maybe I'm wrong. [1] https://calcite.apache.org/ https://calcite.apache.org/
  - manigandham 7y ago
    Calcite is only a SQL parser and planner. It doesn't execute anything and is meant to be a component in a larger database system. You need to use something like Spark, Presto or Drill if you want to run queries across different data sources.
mattbessey 7y ago
is that a bad thing? I don't think anyone is against a reporting monolith. to me, being able to silo data in the appropriate stores for their read / write patterns, and still query it all in a single columnar lake seems like a feature, not a bug to be solved
rjkennedy98 7y ago
This is what Kafka is for. You put Kafka on top of your database to expose data and events. Now BI can take the events put them into their system as they want.
- BrentOzar 7y ago
  > You put Kafka on top of your database to expose data and events. Now BI can take the events put them into their system as they want. Right, that's the data warehouse method that I described. "Put them into their system" is a lot harder work than just typing in "put Kafka on top of your database."
  - jsjohnst 7y ago
    Things like Maxwell[0] help a lot with the getting stuff into Kafka from the DB, but I agree with your point entirely. Kafka is not something I’d recommend unless you’ve got a team dedicated to maintaining it. Thankfully Confluent is rising up to fill that need and reasonably priced. Confluent also has an offering for streaming binlogs into Kafka too. [0] http://maxwells-daemon.io/ http://maxwells-daemon.io/
- skywhopper 7y ago
  Worth pointing out that running Kafka is itself no small thing. So now you've added BI and also Kafka, which itself requires a bundle of services to operate. And you have to keep BI and Kafka in sync with all your various other data stores' schemas, and with each other. Which all gets back to the point of the OP.
- bfung 7y ago
  Without tone of voice over text, unsure if the suggestion is in sincerity or sarcasm. The article talks about micro services being split up due to fad as opposed to deliberate, researched reasons. Putting Kafka over the database also makes the data distributed when in most cases, it’s not necessary!
- gunnarmorling 7y ago
  Shameless plug: feeding data from operational databases to DWH is one main use case of change data capture as e.g. implemented by Debezium (https://debezium.io/ https://debezium.io/) for MySQL, Postgres, MongoDB, and more, using Apache Kafka as the messaging layer. Disclaimer: working on Debezium
sharadov 7y ago
No one has solved that problem and it sucks, and what ends up happening is you end up again porting that data from those disparate SQL and NoSQL databases either to a warehouse which is RDBMS or you put it into a datalake. That's again possible if you somehow manage to find all the drivers. You're doubly screwed if you have a hybrid - cloud and on-prem setup.
- jmngomes 7y ago
  >> No one has solved that problem (...) you end up again porting that data from those disparate SQL and NoSQL databases either to a warehouse That's exactly how that problem has been solved successfully for the past 20 years.
  - jandrewrogers 7y ago
    The high latency between operational data and that data being reflected in reporting using traditional data warehouse pipelines makes it difficult for companies to make effective business decisions in a fast-paced business environment. Even in competent execution, that latency is frequently measured in weeks for big businesses. In the last few years, I've been approached by a number of traditional big businesses looking to rearchitect their database systems to make them more monolithic for the express purpose of reducing end-to-end latency in support of operational decisions. It is extremely expensive and slow to move all the data to a data warehouse. Ignoring the cost element, the latency from when data shows up in the operational environment to when it is reflected in the output of the data warehouse is often unacceptably high for many business use cases. A large percentage of that latency is attributable solely to what is essentially complex data motion between systems.
    - snicker7 7y ago
      It is not necessarily the case that data warehouses have high update latency. Open source streaming tech (e.g. Apache Kafka, Beam, etcetera) can be used to build an OLAP database updated in near real-time.
gowld 7y ago
Build a reporting database that maintains a copy of data from other data stores. Simple enough. Surely you wouldn't run analytics directly on your prod serving database, and risk a bad query taking down your whole system?
- BrentOzar 7y ago
  > Surely you wouldn't run analytics directly on your prod serving database, and risk a bad query taking down your whole system? Uhh, yep, that's exactly how a lot of businesses work. There are defenses at the database layer. For example, in Microsoft SQL Server, we've got Resource Governor which lets you cap how much CPU/memory/etc that any one department or user can get.
  - speleding 7y ago
    That's not a good idea. The usage patterns for a production database and one that runs reporting are very different. Reporting has long running queries with complex joins, production has many parallel short queries. If you start mixing the two, you can no longer reliably tune your database. For example you would want the alert for slow queries set set to a different time out on production and on reporting. Also, you complicate locking down access to the database. A reporting database can typically contain less sensitive info, reporting would not have password (hashes) for user accounts for example.
    - mwigdahl 7y ago
      I don't think Brent was necessarily saying it was a good idea, just something that is commonly seen. At my own company we try hard to get customers to run reports against replicas/extracts rather than production, but some customers insist that they absolutely _need_ to pull in live data from production. So they run complex reports against a production OLTP schema and wonder why the system is running slow...
      - jsjohnst 7y ago
        > I don't think Brent was necessarily saying it was a good idea Reading other comments Brent’s made here, I’m not so sure.
zten 7y ago
I don't think you're right back to a monolith with centralized reporting. Remember, microservices doesn't mean JSON-RPC over HTTP. Passing updates extracted via change data capture and forwarding them to another reporting system is a perfectly viable interface. Data duplication is also an acceptable consequence in this design.
- maxdo 7y ago
  You just subscribe a reporting service to certain topics , done .
- BrentOzar 7y ago
  > Passing updates extracted via change data capture and forwarding them to another reporting system is a perfectly viable interface. Right, that's the data warehouse method I described, keeping a central database in a reporting system. But now you just have to keep that database schema stable, because folks are going to write reports against it. It's a monolith for reporting, and its schema is going to be affected by changes in upstream systems. It's not like source systems can just totally refactor tables without considering how the data downstream is going to be affected. When Ms. CEO's report breaks, bad things happen.
  - zten 7y ago
    Sorry, I noticed you made the same observation in another thread after you got dogpiled by everyone; I left my question over there. Yeah, I've had to contend with that problem before -- hard to imagine how I forgot about it :)
bcrosby95 7y ago
Microservices are primarily about silo'ing different engineering teams from eachother. If you have a singular reporting database that a singular engineering team manages I'm not sure its a big deal. Reporting might be a "monolith" but the system as a whole isn't. Teams can still deploy their services and change their database schemas without stepping on eachother's toes.
- BrentOzar 7y ago
  > Teams can still deploy their services and change their database schemas No, because as soon as you change your schema, you have to plan ahead with the reporting team for starters. The reports still have to be able to work the same way, which means the data needs to be in the same format, or else the reports need to be rewritten to handle the changes in tables/structures.
  - zten 7y ago
    Isn't that a weakness of any data dependency? I could push a new service that deprecates a field and supplies a replacement. I still have to communicate it and get downstream consumers to migrate. Or is the problem that reporting teams are looking at the low-level implementation details, rather than some stable higher-level interface? (I don't know how to avoid that when you're just pulling someone else's database directly or indirectly.)
  - ilikehurdles 7y ago
    That's no different than changing your API specification or refactoring your code or anything else. Ideally the entrypoints into your team's services should be considered hard contracts to the outside world and the rest of the organization. Changing the contract out from under your customer is not something that should ever be easy. IMO the devops folks should define some standard containers that include facilities for reporting on low-level metrics. Most of the monitoring above that should be managed by the microservice owner. The messages that are consumed for BI and external reporting should not have breaking changes any more than the APIs you provide your clients should.
1MoreThing 7y ago
Isn't this the whole point of a data lake?
- BrentOzar 7y ago
  > Isn't this the whole point of a data lake? Yes, but data lakes don't fill themselves. Each team has to be responsible for exporting every transaction to the lake, either in real time or delayed, and then the reporting systems have to be able to combine the different sources/formats. If each microservice team expects to be able to change their formats in the data lake willy-nilly, bam, there breaks the report again.
  - iamEAP 7y ago
    This problem exists in monolithic data stores and codebases too. It's not as if independent teams have absolute sovereignty over their table schemas. Schemas evolve as the needs of the product change, and that evolution will always outpace the way the business looks at the data. The best way I've seen to deal with this is to handle this at report query-time (e.g. pick a platform that can effectively handle the necessary transformations at query-time, rather than at load-time).
AnthonyWnC 7y ago
In my last job I was a DevOps guy in a Data Eng. team and we used microservice (actually serverless) extensively to the point that none of our ETL relied on servers (they were all serverless; AWS lambda). Now databases themselves are different stories, they are the persistence/data layer that microservices themselves use . But it's actually doable and I'd even say much easier to use microservices/serverless for ETL because it's easier to develop CI/CD and testing/deployment with non-stateful services. Of course, it does take certain level of engineering maturity and skillsets but I think the end results justify it.
- texasbigdata 7y ago
  Is there a book or course to buy toward getting an understanding of the more philosophical concepts underneath your approach?
JMTQp8lwXL 7y ago
Whether or not your system intentionally ended up with this architecture, GraphQL provides a unified way of fetching the data. You'll still have to implement the details of how to fetch from the various services, but it gives people who just want read access to everything a clean abstraction for doing so.
- benwaffle 7y ago
  REST can do the exact same thing.
  - nathankunicki 7y ago
    With less flexibility, and more round trips.
    - jsjohnst 7y ago
      > With less flexibility, and more round trips That all depends on the API.
      - nathankunicki 7y ago
        Sure - but if you ever want it to be as flexible as GQL, you need to implement...a query language?
    - MadWombat 7y ago
      I am not sure about that. Both RESTful APIs and GraphQL APIs need to be designed and flexibility is basicaly a measure of how good your API design is. I don't see how GraphQL is intrinsically more flexible.
- adamc 7y ago
  It's nice to have a clean abstraction, but if the various services are on tables in different databases, things like joins get much more expensive, no? Performance is important.
jsjohnst 7y ago
But I’d argue Monoliths don’t have anything inherent to them which makes reporting easier. A proper BI setup requires a lot of hard work no matter how the backend services are built.
- BrentOzar 7y ago
  > But I’d argue Monoliths don’t have anything inherent to them which makes reporting easier. It's easier to join tables in databases that live on a single server, in a single database platform, than it is to connect to lots of different data sources that live in different servers, possibly even in different locations (like cloud vs on-premises, and hybrids.)
  - jsjohnst 7y ago
    > It's easier to join tables in databases that live on a single server, in a single database platform 1) what if your data set is larger than you can practically handle in a single DB instance? 2) nothing about a monolith implies you have a single data platform, let alone a single DB instance
    - BrentOzar 7y ago
      > 1) what if your data set is larger than you can practically handle in a single DB instance? I have clients at 10TB-100TB in a single SQL Server. It ain't a fun place to be, but it's doable.
- jaxn 7y ago
  Monoliths have an advantage up until the point where you have to shard the database in some way.
segmondy 7y ago
This is a solved problem, "Data Engineering" teams solve this by building a data pipeline. It's not for all orgs, but for a large org, this is worth doing right.
- derision 7y ago
  "data pipeline" is just the new trendy phrase for ETL, which the GP mentioned. just because a solution exists, does not make it a solved problem. it's not the right solution for everyone
  - segmondy 7y ago
    no it's not. ETL is really database focused and batch focused , Extract, Transform, Load. Data pipeline, is a combination of streams and batch. For example, you can implement a Data Capture using something like https://debezium.io/ https://debezium.io/ Here's how Netflix solves it https://netflixtechblog.com/dblog-a-generic-change-data-capture-framework-69351fb9099b https://netflixtechblog.com/dblog-a-generic-change-data-capt... Overview "Change-Data-Capture (CDC) allows capturing committed changes from a database in real-time and propagating those changes to downstream consumers [1][2]. CDC is becoming increasingly popular for use cases that require keeping multiple heterogeneous datastores in sync (like MySQL and ElasticSearch) and addresses challenges that exist with traditional techniques like dual-writes and distributed transactions [3][4]."
    - cleong 7y ago
      What your saying applys more to ELT, which is an efficient method when using batches. There's no reason why you can't stream ETL.
- eganist 7y ago
  > It's not for all orgs Then it doesn't seem to be solved. Seems like teams operating at a lean scale would have an issue with this, especially teams with lopsided business:engineering ratios
- timClicks 7y ago
WoahNoun 7y ago
I'm going to disagree heavily here. The world of cloud computing, microservices, and hosted/managed services has made the analyst and data engineers job easier than ever. If the software team builds a new dynamodb table, they simple give the AWS account for the analytics team the appropriate IAM permissions and the analytics team will set-up an off-peak bulk extract. A single analyst can easily run an entire data warehouse and analytics pipeline basically part time without a single server using hosted services and microservices. With a team of analysts, the load sharing should be such that the ETL infrastructure is only touched when adding new pipelines or a new feature transformation. And for data scientists working on production models used within production software, most inference is packaged as containers in something like ECS or Fargate which are then scaled up and down automatically. Eg, they are basically running a microservice for the software teams to consume. Real time reporting, in my opinion, is not the domain of analysts; it's the domain of the software team. For one, it's rarely useful outside of something like a NOC (or similar control room areas) and should be considered a software feature of that control room. If real-time has to be on the analysts (been there), then the software team should dual publish their transactions to kinesis firehouse and the analytics team can take it from there. Of course, all of this relies heavily on buy-in to the world of cloud computing. Come on in, we all float down here.
- dfabulich 7y ago
  Cloud computing helps here, but microservices still make this harder. Some of the data is in Dynamo, some of it is in Aurora, some of it is in MySQL RDS, some of it is in S3, and nobody knows where all of it is at once.
  - WoahNoun 7y ago
    From a project management perspective, each data source should have some requirements behind it from the business team. Those requirements should be prioritized meaning you can prioritize which data source to tackle first. You automate the process in AWS data pipelines for that data source, write the documentation for the next analyst, and move on to the next data source. The complexity you and the OP seem to be describing are more in the management and prioritization of analytics projects than in the actual "this is a hard technical problem" domain. It's just a lot of it is tedious especially compared to "everyone just put all your data in the Oracle RACs and bug the DBA until they give you permission" model of the past.
projektfu 7y ago
It's a little sad because originally, people thought there would be a shared data base (now one word) for the whole organization. Data administrators would write rules for the data as a whole and keep applications in line so that they operated on that data base appropriately. A lot of DBMS features are meant to support this concept of shared use by diverse applications. What ended up happening is each application uses its own database, nobody offered applications that could be configured to an existing data base, and all of our data is in silos.
- default-kramer 7y ago
  Do you know why the shared database vision didn't work out? Because I still think it would be the best approach for many companies. Most companies are small enough that they could spend less than $10k/month for an extremely powerful cloud DB. Then you could replace most microservices with views or stored procs. What could be simpler? I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible (compared to popular languages like C#, Python, etc...) But why has no one written a great DB language yet?
  - zerd 7y ago
    I've worked on a service like this. 800k lines of PL/SQL and Java stored procesures (running inside the database, so you could call Java from PL/SQL and vice versa), powered by triggers. * Testing is god-awful. To test a simple thing you had to know how the whole application worked, because there's validation in triggers, which triggers other triggers, which require things to be in a certain state. This made refactoring really hard/risky so it rarely got done. * There's a performance ceiling, and when you hit that, you're done. We did hit a ceiling, did months of performance tuning, then upgraded to the biggest available box at the time, 96 cores, 2TB ram, which helped, but next time the upgrade won't be big enough. You're limited in what one box can do (and due to stored procedures being tied to the transaction there's limits to what you can do concurrently as well)
    - projektfu 7y ago
MadWombat 7y ago
> Some data belongs in Postgres, some in DynamoDB, some in JSON files. Now, how do we do reporting? One of the key concepts in microservice architecture is data sovereignity. It doesn't matter how/where the data is stored. The only thing that cares about the details of the data storage is the service itself. If you need some data the service operates on for reporting purposes, make an API that gets you this data and make it part of the service. You can architect layers around it, maybe write a separate service that aggregates data from multiple other services into a central analytics database and then reporting can be done from there or keep requests in real time, but introduce a caching layer or whatever. But you do not simply go and poke your reporting fingers into individual service databases. In a good microservice architecture you should not even be able to do that.
- echelon 7y ago
  > But you do not simply go and poke your reporting fingers into individual service databases. This is why I distrust all of the monolith folks. Yes, it's easier to get your data, but in the long run you create unmaintainable spaghetti that can't ever change without breaking things you can't easily surface. Monoliths are undisciplined and encourage unhealthy and unsustainable engineering. Microservices enforce separation of concerns and data ownership. It can be done wrong, but when executed correctly results in something you can easily make sense of.
  - adamc 7y ago
    I do not claim expertise here, but it would seem like microservices would add significant performance costs. Stitching together a bunch of results from different microservices is going to be a LOT more expensive than running a query with joins.
    - ddek 7y ago
      That wouldn't be a microservice then. There's going to be a relationship between data in your services, but it shouldn't be directly referential.
    - 7y ago
yibg 7y ago
This isn’t a new problem to microservices though, although maybe it’s amplified. Reporting was challenging before microservices became popular too with data from different sources. Different products, offline data sources etc that all had to be put together. The whole ETL, data warehousing stuff. In the end everything involves tradeoffs. If you need to partition your data to scale, or for some other reason need to break up the data, then reporting potentially becomes a secondary concern. In this case maybe delayed reporting or a more complex reporting workflow is worth the trade off.
- cobookman 7y ago
  +1, Informatica was founded in 1993. Teradata in 1979. These are not new problems. DataWarehousing has drastically improved recently with the separation of Storage & Compute. A single analyst's query impacting the entire Data Warehouse is a problem that will in the next few years be something of the past.
anbotero 7y ago
Off-topic, but I knew I had seen that name somewhere: https://dbareactions.com/post/183007001237/devops-owns-the-queries-and-the-indexes-but-dba https://dbareactions.com/post/183007001237/devops-owns-the-q... (SRE here, but I work on databases as well all day)
willvarfar 7y ago
https://prestosql.io/ https://prestosql.io/ It can access all those different databases. You can also make your own connectors that make your services appear as tables, which you can query with SQL in the normal way. So if the new accounts micro-service doesn't have a database, or the team won't let your analysts access the database behind it, you can always go in through the front-door e.g. the rest/graphql/grpc/thrift/buzzword api it exposes, and treat it as just another table! Presto is great even for monoliths ;) Rah rah presto.
- freewilly1040 7y ago
  Do issues arise when trying to match types across different persistence layers?
  - willvarfar 7y ago
    In practice, no, haven't hit any.
- joefkelley 7y ago
  It's been about 4 years since I've been in this world, but I remember there being several products all doing a very similar thing: Presto, Hive, SparkSQL, Impala, perhaps some more I'm forgetting. Is the situation still the same? Or has Presto "won out" in any sense?
  - dmitrig01 7y ago
    Hive and Impala are databases. Presto and SparkSQL are SQL interfaces to many different datasources, including Hive and Impala, but also any SQL database such as Postgres/Redis/etc, and many other types of databases, such as Cassandra and Redis; the SQL tools can query all these different types of databases with a unified SQL interface, and even do joins across them. The difference between Presto and SparkSQL is that Presto is run on a multi-tenant cluster with automatic resource allocation. SparkSQL jobs tend to have to be allocated with a specific resource allocation ahead of time. This makes Presto is (in my experience) a little more user-friendly. On the other hand, SparkSQL has better support for writing data to different datasources, whereas Presto pretty much only supports collecting results from a client or writing data into Hive.
tootie 7y ago
This is what gave rise to data lakes. The typical data lake maturity model I see in enterprise is: 1. Pay a ton of money to Microsoft for Azure Data Lake, Power BI, etc. 2. Spend 12 months building ETLs from all your microservices to feed a torrent of raw data to your lake. 3. Start to think about what KPIs you want to measure. 4. Sign up for a free Google Analytics account and use that instead.
- jlmorton 7y ago
  > Spend 12 months building ETLs Okay, sounds reasonable enough for a complex enterprise. > to feed a torrent of raw data to your lake Well, there's the problem. Why is it taking a year to export data in its raw, natural state? The entire point of a data lake is that there is no transformation of the data. There's no need to verify the data is accurate. There's no need to make sure it's performant. It's just data exported from one system to another. If the file sizes, or record counts match, you're in good shape. If it's taking a year to simply copy raw data from one system to another, the enterprise has deeper problems than architecture.
  - satyrnein 7y ago
    If you are "export[ing] data in its raw, natural state" then haven't you lost the isolation benefits of microservices? Now you have external systems dependent on your implementation details, and changing your schema will break them.
    - jlmorton 7y ago
      That's a problem for the future data engineers to deal with. The data lake is an insurance policy so you only need to think about these problems if you later want the data. If you already know you want to analyze the data, then a data lake is not a good choice. Yes, it makes life harder for the data engineers in the future, but it might turn out that analysts only ever need 5% of the data in the lake, and dealing with these schema changes for 5% of the data is easier than carefully planning a public schema for 100% of it. It can be helpful to include some small amount of metadata in the export though, with things like the source system name, date & time, # of records, and a schema version. Schema version could easily be the latest migration revision, or something like that.
Terr_ 7y ago
> now they're stumped as to how they're supposed to quickly get data out I'd argue that (given a large enough business) "reporting" ought to be its own own software unit (code, database, etc.) which is responsible for taking in data-flows from other services and storing them into whatever form happens to be best for the needs of report-runners. Rather than wandering auditor-sysadmins, they're mostly customers of another system. When it comes to a complicated ecosystem of many idiosyncratic services, this article may be handy: "The Log: What every software engineer should know about real-time data's unifying abstraction" [0] https://engineering.linkedin.com/distributed-systems/log-what-every-software-engineer-should-know-about-real-time-datas-unifying https://engineering.linkedin.com/distributed-systems/log-wha...
AmericanChopper 7y ago
Even if you have a monolith, you’re still going to have multiple sources that you want to report on. Even in an incredibly simple monolith I could imagine you’d have: your app data, Salesforce, Google Analytics. Having an ELT > data warehouse pipeline isn’t difficult, and what reporting use case is undermined by the data being a few minutes old?
debrice 7y ago
Where I work we use micro-services through lambda (we have dozens of them) and use DynamoDB for our tables. DynamoDB streams are piped through elasticsearch. We use it for our business intelligence. Took us about a week to setup proper replication and sharding. I don't have a strong opinion on monolith or micro-service, pick one or the other, understand their culprit and write high quality (aka. simple and maintainable) code.
- abhishekjha 7y ago
  Is there a way to do this for a relational database at scale?
  - debrice 7y ago
    We used to do that but dynamodb being very dynamic (which we like) makes it harder to use a declarative schema. ES auto detect the schema which makes it a breeze. I’d say try with JSON fields but I don’t think you’ll get great query speed
  - etxm 7y ago
    You can do change data capture with Debezium. Recently the Netflix engineering blog mentioned a tool called DBLog too, but I don’t believe they’ve released it yet.
oneplane 7y ago
Reporting on databases is a rather 90's thing to do. Why would you still actively pursue such a reporting method from the OLTP/OLAP world? If you use tools that are purposely made to work in such a way, an analyst using those tools will obviously not be able to utilise them in an incompatible environment.
skybrian 7y ago
It seems like interacting with customers and enforcing business rules is one job, and observing what's happening is a different concern. Observing means collecting a lot of logs to a reporting database.
throwaway894345 7y ago
> Reporting on a bunch of different databases is a hard nut to crack. Maybe, but your business analyst already needs to connect to N other databases/data-sources anyway (marketing data, web analytics, salesforce, etc, etc), so you already need the infrastructure to connect to N data sources. N+1 isn't much worse.
liveoneggs 7y ago
just recreate the database you broke apart into something less flexible
toriningen 7y ago
Or you could have CQRS projectors (read models), which solve exactly this - they aggregate data from lots of different eventually consistent sources, providing you with locally consistent view only of events you might be interested in. It will lag behind by some extent, roughly equal to the processing delay + double network delay, but can include arbitrary things that are part of your event model. Though, it's not a silver bullet (distributed constraints are pain in the ass yet), and if system wasn't designed as DDD/CQRS system from the ground up, it would be hard to migrate it, especially because you can't make small steps toward it.
nitwit005 7y ago
You can't entirely escape this problem. Even when companies want to fit everything in a single database, they often find they can't. With enough data you'll eventually run into scalability limitations. A quick fix might be to split different customers onto different databases, which doesn't require too many changes to the app. But now you're stuck building tools to pull from different databases to generate reports, even though you have a monolithic code base.
dcolkitt 7y ago
I disagree with the conclusion. While every situation is unique, the default should be separate persistence layers for analytics and transactions. Analytics has very different workloads and use cases than production transactions. Data is WORM, latency and uptime SLAs are looser, throughput and durability SLAs are tighter, access is columnar, consistency requirements are different, demand is lumpy, and security policies are different. Running analytics against the same database used for customer facing transactions just doesn't make sense. Do you really want to spike your client response times every time BI runs their daily report? The biggest downside to keeping analytics data separate from transactions is the need to duplicate the data. But storage costs are dirt cheap. Without forethought you can also run into thorny questions when the sources diverge. But as long as you plan a clear policy about the canonical source of truth, this won't become an issue. With that architecture, analysts don't have to feel constrained about decisions that engineering is making without their input. They're free to store their version of the data in whatever way best suits their work flow. The only time they need to interface with engineering is to ingest the data either from a delta stream in the transaction layer and/or duplexing the incoming data upstream. Keeping interfaces small is a core principle of best engineering practices.
yahyaheee 7y ago
This is a problem but I’m not sure having everything in a single data store is a great idea either. Generally you want your analytics separate from your operations anyway. We do this by having a central ES instance which just informs on the data it needs, which had worked perfectly fine for our needs
campers 7y ago
I've seen that BigQuery has federated querying, so you can build queries on data in BigQuery, BigTable, Cloud SQL, Cloud Storage (Avro, Parquet, ORC, JSON and CSV formats) and Google Drives (CSV, Newlinen-delimited JSON, Avro or Google Sheets)

andy_ppp 7y ago

> Reporting on a bunch of different databases is a hard nut to crack. It's not necessarily a bad idea though :-/

keithb- 7y ago
I thought that's what solutions like Calcite[1] were for: running queries across disparate data sources. Yes, you still need to have adapters for each source to normalize access semantics. No, not all sources will have the same schema. But if you're trying to combine Postgres and DynamoDB into a single query, you would narrow your view to something that exists in both places, e.g. customer keys, meta data, etc. Maybe I'm wrong. [1] https://calcite.apache.org/ https://calcite.apache.org/
- manigandham 7y ago
  Calcite is only a SQL parser and planner. It doesn't execute anything and is meant to be a component in a larger database system. You need to use something like Spark, Presto or Drill if you want to run queries across different data sources.

mattbessey 7y ago

is that a bad thing? I don't think anyone is against a reporting monolith. to me, being able to silo data in the appropriate stores for their read / write patterns, and still query it all in a single columnar lake seems like a feature, not a bug to be solved

rjkennedy98 7y ago

This is what Kafka is for. You put Kafka on top of your database to expose data and events. Now BI can take the events put them into their system as they want.

BrentOzar 7y ago
> You put Kafka on top of your database to expose data and events. Now BI can take the events put them into their system as they want. Right, that's the data warehouse method that I described. "Put them into their system" is a lot harder work than just typing in "put Kafka on top of your database."
- jsjohnst 7y ago
  Things like Maxwell[0] help a lot with the getting stuff into Kafka from the DB, but I agree with your point entirely. Kafka is not something I’d recommend unless you’ve got a team dedicated to maintaining it. Thankfully Confluent is rising up to fill that need and reasonably priced. Confluent also has an offering for streaming binlogs into Kafka too. [0] http://maxwells-daemon.io/ http://maxwells-daemon.io/
skywhopper 7y ago
Worth pointing out that running Kafka is itself no small thing. So now you've added BI and also Kafka, which itself requires a bundle of services to operate. And you have to keep BI and Kafka in sync with all your various other data stores' schemas, and with each other. Which all gets back to the point of the OP.
bfung 7y ago
Without tone of voice over text, unsure if the suggestion is in sincerity or sarcasm. The article talks about micro services being split up due to fad as opposed to deliberate, researched reasons. Putting Kafka over the database also makes the data distributed when in most cases, it’s not necessary!
gunnarmorling 7y ago
Shameless plug: feeding data from operational databases to DWH is one main use case of change data capture as e.g. implemented by Debezium (https://debezium.io/ https://debezium.io/) for MySQL, Postgres, MongoDB, and more, using Apache Kafka as the messaging layer. Disclaimer: working on Debezium

sharadov 7y ago

No one has solved that problem and it sucks, and what ends up happening is you end up again porting that data from those disparate SQL and NoSQL databases either to a warehouse which is RDBMS or you put it into a datalake. That's again possible if you somehow manage to find all the drivers. You're doubly screwed if you have a hybrid - cloud and on-prem setup.

jmngomes 7y ago
>> No one has solved that problem (...) you end up again porting that data from those disparate SQL and NoSQL databases either to a warehouse That's exactly how that problem has been solved successfully for the past 20 years.
- jandrewrogers 7y ago
  The high latency between operational data and that data being reflected in reporting using traditional data warehouse pipelines makes it difficult for companies to make effective business decisions in a fast-paced business environment. Even in competent execution, that latency is frequently measured in weeks for big businesses. In the last few years, I've been approached by a number of traditional big businesses looking to rearchitect their database systems to make them more monolithic for the express purpose of reducing end-to-end latency in support of operational decisions. It is extremely expensive and slow to move all the data to a data warehouse. Ignoring the cost element, the latency from when data shows up in the operational environment to when it is reflected in the output of the data warehouse is often unacceptably high for many business use cases. A large percentage of that latency is attributable solely to what is essentially complex data motion between systems.
  - snicker7 7y ago
    It is not necessarily the case that data warehouses have high update latency. Open source streaming tech (e.g. Apache Kafka, Beam, etcetera) can be used to build an OLAP database updated in near real-time.

gowld 7y ago

Build a reporting database that maintains a copy of data from other data stores. Simple enough. Surely you wouldn't run analytics directly on your prod serving database, and risk a bad query taking down your whole system?

BrentOzar 7y ago
> Surely you wouldn't run analytics directly on your prod serving database, and risk a bad query taking down your whole system? Uhh, yep, that's exactly how a lot of businesses work. There are defenses at the database layer. For example, in Microsoft SQL Server, we've got Resource Governor which lets you cap how much CPU/memory/etc that any one department or user can get.
- speleding 7y ago
  That's not a good idea. The usage patterns for a production database and one that runs reporting are very different. Reporting has long running queries with complex joins, production has many parallel short queries. If you start mixing the two, you can no longer reliably tune your database. For example you would want the alert for slow queries set set to a different time out on production and on reporting. Also, you complicate locking down access to the database. A reporting database can typically contain less sensitive info, reporting would not have password (hashes) for user accounts for example.
  - mwigdahl 7y ago
    I don't think Brent was necessarily saying it was a good idea, just something that is commonly seen. At my own company we try hard to get customers to run reports against replicas/extracts rather than production, but some customers insist that they absolutely _need_ to pull in live data from production. So they run complex reports against a production OLTP schema and wonder why the system is running slow...
    - jsjohnst 7y ago
      > I don't think Brent was necessarily saying it was a good idea Reading other comments Brent’s made here, I’m not so sure.

zten 7y ago

I don't think you're right back to a monolith with centralized reporting. Remember, microservices doesn't mean JSON-RPC over HTTP. Passing updates extracted via change data capture and forwarding them to another reporting system is a perfectly viable interface. Data duplication is also an acceptable consequence in this design.

maxdo 7y ago
You just subscribe a reporting service to certain topics , done .
BrentOzar 7y ago
> Passing updates extracted via change data capture and forwarding them to another reporting system is a perfectly viable interface. Right, that's the data warehouse method I described, keeping a central database in a reporting system. But now you just have to keep that database schema stable, because folks are going to write reports against it. It's a monolith for reporting, and its schema is going to be affected by changes in upstream systems. It's not like source systems can just totally refactor tables without considering how the data downstream is going to be affected. When Ms. CEO's report breaks, bad things happen.
- zten 7y ago
  Sorry, I noticed you made the same observation in another thread after you got dogpiled by everyone; I left my question over there. Yeah, I've had to contend with that problem before -- hard to imagine how I forgot about it :)

bcrosby95 7y ago

Microservices are primarily about silo'ing different engineering teams from eachother. If you have a singular reporting database that a singular engineering team manages I'm not sure its a big deal. Reporting might be a "monolith" but the system as a whole isn't. Teams can still deploy their services and change their database schemas without stepping on eachother's toes.

BrentOzar 7y ago
> Teams can still deploy their services and change their database schemas No, because as soon as you change your schema, you have to plan ahead with the reporting team for starters. The reports still have to be able to work the same way, which means the data needs to be in the same format, or else the reports need to be rewritten to handle the changes in tables/structures.
- zten 7y ago
  Isn't that a weakness of any data dependency? I could push a new service that deprecates a field and supplies a replacement. I still have to communicate it and get downstream consumers to migrate. Or is the problem that reporting teams are looking at the low-level implementation details, rather than some stable higher-level interface? (I don't know how to avoid that when you're just pulling someone else's database directly or indirectly.)
- ilikehurdles 7y ago
  That's no different than changing your API specification or refactoring your code or anything else. Ideally the entrypoints into your team's services should be considered hard contracts to the outside world and the rest of the organization. Changing the contract out from under your customer is not something that should ever be easy. IMO the devops folks should define some standard containers that include facilities for reporting on low-level metrics. Most of the monitoring above that should be managed by the microservice owner. The messages that are consumed for BI and external reporting should not have breaking changes any more than the APIs you provide your clients should.

1MoreThing 7y ago

Isn't this the whole point of a data lake?

BrentOzar 7y ago
> Isn't this the whole point of a data lake? Yes, but data lakes don't fill themselves. Each team has to be responsible for exporting every transaction to the lake, either in real time or delayed, and then the reporting systems have to be able to combine the different sources/formats. If each microservice team expects to be able to change their formats in the data lake willy-nilly, bam, there breaks the report again.
- iamEAP 7y ago
  This problem exists in monolithic data stores and codebases too. It's not as if independent teams have absolute sovereignty over their table schemas. Schemas evolve as the needs of the product change, and that evolution will always outpace the way the business looks at the data. The best way I've seen to deal with this is to handle this at report query-time (e.g. pick a platform that can effectively handle the necessary transformations at query-time, rather than at load-time).

AnthonyWnC 7y ago

In my last job I was a DevOps guy in a Data Eng. team and we used microservice (actually serverless) extensively to the point that none of our ETL relied on servers (they were all serverless; AWS lambda). Now databases themselves are different stories, they are the persistence/data layer that microservices themselves use . But it's actually doable and I'd even say much easier to use microservices/serverless for ETL because it's easier to develop CI/CD and testing/deployment with non-stateful services. Of course, it does take certain level of engineering maturity and skillsets but I think the end results justify it.

texasbigdata 7y ago
Is there a book or course to buy toward getting an understanding of the more philosophical concepts underneath your approach?

JMTQp8lwXL 7y ago

Whether or not your system intentionally ended up with this architecture, GraphQL provides a unified way of fetching the data. You'll still have to implement the details of how to fetch from the various services, but it gives people who just want read access to everything a clean abstraction for doing so.

benwaffle 7y ago
REST can do the exact same thing.
- nathankunicki 7y ago
  With less flexibility, and more round trips.
  - jsjohnst 7y ago
    > With less flexibility, and more round trips That all depends on the API.
    - nathankunicki 7y ago
      Sure - but if you ever want it to be as flexible as GQL, you need to implement...a query language?
  - MadWombat 7y ago
    I am not sure about that. Both RESTful APIs and GraphQL APIs need to be designed and flexibility is basicaly a measure of how good your API design is. I don't see how GraphQL is intrinsically more flexible.
adamc 7y ago
It's nice to have a clean abstraction, but if the various services are on tables in different databases, things like joins get much more expensive, no? Performance is important.

jsjohnst 7y ago

But I’d argue Monoliths don’t have anything inherent to them which makes reporting easier. A proper BI setup requires a lot of hard work no matter how the backend services are built.

BrentOzar 7y ago
> But I’d argue Monoliths don’t have anything inherent to them which makes reporting easier. It's easier to join tables in databases that live on a single server, in a single database platform, than it is to connect to lots of different data sources that live in different servers, possibly even in different locations (like cloud vs on-premises, and hybrids.)
- jsjohnst 7y ago
  > It's easier to join tables in databases that live on a single server, in a single database platform 1) what if your data set is larger than you can practically handle in a single DB instance? 2) nothing about a monolith implies you have a single data platform, let alone a single DB instance
  - BrentOzar 7y ago
    > 1) what if your data set is larger than you can practically handle in a single DB instance? I have clients at 10TB-100TB in a single SQL Server. It ain't a fun place to be, but it's doable.
jaxn 7y ago
Monoliths have an advantage up until the point where you have to shard the database in some way.

segmondy 7y ago

This is a solved problem, "Data Engineering" teams solve this by building a data pipeline. It's not for all orgs, but for a large org, this is worth doing right.

derision 7y ago
"data pipeline" is just the new trendy phrase for ETL, which the GP mentioned. just because a solution exists, does not make it a solved problem. it's not the right solution for everyone
- segmondy 7y ago
  no it's not. ETL is really database focused and batch focused , Extract, Transform, Load. Data pipeline, is a combination of streams and batch. For example, you can implement a Data Capture using something like https://debezium.io/ https://debezium.io/ Here's how Netflix solves it https://netflixtechblog.com/dblog-a-generic-change-data-capture-framework-69351fb9099b https://netflixtechblog.com/dblog-a-generic-change-data-capt... Overview "Change-Data-Capture (CDC) allows capturing committed changes from a database in real-time and propagating those changes to downstream consumers [1][2]. CDC is becoming increasingly popular for use cases that require keeping multiple heterogeneous datastores in sync (like MySQL and ElasticSearch) and addresses challenges that exist with traditional techniques like dual-writes and distributed transactions [3][4]."
  - cleong 7y ago
    What your saying applys more to ELT, which is an efficient method when using batches. There's no reason why you can't stream ETL.
eganist 7y ago
> It's not for all orgs Then it doesn't seem to be solved. Seems like teams operating at a lean scale would have an issue with this, especially teams with lopsided business:engineering ratios
timClicks 7y ago

WoahNoun 7y ago

I'm going to disagree heavily here. The world of cloud computing, microservices, and hosted/managed services has made the analyst and data engineers job easier than ever. If the software team builds a new dynamodb table, they simple give the AWS account for the analytics team the appropriate IAM permissions and the analytics team will set-up an off-peak bulk extract. A single analyst can easily run an entire data warehouse and analytics pipeline basically part time without a single server using hosted services and microservices. With a team of analysts, the load sharing should be such that the ETL infrastructure is only touched when adding new pipelines or a new feature transformation. And for data scientists working on production models used within production software, most inference is packaged as containers in something like ECS or Fargate which are then scaled up and down automatically. Eg, they are basically running a microservice for the software teams to consume. Real time reporting, in my opinion, is not the domain of analysts; it's the domain of the software team. For one, it's rarely useful outside of something like a NOC (or similar control room areas) and should be considered a software feature of that control room. If real-time has to be on the analysts (been there), then the software team should dual publish their transactions to kinesis firehouse and the analytics team can take it from there. Of course, all of this relies heavily on buy-in to the world of cloud computing. Come on in, we all float down here.

dfabulich 7y ago
Cloud computing helps here, but microservices still make this harder. Some of the data is in Dynamo, some of it is in Aurora, some of it is in MySQL RDS, some of it is in S3, and nobody knows where all of it is at once.
- WoahNoun 7y ago
  From a project management perspective, each data source should have some requirements behind it from the business team. Those requirements should be prioritized meaning you can prioritize which data source to tackle first. You automate the process in AWS data pipelines for that data source, write the documentation for the next analyst, and move on to the next data source. The complexity you and the OP seem to be describing are more in the management and prioritization of analytics projects than in the actual "this is a hard technical problem" domain. It's just a lot of it is tedious especially compared to "everyone just put all your data in the Oracle RACs and bug the DBA until they give you permission" model of the past.

projektfu 7y ago

It's a little sad because originally, people thought there would be a shared data base (now one word) for the whole organization. Data administrators would write rules for the data as a whole and keep applications in line so that they operated on that data base appropriately. A lot of DBMS features are meant to support this concept of shared use by diverse applications. What ended up happening is each application uses its own database, nobody offered applications that could be configured to an existing data base, and all of our data is in silos.

default-kramer 7y ago
Do you know why the shared database vision didn't work out? Because I still think it would be the best approach for many companies. Most companies are small enough that they could spend less than $10k/month for an extremely powerful cloud DB. Then you could replace most microservices with views or stored procs. What could be simpler? I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible (compared to popular languages like C#, Python, etc...) But why has no one written a great DB language yet?
- zerd 7y ago
  I've worked on a service like this. 800k lines of PL/SQL and Java stored procesures (running inside the database, so you could call Java from PL/SQL and vice versa), powered by triggers. * Testing is god-awful. To test a simple thing you had to know how the whole application worked, because there's validation in triggers, which triggers other triggers, which require things to be in a certain state. This made refactoring really hard/risky so it rarely got done. * There's a performance ceiling, and when you hit that, you're done. We did hit a ceiling, did months of performance tuning, then upgraded to the biggest available box at the time, 96 cores, 2TB ram, which helped, but next time the upgrade won't be big enough. You're limited in what one box can do (and due to stored procedures being tied to the transaction there's limits to what you can do concurrently as well)
  - projektfu 7y ago

MadWombat 7y ago

> Some data belongs in Postgres, some in DynamoDB, some in JSON files. Now, how do we do reporting? One of the key concepts in microservice architecture is data sovereignity. It doesn't matter how/where the data is stored. The only thing that cares about the details of the data storage is the service itself. If you need some data the service operates on for reporting purposes, make an API that gets you this data and make it part of the service. You can architect layers around it, maybe write a separate service that aggregates data from multiple other services into a central analytics database and then reporting can be done from there or keep requests in real time, but introduce a caching layer or whatever. But you do not simply go and poke your reporting fingers into individual service databases. In a good microservice architecture you should not even be able to do that.

echelon 7y ago
> But you do not simply go and poke your reporting fingers into individual service databases. This is why I distrust all of the monolith folks. Yes, it's easier to get your data, but in the long run you create unmaintainable spaghetti that can't ever change without breaking things you can't easily surface. Monoliths are undisciplined and encourage unhealthy and unsustainable engineering. Microservices enforce separation of concerns and data ownership. It can be done wrong, but when executed correctly results in something you can easily make sense of.
- adamc 7y ago
  I do not claim expertise here, but it would seem like microservices would add significant performance costs. Stitching together a bunch of results from different microservices is going to be a LOT more expensive than running a query with joins.
  - ddek 7y ago
    That wouldn't be a microservice then. There's going to be a relationship between data in your services, but it shouldn't be directly referential.
  - 7y ago

yibg 7y ago

This isn’t a new problem to microservices though, although maybe it’s amplified. Reporting was challenging before microservices became popular too with data from different sources. Different products, offline data sources etc that all had to be put together. The whole ETL, data warehousing stuff. In the end everything involves tradeoffs. If you need to partition your data to scale, or for some other reason need to break up the data, then reporting potentially becomes a secondary concern. In this case maybe delayed reporting or a more complex reporting workflow is worth the trade off.

cobookman 7y ago
+1, Informatica was founded in 1993. Teradata in 1979. These are not new problems. DataWarehousing has drastically improved recently with the separation of Storage & Compute. A single analyst's query impacting the entire Data Warehouse is a problem that will in the next few years be something of the past.

anbotero 7y ago

Off-topic, but I knew I had seen that name somewhere: https://dbareactions.com/post/183007001237/devops-owns-the-queries-and-the-indexes-but-dba https://dbareactions.com/post/183007001237/devops-owns-the-q... (SRE here, but I work on databases as well all day)

willvarfar 7y ago

https://prestosql.io/ https://prestosql.io/ It can access all those different databases. You can also make your own connectors that make your services appear as tables, which you can query with SQL in the normal way. So if the new accounts micro-service doesn't have a database, or the team won't let your analysts access the database behind it, you can always go in through the front-door e.g. the rest/graphql/grpc/thrift/buzzword api it exposes, and treat it as just another table! Presto is great even for monoliths ;) Rah rah presto.

freewilly1040 7y ago
Do issues arise when trying to match types across different persistence layers?
- willvarfar 7y ago
  In practice, no, haven't hit any.
joefkelley 7y ago
It's been about 4 years since I've been in this world, but I remember there being several products all doing a very similar thing: Presto, Hive, SparkSQL, Impala, perhaps some more I'm forgetting. Is the situation still the same? Or has Presto "won out" in any sense?
- dmitrig01 7y ago
  Hive and Impala are databases. Presto and SparkSQL are SQL interfaces to many different datasources, including Hive and Impala, but also any SQL database such as Postgres/Redis/etc, and many other types of databases, such as Cassandra and Redis; the SQL tools can query all these different types of databases with a unified SQL interface, and even do joins across them. The difference between Presto and SparkSQL is that Presto is run on a multi-tenant cluster with automatic resource allocation. SparkSQL jobs tend to have to be allocated with a specific resource allocation ahead of time. This makes Presto is (in my experience) a little more user-friendly. On the other hand, SparkSQL has better support for writing data to different datasources, whereas Presto pretty much only supports collecting results from a client or writing data into Hive.

tootie 7y ago

This is what gave rise to data lakes. The typical data lake maturity model I see in enterprise is: 1. Pay a ton of money to Microsoft for Azure Data Lake, Power BI, etc. 2. Spend 12 months building ETLs from all your microservices to feed a torrent of raw data to your lake. 3. Start to think about what KPIs you want to measure. 4. Sign up for a free Google Analytics account and use that instead.

jlmorton 7y ago
> Spend 12 months building ETLs Okay, sounds reasonable enough for a complex enterprise. > to feed a torrent of raw data to your lake Well, there's the problem. Why is it taking a year to export data in its raw, natural state? The entire point of a data lake is that there is no transformation of the data. There's no need to verify the data is accurate. There's no need to make sure it's performant. It's just data exported from one system to another. If the file sizes, or record counts match, you're in good shape. If it's taking a year to simply copy raw data from one system to another, the enterprise has deeper problems than architecture.
- satyrnein 7y ago
  If you are "export[ing] data in its raw, natural state" then haven't you lost the isolation benefits of microservices? Now you have external systems dependent on your implementation details, and changing your schema will break them.
  - jlmorton 7y ago
    That's a problem for the future data engineers to deal with. The data lake is an insurance policy so you only need to think about these problems if you later want the data. If you already know you want to analyze the data, then a data lake is not a good choice. Yes, it makes life harder for the data engineers in the future, but it might turn out that analysts only ever need 5% of the data in the lake, and dealing with these schema changes for 5% of the data is easier than carefully planning a public schema for 100% of it. It can be helpful to include some small amount of metadata in the export though, with things like the source system name, date & time, # of records, and a schema version. Schema version could easily be the latest migration revision, or something like that.

Terr_ 7y ago

> now they're stumped as to how they're supposed to quickly get data out I'd argue that (given a large enough business) "reporting" ought to be its own own software unit (code, database, etc.) which is responsible for taking in data-flows from other services and storing them into whatever form happens to be best for the needs of report-runners. Rather than wandering auditor-sysadmins, they're mostly customers of another system. When it comes to a complicated ecosystem of many idiosyncratic services, this article may be handy: "The Log: What every software engineer should know about real-time data's unifying abstraction" [0] https://engineering.linkedin.com/distributed-systems/log-what-every-software-engineer-should-know-about-real-time-datas-unifying https://engineering.linkedin.com/distributed-systems/log-wha...

AmericanChopper 7y ago

Even if you have a monolith, you’re still going to have multiple sources that you want to report on. Even in an incredibly simple monolith I could imagine you’d have: your app data, Salesforce, Google Analytics. Having an ELT > data warehouse pipeline isn’t difficult, and what reporting use case is undermined by the data being a few minutes old?

debrice 7y ago

Where I work we use micro-services through lambda (we have dozens of them) and use DynamoDB for our tables. DynamoDB streams are piped through elasticsearch. We use it for our business intelligence. Took us about a week to setup proper replication and sharding. I don't have a strong opinion on monolith or micro-service, pick one or the other, understand their culprit and write high quality (aka. simple and maintainable) code.

abhishekjha 7y ago
Is there a way to do this for a relational database at scale?
- debrice 7y ago
  We used to do that but dynamodb being very dynamic (which we like) makes it harder to use a declarative schema. ES auto detect the schema which makes it a breeze. I’d say try with JSON fields but I don’t think you’ll get great query speed
- etxm 7y ago
  You can do change data capture with Debezium. Recently the Netflix engineering blog mentioned a tool called DBLog too, but I don’t believe they’ve released it yet.

oneplane 7y ago

Reporting on databases is a rather 90's thing to do. Why would you still actively pursue such a reporting method from the OLTP/OLAP world? If you use tools that are purposely made to work in such a way, an analyst using those tools will obviously not be able to utilise them in an incompatible environment.

skybrian 7y ago

It seems like interacting with customers and enforcing business rules is one job, and observing what's happening is a different concern. Observing means collecting a lot of logs to a reporting database.

throwaway894345 7y ago

> Reporting on a bunch of different databases is a hard nut to crack. Maybe, but your business analyst already needs to connect to N other databases/data-sources anyway (marketing data, web analytics, salesforce, etc, etc), so you already need the infrastructure to connect to N data sources. N+1 isn't much worse.

liveoneggs 7y ago

just recreate the database you broke apart into something less flexible

toriningen 7y ago

Or you could have CQRS projectors (read models), which solve exactly this - they aggregate data from lots of different eventually consistent sources, providing you with locally consistent view only of events you might be interested in. It will lag behind by some extent, roughly equal to the processing delay + double network delay, but can include arbitrary things that are part of your event model. Though, it's not a silver bullet (distributed constraints are pain in the ass yet), and if system wasn't designed as DDD/CQRS system from the ground up, it would be hard to migrate it, especially because you can't make small steps toward it.

nitwit005 7y ago

You can't entirely escape this problem. Even when companies want to fit everything in a single database, they often find they can't. With enough data you'll eventually run into scalability limitations. A quick fix might be to split different customers onto different databases, which doesn't require too many changes to the app. But now you're stuck building tools to pull from different databases to generate reports, even though you have a monolithic code base.

dcolkitt 7y ago

I disagree with the conclusion. While every situation is unique, the default should be separate persistence layers for analytics and transactions. Analytics has very different workloads and use cases than production transactions. Data is WORM, latency and uptime SLAs are looser, throughput and durability SLAs are tighter, access is columnar, consistency requirements are different, demand is lumpy, and security policies are different. Running analytics against the same database used for customer facing transactions just doesn't make sense. Do you really want to spike your client response times every time BI runs their daily report? The biggest downside to keeping analytics data separate from transactions is the need to duplicate the data. But storage costs are dirt cheap. Without forethought you can also run into thorny questions when the sources diverge. But as long as you plan a clear policy about the canonical source of truth, this won't become an issue. With that architecture, analysts don't have to feel constrained about decisions that engineering is making without their input. They're free to store their version of the data in whatever way best suits their work flow. The only time they need to interface with engineering is to ingest the data either from a delta stream in the transaction layer and/or duplexing the incoming data upstream. Keeping interfaces small is a core principle of best engineering practices.

yahyaheee 7y ago

This is a problem but I’m not sure having everything in a single data store is a great idea either. Generally you want your analytics separate from your operations anyway. We do this by having a central ES instance which just informs on the data it needs, which had worked perfectly fine for our needs

campers 7y ago

I've seen that BigQuery has federated querying, so you can build queries on data in BigQuery, BigTable, Cloud SQL, Cloud Storage (Avro, Parquet, ORC, JSON and CSV formats) and Google Drives (CSV, Newlinen-delimited JSON, Avro or Google Sheets)