At Stack Overflow, our environment has multiple implementations of a largely – but not 100% – identical schema. (By “schema,” I mostly mean the set of tables within a database.) I sometimes need to retrieve or update data across a large number of sites. Let’s say, pulling or removing information from the Users table, and … Read more
Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability – https://en.wikipedia.org/wiki/ACID) properties. Concurrency Techniques Broadly there are three concurrency techniques available … Read more
Welcome back to another replication series! As a quick reminder, we explored various methods of using MySQL’s replication capabilities in our previous discussions. Initially, we employed the traditional binary-log-based replication approach to set up our replication servers. This involved tracking the binary log file and its positions to facilitate replication. In this article, we will … Read more
In the last blog (When PostgreSQL Parameter Tuning is not the Answer), we compared several execution plans for a SQL statement as we made changes to parameters and indexes. Still, there was no mention of what an execution plan is, how one can obtain an execution plan for a query, and how to interpret the … Read more
My initial goal of documenting and exploring SQL Server Row Level Security (RLS) was to show the basic functionality and focus on a few performance and administrative items. I also wanted to confirm my base assumption that it is very secure. This security makes it useful in many situations to segregate data by user groups, … Read more
A challenge that reappears periodically in the world of databases (especially database management) is the need to run code on a subset of databases and to do so in a nuanced manner. Some maintenance or metrics collection processes can be simply run against every database on a server with no ill-effect, but others may be … Read more
In the previous article in this series, I introduced you to the MySQL LOAD DATA statement, which lets you retrieve data from a plain text file and insert it into a table. In this article, I cover the SELECT…INTO OUTFILE statement, a type of SELECT statement that includes the INTO OUTFILE clause. The clause lets … Read more
Writing queries to retrieve the data from a database is probably the single most common task when it comes to working with data. Working with data in PostgreSQL is no exception. Further, PostgreSQL has an incredibly rich, wide, and varied set of mechanisms for retrieving data. From standard SELECT… FROM… WHERE to windowing functions and … Read more
In the previous parts of these MySQL optimization series, we’ve told you how queries work on a high level, then dived deeper into the power of SELECT and INSERT statements. In this blog, I will cover some of the ways to optimize modifying your data too. UPDATE Queries – the Basics As the name of … Read more
Welcome back to the world of MySQL Replication! If you’ve been following this series, thank you for your support! And if you’re new here, welcome! Before we dive in, I suggest checking out the first part of the series to get up to speed. In this article, I’ll be delving into multi-source replication and explaining … Read more
When you’re learning SQL DML, the most complicated clause is typically the GROUP BY. It’s a fairly simple grouping based on values from the FROM clause in a SELECT statement. It’s what a mathematician would call an equivalence relation. This means that each grouping, or equivalence class, has the same value for a given characteristic … Read more
So much about parameters tuning, but does it always help? Welcome to the third and final blog of the “magic of parameters” series. In two previous blogs, we discussed how tuning PostgreSQL parameters could help improve overall system performance. However, the very first paragraph of the very first blog on this topic stated that: Although some … Read more
Did you know you can include a SELECT statement within another SELECT statement? When a SELECT statement is embedded within another statement it is known as a subquery. There are two types of subqueries: basic subquery and correlated subquery. In this article I will be discussing both types of subqueries and will be providing examples … Read more
This article explains what Snowflake data warehouse is and how it is different from other traditional data warehouses. This article will cover how to create virtual warehouses and different types of tables, how to handle semi-structured data, how to profile queries, and the benefits of clustering keys. Practical demonstrations of the concepts explained will be … Read more
MySQL transactions provide an effective method for executing multiple statements as a single unit, making it possible to safely modify data, while preventing concurrent users from updating the same data or overwriting changed data. If any statements within a transaction fail or do not achieve their desired results, the changes can be rolled back and … Read more
So far in the series I’ve shown how to create databases, tables, constraints, indexes and schema. Now, it’s time to put some of that information to work and begin the process of manipulating data within the database. After all, a database is only useful if there’s information stored within. PostgreSQL makes use of standard SQL … Read more
In this article, we’ll be focusing on three of the most used SQL commands in Oracle: INSERT, UPDATE, and DELETE. These commands are used to insert new data into tables, update existing data, and delete data from tables, respectively. In this article I will take a closer look at each of these statements and how … Read more
One of the new Extended Event available in SQL Server 2022 is the query_antipattern. This extended event allows to identify anti-patterns on the SQL queries sent to the server. An anti-pattern in this case is some code that the SQL Server optimizer can’t do a great job optimizing the code (but cannot correct the … Read more
A student of mine sent me a T-SQL challenge involving gap-filling of missing dates and balances. I found the challenge interesting and common enough and figured that my readers would probably find it interesting to work on as well. I’ll present the challenge and three possible solutions, as well as the results of a performance … Read more
This is the beginning of a series on SQL Server Dynamic Data Masking. Dynamic Data Masking is a concept familiar with all developers and users of sensitive data. It is implemented in SQL Server with simplicity and elegance, requiring minimal changes to front end applications, including reporting, and almost no changes to queries. The series … Read more
