5 ms·

Postgres is probably the best solution for every type of data store for 95-99% of projects. The operational complexity of maintaining other attached resources f

Postgres is probably the best solution for every type of data store for 95-99% of projects. The operational complexity of maintaining other attached resources far exceed the benefit they realise over just using Postgres. You don’t need a queue, a database, a blob store, and a cache. You just need Postgres for all of these use cases. Once your project scales past what Postgres can handle along one of these dimensions, replace it (but most of the time this will never happen) It also does wonders for your uptime and SLO.

fullstop 4y ago
We collect messages from tens of thousands of devices and use RabbitMQ specifically because it is uncoupled from the Postgres databases. If the shit hits the fan and a database needs to be taken offline the messages can pool up in RabbitMQ until we are in a state where things can be processed again.
- hn_throwaway_99 4y ago
  Still trivial to get that benefit with just a separate postgres instance for your queue, then you have the (very large IMO) benefit of simplifying your overall tech stack and having fewer separate components you have to have knowledge for, keep track of version updates for, etc.
- wvenable 4y ago
  You may well be the 1-5% of projects that need it.
  - Spivak 4y ago
    Everyone should use this pattern unless there's a good reason not too though. Turning what would otherwise be outages into queue backups is a godsend. It becomes impossible to ever lose in-flight data. The moment you persist to your queue you can ack back to the client.
    - mordae 4y ago
      In my experience, persistent message queue is just a poor secondary database. If anything, I prefer to use ZeroMQ and make sure everything can recover from an outage and settle eventually. To ingest large inputs, I would just use short append only files and maybe send them over to the other node over ZeroMQ to get a little bit more reliability, but rarely are such high volume data that critical. There is nothing like free lunch when talking distributed fault tolerant systems and simplicity usually fares rather well.
    - colonwqbang 4y ago
      > The moment you persist to your queue you can ack back to the client. Relational databases also have this feature.
      - Spivak 4y ago
        And if you store your work inbox in a relational db then you invented a queueing system. The point is that queues can ingest messages much much faster and cheaper than a db, route messages based on priority and globally tune the speed of workers to keep your db from getting overwhelmed or use idle time.
    - jakearmitage 4y ago
      > The moment you persist to your queue you can ack back to the client You mean like... ACID transactions? https://www.postgresql.org/docs/current/tutorial-transactions.html https://www.postgresql.org/docs/current/tutorial-transaction...
      - Spivak 4y ago
        You act like the "I can persist data" is the part that matters. It's the fact that I can from my pool of app servers post a unit of work to be done and be sure it will happen even if the app server gets struck down. It's the architecture of offloading work from your frontend whenever possible to work that can be done at your leisure. Use whatever you like to actually implement the queue, Postgres wouldn't be my first or second choice but it's fine, I've used it for small one-off projects.
alberth 4y ago
> Postgres is probably the best solution for every type of data store for 95-99% of projects. I'd say it's more like: - 95.0%: SQLite - 4.9%: Postgres - 0.1%: Other
- roncesvalles 3y ago
  >95.0%: SQLite I'm a bit late to the party (also, why is everyone standing in a circle with their pants down) but, does no one care about high availability with zero data loss failover, and zero downtime deployments anymore?
  - alberth 3y ago
    Do you think more than 5% of projects really need to have HA & zero downtime deployments? (and litestream exists for sqlite if needed)
- contravariant 4y ago
  The way these things go the 5% that you need postgres or otherwise for will account for 95% of the data.
- runeks 4y ago
  Why?
- SergeAx 4y ago
  How do you ensure fault tolerance on SQLite?
  - brohee 4y ago
    In one instance I did that by storing the file on a fault tolerant NAS. Basically I outsourced the issue to NetApp. The NAS was already there to match other requirements so why not lean on IT.
adverbly 4y ago
Careful with using postgres as a blob store. That can go bad fast...
- colonwqbang 4y ago
  Ominous... Care to elaborate?
- no_butterscotch 4y ago
  more deets? I want to use Postgres for JSON, I know it has specific functionality for that. But still, what do you mean by that and does it apply to JSON why or why not?
- jakearmitage 4y ago
  Why?
- dewey 4y ago
  This really depends on the size of your blobs. Having a small jsonb payload will not be a problem, storing 500MB blobs in each row is probably not ideal if you frequently update the rows and they have to be re-written.
  - GordonS 3y ago
    Doesn't Postgres actually use it's TOAST system (basically pointers to blobs on disk) for any large value anyway?
jrochkind1 4y ago
While it makes sense to use postgres for a queue where latency isn't a big issue, I've always thought that the latency needs of many kinds of caches are such that postgres wouldn't suffice, and that's why people use (say) redis or memcached. But do you think postgres latency is actually going to be fine for many things people use redis or memcached for?
armatav 4y ago
Honestly. This is 100% correct.

fullstop 4y ago

We collect messages from tens of thousands of devices and use RabbitMQ specifically because it is uncoupled from the Postgres databases. If the shit hits the fan and a database needs to be taken offline the messages can pool up in RabbitMQ until we are in a state where things can be processed again.

hn_throwaway_99 4y ago
Still trivial to get that benefit with just a separate postgres instance for your queue, then you have the (very large IMO) benefit of simplifying your overall tech stack and having fewer separate components you have to have knowledge for, keep track of version updates for, etc.
wvenable 4y ago
You may well be the 1-5% of projects that need it.
- Spivak 4y ago
  Everyone should use this pattern unless there's a good reason not too though. Turning what would otherwise be outages into queue backups is a godsend. It becomes impossible to ever lose in-flight data. The moment you persist to your queue you can ack back to the client.
  - mordae 4y ago
    In my experience, persistent message queue is just a poor secondary database. If anything, I prefer to use ZeroMQ and make sure everything can recover from an outage and settle eventually. To ingest large inputs, I would just use short append only files and maybe send them over to the other node over ZeroMQ to get a little bit more reliability, but rarely are such high volume data that critical. There is nothing like free lunch when talking distributed fault tolerant systems and simplicity usually fares rather well.
  - colonwqbang 4y ago
    > The moment you persist to your queue you can ack back to the client. Relational databases also have this feature.
    - Spivak 4y ago
      And if you store your work inbox in a relational db then you invented a queueing system. The point is that queues can ingest messages much much faster and cheaper than a db, route messages based on priority and globally tune the speed of workers to keep your db from getting overwhelmed or use idle time.
  - jakearmitage 4y ago
    > The moment you persist to your queue you can ack back to the client You mean like... ACID transactions? https://www.postgresql.org/docs/current/tutorial-transactions.html https://www.postgresql.org/docs/current/tutorial-transaction...
    - Spivak 4y ago
      You act like the "I can persist data" is the part that matters. It's the fact that I can from my pool of app servers post a unit of work to be done and be sure it will happen even if the app server gets struck down. It's the architecture of offloading work from your frontend whenever possible to work that can be done at your leisure. Use whatever you like to actually implement the queue, Postgres wouldn't be my first or second choice but it's fine, I've used it for small one-off projects.

alberth 4y ago

> Postgres is probably the best solution for every type of data store for 95-99% of projects. I'd say it's more like: - 95.0%: SQLite - 4.9%: Postgres - 0.1%: Other

roncesvalles 3y ago
>95.0%: SQLite I'm a bit late to the party (also, why is everyone standing in a circle with their pants down) but, does no one care about high availability with zero data loss failover, and zero downtime deployments anymore?
- alberth 3y ago
  Do you think more than 5% of projects really need to have HA & zero downtime deployments? (and litestream exists for sqlite if needed)
contravariant 4y ago
The way these things go the 5% that you need postgres or otherwise for will account for 95% of the data.
runeks 4y ago
Why?
SergeAx 4y ago
How do you ensure fault tolerance on SQLite?
- brohee 4y ago
  In one instance I did that by storing the file on a fault tolerant NAS. Basically I outsourced the issue to NetApp. The NAS was already there to match other requirements so why not lean on IT.

adverbly 4y ago

Careful with using postgres as a blob store. That can go bad fast...

colonwqbang 4y ago
Ominous... Care to elaborate?
no_butterscotch 4y ago
more deets? I want to use Postgres for JSON, I know it has specific functionality for that. But still, what do you mean by that and does it apply to JSON why or why not?
jakearmitage 4y ago
Why?
dewey 4y ago
This really depends on the size of your blobs. Having a small jsonb payload will not be a problem, storing 500MB blobs in each row is probably not ideal if you frequently update the rows and they have to be re-written.
- GordonS 3y ago
  Doesn't Postgres actually use it's TOAST system (basically pointers to blobs on disk) for any large value anyway?

jrochkind1 4y ago

While it makes sense to use postgres for a queue where latency isn't a big issue, I've always thought that the latency needs of many kinds of caches are such that postgres wouldn't suffice, and that's why people use (say) redis or memcached. But do you think postgres latency is actually going to be fine for many things people use redis or memcached for?

armatav 4y ago

Honestly. This is 100% correct.