News for hackers. Stuff that matters.
Re:At 22 years old, Postgres might just be the most advanced... by EGreg on Wednesday December 05, 2018 @02:35PM (#18611672)
Can you be more specific? I always wondered why people use them
Re:At 22 years old, Postgres might just be the most advanced... by manigandham on Wednesday December 05, 2018 @02:51PM (#18611805)
SQL Server has columnstores, in-memory tables with compiled procedures (which can be combined with columnstores), graph processing, built-in machine learning extensions, AlwaysOn availability/replication groups, Stretch and Polybase to read from external sources, integrated encryption, fantastic tooling, and the best optimizer with batch/vectorized processing.

It still doesn't have proper upsert or JSON columns, so PostgreSQL wins on small usability features, but SQL Server is a serious workhorse when it comes to large scale operational and analytical systems with unmatched performance. PG barely has parallel queries or decent connection management and is a decade away from catching up to SQL server in these features.

Re:At 22 years old, Postgres might just be the most advanced... by BrentOzar on Wednesday December 05, 2018 @03:11PM (#18611981)
> It still doesn't have proper upsert or JSON columns

Upsert (MERGE) was added in SQL Server 2008 (although it's chock full of known bugs): https://www.mssqltips.com/sqlservertip/3074/use-caution-with...

JSON was added in SQL Server 2016, although indexing it is still tricky: https://docs.microsoft.com/en-us/sql/relational-databases/js...

Re:At 22 years old, Postgres might just be the most advanced... by manigandham on Wednesday December 05, 2018 @03:13PM (#18611998)
That's what I meant by "proper". It can do it, but PG does it better. It's one area that I wish they would invest time into considering the large developer impact it would have.
Re:At 22 years old, Postgres might just be the most advanced... by SigmundA on Wednesday December 05, 2018 @05:54PM (#18613570)
My understanding is they got burned by all the work put into the XML datatype and don't want to repeat it with JSON, hence the more conservative approach.
Re:At 22 years old, Postgres might just be the most advanced... by fanf2 on Wednesday December 05, 2018 @05:18PM (#18613243)
How do Stretch and Polybase compare to Foreign Data Wrappers?

Is the integrated encryption comparable to pgcrypto?

Re:At 22 years old, Postgres might just be the most advanced... by manigandham on Wednesday December 05, 2018 @05:55PM (#18613577)
FDWs are much more flexible, as they are basically an endpoint based on the SQL/MED standard that can be implemented by any module and there are dozens of options out there for connections.

Stretch and Polybase are much more limited to Azure storage and Hadoop systems with deep integration into T-SQL, and scale-out using clustering.

Re:At 22 years old, Postgres might just be the most advanced... by krylon on Wednesday December 05, 2018 @03:06PM (#18611932)
At work, we run SQL Server, because we use several third-party applications that use it. And once it's there and paid for, if we need another database, it is easier to just dump it on the server that is already there, rather than run a second RDBMS server.

I am a little torn about this - on the one hand it strikes me as laziness that all these Windows developers will just pick SQL Server because it is so convenient (good integration with Visual Studio, drivers are present by default in .Net Framework). On the other hand, in terms of performance and reliability, I cannot say a single bad word about SQL Server. Another thing I like is that one can use accounts from an Active Directory domain as database accounts, so you do not have to duplicate group structures and such, if you use elaborate database permissions.

I have come to like T-SQL, which integrates some procedural features into SQL, so SQL Server does not need a separate language for writing stored procedures, triggers, and so forth. Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature.

Postgres offers many features SQL Server lacks, e.g. the array of data types available in Postgres is amazing, or the fact that it has enums. SQL Server has some features Postgres lacks, for example the Service Broker, which is very interesting, but apparently non-trivial to use correctly.

Re:At 22 years old, Postgres might just be the most advanced... by gaius on Wednesday December 05, 2018 @04:27PM (#18612759)
Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature.

Embedded R in SQL Server as well.

Postgres is my default database choice, or SQLite if it's small, but SQL Server is a solid, high performance product and I'll gladly run it for the most demanding workloads.

Re:At 22 years old, Postgres might just be the most advanced... by kev009 on Wednesday December 05, 2018 @02:42PM (#18611722)
I'm not advocating their use and the default should be right and smallest fit for the job, which means sqlite or Postgres nearly always. But too often people get in this mindset that old commercial products are meritless or whatever.

An example for Oracle would be RAC. This is a mature shared data architecture to provide high availability and horizontal server scaling to a DB. Deploying this is a fairly standard Oracle DBA activity. Getting something similar with open source tools is a big lift and permanent support commitment and will be for a while. Meanwhile these three commercial DBs have provided continuity for decades for features like this.

Re:At 22 years old, Postgres might just be the most advanced... by devy on Wednesday December 05, 2018 @02:47PM (#18611761)
> But too often people get in this mindset that old commercial products are meritless or whatever.

There is a lot of that assumption. And then, there is the vendor lock-in licensing terms that commercial DB vendors utilize, in that database switching cost is just prohibitively high and impractical.

Re:At 22 years old, Postgres might just be the most advanced... by manigandham on Wednesday December 05, 2018 @02:55PM (#18611829)
The lock-in is not from licensing, it's from the features and performance. If you aren't using those capabilities then you didn't need that product anyway, and can use any number of migration tools to switch to a different database.
Re:At 22 years old, Postgres might just be the most advanced... by devy on Tuesday December 11, 2018 @03:39PM (#18658636)
Yes, most of factors for lock-in are from features and ecosystem and replacement cost. But in licensing negotiations, license up-sale, auxiliary products & services are definitely making the lock-in tighter - since you've just made the big investments, why switching?
Re:At 22 years old, Postgres might just be the most advanced... by TylerE on Wednesday December 05, 2018 @02:42PM (#18611726)
Scaling/replication is the usual thing. Postgres certainly has a better story here than it used to, but if you want to do serious scale replication it's very much in roll-your-own territory.

Like, imagine you have a few hundred terabytes of data you need to keep in-sync globally, between multiple datacenters, with many servers in each DC.

Re:At 22 years old, Postgres might just be the most advanced... by deepsun on Wednesday December 05, 2018 @02:53PM (#18611816)
I was under impression that only Spanner-like databases (CockroachDB and FaunaDB) are the only ones that can do cross-datacenter multi-row transactions at scale. Because they were designed exactly for this, otherwise tradeoffs taken are just too bad for the that use-case (e.g. too slow or too brittle in availability).

By cross-datacenter I mean datacenters far away from each other, in different geo regions.

Re:At 22 years old, Postgres might just be the most advanced... by madhadron on Wednesday December 05, 2018 @03:20PM (#18612111)
You can do single master, many read replicas, which works fine for many, many use cases.