Hacker News Reader

Comment by dr_faustus | Hacker News Reader

I find it fascinating how almost every No/NewSQL database in almost every use case niche (document, time series, key value, etc.) gets blown out of the water by Postgres either from day one or a couple of Postgres releases later (sometimes using a plugin). It just goes to show how great their technology is. Plus, its very mature, well understood, safe, secure and stable. And its much more likely to still be around (maintained) 10 years from now than the current NoSQL flavor of the month.

After we had some pretty horrendous experiences with several NoSQL dbs (mostly MongoDB, CouchDB) on client projects, we strongly urge all clients to just use Postgres. The only exceptions are redis (for caching, queues, etc.) and elasticsearch (for ... search), which are just very convenient complements to Postgres. We have never found a single instance where the postgres performance was not as good or better than the NoSQL alternative.

View on HN

I use and like Postgres, but it's crazy to say there's never a scenario where putting data somewhere else makes sense. You just named two examples, Redis and Elasticsearch.

It might be fair to say something like: if you have a general purpose OLTP workload with lots of individual reads and writes, with lots of interconnected relationships between models, where you'll need to query and index by lots of different attributes, then you'll probably see as good or better performance with Postgres vs a NoSQL alternative.

But there are other uses where you'll have a much easier time using a message queue, a streaming platform like Kafka, flat files in S3, some ingestion system to feed HDFS or Redshift, a sharded key/value store like DynamoDB, etc.

I would even go as far as to say, in my own anecdotal experience, for every case where I've seen an app struggling because they're pushing a NoSQL DB beyond its sweet spot, I've seen another app struggling due to pushing Postgres or MySQL. Things like writing all application logs to the primary Postgres DB, or using it as a message queue. To be fair, the catalyst of the problem in these cases is usually the mixed workloads in a single DB instance, and a separate instance of Postgres would work better. But still, other tools that are built for a much more specific purpose can go even further. Plus they can be easier to tune, with Postgres you'll often have to worry about configuration and things like query planner statistics, vacuuming, and XID wraparound.

I think there is a problem in that a number of current developers never went through the era where SQL (whatever DB you want) was the only choice. Specialized DBs came about - NoSQL, Graph, etc. Developers not trained in or experienced with the basics go with what they deem shiny and interesting. As experience grows, one realizes hey, maybe something like Postgres makes sense.

It's better to go with "what works" and then optimize. Sadly, too many developers don't have the breadth of experience to know the tradeoffs.

I think you're right (although it makes me feel like an old man shaking his head while talking about "the kids these days") but I also think it goes far beyond just SQL v. NoSQL and into many other areas of our field in general.

Druid still crushes Postgres, so I'm happy with our decision. However, I get a lot of sad faces when eager engineers come to me and ask which "cool" database they should use to meet the needs of Project X. "Postgres, unless you can prove otherwise" is always my answer. It is a great piece of technology, but it isn't the answer to everything ... just most everything ;)

I am missing many features in InfluxDB. Some issues are open for years with no progress.

Some highlights:

1. Create measurement and insert data. You'll insert an integer. Then, you insert a float value, because that can happen. Value won't be inserted, as Influx assumes the datatype on first insert. Ended up creating a new measurement and casting all values before inserting.

2. No Log()/exp() functions. I like to store raw values, like example from ADC or barometric devices. I can't convert a raw barometric pressure to a pressure at sea level, because there is no exp() function. I don't like to store two values...

3. Downsampling is a broken. Downsampled values must be saved to a different measurement. Displaying those data in Grafana must therefore be always for 2 (or more) measurement. One for recent data, one for old one. This is a mess.

4. Deleting values. You can "SELECT * FROM bla WHERE value < 10" for example. But you can't "DELETE FROM WHERE value < 10" because delete only supports timestamps in the WHERE clause. This is especially painfull because there are no constraints to disallow obviously false values for inserting.

I'm curious what you do for Postgres to get an HA, as the last time I worked with Postgres it was a nightmare to get even a bare bones HA working. I swore I'd never touch it again until they had a real HA solution. I ask ever year or so and keep being told it still doesn't have one.

Patroni is very good and what we use in our environment since it works out of the box with our existing tools (consul, haproxy).

Use barman http://www.pgbarman.org/

Barman appears to be a solution for backups, not HA. If your master fails, you need to restore from the backup, which means downtime.

Barman can keep a hot-standby for you.

You still need a mechanism to swap over to the standby on failure. However with libpq from postgres 10 you can provide multiple servers. This isn't true HA, but for most people it fits the requirements.

From their FAQ (http://www.pgbarman.org/faq/): Does barman manage replication and high availability as well? How does it compare with repmgr, OmniPITR, walmgr and similar tools? No. barman aims to be a pure disaster recovery solution. It is responsible for the sole backup of a cluster of PostgreSQL servers. If high availability is what you are looking for, we encourage you to use repmgr. barman specifically targets the DR case only

FYI Amazon hosted Postgres (RDS) supports HA - one click during config, and you can add it afterwards in minutes.

Unrelated to your point, but I will be so very happy if/when WordPress ever supports PostgreSQL. At that point, I will be able to ditch MySQL completely; WordPress is the only reason I still have to manage MySQL servers.

This is unlikely since core WP is now only a small piece of the whole. Plugins now make up a significant part of what people want.

I totally agree, PostgreSQL has been an incredibly good experience and joy to develop with. The stuff that already comes with most distro packages is already blowing out most other RDBM systems and the stuff you can get elsewhere just solidifies it.

Though I'm also looking at some alternatives too, CockroachDB has some of my attention since they seem to have masterless HA figured out, if they could combine that with PG... (they do use the PG wire format already)

What about when you want to store documents with an unpredictable schema? That is, a collection where the field names and types could be different in every document.

Granted, I've never actually encountered that situation.

Postgres has exceptional JSON support.

The JSON support in Postgres is in my experience much more consistent than that of e.g. MongoDB, and I understand performance is better too. Postgres really is awesome :)

I think you don't know what you're talking about. Postgres is great if you don't need HA / sharding ect ...

Honestly ... rarely do you "need" sharding. What you _actually_ need are better queries, maintenance, and to end the reliance on ORMs.

Postgres has great support for non-sharded HA.

Reply on news.ycombinator.com