For a few years now, to some extent I’ve been involved in commercial matters in addition to spending nearly all my time leading a development team. That naturally involves all kinds of technical tasks, design and architecture decisions, gathering customer feedback, and so on.
During this time, I’ve been struck by how many potential customers of the products we sell are interested in knowing which database engine our software uses. Some give SQL Server their full blessing; others wouldn’t trade Oracle for SQL Server; and a few have asked us whether the system could run on Cassandra (…). I have no objection to that, except for their completely mistaken belief that the product will be fast and efficient, or slow and not very scalable, simply depending on which database it uses.
I’ve seen this assumption—which is understandable for a certain non-technical profile—in other kinds of highly technical people who know a great deal about software, too, much to my surprise.
The scalability of a software product doesn’t necessarily depend on the database it uses—not by a long shot. The database is just one item on a long list of characteristics we should expect from a scalable software product.
I’ve had to endure this kind of debate not only in the eternal SQL Server-versus-Oracle argument, but also in the way anything involving large volumes of information is immediately associated with a need for a big data solution such as Hadoop or MongoDB, confusing storage needs with the need to analyze and process information.
But what do we mean by scalability? It depends on the kind of product we’re talking about. For a web portal, it certainly means supporting a large number of concurrent users. For a data analysis application, it means being able to ingest and process a certain volume of information. For a system that manages physical devices, such as digital meters—to mention something closer to my own work—there’s a big difference between managing a thousand and a hundred thousand devices in one system, with five or a hundred operators working concurrently.
If a software product uses a database, whatever its kind (relational, NoSQL, document-oriented, and so on), the database alone does not determine the product’s performance.
This may seem obvious, but I’ve had to explain it more than once to people who develop software…
What makes for good use of a database?
This is an exceptionally broad topic, but to show that using a database well is far from trivial, we can say that its design must have no flaws or unnecessary redundancy; queries should be simple and efficient; the number of queries should be minimized; each query should retrieve only the information strictly necessary; transactions should be reserved for operations that really need them; the necessary indexes should be carefully studied based on the nature of the queries; depending on the case, we should choose efficient structures offered by the database engine itself (horizontal and vertical partitioning, for example); we shouldn’t mix a database intended for historical storage with the operational database; and, if the product’s nature allows it, bulk data processing should be scheduled for times that don’t interfere with customers’ operating windows—to name just a few considerations.
If these things aren’t handled well, even the best database engine won’t help us.
In my experience, I’ve clearly seen how the way information is stored determines how easy or difficult it is for high-level client software to manage it. Sometimes the problem isn’t how we access the information, but how that information is stored in the data repository.
How we need the information and how it is stored go hand in hand.
We can improve things at that level by making our application access the database more effectively, but I’m afraid that won’t completely solve the problem.
What about the system’s own architecture?
In a sense, this architecture emerges as we try to support more and more devices, users, or whatever entities represent scalability for our product.
However, when we’re talking about systems that process large volumes of information, tasks, processes, and so on, the software system’s architecture is fundamental. At this point, we’re talking about developing software at another level, with different design strategies and decoupling between subsystems so each can perform its task very efficiently.
There are no general strategies that solve every specific case, but there are good architectural practices.
Distributing these subsystems across different servers is a design challenge in its own right: every instance must be able to do its work concurrently and balance the workload, whether or not we rely on third-party systems for this purpose.
An application that works well for ten concurrent users is completely different in its design, architecture, and data repository design from the same application if it has to serve 10,000 users, for example. The architecture will be nothing alike; the overall design and micro-designs will differ greatly from one system to another, as will the database’s design and use.
The thing is, we rarely begin developing a new product knowing for certain how scalable it will need to become, or when in its lifetime it will need to support more users, devices, and so on. It takes a great deal of experience to anticipate the right architecture and designs. So what can we do?
There are almost no magic solutions, but I can say this: if we start with clean designs, put a great deal of effort into producing decoupled, high-quality code, build a sufficiently broad, thorough, and maintainable test suite, and work at every stage of product development to identify bottlenecks through performance analysis, and so on, we can build a system that won’t be a nightmare to scale.
In certain complex, fairly large systems, everything—absolutely everything—matters. A simple iteration through the elements of a list seems harmless, but when it runs on a server hundreds of thousands of times a day, it can cause a performance problem whose effects accumulate disastrously with others, eventually creating a system-wide performance problem. The details really do matter.
Writing code that works is our job. So is writing it to work as cleanly and simply as possible—and to be efficient, which isn’t always so trivial.
There are books devoted to this topic, including one I often turn to: Pro .NET Performance, to name one example.
Another resource I use frequently, related to the technologies I use most, is Improving .NET Application Performance and Scalability.
Finally, nothing is worse than finding performance problems after the product is already in production. If that happens, the system probably wasn’t tested well enough beforehand. We need to run not only unit, validation, and integration tests, but performance tests as well.