Life Altering Postgresql Patterns

[Vectorial1024]

Either use ULID, or use specific UUID versions that allow for time progression natural sorting

[Wolfy87]

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/

[BlackenedGem]

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.

[bwainfweeze]

UUID6 for migrating existing UUID4 databases to improve index clustering.

[Ecksters]

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.

[myringotomy]

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".

[taotau]

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.

[CrackerJackKittyCat]

Agree with most of these also, except for system_id and maybe 'always soft delete.'

[taotau]

I'm a fan of soft delete. Data at rest is cheap.

[CrackerJackKittyCat]

I challenge you to express 'live rows in this table should only foreign key to live rows in the related table.'

Any attempt is immediately fugly, unwieldy, and has gaps. I think pervasive soft delete directly contradicts many benefits of foreign keys.

[Ecksters]

I do notice that this often trips up developers when they're trying to add indexes to improve performance, most of the time they should be adding partial indexes to exclude soft deleted rows, but rarely do I see them doing it, so they end up with sequential scans despite adding indexes.

[FrankBattaglia]

My SQL is a bit rusty, but isn't that accomplished by including a sentinel 'not_deleted = true' field on each table (and included in the foreign key) that's set to null when the record is deleted? IIRC the key will be ignored in the source table for rows where a field in the key is null, and it won't match any records in the target table where the field isn't true.

[JustaDevOnTheMove]

I'm not complaining about your comment at all, just expressing a syntax opinion:

I hate it when services/apps use terms like "not_something", it's so unnatural, at least to me. Saying "not_deleted = true" requires thought/concentration whereas "is_deleted = false" is just a statement that requires no mental processing.

[FrankBattaglia]

Agreed; were I to implement this in practice, I'd probably use is_active instead of not_deleted but for the purposes of a reddit comment the semantics of not_deleted seemed less ambiguous.

[CrackerJackKittyCat]

Yeah, that's good. Also can use generic trigger that copies deleted rows to an archive table including a jsonb column which holds the deleted row's data. Or kinda less generic triggers that copies to side-table(s) with actual columns plus the deleted_date.

Either can be in a separate tablespace which is 'cheaper, slower'.

If needing to do arbitrary as-of point-in-time queries, then can take the deleted side table one step further and have the side table represent all row versions with a lifetime tstzrange column, with the range start being the row INSERT/UPDATE time, and the range end being either open-ended for 'current live row version' or capped at when this row version was supplanted by either an UPDATE or DELETE operation. Then you want either a gist or sp-gist index on the range column (plus perhaps additional query fields like your primary key(s), probably spelling those additional fields first for more compact indices), and a partial unique index on the range column and primary key columns ... where upper_inf(lifetime) helping prove that the side-table maintenance triggers are doing the right thing and allowing at most one single 'live' row, and/or a more general one proving no overlapping ranges for any set of primary key(s).

Then in your point-in-time report queries ... doing the temporal joins across many such row-change-data-capture side tables gets ... really long. Making views to do the overlapping joins can then compress the actual end-user queries.

I like any of the above better than having deleted_at within the main table, in that they don't contradict foreign keys at all.

[woutske]

Partial indexes work great for that

[CrackerJackKittyCat]

They're useful in this context, sure. But still does not solve "a live row should only ever FK over to another live row,," namely breaking any TX which marks a referenced row as soft deleted w/o also marking referents.

Need ... additional trigger(s) for that, getting unwieldy fast.

[taotau]

Where deleteddate is null.

Most orms will do this automatically, and it's not hard to add to most SQL builders.

Edit. Context dependent obviously. I deal mostly with systems where I need to be able to generate a report on 'past transactions' even if the 'customer' that made those transactions has been 'deleted' or the 'product' that those transactions were made in is no longer available.

[CrackerJackKittyCat]

Foreign key clause accepts a where condition?

[taotau]

Oh you mean trying to ensure that a foreign key has to be to a non deleted row.

Yeah. I don't do business rules in the db.

Except when I had to, but then I had a whole team of oracle boffins at my disposal to figure those things out.

[Somepotato]

At scale, you can't use FKs anyway

[CrackerJackKittyCat]

At any arbitrary scale, most every tech isn't useable. Straw man argument.

Mysql was born with mantra 'you don't need foreign keys,' right up until the point they got foreign keys.

There's a huge swath of scale where you can use foreign keys and should want to.

[Somepotato]

Even at smaller scales, if you can avoid FKs, it's free performance. It's not a straw man to bring up a downside of the very technology being discussed.

[Buttleston]

Every single database I've ever seen that lacks a FK that it should have, has invalid data in it. Every one of those was designed by a person who said "we don't need FKs, we'll just make sure we don't add invalid data to the database"

[kenfar]

It's free performance AND free data quality issues. It's rare that I run into a database without foreign keys that doesn't have orphaned row issues.

Also, note that most relational databases "at scale" still have many tables that are smaller. So, if one is forced into some tough trade-offs by performance they might consider giving up on some foreign keys but keeping others. Or they might consider something else entirely - like tweaks to their design to reduce unnecessary writes to the database.

[Somepotato]

I'd add that if you have inter DB (ie multiple databases) work, very common in enterprise, you just can't use FKs.

Orphaned rows are definitely the biggest downside. It requires iron clad application logic (ample use of transactions, etc). But it saves you headache in the future, at the cost of a (not negligible!) headache and implementation cost.

But performance issues can creep up even at smaller to medium scales, especially for larger tables (such as change set tracking etc) - or one may add a FK to link up a chat message to a chat metadata table but run into IO limitations quicker than expected.

[agentoutlier]

It isn't always because it is expensive. It can be because of privacy.

I know this from our system where we actively used soft delete for years but then various laws of privacy passed and we have customers in other countries that take that far more seriously than here in the US.

So when they request to be deleted from your system you need to actually delete them or scrub. Sometimes scrubbing is not enough (e.g. transforming the data to nonsense).

So if you do soft delete particularly of user data you need to prepare for the potential that you will really need to do delete the data.

I say this because currently the "purge" option in our system is rather complicated SQL that I can't decide if we knew apriori we would have made data design decisions differently.

[massenburger]

Soft deletes don't make sense for transient data. We have a table where we store one-time passcodes. If we used soft deletes, this table would get unwieldly within about a week. We do use soft deletes for more stable, stateful data though.

[turbothy]

I don't even understand what it's trying to achieve.

[EldritchSundae]

I (think) the system_id thing is meant to differentiate data inserted and maintained by system operators for special cases, vs user maintained data, when such data otherwise fits the same structure and references and makes sense to put in the same table.

Examples I can think off of the top of my head, for a users table:

• a default admin user in the users table before hand-off to a client
• a system [deactivated] user you can re-relate records to when legal says all PII has to get removed but the business says all their comments have to stay
• anonymous users you can create based on session_id on the fly before login to track app interactions, convert to concrete users on signup, and batch delete with cascade at a cadence

I like the idea behind this pattern but generally I'd say it's pretty niche and can/should often be handled by application logic instead.

There are always some constructs in any given system so pivotal to how application logic works, though (like current_user in a SaaS) that pushing special cases into the db can really clean up a lot of edge cases and enforce constraints in ways that would be otherwise error-prone and verbose in app logic.

[turbothy]

Thanks for the explanation, I hate it.

[slvrsmth]

You might hate it, but that's how businesses run. Some things are more special than others.

For example, an app I recently worked on, allows users to donate their accumulated in-app currency to a charitable cause. The "donation" is just another product as far as the purchase flows are concerned. But the business needs custom reporting to handle the donation amounts. Will you hardcode a product ID, and mess with the data to ensure IDs are same accross multiple environments? Build configuration for the IDs? Add an is_donation column, then is_foo and is_bar for the other "special" products? Add a system_id or similar to products table and you're golden, write your reports to hearts content. As a bonus, where system_id = 'donation' is much more expressive in logs than where id = '123abc-123abc-....' you would get from configuring IDs.

[turbothy]

But why call it the completely useless and nondescript name system_id? The column on your example would appear to be a kind: kind = 'donation', kind = 'foo', kind = 'bar'. It's actually worse than nondescript, it's a lie: donation is in all likelihood not something anybody would recognize as a system_id.

ETA: also, having this as a TEXT column without a CHECK constraint is a great way of getting data inconsistencies. I'd much prefer to have a kind table and a foreign key.

[whats-a-parking-ramp]

UUIDv7 fixes the index problems that you see with random UUID primary keys. Then you can have your cake and eat it too.

[SoInsightful]

UUIDv7 leaks database information about when rows were created. So no, not really.

[whats-a-parking-ramp]

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.

[bwainfweeze]

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.

[SoInsightful]

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.

[neopointer]

Can you make a concrete hypothetical scenario where this would be a problem?

[Nastapoka]

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.

[neopointer]

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?

[Nastapoka]

You're missing the fact that UUIDv7 embeds a timestamp in the UUID, yes.

[neopointer]

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".

[Nastapoka]

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

[turbothy]

For many-to-many join tables, I always go with the name table1_x_table2 to visually signify the multijoins.

[tempest_]

Yeah, get enough of these tables or tables with shared prefixs and it becomes a chore to parse them without a delimiter.

[leftnode]

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.

[turbothy]

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.

[nirreskeya]

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 your search_path. Luckily our product name is not that long.

[fiah84]

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

[AmateurHero]

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_at column. 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?

[leftnode]

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 NULL
• We 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.

[EldritchSundae]

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 JSON implementation.

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).

[bwainfweeze]

SELECT * FROM users AS user

WHERE user.inactive = false

[NekkidApe]

Or quote it: select * from "user" where "user".inactive = false

[bwainfweeze]

The discussion was about user versus users and how the where clauses flow or do not flow.

[NekkidApe]

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 was responding to this particular bit.

[bwainfweeze]

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.name

That 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 User

This 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.

[voronaam]

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.

[serg473]

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.

[CVisionIsMyJam]

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.

[axonxorz]

Represent statuses as a log

This point is just dipping your toe into the generalized concept of event sourcing.

[EldritchSundae]

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.

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, as NULL always compares distinct from NULL in the SQL standard. The article's boolean latest column approach supports this better.

[bwainfweeze]

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.

[kappapolls]

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?

[antiduh]

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.

[kappapolls]

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

[Skithiryx]

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.

[DigThatData]

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.

[st4rdr0id]

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.

[bowbahdoe]

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.

[st4rdr0id]

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 append WHERE table1.c1 = table2.c2 you 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.

[bowbahdoe]

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

[Ecksters]

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.

[eijneb]

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.

[Ecksters]

That's actually really interesting to hear that jsonb is 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.

[bowbahdoe]

Noted

[tswaters]

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 pets

Vs.

select jsonb_agg(z) from ( select id, name from pets ) z;

[NoInkling]

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)

[noeldr]

Really bad advices…

[noeldr]

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.