Hacker News Reader

Comment by BrentOzar | Hacker News Reader

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.

View on HN

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.

> 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.

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.

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.

The data lake is also a real, live GDPR PII time bomb if you worked out how to get the data in but not take it out

But if I haven't spent the effort to extract it, do I really own it? Let me argue that I don't have it because all my implemented queries turn up none of your data. You wouldn't tax me on gold that hasn't yet been extracted, would you? (End of joke.)

  But if I haven't spent the effort to extract it, do I really own it?

If you collected it, you are responsible for it.

What I think you'd typically do is put different data under different keys/paths, so that red is personally identifiable data, yellow contains pointers to such data, and green is just regular data. You could have a structure like s3://my-data-lake/{red|yellow|green}/{raw|intermediate}/year={year}/month={month}/day={day}/source={system}/dataset={table}

Then you just don't keep red data for longer than 30 days.

One way to avoid this is to have the microservices to publish data changes to some other (monolith) system, like an MQ system with a specified scheme for the payload.

On the other hand, the notion that “microservices == completely independent of everything else” is an unrealistic one to hold.

Changing the schema of an upstream data source almost always breaks or requires updates to the downstream analytics system. It's an unavoidable problem whether its a microservice or a monolith; you just get to choose where you put the pain.

Consider:

Source Data -> Data Lake -> ETL Process -> Reporting DataWarehouse(s)/DataMart(s) -> User Queries

Source Data -> Data Lake -> User Queries

MonolithDB -> User queries

MonolithDB -> ETL Process -> Reporting DataWarehouse(s)/DataMart(s) -> User Queries

A schema change in the source data should be easily updated in the ETL process in example 1. Most changes are minimal (adding, removing, renaming columns). And for a complete schema redesign in the source data, a new entry in the data lake should be created and the owners of the ETL process should decide if the new schema should be mangled to fit their existing reporting tables or to build new ones. Across the four models I outlined above, the first is by far the easiest to update and maintain, IMO.

If the benefit of microservices is primarily an organizational one around ownership, Conway's Law, and so on, then Example 1 still seems problematic, because it's likely a different team that has to deal with the fallout.

Another strategy is that the service has an explicit API or report specification; that way, the team that owns the services also owns the problem of continuing to support that while changing their internal implementation.

Of course, whether the benefits are worth the cost is probably organization specific, just like microservices in general.

You also lose benefits of microservices if they have to stop and change the data exporting system all the time too, slowing down development.

The main benefit of a dumb copy is that the production service is not impacted by reporting, only a copy is. This relates to performance (large queries) but also implementation time.

Yeah I think you'd want the microservice to expose an bulk export API point, to an API specification. It might need to transform data most likely and possibly ignore some data. And then you grab data from those and piss them into the lake. The lake now conforms to your published APIs.

To me this sounds great. And honestly you should do the same thing with a monolith. Nothing worse than "oh you can't make that schema change because a customer with a read-only view will have broken reports".

The key insight and definition of a data lake was pulling data and storing in it's raw form form across the enterprise.

https://en.wikipedia.org/wiki/Data_lake

The reasons for it are hard to explain succinctly in HN comment but if you look up data lake there will be a lot of explanation. But it basically comes down to "is it better for the data integrators or the data consumers to massage the data?" And data lakes was the insight it's really great when the consumers decide how to massage the data.

This implies that the engineers who lobbied so hard for microservices were even all that concerned with these benefits to begin with, and took this into account when designing the architecture of the system. More often than not, in my experience, the developers involved are more concerned with code ownership than genuine architectural concerns.

What does Google Analytics have to do with datalakes? Are you talking about some specific scenario?

Google Analytics is associated with gleaning useful, actionable insights from your users' behavior on your web sites and in your apps, which was what the guy who sold you on the concept of a data lake was promising.

While this is absolutely true in my experience and Google Analytics has handled most of my needs in contrast with a homegrown data lake or ETL, there's always the spectre of Google pulling the rug out from under you with service shutdown or massive price increase.

Use off the shelf stuff but be prepared to have to move in a (relative) hurry.

"Google Analytics" is, as I understand the original context, shorthand for "something simple, cheap, and immediately useful." Feel free to substitute Mixpanel or some Show-HN-Google-Analytics-replacement Docker image or whatever.

Who besides your hypothetical salesman is arguing that the raisin d’etre for data lakes is user web analytics?

In my experience people arguing for data lakes talk a lot about some unspecified future benefit. Companies typically want to learn things about their interactions with their customers that allow them to make more money, and thus GA or its equivalent represents the 80/20 — or more likely 80% of the benefit for 1% of the cost — solution.

Your statement implies a lot of assumptions about a business’s model. Our company cares about user interactions in our app, software development metrics, quality metrics, sales metrics, etc. GA is just one small piece of the puzzle.

“Data lake” may not be the right answer, but GA certainly isn’t.

Hook Microstrategy up to your lake then. If they just want inbound analytics and conversions then IT were probably recommending a data lake out of their own want to make it, rather than the actual need.

I'm mostly being sarcastic but I'm partly describing an exact scenario that I'm witnessing right now. Business wants "analytics". IT starts spending a load of money. Business has no idea what it's for and buys their own thing to just track website visits which is what they wanted.

Where I was a bit more than a year ago they hired a real expensive consultant who did a data lake project which wasn't finished by the time my team were told to use it, so we rolled our own according to that team's instructions and best practices (best practices were a huge deal at this company, everything we did was best practices). We exported data s3 under a particular structure and I built an ETL system around that and spotify's Luigi. Noone else on my team knew what ETL was, which made me feel old. We spent two, maybe three months on this. The BI team got their data and so did the marketing automation team.

But yeah, it's funny how these projects get complicated in larger organizations. Personally I would have rolled something even simpler on gnu/posix tools and scripts, in rather less than a month.

> 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.

> 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.

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.

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.

Humans are the most expensive part of the system. You have to make it easy for humans to understand and change the system, and at the end of the day that's the number one thing to optimize for. This is why microservices are compelling.

But to speak directly to your concern, you have to think about service boundaries and granularity correctly. Nobody is saying make a microservice out of every conceivable table. Think about the bigger picture, at a systems level. Wherever you can draw boxes you might have a service boundary.

Why would you need to join payment data to session and login data?

Do you need to compare employee roles and ACLs against product shipping data?

These things belong in different systems. If you keep them in the same monolith, there's the danger that people will write code that intertwines the model in ways it shouldn't. Deploying and ownership become hard problems.

The goal is to keep things that are highly functionally related together in a microservice and expose an API where the different microservices in your ecosystem are required to interact. (Eg, your employees will login.)

When the data analytics folks want to do advanced reporting on the joins of these systems (typically offline behavior), you can expose a feed that exports your data. But don't expose an internal view of it to them or they'll find ways of turning you into a monolith.

In my experience it is a lot more difficult to navigate around all the different microservices to understand what needs to be done compared to being in a monolith where you can jump from file to file.

Also then what also happens is microservices are created using different languages which in turn adds so much complexity to understand what is going on on the whole big picture level.

And code gets repeated a lot more. If there is change in a microservices or update everyone will need to figure out what services depend on and how they will have to adapt. With monolith you can just use your IDE to see what will break if you make a change. So much repeated business logic. Creating a new feature involves having to have many meetings to figure out what services in which way have to be updated.

It is crazy mess in my opinion.

I have been with a company that had monolith application which they split up to more than 15 services (some python, some js, Scala, Java, etc...). Monolith still is used for some parts that are not migrated. I was working on single service having no idea how the whole system worked together. Then I had to do something in the old parts and I very quickly got an understanding how everything works together.

Programming complexity is changed to devops complexity.

With microservices, without a good documentation how it connects, it's going to leave a very bad impression.

Having to have that documentation, finding, reading, understanding and trusting it already adds so much overhead.

It is still nowhere close to ability to jumping around with IDE.

It might be in a different language, different design patterns and to get to the details you have to check out that project anyway because you can't document absolutely everything out of code base. And if you do you will end up with multiple sources of truth.

It is so much more likely that for every little issue which you otherwise might be able to find an answer to yourself very easily you will have to contact the team owning that microservices.

It is not only mentally exhausting. It is time consuming, it requires so much back and forth. It creates so much dependence on other people because figuring out how things are related is so much more difficult.

Sometimes I have 8 or more different IDE windows open to understand what is going on.

>And code gets repeated a lot more. If there is change in a microservices or update everyone will need to figure out what services depend on and how they will have to adapt. With monolith you can just use your IDE to see what will break if you make a change. So much repeated business logic. Creating a new feature involves having to have many meetings to figure out what services in which way have to be updated.

This is what people mean when they say "distributed monolith" vs. microservices.

I work on a monolith with a team experimenting in microservices and good lord do I hate it. The microservice represents a required step in our user flow, and due to the way we're set up I have to spin up my own private copy. Very often there have been configuration or API changes that were not communicated to me and so for the past few months that service have been broken and I've managed to avoid it for the most part. When I can't, I find it is faster to simply re-assign existing database records or simply bullshit them in a database editor rather than deal with the "why isn't the XXXXXXXXXXXX service working for me again?" flavor of the day

And holy fuck is debugging that stuff difficult. HUUUUUGE waste of time, but management looooooooooves their blasted microservices...

> you have to think about service boundaries and granularity correctly.

This is the hardest part.. I'd argue that this is almost impossible to do correctly without significant domain modeling experience.. also microservices by nature make this hard to refactor these boundaries (compared to monoliths where you'd get compile time feedback)

I prefer to make a structured monolith first (basically multiple services with isolated data that are squished together into a single deployable) and pull them out only if I really need to... Also helps with keeping ms sprawl under control

If you already can't serve your requests from one DB, and you already want to factor out the analytics stuff, the long running background queries, modularize the spaghetti, scale the maintenance load, CI build + testing time, etc...

That's what SOA and microservices is supposed to solve.

At that scale you do reporting from a purpose-built service.

Allegedly.

We do a lot of reporting that when. Then the users are unhappy that the data is slightly "stale". It serves some purposes, but not all purposes.

> It can be done wrong, but when executed correctly [...]

Quite the self-fulfilling prophecy there.

> Yes, it's easier to get your data, but in the long run [...]

Systems can and should be evolved and adapted over time. E.g. deploying components of the monolith as separate services. You can't easily predict what the requirements for your software going to be in say 10 years.

And depending on the stage a company is, easy access to data for business decisions outweighs engineering idealism.

> Quite the self-fulfilling prophecy there.

Microservices require your organization to have an engineering culture. I would be afraid of introducing them at, say, Home Depot where (I've heard) your average programmer doesn't even write tests.

If you have engineering talent within a small multiplicative factor of Google (say 0.5), then you can pull off Microservices at your org.

Edit: I'm being downvoted, but I don't think it's a dangerous assumption or point to make that it takes a certain amount of discipline and experience to implement microservices correctly. When you have that technical capacity and the project calls for it, the benefit is tremendous.

I think you're being downvoted because you're implying monoliths don't require an engineering culture and that microservices are a silver bullet in getting systems built correctly.

I've seen good and bad in each approach. It's certainly possible to enforce good SOCs and proper boundaries in monorepos, and also possible to plough a system into the ground with microservices.

They're all just tools in your toolbox and both have a part to play in modern development.

You’d be surprised how sophisticated Home Depot is. They switched their monolith to microservices using Spinnaker and even contributed back to Spinnaker.

> easy access to data for business decisions outweighs engineering idealism

I think there are different levels of sophistication of "engineering idealism". GP talks about "data ownership", and I get the desire to keep the data a microservice is responsible for locked in tightly with it. But let's be precise why it's good: because isolating responsibility reduces complexity. Not because code has some innate right to privacy.

In my own engineering idealism, there's no internal data privacy in the system. Things should be instrumentable, observable in principle. If an analyst wants to take your carefully designed internal NoSQL document structure and plug it into an OLAP cube for some reason, there must be a path to doing that; if that's an expected part of the business, the service needs to have it on the feature list, that this should be doable without degrading the service.

Software needs to be in boxes because otherwise we can't handle it mentally, but the boxes really shouldn't be that black.

Isolating responsibility reduces complexity for that piece of code. It increases complexity for assembling the whole thing into a holistic package, which is usually what analytics primary need is.

YMMV, but the tradeoff is less complexity at the SWE/prod department, and more at the analytics team.

> But let's be precise why it's good: because isolating responsibility reduces complexity.

The thing is, it just shifts around complexity. Once you have microservices, you have to deal with a bunch of new failure modes, plus a bunch of extra code whose only purpose is to provide an interface to other services. And in terms of separating data, the worst part is that you've prevent access this data with some other data within the same transaction.

My client is doing a lot of it wrong. To be fair, they got sold a lot of really horrible and ridiculous advice from IBM consultants (is there another kind?), but they also have people in charge (organizationally and technically) who aren't great decision-makers.

As the article says though, you can't fix a people problem (bad engineering practices and discipline) by going from one technology to another (monolith to microservices).

DDD enforces seperation also.

It's about code quality, microservices are easy replaceable. Modules are too.

With both systems, the core part ( eg. mesh, Infrastructure, ... ) Is crucial.

I think experienced developers can see this, the ones that actually delivered products and had big code changes. The ones that handled their "legacy" code.

Microservices are just a way to enforce it, there are others. None are perfect or bad, both have their use-case.

You're saying "monoliths encourage unhealthy engineering" and then in the next sentence say "when executed correctly" for microservices. That sounds like a having/eating cake type situation.

To be fair, this is how I've seen tech decisions presented at most big tech companies.

Quit talking about what is behind the curtain

Not exactly. It's hard to tell from the outside if a monolith was architecture well or is about the fall over.

In a microservice architecture it's harder to pretend you're doing it right.

I disagree, "doing it wrong" just looks different there.

The argument (which I sort of buy) is that microservices provide rails that keep people from doing certain stupid things like N clients depending on the data schema (making the schema a de-facto public interface).

The trick with microservices is that the ecosystem is maturing and there are still lots of ways to screw up other things that are harder to screw up with monoliths. In time 95% of those will go away (my specific prediction is that one day we will write programs that express concurrency and the compiler/toolchain will work out distributing the program across Cloud services--although "Cloud" will be an antiquated term by then--including stitching together the relevant logs, etc and possibly even a coherent distributed debugger experience).

You basically just described Erlang/OTP there.

> In a microservice architecture it's harder to pretend you're doing it right.

After seeing a few of them, I'd say: "it's less embarrassingly obvious that you're doing it wrong."

But dig into the code for a few endpoints and it usually don't take long to find the crazy spaghetti and the poorly-carved-out separation of responsibilities breaches.

> you create unmaintainable spaghetti that can't ever change without breaking things you can't easily surface.

How does creating a tangle of microservices (effectively distributed objects) really solve the problem?

Constructed good, it's Ravioli and not spaghetti.

But that's true of a well-constructed monolith, too. And it has far fewer failure modes and less complexity in general.

Microservices provide an abstraction. That is kind of the point. If you feel like the data yours service operates on would be better off stored in a redis database instead of an RDBMS, you can rewrite your persistence layer, test and roll out the new version of the service. As long as your APIs do not change, nobody cares how you produce responses to requests. In a monolith, this would be a nightmare. You don't have a single persistence layer to change, you have to go through every module, find all the places where this specific table or tables are being accessed and change retrieval and storage functionality everywhere.

Seems to me you are talking about a database access layer instead of microservices.

My understanding of microservices is a bunch of loosely connected services that can be changed with minimal impact to the others

Problem with the ideal is in reality this never works as complexity grows the spaghetti code moves to spaghetti infrastructure ( Done a network map of a large k8s / istio deployment lately ? )

The impact would be minimal only if the API of the microservice didn't change. But in the same codebase too, if you have a module whose API doesn't change the changes from refactoring it would likewise be minimal.

> Microservices provide an abstraction.

So do modules/classes/interfaces etc. You don't need a layer of HTTP in between components to have abstraction.

In addition, it feels like microservices solve a problem that very few people really have. I've never run into a case where I though "boy, I'd sure like to have a different database for this one chunk of code". If that did happen, then sure, split it out, but I can hardly believe that splitting your entire code base into microservices has a net benefit. The real problem in nearly every project I've worked on is complexity of business logic. A monolith is much easier to refactor, and you can change the entire architecture if you need to without having to coordinate releases of many different applications.

> enforce separation of concerns and data ownership

You can enforce separation of concerns and data ownership in a monolith just as much as you can not enforce these two characteristics in a micro service architecture. Microservices and monoliths are a discussion about deployment artifacts, full stop.

Only when done by folks that never learned how to write modular code and package libraries.

The same folks aren't going to magically learn how to do distributed computing properly, rather they will implement unmaintainable spaghetti network calls with all the distributed computing issues on top.

And untangling a monolith tends to be much less problematic that untangling a bunch of microservices. For one thing, you can refactor/untangle it all offline, do your testing, and do a single release with the updated version, as opposed to trying to coordinate releases of a bunch of services whose interfaces/boundaries were poorly defined.

In practice, splitting your code into consumable libraries/modules works equally as well.

Then your monolith is just all the modules glued together.

Splitting code into libraries works better, it's simpler and faster. The only thing micro services bring to the table is being to deploy updates independently (although this is also possible with libraries). If you don't need to deploy independently then micro services are useless complexity, if you can't deploy independently then you've got a distributed monolith.

>Microservices enforce separation of concerns

Depending on where you work, it can be a problem, because the separation is not always appropriate, and can for political reasons be much harder to revert when visible at the service level (for example because the architect doesn't understand consistency, or because your manager tells you that the distributed architecture documentation has been sent to the client so it cannot be modified).

In case of undue separation, reworking the internals of the enclosing monolith should have less chance to cause frictions.

> 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.

I agree. In a monolith architecture, though, you CAN do that (and many shops do.) That's where their pains come from when they migrate from monolith to microservice: development is easier, but reports are way, way harder.

> when they migrate from monolith to microservice: development is easier [...]

Not even that -- that idea is still highly debatable.

I would argue that it absolutely isn't easier, and the stepping-back-in-time of developer experience is one of the biggest problems with microservices.

Microservices in general, are way, way harder.

> you do not simply go and poke your reporting fingers into individual service databases

Side point: This is a needlessly hostile and unprofessional way to refer to a colleague. Remember that you and the reporting/analytics people at your company are working towards the same goals (the company's business goals). You are collaborators, not combatants.

You can express your same point by saying something like "The habit of directly accessing database resources and building out reporting code on this is likely to lead to some very serious problems when the schemas change. This is tantamount to relying upon a private API." etc.

We can all achieve much more when we endeavor to treat one another with respect and assume good intentions.

This is an incredible overreaction to an entirely innocuous comment.

I've noticed reporting/analytics people going extinct around my workplace as micro services make monitoring easier. There might be some pent up hostility towards the technology side

If you think telling colleagues not to "simply go and poke your reporting fingers into" things won't insult them or put them on a defensive footing, I encourage you to try it and closely note the reception you receive. In my experience, people do not appreciate being spoken to like that.

They didn't tell their colleagues to do that, they made a slightly humorous comment on a hacker news thread.

We’re colleagues by virtue of the fact that we’re members of the same profession.

Anyway, what’s the reason not to treat people on hackernews with the same respect you’d treat a coworker with?

I think I prefer the poking around analogy. I can immediately visualize why it's bad, and it doesn't have the word "tantamount".

I came here simply to echo this statement! Design a reporting solution that is responsible for ingesting data from these micro services' persistence layers. Analysts should only ever be querying this reporting solution and should not be allowed to connect directly to any micro service persistence layer or API.

We have a whole industry around Analytics and Data and the tools and processes to build this reporting layer is well established and proven.

Nothing will give you as many nightmares as letting your analysts loose on your micro service persistence layers!

This is seriously why my company still has monoliths.

Our databases are open to way too many people. What's worse, they are multi tenant making refactoring really hard.

Having more than one schema owner is practically a death sentence for development and engineering...

We used to have a few of those, especially on exadata clusters. Finally carted them out of the local dc after moving to RDS Aurora databases with strict policies. Might have caused 3 or 4 people to quit, but totally worth it for the 500+ people that stayed who now can own their data, schema and development (and be held responsible for it! -- another issue of multi-db-access, it's always someone else's fault). Went from deploying once a day with a 'heads up' message to no-message deploying multiple times per hour.

Why monoliths? Everyone still wants to to have OLAP and OLTP systems where analytics are done on OLAP. Where having this separation you can get data from multiple sources to put into your analytics.

I cannot imagine people not doing that and having need to have stats in real time. For most shopping/banking stuff you can get away with once in 24 hours dumps and then analytics can be done on that.

What you are describing certainly isn't unique to microservices.

Sorry, but "making an API that gets you this data" is the wrong answer.

Most APIs are glorified wrappers around individual record-level operations like- get me this user- or constrained searches that return a portion of the data, maybe paginated. Reporting needs to see all the data. This is a completely different query and service delivery pattern.

What happens to your API service written in a memory managed/garbage-collected language when you ask it to pull all the data from its bespoke database, pass it through its memory space, then send it back down the caller? It goes into GC hell, is what.

What happens when your API service when it issues queries for a consistent view of all the data and winds up forcing the database to lock tables? It stops working for users, is what.

There are so many ways to fail when your microservice starts pretending it is a database. It is not. Databases are dedicated services, not libraries, for a reason.

It is also true that analysts should not be given access to service databases, because the schema and semantics are likely to change out from under them.

The least bad solution? The engineering team is responsible for delivering either semantic events or doing the batch transformation themselves into a model that the data team can consume. It's a data delivery format, not an API.

>It goes into GC hell

Can you exapand on this a little? Or a paper that I can read?

The parent comment said “is asked for all records..GC hell “.

Since a micro service deals with only its own data and reporting is then across services, we’d need to query across services to get data and make sense of it. If we’d ever need to query all records, then such records would become domain objects in the micro services first before being passed along. A large number of domain objects would require a large amount of memory. Processing and releasing domain objects will result in GC on the released objects.

The service will need to read all its data and put it into objects, then extract the data from the objects to report it, then garbage collect all of that. For every single record in its entire data set.

You could say but oh, why not just return the underlying data without making objects? Well now you are exposing the underlying data format, which is what we’re trying to avoid by giving this job to the service.

And thus such patterns lead to the absurdity where 90% of enterprise apps do little actual computations beside serializing and deserializing JSON (or XML if a "legacy" app).

It's remarkable what you can do with just functions and nested data structures. Used to be big on the whole OOP thing, data roles, so much effort for so little.

Now I try to think about problems as "I have input data of shape X, I need shape Y" and fractally break it down into smaller shape-changes. I am kinda starting to get what those functional programmers are yammering on about.

Wait, I would assume that the people in need of reporting would have a pretty good idea of what those reports should look like. That means you know exactly what data needs to be read from a data store optimized for reporting. Each micro-service contributes their share of data to a data store optimized for reading. This is a text-book use case for a non-relational document store. I'm really not seeing what's so difficult about building such a process.

Reporting and non-relational are like oil and water, coming from experience working with people who make reports.

It’s not like they come up with every report they think they might need while the micro service is being architected. They come up with a new report long after engineers have moved on. If it’s a SQL database, no problem. If it’s some silly resumeware data store, then what?

Yeah, this.

If you can't ask questions you didn't think of in advance, you didn't collect data.

>It is also true that analysts should not be given access to service databases, because the schema and semantics are likely to change out from under them.

Its not perfect but what we do is create a bunch of table views that represent each of the core data types in the system. We can then do all of the complex joins to collect the data analysts want in to an easy to query table as well as trying to keep the views consistent even as the db changes.

So you have a single database for all your microservices?

Real question:

Why pull it into memory like that? Why not just pump it through a stream?

A stream would be the correct way to handle that problem, so backpressure can be used to prevent too much memory churn.

A co-worker had a smart solution for this: your service's representation in a reporting system (a data warehouse for example) is part of its API. Your team should document it, and should ensure that when that representation changes information about the changes is available to the people who need to know it.

This really makes sense to me. I love the idea that part of a microservice team's responsibility is ensuring that a sensible subset of the data is copied over to the reporting systems in such a way that it can be used for analysis without risk of other teams writing queries that depend on undocumented internal details.

your service's representation in a reporting system

At what point in time?

At the beginning: https://docs.pact.io/

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.

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.

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.

Also one of the service teams might need to change their schema, which the reporting team needs to adjust their process to handle that. That's fine, but they need to know that in advance, and then they might have a backlog of other things that they need to do, and then some other teams schema changed without notice, so now they always have to play catch-up.

What/where do you run this mythical one-analyst pipeline, though? Is that in cloud services too? Airflow? Kubeflow? Apache Beam? It sounds like you're just pushing the problem around.

AWS data pipelines and AWS lambda. It's cloud services the whole way down.

https://aws.amazon.com/datapipeline/

I saved a company 20k a month by creating a job server in AWS. Lambda isnt cheap when you start using it hard

Lambda is mostly used for it's trigger functionality for data or artifacts that are created at irregular intervals. Eg, an object is uploaded to s3 which triggers a lambda which runs a copy command for that object into redshift. The kind of stuff that's well below the threshold for leaving the free tier.

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.

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?

> I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible

Where did you get that from?

SQL is actually amazing at some things that are very difficult, verbose, or slow to do in C# or Python.

DBAs were a bottleneck. In the best case, you’d throw your application’s data needs over the wall to the DBAs (remember, this was the waterfall era) and hope they’d update the schema for you in a timely manner. In the worst case, the DBAs were petty tyrants who stifled progress. In the worser case, they were incompetent and you ended up with all these applications directly reading and writing the database, making schema changes impossible and coupling applications in obtuse, incredibly difficult to fix ways.

In any case, designing a single schema that encompassed all the needs of the organization and could grow and change as the organization did was nearly always too much to ask.

This was in the days of enterprise data modeling where people believed there really was just one data model or object model that could represent the whole org, independent of the needs of any given application. I don’t think anybody believes that any more.

For workloads which are read-heavy, I think a single database can be a great solution -- a small monolith has exclusive write access to the db, and any number of polyglot read-only services are connected to as many read-replicas as are needed for horizontal scaling.

For write-heavy workloads, best of luck to you :)

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)

I think they go too crazy with the stored procedures. I would rather see constraints and views to make the data work. Triggers are useful for simple behind-the-scenes things like logging, or creating updateable views, but their behavior should be kept simple. If they're sending e-mails and launching missiles, it's probably too much.

Debugging PL/SQL without the PL/SQL debugger is nearly impossible. Unfortunately a lot of shops cheap out on developer tools after they buy the server licenses. I never liked the idea of Java on the database. The good thing about PL/SQL is that nobody wants to write it so it has a tendency to not be overused by most developers.

The performance ceiling probably wouldn't be too low if there weren't too much extra activity with each update. As with all databases, sharding and replication are your friend.

> 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?

SQL is actually hard to compare to programming languages, because in SQL you say what you want, while in iterative language you say how. I only know one language that was competing with SQL (and lost) it is QUEL (originally it was used by Ingress and Postgres).

BTW for triggers and stored procedures you actually can use traditional language, I know that PostgreSQL supports Python, you just need to load a proper extension to enable it.

Probably for the same reasons that waterfall development doesn't work out. This approach requires up-front specification of the data before the application domain is well understood. Any application desiring a migration to a different schema would need to work with the others to do it. Finally, developers bristle at the idea that they have to wait for another team to get their job done. In the absence of a strong company policy, it will inevitably drift toward shipping an application rather than keeping everything together.

Perhaps refactoring, had it been better understood around 1970, could have gone a long way toward harmonizing diverse schemas, allowing experimentation with eventual refactoring into the common database.

Our current environment makes this impossible. There's no way that Salesforce is going to ship a version that works with your company's database schema. You're going to have to supply that replication yourself. Same for Quickbooks. To get that kind of customization you need to be spending hundreds of thousands for enterprise software.

I didn't make this clear in my original comment, but I was envisioning that an app like Salesforce would use its own schema, but still live in the same DB with other schemas. I'm going to assume that Salesforce has some notion of an Employee. Salesforce would use its own Employee table by default, but it would provide extension points (views and SPs) that would allow you to read and write Employee data from tables in another schema if you want. This might be preferable to duplicating Employee data.

Edit - I just saw that you addressed this in your original post: "nobody offered applications that could be configured to an existing data base"

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.

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.

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

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.

+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.

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.

> 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.

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.)

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.

"That's no different than changing your API specification"

This is a great point. The way to make backward-compatible changes to an API is by adding additional (JSON) keys, not changing / removing keys. The same approach works for a DB -- adding a new column doesn't break existing reporting queries.

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.

>> 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.

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.

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.

Sure, I've done this many times myself. The big caveat is that this only works if your data/update velocity is relatively low. I've seen many operational data models where the data warehouse would never be able to keep up with the operational data flow. Due to the rapidly increasing data intensity of business, there is a clear trend where this latter situation will eventually become the norm. I've already seen instances at multiple companies where Kafka-based data warehouse pipelines are being replaced with monolith architectures because the velocity of the operational system is too high.

For it to work, online OLAP write throughput needs to scale like operational databases. This is not the case in practice, so operational databases can scale to a point where the OLAP system can't keep up. The technical solution is to scale the operational system to absorb the extra workload created by the data warehousing applications, but current database architectures are not really designed for it so it isn't trivial to do.

Our data is near real time. But I can see how latency can be an issue based on size of the data and transformations are needed on the data before it hits the DW. But there are solutions - kafka being one.

Well, it's debatable if that is "solving" the problem or just hiding or mitigating it.

This phrase was new to me: https://en.wikipedia.org/wiki/Data_lake

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.

> 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."

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/

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.

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!

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/) for MySQL, Postgres, MongoDB, and more, using Apache Kafka as the messaging layer.

Disclaimer: working on Debezium

Curious how support is for commercial databases like SQL Server and Oracle? In the larger, older, enterprises these databases are rampant...

Data Warehouses are (usually) not optimized for individual inserts, updates, deletes (DMLs). Loading data into DWHs is usually done through copy/ingestion commands where aggregated/batched data is copied from blob storage (s3, azure, etc...) to a staging table in the DWH.

In a non-append-only scenario, Debezium tracks each source operation (insert, update, delete) from the replication log (oplog, binlog, etc) as an individual operation that's emitted into a kafka topic. How does one efficiently replicate this to a Data Warehouse in an efficient manner?

I have not been able to use Debezium as way to replicate to a Data Warehouse for this very reason. At least not without having to resort to very complicated data warehouse merge strategies.

Note, there exist Data Warehouses that allow tables to be created in either OLAP or OLTP flavor. I understand that Debezium could easily replicate to an OLTP staging table. But are there any solutions if this isn't an option?

> You put Kafka on top of your database

Your casual tone is at odds with what I've seen when teams run Kafka clusters in production. Not a decision I would take so lightly.

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.

"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

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/

Here's how Netflix solves it 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]."

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.

> 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

The remark wasn't that there weren't solutions, but that it is still a problem that needs a solution. Storing all data within a single database is a much simpler way to get started if you want to run analytic queries. You spin up a read slave that is tuned for expensive queries, rather than OLAP ones.

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.

You just subscribe a reporting service to certain topics , done .

> 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.

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 :)

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.

Do issues arise when trying to match types across different persistence layers?

In practice, no, haven't hit any.

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?

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.

I think some of this might be misinformed.

I know Hive can definitely query other datasources like traditional SQL databases, redis, cassandra, hbase, elasticsearch, etc, etc. I thought Impala had some bit of support for this as well, though I'm less familiar with it.

And SparkSQL can be run on a multi-tenant cluster with automatic resource allocation - Mesos, YARN, or Kubernetes.

Presto can't deal with ElasticSearch. It can query basic data but it's not optimized to translate SQL to native ES query (SQL ES query is for paying customer).

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.

Is there a way to do this for a relational database at scale?

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

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.

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.

> 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.)

> 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

> 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.

Monoliths have an advantage up until the point where you have to shard the database in some way.

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.

REST can do the exact same thing.

With less flexibility, and more round trips.

> With less flexibility, and more round trips

That all depends on the API.

Sure - but if you ever want it to be as flexible as GQL, you need to implement...a query language?

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.

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.

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?

> 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.

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.

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...

> 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.

> Reading other comments Brent’s made here, I’m not so sure.

No no, it's not a good idea, but it's just the reality that I usually have to deal with. I wish I could lay down rules like "no analyst ever gets the rights to query the database directly," but all it takes is one analyst to be buddy-buddy with the company owner, and produce a few reports that have really high business value, and then next thing you know, that analyst has sysadmin rights to every database, and anybody who tries to be a barrier to that analyst's success is "slowing the business down."

Pretty trival to setup read replicas using log shipping in MSSQL and postgresql to offload analytical loads to secondary servers.

I work on a monolith that does this, but its usually not even necessary a single db server on modern hardware with proper resource governing can handle quite a bit.

> Uhh, yep, that's exactly how a lot of businesses work.

Just because a lot of businesses do it, doesn’t mean it’s a good idea. A lot of businesses don’t do source control of any kind, so should we all do that too?

There are many business environments where copying the data is effectively intractable so you need to run all of your data model operations in a single instance. More data models have this property with each passing year, largely because copying data is very slow and very expensive.

This is not a big deal if your system is designed for it, sophisticated database engines have good control mechanisms for ensuring that heavy reporting or analytical jobs minimally impact concurrent operational workload.

That's the data warehouse option.

I'd probably reach for setting up a read replica (trivial) before completely overhauling my architecture.

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

> 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-wha...

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.

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.

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

> 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.

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).

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.

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)

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?

Off-topic, but I knew I had seen that name somewhere: https://dbareactions.com/post/183007001237/devops-owns-the-q...

(SRE here, but I work on databases as well all day)

> 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.

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

> Reporting on a bunch of different databases is a hard nut to crack.

It's not necessarily a bad idea though :-/

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/

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.

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.

just recreate the database you broke apart into something less flexible

Reply on news.ycombinator.com