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

211–220 of 323 posts

Re: PostgreSQL is enough

#211

Post by gen220 »

jimbokun wrote: post #156
philippemnoel wrote: post #84

I'm one of the makers of ParadeDB, a modern alternative to Elasticsearch. We build Postgres extensions to do fast search (pg_bm25) and analytics (pg_analytics). I love Postgres. If you have a small workload, like a startup, it certainly makes sense to stay within Postgres as long as you can. The problem is, at scale, Postgres isn't the answer to everything. Each of the workloads one can put in Postgres start to grow…

For scaling, has anyone here used hash based partitioning to scale horizontally? In principle, seems like it should work to allow large scale distribution across many servers. But the actual management of replicas and deciding which servers to place partitions, redistributing when new servers are added, etc. could lead to a massive amount of operational overhead.

As other sibling comments noted, Citus does this pretty well. Recommend reading through their docs and use-cases. There's some migration/setup costs, but once you have a good configuration, it mostly just-works.

Main downside is that you either have to either self-manage the deployment in AWS EC2 or use Azure's AWS-RDS-equivalent (CitusData was acquired by MS years ago).

FWIW, I've heard that people using Azure's solution are pretty satisfied with it, but if you're 100% on AWS going outside that fold at all might be a con for you.

Re: PostgreSQL is enough

#212

Post by beagle3 »

kabes wrote: post #196
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…

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, in retrospect. But there have been many who would have cost 100% more.

Re: PostgreSQL is enough

#213

Post by gen220 »

chasd00 wrote: post #99
philippemnoel wrote: post #84

I'm one of the makers of ParadeDB, a modern alternative to Elasticsearch. We build Postgres extensions to do fast search (pg_bm25) and analytics (pg_analytics). I love Postgres. If you have a small workload, like a startup, it certainly makes sense to stay within Postgres as long as you can. The problem is, at scale, Postgres isn't the answer to everything. Each of the workloads one can put in Postgres start to grow…

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?

The truth is that it really depends on your application work load. Is it read-heavy, or write-heavy? Are the reads more lookup-heavy (i.e. give me this one user's content), or OLAP heavy (i.e. `group by`'s aggregating millions of rows)? Is read-after-write an important problem in your application? Do you need to support real-time/"online" updates? Does your OLAP data need to be mutable, or can it be immutable (and therefore compressed, columnarized, etc.)? Is your schema static or dynamic, to what degree?

I agree with others that a good simplification is "how far can you get with the biggest single AWS instance"? And the answer is really far, for many common values of the above variables.

That being said, if your work load is more OLAP than OLTP, and especially if your workload needs to be real-time, Postgres will begin to give you suboptimal performance without maxing-out i/o and memory usage. Hence, "it really depends on your workload", and hence why you see it's common to "pair" Postgres with technologies like Clickhouse (OLAP, immutable, real-time), RabbitMQ/Kafka/Redis (real-time, write-heavy, persistence secondary to throughput).

Re: PostgreSQL is enough

#214

Post by gen220 »

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?

You might be curious to look up how Alembic and Django accomplish migrations. Even if Python's not your jam, the concepts/semantics are language/framework agnostic.

Basically, you store each migration in a file, and you "squish" the migration history down to a table definition once you've decided you're happy with the change and it's been affected across all the different deployment environments.

It's not perfect, but it works reasonably well.

Re: PostgreSQL is enough

#215

Post by gen220 »

throwaway918274 wrote: post #46

My Trifecta is: Postgres, Redis, S3 Hasn't steered my wrong yet. Every once in a while I'm tempted to try to use Postgres for Pub/Sub but then I realize that I need Redis for caching and sidekiq anyways, and Redis is amazing too, so why bother.

Have you had to deal with read-heavy/OLAP queries? Of the kind that can't be cached effectively (i.e. arbitrary filters).

Re: PostgreSQL is enough

#216

Post by Ensorceled »

randomdata wrote: post #198
Ensorceled wrote: post #193

Earlier quoted context omitted.

We are still dealing with the fact that SQLite still HAS the n+1 "problem", it's just fast enough that it doesn't suffer from it. It's a very important distinction because, as you say, there are problem domains where you can't just "join the problem away".

Are we? "Suffer" was the word used right from the beginning for good reason. Computers aren't magic. I find no importance in pointing out that fact. Presumably everyone here already knows that. And if it turns out that they don't, who cares? That's their problem. Makes no difference to me.

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.

Re: PostgreSQL is enough

#217

Post by jamwil »

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

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.

Re: PostgreSQL is enough

#218

Post by Ensorceled »

emseetech wrote: post #204
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…

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

Re: PostgreSQL is enough

#219

Post by maxbond »

cpursley wrote: post #143
dfee wrote: post #74

Earlier quoted context omitted.

Worth noting that Postgres has a pubsub implementation built in: listen/notify. https://www.postgresql.org/docs/current/sql-notify.html

Yep, I should add that. One of the libraries in my list (that I maintain) is WalEx: https://github.com/cpursley/walex/issues It subscribes to the Postgres WAL and let you do the same sort of thing you can do with listen/notify, but without the drawbacks like need for triggers or character limits.

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

Re: PostgreSQL is enough

#220

Post by mihaic »

Almost every single time I've seen it was better to first prototype a new feature with the existing stack rather than introduce something new. And by careful curation, that initial prototype could be turned into production code with the same stack as well. At some point though the system does creek, and you're starting to wish you'd have a Redis or another specialized tool.

The only important thing though for me is making sure I'd spend some time on writing a nice API wrapper on my end, and only call that. When I really need to use Redis, all that should be needed is changing the implementation inside your wrappers, and testing the migration very well.

People are surprised how long you can delay to make a technical decision, until it becomes obvious.

Post reply on HN