
At 22 years old, Postgres might just be the most advanced database yet - max_sendfeld
https://arcentry.com/blog/postgres-might-just-be-the-most-advanced-database-ever/#
======
kev009
I'm a big fan of Postgres and it is basically the only DB I use but the title
is naive and the post is low content. SQL Server, Oracle, and DB2 are crown
jewels products and each have substantial and difficult to implement niche or
extreme scale features or other sweet spots that no open source databases come
close to after two decades, and they weren't sitting still during that time
either.

~~~
rolleiflex
One thing Postgres definitely doesn't do, and I wish it did, is embedded. This
is usually the place that people use SQLite, for example, apps that are
clients, but still need to do substantial work on the client side.

The problem is, SQLite does have a few limitations that it doesn't make
obvious: some of the more complex SQL syntax that Postgres supports are
missing, but more importantly, it can only ever do one write _or_ read to the
database predictably. (Don't believe me? Try this:
[https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894](https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894))

I know that SQLite supports concurrent writes and reads technically, but it
predictably leads to 'database is locked' issues. In practice you have to use
it in a fashion that it only ever does one thing, read, or write. No multiple
writes. No single write and multiple readers, no single write and single
reader. Just read — or write. (Edit: WAL doesn't help with this either.)

Or you're in a world of hurt. I'd love to be able to move to Postgres just to
get away from that.

~~~
coleifer
> some of the more complex SQL syntax that Postgres supports are missing, but
> more importantly, it can only ever do one write or read to the database
> predictably

I don't know...it's recently added upsert compatible with Postgres syntax as
well as support for window functions.

Additionally, sqlite has a sophisticated json extension.

> it can only ever do one write or read to the database predictably

WAL mode addresses this, allowing an arbitrary number of readers with a
writer.

For concurrent applications just use a dedicated write thread and enqueue
writes...your example is contrived. Connection management, transaction scope
management, etc, can all work in your favor. Writes occur in a few
milliseconds, meaning you only need to hold the exclusive lock very briefly in
most situations.

More information here: [http://charlesleifer.com/blog/going-fast-with-sqlite-
and-pyt...](http://charlesleifer.com/blog/going-fast-with-sqlite-and-python/)

~~~
rolleiflex
The gist I linked is extremely basic, just insert and read, really, and it
does use WAL by default. It'll still end up with database lock, and from there
it's all shallow waters — reverting from a crashed database lock is not
trivial and can cause data loss if handled improperly.

Not all writes are couple milliseconds. I regularly have transactions that
take 15< seconds, and it's not because the data size that's being input is
large, it's just that the SQL is recursive and complex enough to warrant
recursion (storing graph data).

------
blattimwind
I've recently used T-SQL / MSSQL and was surprised how starkly it differs from
your typical "foss sql" (be it postgres, sqlite or mysql).

One really obvious example is how [] are used for qualifying names, or how
there is no LIMIT clause (instead you use SELECT TOP(n), but you still use an
OFFSET n ROWS clause after the ORDER BY clause for an OFFSET; there is also
OFFSET n ROWS FETCH NEXT m ROWS ONLY). Another example are curious limits to
programmability, e.g. TEXT can't be used for procedure parameters. There also
seem to be small limits on BLOBs. No NATURAL JOIN (which I mostly use for ad-
hoc queries).

It is also very different deployment wise (as are all Microsoft products). You
don't have a client library or anything like that, but a system-wide database
driver instead. Applications use a driver interface and could (most don't)
support other database versions or even databases. You can't "just" throw a MS
SQL install on a machine, it needs to be properly installed system-wide and
register all its components or it won't work properly etc. — so spinning an
instance up for testing really isn't nearly as easy as with postgres.

~~~
panarky
I cringe every time I have to use SQL Server.

1) Weird gaps in ANSI compatibility (no concatenation with ||, no
current_date, top n instead of limit/offset, etc.)

2) Just yesterday a client's SQL Server started selecting deadlock victims to
be killed, so the DBA's workaround was to throw (nolock) hints on every table
and read dirty data. It happens when concurrent queries update records in a
different sequence, but the same application on Postgres never deadlocks.

3) The query planner seems amazingly naive for such a mature product. Hard to
understand how a query with three where-clause constraints can execute in 20
ms, but adding a fourth constraint makes it run 240000 ms, but I see this kind
of insanity every day.

~~~
gaius
_It happens when concurrent queries update records in a different sequence,
but the same application on Postgres never deadlocks._

SQL Server has like 4 different isolation levels + MVCC, you just pick the one
you need. Postgres just does MVCC so it sounds like you should just switch SQL
Server to that mode. A poor workman blames his tools. Unless that tool is
MongoDB.

~~~
panarky
_> just switch SQL Server to MVCC_

In the real world of mediocre DBAs and databases managed by application
vendors, it's never that easy, is it?

Postgres _just works_ with default settings while SQL Server chokes on the
same transactions with the same volumes.

SQL Server is _legendary_ [0] for deadlocks even when the workload is mostly
reads and rarely writes. You just don't see this with Oracle, Postgres or
MySQL with default settings.

Not sure why I'd pay for the privilege of extra headaches.

[0] It was a problem in 2008, still not resolved
[https://blog.codinghorror.com/deadlocked/](https://blog.codinghorror.com/deadlocked/)

~~~
manigandham
Yes the defaults are more pessimistic but why is such a problem to change the
locking level?

SQL Server has one of the most advanced query optimizers. Are you running on
an old version or using out of date statistics?

------
ernst_klim
I've dived into the postgres code recently and it was incredible. Lisp legacy
is all over the place, it's literally lisp in C, though unlike many "Langname-
styled C" codebases it looks really clean and organic.

I'm still confused how people prefer oracle [1] other postgres.

[1]
[https://news.ycombinator.com/item?id=18442941](https://news.ycombinator.com/item?id=18442941)

~~~
PaulHoule
Postgres has led the world in trash-talking other databases for 22 years.

I used to call it CrashGreSlow back when Mysql was maintained. Postgres had a
manifesto which trash-talked Oracle but it was not a reliable product at that
time. Maybe the people who said it was fast were running it on ram disks with
fsync turned off or something.

After mysql got bought by Sun and put on ice, postgres caught up with the hype
and now it is pretty reliable. That wasn't always the case.

~~~
threeseed
Seriously though this is true especially from their users.

It is never just PostgreSQL is a great database. It’s always that MySQL,
Oracle, MongoDB and the hundreds of NoSQL databases are all unusable junk with
no unique benefits.

~~~
PaulHoule
Oracle is OK if you can afford it and afford a nice battery backed storage
array that (truthfully) fsyncs quickly.

MongoDB is crap.

I think the people who use Arangodb don't talk about it because they see it as
a competitive advantage.

Right now I am working on an abstraction layer for document databases and
using couchdb side by side with Arangodb. I guess I'll have to write a N1QL
parser to go with my AQL parser.

The database I have the most fun with is SQLite. It is single-user, which
means you can't log in with the SQL monitor when your program is running, but
if you can accept that it's just great.

------
atonse
Thanks to these engines and extensions, Postgres has become that rare tool for
me where I have to ask "why use ___ instead of Postgres?"

If they got their clustering story to be as easy as MongoDB's was 5 years ago
(From what I read, Citus does this well), it's yet another excuse to stick
with it.

~~~
nickjj
Are you running at a scale where a single master DB isn't sufficient?

Because nowadays you can get servers with about 192GB of RAM and 32 CPU cores.

Clustering seems like one of those "what if"[0] scenarios where maybe if you
were operating at roflcopter scale you might need it but for 99.9999999999999%
of cases, a single master DB is more than enough -- at least with a well
designed SQL db like Postgres.

[0]: [https://nickjanetakis.com/blog/optimize-your-programming-
dec...](https://nickjanetakis.com/blog/optimize-your-programming-decisions-
for-the-95-percent-not-the-5-percent)

~~~
zorga
A single master db isn't sufficient for any production app; you should have no
single points of failure and that includes having at a minimum 2 db's.
Clustering isn't a what if scenario, it's a must have for any serious
business, single points of failure are not acceptable.

~~~
manigandham
100% uptime is impossible and the vast majority of companies do not need
anywhere near the 99.99% they claim is necessary, nor can they actually pay
for it.

A 30-second outage to switch to a replica is perfectly fine for production.

~~~
zorga
If you have a replica, you have two db's already as I've suggested. And no one
said anything about uptime; the point was single point of failure, not uptime.
When I said single master, I just meant a single db as opposed to a
master/slave setup.

~~~
Tostino
The GP said single master, not single database.

~~~
zorga
I am the GP, I think I know what I meant better than you, which is why I
clarified that in the comment you just replied to.

------
paulryanrogers
While indeed advanced I find it's focus on reliably and ACID are more useful
than triggers or extensions. Triggers increase write load and extensions
aren't available on all hosting services. Replication, interchangable storage,
and standard SQL/PL support are getting better, but still lag competitors like
MySQL.

~~~
segmondy
I've a love and hate relationship with triggers, but I just need to say
triggers don't have that much overhead if done correctly. 99% of businesses
don't have a scaling concern, and triggers will do them well if they use it to
capture mandatory rules instead of trying to implement it at the application
layer. It's a pain when misused. The key thing is to reason about your user
permission/roles correctly so that you don't end up with cascading failures
when adding new triggers.

------
gt565k
I'd like to see support for computed/derived columns with options to be
materialized or non-materialized.

I guess that's coming in version 12 after a few google searches.

------
siquick
Started using PG recently at a new job but can't really see any benefit over
MySQL. What am I missing?

~~~
gaius
If you lose the root password, you can fix it in Postgres without taking the
database down.

~~~
captainperl
There are several ways to update the root password in MySQL with zero down-
time, either by manipulating the user table, using replication, or using other
accounts (ie. most people grant excessive privileges to role accounts.)

[https://www.percona.com/blog/2014/12/10/reset-mysql-root-
pas...](https://www.percona.com/blog/2014/12/10/reset-mysql-root-password-
without-restarting-mysql-no-downtime/)

------
sroussey
The title should include “free” before database, and it’s generally true.

But the article doesn’t really delve into anything advanced at all. Triggers?
Please.

------
tonysdg
A fun article about PostgreSQL and fsync():

"PostgreSQL's fsync() surprise"

[https://lwn.net/Articles/752063/](https://lwn.net/Articles/752063/)

------
rubyn00bie
Anyone else getting a bad certificate when visiting in Firefox?

~~~
joecool1029
Check your clock and date. If it's off by a lot you'll get weird cert errors.

------
luord
The article doesn't have anything I didn't know about or new but yet another
reminder of how postgres is, IMO, the best piece of sw ever created is nice.

------
thedangler
I started using Postgresql a long time ago when I tried to do a sub query in
MySQL and it didn't support sub queries. Haven't looked back since.

~~~
andscoop
I do not know if this is still the case but the Json support was non existent
in mssql 4 years ago and in postgresql is absolutely phenomenal.

------
NedIsakoff
One word: Exadata..

------
CyanLite2
Postgres doesn't come close to the enterprise-level features AND support as
that of Oracle or SQL Server.

~~~
koolba
What features of Oracle or SQL Server would you say it is lacking?

Regarding support, there's plenty of fantastic companies providing support and
consulting services for PostgreSQL and having used plenty of commercial
databases I know of know no other database that has as vibrant a community
providing amazing _free_ support as well.

~~~
guscost
SQL Server has a column-oriented index type. Oracle has a thing that lets you
query based on what the data was at a particular (recent) timestamp. They both
have lots of useful features like that.

However from my experience with Oracle and from what I've heard about their
source code, its fundamentals are just disgusting. Decades of haphazardly
piled-up features and fixes means that if you're the lucky customer to find a
new bug, good luck understanding what the heck is going on by yourself.

I'd like to think MSSQL is in a bit better shape, but they have lots of the
same incentives so who knows.

~~~
abraae
Oracle is like America. Its not all the same. The database itself is now one
of the most rock solid pieces of software ever built. Other products - yeah,
less so.

~~~
SahAssar
I think this is perhaps referencing a recent HN comment about the code quality
of Oracle DB:
[https://news.ycombinator.com/item?id=18442941](https://news.ycombinator.com/item?id=18442941)

~~~
abraae
Ha, that's pretty interesting. Not too sure how much credit to give it though
based on this:

> It takes 6 months to a year (sometimes two years!) to develop a single small
> feature (say something like adding a new mode of authentication like support
> for AD authentication).

There's no way in the world I would have imagined that adding AD
authentication to any database would be a "single small feature".

