Press "Enter" to skip to content

Category: Administration

An Explanation of PostgreSQL’s Citus Extension

Published 2025-03-19 by Kevin Feasel

Craig Kerstiens covers a misunderstood extension:

Citus is in a small class of the most advanced Postgres extensions that exist. While there are many Postgres extensions out there, few have as many hooks into Postgres or change the storage and query behavior in such a dramatic way. Most that come to Citus have very wrong assumptions. Citus turns Postgres into a sharded, distributed, horizontally scalable database (that’s a mouthful), but it does so for very specific purposes.

Read on to learn when Citus can work well, when it isn’t a good fit, and a few architecture and design recommendations around using the extension.

Comments closed

Identifying an Object Name from a Wait Resource

Published 2025-03-18 by Kevin Feasel

Haripriya Naidu wants to know what object this is:

You run a query to check for locking or blocking in SSMS and find a wait resource in the format (8:1:3610). To identify the object name, you would typically run multiple queries, first find database name, turn on trace flag 3604, then find object id from DBCC PAGE and then find object name from sys.objects.
However, with a new function “sys.dm_db_page_info” introduced in SQL Server 2019, you no longer need to go through these steps. Instead, you can run a single query to get the object name directly.

Read on to see how it all works. This is definitely a lot easier than in the olden days.

Comments closed

Vacuuming Delta Tables in Microsoft Fabric

Published 2025-03-13 by Kevin Feasel

Kenneth Omorodion explains why you sometimes need to bust out the VACUUM:

Efficient data management in Microsoft Fabric is a necessity in maintaining large-scale partitioned Delta tables. In dynamic datasets with frequently generated new files, the need to ensure the removal of stale files becomes very important to prevent storage bloating. In settings with partitioned tables, where data is in a hierarchical structure (e.g., by year, month, day), this can be particularly challenging, and files must be cleaned without disrupting active data. Learn how the VACUUM operation can help optimize delta tables.

Read on to learn more.

Comments closed

An Introduction to pgNow

Published 2025-03-11 by Kevin Feasel

Ryan Booz introduces a new free tool from Redgate:

pgNow is a free, cross-platform desktop tool created by Redgate that helps you identify key performance metrics and configuration optimizations in your running Postgres instance. Available now as a public preview application, it’s designed to help when you’re in a pinch and don’t have the Postgres experience or monitoring solution already in place to help identify why your server or database is experiencing a degradation in performance. Even in its current preview offering, pgNow is a helpful front-line tool for troubleshooting your Postgres cluster. And I couldn’t be more excited to share it with you.

Click through to see what’s in the preview. Ryan has mentioned that the tool will remain free even when it’s in a stable release.

Comments closed

Automating Management of Extended Statistics in PostgreSQL

Published 2025-03-10 by Kevin Feasel

Andrei Lepikhov builds an extension:

The extended statistics tool allows you to tell Postgres that additional statistics should be collected for a particular set of table columns. Why is this necessary? – I will try to quickly explain using the example of an open power plant database. For example, the fuel type (primary_fuel) used by a power plant is implicitly associated with the country’s name.

Click through to learn more about what extended statistics are and the nature of the extension.

Comments closed

Microsoft Fabric Shortcuts and Lakehouse Maintenance

Published 2025-03-07 by Kevin Feasel

Dennes Torres has a public service announcement:

I wrote about lakehouse maintenance before, about multiple lakehouse maintenances, published videos about this subject and provided sample code about it.

However, there is one problem: All the maintenance execution should be avoided over shortcuts.

The tables require maintenance in their original place. According to our solution advances, we start using shortcuts, lots of them. Our maintenance code should always skip shortcuts and make the maintenance only on the tables.

Click through to see how you can differentiate shortcuts from actual tables and write code to avoid shortcuts.

Comments closed

Default Tenant Settings Changes in Microsoft Fabric

Published 2025-03-03 by Kevin Feasel

Nicky van Vroenhoven notices a change:

In case you have access to the M365 Admin Center, or more specific the M365 Message Center, you might have seen this message. I reckon not many people did.. That’s why I’m blogging about it here

I’m specifically talking about this message in the Message Center, being a major update and with admin impact: 

Communications on default checkbox changes on tenant settings and billing start for SQL database in Fabric.

Read on for more information about what’s changing.

Comments closed

Common Reasons for Emergency SQL Calls

Published 2025-03-03 by Kevin Feasel

Kevin Hill compiles a list:

If you are a production DBA (or Accidental prod DBA) you’ve gotten that frantic call in the middle of the night. Or maybe during little Suzy’s soccer game? Something broke, alerts are firing, nobody can order your widgets and the help desk tech has no idea where to start.

I’ve been on call since 2006. These are the most common things I’ve been called for at the worst times:

Read on for Kevin’s list. It’s a good one. I also get my share of “replication broke” but fortunately for most people, replication is pretty uncommon in environments.

Comments closed

Maintaining a Heap Table in SQL Server

Published 2025-02-24 by Kevin Feasel

Lori Brown performs maintenance:

I have a customer who let me know that some of their tables had a large amount of unused space in them.  They were wondering if I could get them to release the space.  After doing some investigation, I found that all tables with the huge amount of unused space were heap tables.

Read on to see how this is possible. Lori has a bonus script for us as well.

Comments closed

A List of PostgreSQL Parameters

Published 2025-02-21 by Kevin Feasel

Semab Tariq has a list:

Have you ever experienced your database slowing down as the amount of data increases? If so, one important factor to consider is tuning PostgreSQL parameters to match your specific workload and requirements. 

PostgreSQL has many parameters because it is designed to be highly flexible and customizable to meet a wide range of use cases and workloads. Each parameter allows you to fine-tune different aspects of the database, such as memory management, query optimization, connection handling, and more. This flexibility helps database administrators to optimize performance based on hardware resources, workload requirements, and specific business needs.

In this blog, I will cover some of the important PostgreSQL parameters, explain their role, and provide recommended values to help you fine-tune your database for better performance and scalability. 

Click through for those parameters, including descriptions, default values, and recommendations.

Comments closed