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

231–240 of 323 posts

Re: PostgreSQL is enough

#231

Post by emseetech »

neovim_btw wrote: post #227
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 o…

It’s effortless to get 2-4K writes per second with SQLite on cheap commodity hardware.

That will carry most early stage applications really far.

Re: PostgreSQL is enough

#232

Post by emseetech »

Ensorceled wrote: post #218
emseetech wrote: post #204

Earlier quoted context omitted.

> Needs to be accessible, with redundancy, across multiple AWS zones How many projects start with these requirements?

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.

Re: PostgreSQL is enough

#233

Post by bun_terminator »

ndriscoll wrote: post #164
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…

It gives you an easy, high-level way to use high performance data-structures and algorithms. You don't need to explicitly write or rewrite code to maintain hash maps or b-trees or whatever and to use the right structures for fast lookups from one set of data to another. You just say "CREATE INDEX name ON table USING HASH(column)", and from then on, your hash map will maintain itself, and any lookups that would benefi…

It's pretty opaque to me. If something in my cpp code is dodgy or runs slow, I can use a number of debugging and profiling tools. While I don't really even know how databases work on the insides, let alone profile or diagnose them. To this day my colleagues rewrite equivalent SQL statements because some run better than others. And we regularly run into unexpected latency spikes where most of the time a statement runs fine, but even nth time it's several magnitudes of times slower - and no one knows why. So we cobble code and caches around things. It all seems pretty mind boggling to me.

Re: PostgreSQL is enough

#234

Post by hot_gril »

Chiron1991 wrote: post #185
hot_gril wrote: post #178

I use Postgres for a lot, but I can't imagine using it to make HTTP requests.

https://github.com/pramsey/pgsql-http

That's what I was referring to, wondering if anyone uses it and why. It even says "Why This is a Bad Idea" at the bottom.

Maybe for databases queried directly by users who also need to mix in API responses, and you're wrapping this all up in triggers or some other stored procedure cause you really want to use Postgres without some controller written in Python or JS.

Re: PostgreSQL is enough

#235

Post by asah »

ndriscoll wrote: post #123
chasd00 wrote: post #99

Earlier quoted context omitted.

what is "at scale"? Is there a specific metric or range of metrics that raises a flag to begin considering something else? For example, in the olden days when it was my problem, page load times were the metric. Once it got high enough you looked for the bottleneck, solved it, and waited. When the threshold was broken again you re-ran the same process. Is there an equivalent for postgres?

This bugs me every time performance comes up. No one is ever concrete, so they can never be wrong. If Michael Jackson rose from the dead to host the Olympics opening ceremony and there were 2B tweets/second about it, then postgres on a single server isn't going to scale. A crud app with 5-digit requests/second? It can do that. I'm sure it can do a lot more, but I've only ever played with performance tuning on weak ha…

minor nit: 9K TPS for Visa are business transactions - TBD how many database transactions are generated...

(still, modern postgresql can easily scale to 10,000s (plural) of TPS on a single big server, especially if you setup read replicas for reporting)

Re: PostgreSQL is enough

#236

Post by saltcured »

SJC_Hacker wrote: post #205
Ensorceled wrote: post #192

Earlier quoted context omitted.

I guess that's really my point here. They difference in setup time is negligible so I'm not sure why people keep bringing it up as a reason to choose sqlite over PostgreSQL. For instance, "deployable inside a customer application" is an actual requirement that would make me loath to pick PostgreSQL. "Needs to be accessible, with redundancy, across multiple AWS zones" would make me very reluctant to pick sqlite. Neith…

I'm not sure its neglibile, I suppose once you know what you're doing. But postgres setup, at least the package managers on Linux, will by default, create a user called postgres, and lock out anyone else who isn't this user from doing anything. Yeah you can sudo to get psql etc. easily, but that doesn't help your programs which are running as different users. You have to edit a config file to get to work, and I never…

That's interesting... my experience (almost all on RHEL/CentOS/Fedora) is that it is trivial to have unix domain socket with local Postgres clients and a pain to setup any remote clients.

You just have to call the basic "createuser" CLI (or equivalent CREATE ROLE SQL) out of the postgres superuser account to create database users that match local Linux usernames. Then the ident-based authentication matches the client process username to the database role of the same name.

Re: PostgreSQL is enough

#237

Post by Too »

dagss wrote: post #230
Too wrote: post #150

Earlier quoted context omitted.

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

Yes. Aggregations and search are often best done as close to the data as possible, in the DB.

Rendering html, caching, parsing api responses, sending emails, background jobs: Nope.

Basically, use the database for what it’s good at, no more.

Re: PostgreSQL is enough

#238

Post by tmountain »

0xbadcafebee wrote: post #207
adlpz wrote: post #5

Love postgres and use it extensively. However, there's always an issue when I start doing the more advanced stuff: how do I combine that with all my years of experience with version control, code reviews, types, tests, static analysis and all the niceties of coding in general? Migrations?

RDBMS's are still very much an 80s thing. Great at dynamic queries, horrible at managing changes in an immutable, atomic, versioned way. The structure and relationship of data is so brittle it's kind of crazy it's still used. Probably it's because so many people have grown dependent on its particular flaws; the incumbent just lumbers on.

There’s always a trade off though. Append-only storage models are great for use cases where they are truly necessary, but they also store substantially more data than mutable databases do, and designing for something like Datomic carries a heavier cognitive cost than a simple RDBMS.

Re: PostgreSQL is enough

#240

Post by nyrikki »

beagle3 wrote: post #212
kabes wrote: post #196

Earlier quoted context omitted.

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

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.

Choosing a default product to optimize for delivery is fine, claiming that one product fits all needs is not.

Psql does have limits when being used as a message or event bus, but it can be low risk if you prepare the system to change if/when you hit those limits.

Letting ACID concepts leak into the code is what tends to back organisations into a corner that is hard to get out of.

Obviously that isn't the Kool aid this site is selling. With this advice being particularly destructive unless you are intentionally building a monolith.

"Simplify: move code into database functions"

At least for any system that needs to grow.

Post reply on HN