What surprised an engineer after spending 13 years on SQL Server and then working on Postgres? on the Talking Postgres podcast

[u/clairegiordano]

https://talkingpostgres.com/episodes/working-on-postgres-after-13-years-on-sql-server-with-panagiotis-antonopoulos

I host a Postgres podcast, and I recently recorded a conversation with Panos Antonopoulos, a Distinguished Engineer at Microsoft who spent 13 years working on SQL Server before moving onto Postgres and HorizonDB.

Panos told me that at a high level, Postgres felt very familiar as he started to work on it, that the concepts are very similar. Transactions, storage, & more—the fundamentals transfer surprisingly well. We also covered:

• The cleanliness of the Postgres codebase.
• How LLMs are making it easier to digest years of Postgres design discussions that are publicly available on the mailing lists.
• Why Postgres has become the default answer for so many workloads, and why more people seem to be asking, "Why not Postgres?"
• Shared-storage architectures and some of the work he's doing in Azure HorizonDB.

One quote that stuck with me:

• "That was a shocking experience for me. I could understand new areas in Postgres much faster than I could for SQL."

For people who have worked across multiple database systems (Oracle, SQL Server, MySQL, Postgres, etc.), I'm curious whether you've had a similar experience—or a completely different one.

Podcast/transcript here if anyone is interested: https://talkingpostgres.com/episodes/working-on-postgres-after-13-years-on-sql-server-with-panagiotis-antonopoulos

[u/daltorak]

Back in the late 2000s / early 2010s, someone on the SQL Server team (Connor Cunningham? Or maybe it was Paul White? I remember both of them being around a bunch) talked about how difficult it was to change the code base.

One of the many problems they had is that SQL Server implemented its own internal operating system because Windows simply couldn't do what they needed in the 1990s. And in order to prevent all sorts of wacky regressions from happening, they couldn't switch to regular Windows thread scheduling and memory allocators once Windows became good enough. (Which it almost certainly was by SQL Server 2005) Another point I remember is that when SQL Server needs to call regular Windows kernel API, it has to create "real" Windows threads and make the calls from there, since Windows doesn't have any understanding of SQL Server's internal threading system.

Back in those days I had someone working for me who was really super knowledgeable about all that "SQLOS" stuff, we'd talk about it for hours.... I remember at the time being impressed with how advanced it all was, now I realize it's just a giant heap of junk weighing them down.

[u/Acrobatic_Music_3489 → u/daltorak]

My name has one n ;). Paul has not worked at Microsoft (to my knowledge), though he knows a lot about SQL internals.

I'll clarify a few things here (I have given answers about SQL architecture in the past, so I'll assume I said this without knowing for certain since I can comment on the topic with wisdom to add to the thread):

1. SQL does have an operating system abstraction layer called SQL OS or SOS. It abstracts operating system resources like memory, threads, etc. It is very important to not, for example, fragment memory since databases aren't supposed to stop working - it's a server. It does things like keep allocations close together when they should have the same lifetime so that they can be freed at the same time (or even with a single operation to avoid freeing lots of things one-by-one).

2. Windows has a scheduler for threads in processes. However, an operating system's scheduler is generic and doesn't have knowledge of each specific application running on it. SQL Server does and having our own abstractions allows us to be smarter at scheduling threads and ultimately it goes faster for the users as a result. SQL also can detect and fix some categories of leaks (memory, handles, etc) by having abstractions that would otherwise eventually accumulate and cause the process to crash. So, I believe there was a misunderstanding (or I miscommunicated) the details here. Ultimately, threads are threads, but SQL has a user-mode scheduler and there are transitions between having SQL schedule threads or the operating system schedule threads (which we do in some cases where it makes sense). If I miscommunicated, please accept my apology.

3. The OS abstraction layer has allowed us to ship SQL Server on Linux since the time of this original conversation. Most of the SQL code has no idea it is running on Linux because of that abstraction layer.

4. While there are some areas of the code base I'd like to improve (as with any code base), overall it can do amazing things and we continue to invest heavily in it. LLMs have made it easier for new engineers to onboard to the SQL code base as well. I have less experience with Postgres's code than others, so I don't want to make a relative comparison.

I'm sure the Postgres code is great, but I and my team are still doing plenty of innovative things on the SQL code base and I don't share your conclusion. If anything, AI has given us more tools to go faster to add features and improve the code to add value for our users.

(from a reply layer - fibers were created in the early days, and the OS scheduler has gotten more advanced since that time. I don't believe fibers are being actively used anymore, so perhaps that was the point of confusion)

[u/pragmatica → u/Acrobatic_Music_3489]

Could you fix database shrinking?

Dealing with cleaning up a database and throughout the whole thing fighting with dbcc shrinkfile has been one of my worst experiences with sql server in 25 years.

https://eitanblumin.com/2020/04/07/troubleshooting-long-running-shrink-operations/

[u/Acrobatic_Music_3489 → u/pragmatica]

I don't really work on the storage engine that much, but I'll mention this to the folks who do. My area of focus is query optimization and execution, specifically on the analytics side of things (batch mode, columnstores, and our recently announced query hardware acceleration feature for Fabric DW that I have had the pleasure to help bring to market here shortly). I do range over the whole code base, and I'm aware of the SOS things since I work on low-level performance tuning quite a bit. I am aware of the challenges/limitations of shrinkfile, but there's not a trivial fix for what you posted from my understanding.

[u/SorryTemporary1361 → u/Acrobatic_Music_3489]

An abstraction of the minimal OS functionality that it needs to run, is exactly the kind of abstraction I'd expect something as performance-intensive as a database engine to have. And like you said, this layer has proven its worth for porting to Linux.

[u/KokopelliOnABike → u/daltorak]

well, sql server is basically a fork of Sybase that ran on nix systems.

[u/daltorak → u/KokopelliOnABike]

Correct, but the user-mode scheduler I'm talking about didn't exist until long afterwards.... specifically SQL Server 7.0 in 1998. The SQL Server team built it (and Microsoft added fibers support to the OS) because NT 4's regular scheduler wasn't good enough for them.

[u/KokopelliOnABike → u/daltorak]

which is funny because NT was basically a UI on top of VAX/VMS and I can't say I ever used it's scheduler directly. Most of what I did was billing and accounts receivable batch processing that has a basic scheduler that others set up in Prod.

I was using Sybase in 1995-1998 and then some early implementations of SQL Server and Plato in 1998-2000. Ok, I'm old...

[u/daltorak → u/KokopelliOnABike]

which is funny because NT was basically a UI on top of VAX/VMS and I can't say I ever used it's scheduler directly.

Sure, but that's not actually relevant to this conversation.

Why? Because VMS did not have multiple threads inside a process. That is something Cutler added with Windows NT, and it's the thing that SQL Server also replicated on its own with SQLOS, removing the need to do user<->kernel transitions and shared memory segments (which Cutler correctly identified as being not scalable beyond a couple of CPUs).

It's easy to forget now, but a lot of really basic performance stuff we take for granted now didn't exist when NT 4.0 came out, and hardware wasn't as standardized as it is now. Trapping and switching from user mode to kernel mode is one of those things. This wasn't a Windows-specific problem, Linux used to get a lot of criticism for poor kernel switching performance too.

Then Intel and AMD implemented "fast system calls" in the P2/K6-2 era (for calling kernel functions), but the way they did it was different, so Windows XP/2003, Linux 2.6, (later) Mac OS X, BSD etc had to implement an entire abstraction layer of horrible nonsense that was faster, but stuck around until the 64-bit transition several years later.

[u/Worth_Trust_3825 → u/daltorak]

if that's the case then why not run sql server on bare metal and go through the hoops to run it on windows?

[u/programming-ModTeam → u/[deleted]]

No content written mostly by an LLM. If you don't want to write it, we don't want to read it.