
Why Use Postgres? - timf
http://www.craigkerstiens.com/2017/04/30/why-postgres-five-years-later/
======
ainar-g
[https://www.postgresql.org/docs/9.6/static/rangetypes.html](https://www.postgresql.org/docs/9.6/static/rangetypes.html)

[https://www.postgresql.org/docs/9.6/static/functions-
range.h...](https://www.postgresql.org/docs/9.6/static/functions-range.html)

Holy shit. Why hasn't anyone talked about it sooner? I've seen literally
dozens of tables with

    
    
        begin TIMESTAMP,
        end   TIMESTAMP,
    

and with handmade validation against intersection. And there is even a union
operation! Seriously, my mind is blown.

Rails even supports it!

[http://edgeguides.rubyonrails.org/active_record_postgresql.h...](http://edgeguides.rubyonrails.org/active_record_postgresql.html#range-
types)

~~~
stesch
Some people don't want to use SQL. Or learn it. Or read documentation.

The anti ORM movement convinced only a few interested database users.

~~~
jdc0589
Letting go of ORMs is so easy with good lightweight model binding libraries.
e.g. Dapper
([https://github.com/StackExchange/Dapper](https://github.com/StackExchange/Dapper))

~~~
cryptarch
How do model binding libraries compare to ORM's w.r.t. performance, e.g. query
optimization?

~~~
Nuzzerino
ORMs are doing more work and are naturally going to have more overhead. With
Dapper , at least with the way I used it, you're going to write your own SQL
queries so there is nothing to optimize. I've even run into some nasty SQL
server parameter sniffing issues. But despite that I still prefer the style
over ORMs.

------
unixhero
Far too brief.

I would appreciate a really long text that would in a convincing manner
explain why Postgres Is so awesome.

I work in the industry, and all I see are Oracle and Sybase everywhere. The
experts are zealots also, not even having heard of Postgres. Not willing to
believe a word I'm saying about Postgres.

I am already convinced of course, but the industry is not. Not finance, not
trading, not telecom.

~~~
gdulli
It sounds like you're not so much in "the" industry but a subset of the
industry that doesn't consider free open source software to be an option.

If you're fighting the attitude that the only viable option is one that costs
a ton of money instead of a decision based on technical merit, additional
facts about Postgres won't help.

Sometimes the decision gets made on a golf course and not after reading very
long texts.

~~~
brandall10
It's not about technical merit or cost - it's about service contracts.

I was a Sybase point person for years at a fortune 50 medical devices
enterprise company (we had revenues of well over $1B annually on devices
running Sybase dbs). There were dozens of bugs/issues I found that I pushed up
the chain to an engineer and had special patches turned around within 48
hours, sometimes even hours.

Before I left that job I started playing around with Rails and mentioned MySQL
and Postgres for a potential greenfield project. I was told it would be fine
for internal use but no way, no how were they going to deploy any software
without that kind of parachute based on economic leverage.

~~~
gdulli
> no way, no how were they going to deploy any software without that kind of
> parachute based on economic leverage.

Making software work at scale without that economic leverage could never work
in theory. It only works in practice.

~~~
watwut
Those in practice fixed bugs in mysql themselves and had to make it more
performance themselves. Which basically means larger development team and more
time. The parachute is meant to avoid that expense.

Oh, and those who did it were above oracle in terms of data size making it
rational decision too. The calculation is still different for majority of
companies.

------
jerrysievert
great read, but may I also suggest Array?

being able to have a field like:

    
    
      ingredients VARCHAR[]
    

and index like:

    
    
      USING GIN (ingredients)
    

and using an operator like @>

    
    
      SELECT * FROM table WHERE ingredients @> ARRAY['mushrooms', 'sour cream']
    

gives you such amazing flexibility and speed, it's not even funny.

also, while I was sad to not see PLV8 up there with PostGIS (an amazing
extension, btw), I was still happy to see it mentioned with such gusto.

~~~
rkv
Can you give me an example where an array would be more beneficial than having
another table with {id, name}? I personally have never found a use for them.

~~~
cygned
I could think of denormalization; e.g. you have an entitiy that can have 0..n
tags and you want to speed up the retrieval, I could think of arrays being a
faster way.

~~~
jerrysievert
Quite a bit faster, actually, depending on the dataset.

------
billions
Postgres seems to have become the go-to relational database ever since MySQL
fell in the hands of Oracle. Can anyone speak to how its json tree compares to
MongoDB's document store in practice?

~~~
brianwawok
I don't think postgres is the default over mysql.

I think WordPress and php guys default to MySQL.

I think python and Django guys end up defaulting to Postgres.

Rails and node.js I am less sure what the default is, maybe mongodb?

I don't think either camp has really changed since Oracle came along. A lot of
momentum in different stacks.

~~~
bshimmin
The actual default for Rails is SQLite [1], though a lot of people (perhaps a
majority) use PostgreSQL. You certainly can use MongoDB with Rails via
Mongoid, though I'm not sure vast numbers of people do, comparatively.

For Node, well, it varies, but at least once upon a time, people used to talk
about the "MEAN" stack (MongoDB, Express, Angular, Node) - MongoDB and Node
are certainly often used together.

[1] Not MySQL - edited as per the below correction.

~~~
werdnapk
The default for Rails is sqlite and has been for quite a while now.

~~~
bshimmin
You're quite right. It's so long since I've used Rails with anything other
than Postgres I knew that it couldn't be the default because of having to
specify the configuration flag, but it escaped me that it would be SQLite.
Thanks for the correction.

------
retox
I rented some time on a vultr server recently and chose a prepackaged build
which included a MySql install. Coming from a MS SQL background it felt
positively medieval. I haven't migrated yet but from my research Postgres
seems the closest competitor in the relational db space.

I considered MS SQL for Linux but the server alone required 3GB RAM...

~~~
mamcx
PG is far better than Mysql, something to note if you come from a traditional
but robust RDBMS/environment.

~~~
wst_
Do you have anything to back up this opinion?

~~~
mamcx
The article of this HN already cover that (plus the others that the original
post also point)...

But because this is about someone coming from a more traditional RDBMS (like
oracle, sql server, sybase) it will note that some or most of the features of
PG are not different from something like them.

Also, PG was from the start more focused in be robust, instead of MySql that
was focused in be fast (at the cost of being robust). PG is a better fit for
more traditional workloads from years now.

And the careful, well-thought, solid development, feature implementation and
release discipline is clearly very professional, to match the ones from
commercial vendors.

PG is not just good for startups and enthusiasts, but solid enough to be
recommended to most companies with total confidence.

~~~
wst_
I've got a feeling that MySQL has improved a lot. I have nothing besides my
experience as a user (not an admin) to back it up, but I haven't found
anything that would make me switch from MySQL to PG. Note that I don't look
down on PG. I've used it in the past and it is a solid piece. It's just the
feeling that both of them covers most users' needs and, apart from some corner
cases, they are more or less equal.

------
fabian2k
HyperLogLog sounds interesting, but looking at the Github page of that
extension it mentions that it has been tested with the versions 9.0, 9.1, 9.2,
9.3 and the last commit is 2014. Is it just finished and doesn't need any
updates to keep up with newer Postgres versions? Or is it more of an
abandonded project?

~~~
jeltz
Most parts of the PostgreSQL extension API are really stable so if it is a
simple extension one should not be too worried about the project being
inactive. The extensions I have written have not required any changes at all
when upgrading PostgreSQL.

------
scurvy
Is there a new way around the requirement to rebuild your entire replication
topology after upgrading versions? (say 9.4 to 9.5) You get a new master ID
when running the initdb step, and doing this throws everything else in the
topology off. TIA

------
myth17
Most important reason to use PostgreSQL : Constant Time Recovery (Recovery
from long running transaction that rollback is instantaneous)

------
mack73
From reading the documentation [0] I can't help but feel a bit underwhelmed by
the feature set of a GIST index. Maybe I'm not looking in the right place, but
what index provide near mathes (fuzzy, prefix) as well as exact term matches?

~~~
elmigranto
Look up Postgres FTS.

------
avenoir
Has anyone done a serious implementation on top of full-text search
functionality in Postgres? I have a pretty large dataset that's currently in
Postgres and I'm deciding between it and Elasticsearch.

~~~
rpedela
Depends on your needs, but ES is a superior search engine in just about every
way. PG may be good enough for your needs though. In my general view, if you
want a more powerful and easy-to-use alternative to LIKE then PG search is
great. If you need something more like Google then you should use ES.

~~~
jeltz
Not just that. PostgreSQL also offers a clear advantage if you need to access
both full text data and relational or geographical data in the same query.

On the other hand what you say is true, ES is much more flexible in what it
can offer in full text search.

~~~
rpedela
ES is very good at storing and querying non-text data including geo data
although there are exceptions such as numeric. Both PG and ES have their
strengths and weaknesses. ES is great at search. PG is great at joins and
constraints. Everything else depends a lot on the specific use case.

~~~
jeltz
Yes, both databases can handle all kinds of data and have support for a wide
range of index types, but ES does not come anywhere close to PostGIS when it
comes to geodata. PG is superior at search for geodata, while ES is superior
at text search.

------
frostymarvelous
Interestingly, I just yesterday published a post about pglogical.
[http://thedumbtechguy.blogspot.com/2017/04/demystifying-
pglo...](http://thedumbtechguy.blogspot.com/2017/04/demystifying-pglogical-
tutorial.html)

Postgres is an amazing piece of software.

------
Minikloon
No disclaimer in the article that the author works for Citus.

~~~
craigkerstiens
Apologies. Now updated for that.

~~~
asah
since you're reading HN... Thx!!

suggestion: under 'Much More' it would be useful to link to articles or docs
about each of the features listed.

~~~
craigkerstiens
Will make sure to add more links when back at my machine

------
jgord
Id love postgres even more if they had native support for writing stored procs
[ functions ] in javascript.

~~~
anarazel
I guess you know about
[https://github.com/plv8/plv8](https://github.com/plv8/plv8) , but would like
to see it builtin?

Unfortunately, having something like v8 as a postgres build dependency would
be pretty drastic increase in build requirements.

A number of distributions of postres (debian/ubuntu packages (all versions
[http://apt.postgresql.org/](http://apt.postgresql.org/)), rhel based
([https://yum.postgresql.org/](https://yum.postgresql.org/)) provide it in an
easy manner. I'm not sure there's something as convenient for OSX and windows
however :(

~~~
jgord
my bad.. just found about plv8, and will experiment with it and jsonb on
future projects.

[ pg installs I work with are invariably running on linux, so all good. ]

------
hartator
Even with the inclusion of JSONB, I think Postgres is still lagging behind
MongoDB by enforcing schema. After so many years doing web apps, I am seeing
very little interest to have to enforce 2 times the schema: one time in the DB
via migtations and one time in the app itself via ORMs. Maybe I am missing
something really obvious.

ps: I don't mind the downvoting. I am truly looking for answers.

~~~
narrator
The schema in the database can generate most of the ORM layer. If you're using
a strongly typed language and ORM like Slick/Scala or C#/LINQ the compiler
will actually prevent you from writing invalid queries or putting invalid data
into the database. This will eliminate most runtime database errors. This can
make development go really fast once you get the hang of it all.

~~~
hartator
Yeah, it's what Rails is doing. However, if you are doing an app like this,
you are building logic around the db mainly. If you are that DB centric, why
not having Postgres itself serve web requests? There is no consistency with
the double approach of having a logic rich app and a logic rich db.

~~~
kuschku
> why not having Postgres itself serve web requests?

Good question! Why not have all the logic in the database?

The answer is, you can actually do that. And it’s popular enough that someone
founded a company on that concept, although they didn’t use Postgres, but
their own self-built database.

You might have heard of it:
[https://firebase.google.com/](https://firebase.google.com/) (They were
acquired later, and are now Google’s top database offering).

~~~
koffiezet
Well, you have [https://postgrest.com/](https://postgrest.com/)

