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

281–290 of 323 posts

Re: PostgreSQL is enough

#281

Post by dventimi »

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…

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

A trivial amount of lateral joins plus JSON aggregates will give you a relation with on record, containing a nested JSON value with a perfectly adequate tree structure, with perfectly adequate performance, in databases that support these operations.

There are solutions to these problems. One only needs to willingness to accept them.

Re: PostgreSQL is enough

#283

Post by dventimi »

randomdata wrote: post #272
sgarland wrote: post #262

Earlier quoted context omitted.

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?

> I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. That's simple: Not being an RDMBS, only an engine, is how it avoids the suffering. The n+1 problem is the result of slow execution. Of course, an idealize database has no time constraints, but the real world is not so kind. While SQLite has not figured out how to defy the laws of physics, it is able to reduce the time t…

Compare and contrast the query execution stages of PostgreSQL and SQLite. How exactly do they work? Please be as precise as possible. Try to avoid imprecise terms like "simple", "suffering", "system layer", and "hack."

Re: PostgreSQL is enough

#284

Post by randomdata »

dventimi wrote: post #283
randomdata wrote: post #272

Earlier quoted context omitted.

> I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. That's simple: Not being an RDMBS, only an engine, is how it avoids the suffering. The n+1 problem is the result of slow execution. Of course, an idealize database has no time constraints, but the real world is not so kind. While SQLite has not figured out how to defy the laws of physics, it is able to reduce the time t…

Compare and contrast the query execution stages of PostgreSQL and SQLite. How exactly do they work? Please be as precise as possible. Try to avoid imprecise terms like "simple", "suffering", "system layer", and "hack."

For what purpose? I can find no source of value in your request.

Re: PostgreSQL is enough

#285

Post by dventimi »

randomdata wrote: post #165
turnsout wrote: post #153

Earlier quoted context omitted.

You can of course use a vanilla database, read every row and just roll your own vector distance function, but it's just frustrating that there isn't a standardized pattern for this. There are plenty of proprietary databases and APIs, but now you're taking on a dependency and assuming a certain amount of risk.

> it's just frustrating that there isn't a standardized pattern for this. Be the change you want to see, I suppose. No doubt convergence will come, but it is still early days. Six months ago, most developers didn't even know what a vector database is, let alone consider it something to add to their stack. It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. Even PostgreS…

> It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying.

IBM had SQL in their database product in 1981, Oracle had it by v4 in 1984, ANSI picked SQL as its standard that same year, and completed the first version by 1986.

Some time scientists say that the 1980s occurred before "well into the 1990s" but I mean, who can really say, right?

Re: PostgreSQL is enough

#286

Post by dventimi »

randomdata wrote: post #284
dventimi wrote: post #283

Earlier quoted context omitted.

Compare and contrast the query execution stages of PostgreSQL and SQLite. How exactly do they work? Please be as precise as possible. Try to avoid imprecise terms like "simple", "suffering", "system layer", and "hack."

For what purpose? I can find no source of value in your request.

To educate your adoring fans

Re: PostgreSQL is enough

#288

Post by bun_terminator »

edwardsdl wrote: post #274
bun_terminator wrote: post #273

Earlier quoted context omitted.

I mean you do a job because of the pay, not because you like the people or they are particularly competent. So it's a good gig for two years or so

I’ve found that surrounding myself with competent people - or better yet, people far better than me - has lead me to much better paying jobs.

It also makes you look less like a genius haha. Yeah maybe you're right. It wasn't the best choice in hindsight. But switching jobs is a relatively rare thing in Europe. You only can do that ever so often, so I'll have a bit of waiting to do

Re: PostgreSQL is enough

#289

Post by cnity »

spothedog1 wrote: post #206
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…

Can you expand on Postgres vs Graph Databases?

It is easy to represent a graph in Postgres using edge and node tables. For the use case we have, it is more performant to query such a setup for many millions of relationships vs using the big names in graph databases.

You just need a little bit of appropriate index selection and ability to read the output of EXPLAIN ANALYZE to do so.

There are probably use cases where this doesn't hold, but I found in general that it is beneficial to stick to Postgres for this, especially if you want some ability to query using relations.

Post reply on HN