4 ms·
Interestingly, this article reflects a really common misconception about database normalization: The relational algebra that normal forms are expressed in has
by nostrademons 10y ago
Interestingly, this article reflects a really common misconception about database normalization:
The relational algebra that normal forms are expressed in has no concept of aggregate functions.
The example the article gives uses fields like numUnreadRooms or lastSeenTimestamp. These are derivable from the contents of other tables with SQL aggregates like COUNT() or MAX(), but the relational algebra itself would treat them as facts of the relation itself, dependent upon the primary key. And they'd be updated by the application itself.
In other words, having numUnreadRooms or lastSeenTimestamp in your schema does not make it denormalized.
I was under a similar misconception for the first 5-6 years of my career, and then when I realized that it's perfectly fine to store these aggregates inline, my database queries suddenly started performing acceptably.
As for how to update them - in non-broken databases, the solution is usually triggers. A trigger is a stored procedure that executes when a database operation makes some condition true, and then is executed entirely in the DB, with full transactional awareness. So you could listen for inserts and deletes and then update the count on them, and if your insert failed or the transaction was rolled back, your trigger would never be executed. This keeps data integrity in the database, but still gives you efficient queries and an application interface that's consistent.
- rjbwork 10y agoYeah, the whole time I was reading this I was thinking - wouldn't you just do an insert trigger to increment your unread message counter?
- developer2 10y agoExcept you have the same problem, but are simply moving the complexity from the application to the database. It's not just a single insert trigger. You need one or more delete triggers. You need update triggers for things like unread or soft delete flags. It's also more difficult to maintain 5-10 triggers at the database level where there are few tools to see how multiple triggers for the same aggregation interact with each other. The point is you're suffering the same problem. Whether you do it at the application level or the database level, it's a mess.
- nostrademons 10y agoRight, but the point is that by moving it to the database level, you give the applications above it some guarantees about the state of the DB. You can be sure that some other application hasn't gone and written to the DB without firing the trigger. You can be sure that you don't get race conditions from an application not properly using transactions when updating the derived data. And you can be sure that if a transaction is rolled back, the derived fields will still be in a consistent state. It doesn't free you from programming, from reasoning about your code, or from ensuring that this code is free of bugs. But it does free you from worrying about whether the bugs exist in multiple places or whether there are subtle race conditions involved in the code interacting with itself. Whether this important depends on how many applications are accessing the same database. There's a strong argument for one app = one DB, if you can get away with it, and then none of this is relevant. This is not always possible.
- Roboprog 10y agoOther application? There is no other application, old man. What you talkin' 'bout? ... (2 years later) ... Oh, now I get it!
- catnaroek 10y agoMaterialized views are a superior, or at least less error-prone alternative to triggers, since they don't depend on the programmer's ability to get state transitions right (whenever you manually update aggregates).
- nostrademons 10y agoWhat's the performance characteristics of materialized views? My understanding is that they require running & storing the full query. For a SELECT COUNT(star) FROM TableName this is trivial, but if it's SELECT COUNT(star) FROM BigTableName WHERE ComplexWhereCondition it can get very expensive. For the simple count/max aggregates on a single relation that the blog post is talking about, the trigger logic seems straightforward - you just need a trigger on a single table, for insert/delete/truncate, + update for max or if there are conditions. If there's more than one relation involved or if the condition is complex, then it can get pretty complicated.
- catnaroek 10y ago> My understanding is that they require running & storing the full query. This is right. Having a single materialized view containing nested queries is not a good idea. But you can factor out the nested queries into their own materialized views. The propagation of updates between materialized views can achieve an effect similar to that of complex triggers. I should've been more careful with my original comment, though. I'm not saying triggers are worthless. But, whenever a materialized view can do the job, it's usually preferable.
- icebraining 10y agoPipelineDB (based on Postgres) seems to get the best of both worlds by allowing you to specify a query, like materialized views, but refreshing those results based on inserts/updates, like triggers: https://www.pipelinedb.com/ https://www.pipelinedb.com/ Not affiliated, not even a user, just found it interesting when it was posted here.