Category: Data

Brent Ozar wraps up a survey:

Last week, I asked if your database administrators could read all of the data in all databases. The results (which may be different from this post, because I’m writing the post ahead of time and the poll is still open):

In a lot of cases, this doesn’t really matter much. In places where it does matter (for example, reading protected health information or critical financial data), there should be controls in place. I’ve always been on the side of this issue that says that yes, you do need to be able to trust your administrators at the end of the day, because somebody’s going to need a way to get to that data in case of company emergency. But as a company grows and there are additional opportunities for division of labor and specialization, you do open up the possibility of stronger controls, proper auditing, limiting certain data access to privileged accounts, and consequences for violating the rules.

Comments closed

Hans-Jürgen Schönig compares data sizes:

In this case study, we’ll delve into each of PostgreSQL’s main storage options, their characteristics, and the factors that influence their choice, enabling you to make informed decisions about your database’s storage strategy. You will also learn how you can archive data in a hybrid environment for long term storage. 

Click through for a comparison of two common file formats, plus two PostgreSQL-specific mechanisms for data storage. The comparison here is mostly one of final file size, though common query performance would be interesting to include as well, especially because the columnar data file types (Parquet and Citus-based columnstore) have a very different performance profile versus row-store data.

Comments closed

Semab Tariq deletes a significant amount of data:

In today’s blog, we will discuss another crucial aspect of time-series data management: massive delete operations.

As your data grows over time, older records often lose their relevance but continue to occupy valuable disk space, potentially increasing storage costs and might degrade the performance if not managed well. 

Let’s walk through some strategies to clean up or downsample aged data in TimescaleDB, helping you maintain a lean, efficient, and cost-effective database.

The “or downsample” is huge, by the way: as a simple example, suppose you collect one record every millisecond, or 1000 per second. Say that we have a date+time and a few floating point numbers that add up to 40 bytes per record. If we have a year of data at that grain, we have 40 bytes/record * 1000 records/second * 3600 seconds/hour * 24 hours/day * 365.25 days/year, or 1,262,304,000,000 bytes/year. That’s ~1.15 terabytes of data per year, assuming no compression (which there actually is, but whatever). By contrast, if you keep millisecond-level data for a week, second-level for 3 weeks, and minute-level for the remaining year, you have:

40 bytes/record * 1000 records/second * 3600 seconds/hour * 24 hours/day * 7 days/week * 1 week = 22.53 gigabytes
40 bytes/record * 1 record/second * 3600 seconds/hour * 24 hours/day * 7 days/week * 3 weeks = 69 megabytes
40 bytes/record * 1 record/minute * 60 minutes/hour * 24 hours/day * 337.25 days = 18.5 megabytes

And for most cases, we only need the lowest level of granularity for a relatively short amount of time. After that, we typically care more about how the current data looks versus older data, for the purposes of trending.

Comments closed