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

PostgreSQL is enough

gist.github.com

Post reply on HN
Print view View original

121–130 of 323 posts

Re: PostgreSQL is enough

#121

Post by themerone »

bun_terminator wrote: post #113
afandian wrote: post #109

Earlier quoted context omitted.

It's a narrow use-case that doesn't cover RDMS. SQLite fits it though.

The first thing I did was ask why we don't use sqlite or at least postgres. The answer was that they are free and therefore we don't trust them. So Oracle it is. Which is bananas because our customers have to buy an oracle license, which is money that we don't get. Pretty wild

Sqlite is running on billions of devices and has a test suite with 100% branch coverage.

Only a fool would put more trust in Oracle.

Re: PostgreSQL is enough

#122

Post by zilti »

EasyMark wrote: post #44
Ensorceled wrote: post #39

Earlier quoted context omitted.

> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%? I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?

it's much bigger and requires running a server, that's why I use sqlite3, and my needs are 99% modest most of the time.

It requires running a separate process.

Re: PostgreSQL is enough

#123

Post by ndriscoll »

chasd00 wrote: post #99
philippemnoel wrote: post #84

I'm one of the makers of ParadeDB, a modern alternative to Elasticsearch. We build Postgres extensions to do fast search (pg_bm25) and analytics (pg_analytics). I love Postgres. If you have a small workload, like a startup, it certainly makes sense to stay within Postgres as long as you can. The problem is, at scale, Postgres isn't the answer to everything. Each of the workloads one can put in Postgres start to grow…

what is "at scale"? Is there a specific metric or range of metrics that raises a flag to begin considering something else? For example, in the olden days when it was my problem, page load times were the metric. Once it got high enough you looked for the bottleneck, solved it, and waited. When the threshold was broken again you re-ran the same process. Is there an equivalent for postgres?

This bugs me every time performance comes up. No one is ever concrete, so they can never be wrong.

If Michael Jackson rose from the dead to host the Olympics opening ceremony and there were 2B tweets/second about it, then postgres on a single server isn't going to scale.

A crud app with 5-digit requests/second? It can do that. I'm sure it can do a lot more, but I've only ever played with performance tuning on weak hardware.

Visa is apparently capable of a 5-digit transaction throughput ("more than 65,000")[0] for a sense of what kind of system reaches even that scale. Their average throughput is more like 9k transctions/second[1].

[0] https://usa.visa.com/solutions/crypto/deep-dive-on-solana.ht...

[1] PDF. 276.3B/year ~ 8.8k/s: https://usa.visa.com/dam/VCOM/global/about-visa/documents/ab...

Re: PostgreSQL is enough

#124

Post by alternatex »

niels_bom wrote: post #111
legohead wrote: post #78

Earlier quoted context omitted.

My saying has always been: be nice to the DB Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your stack.

Would you say it's slower than file IO too?

Kind of irrelevant since a DB provides some guarantees that a simple file does not by default.

Re: PostgreSQL is enough

#125

Post by bun_terminator »

bsdpufferfish wrote: post #118
bun_terminator wrote: post #87

As a hardcore c++ guy, I recently switched to a company heavily into databases. I never had contact to databases before. And I'd like to go one step further: Why databases? I come from an industry that heavily uses custom binary file formats. And I'm still bewildered by the world of databases. They seem to solve many issues on the surface, but not really in pratice. The heavy limitations on data types, the update dis…

Read the original relational database paper by Codd. The problem is that trees of data are not flexible for querying, you really need a graph. If you draw it out on paper, you will find a relation is a very efficient way to store general graphs. I'm actually suprised to hear this from a C++ engineer, because sorting and searching columns of data is the name of the game in C++. Another key problem they solve is separa…

But that's the thing: All that is nice on paper. Of course the relational nature is nice. Of course the disk representation is nice. But I never feel like that's worth the trouble day in day out. The costs, the mystery box of performance, the insane statements, the extra hardware, the updates, the strange data types, the box of tricks everyone needs to make them behave. But I've been made aware that our usecases suck, so I'll attribute most to that.

Re: PostgreSQL is enough

#126

Post by tootie »

bun_terminator wrote: post #95
treflop wrote: post #91

Earlier quoted context omitted.

Well a database is just a binary file format with an API attached on. Because having hundreds or thousands of concurrent file handles across a data center is kind of hard.

ok I get that, but at least our applications usually only have one thing (one thread of one process on one machine) operating on the database. So maybe it's just our silly usecase

I think it's important context that gets lost in so many tech conversations. Software engineering is a vast field covering almost every domain of human endeavor and they're all going to have different constraints. Some are technical and some are social. If you're working with 30 other engineers trying to marshal and unmarshal the same set of data for different purposes, it's really easy to tell them all "use SQL" instead of "here's the binary format invented by the guy who quit 7 years ago" even if that binary format was technically superior.

Re: PostgreSQL is enough

#127

Post by redskyluan »

Regarding scale, PostgreSQL may not cover all bases. Though I'm a PostgreSQL fan, I prefer specialized services for specific tasks. Using PG-based plugins could help, but a dedicated SQL-compatible database is often a better fit.

For vector retrieval, going with a database like Milvus, designed for vectors, is usually more efficient and cost-effective. Similar principles apply across domains. Is there any vectordb under PG format?

What if we've got a deeply customized distributed vector search service on PostgreSQL, that's impressive!

Re: PostgreSQL is enough

#128

Post by dig1 »

bun_terminator wrote: post #110
dig1 wrote: post #106

Earlier quoted context omitted.

> They seem to solve many issues on the surface, but not really in practice I believe you haven't had a chance to work on problems that require an actual database. Multi-user access, ACID support, unified API (odbc/jdbc), common query language... all of these would require many man-years to set properly with a custom solution. > the update disasters, the incompatibility between different SQL engines etc all make it s…

All these comments seem to fuel my suspicion that we in fact shouldn't use databases, because we don't use any of these features. We just use them as external data storage for a single application. And not even that much data, like But the updating I would have expected to go more smoothly. If you make a point of using a software dedicated to managing data, I sure as hell would expect an update to go so smooth that I…

> because we don't use any of these features. We just use them as external data storage for a single application.

You are using it :) Reboot the server where the database runs or suddenly cut off the connection. Unless you have ACID-compatible storage, you'll have malformed data.

Plan for the future and use a database from the start. When your project/company expands and starts to use multiple applications/services (and that inevitably happens), you'll see (one of) the benefits of the database.

> But the updating I would have expected to go more smoothly

I'm not sure what you are talking about. Database updates are one of the smoothest (critical) software updates you'll find, assuming the database has a good track record.

Re: PostgreSQL is enough

#129

Post by sgarland »

randomdata wrote: post #92
Ensorceled wrote: post #39

Earlier quoted context omitted.

> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%? I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?

Postgres complicates the application in several ways. In particular, Postgres suffers from the n+1 problem, while SQLite does not. That requires a significant amount of added complexity in the application to hack around. Why over engineer the application before it has proven itself as something anyone even wants to use? Let's face it, the large majority of software written gets thrown away soon after it is created. I…

This is a misunderstanding of the n+1 problem, which is exacerbated by SQLite's deceptive phrasing of the issue:

> In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag.

While the above is true on its own, this is _not_ the typical definition of n+1. The n+1 problem is caused by poor schema design, badly-written queries, ORM, or a combination of these. If you have two tables with N rows, and your queries consist of "SELECT id FROM foo; SELECT * FROM bar WHERE id = foo.id_1...", that is not the fault of the DB, that is the fault of you (or perhaps your ORM) for not writing a JOIN.

Re: PostgreSQL is enough

#130

Post by brightball »

dfee wrote: post #74
brightball wrote: post #58

Earlier quoted context omitted.

PG works really well as a message queue and there's several excellent implementations on top of it. Most systems are still going to need Redis involved just as a coordinator for other pub/sub related work unless you're using a stack that can handle it some other way (looking at BEAM here). But there are always going to be scenarios as an application grows where you'll find a need to scale specific pieces. Otherwise t…

Worth noting that Postgres has a pubsub implementation built in: listen/notify. https://www.postgresql.org/docs/current/sql-notify.html

Oh yea, definitely aware of it. I believe many of the queuing solutions utilize it as well.

I've ready a lot of reports (on here) that it comes with several unexpected footguns if you really lean on it though.

Post reply on HN