BUT FRANCE HAS A FREE PONY
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_agg and UNNEST, PG offers simple ways 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 at 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.
22 Comments. Leave new
How about correct cardinality estimation across Linked Servers? Single table/schema backup?
The things missing from PG that SQL Server has is quite a list too – starting with SSMS. pgAdmin III is nowhere near SSMS, especially with SQL Prompt installed.
Both
psqland 0xDBE give you all of those features. psql is free with PostgreSQL and 0xDBE is still free while it’s in beta. Much like SQL Server, you just have to know where to look to find the good tools.I agree that 0xDBE is pretty good. I am working on my psql skills and it’s probably just a matter time til I am wishing sqlcmd had more features.
For the most part, when it comes to Linked Servers, I’d rather pull data across and query it locally. Even using the REMOTE JOIN hint is kind of hit or miss.
Being able to back up single objects would be nice, but we’d also need a way to restore single objects. If I’ve ever needed to futz with a single table ad hoc, I’d do some combination of SELECT INTO / run my mods in a transaction so I didn’t bonk anything too badly.
And yeah, PG has shortcomings, but you get a lot for your money. Just not parallelism. Yet. Maybe in 9.6?
I went to a user group with super PG nerds and contributors and I don’t think 9.6 is going to be the version for parallelism.
No multi flavors of the engine with a KILLER price associated with enterprise. A 1/2 million for licenses is enough to consider anyone to look at a fantastic free product, as in beer, with new enthusiasm.
LoL @ service broker. I think you may have just started a SQL fight with Jonathan Kehayias. That boy loves him some service broker (and after listening to him speak about it I found myself saying “Huh! Never knew it was actually useful – go figure!”). Some cool features in this list (the unlogged tables especially), but I feel like they’d come at too high of a cost to my money maker (whole facial hair from weird places thing)…
These Oracle and PostgreSQL feature lists are pretty cool, but we want results from Microsoft — not just a lot of feature-envy whining about what the other guys have that we don’t in SQL Server. I think we should send these lists not to technical people but marketing! Yes, the same marketing people that help give us changes we don’t need, standards that are quickly abandoned, and buzzword-heavy jargon that confuses us, all in the name of pushing licenses out the door. You think developers have feature envy? I’ll bet those ultracompetitive marketing people will stay up late figuring-out how to justify allocating resources to provide features that the competition already has. We could start with Jeff Marcoux, who seems to have a minimally active Microsoft marketing blog here: https://www.microsoft.com/enterprise/marketing-at-microsoft-blog/default.aspx#fbid=QCMW3RS_FOj
I just have to chime in – I didn’t see the wonderful things you all talked about in PostreSQL. What I saw was No Stored Procedures (use functions instead), a clucky front end which I had to relearn every time I found time to check out PostgreSQL (which was a few hours on Fridays each week), and really simple documentation. I was asked to set up a test environment to see if we could get around the huge license costs that Microsoft is now charging and use PostgreSQL for our sophisticated Application….”after all Google uses it!” We would have to rewrite our whole app to use functions and not stored procedures. I struggled to get a lot of the features we have in SQL Server to work in PostgreSQL. I finally gave up! The Syntax was driving me crazy to try to learn! Maybe I’m too set in my ways. 🙁
After a few months of working with PG I feel like the lack of ad-hoc procedural code is probably the biggest problem in transitioning an existing SQL Server application to PG.
Are there connect items associated with these? I’d put a couple up-votes on there for you.
Yes, there’s been a Connect item open (and, miraculously still open) for the last 8 years and MS still hasn’t come up with such a simple feature as a number or date generator.
And I’d love the idea of a “no logged” table definition for staging tables. They kind of tried to do that with the in-memory tables to some extent but I don’t like it. It would be so much easier to just do a CREATE TABLE tablename WITH NOLOG, especially for Temp DB.
And yeah… at least a decent built in splitter would be nice with or without arrays. It would also be nice if you could tell it to return vertically (like most homegrown splitters do in SQL Server) or as an array (to make it so you don’t have to re-pivot the data into rows if that’s what you need).
Multiple Language Sprocs sounds like a dream come true (as did CLR) but I’d be really concerned about the “Tower of Babel” that would create. It would be much better if T-SQL were written simply to handle more things easier.
I think that with support for arrays, string splitters, and RegEx in T-SQL, you’d cut out a lot of the reason for multi-language procs, and even just CLR.
Being able to split a string on any whitespace character would have saved me about a billion years on more than one occasion. At least DelimitedSplit8k made multiple passes over the data a little less traumatizing 🙂
Agree 100%. Hive has many features missing when compared to SQL Server, but it does contain all 3 of these (arrays, split function, regexp). I am still surprised how much I can accomplish with them, especially arrays.
Regarding regex, I seemed to recall Ken Henderson having come across a blog of his on this (as well as tons of other amazing topics, that man was incredibly bright =( ). http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx – I don’t really see the fact that it’s in a function as too limiting either, since you’re going to hose your performance however you slice it.
Ugh. Ignore nonsensical grammar, evidently it is too early for my head and fingers to be in sync with each other – should be “I seemed to recall having come across a blog of Ken Henderson’s on this”. Man – I suck.
How about Table-Valued Parameters? They can be very useful for transferring larger sets of structured data to the database. Interesting optimizations become possible.
https://msdn.microsoft.com/en-us/library/bb510489.aspx
So… you’d like Postgres to have these?
I want T-SQL compatible syntax in Postgresql.
If will make it easier to migrate to Postgresql, and gain more traction.
I’m sure the SQL Server developer team will get right on making it easier to migrate to Postgres 🙂
https://www.bigsql.org/docs/pgtsql/
[…] Postgres Edition […]