Using Postgres for Everything

https://www.timescale.com/blog/how-to-collapse-your-stack-using-postgresql-for-everything/

by rmason • 2 years ago

155 82 2 years ago

timescale.com

Grimeton• 2 years ago

"Your Scientists Were So Preoccupied With Whether Or Not They Could, They Didn’t Stop To Think If They Should"

-- Ian Malcolm

SavageBeast3• 2 years ago

And run it all on a single piece of big iron:

https://thmsmlr.com/cheap-infra

I'm not joking either.

hobs• 2 years ago

It's mostly right, but the problem lies in when the requests per second are not average. Even then most people just do some unscalable stuff on the cloud for minimum 10x the cost.

ndriscoll3• 2 years ago

Where big iron might be something between a raspberry pi with an NVMe hat and a gaming PC.

My 6th gen i5 (4 cores) can render ~15k page views per second with database queries, or several times that serving cached pages from nginx. 150 requests/second wouldn't be noticeable if I were using it for gaming at the same time.

SavageBeast3• 2 years ago

Thanks for the metric - I wouldn't have naively assumed that kind of through put was possible on such a setup.

kgeist2• 2 years ago

Another team's product at our company runs inside a Kubernetes cluster which consists of lots of microservices. CPU's are at 80% all the time. Redis, RabbitMQ, MySQL, Elastic Search etc. There's no number crunching, mostly CRUD with some business logic on top. It requires a separate operations team to manage it (otherwise this goldberg machine easily explodes).

A few days ago I asked our ops team, what's our rps? ~500. Out of curiosity, I ran a few benchmarks to stress test my monolithic pet project in Go with a single DB for everything and it easily handled 15k/s on my home computer, too, without any special optimizations. I understand its load is totally different from our production server, and my benchmark is most likely flawed, but the order of magnitude of difference is interesting.

I was told the production cluster also has additional 500 rps just for communication between the microservices. I suspect there's just too much overhead in their setup: communication between services, serialization of data between different DBs, etc. If it was a single modular monolith with a single DB, I suspect it could be faster and easier to maintain... Another issue is that they also sell it as an on-premise solution. And with the complexity growing, it now often fails to run on clients' infra.

vineyardmike2• 2 years ago

Where big iron might be something between a raspberry pi with an NVMe hat and a gaming PC.

At my last startup (with ~3 technical employees), I was talking to the CTO about our cloud spend and planning for properly scaling infra after we got our first customer. We weren't "a SaaS website", but (hand-waving) we did complex physics modeling and analytics on data streams. The (physicist) CTO had heard of k8s and cloud stuff and wanted me to investigate how we should scale our cloud. He was convinced it'd be expensive to do "all those physics calculation with 64 bit numbers".

I showed him our entire cloud - gateway, database(s), physics modeling, metrics collection, log aggregation, etc - running at 1000x estimated 1-customer load from a MacBook. I offered to set him up a personal raspberry pi to stress test before a k8s cluster.

We ended up with a sensible single medium EC2 instance running everything, with some extra stuff for fail-over. AFAIK the only change made after I left was using a cloud-vendor DB.

tkiolp42• 2 years ago

I agree with everything said there except one thing: high availability is difficult to achieve with a single box. So, I would use two.

ffsm8• 2 years ago

I will have to disagree with your disagreement.

High availability is not difficult to achieve with a single box, it's just depending on luck

I've had boxes run with 100% availability for multiple years

gymbeaux2• 2 years ago

The industry really has become bloated. Now we have DevOps and Agile Coaches and Cloud Engineers and microservices and JavaScript Everywhere(TM).

It’s all bullshit. DevOps is really so complex that a medium-sized company needs an entire team?

Earlier today I was trying to set up some elaborate Postgres database backup and couldn’t get it to work so I just put the command in the cron tab and called it a day. Took 1 minute.

vineyardmike2• 2 years ago

DevOps is really so complex that a medium-sized company needs an entire team?

Depends on your goals, but most people probably don't need it.

I just put the command in the cron tab and called it a day

Obviously this will lead to data loss if any data is written and then a corruption event or issue occurs in-between cron events. The road to over-complicated infrastructure is paved with mistakes and outages. The home-lab server running on a pi in my closet can use cron jobs and scripts, but at work we maintain higher standards because our data loss would cost more than a few extra days of engineer time.

Everything should be as simple as possible, but let's not pretend everyone is a chump because their engineering tolerances don't allow for data loss.

cpursley2• 2 years ago

Related thread: "PostgreSQL is enough"

https://news.ycombinator.com/item?id=39273954

dangoodmanUT2• 2 years ago

On the other hand, this is backing companies into corners that are dangerously hard to get out of.

dangoodmanUT2• 2 years ago

And on the third hand, Timescale is not advised to be your production OLTP if you're using Timescale cloud, so it's a strange double-standard that they are communicating here since they cluster totally differently than normal OLTP postgres workloads? Or maybe I skimmed it to fast and haven't used Timescale in a while

akulkarni3• 2 years ago

(Timescale co-founder)

We actually have 100s of customers who use Timescale for their production time-series _and_ OLTP workloads :-)

sophacles• 2 years ago

Which corners, and what dangers?

VeejayRampay• 2 years ago

those corners do not exist, but people love yo create artificial needs to justify unneeded complexity

monero-xmr• 2 years ago

I don’t know about timescale specifically but using Postgres for everything is the safest thing you can possibly do as a startup.

smt882• 2 years ago

Postgres is the only RDBMS that is not backing companies into corners because so many products are forks of it or "speak" Postgres. You can completely swap out your database without changing any of your code. There is no other database that lets you shop around (or upgrade to a more specialized product) like that.

vrosas2• 2 years ago

You can completely swap out your database without changing any of your code

Uhhhh in theory... maybe... not really. Switching PG out from under our relatively toy-ish SaaS product would be a huge undertaking.

whateveracct• 2 years ago

100s of engineers all coding against the same Postgres database becomes a pain. Lack of decentralization leads to silly outages due to people sharing resources they really shouldn't.

vrosas2• 2 years ago

In my experience it's really hard to prevent issues like this. RDBMS's kind of push you to keeping everything in the same database to take advantage of foreign keys and other features when in reality different data types will need their own source of record. I consider myself more experienced than most and I still fell into this trap in recent memory, failing to expect the scaling requirements for one of my tables until it was too late.

schainks• 2 years ago

Can you say more about this? Seems like an organizational / communication problem than a technical one.

simonw2• 2 years ago

That's a great problem to hold off on tackling until you have hundreds of engineers.

from-nibly• 2 years ago

Not any more dangerous than any other technical choice.

ZitchDog3• 2 years ago

I would much rather inherit and optimize someone's postgres DB with specific scaling challenges than a prematurely optimized multi-database solution built for "web scale".

whalesalad• 2 years ago

I understand the sentiment here (and agree PSQL will get you quite far) but this post is completely devoid of any actual content/meaning, examples etc. This is a literal shitpost.

zooq_ai2• 2 years ago

Not sure why you are downvoted.

One of the biggest concerns about any DB is speed of query execution. Today a decent app/site can generate terabytes of machine data that you need to analyze. There is a massive difference in usability of a query that takes < 5 secs vs even 5 minutes. So, some benchmarks of speed vs major use cases would have been ideal.

I understand, general purpose DBs can't be the best, but I can compromise for 80%. If it's 50% or less, then I really have to question that choice.

jamesgresql4• 2 years ago

(Author here!) I'd compare performance to what you need, not to other technology.

But also, point taken. The article this comment thread describes also needs to be written.

simonw2• 2 years ago

If you're generating terabytes of analytical data you should be using a different stack for analyzing that than you do for running your production workload.

(You could still use PostgreSQL, just not the same PostgreSQL servers that are running your user-facing features.)

caleblloyd• 2 years ago

I don't understand why corporate blog posts that put memes between every other paragraph still exist. I especially can't stand the animiated gifs.

jamesgresql4• 2 years ago

Author here, this is obviously a high level post! There are many, many posts about how Postgres with different extensions can get you different things.

I didn't want to go down that path for this article because I didn't want it to end up as a sales pitch. The article you describe needs to be written, but it's a follow-up in my mind (and much more technical, for a slightly different audience).

akulkarni3• 2 years ago

(Timescale co-founder)

James did put a lot of thought into this post. I saw multiple iterations of it before it was published. I think calling it a shitpost is being unkind to him.

I think we just find that some developers don't like reading long posts, so he kept this one short :-)

But if you want something with more length/depth, here is another one we recently published:

https://www.timescale.com/blog/postgres-for-everything/

renegade-otter2• 2 years ago

Seems like it's much easier to get Postgres expertise than your esoteric "Kafka/Cassandra/Kubernetes" one.

MajimasEyepatch• 2 years ago

Kubernetes hasn’t been esoteric for at least five years at this point.

renegade-otter2• 2 years ago

Kubernetes will never stop being esoteric.

Lucasoato• 2 years ago

Why Postgres? Just use Excel, who even needs more than a few thousand rows anyway

ZitchDog3• 2 years ago

One thing that postgres does not solve very well is reactive UIs. Postgres does have listen/notify but it requires much more boilerplate and infrastructure to set up than Firebase/Firestore. For example I'd like to be able to run a query and get notified that the results have updated when any of the affected rows change.

j452• 2 years ago

Could a simplification of reactive UIs be a possible angle?

Part of me is thinking about htmx or something from alpine and wondering if it’s enough. Shoutout to livewire too.

ZitchDog3• 2 years ago

Htmx doesn't help with this problem (knowing whether the underlying data has changed in order to update the UI). It's a common problem in any kind of collaborative app.

samwillis3• 2 years ago

Solving this is high on my list with PGlite (https://github.com/electric-sql/pglite). I have a bunch of iteas/thoughts that I hope to get to at some point (IVM, streaming queries, result diffs).

When PGlite (or SQLite) is used with Electric (https://electric-sql.com/), we already provide good reactive primitives. But we hope to improve this so that where possible the full queries don't have to be re-run.

SparkyMcUnicorn• 2 years ago

I've been very happy using Hasura on top of pg.

There are several fantastic options that remove the need to manually write resolvers, set up subscriptions, solve N+1, reinvent the wheel, etc.

cpursley2• 2 years ago

Check out WalEx for this, it doesn't suffer from the character limits of listen/notify: https://github.com/cpursley/walex

jamwt• 2 years ago

Agree. If you want a fully reactive database (and not just hierarchy-based reactivity like Firebase) but want to keep your ACID + relational data modeling, check out https://convex.dev .

Plug notice: this is my company.

antirez• 2 years ago

When I was doing databases, I formulated this joke-theorem of modern computing: Whatever reasonable and widespread database/language you use to model your application, if you do things sensibly, it will work well. A corollary is that: 1. It's a lot more about taste and less about technology we may believe. 2. Good programmers can bend a non perfect tool to work well for a given use case, bad programmers will mess everything even with perfect tools, so in general it is better to spend time becoming better programmers than spend time learning all the new cool tools.

This idea, if true (and I believe it is) tells us how lucky we are compared than the past programmers. Advanced databases and languages are so advanced that they can do almost everything, even if they are not good at doing everything. And hardware and RAM is so generous today, that will mask a lot of potential issues unless you are at a big scale.

tehlike4• 2 years ago

Postgres storage engines are not mature yet to allow higher through put for insert heavy scenarios (That are not necessarily analytics focused). They also don't have a way to offload storage to cheaper storage.

Orioledb, pg_analytics, hydra are getting there, but nothing out of the box postgres yet.

Compression also nearly non-existent except for TOAST.

jamesgresql4• 2 years ago

Just out of interest what would you say a 'higher throughput' was?

tehlike4• 2 years ago

It is very application dependent but this email thread explains one of my problems

https://www.postgresql.org/message-id/315b7ce8-9d62-3817-0a9...

justanotheratom• 2 years ago

Noticed recently that Oriole joined Supabase: https://supabase.com/blog/supabase-aquires-oriole

tehlike4• 2 years ago

That's really cool - didn't know this was the case.

philippemnoel• 2 years ago

This is very much a work-in-progress in the ecosystem, though. We (ParadeDB) also have `pg_lakehouse`, which is a version of `pg_analytics` designed to work over cloud object stores (AWS S3, GCS, etc.) for "cheaper storage".

I don't know if columnar engines will make it to core Postgres (I doubt it), but I do see the work of OrioleDB making it to core someday. We've joined an "alliance", alongside the core OrioleDB folks and some people from Timescale and Yandex, to push for it.

tehlike4• 2 years ago

I have been following your work with much joy - so kudos to you.

I have also some dream that one day we will see something like rocksdb/lsm backing postgres.

gmuslera• 2 years ago

Coming from Timescale, the proposal is not to use the best kind of database for a workload, but to use one database but then multiple extensions for workloads that Postgres may or not be the best fit, moving the multiple layer one level down. And maybe not getting the best performance (database people are specially sensitive on that topic, for heavy loads at least), that may or not introduce incompatibilities, inconsistency or compromise stability.

Shaving complexity is a good goal, but is partially negated by adding it again closer to the core.

Anyway, maybe for most (in unique installations, or companies, not amount of servers dedicated) the workload won’t be heavy enough to deserve the extra complexity of handling many different database servers.

the-golden-one• 2 years ago

I’m imagining the bell curve meme with ‘I’m going to put everything in a SQL database’ at both ends.

akulkarni3• 2 years ago

You don't need to imagine ;-)

https://x.com/avthars/status/1788195573989806448

samwillis3• 2 years ago

The best things that's happening in the Postgres ecosystem right now is the separation of compute and storage. Breaking that link opens up Postgres as a generic query processing engine that can sit in font of multiple different table and storage types. It allows joining between all these different types of data. It really will enable you to use Postgres as generic database for everything.

My theory is that every new database tech starts and a new domain specific db, then over time gets merged into the generic engines to serve the 99%.

tkiolp42• 2 years ago

The vast majority of companies I have worked for, had products that required at most 3 main components:

- web server

- relational db

- a queue system for async processing

So, yeah two of such components can be handled by postgres (although I’d go for rabbit mq for async processing).

samwillis3• 2 years ago

With PLv8 you can run much of your server side logic I. The database, even React server components: https://react-postgres-components.vercel.app/

galaxyLogic• 2 years ago

Shouldn't it be "Using Postgress as the only database"?

There are probably other things in your stack than just the database.

jamesgresql4• 2 years ago

(Author here) Oh for sure! This is specifically about databases, databases can't do it all (unless you use Omnigres I suppose?)

westurner• 2 years ago

Postgres notes:

ElectricSQL, SQLedge, pglite: https://news.ycombinator.com/item?id=38690588

pgreplay, pgkit: https://news.ycombinator.com/item?id=37959945

pg_timeseries: https://news.ycombinator.com/item?id=40417347

pgvector, postgresml: https://news.ycombinator.com/item?id=37812219

cmu ottertune, pg_auto_tune, postgresqltuner,

timescaledb-tune: https://github.com/timescale/timescaledb-tune

awesome-postgres: https://github.com/dhamaniasad/awesome-postgres

nojvek• 2 years ago

I failed a system design interview for saying use postgres for the whole solution. App data to support about a millions users and a job/task table to support asynchronous tasks. The throughput was a few tasks per user per day.

I feel they were expecting a more complex solution with kafka queues.

He! Interviews don't go well for saying "just use postgres"

eska• 2 years ago

You dodged a bullet. My company burst out laughing and applauded when I suggested we should write boring software (we’re a milling company)

fridder• 2 years ago

One barrier to this idea is AWS RDS and the limited number of extensions it has

redbell• 2 years ago

Related post from late 2022 with +700 points and +400 comments: Just Use Postgres for Everything ( https://news.ycombinator.com/item?id=33934139 )

helsinki• 2 years ago

I love Postgres, but this is the most pointless, tone-deaf, biased article I’ve read in a long time.

robert2003b• 2 years ago

Replace Redis by using UNLOGGED tables and TEXT as a JSON data type in PostgreSQL. Use PostgreSQL with SKIP LOCKED for message queuing instead of Kafka. https://www.mycoveredcalifornia.com

Atotalnoob• 2 years ago

Why are you referencing covered California?

Using Postgres for Everything

Related Stories