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

131–140 of 323 posts

Re: PostgreSQL is enough

#131

Post by sgarland »

cyrialize wrote: post #26

I love postgres - although there are some things I dislike about it - mainly due to me using mysql for a years. All dislikes are mainly syntactical things, like having USING in deletes instead of JOINs. I also liked the output of EXPLAIN more in mysql. Using postgres made me realize that many of the people I work with had no idea what an ambiguous group by is, because unlike mysql, postgres will never, ever let you d…

While I love MySQL for a variety of reasons (and think it's legitimately better for certain features – tangential to your point, being able to ORDER BY in a DELETE is great), its lax GROUP BY option isn't really a great idea in terms of correctness.

You can emulate it in Postgres with DISTINCT ON if desired, and as long as you also ORDER BY something that makes sense for your query, it should work similarly.

Re: PostgreSQL is enough

#132

Post by fuy »

williamdclt wrote: post #56
wg0 wrote: post #21

Earlier quoted context omitted.

I think SQS is cheap enough to build on as a messaging queue even if you're not hosting within AWS. Out of the widely underrated AWS services include SNS and SES and they are not a bad choice even if you're not using AWS for compute and storage.

The problem is rarely cost, it's operational overhead. Using SQS for a queue rather than my already-existing Postgres means that I have to: - Write a whole bunch of IaC, figuring out the correct access policies - Set up monitoring: figure out how to monitor, write some more IaC - Worry about access control: I just increased the attack surface of my application - Wire it up in my application so that I can connect to S…

+. And then you have to figure everything one more time when you decide to move to (or to add support for) Azure/GCP.

Re: PostgreSQL is enough

#134

Post by vhcr »

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

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

Re: PostgreSQL is enough

#136

Post by sgarland »

api wrote: post #61

No it's not, because it's very hard to set up in clustered HA environments. This is 2024. It should be possible to just add database nodes.

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/

Re: PostgreSQL is enough

#137

Post by hobs »

bun_terminator wrote: post #125
bsdpufferfish wrote: post #118

Earlier quoted context omitted.

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…

The problems are not specific to SQL though - if you want to store and manage huge amounts of data you're going to need some specialized data structures, some way to manage disks paging and memory, manage consistency, isolation, durability, and atomicity of your changes.

The general idea is that its such a PITA to deal with all that so just let a database do it, and of course the consequence is an often leaky abstraction because computers suck.

Re: PostgreSQL is enough

#138

Post by randomdata »

sgarland wrote: post #129
randomdata wrote: post #92

Earlier quoted context omitted.

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 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 you try to build those types of structures from tables/relations.

A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution. Given an idealized database, many queries is the proper solution to the problem. Of course, an idealized database doesn't exist, so we have to deal with the constraints of reality. This, in the case of Postgres, means moving database logic into the application. But that complicates the application significantly, having to take on the role that the database should be playing.

But as far as SQLite goes, for all practical purposes you can think of it as an ideal database as it pertains to this particular issue. This means you don't have to move that database logic into your application, simplifying things greatly.

Of course, SQLite certainly isn't ideal in every way. Tradeoffs, as always. But as far as picking the tradeoffs you are willing to accept for the typical "MVP", SQLite chooses some pretty good defaults.

Re: PostgreSQL is enough

#139

Post by hobs »

bun_terminator wrote: post #97
acaloiar wrote: post #93

Earlier quoted context omitted.

It's a decent question. I believe there's a simple answer that explains a large part of the reason people choose databases over flat files. People want ACID compliance _and_ data distribution, and databases are traditionally the only things that provide both.

I have never heard the term ACID so I don't believe that's a reason for us at least

Just because you haven't heard it doesn't mean it's not a fundamentally important part of storing data :)

Re: PostgreSQL is enough

#140

Post by sgarland »

mcdonje wrote: post #10

Obligatory "depends". I'm aware of more than one company that uses postgres for huge datasets serving workflows with high throughput and importance. It's truly a workhorse, it's comparatively a pleasure to work with, and its extensibility makes it useful in places some other DBs aren't suitable for. But a relational DB isn't right for every workload.

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

Post reply on HN