Press "Enter" to skip to content

Author: Kevin Feasel

Moving Away from pg_dump

Published 2022-11-07 by Kevin Feasel

Pat Wright explains why pg_dump isn’t the best solution for backup and restore:

I’m still fairly new to Postgres having only started about 5 years ago. I started with Pg9.6 and we quickly moved that environment to pg10.   When I arrived at this company,  pg_dump was the only backup we were using.  The DB at that time was still around 50GB, it was reasonable to do the backups in a timeframe that worked for us.  A dump was done every night and stored off to another server.   After some time we started to test Pg_basebackup. This allowed us to full server backup each night. It was a huge improvement as far as speed and ability to handle much larger data sizes. 

Read on for the tradeoffs around tools and various thoughts from Pat.

Comments closed

Profiler Errors against Power BI Dataset

Published 2022-11-07 by Kevin Feasel

Shabnam Watson troubleshoots an issue:

I was trying to trace a dataset I had published to Power BI service using SQL Server Profiler and I was getting this error:

Either the trace with the ID of ‘MicrosoftProfilerTrace1667261566’ does not exist in the server with the ID of ‘autopremiumhostnorthcentralus001-081’, or the user does not have permissions to access the object.

Read on for the solution.

Comments closed

The Importance of Validating Postgres Backups

Published 2022-11-07 by Kevin Feasel

Grant Fritchey brings an important insight:

I’m very much just beginning my journey of learning PostgreSQL. I’ve been documenting that learning over here at Simple-Talk (more on the way there), including backups. For this post, I’m not going to tell you about my “experience” maintaining a PostgreSQL backup routine because, well, there isn’t any. Instead, I have something else to say about backups that I learned, the hard way I might add, while working in SQL Server, that is 100% applicable to PostgreSQL.

Click through for Grant’s thoughts. The “what” makes sense. The “how” is the important part.

Comments closed

Thoughts on Postgres Backups

Published 2022-11-07 by Kevin Feasel

Ryan Booz shares some thoughts on backups in Postgres:

To be honest, I feel pretty unqualified to talk much about backups in Postgres, partially because it’s been a couple of years since I’ve had to manage the uptime of an application. As PostgreSQL has grown in popularity and usage, there is a lot of effort being put into tools like pgBackrest, pgBarman, and some newer solutions like pgcopydb (ok, I realize this isn’t billed as a “backup” tool, but…).

What I can share are a couple of things I’ve learned about restoring clusters and databases, particularly given the extensible nature of Postgres and the variety of tooling.

Read on for thoughts on a variety of topics.

Comments closed

Removing Backgrounds from Images

Published 2022-11-04 by Kevin Feasel

Brendan Tierney focuses on the subject at hand:

There are a number of methods available for preparing images for input to a variety of purposes. For example, for input to deep learning, other image processing models/applications/systems, etc. But sometimes you just need a quick tool to perform a certain task. An example of this is I regularly have to edit images to extract just a certain part of it, or to filter out all the background colors and/or objects etc. There are a a variety of tools available to help you with this kind of task. For me, I’m a Mac user, so I use the instant alpha feature available in some of the Mac products. But what if you are not a Mac user, what can you use.

I’ve recently come across a very useful Python library that takes all or most of the hard work out of doing such tasks, and has proved to be extremely useful for some demos and projects I’ve been working on. The Python library I’m using is remgb (Remove Background). It isn’t perfect, but it does a pretty good job and only in a small number of modified images, did I need to do some additional processing.

Click through to see how the tool works, as well as some cases it doesn’t quite get correct.

Comments closed

The Importance of the Power BI Service

Published 2022-11-04 by Kevin Feasel

Reza Rad explains why the Power BI Service is useful:

The Power BI toolset comes in many shapes and forms. There is a Power BI Desktop, Power BI Mobile app, Power BI Report Server, and Power BI Service (and some other applications and components too). The questions I hear from the new users of Power BI are; Do I need to have an account for Power BI? do I need to use the Power BI website for creating visualization etc.? What is the Power BI website or service, and what is its usage? If I can do the reporting using Power BI Desktop for free, then why would I need the service? In this article and video, I will answer all of that.

Click through for a video or for the article explaining the purpose behind the Power BI Service. Having done work with places using Power BI Report Server and places using the Power BI Service, I will say that the latter takes more work to get corporate-compliant but offers a whole lot more.

Comments closed

LATERAL and APPLY

Published 2022-11-04 by Kevin Feasel

Lukas Eder shows off one of my favorite operators:

The SQL:1999 standard specifies the <lateral derived table>, which is SQL’s way of allowing for a derived table (a subquery in the FROM clause) to access all the lexically preceding objects in the FROM clause. It’s a bit weird in terms of syntax, I personally think that Microsoft SQL Server has a much nicer solution for this concept via APPLY. Oracle supports both syntaxes (standard and T-SQL’s). Db2, Firebird, MySQL, PostgreSQL only have LATERAL.

Click through to see how the operator works.

Comments closed

Non-Parallel Plans from Computed Columns with Scalar Functions

Published 2022-11-04 by Kevin Feasel

Etienne Lopes tells a tale:

I must say that per principle I’m not a big fan of neither computed columns nor scalar UDFs. I mean, I find them attractive in the way they (appear to) make “things simpler” also allowing code reuse, improving queries readability, etc. Yes but they also hide or mask the complexity behind their use, which can often be quite deceiving, making it much harder to troubleshoot and solve performance problems. Furthermore they have several limitations by design that can hurt performance and all this combined, can sometimes make a “simple” query take many minutes or hours to run, instead of just a few seconds! When you see this situation happen again and again while fine tuning databases, their use becomes much less appealing.

Having this said, sometimes they can be useful of course but it’s very important to choose carefully where, how and when to use computed columns and scalar UDFs, so that performance won’t get hurt and its benefits outweigh the drawbacks.

Click through for an example of where the combo really falls short. I do like computed columns, though never with user-defined functions.

Comments closed

Incorporating Power BI with Azure Synapse Analytics

Published 2022-11-04 by Kevin Feasel

Ginger Grant counts the ways:

The first is to connect Power BI to Azure Synapse to explore and visualize data. You can examine your datasets that you have loaded in your datalake with Power BI to help with the analysis of the data either for a data science solution or to determine how you are going to transform the data. For more information on how to do this, check out my previous blog .

Click through for three additional methods.

Comments closed