| JSON in Postgres is a bit like a nail gun. Used correctly, it's incredibly useful. But in inexperienced hands (and lacking good technical leadership), it's easy to shoot yourself in the thigh. You don't even need JSONB to commit war crimes on a Postgres database. There's many things that Postgres can do, but probably shouldn't be done: - Storing "foreign keys" in an array column, instead of using a join table - Storing binary files as base64 encoded strings in text columns - Using a table with `key` and `value` string columns instead of using redis - Pub/sub using NOTIFY/LISTEN
- Message queueing - Other forms of IPC in general - Storing executable code - God tables Even when trying to use Postgres appropriately, plenty of engineers don't get it right: unnecessary indices, missing indices, denormalised data, etc. This isn't unique to Postgres, or relational databases in general. Any form of storage can and will be used to do things it's not designed or appropriate for. You can use as easily use S3 or Elasticsearch for message queuing, and can even find official guides to help you do so. Go back 20 years or so, and you can find implementations of message busses using SOAP over SMTP. The problem isn't JSONB (or any other feature). It's bad engineering. Usually it's an incarnation of Maslow's Hammer: when all you have is a hammer, everything looks like a nail. |
Very bad idea for a base table, sure. OTOH, potentially great idea for a (possibly materialized) view (as might eagerly storing an array of row values instead of keys.)
> Storing binary files as base64 encoded strings in text columns
That might not be a bad idea depending on size, and depending on how often you needed the binary vs. a base64 encoded string: if most of the use of the binary is in a context where it will be sent as base64 encoded, storing it that way might be a great idea.
> Using a table with `key` and `value` string columns instead of using redis
If you need an in-memory cache, sure. Otherwise...you could use redis, but I’m not sure why it would always be preferred.
> Pub/sub using NOTIFY/LISTEN - Message queueing
NOTIFY/LISTEN are a pub/sub mechanism. You shouldn’t use them alone as an application level message queueing system, but you definitely can build such a system on PG and might well use NOTIFY/LISTEN in the implementation.
> - Storing executable code
There are probably problems that involve specific instances of doing this, but this is at best to general to describe a thing yoi shouldn’t do.