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

121–130 of 147 posts

Re: Postgres is eating the database world

#121

Post by riku_iki »

ksec wrote: post #66

>As DuckDB’s manifesto “Big Data is Dead” suggests, the era of big data is over. I have been stating this since at least 2020 if not earlier. We are expecting DDR6 and PCI-E 7.0 Spec to be finalised by 2025. You could expect them to be on market by no later than 2027. Although I believe we have reach the SSD IOPS limits without some special SSD with Z-NAND. I assume ( I could be wrong ) this makes SSD bandwidth on Se…

> As DuckDB’s manifesto “Big Data is Dead” suggests, the era of big data is over.

yet, their db can't handle many cases where data doesn't fit into memory, and PgSQL always does large writes in single thread..

Re: Postgres is eating the database world

#122

Post by jillesvangurp »

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 ?

Still very limited and frankly all a bit low level primitives. Unless you are a search expert, you won't be able to do much productive with this stuff. If you are, it might fit a few use cases. But then, why limit yourself to just this stuff?

The point of that of course being that the target audience for this stuff is actually people that for whatever reason are a bit shy using the right tools for the right job here and are probably lacking a lot of expertise. The intersection of people with the expertise that would be happy with this narrow subset of functionality is just not a lot of people.

Re: Postgres is eating the database world

#123

Post by riku_iki »

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.

You should look at CocroachDB: it tries to be PG compatible (not there yet), but true HA-first.

Re: Postgres is eating the database world

#124

Post by thinkerswell »

LAC-Tech wrote: post #49
thinkerswell wrote: post #26

Is there an extension that can make it compete with TigerBeetle for transaction processing speed?

That would surely be impossible. TB is designed from the ground up to do one thing and do that one thing well. Postgres is a swiss army knife, and TB is a knife.

Yes I think this is the one area where specialized DBs will win over PG

Re: Postgres is eating the database world

#125

Post by sgarland »

Izkata wrote: post #107
sgarland wrote: post #90

Earlier quoted context omitted.

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

It may use the same name, but reading https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.h... this doesn't really look like the same thing and wouldn't help here. InnoDB's clustered index (usually on the primary key) is used for fast row lookups by that primary key. It only has an advantage if the primary key is the order you want, but otherwise would have the exact same problem postgres's query planner was p…

From the same page:

> In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

I think that unless you're only doing table scans, or your rows are inserted in no discernible order, you should see a speedup. If the latter though, then yes of course, page jumps are page jumps.

Happy to test this to find out.

Re: Postgres is eating the database world

#126

Post by sgarland »

renegade-otter wrote: post #106
sgarland wrote: post #92

Earlier quoted context omitted.

> 99% of people who recommend or use Postgres barely know how to use it. You're not wrong here, although you could just as easily say "99% of people who recommend $DB barely know how to use it." Databases remain a mysterious black box to entirely too many people, despite the three largest (SQLite, Postgres, MySQL) being open source, and having extensive documentation. I've come to the conclusion that most devs don't…

I feel this on a soul level. I wrote about it: https://renegadeotter.com/2023/11/12/your-database-skills-ar...

My god, are you me? I also thoroughly enjoyed this diatribe from [0]:

> First, a whole army of developers writing JavaScript for the browser started self-identifying as “full-stack”, diving into server development and asynchronous code.

> ...early JavaScript was a deeply problematic choice for server development. Pointing this out to still green server-side developers usually resulted in a lot of huffing and puffing.

[0]: https://renegadeotter.com/2023/09/10/death-by-a-thousand-mic...

Re: Postgres is eating the database world

#127

Post by kingraoul »

zozbot234 wrote: post #96
TOMDM wrote: post #19

Postgres is such a great tool. The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance. Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

An "incremental view" is just an index over some custom query. So you're pretty much asking for improvements in Postgres' index support.

He means a materialized view, I believe.

Re: Postgres is eating the database world

#128

Post by scientist4397 »

Macha wrote: post #80
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.

I manage many PostgreSQL databases, the only time it was a pain, it was due to PostGIS upgrade but not the postgresql cluster itself…

You don’t need to dump and re import the database since a long long time…

Re: Postgres is eating the database world

#129

Post by Izkata »

sgarland wrote: post #125
Izkata wrote: post #107

Earlier quoted context omitted.

It may use the same name, but reading https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.h... this doesn't really look like the same thing and wouldn't help here. InnoDB's clustered index (usually on the primary key) is used for fast row lookups by that primary key. It only has an advantage if the primary key is the order you want, but otherwise would have the exact same problem postgres's query planner was p…

From the same page: > In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. I think that unless you're only doing table scans, or your rows are inserted in no discernible order, you should see a speedup. If the latter though, then yes of course, page jumps are page jumps. Happy to test this to find out.

If it does an index scan on the secondary index, unless it's an index-only scan it'll still have to jump around to different pages on the clustered index, so it can't really take advantage of the disk cache unless the whole thing is cached. Or if there's a high correlation between the secondary index's order and clustered index's order, which is what the postgres CLUSTER command does.

Re: Postgres is eating the database world

#130

Post by atonse »

zachmu wrote: post #105

We're writing a postgres-compatible database that doesn't use any postgres code: https://github.com/dolthub/doltgresql/ We're doing this because our main product (Dolt) is MySQL-compatible, but a lot of people prefer postgres. Like, they really strongly prefer postgres. When figuring out how to support them, we basically had three options: 1) Foreign data wrapper. This doesn't work well because you can't use non-nati…

I can totally understand this. Your feature set isn’t compelling enough for me to switch to MySQL when, as the original article states, Postgres basically does everything under the sun, well enough that I can keep my stack simple.

But if it allows me to use my existing Postgres tools, drivers, code, and knowledge then I’d consider it.

Post reply on HN