PostgreSQL is enough • Hacker News Board

Re: PostgreSQL is enough

#251

Post by cbreezyyall » Tue, Feb 06, 2024, 9:48 PM UTC

cnity wrote: post #142

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…

This desire can sometimes be so strong that people insist on truly wacky decisions. I have before demonstrated that Postgres performs perfectly well (and in fact exceeds) compared with a niche graph database, and heard some very strange reasons for why this approach should be avoided. A lot of the time you hear that it's engineers who chase shiny technology, but I've seen first hand what can happen when it's leadersh…

Often referred to as resume driven development.

Re: PostgreSQL is enough

#252

Post by beagle3 » Tue, Feb 06, 2024, 10:01 PM UTC

jamwil wrote: post #217

beagle3 wrote: post #212

Earlier quoted context omitted.

To me this argument sounds like “I don’t have time for hobby projects, so I’m going to treat this professional one as a hobby”. I always start a professional project with technologies I am intimately familiar with - have used myself, or have theoretical knowledge of and access to someone with real experience. There has never been a new shiny library/technology that would have saved more than 10% of the project time,…

I take your point but you don’t explain how you came to be intimately familiar with those technologies in the first place. Applied consistently, this logic would seem to preclude becoming familiar with anything.

For projects where I have a paying customer, this rule is absolute; I do not experiment on my client's time (and dime) unless they specifically request it.

But I do have projects which I finance myself (with myself as customer), and which do not have a real deadline. I can experiment on those. Call them "hobby" projects if you insist.

> Applied consistently, this logic would seem to preclude becoming familiar with anything.

Well, project requirements always rank higher, and many projects require some piece I am unfamiliar with (a new DB - e.g. MSSQL; a new programming language; etc). That means one does get familiar on a need basis , even applying this approach robotically.

If a project requires building the whole thing around a new shiny technology with few users and no successful examples I can intimately learn from ... I usually decline taking it.

Re: PostgreSQL is enough

#253

Post by beagle3 » Tue, Feb 06, 2024, 10:03 PM UTC

nyrikki wrote: post #240

beagle3 wrote: post #212

Earlier quoted context omitted.

To me this argument sounds like “I don’t have time for hobby projects, so I’m going to treat this professional one as a hobby”. I always start a professional project with technologies I am intimately familiar with - have used myself, or have theoretical knowledge of and access to someone with real experience. There has never been a new shiny library/technology that would have saved more than 10% of the project time,…

This isn't a dichotomy. That is the point of DDD,SoA,Clean, Hexagonal patterns. Make a point to put structures and processes in place that encourage persistence ignorance in your business logic as the default and only violate that ideal where you have to. That way if you outgrow SQL as a message bus you can change. This mindset also works for adding functionality to legacy systems or breaking apart monoliths. Choosin…

I was not saying "psql is all you'll ever need". I was just replying to

>>> "Applied consistently, this logic would seem to preclude becoming familiar with anything."

As a general principle.

Re: PostgreSQL is enough

#254

Post by hot_gril » Tue, Feb 06, 2024, 10:06 PM UTC

cpursley wrote: post #250

cryptonector wrote: post #199

Earlier quoted context omitted.

No. (You don't want to cause blocking I/O in transactions.)

Non-blocking: https://github.com/supabase/pg_net

Impressive!

Re: PostgreSQL is enough

#255

Post by sgarland » Tue, Feb 06, 2024, 10:20 PM UTC

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.

I don't know how precisely strict you expect a tree to be in RDBMS, but this [0] is as close as I can get. It has a hierarchy of product --> entity --> category --> item, with leafs along the way. In this example, I added two bands (Dream Theater [with their additional early name of Majesty], and Tool), along with their members (correctly assigning artists to the eras), and selected three albums: Tool's Undertow, with both CD and Vinyl releases, and Dream Theater's Train of Thought, and A Dramatic Turn of Events.

The included query in the gist returns all available information about the albums present in a single query. No n+1.

The inserts could likely be improved (for example, if you were doing these from an application, you could save IDs and then immediately reuse them; technically you could do that in pl/pgsql, but ugh), but they do work.

This is also set up to model books in much the same way, but I didn't add any.

> A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution.

Joins are not a "hack," they are an integral part of the relational model.

[0]: https://gist.github.com/stephanGarland/ec2d0f0bb54161898df66...

Re: PostgreSQL is enough

#256

Post by sgarland » Tue, Feb 06, 2024, 10:26 PM UTC

SJC_Hacker wrote: post #208

sgarland wrote: post #140

Earlier quoted context omitted.

> But a relational DB isn't right for every workload. While sometimes true, I'll counter that it's more common that the application was not truly designed for a relational DB, and instead was designed for reading and storing JSON.

You can store JSON (since 9x) and JSONB in PostgresQL (since 9.4 back in 2014)

I'm very aware of this fact. The issue is that by definition, JSON (or any other non-scalar) cannot be in normalized form, and so what you end up with is devs using RDBMS as a K/V store that has a few joins thrown in.

JSON performance (or JSONB, it really doesn't matter) is abysmal compared to more traditional column types, especially in Postgres due to TOAST.

Properly normalized tables (ideally out to 5NF, but at least 3NF) are what RDBMS were designed to deal with, and are how you don't wind up with shit performance and referential integrity issues.

Re: PostgreSQL is enough

#257

Post by sgarland » Tue, Feb 06, 2024, 10:28 PM UTC

mcdonje wrote: post #244

SJC_Hacker wrote: post #208

Earlier quoted context omitted.

You can store JSON (since 9x) and JSONB in PostgresQL (since 9.4 back in 2014)

imo, the value in that is interop with relational data. If you're primarily working with json, it's probably best to use a document db.

Yes. I don't mind the occasional JSON[B] column when it makes sense, but it should not be used as an excuse to not design a good schema. In general, if you find yourself trying to fit a non-scalar value into RDBMS, you should reconsider the data model.

Re: PostgreSQL is enough

#258

Post by jurschreuder » Tue, Feb 06, 2024, 10:42 PM UTC

The thing I'm missing in PG is jsonb compression the way mongo does it.

We have both Mongo and PG. PG is much simpler and I would love to ditch Mongo for simplicity.

Only thing I would need is a "dumb" compressed jsonb column. No updates, no queries, only insert, select, delete.

And the same 80-90% compression without maintenance as Mongo on highly repetitive json keys.

Re: PostgreSQL is enough

#259

Post by Ensorceled » Tue, Feb 06, 2024, 10:53 PM UTC

randomdata wrote: post #228

Ensorceled wrote: post #216

Earlier quoted context omitted.

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

> Who gives a shit? [...] I'm not sure what you are trying to add here.

I guess nothing. You must be fun at design reviews.

Re: PostgreSQL is enough

#260

Post by randomdata » Tue, Feb 06, 2024, 10:58 PM UTC

sgarland wrote: post #255

randomdata wrote: post #138

Earlier quoted context omitted.

> 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. I don't know how precisely strict you expect a tree to be in RDBMS, but this [0] is as close as I can get. It has a hie…

> Joins are not a "hack," they are an integral part of the relational model.

Yes, joins are an essential part of the relational model, but we're clearly not talking about the relational model. The n+1 problem rears its ugly head when you don't have a relational model – when you have a tree-like model instead.

> The included query in the gist returns all available information about the albums present in a single query. No n+1.

No n+1, but then you're stuck with tables/relations, which are decidedly not in a tree-like shape.

You can move database logic into your application to turn tables into trees, but then you have a whole lot of extra complexity to contend with. Needlessly so in the typical case since you can just use SQLite instead... Unless you have a really strong case otherwise, it's best to leave database work for databases. After all, if you want your application to do the database work, what do you need SQLite or Postgres for?

Of course, as always, tradeoffs have to be made. Sometimes it is better to put database logic in your application to make gains elsewhere. But for the typical greenfield application that hasn't even proven that users want to use it yet, added complexity in the application layer is probably not a good trade. At least not in the typical case.