PostgreSQL many jsonb columns vs many rows

Question

From other answers on many columns vs many rows (or tables) it seems columns are more performant for normalized data. What about serialized data?

I'm going to store many in-progress web forms, i.e. not yet validated just a dump of what the user has so far so they can continue in another session. The forms will be serialized as json and stored in a jsonb column. There are currently ten forms but (many) more will be added in the future.

Is it better to have one column with a user id and a column for each form:

CREATE TABLE "forms" (
    "user_id" uuid NOT NULL,
    "form_a" jsonb,
    "form_b" jsonb,
    "form_c" jsonb,
    ...
)

or many rows with a user uuid, form id, and form json columns:

CREATE TABLE "forms" (
    "user_id" uuid NOT NULL,
    "form_id" uuid NOT NULL,
    "form_json" jsonb NOT NULL
)

I'm sure querying for just one row is faster, but what about updating a column in a row with many jsonb columns? or adding a new jsonb column to a table with millions of rows? At what point does it tip to favoring many rows?

thanks!

Laurenz Albe · Accepted Answer · 2017-01-25 10:02:00Z

If new forms are introduced only during maintenance windows (upgrades), you might get away with using the first method.

If new forms can be introduced during normal operation, that would cause problems:

ALTER TABLE blocks and is blocked by all concurrent data modifying statements, which can be a problem.
You need to be the table owner or a superuser to run ALTER TABLE, but for security reasons it is better if your application user can be somebody else than the table owner.

Increased data volume for UPDATE is not a consideration, because as the documentation says:

During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.

I think that the second design is cleaner, and the slightly more complicated query will not be noticably more expensive if you have the right indexes.

Collectives™ on Stack Overflow

PostgreSQL many jsonb columns vs many rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related