Press "Enter" to skip to content

Author: Kevin Feasel

Materialized Views in PostgreSQL

Published 2024-11-14 by Kevin Feasel

Brent Ozar builds a view but a special one:

That query gets the top 100 users who have the most accepted answers. On my server, that takes about a minute to run – unacceptably long for a public-facing web site, for example.

Does that data need to be up to the second? Of course not. The leaderboard of the top 100 answerers isn’t going to change all that quickly. People who have successfully answered hundreds of questions aren’t going to suddenly disappear, nor is someone else suddenly going to rocket to the top.

For report queries like this, Postgres offers materialized views: a view that’s written to disk, and then updated only when you want to update it. 

Read on to see how you can create one in PostgreSQL. Brent does touch on one of the differences between indexed views in SQL Server and materialized views in PostgreSQL while covering the process of creating, querying, and updating materialized views. In discussing how to update them, Brent covers en passant a second difference between indexed views in SQL Server and materialized views in PostgreSQL. Whether the “keep it up to date at all times” approach beats the “update it when you want but let data go stale in the meantime” approach is better, that’s something worth debating.

Comments closed

Create a Case Insensitive Warehouse in Microsoft Fabric

Published 2024-11-13 by Kevin Feasel

Gilbert Quevauvilliers is speaking my language:

This is a quick blog post to show you how to use a Microsoft Fabric Notebook to quickly and easily create a Case Insensitive Warehouse.

Just a quick note when I talk about a Case Insensitive Warehouse, what that means is that the upper casing and lower casing of column names and text are ignored. By default, Warehouses and Lakehouse’s are case sensitive in Microsoft Fabric.

Case sensitivity is a trap, so I applaud Gilbert’s commitment to excellence here.

Comments closed

Moving Averages in T-SQL

Published 2024-11-13 by Kevin Feasel

Jared Westover does the math:

Even though I enjoy using SQL Server, there are some things other tools do better. For example, calculating moving averages or rolling totals is often simpler in tools like Power BI or Excel. That’s because Microsoft built those programs with that functionality in mind. Recently, we had to optimize a complex moving average query written for SQL Server 2008R2. Surprise! There’s no built-in function for moving averages in SQL Server. But don’t worry; I’ll show you how to make it work.

Read on for the solution, as well as the mess we had to work with prior to SQL Server 2012.

Comments closed

The Challenge of Importing Items into a Fabric Workspace

Published 2024-11-13 by Kevin Feasel

Marc Lelijveld performs an airing of grievances:

Obviously, you don’t want to start every solution from scratch. Therefore, it might be beneficial to kick-start your new solution by just importing components you already developed at earlier stages. Recently, I wanted to import a notebook to a Fabric workspace but was a bit confused. In this blog, I will further elaborate on the confusion and show how, in the end, I successfully imported the notebook to the workspace.

Read on for a story of pain.

Comments closed

Indexes in PostgreSQL versus Oracle

Published 2024-11-13 by Kevin Feasel

Umair Shahid continues a series on migrating from Oracle to PostgreSQL:

For database experts well-versed in Oracle, moving to PostgreSQL opens up new indexing methods that differ significantly in terms of structure, management, and optimization. While both databases leverage indexing to enhance query speed, their approaches vary, particularly in terms of available types, performance tuning, and maintenance. This guide clarifies key differences and provides practical strategies for effectively handling indexes in PostgreSQL.

Read on for the article.

Comments closed

Describing R Models with Tilde (~)

Published 2024-11-12 by Kevin Feasel

Steven Sanderson describes a relationship:

The tilde operator (~) in R is more than just a symbol – it’s a powerful tool that forms the backbone of statistical modeling and formula creation. Whether you’re performing regression analysis, creating statistical models, or working with data visualization, understanding the tilde operator is crucial for effective R programming.

Read on to see how it works and several examples along the way.

Comments closed

The Concepts of Data Control Language in SQL

Published 2024-11-12 by Kevin Feasel

Joe Celko talks about the lesser-known language in SQL (compared to DML and DDL):

But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.

The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL.

Click through to learn more.

Comments closed