Voice of the DBA

Tag: database design

Concurrency Challenges Around Schema Changes
I saw a great question on Twitter from Frank Pachot, a developer advocate of Yugabyte. He wrote: Without thinking how your preferred database deals with it, what do you expect if:
- session 1 starts to reads table T
- session 2 drops table T
- session 1 continues to read
The choices in his poll were: session 2 waits, session 2 fails, session 1 fails, both fail. My first thought was SQL Server and the default need for session 2 to get an exclusive lock. In that case, session 2 would wait. Most people answered that same way, but then Frank posted a follow-up with a link to his blog. The answer for Yugabyte is that session 1 fails as it gets the message that the table was deleted.

Leaving aside the decision to drop a table, imagine this is some schema change instead. In the blog, some good points are raised about how to handle high concurrency changes, and the potential problems with having session 2 wait. On a busy system, this could cause lots of blocking as threads stack up behind session 2.

It’s an interesting read about the challenges of distributed system design and how to handle changes. In some sense, I get that this makes sense, but I wonder where this causes issues. If any schema change on the table by session 2 were to cause an error in session 1, that would be bad. However, does this mean that the database engine must now evaluate whether a column change impacts a query in flight? Then decide to send an error? What about evaluating views or procedures/functions that depend on

Does this mean that all nodes need to sync up the schema changes quickly, and at a higher priority than data movements? I don’t know exactly how Yugabyte distributes data, and if there are copies on multiple nodes, but I assume there are. This adds complexity to the communication between nodes, which is likely needed. Honestly, if someone drops a table and they should have, we probably don’t want clients getting results. If they do this accidentally, I’d like to know about it quickly.

The question is interesting, and there are multiple ways to look at this, but I found it fascinating to spend a few minutes thinking about the complexities of data in distributed systems and the challenges involved. This also made me think that the people who keep data safe and fix problems when they occur are invaluable in the modern world.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
June 26, 2023
Data Modeling Information

Data modeling is something that we should all be doing when altering the schema in our databases. I’d like to think that most people spend time here, but I don’t think that’s the case. I think plenty of people think “I need to store a piece of data” and they pick a string or numeric datatype and start stuffing in values. If in doubt, just pick a string. It’s why I think we have lots of dates stored in string columns because that was someone’s first thought.

There was a post recently that talked about storing data in its highest form. It was interesting to me because these are the type of decisions I try to make when designing a table. What is the best form in which to store data? The authors talk about picking not only a type that easily converts, but the fields that make it easiest to work with the data in different ways.

I do think that the aggregations or calculations that we need to perform should influence your data type. If you are measuring something, use a numeric. In fact, in their example of movie times, integer is probably the best type. While many databases and languages have time datatypes, some represent a measure of time (timespan), while others represent a clock (T-SQL time). Either might work for movies, but in aggregations, the T-SQL time will have issues beyond 24 hours. An integer is a better choice, assuming we don’t care about seconds.

The second part of the post looks at multiple values, in this case customer loyalty points earned and redeemed. A simple running sum is what we might store in a database, though the application class might need two fields. Of course, modern software often totals these things for a customer as part of gamification and inducement to engage more, so maybe a data store would also want to store the title earned and redeemed, with a calculation to show the balance.

The one thing that I might add for developers to a post about modeling is the need to consider operations at scale. While using a bit more or less storage often doesn’t matter for any row or any operation on a singleton set of data, when we scale across millions of rows, little things matter. Consider how your data might be aggregated and what happens if you have millions of rows to work on. There a better design decision can out perform a poor one by many orders of magnitude.

That and generate lots of data to test. You ought to know how to quickly mock up a million rows to check your queries. You might have a million rows in production.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

April 19, 2023
Database Design for Tracking Solar Production
We had a solar system installed at our house this year. I’m excited to see how this performs, as our estimates and research shows this ought to be a good financial decision for us over time. While the hardware that came with the system includes some monitoring and reporting, I wanted to track things independently to be sure that I have the data. I know many of these companies might not be as prepared for an issue as I would like, and if they lose some of my historical data, I’m not sure they care.

I decided to set up a small database, which will need an import process along with reporting and this is the first in a series of posts on how I’m addressing the database design. In this post, I’ll look at the initial tables I created.

Estimated Production Table

My system included some estimated levels of production for the year, and I will be able to record the actual levels each day. I decided to track these two sets of data separately for a couple reasons.

First, the estimates are monthly, and they do not vary. While I could just stick this data in the same table, it’s a lot of wasted data. Not a lot of space, but still, I decided to be efficient here. The estimates I have are a total for each month, with the math done to give me a daily power level. I decided to create this table:
```
CREATE TABLE [dbo].[SolarPowerEstimate](
     [TrackingKey] [int] IDENTITY(1,1) NOT NULL,
     [trackingmonth] [tinyint] NULL,
     [estimate_month] [numeric](6, 2) NULL,
     [estimate_daily] [numeric](4, 2) NULL,
  CONSTRAINT [SolarPowerPK] PRIMARY KEY CLUSTERED 

(
     [TrackingKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO
```
This table has a PK just to keep things simple, and then I have a month number, which tracks for which month I have an estimate. There should only be 12 months in this table, as the estimate is supposed to repeat each year. I included the numeric values for the month and daily levels.

The data in this table looks like this:

I can join this with my actual production to compare how well things are working.

Actual Production

For Actual production, there is a value for each day. As a result, I need a date and a numeric value. I decided to separate out the date into separate parts, as I can always combine those, but this is really a data warehouse structure for me and I want to quickly join this with my estimate. I also expect to do some reporting by month, so having the month separated out (and the year) is a quick way to join data without needing a function.
```
CREATE TABLE   [dbo].[solarpoweractual](
     [TrackingKey] [int] IDENTITY(1,1) NOT NULL,
     [trackingyear] [smallint] NULL,
     [trackingmonth] [tinyint] NULL,
     [trackingday] [tinyint] NULL,
     [actual_daily] [numeric](10, 4) NULL,
  CONSTRAINT [SolarPowerActualPK] PRIMARY KEY CLUSTERED 

(
     [TrackingKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO
```
This table will be populated with numbers for the date parts and then the production value. Right now, I see data like this:

I’ll go over reporting and how I use this data in another post, but there is one more table I need for this system.

Staging Imports

I can download data daily, but I really don’t care about the flows of the data each day. The data is reported each 15 minutes, but that’s a bit granular for me. Instead, I want to download monthly data. If I do that, I get a row for each day of the month, but some days are 0 if they are in the future. The current day is also incomplete until the sun goes down, so I may need to update that data regularly.

Rather then try to parse the data and build a complex ETL process, I’m aiming for an ELT, with a T that moves data from a staging table to my actual table with an upsert process.

The csv I get from my monitoring system is a date and a numeric value, so I built a staging table like this:
```
CREATE TABLE [dbo].[SolarStaging](
     [Time] [date] NOT NULL,
     [System_Production_Wh] [varchar](50) NOT NULL

) ON [PRIMARY]

GO
```
My aim here is to truncate this table, load the entire CSV, and then transform data as needed.

Summary

That’s the basics of my solar tracking database. I have a place to land new data, a table for the estimates I have for each month of the year, and then a table that is essentially a fact table of actual values.

I’ll add more details on how I load data, as well as how to analyze the data over time.
April 27, 2022
Eyes Wide Open

Not many of us work in startup environments, but many of us do work with new databases that are created for new applications. These might be carefully designed, thrown together, or your database might be constructed by an ORM. In any case, I find many people make decisions and write database code for today, solving the problems that they see in front of them. they often do this with little data and a single system. They might have their eyes on using some new technology, and they decide on a data strategy without really considering what they will need later.

That seems to be what happened with Expensify, which started in the financial industry. Their system had requirements for low response times, multiple locations, and detailed logging. This required a robust database architecture, which turned out to be helpful when the company pivoted to a new business model. Their CTO talks about some of the problems he sees with startups making database decisions. I think many of these lessons are helpful for all organizations that are trying to ensure their database can grow and meet their needs.

I think one of the most common problems I see is that developers and leaders get enamored with new technology. There is a lot of promise in some of the platforms and designs that are being put forth. Some are even proving themselves in high-profile situations, but not all. For most of us, however, we aren’t going to be solving the same problems in the same way. As the article notes, we’re not Google, but we’re also no Uber, Facebook, or Spotify. Choosing to mimic their choices because of their success doesn’t necessarily map to our business model. I find no shortage of companies that struggle to adopt some new platform because they built a proof-of-concept and assumed the way the system works with small amounts of data. This becomes an issue later with the moderate or large amounts that they have over time.

I also see companies creating complexity, with the chance that they will need to deal with many petabytes or exabytes of data at some point. Face it, most of us will barely deal with terabytes of data in any particular system. We ought to plan for a high-performing system at that scale, not worry about a future that will not likely come. At the same time, we aren’t going to be dealing with megabytes of data, so if your developers only test on MBs, they are going to miss problems.

I like the advice to go into your decisions with your eyes wide open. Don’t copy others, and realistically think about what you will need. I believe that engaging a data professional early is helpful. Developers do some amazing things when they build software, but so often the majority of them don’t really think about the challenges of a database system. They don’t consider low response times or ensuring there are HA and DR (two separate things) strategies. They also forget about the challenges of aggregation and reporting lots of data. Most humans work with a few rows of data at a time, which is what developers do on their systems. When you need to aggregate things, or all your customers are generating a workload, that’s when a data professional can help ensure you’ve properly indexed entities and planned for a demanding workload.

I do like the common sense advice that most startups won’t outgrow a relatively modest single database server. Many applications might not as well, but that doesn’t mean you can put all your eggs in that one server basket. Make sure if it dies that you have multiple people that can recover it and ensure your system is quickly running in another place. There are different ways to handle this, but engage someone that knows your platform and have them ensure you have some staff, operations or developers, that understand how HA and DR work in your environment.

Lastly, be secure. I really like the idea of always using stored procedures. I know this becomes a pain for developers, who now write code in two places, but this really helps you ensure better security, and maybe more importantly, ensures you can tune one part of your code regularly, the database side, without impacting the other side.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

August 30, 2021