Why we’re abandoning M$ SQL Server for PostgreSQL

Yes, we’re actually working on this epical migration – and our database is huge and complex, won’t be a picnic; anyway, we have reasons, and had reasoned.
Sure, there is a big difference in licensing costs – some thousand of euros per year versus nothing, but this one alone isn’t enough – at least if you have money to burn invest.
Sure, T-SQL lacks of many useful features, like the ability to use composite types to declare variables, arrays, and syntactic sugar for cursor loops, but we can overcome with a good-old-fashioned bloated code.
Yes, if we talk about triggers, we have no option to implement them from a per-row perspective, that usually is more comprehensible and mantainable, nor we have support for before-triggers, nor we can define an execution order during declaration – just afterwards by calling a system stored procedure.
And no, we can’t have multiple cascade paths, but we can implement them on our own with a bunch of instead-of triggers.
And no, no boolean type, but we can use the bit one; not quite the same since we can’t combine bits with logical operators, but it does the job. We just had to wait ’till 2008 to have the ability to store dates and times separately, spending previous years by forcing one or the other part to 0 to be sure to obtain meaningful data when SELECTing, so maybe we’ll see booleans before 2020.
We still have many limitations inside FUNCTIONs, but many can be overcomed with some tricky workaround ££T-SQL: NEWID inside a FUNCTION££.

What we can’t workaround is the impossibility of having DDL statements in concurrent isolated transactions. As you may know, you have to manually enable snapshot isolation in your SQL Server databases, or anything you can achieve is a read committed isolation level, the default for this DBMS, and deal with tons of table-locks and dead-locks; actually you’ll be able to provide a good service just if every database interaction will be almost instantaneous.
So, let’s switch the snapshot isolation on: here comes the fun.

It happens that our system relies upon materialization of hierarchical survey structures, and you just can’t execute DDL statements in isolated transactions, nor you can execute them concurrently with other isolated transactions; if you try, as a butterfly effect, every concurrent transaction, even if manipulating datas not interested by the current materialization process, will be forcibly terminated.

So now, after a month of rewriting, our procedural code in PostgreSQL has dropped in size to about a fifth of its correspondent in SQL Server, we have full snapshot-isolated interaction thanx to its awesome MVCC implementation, and we’re ready to set up the test environment on some O.S. with the X of POSIX inside and the antivirus service outside.

All’s well that ends well :-)

Post a comment


7 − = 3

You may use the following HTML:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">