This is the only Postgres schema you need, change my mind

[philmcp]

CREATE TABLE x (

id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,

data jsonb,

inserted timestamp with time zone DEFAULT now(),

updated timestamp with time zone

);

[sargstuff]

Given schema is limited to where uuid v4 usage is relevant/appropriate.

uuid version 7 more appropriate for keys in high-load databaseses and distributed systems.

Issues if need something other than uuid_v4. aka v8,

snowflake_id bit more compact than separate uuid_v4 & timezone

json, "blob" storage, not efficent for/optimized for search/replace operations. Json blob will need to be normalized every time data cached. File system storage with uuid_v7 index less overhead.

[philmcp]

Fair enough, ive not used v7/v8

I stand by the rest however

More pros than cons

[perrygeo]

uuids as a primary key can be a major insert performance bottleneck since they are randomly distributed.

data isn't indexed so queries other than select-by-uuid will be slow (unless you're putting indexes on special keys which is just an ad-hoc schema with extra steps)

data migrations will be painful and require full table scan/rewrite (hope you can afford downtime)

No relationship between any of your data; it's all just independent blobs of data that may or may not be related. No referential integrity means you need another out-of-band process to make sure everything is pointing to valid data.

I get the temptation to nope out of schemas and do schema-on-read. Worked for Mongo, right? (did it?) However, postgres allows an even better option: create an actual schema for your business domain THEN add a jsonb column to every table. If you need to add extra stuff, you can just shove it into the data column. You get all the benefits of a static schema plus an option to jump ship and do JSON in a pinch.

[bravesoul2]

Depends. Having a real schema can help with performance and correctness guarantees.

[speedgoose]

I believe you can make constraints and indexes on jsonb fields.

[yanko]

Having extra 'deleted at' timestamp column allows if not null to count the record deleted without actually delete the record

[codegeek]

Tell me you have never built a real world production application without telling me you have never built a real world production application.

[philmcp]

Doing this on 2 apps

200k users a month, not huge but not nothing

Why do you think it's a bad schema?

[roscas]

Care to example some usage cases? And why uuid over auto-increment sequenced number?

[speedgoose]

An auto-incremented sequenced number can leak business information that one may not want to leak. Such as number of customers, transactions, documents…

Sometimes it’s fine and the simplicity is worth it if you aren’t dealing with a distributed database, but a random uuid is a better default in my opinion.

[mooreds]

Use mongoDB without using mongoDB.

[philmcp]

10x better than mongo