r/programming • u/[deleted] • Mar 18 '25
Life Altering Postgresql Patterns
https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns33
u/whats-a-parking-ramp Mar 18 '25
UUIDv7 fixes the index problems that you see with random UUID primary keys. Then you can have your cake and eat it too.
2
u/SoInsightful Mar 18 '25
UUIDv7 leaks database information about when rows were created. So no, not really.
28
u/whats-a-parking-ramp Mar 18 '25
Yep. If that's a problem for your system then don't use UUIDv7. What kind of system would that be? At my job, I can't think many that would have that constraint so I'm curious what you work on. I'm in retail/e-commerce, for reference.
3
u/bwainfweeze Mar 18 '25
Or both. Just because your PKs are guessable doesn’t mean your slugs have to be.
It’s primarily that not leaking PKs to customers (who may be competitors of other customers) takes a ton of discipline and vigilance that may be better spent on other functionality.
If you use a monotonically increasing identifier for your table joins and compound indexes, you can get away with having an index per table that is slug-only and is not a particularly efficient use of b-trees.
I don’t think that non increasing keys present the inscrutable wall people think they do either. Timing attacks against caches are everywhere these days and the lack of documented timing attacks against databases is IMO an oversight. Not evidence of absence, just absence of evidence.
1
u/SoInsightful Mar 18 '25
My point is that you have to consider whether a bad actor could use that business information for anything malicious if you use UUIDv7. In e-commerce, that could be sales data or information about merchants or products. If you discover later that you don't want this information to be public, maybe you can't easily change all UUIDs without breaking a bunch of links, for example.
Contrarily, I don't believe the positive effects of monotonically increasing IDs are especially big in today's day and age, so I would just go with UUIDv4s or cuid2s.
3
u/neopointer Mar 18 '25
Can you make a concrete hypothetical scenario where this would be a problem?
7
u/Nastapoka Mar 18 '25
Why should every member of your website automatically disclose when they became a member?
Sure, many websites show this info, but not all of them do.
3
u/neopointer Mar 19 '25 edited Mar 19 '25
That's only possible if you have the list of UUIDs.
If you leak all the user IDs of your whole database, that's not UUID v7's fault.
To me your example doesn't make sense or am I missing something?
3
u/Nastapoka Mar 19 '25
You're missing the fact that UUIDv7 embeds a timestamp in the UUID, yes.
4
u/neopointer Mar 19 '25
No, I know this fact.
What I'm intrigued about is how an attacker, so to say, would grab all those UUIDs.
As a user of a website I would normally get access to my own UUIID, not to everyone's UUID.
This is a prerequisite to leak the "registration dates".
4
u/Nastapoka Mar 19 '25
Typically when you visit another user's profile, how does the request target this precise user? Sure could could use another unique identifier but you have to make sure it never changes, the slugs don't collide (if it's passed in the URL), and now you're basically dealing with two primary keys instead of one
12
u/turbothy Mar 18 '25
For many-to-many join tables, I always go with the name table1_x_table2 to visually signify the multijoins.
3
u/tempest_ Mar 18 '25
Yeah, get enough of these tables or tables with shared prefixs and it becomes a chore to parse them without a delimiter.
23
u/leftnode Mar 18 '25
I'm glad he mentioned singular table names and that they should be named that because they represent a collection of individual rows. The only problem is that you can't (easily) name a table user in Postgres so I'm left calling it users (though you can get around this by calling it user_account or profile or something similar).
I have mixed feelings on soft deletes: yes, storage is cheap and it's far easier to recover a soft deleted record, but you quickly run into issues when it comes to joins. Also, if a developer forgets to add a WHERE table.revoked_at IS NULL to a query, you can accidentally display data that the user thought was deleted (which can open you up to litigation in extreme cases).
Another solution is to create a "trash can table" and trigger to insert records into the trash can table when they're deleted. This has the added benefit that if you do use cascading deletes that as long as the table has the trigger on it, the deleted records will be put into the "trash can table" as well. Recovering them isn't as simple as nullifying a timestamp, true, but it's simpler than having to pull the record from a backup.
The deleted record can be stored as a JSON encoded string as well so the trash can table structure doesn't have to mirror the table it's mirroring.
17
u/turbothy Mar 18 '25
if a developer forgets to add a WHERE table. revoked_at IS NULL to a query, you can accidentally display data that the user thought was deleted (which can open you up to litigation in extreme cases).
If you're facing litigation in extremis for displaying soft-deleted data, that's a pretty good sign you should have hard-deleted it in the first place.
4
u/nirreskeya Mar 18 '25
I'm glad he mentioned singular table names and that they should be named that because they represent a collection of individual rows. The only problem is that you can't (easily) name a table user in Postgres so I'm left calling it users (though you can get around this by calling it user_account or profile or something similar).
This is halfway solved by another suggestion: use schemas. We have a
custom_schema_named_after_product.user. The only downside is that for that table one always has to reference it with that fully qualified name, even if the custom schema is in yoursearch_path. Luckily our product name is not that long.4
u/fiah84 Mar 18 '25
Another solution is to create a "trash can table"
I've implemented those and it works OK and keeps the not-deleted data cleaner, but then using the historical data in conjunction with the current data was a bother
I have mixed feelings but I'd probably use them again given the same circumstances
2
u/AmateurHero Mar 18 '25
The deleted record can be stored as a JSON encoded string
We've had some minor discussion around soft deletes with this being suggested rather than a
revoked_atcolumn. I wouldn't expect soft deletes to have relatively high usage for us, and the structured nature of JSON seems to make searching easy enough. Have you run into any challenges restoring data from this table?5
u/leftnode Mar 18 '25
No, we haven't. After about the 100th time we had to waste developer time restoring a single record from a backup, we finally wisened up. It works really well:
Developers don't have to worry about forgetting a
WHERE table.revoked_at IS NULLWe don't want/have to configure our ORM/database abstraction layer to automatically include that in all queries because there are times when superusers do need to see that data.
We updated our admin panel (monolithic app; administration is baked into the software) so that customer support agents can easily query and restore "deleted" data.
We don't have any specific data compliance regulations, but if you did, then you can simply schedule a
DELETE FROM trash_can_table tct WHERE tct.created_at >= NOW() - INTERVAL '6 MONTHS';to actually delete the data.You could also argue that for strict regulatory environments that the current view (aggregate) of the data should just be the summary of all events performed against it which isn't exactly wrong, but does open up a whole other can of worms to contend with.
3
u/EldritchSundae Mar 18 '25
I'd point out that all of these perks apply to any "trash" table, and the person you are replying to is specifically asking about problems restoring a
JSONimplementation.The tradeoff I've ran into before is that a trashed JSON record can be difficult to impossible to restore into the source table if the source table's structure changes over time. You either have to mirror each DDL to change the source table to trashed JSON documents, or give up the "restoration" mechanism and treat it simply as an audit log.
I prefer systems with trash tables with structures that mirror their source tables, as it is easy to mandate/automate applying the same DDLs to them as their sources in a transaction, such that restoration becomes trivial. The trade-off there is you have to think carefully about constraints, especially uniqueness ones, on the trash tables (including primary key sequences and, depending on traffic, randomly-generated UUID primary keys).
1
u/bwainfweeze Mar 18 '25
SELECT * FROM users AS user
WHERE user.inactive = false
4
u/NekkidApe Mar 18 '25
Or quote it:
select * from "user" where "user".inactive = false0
u/bwainfweeze Mar 18 '25
The discussion was about user versus users and how the where clauses flow or do not flow.
3
u/NekkidApe Mar 19 '25
The only problem is that you can't (easily) name a table
userin Postgres so I'm left calling itusers(though you can get around this by calling ituser_accountorprofileor something similar).I was responding to this particular bit.
1
u/bwainfweeze Mar 19 '25
But I was speaking to u/leftnode, who didn’t get hung up on a particular pre-existing table in the default schema. Thats one case among millions.
Complex joins and column filtering look weird when the WHERE clause is operating per row and you’ve named the table a plural.
WHERE rows.finished_at IS NULL
doesn’t scan.
Maybe it’s because I’ve only touched Ecto in the last year for database access, it is simple/simplified there.
Schema is plural, queries are stored in a file that is plural, records are singular, but the query looks like:
query = from u in "users", where: u.age > 18, select: u.nameThat is ver batim the first example on the Ecto.Query docs.
Of you can also work from the data type User:
age = "18" Repo.all(from u in User, where: u.age > ^age, select: u.name)Personally I’d go with
from user in UserThis isn’t a sales pitch for Elixir, I believe these ideas are cribbed from Rails’ ActiveRecord. I’m just saying these are solved problems. Just alias the table in the FROM.
6
u/voronaam Mar 18 '25 edited Mar 18 '25
I am bit surprised comment on was not mentioned. It was truly life altering for me when my old company adopted an approach to add more comments to our DDL scripts.
You know, in every system you are eventually staring at customer_account_manager_product table and thinking "Is it a many-to-many table between customers and account manager products, or between customer account managers and the products? Perhaps it is customer accounts to product managers?" Having a human-written comment on a table or a column is a godsend.
Edit: thanks for the tip on enum. Very timely. We recently tried to use the actual enum types in the DB and are now kicking ourselves for it - it is way too hard to rename an enum value while maintaining compatibility with the application code.
16
u/CVisionIsMyJam Mar 18 '25 edited Mar 18 '25
My review of this article.
Use UUID primary keys
Use UUIDv7 to avoid sorting and indexing issues.
Give everything created_at and updated_at
While giving your car table created_at and updated_at fields are a great idea, giving your car_gps_position_log table created_at and updated_at is not, if this is an insert only table.
If the last car_gps_position_log for a given car is the current position, I have sometimes seen tables where there is log_time and valid_until where valid_until is NULL for the latest position.
But most of the time log_time is enough for tables which store sampled output from a gauge.
on update restrict on delete restrict
This I agree with, handle proper clean-up at the application layer so it can be properly tested.
Use schemas
This is a relatively good idea but does have significant downsides for third-party tooling you should take into account.
Many PostgreSQL compatible tools which involve writing SQL will provide auto-complete for tables in the default schema for the user. That's typically public but can be configured to other schemas as well. If you design things such that you are working across schema bounds a lot, it will make these tools work not as well, as your auto-complete will not work across schema boundaries without customization.
Additionally, handling user permissions can becomes somewhat more complicated if you have a lot of schemas, and especially if schemas are not always present in all environments.
That said schemas are very useful and I like to use them to separate out first party entities from third party more general stuff. So keycloak and celery live in their own schema, and all our first-party stuff lives in an 'application' schema. I personally don't like separating things out more than that for application tables due to the tooling issues it creates.
Enum Tables
This is definitely more complicated but a very good idea for anything which requires dynamic enums. The enum type is a trap.
Using a "text" attribute is fine as a first step for a lot of use-cases as well. It's relatively easy to break out later on.
Note that in this example the author does not give their enum table a created_at or updated_at
Name your tables singularly
yeah this is a good idea.
Mechanically name join tables
This is a good idea as well so long as there's no risk of semantic collision. For example, imagine if we wanted to record people who were also pets in their own person_pet table; now its confusing whether its a join table or if it is an entity.
I think a double underscore is a decent idea to avoid this potential issue; person__pet makes it unambiguous that it is a join table.
Almost always soft delete
Not necessarily a bad idea, I personally prefer to have a log table and then hard delete from the primary table to simplify joins when working against present value data and makes it easier to expire deleted data in log tables.
For example, vet.prescription would have a corresponding vet.prescription_log table, and on CREATE, UPDATE or DELETE, would have the record inserted here as well. Then I can hard delete from vet.prescription and still have the record in my vet.prescription_log table. Finally, if I do need to free up space due to a bug that was filling up the vet.prescription_log, I can potentially do something as simple as truncate the entire table.
Represent statuses as a log
This is an interesting idea that I have not tried before. Would ensure we would not need a separate log table for each table we want history from. In general I like to model everything as a log so this would be pretty cool to try.
Mark special rows with a system_id
Interesting idea but I am not totally sure I would do this inline vet.contact_info; I think I would rather have a vet.contact_info_system_id table that only has inserts for particular entries. But I could go either way I guess. I imagine this is most useful for offering third-party systems an anchor-like id for special types that will never change. I have never had this "special row" scenario come up and it does smell like a schema design issue a bit to me.
Use views sparingly
Pretty much. Personally I only like to use them when providing third-parties direct query access against a database replica for custom reporting purposes. That way we can still move things around under the view by deleting the view, running a migration and then recreating it. I don't use views for features or reports we are responsible for directly.
JSON Queries
I use postgraphile for offline apps and it does this automatically, its pretty cool.
8
u/axonxorz Mar 18 '25
Represent statuses as a log
This point is just dipping your toe into the generalized concept of event sourcing.
1
u/EldritchSundae Mar 18 '25
If the last
car_gps_position_logfor a given car is the current position, I have sometimes seen tables where there islog_timeandvalid_untilwherevalid_until is NULLfor the latest position.This approach makes it difficult to enforce a uniquness constraint on there being only 1 active "latest" record on a table for a given
car_id, asNULLalways compares distinct fromNULLin the SQL standard. The article's booleanlatestcolumn approach supports this better.-3
u/bwainfweeze Mar 18 '25
Use UUID primary keys
Use UUIDv7 to avoid sorting and indexing issues.
I realize that UUID7 was only introduced in May of last year, but I’m already tired of having this conversation every. Single. Time.
If you’re giving advice about database design and you don’t know about UUID7 and 6, then maybe you should consider if you should be the one giving advice on database design.
9
u/serg473 Mar 18 '25
Almost all described patterns don't come for free, you will pay for them with slower queries, higher server load, more complex queries and slower development time. So you shouldn't slap them on every table, only when it is absolutely necessary, and in 99% cases these are not necessary.
At one point in life I stopped worrying about table normalization and started storing statuses (enums) right in the table using human readable names (yes, with spaces and capital letters, Pending Review instead of pending_review or status_id=5), that was the best decision I ever made. Since then I pick the simplest table structure that gets the job done while taking into account what it would take me to refactor/fix/upgrade it. If something will take me less than a day to refactor it's not worth preemptively implementing it just in case I might need it one day. Updating the hardcoded values once a year is many orders of magnitude easier than having to deal with an extra join every day for the rest of the project lifetime.
8
u/kappapolls Mar 18 '25
odd collection of recommendations with a lot of personal preference baked in. some are good to be aware of, but not life altering and definitely not "do this in all cases".
also UUIDs as primary key is just not good practice in a vacuum. you should absolutely default to using an integer flavor
also the system id stuff makes no sense
It's not uncommon to end up with "special rows." By this I mean rows in a table that the rest of your system will rely on the presence of to build up behavior.
but, every row in the table is a row that the rest of the system relies on for behavior?
2
u/antiduh Mar 18 '25
I agree with you. One in particular was referencing enum values by their string key. Seems inefficient - wouldn't it make much sense to refer to each enum value with an integer?
Databases are for software first, people second.
0
u/kappapolls Mar 18 '25
honestly i glossed over the enum part. i'm personally not a huge fan of using things that aren't ANSI sql if i don't have to. and i don't understand what problem enums solve that can't be solved by just doing some normalization and using regular tables and keys
3
u/Skithiryx Mar 18 '25
Personally I hate log tables, they lead to dumb problems at query time like having to query through 20 years and billions of rows of logs to find out which user accounts are currently active. For that system that kind of query was an overnighter.
2
u/DigThatData Mar 18 '25
most of this is SQL best practices, not just postgresql.
re: "join tables", I think of them as "cross-reference tables" and use the prefix xref_ in addition to your thing, so you get a bunch of XREF_SRC_TGT tables.
2
u/st4rdr0id Mar 20 '25
Give everything created_at and updated_at
That is going to tax bulk insertions for sure, since it is calling a timestamp function per row.
Mark special rows with a system_id
Why not a boolean column "is_this" or "is_that"? It is more explicit, and uses less space than a text field that also needs to be hardcoded in the application that makes the query.
JSON Queries: But the giant upside is that you can get all the information you want from the database in one trip, no cartesian product nightmares or N+1 problems in sight
I'm not sure what this means. You don't need JSON for getting "all in one trip". How is serialization related to the fact you need to make a certain query? The "no cartesian product" thing baffles me, for the example given in the article the DB is still doing all the joins that it needs.
1
u/bowbahdoe Mar 20 '25
There are a few examples of the system id thing in the comments on the site itself.
For JSON queries, Consider this situation:
You want to fetch a person
And all the pets of that person
And all the vaccination records of those pets
And all the previous addresses of that person
And all their doctors appointments for the last year
And the info for the doctors who were on call during those appointments
If the number of rows in your resulting query is found by multiplying all the sizes for these sets of data, that's the Cartesian product. For every doctor you will have a duplicate pet name, for every appointment a duplicate vaccination record, etc.
With a hierarchical query result - which you can get with Json but also apparently with a poorly supported SQL standard thing called a multi set (pros of publishing is that you can learn stuff from nerd sniping) - you get the sum of the number of rows in each set.
1
u/st4rdr0id Mar 20 '25
With a hierarchical query result - which you can get with Json but also apparently with a poorly supported SQL standard thing called a multi set (pros of publishing is that you can learn stuff from nerd sniping) - you get the sum of the number of rows in each set.
I'm trying to decipher what you meant here. To begin with the full cartesian product is rarely computed by a DB engine, even if your query had
FROM table1, table2, the moment you appendWHERE table1.c1 = table2.c2you are already restricting the output to one equivalent to a join.Then you talk of the N+1 problem which usually is caused by ORMs doing N separate queries by id on top of the main query. That should not happen when you use joins in an SQL query as the DB engine should optimize the query and get everything in "one trip".
And finally I don't understand the link to the JSON serialization. It is my understanding that a query made by a program using the pertinent DB driver can benefit from a more concise binary representation, which you can then transform in your code to whatever format you need.
1
u/bowbahdoe Mar 20 '25
Sketch out what the rows returned by the database look like. I'm not saying it's a Cartesian product of every row in the db, just a Cartesian product of the rows involved in your result
1
u/Ecksters Mar 18 '25
That final example is making me wonder if any of the Postgres-GraphQL tools, especially the tightly integrated ones like Postgraphile are essentially building their queries to just output JSON responses like that.
2
u/eijneb Mar 18 '25
That’s what PostGraphile v4 does, essentially. We actually use json_build_object rather than jsonb, we find it to be about 5x as performant (JSONB is great for filtering data/etc, but JSON is essentially constrained text so Postgres can compose it in output very efficiently). In V5 our queries are much more efficient than this because we do more work in the JS layer and less work in the Postgres layer, helping to scale even further.
2
u/Ecksters Mar 18 '25
That's actually really interesting to hear that
jsonbis slower in this case, good to know, I definitely was in the camp of "always use JSONB, pretend JSON doesn't exist".That makes sense to move more work into the JS layer where you can, I figure it's generally easier to scale that horizontally than you upgrade your PG servers.
1
1
u/tswaters Mar 18 '25 edited Mar 29 '25
One thing I prefer to jsonb_build_object is to_jsonb and jsonb_agg for arrays.
select array(
jsonb_build_object(
'id', id,
'name', name
)
)
from petsVs.
select jsonb_agg(z)
from (
select id, name from pets
) z;1
u/NoInkling Mar 19 '25
Give everything created_at and updated_at
Protip: don't write the trigger function yourself. Instead enable the built-in moddatetime extension (CREATE EXTENSION IF NOT EXISTS moddatetime;) and call like this: moddatetime(updated_at)
1
u/noeldr Mar 19 '25
Really bad advices…
0
Mar 19 '25
[deleted]
1
u/noeldr Mar 21 '25
If you want specifics here are some: UUIDs and a Btree indexes are not good at all unlesss you use a monotonic version like v7
Foreign keys are not a panacea , particularly for very large tables. In the long run they will hinder more than what they would help.
Having functions as default values may seem like a good idea until you decide to write more than a handful of rows.
I could go on but my intent was simply to warn the reader to takes these “advices” with a healthy dose of skepticism.
0
Mar 18 '25
[deleted]
51
u/Vectorial1024 Mar 18 '25
Either use ULID, or use specific UUID versions that allow for time progression natural sorting
87
u/Wolfy87 Mar 18 '25
For those that aren't sure, UUID v7 is generally the one you want these days. It encodes time for you which helps postgres create smaller and faster indexes with consistent predictable sizes.
https://equenum.github.io/posts/uuid-v4-vs-v7-in-postgre-sql/
20
u/BlackenedGem Mar 19 '25
The one thing you do need to be mindful of is that UUIDv7 breaks one of the advantages in the original post:
They are safe to share externally
As you're now encoding information within the UUID this makes them not truly anonymous identifiers. For example if you had an account uuid and transaction uuid then an attacker finding one would be able to infer when the account was created or the transaction performed. That might be quite important depending on the context.
12
9
u/Ecksters Mar 18 '25
My primary complaint is how long they are, particularly given many languages will store them in memory as a string. I've considered adding a UUID shortener (something like short-uuid) to my Postgres deserializer, but even with base64 instead of base16 to represent them, they're quite a bit longer than is convenient for the end user.
9
u/myringotomy Mar 19 '25
The biggest shortcoming for UUIDs is that they are difficult to communicate. I can't even begin to count the number of times I have said "item number X needs to be fixed" or "user X is having a problem".
45
u/taotau Mar 18 '25
Good summary of common best practices in general. I'd love to share this with my junior bootcamp Devs who don't like to read, but the system_id thing is just weird and a pretty bad practice.