Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL for Everything

raphaelbauer.com

Post reply on HN
Print view View original

171–180 of 286 posts

Re: PostgreSQL for Everything

#171

Post by zelphirkalt »

KronisLV wrote: post #160
zelphirkalt wrote: post #107

Earlier quoted context omitted.

I can't recommend this "switch". If you are not testing locally with the same relational database as in production, you can miss mistakes and bugs. This is not just theoretical. One example where I thought I will be fine using SQLite was with a small Django project. But time and time again I ran into limitations of either SQLite or Django's database adapter for SQLite, when it came to dealing with many to many relati…

> Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions. Containers are great for this during development. Testcontainers are great for tests in particular when you don't want to use some mocked in-memory DB because those have the same issues as using a different DB during development: https://testcontainers.com/

Agreed, and it is easy to have a Postgres container for local tests or in CI as well. I don't really see much need to avoid Postgres in testing. Also if production runs in containers, testing can and probably should just run in containers as well. Also makes for a cleaner test setup usually.

Re: PostgreSQL for Everything

#172

Post by lelanthran »

0cf8612b2e1e wrote: post #108
devin wrote: post #30

This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet. Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.

I think it would be helpful if some of these posts included scale. There are almost always two groups talking past each other - I run my B2B application, Postgres only, and it is perfect for my 50k MAU. No complaints, sleeping soundly with the low complexity and a two man team. - I work at FAANG, where we have 1 billion DAU, and this is a joke. Would fall over immediately. The dedicated ops teams for Kubernetes, Elas…

I broadly agree, but would tweak those numbers a little for small B2B apps: I ran a small B2B application on a cheap VPS using PostgreSQL as a primitive messaging interface, and even on a small VPS 50k DAU won't even cause the machine to break a sweat.

Re: PostgreSQL for Everything

#173

Post by majormajor »

tensor wrote: post #164
majormajor wrote: post #111

Earlier quoted context omitted.

If you start here, with the "Postgres will take you wherever you need to go" meme, without thinking extremely deeply about your schema and how you expect to evolve it in the future, you can easily paint yourself into a very difficult and expensive corner. It's easy to use Postgres poorly in ways that result in painful centralized bottlenecks. (Obviously this is largely true for anything , but I think that in 2026, wh…

Having done that, e.g. used rabbitmq plus postgres, honestly I wish I had just used postgresql for both messages and data. It would have been easier to manage by an order of magnitude, especially at scale and needing to satisfy enterprise requirements. Also the flexibility of postgres would have solved problems that we ran into because of limitations of rabbitmq.

Without knowing any specifics of your uses, my usual starting point on that sort of design is that "messages AND data" is it's own special little way of ending up with a hard-to-debug-and-operate system. ;)

It's very hard to best-of-both worlds event-driven system + RDBMS-storage, it's very easy to end up with worst-of-both-worlds. Hello distributed transactions!

Again, you just should think about all the ways you want to use it and the maintenance/uptime requirements your users are going to have in advance.

Re: PostgreSQL for Everything

#174

Post by majormajor »

encoderer wrote: post #113
majormajor wrote: post #111

Earlier quoted context omitted.

If you start here, with the "Postgres will take you wherever you need to go" meme, without thinking extremely deeply about your schema and how you expect to evolve it in the future, you can easily paint yourself into a very difficult and expensive corner. It's easy to use Postgres poorly in ways that result in painful centralized bottlenecks. (Obviously this is largely true for anything , but I think that in 2026, wh…

It doesn't take very long (because compute and storage are separate in most of them) but good lord does it get expensive. Every time you click that upgrade button you are doubling your cost. It's really painful when you have a spiky workload that is performing fine like 95% of the time but you are watching the p99 and need to double the cost of a very expensive infra component, only to improve the experience of the h…

I haven't seen a way to get guarantees of upscaling operations under like 30 seconds (with Multi-AZ RDS) with well-supported RDS stuff (leaving out active-active setups with logical replication because that's a whole other can of worms).

If you know you're gonna be ok with that for a long time, go nuts. I'm just saying: think about it in advance!

The cost pain for spikes is also a thing - some of Aurora's billing models look potentially promising but I haven't used them in practice - though it's also somethings that's harder to avoid with alternatives. Distributed DBs aren't generally super friendly to dynamic scaling IME.

Re: PostgreSQL for Everything

#175

Post by aitchnyu »

philippemnoel wrote: post #123
devin wrote: post #30

This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet. Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.

You're right that vanilla Postgres doesn't come close to replacing Elastic. There are efforts to resolve this, though, like ParadeDB: https://github.com/paradedb/paradedb (disclaimer: I work for ParadeDB)

I see Tantivy mentioned in your readme but AFAICT there is no PG-Tantivy sync. I also see "native vector support is coming to our search index soon". Could you clarify?

What do you suggest for a language like Malayalam which has no native support, preferably with low RAM requirements?

Re: PostgreSQL for Everything

#176

Post by mrkeen »

> My tip: Start with PostgreSQL as a queueing system. Only when that does no longer perform well switch to other systems like Kafka, RabbitMQ or SQS.

My tip: store your company's source code on a samba file server. Only when that no longer performs well, switch to other systems like Git.

Re: PostgreSQL for Everything

#177

Post by preg_match »

andai wrote: post #155
crazygringo wrote: post #153

Earlier quoted context omitted.

Not really. They are two different paradigms. Use the one that is right for you. SQLite is embedded for local applications with one writer mostly. Postgres is for a client-server architecture with many writers. When you start a project, you generally know which architecture you need.

So if it needs to work offline, but it syncs with a server, then you use both? (And the schema becomes some kind of lowest common denominator?)

I would probably do an event-source architecture, where you record events on the client and then push them to the server when you're reconnected. It has a lot of benefits, for instance, trivial auditing and free serialization.

Re: PostgreSQL for Everything

#178

Post by jtwaleson »

At Comper we have a very hot key-value store for annotating git data. We maintain a parallel git-blame data structure so we can do incremental "git blame -w -M -C -C". Typically a very expensive operation, but if you make it incremental, you can make it very cheap when new commits need to be analyzed. However, building the git blame tree is still pretty intensive for large repos.

We currently use rocksdb with storage on the same node, and hit rocksdb 1000s of times per second during our analysis. About 20% writes, 80% reads. The issue is that we need to start scaling horizontally, for burstable workers and zero-downtime deployment. So we're thinking to offload to an external kv service instead of a local rocksdb.

TiKV seems a good replacement, about 3-4x slower, but very scalable. Reading this article, I think a separate postgres cluster with unlogged tables might be a good idea. If anyone has some experience to share, let me know!

Re: PostgreSQL for Everything

#179

Post by tensor »

majormajor wrote: post #173
tensor wrote: post #164

Earlier quoted context omitted.

Having done that, e.g. used rabbitmq plus postgres, honestly I wish I had just used postgresql for both messages and data. It would have been easier to manage by an order of magnitude, especially at scale and needing to satisfy enterprise requirements. Also the flexibility of postgres would have solved problems that we ran into because of limitations of rabbitmq.

Without knowing any specifics of your uses, my usual starting point on that sort of design is that "messages AND data" is it's own special little way of ending up with a hard-to-debug-and-operate system. ;) It's very hard to best-of-both worlds event-driven system + RDBMS-storage, it's very easy to end up with worst-of-both-worlds. Hello distributed transactions! Again, you just should think about all the ways you wa…

I think messages + database are extremely common in any sort of large application where you have data processing nodes. Postgresql actually has very good mechanisms to support message style communication, and as long as you design your message tables independently you shouldn't have horrid issues around locking and transactions. Message queues don't save you from thinking about that anyways, they just replace transactions with acknowledgements.

Trying to manage a highly available and durable rabbitmq or other message system that can also be recovered from backup to an offsite mirror infrastructure in the worst case is actually incredibly difficult. Usually these systems are designed with the assumption that you can just regenerate messages based on database state anyways in worst case scenarios.

In this use case your database already is highly available and can recover on an offsite backup if you have suitable wall shipping going on. So you've done all the hard work once, may as well reuse it unless you truly have some mind bogglingly large message throughput needs.

Finally, we had a need of a queue that was more than just first in first out. We wanted to fairly balance workloads across users and tenants. Whenever you have such a need postgresql lets you design this type of queue far easier than trying to do some elaborate multi-queue setup with a traditional queue.

Re: PostgreSQL for Everything

#180

Post by atherton94027 »

tux3 wrote: post #70
atherton94027 wrote: post #35

Earlier quoted context omitted.

At least you have access to USE INDEX on MySQL. On Postgres it's not rare to have a query suddenly perform awful in production because some switch flipped in the planner and now it's picking some random index

Good news is that they just added a plan stability feature in pg19. It's actually full planner hints, so you can edit the plan to whatever you want if you have no fear, but the main motivation is exactly index stability.

I looked it up because I'm interested in the subject but it seems it's only a proposal right now? At least according to this 5 month old blog post https://rhaas.blogspot.com/2026/03/pgplanadvice-plan-stabili...

Edit: Never mind, found it https://www.postgresql.org/docs/19/pgplanadvice.html

Post reply on HN