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

221–230 of 323 posts

Re: PostgreSQL is enough

#221

Post by deathanatos »

kabes wrote: post #196
deathanatos wrote: post #79

Earlier quoted context omitted.

I have certain experience with some technologies, e.g., SQS and Postgres. Say I'm on your team, and you're an application developer, and you need a queue. If you're taking the "we're small, this queue is small, just do it in PG for now and see if we ever grow out of that" — that's fine. "Let's use SQS, it's a well-established thing for this and we're already in AWS" — that's fine, I know SQS too. I've seen both of th…

Ok. I get that. But to play devil's advocate: with that mentality we'd never learn a new technology and still be stuck on punch cards. And I don't have the time anymore for hobby projects. I'd say it's ok to introduce something new as long as it's one thing at a time and not an entire new stack in the "a rewrite will solve all problems" projects

I'm okay with new technology, actually, but the person introducing it has to be able to champion it & do the work of debugging issues and answering questions about its interactions with the rest of the system. I.e., they have to be responsible for it.

The last part in my parent comment is more of a "it was chucked over the fence, and it is now crashing, and nobody, not even the devs that chose it, know why".

I do have examples of what you describe, too: a dev I worked with introduced a geospatial DB to solve issues with geospatial queries being hard & slow in our then-database (RDS did not, at the time, support such queries) — so we went with the new thing. It used Redis's protocol, and was thus easy to get working with¹. But the dev that introduced it to the system was capable of explaining it, dealing with issues with it — to the extent of "upstream bugs that we encounter and produce workarounds", and otherwise being a lead for it. That new tech, managed in that way by a senior eng., was successful in what it sought to do.

The problematic parts/components/new introductions of new tech … never seem to have that. That's probably partly the problem: it's such an inherently non-technical issue at its heart. The exact thing almost doesn't matter.

> as long as it's one thing at a time

IME it's not. When there are problems, it's never just one new thing at a time.

> a rewrite will solve all problems

And the particular system I had in my mind while writing the parent post was, in fact, in the category of "a rewrite will solve all problems".

Some parts of the rewrite are doing alright, but especially compared to the prior system, there are just so. many. new. components. 2 new queue systems, new databases, etc. etc. So it's then hard to learn one, particularly without someone championing its success. It's another to self-learn and self-bootstrap on 6 or 8 new services.

¹(Tile38)

Re: PostgreSQL is enough

#222

Post by mkleczek »

randomdata wrote: post #138
sgarland wrote: post #129

Earlier quoted context omitted.

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

> that is the fault of you (or perhaps your ORM) for not writing a JOIN. It's your fault for not writing a join if you need a join. But that's not where the n+1 problem comes into play. Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when y…

> Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations.

You can easily get hierarchical output format from Postgres with its JSON or XML aggregate functions.

You can have almost all benefits of an embedded database by embedding your application in the database.

Just change perspective and stop treating Postgres (or any other advanced RDBMS) as a dumb data store — start using it as a computing platform instead.

Re: PostgreSQL is enough

#223

Post by DanielHB »

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…

Besides what others are saying here, data integrity is a lot easier with a relational database if your data is heavily relational. I have managed systems with About data types, what limitations are you referring to? jsonb is well supported in many dbs and throwing random large binary blobs in the middle with your normal data is a bad idea with or without a relational database.

I can see the appeal to replace noSQL solutions though, a lot of people are using S3 (and other storage solutions) as a makeshift database lately

Re: PostgreSQL is enough

#224

Post by HermitX »

I really like Postgres, but I still don't think Postgres can do everything. From my own experience, when the scale of data becomes very large, when you need to analyze massive amounts of data, your Postgres always seems somewhat inadequate. I think Postgres performs perfectly when dealing with OLTP-type workloads. However, if you need more OLAP support, I recommend you use StarRocks (www.starrocks.io). We now import data from Postgres into StarRocks for data analysis, and the experience is fantastic. Moreover, StarRocks also supports direct queries on data lakes, which is incredible.

Re: PostgreSQL is enough

#225

Post by bun_terminator »

dig1 wrote: post #128
bun_terminator wrote: post #110

Earlier quoted context omitted.

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

eh the oracle upgrades went awful. But I try to not touch the database at all if I can

Re: PostgreSQL is enough

#226

Post by bun_terminator »

DanielHB wrote: post #223
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…

Besides what others are saying here, data integrity is a lot easier with a relational database if your data is heavily relational. I have managed systems with About data types, what limitations are you referring to? jsonb is well supported in many dbs and throwing random large binary blobs in the middle with your normal data is a bad idea with or without a relational database. I can see the appeal to replace noSQL so…

I think my wtf moment was that you can't have real variable length strings. And I'm pretty pampered by c++ highly nuanced numeric types with varying sizes and signed/unsigned.

Re: PostgreSQL is enough

#227

Post by neovim_btw »

emseetech wrote: post #22

This makes a strong case, but I've decided to start every new project with sqlite and not switch until absolutely necessary. If Postgres is the 90% case, then sqlite is the 80% case and is also dead simple to get going and genuinely performant. So when vertical scaling finally fails me, I know I'll be at a wonderful place with what I'm building.

Not with concurrent writes, you're not!

Even with a WAL or some kind of homegrown spooling, you're going to be limited by the rate at which one thread can ingest that data into the database.

One could always shard across multiple SQLite databases, but are you going to scale the number of shards with the number of concurrent write requests? If not, SQLite won't work. And if you do plan on this, you're in for a world of headaches instead of using a database that does concurrency on its own.

Don't get me wrong; SQLite is great for a lot of things. And I know it's nice to not have to deal with the "state" of an actual database application that needs to be running, especially if you're not an "infrastructure" team, but there's good reasons they're ubiquitous and so highly regarded.

Re: PostgreSQL is enough

#228

Post by randomdata »

Ensorceled wrote: post #216
randomdata wrote: post #198

Earlier quoted context omitted.

Are we? "Suffer" was the word used right from the beginning for good reason. Computers aren't magic. I find no importance in pointing out that fact. Presumably everyone here already knows that. And if it turns out that they don't, who cares? That's their problem. Makes no difference to me.

I agree that suffer is the right word, but unclear. You are getting down voted because a lot of people are interpreting to mean you are saying applications using sqlite don't have n+1 queries.

1. At time of writing, there has been one downvote in the first comment, followed by one upvote in the subsequent comment. Not only does that not translate to "a lot of people", it was quite likely just one person. And unless that person was you, it is impossible to know what their intent was. I'm not sure what are you trying to add here.

2. Who gives a shit? If the "computers truly are magic" camp don't understand what I wrote, great! It wasn't written for them in the first place. If that compels them to use their time pressing a button instead of learning about how computers work, great! Not my problem. I'm not sure what you are trying to add here.

Re: PostgreSQL is enough

#229

Post by refset »

api wrote: post #141
sgarland wrote: post #136

Earlier quoted context omitted.

It is possible, if you pay for it. You can do Multi-AZ Clustered Instances in RDS, where you get the benefits of Multi-AZ failover with traffic sharing. If you can run your own infra – at least on an EC2 level – you can do things like Citus [0] for Postgres, which is about as close to "just add database nodes" as you'll get. [0]: https://www.citusdata.com/

Very clunky compared with things like CockroachDB. Ultimately using something like Postgres in 2024 is just an on-ramp for expensive managed cloud database services, which is probably why it's promoted so much.

> which is probably why it's promoted so much

I think what you're actually observing is simply that Postgres is by far the most vendor-neutral DBMS (/API) available, and therefore the volume of conversation & marketing around it stacks up very disproportionately.

In contrast, asides from MySQL all other DBMS options require getting invested in ~one company and relying entirely on the whims & fortunes of their commercial support organisation.

A relevant article and comment thread: https://news.ycombinator.com/item?id=31425872

Re: PostgreSQL is enough

#230

Post by dagss »

Too wrote: post #150
niels_bom wrote: post #111

Earlier quoted context omitted.

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

It’s not slow by itself. It’s a single point of bottleneck that will inevitably become slow as you cram everything into it.

...but by trying to avoid the bottleneck and moving things to backend, you make things 10x worse resource wise for the DB. So it is not a easy tradeoff.

Take any computation you can do in SQL like "select sum(..) ...". Should you do that in the database, or move each item over the network and sum them in the backend?

Summing in the database uses a lot less resources FOR THE DB than the additional load the DB would get from "offloading" this to backend.

More complex operations would typically also use 10x-100x less resources if you operate on sets and amortize the B-tree lookups over 1000 items.

The answer is "it depends" and "understand what you are doing"; nothing about it is "inevitable".

Trying to avoid computing in the DB is a nice way of thinking you maxed out the DB ...on 10% of what it should be capable of.

Post reply on HN