Immutable Data (2015) | Hacker News Reader

Immutable Data (2015)

kevinmahoney.co.uk

71 pointsakbarnama40 comments

Author here, 8 years on.

Although the advantages are real, I can't say I have had much opportunity to implement schemas like this. The extra complexity is usually what gets in the way, and it can add difficulty to migrations.

I think it would be useful in certain scenarios, for specific parts of an application. Usually where the history is relevant to the user. I think using it more generally could be helped by some theoretical tooling for common patterns and data migrations.

I agree this is more of an application level concern than a database thing. If you need to maintain a history for the user requirement then you will naturally land on a scheme like this.

We also have help from other quarters nowadays.

Databases often provide a time travel feature where we can query AS OF a certain date.

Some people went down the whole event sourcing / CQRS / Kafka route where there is an immutable audit log of updates.

Data warehousing has moved on such that we can implement “slowly changing data” there.

All in all, complicating our application logic, migrations and GDPR in order to maintain history in line of business applications might not be worthwhile.

> Databases often provide a time travel feature where we can query AS OF a certain date.

This is a good overview of SQL:2011 temporal functionality support across the major players: https://illuminatedcomputing.com/posts/2019/08/sql2011-surve...

I'm curious if the author of that blog ever completed their work with temporal Postgres. I know about [1] but unfortunately work on hosted Postgres most often where the extension isn't an option.

[1] https://pgxn.org/dist/temporal_tables/

You can use Datomic for instance (mentioned already in your article IIRC!?) or SirixDB[1] on sich I'm working in my spare time.

The idea is an indexed append-only log-structure and to use a functional tree structure (sharing unchanged nodes between revisions) plus a novel algorithm to balance incremental and full dumps of database pages using a sliding window instead.

[1] https://sirix.io | https://github.com/sirixdb/sirix

From a user's perspective, I can see a privacy drawback as well.

Suppose that instead of a typical User table, you have a User_Revision table like suggested. Every time a user updates their account settings, you INSERT a new row there. If a user changes their email address, you get a row each time they update it.

Not only the company gets an history of email addresses, but also they are tied to each other. If this information gets leaked, the user is exposed to more vectors of attack.

GDPR and the right to having your personal data deleted certainly puts a bit of a stopper on using an immutable database for anything personally identifying.

This would be the case if you’re a purist, but you can be practical and allow physical deletions of certain record just for this reason.

Pretty much all databases are "immutable". The logs contain every transaction ever made and most institutions save their logs. Also, backups of data are stored on tape and likely shipped somewhere for safekeeping.

First, I don't consider that immutable. Second, that isn't true for all databases, often you have to set it up and turn it on, and think about what you're doing as there are considerations for storage and backups. Third, it isn't like you can just get the data back magically and without effort. It can be a problem to hunt through. It can be an enormous amount of data.

That's not immutability, that's "log shipping". And if an organisation doesn't have the capability to delete personal data from it's backups, and it operates in Europe, chances are that it's in violation of GDPR and could be subject to millions of Euro's in fines.

Slightly unrelated: A great way to solve this specific email issue is to store hashes for emails only, similarly to passwords. When a user needs a password reset, they should send an email to a specific address, the sender mail is hashed, and if it matches a user, a password reset link is replied. That way a data breach can’t expose user emails.

(This is what Tildes use: https://tildes.net/settings/account_recovery , and I found it very genius)

This works great for password resets.

It does not work if you actually want to send emails, for instance notifications.

That’s true, though many websites would be much better off if they would properly evaluate the question of do they actually need to send those?

That's an added bonus to the user in most cases!

While it's better than storing emails in clear-text, nobody uses vvkcbrxrsewf@gmail.com. Brute-forcing emails is going to be pretty effective. And if you have another e-mail database (which are plenty available in the open wild), I bet that success rate will be huge.

I like Apple's approach to generating random throwaway emails.

You don’t have to use the same salt for each email, right? You can hash each email with a separate, random salt and “brute force” upon password recovery. That way while individual emails can still be restored, you can’t easily get a database of emails to mass-spam, which is the point (emails are not really private completely, anyway).

I also like apple’s method, but it is not either-or.

This is probably worth doing, but one should be aware that a typical e-mail address does not contain enough entropy to not be guessable offline.

Perhaps someone should deliberately leek a bogus database that contains the bcrypt hash of davey.jones.490@well-known-email-provider.com and see if that account starts receiving spam ... probably it won't.

There is (almost certainly) no associated e-mail account, but the following is the SHA-256 hash of firstname.lastname.threedigits, something like "davey.jones.490". I'm half-expecting someone to reply with the solution, though I think there are about 36 bits of entropy in my choice:

b8b6395265714064afb525f37e9969a7dbf09a4a2afa28812e917580be2dc667

Forgetting is a separate operation that can be (relatively) easily added whereas updating existing system to work w/ immutable data is an effort on a whole another scale.

I'll argue that this is bad design. It works as long as the amount of data is small, but even then taking a low-data scenario and building lots of views or triggers just seems a bit weird. When I do that, it works for a month and then falls over in maintenance because you've got a table-based database where none of the important data is in a table?! This is not a design that will be flexible if needs change even slightly.

Immutable design is extremely powerful, but it needs to be a first-class citizen to get full benefit. Clojure's data structures are a great study in this - they squeeze a shocking amount of efficiency out because they have guarantees that the underlying data is immutable (eg, copy-and-slightly-update a large object is effectively a free operation, we have old & new objects available for comparison and that is lovely). Mimicking the same style of programming in, say, Java would gain none of the performance advantages or the logical conveniences. I expect it would be an uncomfortable programmer experience.

Here I think it would be more effective to design the tables as-usual and keep a log of all the changes separately. There is a chance the log will get out of sync with the active tables but frankly if that is a problem go use something designed with immutability in mind and don't twist PostgreSQL into pretzel shapes.

It's ironic that postgres the underlying storage model for postgres are immutable tables that marks records as "invisible" and asynchronously cleaned up.

Yeah. Immutable design is better, what more is there to say?

Unfortunately Postgres doesn't have an API to get at those well designed internals. It implements SQL.

Old but relevant discussion about pulling out the MVCC data from Postgres to mimic Datomic: https://news.ycombinator.com/item?id=4448189

>what more is there to say?

That it’s not better. Immutability is a tool, not a rule. Deploying immutability unanimously without regard for anything is a great way to create a terrible application.

It’s a mistake to use timestamp for sorting versions instead of revision_id imo

What case would you make for using revision_id?

I'm not OP, but I can at least give an argument against using a timestamp for sorting versions: for various reasons system time is not guaranteed to be monotonic, which can result in cases where newer versions would be sorted before older versions.

See https://www.erlang.org/doc/apps/erts/time_correction.html#in... for more info. It's about time handling in the Erlang run-time system, but the issue it describes is universal.

And having an Erlang background, I would say timestamps are also tricky in the context of distributed systems.

This is great, thank you. I’d never considered using time stamps like this before which is why I was curious, but I suppose now I can continue not using them slightly more confidently. What is programming really, but figuring out what not to do.

E.g. timestamps may (slightly) differ if the updates occur in different transactions, while the revision ID is likely passed around, and is easier to correlate.

This is relatively rare though.

It does seem like it would be rare, but it’s a valid concern. You can arguably gain any insights that time stamps offer by including them as metadata or in an audit log, right?

I find date and time data so difficult to work with that I typically avoid using it for anything important unless absolutely necessary.

Evergreen article that neatly lists the problems with time (not just as a version key): https://infiniteundo.com/post/25326999628/falsehoods-program...

Total ordering. Database guarantees monotonicity.

(1) You will benefit immensely in the applications built from these sorts of structures by keeping the version log for an entity in a separate table from the current value. First because you can just use old-school triggers, “any update over here triggers inserting a new row into the corresponding versions table,” you don't need to even care about implementing the versioning in application logic. Second because the sorts of queries that would involve looking at previous versions tend to not be the same as the sorts of queries for general data manipulation.

(2) To give an instance of where (1) becomes important, suppose you change some X to X' and then want to change it back. Suppose that after the change some entity was deleted—X foreign keys to a now-deleted value, X' does not. Most applications that try to shove both current state and history into one ubertable disable a bunch of constraint checking and other suchness, and permit this dubious feature of partially-rolling-back into an inconsistent state. But if you just DELETED the row when you said you had, then you would have gotten a foreign-key-error and your user would have copy-pasted you on their “unexpected error occurred” error message and you'd immediately be able to diagnose what foreign key constraint was blocking the undo, rather than mysterious failures several weeks later.

(3) Regardless of your stance on (1), once your application supports deletion, your relational integrity usually suffers because the technically correct value for all of the columns in a deleted-row is to make them all null. This is basically the problem that databases do not have sum types. A sum type in a database is not hard to create once you need it, create a row that has 3 columns which foreign-key to other tables, plus constraints that exactly one of these values is non-null. So the very lightweight construction if you are upset about denormalizing your data is for a Cat in your Cats table in your PetStore database to be a nullable pointer to a CatVersion. So that's how to proceed if you REALLY want to normalize.

(4) All of the above assumes that for every edit to an entity you will save a new row in the versions table, copying all of the other data. The problem is that inevitably some tables get super wide as they have to hold dozens of pieces of business data together, and it's never the ones that you initially expected. There is an easy fix for this as well, it is for versions to also be “mutable.” Whaaaaa??? Yes. Snapshots plus deltas. It's not really mutable because it's append-only.

Reply on news.ycombinator.com