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

Postgres is eating the database world

medium.com

Post reply on HN
Print view View original

71–80 of 147 posts

Re: Postgres is eating the database world

#71

Post by afandian »

tacone wrote: post #23
oldandtired wrote: post #22

Earlier quoted context omitted.

The problem is not the query planner per se. There is a much more subtle problem and it is related to how you have created the query in the join structure. For many queries, the order in which you specify the joins doesn't really matter. But there are a number of classes where the join order dramatically affects how fast the query can actually run and nothing the query planner does will change this. I came across thi…

Would love to see a practical example of this. Another thing to consider is table fragmentation. Fragmentation > bad row count estimation > bad query plan.

We hit some weird query plans. I don't have forensic evidence but here's an example: https://crossref.gitlab.io/engineering/decision-records/dr-0...

Combination of two joins, filtering on all tables, and sorting by the LEFT one. Performance was fine, until we hit the scale when it suddenly became unpredictable.

In hindsight, given the variability of the queries and table structure, I don't think any query planner could have done a good job. The natural answer was to denormalize. But the journey to get there was a little unpredictable.

Re: Postgres is eating the database world

#72

Post by vsnf »

fulafel wrote: post #47

Someone who picked their tools with good tech judgement 25 years ago can be using the same today (eg PG, Python, Linux) without corporate control of them, it's pretty great.

That feels a bit like hindsight talking. Linux perhaps, but were Python and Postgres really the obvious good judgement choices 25 years ago? Every other choice was poor judgement?

Re: Postgres is eating the database world

#73

Post by NorwegianDude »

This post was very wrong and misleading on multiple points.

I have seen a lot of people praising Postgres over e.g. MariaDB. But more often than not it seems to be people how lack knowledge.

Take this linked post, where the author points out "The untuned PostgreSQL performs poorly (x1050)" later followed by "This performance can’t be considered bad, especially compared to pure OLTP databases like MySQL and MariaDB (x3065, x19700)".

Frist of all, those are not pure OLTP databases. And if the author took a better look at the benchmark he would see that MariaDB using ColumnStore is at x98. That's 10x the performance of Postgres out of the box, and 200x faster than the author stated.

Re: Postgres is eating the database world

#74

Post by CodesInChaos »

avinassh wrote: post #67
egnehots wrote: post #54

Postgres is far from perfect: - The codebase is old and huge, accruing some heavy technical debt, making it a less than ideal foundation for iterating quickly on a new paradigm like AI and vector databases. - Some ancient design decisions have aged poorly, such as its one connection per process model, which is not as efficient as distributing async tasks over thread pools. If not mitigated through an external connect…

Can you tell me more about the write amplification issue?

Postgres handles updates as insert+delete, and its secondary indexes reference the physical location of the row, instead of the primary key. This means that whenever an update results in an insert to a different page, the index needs to be updated as well, even if the indexed column hasn't been modified.

At least it has an optimization that if the insert ends up in the same page, it won't need to update the index https://www.postgresql.org/docs/current/storage-hot.html

Replication has a similar amplification issue. Historically postgres has favored physical replication over per-row logical replication, that means that replication needs to transfer every modified page, including modified indexes, instead of just the new value of the modified row. (I think logical replication support has improved over the last couple of years).

There is the OrioleDB project, which attempts to improve on the design flaws in postgres's storage engine, but it's definitely not production ready yet.

Re: Postgres is eating the database world

#75

Post by layer8 »

fulafel wrote: post #47

Someone who picked their tools with good tech judgement 25 years ago can be using the same today (eg PG, Python, Linux) without corporate control of them, it's pretty great.

Neither of those three would have been particularly good judgement 25 years ago, without a crystal ball.

Re: Postgres is eating the database world

#76

Post by Vonng »

NorwegianDude wrote: post #73

This post was very wrong and misleading on multiple points. I have seen a lot of people praising Postgres over e.g. MariaDB. But more often than not it seems to be people how lack knowledge. Take this linked post, where the author points out "The untuned PostgreSQL performs poorly (x1050)" later followed by "This performance can’t be considered bad, especially compared to pure OLTP databases like MySQL and MariaDB (x…

How about: PostgreSQL tuned (x47). PostgreSQL + Hydra Extension (x42) PostgreSQL + ParadeDB Extension (x10.7)

Re: Postgres is eating the database world

#77

Post by hans_castorp »

jillesvangurp wrote: post #59

> not to mention its ElasticSearch grade full-text search capabilities. I played with postgresql a while ago to implement search. It's not horrible. But it's nowhere near Elasticsearch in terms of its capabilities. It's adequate for implementing very narrow use cases where search ranking really doesn't matter much (i.e. your revenue is not really impacted by poor precision and recall metrics). If your revenue does de…

> I played with postgresql a while ago to implement search. It's not horrible. But it's nowhere near Elasticsearch in terms of its capabilities.

The article is referring to the ParadeDB extension, not the built-in full text search

Re: Postgres is eating the database world

#78

Post by slyall »

vsnf wrote: post #72
fulafel wrote: post #47

Someone who picked their tools with good tech judgement 25 years ago can be using the same today (eg PG, Python, Linux) without corporate control of them, it's pretty great.

That feels a bit like hindsight talking. Linux perhaps, but were Python and Postgres really the obvious good judgement choices 25 years ago? Every other choice was poor judgement?

Well 25 years ago was pretty much (December 1998) when "LAMP"[1] was defined and that was originally Linux, Apache, MySQL and PHP.

So Postgres and Python were not the obvious choices back then.

[1] https://en.wikipedia.org/wiki/LAMP_(software_bundle)

Re: Postgres is eating the database world

#79

Post by hans_castorp »

egnehots wrote: post #54

Postgres is far from perfect: - The codebase is old and huge, accruing some heavy technical debt, making it a less than ideal foundation for iterating quickly on a new paradigm like AI and vector databases. - Some ancient design decisions have aged poorly, such as its one connection per process model, which is not as efficient as distributing async tasks over thread pools. If not mitigated through an external connect…

> Some ancient design decisions have aged poorly, such as its one connection per process model

Oracle uses the same model by default on Linux.

Since 19 (or maybe earlier) it is configurable though, but the default is still one process per connection if I'm not mistaken.

Re: Postgres is eating the database world

#80

Post by Macha »

patrickdavey wrote: post #32

I have a handful of sites I run on a VPS with a basic setup, including MySQL. One thing I've always liked about MySQL is that it pretty much looks after itself, whereas with Postgres I've had issues before doing upgrades (this was with brew though) and I'm not clear on whether it looks after itself for vacuuming etc. Should I just give it a go the next time I'm upgrading? It does seem like a tool I need to get famili…

Postgres updates are definitely a pain. MySQL is usually just a matter of upgrading the package and restarting the server for the projects I run, but postgres is a full dump and import process.
Post reply on HN