0

I’m working on an e-commerce store that is multi-lang and I’m using Node.js and PostgreSQL.

I’m confused if I should store the product images in a separate table or in a JSONB column in database?

The same thing for the translation, the product may have different titles for every language so which one is better ? to store them in a separate table too, or in JSONB column?

Edit 1: By saving images in the database I mean the path of the image in Amazon S3 or something like this not the image itself

Edit 2: Let's say I have a logo, thumbnail, and banner for the product also the product has multiple images for the carousel, which one is better, store them in JSONB column or multiple varchar columns or a separate table?

Adel Bassiony
  • 127
  • 2
  • 11
  • 1
    You may place the product images in a `bytea` or Base64-encoded `text` column, not JSONB. They will be [TOAST](https://www.postgresql.org/docs/14/storage-toast.html)-ed. – Stefanov.sm Oct 31 '22 at 13:45
  • Thank you, I updated the question because I was asking if I should save the image path in separate table or in a json column, not the image itself – Adel Bassiony Oct 31 '22 at 16:50

1 Answers1

1

I'm not marking this question as duplicate just because it includes also a question about how to store internationalization data.

I’m confused if I should store the product images in a separate table or in a JSONB column in database?

I would avoid saving images in a database. A better approach would be using file system or cloud storage like Amazon S3; then you could save a filesystem path or cloud URL in a VARCHAR column to have a reference to the image file.

If you still want to save images in PostgreSQL, if for each product you only have one image, I don't see the a reason to use a separate table. You can use bytea or blobs. Look here for more details.

The same thing for the translation, the product may have different titles for every language so which one is better ? to store them in a separate table too, or in JSONB column?

I see two possible approach:

  1. Storing titles in one nullable column for each supported language. If you plan to add languages in the future, you have to handle it with database migrations. Also, if you have more text data like product descriptions that should be supported for all languages, you will need not only the en_tile, fr_title, es_title ... columns but also the en_description, fr_description, es_description ... as well.
  2. Using JSONB (or even better JSON) datatype. You can store all internazionalized data in a single JSON column, with more flexibility if the JSON structure changes. I would use JSON instead of JSONB because it's faster and requires less space on disk. JSONB should be used if you intend to index JSON properties and to query with conditions on them. I don't think this is the case. Here and here you can read more on JSON vs JSONB in postgres

I would go for option 2.

EDIT 1

As Frank Heikens told in comments, JSONB is generally adviced over JSON from postgres documentation.
Benchmarks could help you to understand better if JSON or JSONB is the best choice for you.
I've found a benchmark here. Results here shows better performance on JSONB over JSON. This is strange to me to be honest and if I will have time I will do some more depth test.
Anyway, for your situation, JSONB should just be fine as JSON is.

radar155
  • 1,796
  • 2
  • 11
  • 28
  • 1
    Why using the JSON datatype when even the manual advises the usage of JSONB? "In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.". https://www.postgresql.org/docs/current/datatype-json.html – Frank Heikens Oct 31 '22 at 14:15
  • 1
    again, because the JSON type Is more efficient. If you don't Need indexing in the column, using JSONB Will Just requires more space and time. For small objects this difference Is negible. But when objects grows... – radar155 Oct 31 '22 at 16:42
  • I updated the question, I was asking to save the image path, not the image itself – Adel Bassiony Oct 31 '22 at 16:49
  • For the translation, I tried both options but I think option 2 will be better to store them all in one json column, not a separate table or multiple columns – Adel Bassiony Oct 31 '22 at 16:52
  • @radar155: the manual tells me jsonb is the most efficient data type, not json because that’s just synthetic sugar for text – Frank Heikens Oct 31 '22 at 17:57
  • 1
    @FrankHeikens what do you mean with efficient? The PostgreSQL documentation tells that jsonb should be used in most cases because it assumes that if you store a JSON in a postgres database, you are then going to perform queries where you want to filter for a JSON property; or maybe you want to select only a JSON property in your query output. For this kind of usage, JSONB is more efficient than JSON. But if you only plan to store and retrieve the entire JSON data, without any operation on it, JSON should be the way to go. – radar155 Oct 31 '22 at 18:25
  • @radar155: you’re saying the manual is wrong, because it states pretty clear that jsonb is the way to go: In general, most applications should prefer to store JSON data as jsonb – Frank Heikens Oct 31 '22 at 19:20
  • I'm not saiyng the manuale Is wrong at all. It dependa on use cases. I Will edit my answer with benchmarks to prove It. In the past I already switched from JSONB to JSON to improve performance of a highly used postgresql table – radar155 Oct 31 '22 at 19:25
  • Ok I don't have the possibility to write the benchmark by myself now. But googling i've found [this](https://github.com/evanj/pg-benchmarks). From this benchmarks, it looks JSONB are faster than JSON; based on my experience, this results are strange, but in next days I will try to make new benchmarks by myself. I already did in the past ai I told you, with a great performance improvement passing from JSONB to JSON (as indexing and json operations were not required). For now, I'll edit my response suggesting only JSONB. I will do more benchmark and if needed I will edit it again. – radar155 Oct 31 '22 at 19:52