SQL Server Features I’d Like To See, PostgreSQL Edition

来源:互联网 时间:1970-01-01


That’s right. PostgreSQL is basically free. The MIT license is like the Church of England of licenses. They do not care what you do.

But I care what they do! If you read the first part of this article, you saw some Oracle features that I wish SQL Server had. Over here, I’ll be talking about some cool stuff PG does that SQL doesn’t do. At least not ‘natively’. There are workarounds, but we’re still getting some circles run around us.

So here goes!

Unlogged Tables! Forget wrestling with minimal logging, which sometimes just doesn’t work, no matter which Trace Flags and hints you throw at it. You can just tell the engine you don’t care about this table and it won’t log any transactions for it. Yeah, put that in your ETL and smoke it.

Generate_series! This is one of those things that I used, and then spent hours playing with. You know all that crazy stuff we do with Tally Tables and Date tables? Yeah, PG users just use generate_series, and it spits out the range of values they want. It works with numbers and dates, and even handles intervals. It’s pretty wonderful. Sick burn.

Arrays! Between array_aggand UNNEST, PG offers simpleways to handle a couple things that SQL Server users spend a lot of time trying to hack together T-SQL and CLR methods to deal with. If you’ve ever been mortified and confused by those SELECT STUFF FOR XML PATH shenanigans we use to create lists/arrays, or read one of the bounty of articles and arguments about splitting strings in SQL Server, you’d probably buy features like this a steak dinner.

BRIN Indexes! As a guy who has dealt with a lot of large tables, and large indexes, something like this is really awesome. A Block Range Index basically stores high and low keys for each, you guessed it, Block Range within the index, and decides which blocks it needs based on those values. The upside is that indexes of this type are TINY compared to traditional indexes. It’s a lot more like scanning the statistics histogram to figure out which steps you need, and then retrieving those steps.

Multiple language stored procedures! Yep. Write a stored procedure in a language you’re comfortable with. Tell PG what the language is as the end of the stored proc, and it will use it. If you’ve ever used CLR, and struggled with .dlls and trustworthy and blah blah blah, this probably sounds like a dream.

MINDOP! Just kidding. But I do wish we had this. It’d be way more useful than, like, Service Broker, or Resource Governor, or Affinity Masking, or Priority Boost, or… I’m getting carried away. Apologies; Parallelism riles me.

Anyway, I hope you enjoyed these, and I hope that you’ll give other database systems a look. There’s some pretty cool stuff out there.