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

81–90 of 147 posts

Re: Postgres is eating the database world

#81

Post by sgarland »

CodesInChaos wrote: post #74
avinassh wrote: post #67

Earlier quoted context omitted.

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 htt…

It’s worse than that, unfortunately – since the entire row has to be updated due to MVCC, if a single index is updated, then _all_ indexes are updated. If you have a wide table with a lot of indexes, each UPDATE is N*index writes. This becomes even worse with multiple updates if indexed columns aren’t k-sortable (like UUIDv4), since now the DB will probably have to jump around multiple pages despite the rows being logically sequential.

Re: Postgres is eating the database world

#82

Post by kiwicopple »

Vonng wrote: post #60
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…

That's true for the kernel, How about extensions such as ParadeDB BM25 https://www.paradedb.com/ + PGroonga https://pgroonga.github.io/ + PG Bigm https://github.com/pgbigm/pg_bigm ?

also with pg_trgm[0] (mentioned by OP) and pgvector for semantic search you have a pretty powerful search toolkit. for example, combining them for Hybrid Search [1]

[0] https://www.postgresql.org/docs/current/pgtrgm.html

[1] Reciprocal Ranked Fusion: https://supabase.com/docs/guides/ai/hybrid-search

Re: Postgres is eating the database world

#83

Post by coldtea »

throwawaaarrgh wrote: post #51

It's not a best practice, it's a fad. 99% of people who recommend or use Postgres barely know how to use it. Another trendy database will come along and you'll stop seeing all these posts about it. Happens every decade. I'll link back to this post in a few years with "I told you so".

You're off by 30 years already...

Re: Postgres is eating the database world

#84

Post by sgarland »

lmm wrote: post #31

Postgres is still single-node-first, and while Citus exists I'm skeptical that it can ever become as easy to administer as a true HA-first datastore. For me the reason to use something like Cassandra or Kafka was never "big data" per se, it was having true master-master fault tolerance out of the box in a way that worked with everything.

> Postgres… Kafka… Cassandra

These are all wildly different products that should not be considered for the same purposes.

Re: Postgres is eating the database world

#85

Post by sgarland »

monero-xmr wrote: post #2

Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major p…

IME, many query flips occur due to inadequate analysis, inadequate vacuuming, or table / index bloat.

Examining statistics for your tables / indices can be quite helpful in determining the issue.

Re: Postgres is eating the database world

#86

Post by dagw »

throwawaaarrgh wrote: post #51

It's not a best practice, it's a fad. 99% of people who recommend or use Postgres barely know how to use it. Another trendy database will come along and you'll stop seeing all these posts about it. Happens every decade. I'll link back to this post in a few years with "I told you so".

Another trendy database will come along and you'll stop seeing all these posts about it. Happens every decade.

And then after a couple of years people will realise that Postgres can do everything the trendy database can do and come back to Postgres. Happens every decade. This is at least 'hype cycle' 3 for Postgres since I started my career.

Re: Postgres is eating the database world

#87

Post by sgarland »

oldandtired wrote: post #22
monero-xmr wrote: post #2

Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major p…

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…

Usually that kind of problem is a result of exceeding {from,join}_collapse_limit, which defaults to 8. If you have more tables than that in a query, Postgres doesn’t exhaustively try all ordering to determine the best, and instead uses its genetic algorithm, which can be worse.

You can raise the limit at the risk of causing query planning times going up exponentially, or refactor your schema, or, as you did, rewrite it to be more restrictive out of the gate. That way, those join paths will be found first and so will be the best found when the planner gives up.

Re: Postgres is eating the database world

#88

Post by Shish2k »

slyall wrote: post #78
vsnf wrote: post #72

Earlier quoted context omitted.

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)

In my personal experience of being around back then, postgres and python were still considered "technically better", but such a massive pain in the ass to install (especially on cheap shared hosting where it was often actually impossible to install) that only the most masochistic people would even try. I myself wrote a fastcgi implementation in PHP which would allow a web server which only supported php to call python under the hood and forward the inputs and outputs :P

It is kind of depressing that 25 years later, no other language has even attempted to compete with PHP in the “easy to get started on bargain-basement-tier shared web hosts” space D:

Re: Postgres is eating the database world

#89

Post by sgarland »

teaearlgraycold wrote: post #3
monero-xmr wrote: post #2

Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major p…

I’m currently learning the basics of this. Currently struggling with multiple similar scenarios where switching from a left to an inner join, or any equivalent, kills performance. But these are aggregation queries so there are only 5 records returned. I could just filter in my app code no problem. But why the hell does adding “where foo.id is not null” in SQL make it O(N*M)??? CTEs are not helping.

It shouldn’t. At worst, if id doesn’t have a usable index, a sequential scan of foo, so O(N).

Re: Postgres is eating the database world

#90

Post by sgarland »

Izkata wrote: post #17
teaearlgraycold wrote: post #13

Earlier quoted context omitted.

Yup, used a nice EXPLAIN GUI tool as well to try and help.

Every time stuff like this comes up I wonder how much the people having issues would be willing to share - because every time I've fought with the postgres query planner, it eventually turned out what I wanted to do had massively worse performance* because of something I didn't take into account that postgres did. And each time, once I learned what that thing was, I was able to fix it the right way and get the query…

> CLUSTER

And here we see the benefit of clustered indices, á la MySQL. Assuming, of course, your PK is k-sortable.

Post reply on HN