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

261–270 of 323 posts

Re: PostgreSQL is enough

#261

Post by cpursley »

cpursley wrote: post #241
maxbond wrote: post #219

Earlier quoted context omitted.

What's the drawback to a trigger? I would think that any overhead you recouped by avoiding a trigger would be offset by the overhead of sending the entire WAL to your listener, rather than the minimized subset of events that listener is interested in. (To be clear I do see other downsides to listen/notify and I think WalEx makes a lot of sense, I just don't understand this particular example.)

You don’t send the entire WAL, just what you subscribe to - and you can even filter via SQL: https://github.com/cpursley/walex?tab=readme-ov-file#publica...

This post describes some of the other issues with listen/notify trigger approach: https://news.ycombinator.com/item?id=36323698

Re: PostgreSQL is enough

#262

Post by sgarland »

randomdata wrote: post #260
sgarland wrote: post #255

Earlier quoted context omitted.

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

n+1 can show up any time you have poorly modeled schema or queries. It’s quite possible to have a relational model that is sub-optimal; reference the fact that there are 5 levels of normalization (plus a couple extra) before you get into absurdity.

I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. Do you have an example schema?

Re: PostgreSQL is enough

#263

Post by cpursley »

wg0 wrote: post #27
benrutter wrote: post #8

On the [Simplify: move code into databases]( https://sive.rs/pg ), has anyone actually tried this? My instinct is that the output won't be simpler at all, but a big twisty web of varying parts and competing use cases, all extremely hard to discover. To me, having two things that do something distinct is simpler, and ironically, Rich Hickey says this exact thing in Simplicity Matters, which is quoted in the article.

One caveat is when you have to upgrade across major database versions. The more your application utilises these features, the more you're likely to hit when next major version upgrade has to happen. Not that major database versions are any simpler even if you just stick to CRUD for databases.

This is also true of upgrading dependencies in the application layer.

Re: PostgreSQL is enough

#264

Post by LaGrange »

cpursley wrote: post #247
LaGrange wrote: post #174

I'm gonna go ahead and make a half-serious half-joking hot take: Actually you should be using an embedded database. Half-joking because there's like, tons of infrastructure, both literal and theoretical, that you're gonna miss out, and because I'm not sure we have on-disk standards (so less "good software" than "popular software") other than sqlite and libdb, both of which have some issues that make me hesitate befor…

Are you familiar with Erlang's ETS? It's pretty neato: https://elixirschool.com/en/lessons/storage/ets

No, but Erlang having something like that seems very in-character.

Re: PostgreSQL is enough

#265

Post by smitty1e »

cbreezyyall wrote: post #251
cnity wrote: post #142

Earlier quoted context omitted.

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.

RDD leaves serious wreckage in its wake.

Re: PostgreSQL is enough

#266

Post by agumonkey »

cpursley wrote: post #249
agumonkey wrote: post #172

Earlier quoted context omitted.

The more I do fullstack work the more I see an obesity crisis. I under the need to modularize (I dearly think I do) but god you have relational model, reimplemented in your framework, reencoded as a middleware to handle url parsing, the one more layer to help integrate things client side. I find that insane. And Postgrest was a refreshing idea.

Seriously. There's like 7000 duplicates of the very same data layer in a single stack: database, back-end ORM/data mapper, front end and various caching things in between. Things like PostgREST and Hasura area great pared with fluent clients.

And then there's the failed microservice case.. what some people describe a distributed monolith where data has to be passed around through every layer, with domain logic replicated here and there.

Re: PostgreSQL is enough

#267

Post by int_19h »

emseetech wrote: post #232
Ensorceled wrote: post #218

Earlier quoted context omitted.

Anything with real customer data in the cloud? Certainly you need replication.

Litestream can handle realtime replication. But most projects don’t even have customers when they start, let alone large quantities of their data and legal requirements for guaranteed availability.

I think it's reasonable for a business, even a freshly starting one, to expect to grow to the point where it does have enough customers to outgrow SQLite fairly soon. Between that and PG offering more features in general (which often simplifies app code, because you can do more with queries), it's still not clear why not start with PG in the first place.

PG, on the other hand, can scale enough to cover foreseeable future needs for most businesses, so aiming for something more complex than that is almost always premature.

Re: PostgreSQL is enough

#268

Post by int_19h »

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…

If you need to query over a tree data structure, then that's what WITH RECURSIVE is for, and it's present in both DBMS.

If you additionally need the result of that query to be hierarchical itself, then you can easily have PG generate JSON for you.

Re: PostgreSQL is enough

#269

Post by int_19h »

vhcr wrote: post #134
bun_terminator wrote: post #113

Earlier quoted context omitted.

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

Your experience with databases was probably clouded by having to use Oracle.

It could be worse; there's always DB2...

Re: PostgreSQL is enough

#270

Post by emseetech »

int_19h wrote: post #267
emseetech wrote: post #232

Earlier quoted context omitted.

Litestream can handle realtime replication. But most projects don’t even have customers when they start, let alone large quantities of their data and legal requirements for guaranteed availability.

I think it's reasonable for a business, even a freshly starting one, to expect to grow to the point where it does have enough customers to outgrow SQLite fairly soon. Between that and PG offering more features in general (which often simplifies app code, because you can do more with queries), it's still not clear why not start with PG in the first place. PG, on the other hand, can scale enough to cover foreseeable fu…

> outgrow SQLite fairly soon

That would be the result of either vastly overestimating their business plan or vastly underestimating SQLite.

Post reply on HN