This is the only Postgres schema you need, change my mind 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 ); |
This is the only Postgres schema you need, change my mind 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 ); |
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.
I stand by the rest however
More pros than cons
Essentially using a database as a file system[0].
[0] : postgres fuse file system : https://github.com/petere/postgresqlfs
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.
Not sure about performance. I imagine if that user_id field is a column you might get better performance. Not least because by choosing columns in a query you reduce the amount of data to be processed.
Another benefit of doing it the boring way is tooling. Orms, schema migrations and so on. I work in this space somewhat and think alot about the DX of both key value and relational stores!
Think of me as a "Just use postgres" and "Also just use dynamo" at the same time :)
200k users a month, not huge but not nothing
Why do you think it's a bad schema?
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.