Press "Enter" to skip to content

Category: Internals

PostgreSQL for the Oracle DBA: Tuples and MVCC

Kellyn Gorman continues a series on PostgreSQL for Oracle DBAs:

After our first two weeks of ensuring Grant and I didn’t burn down SQLServerCentral figuring out how Steve Jones has kept the pace he has for so long, (quite an impressive feat, I think we’d both agree!) I’m back to working with my comparisons and building more knowledge in PostgreSQL.  What caught my attention this week was the simple concept of a row (or tuple) which might seem universal in relational systems, after all, data is data no matter the platform, right? But under the hood, the way databases store, manage, and control visibility of that data can differ drastically. This is especially true when comparing PostgreSQL with how Oracle manages rows.

Read on to learn more about how these systems work.

Leave a Comment

Fast-Path Search in OrioleDB

Alexander Korotkov describes a new feature coming to OrioleDB:

When you optimize the CPU time of a transactional database management system, it comes down to one question: how fast can you read a page without breaking consistency? In this post, we explore how OrioleDB avoids locks, trims memory copies, and — starting with beta12 — even bypasses both copying and tuple deforming altogether for fixed-length types during intra-page search. This means that not only are memory copies skipped, but the overhead of reconstructing tuples is also eliminated. The result: an even faster read path, with no manual tuning required.

Read on to see what’s new and how it works.

Leave a Comment

Looking at PostgreSQL Internals with pg_visibility

Ian Parker introduces us to an extension:

When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level—information that directly affects vacuum efficiency and index-only scans.

Read on to see how this extension works.

Leave a Comment

What’s New in SQL Server 2025 CTP 2.1

Brent Ozar looks under the covers:

When CTP 2.0 came out, I blogged the thousands of new feature flags, messages, and system objects in SQL Server 2025 CTP 2.0, and I haven’t had the time to dig more deeply into those yet. In that post, I discussed the new sys.dm_feature_switches table, and I mentioned that it might disappear before release. Indeed it has – last week’s new CTP 2.1 removed access to that table, meaning that was probably our one-time shot to get a glimpse into something cool and undocumented. I would have loved to have been a fly on the wall when folks at Microsoft read my blog post and realized they’d left that table unguarded and publicly visible, hahaha. I love my job.

So now CTP 2.1 is out, and it comes with a few things I think you’re going to be interested in.

Read on to see what Brent has found.

Leave a Comment

Comparing Oracle and PostgreSQL Physical Architectures

Kellyn Gorman continues a series on learning PostgreSQL for Oracle DBAs:

In the previous post, I covered some high-level areas around installation and architecture, but for this post, we’re going to go a little deeper.  For the seasoned Oracle DBA, this should feel like we’re stepping into a familiar landscape with just a few different rules. While both PostgreSQL and Oracle Database are robust, feature-rich systems, their physical architecture and internal mechanics diverge in key areas, especially around storage structures, memory architecture, and background processing.

In this post, we’ll break down these differences so Oracle DBAs can feel more comfortable with the shift when they transition between the two.

Click through to see how the two differ.

Comments closed

Optimizing SQL Server via Indirect Checkpoints

Jon Russell covers a quiet feature:

A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to disk, and finally writes those pages to disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with SQL instances that have OLTP databases, use more memory and/or involve sequential scanning of all pages, the performance of the system could be impacted.

With SQL Server 2012, indirect checkpoints were introduced. In this case, the dirty page manager manages the dirty page list and keeps track of all the dirty page modifiers of the database. By default, it runs every 60 seconds and tracks the dirty pages that need to be flushed.

Read on to learn more about why indirect checkpointing exists, the kinds of capabilities it offers, and the extent to which you might want to tweak its settings.

Comments closed

New Objects in SQL Server 2025

Brent Ozar does a bit of spelunking:

The first public preview of SQL Server 2025 dropped yesterday, and here’s what’s new under the hood as compared to today’s SQL Server 2022 CU19. Forgive me – this is quite a lengthy post, and it’s mostly a data dump. There are new stored procedures, extended stored procs, views, feature switches, new columns in existing tables, messages, and more.

I suspect that the contents of the new undocumented sys.dm_feature_switches table alone will get y’all really curious because there are 5,910 lines in there (included in this post) that suggest all kinds of wild things Microsoft is currently working on, but hasn’t announced publicly yet.

Read on for the very long list.

Comments closed

Data Recovery in SQL Server without a Backup

Rodrigo Riberio Gomes digs in:

In more than 10 years of experience, I have dealt with cases where someone has performed incorrect operations on a table, such as updating or deleting wrong rows, in a SQL Server database that does not have full backups available. There are multiple reasons for no full backup: corrupted backups, taking too much time to restore, etc.

In this post, I want to show an alternative for these cases, an ace up one’s sleeve, that you can use to recover data. This method also provides a deep understanding of the internal workings of how your SQL Server stores data. So, in addition to learning how to recover data, you will gain more insights into the internals of SQL.

Read on to see how. Rodrigo also points out some limitations or things that would need to change if you have index compression. I consider this a very neat thing you might need to know but never want to use.

Comments closed

A Dive into Oracle Memory

Kellyn Gorman remembers:

When describing Oracle features to folks that are new to the platform or coming from other database platforms, I found it’s best to keep it simple, but take on the important aspects of the technology.  I’m going to take on a few more posts on Oracle internals from the perspective of the individual new to Oracle.

To start, I’ve been having some long discussions, as well as documenting how Oracle memory works and how we monitor it when you don’t have the diagnostic and tuning pack at your disposal.  With the exclusion of these management packs, you have to be intentional in your queries to provide the data, without violating the licensing by using the %_HIST_% views.

Click through for an overview of how Oracle makes use of memory and how you can monitor this usage.

Comments closed

Understanding Multi-Version Concurrency Control in PostgreSQL

Grant Fritchey explains a mechanism:

Let me start by giving you the short version of what MVCC is, and then the rest of the article explains more details. Basically, PostgreSQL is focused on ensuring, as much as possible, that reads don’t block writes and writes don’t block reads. This is done by always, only, inserting rows (tuples). No updates to an existing row. No actual deletes or updates. Instead, it uses a logical delete mechanism, which we’ll get into. This means that data in motion doesn’t interfere with data at rest, meaning a write doesn’t interfere with a read, therefore, less contention & blocking. There’s a lot to how all that works, so let’s get into it.

Click through for the dive. The pattern for MVCC is interesting, though quite different from pretty much any other implementation of concurrency management in a relational database system.

Comments closed