Category: Architecture

Louis Davidson resurrects an old article:

When I wrote this post, I had a very strong belief that my OLTP databases are heavy writes and OLAP heavy reads. But in many cases this isn’t exactly the true story. Why? Because you have to read a lot of data to update, delete and display data. Even inserts have reads to look for duplicates, for example. OLAP systems should likely be read more times than write, but loading data can be a lot of data moved around and reads maybe less if the queries are efficient. When I was looking for stuff to post, this post stood out as one that I really liked (other than the aforementioned title!)

I came upon this idea when I found a post by Jason Massie (whose handle was statisticsio back then) that I had referenced, and Kendal Van Dyke alerted me this post at SQL Saturday in Atlanta (this would have been back in 2008 or 2009). After reading that (long gone) post, I wrote this post using sys.dm_db_index_usage_stats, and added references to sys.dm_io_virtual_file_stats as well.

Click through for the script. My expectations going in would be that OLTP and OLAP servers would actually have fairly similar read-write ratios. We talk about OLTP being “write-heavy” and OLAP being “read-heavy” but in practice, you need a lot of write behavior to sustain a warehouse—especially one that isn’t just a “truncate and reload once a day” type of thing—and people care about reading the data from their OLTP systems. In practice, I see the split as more of optimizing for accuracy in data entry (OLTP) versus simplicity of reading data (OLAP).

Lee Asher digs into keys:

Keys come in two basic flavors: natural and surrogate. Natural keys, as their name suggests, are values that occur naturally in the data. They have real-world or business meaning: an email address, the street address of a building, the serial number of an inventory item, etc. In contrast, a surrogate key (sometimes called a synthetic key) is a value added to each row upon creation. A surrogate exists only within the confines of the database; it has no meaning outside of it.

A natural key often contains two or more columns. For instance, the key for a table of vehicle types might include the make, model, year, and trim level, all four columns of which must be combined to create the value that uniquely identifies each row. Surrogate keys are always a single column, though the value of each key may be anything you choose – as long as each value is distinct from all others.

One thing I would very strongly note here is that surrogate keys are a physical data model concept. I’m a firm believer that you almost always should have a surrogate key, but there must be a natural key, even if you don’t put an actual constraint on it. Though I do recommend having a unique key constraint on the natural key as well as a primary key constraint on the surrogate key.

Bijoy Choudhury builds a process:

In any microservices migration, extracting services from all their dependencies and point-to-point integrations carries the most risk. If you feel hesitant about decomposing your application, that hesitation is justified. The first service extraction is uniquely challenging because you have to examine years of accumulated technical debt and unresolved organizational decisions at the same time. 

That’s why the objective for the first service extraction should not focus on achieving immediate scalability or to redefine organizational practices but to validate a narrow capability. Instead, it’s about identifying a discrete unit of functionality that can be isolated, deployed independently, and integrated with the existing system without rewriting the entire system or introducing instability.

There’s some good advice in here, as well as one reason why I’m not totally sold on microservices: the isolation of databases. This sounds great until you’re hitting seven different services to retrieve data 100x slower than a simple SQL query would have been because you have complex filtering criteria across these seven services. And then you build an extra layer of caching, introducing even more complexity to solve a problem that never needed to exist.