PostgreSQL is enough
71–80 of 323 posts
Re: PostgreSQL is enough
#72Earlier quoted context omitted.
Because it's incorrect. If you have any non-postgres side-effect, you can't have exactly-once (unless you do 2PC or something like that). There isn't any technology that gives you "exactly once" in the general case.
That's not how exactly once is defined for queue. We are talking about semantics of what queue systems is providing.
Nobody will understand it like that.
Re: PostgreSQL is enough
#73I often go down rabbit holes like this, trying to collapse and simplify the application stack. But inevitably, as an application grows in complexity, you start to realize _why_ there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable. That s…
I think a lot of the industry struggles with the idea that maybe there is no "one size fits all", and what makes sense when you're a one person company with 100 customer probably doesn't make sense when you're a 1000 people company with millions of customers. If you use a stack meant for a huge userbase (with all the tradeoffs that comes with it) but you're still trying to find market fit, you're in for a disappointm…
> It's OK to make a choice in the beginning based on the current context and environment, and then change when it no longer makes sense.
Yep. And Postgres is a really good choice to start with. Plenty of people won't outgrow it. Those who do find it's not meeting some need will, by the time they need to replace it, have a really good understanding of what that replacement looks like in detail, rather than just some hand-wavy "web scale".
Re: PostgreSQL is enough
#74I often go down rabbit holes like this, trying to collapse and simplify the application stack. But inevitably, as an application grows in complexity, you start to realize _why_ there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable. That s…
PG works really well as a message queue and there's several excellent implementations on top of it. Most systems are still going to need Redis involved just as a coordinator for other pub/sub related work unless you're using a stack that can handle it some other way (looking at BEAM here). But there are always going to be scenarios as an application grows where you'll find a need to scale specific pieces. Otherwise t…
Worth noting that Postgres has a pubsub implementation built in: listen/notify.
Re: PostgreSQL is enough
#75How about metrics? Would it be possible to do this in Postgres as well? I'm talking e.g. about simple counters that are not directly vital to business logic. I'm currently using Redis for those things as I'm thinking that Postgres seems to be the wrong choice for this kind of data, would anyone disagree?
You can use the TimescaleDB PostgreSQL extension. It works really well and has a high compression rate for time series data, such as metrics. It can also downsample data. I have also tried InfluxDB, but you have to rewire your mental model to fit InfluxDB (it has its own query syntax and internal mechanism). They have versions v1, v2, and v3, and it's not clear which one to use, probably v2 at this point in time. It's kind of confusing and the state of development is very much in flux.
I think TimescaleDB works well up to the single-digit terabyte range (according to various sources). If you need a solution in the multi-digit terabyte or petabyte range, then you probably need something like a distributed VictoriaMetrics setup.
Re: PostgreSQL is enough
#76Is anyone aware of a streams implementation for Postgresql? I see we can do simple queues, but how about a stream with multiple consumers / consumer groups (much like the Redis streams implementation)?
Re: PostgreSQL is enough
#77This 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.
> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%? I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?
Practically, because sqlite is good enough for one machine and compatible-enough with postgresql that you can use either pretty easily. One thing I wrote was an exactly-once stream processor that fetched events from a lot of remote systems for processing. Transaction-based queue in the DB to achieve exactly-once with recovery (remote systems accepted time-stamp resyncing of the stream of events). It works fine at small scale on a single machine for design and testing (local integration tests with short startup time are very valuable) but trivially scales to hundreds of workers if pointed at a postgres instance. The work to allow sqlite vs postgres was a single factory that returned a DB connection in Go based on runtime configuration.
It's also good practice for designing reasonably cross-database compatible schemas.
Re: PostgreSQL is enough
#78I often go down rabbit holes like this, trying to collapse and simplify the application stack. But inevitably, as an application grows in complexity, you start to realize _why_ there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable. That s…
My saying has always been: be nice to the DB
Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your stack.
Re: PostgreSQL is enough
#79I often go down rabbit holes like this, trying to collapse and simplify the application stack. But inevitably, as an application grows in complexity, you start to realize _why_ there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable. That s…
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 these decisions get made. (And both worked: the PG queue was never grown out of, and generally SQS was easy to work with & reliable.)
But what I've also seen is "Let's introduce bespoke tech that nobody on the team, including the person introducing it, has experience in, for a queue that isn't even the main focus of what we're building" — this I'm less fine with. There needs to be a solid reason why we're doing that, and that we're going to get some real benefit, vs. something that the team does have experience in, like SQS or PG. Instead, this … thing … crashes on the regular, uses its own bespoke terminology, and you find out the documentation is … very empty. This does not make for a happy SRE.
Re: PostgreSQL is enough
#80There is https://github.com/pipelinedb/pipelinedb for streaming time series data, but it is abandoned since 6 years.
There is now https://github.com/paradedb/paradedb/tree/dev/pg_analytics (disclaimer: I am one of the makers of pg_analytics)