Comment by ulrikrasmussen
3 months ago
It is closer to Datalog I think, or can you express cut? CTEs are fairly restricted compared to logic programmming languages though, at least for Postgres. In particular, relations cannot be mutually recursive and your rules may only be linearly recursive in themselves (i.e. can contain only one instance of themselves in the right hand side). Postgres is overly restrictive in the latter and requires at most once recursive reference over all subqueries in the UNION even though it would be safe to only restrict the number of recursive calls for each subquery (each corresponding to a separate Datalog rule for the same relation). It is possible to work around that restriction using a local WITH expression (a hack really), but then you are also on your own since it disables all checks and allows you to write rules which uses actual nonlinear recursion and will give incorrect result sets when evaluated.
I really would like Postgres to have proper support for writing Datalog queries, and with better and more efficient incremental evaluation algorithms as opposed to the iterative semi-naive algorithm that is only supported now.
Haven’t written SQL in a while (and I used to write a lot) but I think SQL Server recursive CTEs are fairly unbounded so it’s just a Postgres limitation unfortunately.
(I’m a fan of MS SQL but it’s Microsoft and also hard to financially justify for many companies. But if you ever get to use it, it is a very solid RDBMS, even if the rest of your stack is open source.)
The cost of MSSQL is largely controlled by how the system is designed and the complexity of the business.
The model I am most familiar with is a 10-20 employee B2B SaaS startup running one "big" instance on a single vm in the cloud somewhere. If this is approximately all you require, then the cost should not be a dominating factor in your decision.
I think "because Microsoft" is also really poor justification if we are being serious about the technological capabilities and pursuing high quality business outcomes. If your business is fundamentally about open source advocacy and you are operating as a non profit, I totally get it. But, this is probably not your business model.
MS SQL is one fascinating piece of software and much closer to commercial big bro offering such as Oracle and DB2, yet much more user friendly and convenient.
This sentiment against it really always comes only from people who have not used it or have never touched the enterprise version which is a very mature ecosystem with lotta features available for ages now.
Most of my career I’ve been dealing with DBs and MSSql is the easiest to admin perhaps being also tightly integrated with all scripting in the platform. It also runs Linux and is doing it better than the rest can say for running Windows.