undefined | Better HN

0 pointstoomanybeersies4y ago0 comments

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.

0 comments

dragonwriter4y ago

> Storing "foreign keys" in an array column, instead of using a join table

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.

jordanthoms4y ago

Yeah re the binary base64 encoding - we found that when using text based queries (the default for pg gem on ruby and most other client libraries AFAIK), base64 in a text column outperformed byte columns as it avoids extra conversions to and from base64.

We did switch to using binary queries though (by passing result_format as 1), and at that point we did see a speedup from using bytea columns - but it’s quite a bit of work to get the type mapping correct then so probably not worth it in most cases

j / k navigate · click thread line to collapse