Why old-school PostgreSQL is so hip again | Hacker News Reader

Why old-school PostgreSQL is so hip again

infoworld.com

69 pointsbjourne78 comments

Is Postgres really perceived as "Old-School?" I mean it's at least as modern and hip as MySQL these days.

At least? 9.x removed some of the last barriers to adoption, like having a simple replication strategy, and now we're already on to 10.1.

The release notes for each version on Postgres highlight dozens of important new features: https://wiki.postgresql.org/wiki/New_in_postgres_10 That team is crushing it.

Meanwhile MySQL has gone from 5.5 to 5.7 and added JSON columns and a bit of InnoDB tuning.

I consider it modern, especially the great changes brought forth in 9.x and 10.x. Multi-node databases, big data partitioning, replication are all areas which are still evolving for all sorts of data stores. Postgres certainly has been around for awhile, but to say the code is old is inaccurate. Evolved might be a better way to state it.

It's the thing everybody fled to once MySQL got Oraclized. It was always a bit more superior in terms of features and reliability but once Larry Ellison appeared with his whip people really started running.

It came out earlier than last week. Definitely "old-school" for some.

Three JavaScript frameworks and a dozen political scandals ago.

I think they mean in comparison to Mongo/Cassandra/etc., not other relational databases.

The biggest gripe I've had with Postgres (and I mention this elsewhere) is the lack of a decent GUI/IDE.

But as Datagrip matures, the landscape can only get better. Datagrip is not free, but I'm willing to pay for something that works better than pgAdmin.

There are some exciting things happening in the Postgres space. Outside of Citus for scaling out, TimescaleDB has a plugin that makes Postgres a strong contender in the time-series database space. Temporal joins have always been a problem with NoSQL time-series databases; otoh joins are what SQL databases have traditionally been optimized for. It'll be exciting to have a large-scale datastore where you can easily combine and recombine different time-series in a single query.

For OSX: Postico (https://eggerapps.at/postico/). You have to pay, and it's no Sequel Pro, but it's leagues better than all the garbage free ones.

Hmm i have been looking at this one before.

Could you share some user experiences, like how often you get updates, and the start guide has a 'Connecting to a remote PostgreSQL server via SSH' section, without content yet, does tunnelling via SSH work nice/as expected?

I can answer this, as I purchased Postico for our team just today (just out of gratitude really, as the free version is good enough)

Tunnelling via SSH works great, but you do have to provide and save your ssh key password in Postico, because it does not use ssh-agent.

Lots of great features and small updates here and there: it was updated with touch bar support quite quickly. A killer feature is Ctrl+P fuzzy search for table names in database.

It's great for getting query data out of your db quickly or just browsing around. Ok to add the odd column here and there. However managing roles and permissions seems to be out of scope.

Not the op but connecting through SSH works great in my experience and is very straight forward in the UI. I feel like updates are pretty frequent as well. I also love that it saves all my queries unlike psequel.

I tried Datagrip, and ... I cant say I am enthusiastic

First thing to note, datagrip is a sql development ide, not a sql server admin tool, so it may lack some of the administration tool you might expect, like monitoring for example

Even as an IDE, it seems lacking some basic notions, for example, there isnt really a meaningful way to have a SQL Project, you get a bunch of connection and query shells, but you dont really get a Project that encapsulate both, also there is not support for deployment

Finally, I am getting the impression that Jetbrains, it getting too stretched, if you follow their bug list, its too big ... and many IDEs do not seem to get the same attention as others

In conclusion, dont bet on Datagrip, its got a long way to go, there doesnt seem to be a good designer with interesting concepts behind it .. so it will just be at best a glorified sql mode

> I am getting the impression that Jetbrains, it getting too stretched,

And how. With the rate at which they're churning out new projects and initiatives, I have to assume that they're either severely understaffed, or seeing the average tenure of their development team dropping like a stone with all those new hires. Maybe both at the same time.

My workplace uses Aqua Data Studio (http://www.aquafold.com/aquadatastudio_features). It supports everything from Oracle, DB2 and SQL Server to PostgreSQL, MySQL, and SQLite. It even supports MongoDB. And it even has tools for importing from Excel spreadsheets directly. Pretty much if your RDBMS has a JDBC provider, it works with Aqua Data Studio.

It's not free, and it's not cheap, but it's really quite nice.

PGadmin and DBeaver are nice, just in case you didn't tried them.

+1 for DBeaver, tried most of the alternative clients already, love how you can easily copy and paste a generated INSERT statement based on a selected row, makes easy for "debug cycles".

Pgadmin went downhill, the new version is borderline unusable.

Yep. I'm still using pgadmin 3.

If you haven't checked out Postico, it is hands-down the nicest database GUI I've worked with: https://eggerapps.at/postico/

Although it's expensive, Navicat (https://www.navicat.com) provides polished experience for PostgreSQL.

We use Sql Pro for Postgres. It's been great. https://macpostgresclient.com/

Have you tried pycharm? It offers some nice things like auto-complete over SQL as well as your table schema.

datagrip and pycharm are both made by JetBrains. Datagrip is probably the exact same thing built into pycharm, but as a standalone product.

Having use intellij ultimate and datagrip, pretty much, datagrip was a DBA orientated product built around the excellent database tools in intellij.

Postgres fills a niche that has a lot going for it. It’s standard SQL with transactions so it’s easy to code against compared to Nosql like Cassandra. There are lots of powerful extensions like JSON and full text search. And you can scale it pretty far.

Postgres is likely suitable for 90% (or more) of the crap "engineers" currently fall all over themselves to implement Kafka, Spark, and whatever other bizzword laden "big data" or "machine learning" resume driven development for.

It's not a niche.

I'm still trying to find an engineer that can explain to me why they use Redis for job queuing over Postgres.

Redis for HTML caching: Cool.

Redis for queueing: Are you really sure you need the performance gain? Really? You can do 10k writes per second with Postgres (100k per second with COPY). If you need more than that fine, but for 99% of web apps out there it's just one more moving piece that probably doesn't get included in your backups.

I was looking at that choice for my latest project and the gem recommended (resque?) only using Redis for the job queuing at the benefit of much higher performance over something like delayed_jobs using postgresql in jobs. It didn't seem worth it as job queueing was not the main feature of the server. I suppose if one were doing some sort of real time feature where you had to queue things off a Rails API response thread it would make sense but wasn't doing that for now. But just judging by the number of downloads, the Redis based job queue is possibly more popular for some reason.

Proper queuing in RDBMSs can be tricky.

2ndQuadrant has a good write up of what is required to make sure you don't fall into any traps: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...

Now if we're just talking about persisting data to somewhere - postgres COPY is pretty amazing.

Spark is not a buzzword if you do analytics, it's the preferred platform, especially over native Hadoop. Honestly most developers have no idea what the heck Spark is or how to use it. NoSQL/Kafka yes, Analytics/Machine Learning is still far too complicated for most to stand up on their own.

Spark has a place: at large scale. For 100s of GB to a few TB of data PostgreSQL works very well. At least, it does for my team. I don't want Spark, Kafka, NoSQL or any other modern fad near my team's data. It's just not appropriate.

Kafka was useful in a project because of the semantics for what amounts to writing to file from network sockets.

Spark is a terribly inefficient solution to any known/stable data processing or analytics jobs. If you want a common format to trade with buddies, it's useful now. I expect something else will come along to replace that fad tech.

>Spark is a terribly inefficient solution to any known/stable data processing or analytics jobs.

Can you expand on that?

Spark is a terribly inefficient solution to any known/stable data processing or analytics jobs.

You gave a typo there.

Spark is a terribly inefficient solution to any known/stable data processing or analytics jobs I have ever come across.

There, fixed that for you.

I don't think that's fixed, because it's not what I assert.

Any job that is predictable, can be done faster and cheaper with some C (or Go) and ad-hoc delegation to load balanced VMs. Spark is difficult to optimize for consistent processes, poor on resource usage, and requires specialized knowledge. Just pay for someone who has done a little embedded programming and stop creating buzzword jobs because a prototype went to production without comparison.

A niche?

If you get deep enough into the hipster development movement (or anything) you lose sight of everything that's going on around you.

That niche being "anyone who needs to store structured data and reliably retrieve it". It's a small market, but there's demand there.

Is it possible that a rise in geospatial applications is partly a driver of Postgres’s rise in popularity? PostGIS seems to be the best option for storing geospatial data in an RDBMS (open source or otherwise).

Anecdotal, but PostGIS is the reason I started using Postgres several years ago in some of my own projects, which later came in handy at different jobs. At one place, we standardized on Postgres partly because of PostGIS.

I think Django has had some influence too, since they've always recommended Postgres.

I selected PostgreSQL for a geo project recently. Still haven't got my head around tuning, but apart from that I've found it great to use. I use psycopg2 to connect from Python. No nasty surprises in use, and found that all my pidgin SQL translated easily. Also worth knowing that ogr2ogr supports PostgreSQL and is an awesome tool.

I wish WordPress would support it. It would make it easier for me to suggest PostgreSQL for people with simple web hosting needs.

Looking back, I never understood why it didn't win the database wars in the 90's against MySQL (in the famous LAMP stack)? Was it that it was harder to administrate?

I was a part-time web developer at the time. I remember that PostgreSQL didn't run on Windows. Most hosting providers also only supported MySQL. Back then I used [Xampp](https://www.apachefriends.org/index.html) to do all my development on Windows.

Having been involved in enterprise related technology at the time, I can tell you that there were a number of factors, some of which I think may have been alluded to here.

* MySQL was looked at as cost effective and fast data store for developing less than mission critical things like backing web front ends; and back then much of the web work was really less than mission critical for a lot of organizations; this meant that so long as it was fast and didn't burden these new "web developer" types with too much ceremony, it was a compelling choice. Postgres of that era even then boasted a much better and more "correct" set of features that would appeal to enterprises, but wasn't all that fast and did bring that extra effort that most of these reckless developers on the fringe didn't want.

* For enterprise work, Oracle, DB2, or to a lesser extent MSSQL where the choices. Open source was distrusted (what do you mean "free", what's the catch? Must not be worth anything if you can't charge for it. Nobody got fired for buying IBM, MS, etc). So the people that would most likely look at the trade offs in Postgres of that time favorably were the ones least likely to entertain it. The innovation was on the web side of the world, and there I go back to my first point.

* PHP was also a big deal back then. Our company was convinced by one of our sysadmins to let him greenfield a help desk application for our internal support (~1997/98). PHP was the obvious choice because it was so easy and fast to write code that worked and was web focused... and the default database choice was MySQL. (We went way out on a limb and installed it on this new OS called Linux, Slackware if I recall correctly). PHP and its good support of MySQL as data store made it the default choice. Postgres wasn't even on the radar or a discussion point.

* MySQL was developed by a consulting company with an interest in promoting it. Postgres didn't have that kind of directly interested marketing support. Sure you could get the code and install it, but there really was no marketing as compared to MySQL.

So, easy to use, relatively friction-less technologies; targeted at really and an new technology industry that was just starting to take off; and that the concerns of that new industry didn't match up well with the strengths and weaknesses of the Postgres you could get back then, but did match up well with MySQL.... yep, I think all of those are reasons why things turned out as they did.

BTW... I say this as someone that was never terribly fond of MySQL and still not. Back then I was an Oracle guy, since about Postgres v8.0, I've been favoring Postgres.

From my perspective it was due to PHP being really popular back then, and MySQL was a first class citizen there.

Then Python showed up, which reversed it (Postgres is defacto standard database for it).

Acquisition of MySQL by Oracle sped up this change further.

I ran a hosting company in the nineties. My experience: in those days everybody needed speed. Hardware was underpowered and number of hits unexpected. Connection pooling was either unknown, or very new, in any case, not enabled by default. So a hit required a new connection to be started. This was way easier with MySQL, which was just a socket connection, where PostgreSQL started an entire new process for each connection. Way to slow. Because of this people even took for granted that you needed to check MySQL databases for errors every night.

MySQL had faster reads and better clustering out the box.

It did a lot of questionable things to get that (mostly MyISAM, innodb is better) and it was a lot more tolerant of shoddy data modelling (again questionable).

As a developer from that time, Postgres was more fiddly and unnecessarily complex. It was probably always an objectively better DBMS (except at replication) but MySQL was simpler and more predictable. And subsequently the entire ecosystem fit together better with MySQL (the LAMP stack as you mentioned).

Postgres focused on being accurate. MySQL focused on being fast. It turned out people cared a lot more about being fast than about being accurate.

Different target audience. MySQL originally targeted dot-com start-ups while PostgreSQL focused on being a reliable longer-term platform for established companies or niches. It's not going to hurt a web start up if they lose 1 out of every 5,000 customer transactions (posts), but that ratio would squash a financial application.

Based on the other replies about MySQL being simpler and faster, it makes me wonder again why SQLite didn't win.

SQLite was late to the party. The first code for SQLite was laid down in 2000, years after MySQL was already well-established in the LAMP stack.

Also, SQLite is embedded, not client/server. That means that the application needs to run on the same machine that holds the data. And because there is no server to coordinate access, concurrency is necessarily limited.

On the other hand, SQLite arrived just in time to get picked by smart phones, and is consequently the most-deployed database software in the world. There are far more instances of SQLite running today than there are MySQL instances.

It doesn't allow multiple concurrent writers easily - you have to handle the "SQLITE_BUSY" errors yourself and cope with any changes that might have happened in the interim (although transactions can obviously help.)

https://www.sqlite.org/lockingv3.html

Manual vacuum used to be a pain on Postgres.

IIRC setting up and doing basic admin (creating users, etc.) was harder for PG than MySQL.

Not everyone jumped on the NoSQL bandwagon. Software development is a horrible gaping maw of fad-driven, ill-thought-out crap.

Yeah, what a disappointment.

In the early days of NoSql, a lot of dev were still shoehorning graphs, objects, or giant look up tables into SQL database, and yes, it turns out that there are some wonderful databases to store and query this sort of data that don't rely on a relational model.

And then... yep, a bunch of magpies decided that SQL shouldn't ever be used. It's important to point out that plenty of people who pioneered and promoted graph databases, object databases, and so forth didn't go down this rabbit hole. When the data was relational, plenty of these folks were all for relational databases.

But I don't think you can claim the "NoSQL => never use SQL => SQL is obsolete" crowed was just a few outliers. This opinion infected a lot of software development organizations, and did plenty of harm.

I love SQL, but let's not get too enthusiastic about sending the pendulum swinging back the other direction, even if tempting to watch it knock a few of the overly zealous NoSql types off their perches. Trust me, I still feel a flash of anger inside when I think of some of the arguments I had to go through when I advocated for relational SQL databases. But I don't want to be that guy myself - plenty of non-sql data bases are really well conceived and designed, and are ideal for the problems they address.

I would lay some of this at the feet of the fad for object-relational mapping, too.

Trying to deal with object/relational impedance by sticking an object-oriented API in front of the database, and then using that to shoehorn OO ways of organizing and accessing data into an RDBMS, is a great way to place an upper bound on what kind of performance you can get out of the database. It also makes (what should be) routine schema migrations much more painful than they need to be. I think that that can explain a lot of the perceived success people were seeing with many document stores.

That's a really good point. I have, certainly in Rails projects[1], seen the strangest databases. They are SQL databases that are useless outside the rails application. You actually can't query them in a relational sense, you have to go through the app and its methods of querying, transforming, and merging data, before you understand what it means.

In short, the developers more or less used the ORM to serialize and object and write it out to disk. The persistent data basically makes no sense on its own, it has to repopulate the objects before it can be accessed in any meaningful way. There's little doubt that the devs behind these project had very little understanding of what an RDMBS actually is.

And if you don't, then I suppose NoSql makes sense, because your data is never relational - not because it the relational model wouldn't be helpful, but because the developer can't see the relational model in the data stored, and is incapable of structuring it this way.

I remember this idea (paraphrasing)[2] - the data will outlast the application, and the application will most likely outlast the developer.

To me, this led to a design principle - the database should always be useful outside the context of the application. If you want information about the data you're storing, the DB should be a very, very useful place to get it. I'm not ruling out the value of the app! There are lots of ops you'd want to do as a combination of code and data, lots of UI, plenty of things. But if your data is useless and nonsensical unless your app transforms it, that is probably a sign of a big mistake[3]

[1] I love rails, and I like ORMs. They save me so much typing compared to the old days of JDBC. It turns out it's not all that hard to create a well organized relational database through generators, and if you prefer to design the DB separately, it's not hard to map models to db tables. It really only gets out of control when people are essentially designing an RDBMS through generators and an ORM, but have no idea what idea what it all means on the backend.

[2]https://blog.jooq.org/2014/01/02/why-your-data-will-outlast-...

[3] probably

> That's a really good point. I have, certainly in Rails projects, seen the strangest databases. They are SQL databases that are useless outside the rails application. You actually can't query them in a relational sense, you have to go through the app and its methods of querying, transforming, and merging data, before you understand what it means.

When I converted our application to Rails, I had a database that was pretty home-grown, as we were using a language/framework where you tended to write raw SQL. While I was able to tweak the model files to work with our schema, in most places I found it too challenging to construct ActiveRecord queries, and ended up writing a lot of raw SQL (or rather, copying it from previous iteration). Initially I thought I was doing it wrong because it wasn't Railsie enough, but realized that our database is our asset, and Rails is secondary. We do a lot of ad-hoc querying and report writing, so I definitely think database-first is the way to go in our use case.

I’m not sure why object oriented databases were a thing other than people liked to write OOP. The fact there is such a huge impedance between the structured data and management code seems to be a huge demonstration of cognitive dissonance in favour of OOP.

Part of it is that DBA's got a bad reputation for bottlenecking projects for many different reasons. Some of it legitimate, as they wanted to make sure schema design was consistent, properly normalized, and followed org standards; but some DBA's were control-hungry.

Therefore, developers tried to minimize their need to go through a DBA by doing more data-ish things inside the application itself. That's more of a staff management problem than a technology problem, but OOP had the "fad cred" of the time and often bowled over DBA's. The pendulum has started to swing the other way as reinventing the database & querying in applications has proved messy.

Indeed. I'm redesigning a database in Postgresql, and I'm taking the approach of building the best schema, and I'll tweak the ORM (ActiveRecord in Rails in our case)to work with that, as opposed to the other way around. (Currently we're probably 80% raw SQL)

RE: "I love SQL": SQL as a language sucks in many ways, but so far there is no relational language that is ready to give it serious competition. The NoSQL movement was not really about the language, but about niches where processing or location scaling was more important than standard or even good query languages.

The query language and hardware scaling are mostly two separate issues, and the NoSql movement unfortunately confused the two. It's mostly that SQL-based databases were slower to scale up, not that SQL is a horrible query language.

As far as query languages, I've been in long discusses and debates about alternatives to SQL. I was partial to a draft language called "SMEQL", but everyone has different opinions on what a query language should look like and emphasize: it's hard to make everyone happy.

Not really a fan of SMEQL, but I do wonder why we don't have more specialist languages that can transpile to sql. It might be the case that that a better sql only offers minor improvements and work needs to be done elsewhere, like in the hydration back into non-2d data, the current solutions I've seen require multiple repetitive sql statements and we might be better off making the hydrators smarter.

Psql jsonb types is NoSQL enought for me. I now use it to great effect, instead of an EAV.

On a side note, I was pretty excited that graphcool got open sourced, but their json limitation to 64kb is a dealbreaker.

Increasingly I'm seeing NoSQL referred to as NOSQL - not "No SQL" but "Not Only SQL"

I discussed this on HN a long time ago with one of the original creators/proponents of NoSql, and I believe this is what was intended. Sounds like a term that really got away from them.

> Sounds like a term that really got away from them.

I think there's a class of developer that is very against "old" ways of doing things and has driven a lot of that narrative.

> Software development is a horrible gaping maw of fad-driven, ill-thought-out crap.

Words such as these deserve a poster to live on.

If you want this on a poster I could easily have it printed up and sent for $35.

T-Shirt

I'm so glad I started reading Michael Stonebreakers opinions on nosql before I dove into it. This is why scidb and voltdb are very intriguing to me.

Do you have any recommendations on what to read? I'm curious about both of these DB's. Thanks!

Sure, just a few blog posts and some other things really, but here you go.

https://cacm.acm.org/blogs/blog-cacm/50678-the-nosql-discuss...

http://www.labouseur.com/courses/db/Stonebraker-SQL-vs-NoSQL...

http://www.redbook.io/

https://www.barrons.com/articles/michael-stonebraker-describ...

https://blog.jooq.org/2013/08/24/mit-prof-michael-stonebrake...

http://wp.sigmod.org/?p=1629

https://www.voltdb.com/blog/

http://www.paradigm4.com/category/blog/

Oh wow! Thank you!

Reply on news.ycombinator.com