Press "Enter" to skip to content

Category: Administration

Handling Large Delete Operations in TimescaleDB

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

Monitoring Node Health in Oracle RAC

Kellyn Gorman continues a series:

After my last blog post on Oracle Real Application Clusters (RAC) I was asked to talk about both health and how performance impact can affect a RAC database. Its architecture enables failover, workload distribution, and offers an option to scale performance, but only when all nodes play well together. When one node drags behind or becomes unstable, RAC has no choice but to protect the rest of the cluster- so help me, Oracle Gods. This protection can come in the form of node eviction, which can be both disruptive and at times avoidable with proactive monitoring and intervention.

Click through to learn how Oracle monitors node health, the types of issues you might run into, and how to prevent node eviction.

Comments closed

Analyzing Snowflake Costs

Kevin Wilkie watches a moth fly out of his wallet and wonders where all of the money went:

Last time, in Dashboard Dreams and Snowflake Schemes, we talked a little about showing how much Snowflake really costs in a dashboard internal to Snowflake itself instead of having to push it to PowerBi, Tableau, Looker, or a myriad of other tools.

This time, let’s take it a step further: instead of sticking with the basic bar charts or exploding pie charts, we’ll explore how to better highlight usage trends by adding a Rolling 7-Day Average to our visualizations. This helps us more easily spot patterns and anomalies within our warehouses.

Read on for a pair of queries and a neat chart.

Comments closed

The Basics of Oracle RAC

Kellyn Gorman gives us a primer:

Oracle Real Application Clusters (RAC) is still one of the most robust instance high-availability and scalability solutions, designed to provide resilience, performance, and continuous service for many Oracle enterprise workloads. Whether deployed on two nodes or a complex multi-node setup, RAC ensures your database infrastructure is both fault-tolerant and responsive under increasing demand.  RAC is an essential part of the Maximum Availability Architecture (MAA) recommended practices, (and in my experience) found in about 40% of small to medium Oracle environments, 98% of large enterprise environments and 100% of Exadata engineered systems. 

In this post, we’ll walk through the architectural foundation of RAC, configuration essentials, and a real-world transactional scenario that highlights the importance of its shared and synchronized architecture.

Read on to learn more.

Comments closed

Data Archival and Retention in PostgreSQL

Daria Nikolaenko walks through a presentation:

I’ve started talking about something that happens with almost every Postgres database — the slow, steady growth of data. Whether it’s logs, events, or transactions — old rows pile up, performance suffers, and managing it all becomes tricky. My talk was focusing on  practical ways to archive, retain, and clean up data in PostgreSQL, without breaking queries or causing downtime.

Read on to learn more.

Comments closed

Auditing SQL Server Login Options

Chad Callihan audits logins:

Do you know who is logging into your SQL Server?

I was once asked about the need to track SQL Server logins. Many servers were already tracking failed logins. Where the issue came up in this case was tracking successful logins to determine login usage. Let’s take a quick look at how we can track both failed and successful logins.

Security-oriented me always wants both failed and successful logins, as you want to know if the person who failed to log in eight times did in fact successfully log in on the ninth attempt.

Comments closed

Checking Valid Configuration Items for Azure SQL Managed Instances

Ben Johnston looks at the art of the possible:

In my last post I described issues that might stop your migration to a SQL Server Managed Instance (SQL MI). This covers configuration items that differ or are not supported in SQL MI. These likely won’t stop your migration, but they could slow you down if you aren’t ready for these changes.

As with previous issues discussed, testing your migration is key. Validate all of your settings and be prepared to make some changes during your migration process. Most of the incompatible options make sense when you think about the purpose of SQL MI – it is controlled by Microsoft. Hardware settings, local file access, high-availability settings, and auditing are configured differently or completely disabled.

Click through to see what you can and cannot do when it comes to configuration.

Comments closed

Thoughts on Index Rebuilds

Kevin Hill shares some thoughts:

Here’s the truth: if you’re doing this daily on indexes smaller than 10,000 pages, you might be chewing up CPU, bloating your logs, and annoying your users  for zero gain.

Let’s fix that.

I disagree with Kevin on index reorganization, in that I would never perform index reorgs—there’s no there there. Even so, I firmly agree with the thrust of Kevin’s argument and believe that, in the majority of cases, companies with DBAs (or people who have stumbled through maintenance plans before) are maintaining indexes too much rather than not enough.

Comments closed

Setting up Physical Streaming Replication in PostgreSQL

Umair Shahid pushes the contents of the write-ahead log to another machine:

Physical streaming replication in PostgreSQL allows you to maintain a live copy of your database on a standby server, which continuously receives updates from the primary server’s WAL (Write-Ahead Log). This standby (or hot standby) can handle read-only queries and be quickly promoted to primary in case of failover, providing high availability and disaster recovery. 

In this guide, I will walk through provisioning a primary PostgreSQL 16 server and a standby server on Linux, configuring them for streaming replication, and verifying that everything works. I assume you are an experienced engineer familiar with Linux, but new to PostgreSQL replication, so I will keep it friendly and straightforward.

Click through for the process.

Comments closed

Managing SQL Agent Jobs with DBADash

David Wiseman shows off an open-source product:

For T-SQL Tuesday #186, Andy Levy asks“How do you manage and/or monitor your SQL Server Agent jobs?”

This is a great opportunity for me to discuss how DBA Dash can help monitor SQL Agent jobs. DBA Dash is a free and open-source monitoring tool for SQL Server, created by me. It’s used to monitor thousands of SQL Server instances within Trimble alone, and it’s gaining popularity in the SQL Server community.

Read on to see how the product can help if you have a series of SQL Agent jobs.

Comments closed