Tag: databases

Is there a “best” database engine? Is there one database system that you can use for all of your needs? The question is addressed in a long, but captivating, piece at O’Reilly. The author talks about finding an HTAP database, which stands for Hybrid Transaction/Analytical Processing. In short, the ultimate database that can handle your OLTP, ODS, BI and analytical needs, while also addressing all the other types of features most of us would want.

Tl;dr No.

The piece is worth a read in that it examines the requirements and features needed to achieve a high performing database system. This system doesn’t look at RDBMS over NoSQL or NewSQL systems, and does a fair job of evaluating why trade-offs are needed by many of the types of databases that have been used by companies in an effort to find a better platform. It’s almost a bit of a history of database platforms.

What I like about this piece is that they look at the differences between startup companies and enterprises. The former is bound by legacy constraints, but more importantly, their failures are just inherently less impactful. For enterprises, however, there is a certain inertia, not to mention existing skill sets that make adoption of new technologies more difficult. There’s also that pesky transaction requirement. Some domains might not need these, but in some situations transactions are really important, and as the article states, “Transaction support was useful in certain cases in spite of its overhead.”

Throughout the piece, the author seems to move back and forth in examining the advantages of moving to one platform over the other. We have certainly spent time trying to build large monolithic databases, then lots of effort to move data around to other systems. Who remembers the ODS/large data warehouse trend, which then fractured into further movement to “data marts” before it seems many people have moved back to a large single DW against which analysts can run queries?  Movement of data, gluing complex ETL (or ELT) pipelines together requires a lot of resources, and often these can be somewhat brittle, prone to regular, albeit small, failures.

The nirvana is a single database platform, but I’m not sure that’s possible. All of the requirements we place on databases require some trade-offs. I don’t think anyone has found a way around the CAP Theorem, which will inherently limit the ways in which we can meet many requirements. However, I think we can “fake” the success of our platforms at times, especially since our end users don’t care how we meet their needs. Just that we meet them.

The Azure platform seems to be moving in the right direction, in my opinion. There is a separation of storage from compute, which in database terms means a separation of data storage (the storage engine) from the query engine (where compute resources are needed). Certainly this isn’t complete or clean, but already in Azure SQL Data Warehouse, I can scale up or down my compute needs separately from the storage for my data. The Data Lake allows me to store disparate types of data, without specifying a structure, and query the bits from multiple applications. Even the ability to instantly “copy” a database in Azure SQL Database could be used to “fake” the instant replication of data to another system.

Granted, there are issues with clients constantly querying databases and maintaining up-time (or switching clients to a new copy), with reconciling changing data across many clients, and certainly the management of all this data. However, as we search for ways to build the ultimate OLTP/ODS/BI/Machine Learning database platform, I’d like to remind you we don’t need to build a single engine. We just need to make our clients to think we did by providing a data source that appears to handle all their needs.

Steve Jones

Is a million writes/sec a large number? I guess it’s all relative, but I’d consider it a fairly large number. Our peak transfers for SQLServerCentral are in the hundreds/sec, though we’re not really a busy database server. Some of you might have much higher numbers, and if you’re in the 100,000/sec and can let us know, I’m sure others would be interested in hearing about your experiences.

I ran across a piece on the Uber database infrastructure that I found impressive. No, Uber doesn’t use SQL Server (they use Cassandra), but they have worked to build a large scale infrastructure. Their goals:

• 1 in 100,000 requests can fail
• 1,000,000 writes/sec
• > 100,000 reads/sec

Those are quite impressive. While I’m not sure they’ve ever achieved these levels in production, I’m glad they’re testing at these points. I think far too many people forget to test the limits of where there systems might grow and only stick with where they are today. Or where they were a month ago when they refreshed a test environment from production. Test at larger than production levels, at least once in awhile.

There’s something impressive with one million. Getting to a MB, roughly 1mm bytes, was impressive to me. Not such a big deal now (with pictures requiring > 1MB), but 1mm MB is a terabyte, and while I carry that in my pocket, it’s still an impressive size. Crossing one million members at SQLServerCentral was impressive. I think $1mm is a lot of money. One in a million still seems like a very small chance of an event. At the recent Data Science Summit, we see SQL Server scoring over 1mm fraud predictions/sec.

Achieving 1mm of anything in a database system is still a large number. I know many people have tables with over a billion rows, but I’d still say a million is large. Perhaps you disagree, but I’m still a little awed at seeing SQL Server process a query of 1mm rows in less than a second.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

The “yet another” moniker is used throughout technology. YAAF, YARN, YAML, and more ways that people talk about yet another way to solve some problem or build a platform. That’s one of the amazing things about technology is that we, as a group, are not satisfied with the way things are done. Someone is constantly looking to find a better solution and advance our technology choices in a new direction.

In the database world, we’ve seen changes as well. Relational databases were the obvious, or perhaps just dominant, choice for most of my career. At some point I saw object databases, XML databases, then streaming databases, none of which gained much popularity. The last few years have seen document databases, graph databases, key value databases, and more. We categorize databases as row-store or column-stores now. There are probably other types I’m forgetting.

There are also categorizations of OLTP, OLAP, operational, ODS, data warehouses (DW), etc. that people use. I ran across a piece called “Why you need another database“, whose opening line is “Do you really need yet another database?” The answer is, sure, you need an analytical database, but I think the piece is a bit behind the times. Lots of people have built an analytic database in the form of a relational DW, an SSAS cube, or perhaps some other type of NoSQL store (Hadoop is popular) that allows complex querying of data away from the operational, every day OLTP system.

I would guess many of you have some separate reporting copy of data that allows analysis separately from your online system. This could be a copy of your production database, a data warehouse receiving regular loads, a read-only AG replica, or something else. Some of you might perform analysis in your primary OLTP system, with enough spare capacity to handle the reporting load.

There are multiple ways in which you may decide to implement an analytical database, but I do agree that most businesses ought to have some way of analyzing the data they collect. The questions in the piece (structure, size, analytics), are good ones, and no matter what your platform, architecture, or technology, make sure that you build something that meets the needs of your organization as a whole, not just the technologists that prefer one platform over another.

Steve Jones