News for hackers. Stuff that matters.
Re:PostgreSQL 10 Released by disconnected on Thursday October 05, 2017 @11:11AM (#15409788)
I've used both mysql and postgresql and they worked just fine for my needs.

But I have always been curious: how does postgresql (or even mysql) stack up vs proprietary databases like Oracle and Microsoft sql server?

Re:PostgreSQL 10 Released by irrational on Thursday October 05, 2017 @12:35PM (#15410500)
We've recently moved from Oracle (after using Oracle for 15 years) and we have found Postgresql's documentation to be superior to Oracle and the SQL dialect to adhere closer to the standard and to make more sense (Oracle has been around for a long time and has a lot of old and non-standard SQL built in). Overall things are simpler yet everything runs as fast or faster than it did on Oracle. Better price, better performance, better documentation, better SQL, better install process... what's not to like?
Re:PostgreSQL 10 Released by kuschku on Thursday October 05, 2017 @01:01PM (#15410695)
> yet everything runs as fast or faster than it did on Oracle.

Be careful with that statement, Oracle’s license disallows users of the database from making benchmarks or any kind of performance comparisons (another reason to move to PGSQL)

EDIT: Source that refers to the ToS: https://stackoverflow.com/a/12116865

Re:PostgreSQL 10 Released by bostik on Thursday October 05, 2017 @03:04PM (#15411738)
The beauty of course being that the mere existence of such a clause tells you more than enough about the performance characteristics.

Now, I'm not going to discount Oracle entirely. From what I've learned from some truly hardcore DBAs, there are certain workloads and scenarios where Oracle is still unbeatable.[ß] But outside those special cases, postgres is the rational, correct choice.

ß: For example, if you have insane performance requirements on a single system, I have been explained that oracle can be used with their own "fs" layer, which in turn runs directly on top of block device layer. Essentially, a bare-bones file system designed for nothing but their own DB usage.

Re:PostgreSQL 10 Released by RJIb8RBYxzAMX9u on Thursday October 05, 2017 @09:59PM (#15413916)
I'll play devil's advocate: it's easy to benchmark, but it's hard to demonstrate that the outcome is significant, accurate, or even meaningful. And for whatever reason[0], misinformation tends to be stickier than truths. If I were Oracle, I'd get tired of dispelling bad benchmarks real fast, so much so that I'd go as far as prohibiting publishing benchmarks in the ToS, just to avoid dealing with it.

For example, APFS vs. HFS+ (filesystems are a kind of hierarchical database, so it's not that big a stretch). Multiple secondary sources, citing the the same outdated benchmark from months ago, declare that APFS is slower than HFS+. Here's one from HN[1], with some back & forth arguments and further ad-hoc measurements. Yet nobody bothered running or even mentioned fio.

Or the "ZFS will eat your data if you don't use ECC memory" meme that refuse to die.

[0] Maybe it's because in order to refute "X is bad is wrong", it's hard to not state "X is bad" first, and biased / less astute audiences tend to stop paying attention after absorbing "X is bad"[2].

[1] https://news.ycombinator.com/item?id=15384774

[2] https://www.youtube.com/watch?v=yJD1Iwy5lUY&t=46s

Re:PostgreSQL 10 Released by tschwimmer on Thursday October 05, 2017 @01:33PM (#15410912)
This is absolutely 100% insane.

Imagine if CPU makers prevented you from running PassMark.

Hell, imagine if car's manufacturer warranty was voided if you took it to a dynamometer.

Why do we as a society allow these absurd rent seeking practices like these? What possible social good could come from preventing discussion about an enterprise software product?

Re:PostgreSQL 10 Released by kuschku on Thursday October 05, 2017 @02:40PM (#15411539)
> Hell, imagine if car's manufacturer warranty was voided if you took it to a dynamometer.

Ferrari has the right to force you to give your car back if you do anything unauthorized with it, including any modification, or any unapproved testing. This was why Deadmau5 had to give back his Purrari (he modified the logo and theme to a nyancat theme)

Re:PostgreSQL 10 Released by ben-schaaf on Thursday October 05, 2017 @06:17PM (#15412988)
This sounds like bullshit to me. If you own the car you have every right to modify it. It's your property.

I can see Ferrari not selling you any new cars because you did something they don't like, but I don't see how they have e the right to force you to give your car back (which you paid for) if you modify it.

Re:PostgreSQL 10 Released by omginternets on Friday October 06, 2017 @07:40AM (#15415968)
>If you own the car you have every right to modify it. It's your property.

Some (many?) Ferrari models aren't for sale. Rather, they're indefinitely leased.

Re:PostgreSQL 10 Released by kuschku on Thursday October 05, 2017 @07:36PM (#15413398)
Well, because you don’t buy the car. Ferrari owns it, you buy a license to use it.

Just like with all your online services, or your software.

Re:PostgreSQL 10 Released by dagw on Friday October 06, 2017 @07:10AM (#15415812)
Ferrari owns it, you buy a license to use it.

That sounds like sort of claim that should be backed up with some pretty significant evidence.

Re:PostgreSQL 10 Released by omginternets on Friday October 06, 2017 @07:42AM (#15415972)
One random google result: https://www.autotrader.com/car-news/these-are-the-insane-ter...

This is far from the first time they've done this, as subsequent google searches will attest. I'll leave this as an exercise to the reader.

Re:PostgreSQL 10 Released by pmontra on Friday October 06, 2017 @08:31AM (#15416262)
It's not a license. It's a lease and the car is yours at the end of the lease period.

> The lucky few selected to get an F50 would make a down payment of $240,000. Yes, that's right: a down payment of $240,000... on a car. After that, monthly payments were $5,600. Five thousand. Six hundred. Per month. For 24 months. And then, at the end of the lease, you owned the car -- assuming you could come up with the final payment of $150,000. Total payments over the 2-year span: $534,400. Only then could you resell your F50 and make money off the wild speculation.

Re:PostgreSQL 10 Released by omginternets on Friday October 06, 2017 @04:21PM (#15420098)
That's a distinction without a difference.

I also suggest the Google query.

Re:PostgreSQL 10 Released by luka-birsa on Friday October 06, 2017 @12:13AM (#15414370)
Bullshit. Purrari was sold online.
Re:PostgreSQL 10 Released by petepete on Friday October 06, 2017 @03:46AM (#15415072)
Yeah, Ferrari sent him a cease and desist notice. He replaced it with a Nyanborghini.
Re:PostgreSQL 10 Released by mattb314 on Thursday October 05, 2017 @08:16PM (#15413563)
Worth noting that (I'm 99% sure) you're absolutely allowed to benchmark Oracle, just not publish the results publicly. It's pretty common for DB papers to compare against postgres and a number of "mystery" databases because the authors' license agreement with Oracle prevents them from naming it.
Re:PostgreSQL 10 Released by efficax on Thursday October 05, 2017 @03:41PM (#15412005)
I think the purpose is to try and prevent benchmarks being posted that are on improperly configured or optimized systems and therefore misleading, not to "hide" the performance characteristics of the database. That said, Oracle sucks and I can never imagine using their software.
Re:PostgreSQL 10 Released by Drdrdrq on Thursday October 05, 2017 @04:36PM (#15412396)
I'm sure that's just a very convenient excuse.
Re:PostgreSQL 10 Released by Tostino on Thursday October 05, 2017 @04:55PM (#15412514)
It is, but it is also a very true one. I've seen way too many terrible benchmarks of MySQL and Postgres to remember them all, because of misconfiguration, not understanding best practices which apply to each, etc. I can see why they'd restrict them.
Re:PostgreSQL 10 Released by vkjv on Thursday October 05, 2017 @01:04PM (#15410714)
If they are no longer using it, would the license still apply?
Re:PostgreSQL 10 Released by 7265626F6F74 on Thursday October 05, 2017 @01:19PM (#15410815)
Even if they are an ex-user of Oracle products, they would have had to benchmark the software at a time the were using it or have someone who is using it benchmark it. Either way a benchmark would be made at some point while an Oracle product user, which taking kuschku's word for it, is against the license agreement.
Re:PostgreSQL 10 Released by mjw1007 on Thursday October 05, 2017 @03:12PM (#15411788)
Looking at the license agreement, it doesn't prohibit running benchmarks and recording their results, so if you ask me they wouldn't be breaking the agreement at the time they were bound by it.

(Of course nobody in danger of getting involved in a court case with Oracle is going to be asking me.)

Re:PostgreSQL 10 Released by kuschku on Thursday October 05, 2017 @05:29PM (#15412731)
Well, it does exactly that, doesn’t it?

> You may not

[…]

> - disclose results of any program benchmark tests without our prior consent.

Re:PostgreSQL 10 Released by mjw1007 on Thursday October 05, 2017 @06:54PM (#15413183)
The point is that performing the benchmarks and disclosing the results can happen at different times.
Re:PostgreSQL 10 Released by SomeHacker44 on Thursday October 05, 2017 @01:04PM (#15410712)
Sure sounds like they're an ex-user of Oracle and not a user. :)
Re:PostgreSQL 10 Released by cup-of-tea on Friday October 06, 2017 @07:07AM (#15415804)
Unbelievable. Unfortunately Oracle will stick around thanks to the non-technical people in large organisations that control the money and like products they can "trust".
Re:PostgreSQL 10 Released by mohaine on Thursday October 05, 2017 @02:19PM (#15411348)
The warts from a dev perspective:

1) Oracle is really lacking in modern features/usability. Features where frozen in roughly 1999 and they are pretty still the same (mostly). (You are STILL limited to 30 chars for a table name FFS). They do add new stuff from time to time but anything existing isn't modified. Works but not fun to work with.

2) MSSQL needs NOLOCK everywhere (I've seen codebases with this on EVERY query). The default locking really sucks. I'm sure a DBA can make the locking sane system wide but I've never seen this on any of the DBs I've worked with. Also, SQL Manager is a PITA IMHO. Toad it is not. Almost all DB interactions via a 1G windows only install is a "bad idea"

3) MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason.

4) Postgres. Lots of nice features and easy to work with but the optimizer will sometimes do something silly. Sometimes you have to cast your bound variable just use an index. (id=? => id=?::NUMBER just because you do a setObject in jdbc)

Re:PostgreSQL 10 Released by gnud on Thursday October 05, 2017 @02:32PM (#15411462)
For the MSSQL locking, you should probably change the transaction isolation level instead of using NOLOCK everywhere.

I think it's a horrible wart that you have to do that for every session, though. The default can't be changed.

Re:PostgreSQL 10 Released by adzm on Thursday October 05, 2017 @02:54PM (#15411662)
Change the mssql translation to run in read committed snapshot to get similar behavior to Oracle.

NOLOCK is usually a bad idea.

Re:PostgreSQL 10 Released by skissane on Thursday October 05, 2017 @03:56PM (#15412120)
> (You are STILL limited to 30 chars for a table name FFS).

In the latest release, 12.2, the limit has been increased to 128.

https://docs.oracle.com/database/122/NEWFT/new-features.htm#...

Re:PostgreSQL 10 Released by mohaine on Friday October 06, 2017 @11:54AM (#15417767)
Thanks, I didn't realized they finally improved that.

Of course I doubt I ever notice since I doubt my current employer will never upgrade that far. We have currently frozen our large oracle database as a way to force long term migration off it.

Pretty much everything is moving to Postgres on AWS with a bit of other databases thrown in for spice.

Re:PostgreSQL 10 Released by beefield on Friday October 06, 2017 @01:39AM (#15414649)
> MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason.

Looks like I am forced to use MySQL (or some of its variants ) in the near future. This thing about MySQL eating data is a statement I have read about occasionally. Is there any way to identify and beware use cases where this could happen? Would there be any more thorough documentation of this issue anywhere?

Re:PostgreSQL 10 Released by erulabs on Friday October 06, 2017 @04:00AM (#15415131)
Any modern distribution of MySQL or MariaDB should come configured to throw query errors rather than truncate data on insert.

See STRICT_ALL_TABLES / https://mariadb.com/kb/en/library/sql-mode/

Modern MySQL is extremely well suited for data that cannot be lost - as is I'm sure, Postgres.

That said, if you're pre 5.6, I _strongly_ suggest upgrading to 5.6 or all the way to MariaDB 10. The performance, safety, stability, etc have skyrocketed in recent years.

Re:PostgreSQL 10 Released by mohaine on Friday October 06, 2017 @12:05PM (#15417882)
Older versions would silently allow you to insert 75 chars in to a 50 char column. The extra was just gone. Of course without an error, nobody notices until somebody notices the missing data. This is usually an end user in production and the data is just gone.

Also watch out/avoid enums.

Example:

CREATE TABLE shirts ( ... size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));

You have to specify all the values in the alter so to add xx-small it is

('xx-small','x-small', 'small', 'medium', 'large', 'x-large')

and then later if you add xx-large and forgot about the xx-small add:

('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large')

You just silently lost all the xx-small values, they have been promoted to different values that exist. (Unless this has been fixed as well). Migration scripts are the real issue as they don't know about any custom values that may have been added out of band.

Re:PostgreSQL 10 Released by beefield on Friday October 06, 2017 @01:34PM (#15418608)
Thank you. Helpful.
Re:PostgreSQL 10 Released by reednj on Thursday October 05, 2017 @10:06PM (#15413935)
Theres a server wide option to turn off auto-truncate for MySQL, which I believe is on by default, at least on ubuntu
Re:PostgreSQL 10 Released by tatersolid on Sunday October 08, 2017 @05:07PM (#15430005)
Sane and safe defaults matter in programming. MySQL has failed at this since its inception.
Re:PostgreSQL 10 Released by wenc on Thursday October 05, 2017 @02:58PM (#15411690)
I cannot comment on every single aspect of Postgres vs MSSQL, but there are a few things I like in MSSQL that I don't believe exist in Postgres:

1) SQL Server Management Studio (SSMS) - the default GUI is a decent free front-end that integrates well and lets you do advanced stuff like graphically live-profile queries (live execution plans), easily setup security, build indices, setup various things like linked servers, compression, etc. Although I'm a text-editor sort of person, I don't have SQL syntax memorized for infrequent tasks like creating indices so a GUI (or an IDE) can really help productivity in these instances.

Postgres's default GUI, pgAdmin is comparatively weak, and the good ones are third-party payware.

2) Columnar indices - MSSQL has a fairly good implementation called columnstore indices, which creates a derived column-oriented data structure which speeds up analytic queries quite a bit.

3) Speed - SQL Server is very performant and optimized, and doesn't choke on very large datasets. Postgres is decent, but on my datasets it doesn't seem to be very performant.

Also, MSSQL locking is a boon and a bane. It's not the best for environments with high contention, but it is ok for most analytic database use cases. On the other hand, Postgres' MVCC (and oh the vacuuming) can be annoying.

Re:PostgreSQL 10 Released by emptyfile on Friday October 06, 2017 @04:31AM (#15415257)
Working with pgAdmin on a daily basis is pure torture, and the new v4 JavaScript app is just....shockingly bad.
Re:PostgreSQL 10 Released by wenc on Friday October 06, 2017 @10:31AM (#15417044)
pgAdmin is a surprisingly bad GUI for a database as good as Postgres, yet I see folks recommending it on forums and such. I don't quite understand the reasoning behind that -- I can only surmise that these folks have never used a decent SQL GUI nor experienced how a decent SQL GUI can massively increase their productivity.

I wonder if rewriting pgAdmin in Electron might help.

Others recommend using psql from the command-line. Now I spend most of my time on the command-line, and psql is great for one-off stuff, but when you have to edit, test and profile complex queries, the cmd-line interface just doesn't cut it for rapid iteration.

I think this is a huge gap in the Postgres world. But I also think that DataGrip is very promising. I have a pretty high view of Jetbrains' tools.

Re:PostgreSQL 10 Released by jeltz on Saturday October 07, 2017 @08:13PM (#15426369)
Rewriting it as a HTML+JS application only made it worse.
Re:PostgreSQL 10 Released by starik36 on Thursday October 05, 2017 @03:44PM (#15412022)
What are the good third-party payware GUIs for Postgres? I've looked and never seen any that came anywhere close to SSMS, much less SSMS + SQL Prompt combo.
Re:PostgreSQL 10 Released by wenc on Thursday October 05, 2017 @04:50PM (#15412485)
There's Navicat.

Datagrip is on its way to becoming really good, though it still has some issues.

There's a whole list here: https://postgresapp.com/documentation/gui-tools.html

(I use the free Apex Refactor with SSMS and it makes editing SQL a pleasure)

Re:PostgreSQL 10 Released by sitepodmatt on Thursday October 05, 2017 @08:02PM (#15413504)
Navicat is poor. Their buggy native linux app is horrible slow .net 2.0 app with wine loaded included, we felt scammed on that but managed to get our money back.
Re:PostgreSQL 10 Released by wenc on Friday October 06, 2017 @10:26AM (#15417008)
I've read some good reviews about the Windows version of Navicat, but I can see the Linux port being bad if it's WINE based.
Re:PostgreSQL 10 Released by insulanian on Thursday October 05, 2017 @04:58PM (#15412531)
JetBrains DataGrip is the best I found.
Re:PostgreSQL 10 Released by mappu on Thursday October 05, 2017 @03:53PM (#15412095)
I use HeidiSQL for MySQL, it seems to support Postgres.
Re:PostgreSQL 10 Released by anonetal on Thursday October 05, 2017 @11:49AM (#15410114)
Aside from tooling, those systems often perform much better than PostgreSQL for large queries or transactions, as they feature much better optimizations. Even outside of newer optimizations like "columnar" storage, several of those systems do code generation from queries to avoid function calls, branches, etc., which can have huge performance implications. I worked on the internals of PostgreSQL once, and the number of function calls in the innermost loops were very high.

PostgreSQL also used to be (is?) single-threaded, which limited performance of a single query on multi-core machines -- I haven't looked into it to see if there has been any fundamental change in the architecture in the last 4-5 years.

Re:PostgreSQL 10 Released by grzm on Thursday October 05, 2017 @11:55AM (#15410173)
> PostgreSQL also used to be (is?) single-threaded, which limited performance of a single query on multi-core machines

From the submission:

"Improved Query Parallelism - Quickly conquer your analysis"

Query parallelism was introduced in 9.6 and expanded in 10.

Re:PostgreSQL 10 Released by anonetal on Thursday October 05, 2017 @12:22PM (#15410389)
Yes, I was just reading through that. The server is still single-threaded though -- they are getting the parallelism by starting multiple processes to do independent chunks of work. This makes sense for PostgreSQL, but has some fundamental limitations (e.g., it requires duplicated copies of a hash table to parallelize a hash join).
Re:PostgreSQL 10 Released by endorphone on Thursday October 05, 2017 @01:48PM (#15411024)
>The server is still single-threaded though -- they are getting the parallelism by starting multiple processes to do independent chunks of work.

So...it isn't single threaded then? I mean that is exactly how the most advanced competitors operate (Oracle, SQL Server) as well -- a given connection stays on one thread, with the advantages that confers, unless the planner decides to parallelize.

Re:PostgreSQL 10 Released by adzm on Thursday October 05, 2017 @02:59PM (#15411707)
To be technical, MSSQL uses its own bespoke scheduling, and will preempt the thread for io. All io is nonblocking. The physical thread can vary for this reason. PGSQL really does use synchronous io and a single thread though. The former is probably more scalable but the latter has been serving PGSQL fine, too.
Re:PostgreSQL 10 Released by tychver on Friday October 06, 2017 @04:21AM (#15415208)
I think bitmap heap scans have had concurrent IO for quite a while now? There's the effective_io_concurrency setting for it.
Re:PostgreSQL 10 Released by jeffdavis on Thursday October 05, 2017 @06:03PM (#15412908)
No, processes don't create fundamental limitations. They can still share memory, it's just an "opt-in" choice.

Postgres processes share memory for all kinds of things. Hash tables may be duplicated, but not due to any fundamental limitations.

Re:PostgreSQL 10 Released by halayli on Thursday October 05, 2017 @01:05PM (#15410717)
PostgreSQL uses shared memory, it doesn't copy the hash table.
Re:PostgreSQL 10 Released by anarazel on Thursday October 05, 2017 @01:52PM (#15411058)
In the specific case of hashjoins, it does build them independently right now. There's a patch to rectify that though, by putting the hashtable also into shared memory. The coordination necessary to make multi phase batch joins and other such funny bits work, unfortunately made it infeasible to get into 10.
Re:PostgreSQL 10 Released by halayli on Thursday October 05, 2017 @04:42PM (#15412436)
I stand corrected, it definitely reconstructs the hash table in each process.
Re:PostgreSQL 10 Released by anarazel on Thursday October 05, 2017 @05:16PM (#15412654)
FWIW, here's the patchset to fix that: https://commitfest.postgresql.org/15/871/
Re:PostgreSQL 10 Released by halayli on Thursday October 05, 2017 @07:35PM (#15413390)
Thanks! I remember reading this thread a while back and I thought it made it in.
Re:PostgreSQL 10 Released by Tostino on Thursday October 05, 2017 @11:50AM (#15410126)
Yes there has been. This release expanded it significantly.
Re:PostgreSQL 10 Released by brightball on Thursday October 05, 2017 @02:07PM (#15411233)
The biggest issue with SQL Server is that it is myopic. The tooling and everything around it is geared toward only SQL Server. The database itself is also geared around only SQL Server...making it a huge pain to get your data out to use it with something else like Elastic Search. It's geared towards being comfortable enough to lock you in and hold your data hostage.
Re:PostgreSQL 10 Released by manigandham on Thursday October 05, 2017 @11:25AM (#15409923)
For 99% of standard SQL, they all work the same today.

The commercial databases are still faster since they have more advanced algorithms and optimizations, as well as better scale out options and tooling - but Postgres is quickly catching up and will be fine for the majority of scenarios. Postgres also has better general usability with robust JSON support, CSV handling, foreign data(base) access, lots of extensions and other features that help make it a powerful data platform.

Today the real difference will be for companies that have some combination of existing Oracle/Microsoft tools and services, advanced clustering needs, complex security requirements, or a dependency on the more advanced features like MSSQL's in-memory OLTP.

Re:PostgreSQL 10 Released by zitterbewegung on Thursday October 05, 2017 @11:25AM (#15409928)
The tools to access Microsoft SQL server are really good since they have Visual Studio based tool. MySQL workbench is almost at that quality but I'm not sure what a Postgres alternative would be.
Re:PostgreSQL 10 Released by benjaminjackman on Thursday October 05, 2017 @11:30AM (#15409969)
IntelliJ Ultimate has pretty good built in tools as well that handle a lot of different dialects. I've used MSSQL and PG with pretty good results, e.g. code complete that uses the db schemas is really nice to have when exploring databases.
Re:PostgreSQL 10 Released by teilo on Thursday October 05, 2017 @11:37AM (#15410013)
Look at JetBrains DataGrip - the standalone tool for DBs. IntelliJ's tools are a derivative of that.
Re:PostgreSQL 10 Released by noir_lord on Thursday October 05, 2017 @12:23PM (#15410394)
Aye, Ultimate is a no brainer if you are a polyglot, you get nearly all the singular ide's in one.
Re:PostgreSQL 10 Released by the_rosentotter on Thursday October 05, 2017 @11:48AM (#15410104)
For a web interface, especially for data browsing/editing, TeamPostgreSQL[1] is the best I have used for any database.

[1] http://www.teampostgresql.com/

Re:PostgreSQL 10 Released by manigandham on Thursday October 05, 2017 @11:27AM (#15409943)
DBeaver is highly recommended: https://dbeaver.jkiss.org
Re:PostgreSQL 10 Released by LeonM on Thursday October 05, 2017 @11:31AM (#15409974)
Better link to the official website: https://dbeaver.com/
Re:PostgreSQL 10 Released by manigandham on Thursday October 05, 2017 @11:34AM (#15409995)
That's the enterprise/commercial version which recently has a cost involved. Worth it for the nosql access.
Re:PostgreSQL 10 Released by bdcravens on Thursday October 05, 2017 @02:52PM (#15411649)
I've been using DataGrip for both SQL Server and Postgresql for over a year now, and am very happy.
Re:PostgreSQL 10 Released by ianamartin on Thursday October 05, 2017 @08:16PM (#15413567)
I cut my teeth on Oracle when I was first getting started in technology. All I did was write ad hoc queries all day long. My next DB heavy job was using SQL Server, where I built an analytics engine to do bootstrapping for sparse datasets. After that I used it to run the back-end data layer for a credit card processing company. I used MySQL at a different finance company that was doing similar things but at a smaller scale. Ever since then, I’ve been using Postgres.

Based on that experience, I’d rank them in this order:

1. Postgres 2. SQL Server 3. Oracle 99. MySQL

Postgres often lags behind the others in features, but the dev team chooses their battles wisely and ends up with better implementations.

Postgres is a real beacon of light in the open source world. Solid community. Many projects claim the benefits of open source, but they are never fully realized. Also, because Postgres is not operated by a freemium model, you always have access to the latest and greatest features. The extensibility is fantastic and well-leveraged by the community. I’ve never experienced a case where Postgres tried to figure out what I was doing and decided to do the wrong thing. Postgres fails early and loudly when there’s a problem with what I’m asking it to do, which is exactly what I want it to do. I don’t ever want to have to second guess the integrity of my data.

I haven’t run explicit benchmarks between any of these databases. But when I do similar things across two different systems, I feel like they are generally on par. But like I said, I can’t prove that with any numbers. There are probably specific work profiles that people can come up with that would show better performance for one platform over the other. But I don’t think there’s a realistic difference in performance in general. Not one that’s big enough to push your decision.

The real moment of revelation though, is when you find out that you can run your preferred programming language inside of Postgres. When you actually get to the point that transformations are outside of what you want to do in SQL, and you can just write a Python function and have it execute inside your database instead of having to do I/O, process the data, and then push it back . . . it is life-changing.

The only reason SQL Server isn’t tied for first place is because of the lack of extensibility and because it’s expensive to use in production. But it is rock solid, and has some nice things that Postgres doesn’t have, like hinting queries. Again, the Postgres community has discussed this, and it may never actually happen, but there are reasonable points as to why not. But it is really handy in SQL Server to be able to guide the query planner on the fly like that. SQL Server has also had solid pub-sub for a long time, though we’re getting that now with this version of Postgres.

I’m not a huge fan of Microsoft in general, but you absolutely have to give them props for their tooling. There is nothing even close to SSMS for any other database system. It is by far the gold standard for a visual interface to you data.

Obviously, if you’re throwing down money, you’re also getting a certain level of support for the product. I’m not convinced this should be a deciding factor between Postgresand SQL Server because, again, the Postgres community is amazing.

I should also point out that there’s a free version of SQL Server that will suffice for the needs of a great many people. It’s features are limited (no pub/sub, and there’s a size limit on your total dataset), but it’s totally functional for a lot of use cases. Even though I use Postgres for everything in production, I will always keep a PC around to run SQL Server for one off things that are just easier to do there.

Oracle is mostly fine. I was so new to everything when I was using it that I probably can’t speak that well to its strengths and weaknesses. Other people who have used it more recently can probably do it better than me. I just can’t for the life of me understand why anyone would pay their prices when SQL Server and Postgres exist, unless it’s for the support contract. And where I’m kind of meh about Microsoft, I’m actively against Oracle and Sun Microsystems. I’m pretty sure that Larry Ellison’s personal model is, “Just go ahead and be evil.” But that’s kind of a tangent and not really all that relevant.

MySQL is a different animal. It has a different design philosophy than the others. It’s more like MongoDB in principle than the others are. It’s main goal is to be friendly to the developer. And to entice you into upgrading to the paid tier.

Which is all fine. But one consequence of that is that it tries really hard to succeed under any circumstance, even if “success” means corrupting or losing your data. So it fails rarely, late, and quietly under certain conditions.

For that reason, I don’t think of it as even being in the same category as the other three. As in, it would never be an option for me, similar to MongoDB. I want my dev tools and programming languages to be focused on the developer. And I want my data store to be focused on my data. I think that this is a fundamental and deadly flaw with MySQL.

Different use cases have different requirement though, so your mileage will vary. I’m an incredible pedant about data integrity because the work I do requires it. There are legitimate cases where it just doesn’t matter all that much.

But in terms of feature parity and performance, they are all pretty close in general terms. Each will have specific cases that they really excel at and have been optimized for.

Re:PostgreSQL 10 Released by int_19h on Friday October 06, 2017 @03:47AM (#15415074)
> The real moment of revelation though, is when you find out that you can run your preferred programming language inside of Postgres. When you actually get to the point that transformations are outside of what you want to do in SQL, and you can just write a Python function and have it execute inside your database instead of having to do I/O, process the data, and then push it back

https://blogs.technet.microsoft.com/dataplatforminsider/2017...

Re:PostgreSQL 10 Released by wehadfun on Thursday October 05, 2017 @11:46AM (#15410081)
I feel like Microsoft SQL server is the easiest to use and has the best tooling.

I would say that MySQL and Postgres are generally ok to use I slightly perfer MySQL tooling.

Oracle seems to be the most different one but once I got use to it it was ok.

Re:PostgreSQL 10 Released by slysf on Thursday October 05, 2017 @01:15PM (#15410782)
I'm sorry but I just can't take MSSQL Server seriously when it cannot export valid CSV. It does not escape commas for CSV or tabs for TSV, and has no option to. If they had their own flat file export that could be re-imported I could forgive it because I could write my own library for that format, but there simply is no way to cleanly export data from MSSQL. I had to write a SQL query that was ~2000 lines of 80 columns in order to export a database to CSV and properly escape each field manually, and it took FOREVER.
Re:PostgreSQL 10 Released by infogulch on Thursday October 05, 2017 @03:24PM (#15411884)
Not just exporting though. I've seen automated systems that attempt to import csv files directly through SQL Server, and it would always break at quoted fields and fields with newlines. And nobody could figure out why it broke all the time, and fields would be out of order or shifted or missing.

I wasn't able to convince people to fix it but I ended up writing a Go utility to reliably import/export csv into sql server using Bulk Insert for my own use (and sanity). And it ended up being faster than other methods to boot.

Re:PostgreSQL 10 Released by dragonwriter on Thursday October 05, 2017 @02:09PM (#15411250)
> I'm sorry but I just can't take MSSQL Server seriously when it cannot export valid CSV.

“Valid CSV” is a dubious phrase, since the closest thing CSV has to a spec is an RFC that tried to map out the space of the wide variety of inolementations then existing.

Anyhow, SQL Server is a database server; there are a wide variety of ETL tools that will export from the server to any common (or not, really) format you like, including just about any flavor of CSV/TSC you might be interested in.

Re:PostgreSQL 10 Released by slysf on Thursday October 05, 2017 @06:56PM (#15413195)
If you can't write data to a file, and then read it back in, it's not valid.
Re:PostgreSQL 10 Released by tatersolid on Sunday October 08, 2017 @05:22PM (#15430060)
There is no such thing as “Valid CSV”. It’s an ambiguous format, with dozens of variants.

SQL server supports CSV with exactly the same senantics as Excel. Which is what people expect 99.9% of the time, because most CSV data goes to or from Excel in the real world.

If you’re DB-to-DB imports and exports, use a sane file format with a sane delimiter such as the Unicode INFORMATION SEPARATOR and RECORD SEPARATOR characters which were inherited from ASCII.

Re:PostgreSQL 10 Released by hunterjrj on Thursday October 05, 2017 @01:55PM (#15411097)
Not sure if you're trolling here but... have you tried bcp?

"The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files."

https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Re:PostgreSQL 10 Released by slysf on Thursday October 05, 2017 @06:56PM (#15413192)
Thank you, yes I did try it, bcp does not escape commas or newlines in a field.
Re:PostgreSQL 10 Released by reed1 on Friday October 06, 2017 @12:37AM (#15414457)
Compare that with creating a table right from csv file (using fdw) in postgres without importing data. Very neat
Re:PostgreSQL 10 Released by greggyb on Friday October 06, 2017 @08:18AM (#15416187)
Polybase does this for SQL Server and is the recommended way to.load data into Azure SQL DW.
Re:PostgreSQL 10 Released by reed1 on Friday October 06, 2017 @01:16PM (#15418443)
Looks good, setup is quite a hassle. but still not as straightforward as postgres file fdw
Re:PostgreSQL 10 Released by dhd415 on Thursday October 05, 2017 @02:01PM (#15411160)
A missing small feature here or there is kind of a lame reason to not take a large and well-respected product seriously. That said, bcp and PowerShell (piping the output of Invoke-Sqlcmd through Export-CSV) are among the more common ways that can be done with SQL Server.
Re:PostgreSQL 10 Released by slysf on Thursday October 05, 2017 @06:51PM (#15413170)
If they can't get a fundamentally simple feature right, why would I give them the benefit of the doubt that other features are thought out? bcp does _not_ escape commas or newlines in a field, you have to write sql to replace() those characters on select.
Re:PostgreSQL 10 Released by vacri on Friday October 06, 2017 @12:51AM (#15414511)
Exporting data incorrectly isn't a missing small feature. It's data corruption.
Re:PostgreSQL 10 Released by enraged_camel on Thursday October 05, 2017 @11:52AM (#15410142)
>>I feel like Microsoft SQL server is the easiest to use and has the best tooling.

Agreed. SQL Server Management Studio is fantastic and is one of the main reasons I enjoy working with MSSQL.

Unfortunately Postgres severely lacks in the tooling department. PgAdmin 3 used to be good, but PgAdmin 4 is simply horrendous. It makes me dread interacting with Postgres.

Re:PostgreSQL 10 Released by troyk on Thursday October 05, 2017 @12:32PM (#15410486)
PostreSQL has great tooling, maybe not the GUI-UX experience you are familiar with MSSQL on Windows.

psql is great for inspecting schema and running ad-hoc queuries. SQL scripts can also be piped through it for cron-sql jobs etc.

pg_dump + psql + ssh to easily copy databases between hosts

pg_bench for benchmarking.

Lots more: https://www.postgresql.org/docs/current/static/reference-cli...

Re:PostgreSQL 10 Released by X86BSD on Thursday October 05, 2017 @01:17PM (#15410803)
Zfs snapshots and send/recv are even easier for making backups or copying databases.
Re:PostgreSQL 10 Released by majewsky on Thursday October 05, 2017 @06:28PM (#15413042)
A filesystem snapshot is only reliable if you stop the database to do it, or if there is some sort of cooperation between the database and the tool that triggers the FS snapshot creation.
Re:PostgreSQL 10 Released by jeltz on Thursday October 05, 2017 @06:42PM (#15413115)
File system and LVM snapshots are atomic, so if you cannot get a working database from that your database won't survive a power failure either. You can also do a file system backup of PostgreSQL without atomic snapshots but then you will need to inform PostgreSQL of the backup and then copy files in the right order. The pg_basebackup tool and various third party tools handle this for you.
Re:PostgreSQL 10 Released by cookiecaper on Thursday October 05, 2017 @11:59AM (#15410202)
pgAdmin 3 still exists and afaik remains compatible with newer Postgres releases. You can still use it.

Postage, a tool developed by a family of software devs, was gaining popularity but recently became unmaintained without explanation (afaik). [0]

I started using IntelliJ DataGrip on a trial basis and it's good, but I probably won't pay for it. Sick of paying monthly subscription fee for every little tool I need from JetBrains, especially when I put down a project and don't need that tool for another x months.

Used DBeaver briefly but it's so many clicks just to set up a primary key that I shelved it for now. Will probably come back to it when DataGrip trial is over.

Not a conventional management tool but pgModeler [1] is a cool project IMO. Open-source, but they put a limit on the Windows binaries they distribute to try to get people to fund development. Can build from source yourself, install on Linux, or probably find free third-party builds elsewhere.

I think that most devs are just sticking with pgadmin3.

[0] https://github.com/workflowproducts/postage

[1] https://pgmodeler.com.br/

Re:PostgreSQL 10 Released by kuschku on Thursday October 05, 2017 @12:03PM (#15410242)
You can just buy jetbrains' tools, you don't get updates, but you can always use them. (Specifically, if you subscribe even once you get the latest version, and can use it forever, even if you unsubscribe)
Re:PostgreSQL 10 Released by reitanqild on Thursday October 05, 2017 @01:23PM (#15410844)
Sad to see postage becoming unmaintained shortly after the first time I heard about it.

That said it seems there is little to gain except nice words for smaller open source efforts.

Re:PostgreSQL 10 Released by uptownJimmy on Thursday October 05, 2017 @01:40PM (#15410971)
SSMS is not fantastic, IMO.

JetBrains' DataGrip is significantly better, and works with all the extant SQL implementations.

We can definitely debate the SQL implementations themselves, but DataGrip makes tooling a non-issue.

Re:PostgreSQL 10 Released by gnud on Thursday October 05, 2017 @02:39PM (#15411528)
I'm sure DataGrip is better for writing queries in. I really doubt it's better for managing SQL Agent jobs, doing backup/restore, or for managing users.

Does DataGrip let you do Kerberos authentication?

Re:PostgreSQL 10 Released by kuschku on Thursday October 05, 2017 @03:20PM (#15411852)
> Does DataGrip let you do Kerberos authentication?

It allows you to manually specify every authentication parameter if you wish to, but the simple login page only supports password or client-cert auth.

Re:PostgreSQL 10 Released by bdcravens on Thursday October 05, 2017 @02:49PM (#15411622)
I've been using DataGrip with both SQL Server and Postgresql for over a year, and am very happy with it.
Re:PostgreSQL 10 Released by irrational on Thursday October 05, 2017 @12:38PM (#15410530)
I use DataGrip if I want to use a GUI, but the vast majority of time its just so simple to work from the command line.
Re:PostgreSQL 10 Released by matrix on Thursday October 05, 2017 @04:13PM (#15412239)
I have yet to find anything quite as nice as SSMS for Postgres, but Navicat for PostgreSQL is pretty good. It's not free, but its not expensive and has features I feel are worth paying for such as being able to diff schemas or data across DBs on different servers.
Re:PostgreSQL 10 Released by SomeHacker44 on Thursday October 05, 2017 @01:07PM (#15410737)
SSMS may well be fantastic, but as the vast majority of our dev team uses Macs, we have been converting our SS databases whenever possible to PostgreSQL over the years and have found only performance benefits, in addition to the (obvious) cost benefits.
Re:PostgreSQL 10 Released by bdcravens on Thursday October 05, 2017 @02:50PM (#15411628)
We are (much slower than I'd like lol) going through the same migration. We've been using DataGrip for some time for both. I used to run Navicat and occasionally spin up a Windows VM for SSMS, but since moving to DataGrip, found that it does everything I need for both database engines.