Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL is the worlds’ best database

2ndquadrant.com

Post reply on HN
Print view View original

61–70 of 365 posts

Re: PostgreSQL is the worlds’ best database

#61

Post by hnarn »

bertil wrote: post #55

I’m assuming anyone who has to make that decision already knows this but, while PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale, or to train or store your machine learning features. It works, but it’s not great at scale. You can get away with having a scheduled pg_dump early on, some reports on that, while you figure out an ETL/Messaging process — but picki…

> PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale

How do you define an "analytic" database? Time series data, or something else?

Re: PostgreSQL is the worlds’ best database

#62

Post by BiteCode_dev »

onion2k wrote: post #7
Jonnax wrote: post #2

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

One of Postgres's biggest pros (over other commonly used dbs in web dev) is Row Level Security. Controlling what data a user can access at the database rather than the application or as part of a query makes the application logic a lot simpler. That's a big win.

But that also means that:

- the code is not reusable outside of a database setting. So not cacheable.

- the code is not reusable accross different storage layers. So not portable.

- the code may needs updating if the schema change, you can't abstract that

- changing the logic means a db migration

- testing the code requires a DB

- tooling support to check that code si limited to SQL tooling, which is very weak, especially for code completion, refactor and debugging.

That's a lot of constraints for just making the application logic a lot simpler.

Re: PostgreSQL is the worlds’ best database

#63

Post by hnarn »

jwr wrote: post #25

PostgreSQL is great if you: a) are OK with using SQL (this is not obvious) b) do not need a distributed database I've spent a lot of time on looking at database solutions recently, reading through Jepsen reports and thinking about software architectures. The problem with PostgreSQL is that it is essentially a single-point-of-failure solution (yes, I do know about various replication scenarios and solutions, in fact I…

Can you just shortly elaborate on why PostgreSQL is not good for distributed scenarios for those of us who are/were under the impression that psql does have stable distribution features?

Re: PostgreSQL is the worlds’ best database

#64

Post by ddorian43 »

wlll wrote: post #57
willvarfar wrote: post #18

Earlier quoted context omitted.

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new…

Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-...

No. LSM/Rocksdb databases do "block compressions". Even better is dynamic dictionary compression: https://github.com/facebook/rocksdb/wiki/Dictionary-Compress...

Re: PostgreSQL is the worlds’ best database

#65

Post by redis_mlc »

mulmen wrote: post #44
redis_mlc wrote: post #22

Earlier quoted context omitted.

First of all, Yahoo historically used Oracle Enterprise and MySQL for RDBMS, roughly 50% each. Large companies use some of everything, but I never saw PG in use there.

https://www.computerworld.com/article/2535825/size-matters--... Yahoo’s use of Postgres was legendary.

No, it wasn't legendary.

That's just one data warehouse application.

The one I administered, just as big, was Saturn. And it was MySQL.

Source: worked at Yahoo, saw way more MySQL than PG.

Re: PostgreSQL is the worlds’ best database

#66

Post by nicoburns »

ksec wrote: post #24
drdaeman wrote: post #3

Earlier quoted context omitted.

Replication, failover and high availability are complicated matters. I believe there are other SQL databases that may be said to handle this better than PostgreSQL. At the very least, CockroachDB is much simpler to set up and manage. Cannot vouch for its stability and complexity of abnormal emergency recoveries, though - haven't used it long enough, only had simplest outages (that it had handled flawlessly).

>Replication, failover and high availability are complicated matters. I wonder why Postgre hasn't improved in these areas and instead leaving it to third party solution. I mean every year these few points are still listed as something that flavours MySQL.

It might be partly because Postgres is largely developed by the providers of "3rd-party" solutions for features like this. You could see it as an open-core model of sorts.

Re: PostgreSQL is the worlds’ best database

#67

Post by krainboltgreene »

INTPenis wrote: post #29

My first job in IT, I was 18, it was the early 2000s. I was a nerd but in professional IT I was essentially a blank slate. I lucked up and ended up with a real hacker for a boss. As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. He was a BSD guy and a postgres guy. He made me into a fanboy of both those technologies. So out of sheer luck I've preferred Postgres for over 15…

> As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres.

I'm extremely curious how this worked out.

Re: PostgreSQL is the worlds’ best database

#68

Post by yrro »

mrweasel wrote: post #14
Jonnax wrote: post #2

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

I think the only reason I wouldn't choose Postgresql is if I need master-master replication. It's not that you can't create a master-master setup using Postgresql, I believe 2ndquadrant have and add-on that allows this. It just feels like it's messing with the fundamentals of the database on such a low level that I really only trust it, if it's part of Postgresql it self.

Logical replication has been in PostgreSQL itself for a while now.

There are naturally caveats: sequences don't get replicated, so you need to configure each replica with a non-overlapping range for each sequence. And DDL statements are also not replicated, so you have to migrate database schemas by hand on each replica.

Re: PostgreSQL is the worlds’ best database

#69

Post by mulmen »

redis_mlc wrote: post #65
mulmen wrote: post #44

Earlier quoted context omitted.

https://www.computerworld.com/article/2535825/size-matters--... Yahoo’s use of Postgres was legendary.

No, it wasn't legendary. That's just one data warehouse application. The one I administered, just as big, was Saturn. And it was MySQL. Source: worked at Yahoo, saw way more MySQL than PG.

You just said you never saw Postgres at Yahoo. Yet Yahoo had possibly the world’s largest Postgres installation. I consider that legendary. I’ve certainly been aware of it for over a decade. It was also an early (?) columnar store which is now commonplace among OLAP databases. Seems like if it didn’t start a trend it was at least an early adopter. I’d say that’s legendary too. Even today the scale is impressive.

Now you say you did see some Postgres but there was more MySQL. Fine, you were there, I’ll take your word for it. But I can’t reconcile your own statements on this. Yahoo very clearly used Postgres.

You ran a 2PB MySQL install. Cool, I’d love to hear about that, truly. Do you have any written accounts or talks about that?

Re: PostgreSQL is the worlds’ best database

#70

Post by yrro »

redis_mlc wrote: post #36

Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-…

If you grant untrusted users administrative access then you're going to get owned, regardless of your chosen DB engine.
Post reply on HN