Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: What's your preferred way of ensuring complex database integrity?

news.ycombinator.com

Post reply on HN
Print view View original

41–50 of 58 posts

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#41

Post by scarface74 »

chatmasta wrote: post #34
clavalle wrote: post #27

Earlier quoted context omitted.

>As mentioned by the other comment, it's standard to use database triggers to ensure consistency. shudder Triggers are almost always the wrong level to handle consistency. It flies in the face of 'fail fast' if you need to actually handle problems of consistency and if you need 'last line' protection, why not bake it into the structure instead?

Foregoing triggers in favor of validating data in the application layer can be dangerous. The problem is that you need to re-implement data validation on every path to the database. If you've got a single application server, and it's the only client that ever writes to the database, this might work. But as soon as you add another service with write capability, you need to re-implement the validation logic. What if yo…

But as soon as you add another service with write capability, you need to re-implement the validation logic.

Then Don’t Do That (tm)

It’s hardly ever a good idea to have multiple apps writing to the same set of tables directly.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#42

Post by scarface74 »

TotempaaltJ wrote: post #3

Generally my rule of thumb is to keep the integrity checking as close to the data as possible. Usually that means integrating it into the schema/database (optionally with triggers), as long as that has proper tooling (versioning specifically). With more complex checks it might be necessary to build a periodically runnable check mechanism. At startup might be a good way to do it, or some sort of cronjob. I can imagine…

I’ve never heard someone say as the database grows in complexity - “I’m so glad we decided to use triggers everywhere”.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#43

Post by scarface74 »

I keep as much logic out of the database as possible besides PKs/FKs etc.

I keep all of my logic in the application and only one “service” can write to related tables.

I also think that you should always write software as domain specific “microservices”. Not necessarily out of process, separately released microservices, but the Domain Driven Design concept of “services” that can just as easily be part of a monolithic project with different modules or if necessary shared between teams via versioned modules.

It is so much easier to deploy, rollback, and version code than databases.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#44

Post by chatmasta »

scarface74 wrote: post #41
chatmasta wrote: post #34

Earlier quoted context omitted.

Foregoing triggers in favor of validating data in the application layer can be dangerous. The problem is that you need to re-implement data validation on every path to the database. If you've got a single application server, and it's the only client that ever writes to the database, this might work. But as soon as you add another service with write capability, you need to re-implement the validation logic. What if yo…

But as soon as you add another service with write capability, you need to re-implement the validation logic. Then Don’t Do That (tm) It’s hardly ever a good idea to have multiple apps writing to the same set of tables directly.

Why not? How is it any different than two web requests causing a multi-threaded application server to send two simultaneous writes to the database?

If you use triggers for validation, you can rely on your database's MVCC to resolve any conflicts between triggers. Note that this is significantly more performant and robust than the application trying to resolve, or even notice, those conflicts.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#45

Post by scarface74 »

chatmasta wrote: post #44
scarface74 wrote: post #41

Earlier quoted context omitted.

But as soon as you add another service with write capability, you need to re-implement the validation logic. Then Don’t Do That (tm) It’s hardly ever a good idea to have multiple apps writing to the same set of tables directly.

Why not? How is it any different than two web requests causing a multi-threaded application server to send two simultaneous writes to the database? If you use triggers for validation, you can rely on your database's MVCC to resolve any conflicts between triggers. Note that this is significantly more performant and robust than the application trying to resolve, or even notice, those conflicts.

Don’t do that, was in response to:

But as soon as you add another service with write capability, you need to re-implement the validation logic. What if you forget?

In context of application validation logic, the logic is only living in one place.

Triggers are basically “spooky action at a distance”.

It’s also much easier to promote, version, upgrade, and rollback application changes.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#46

Post by combatentropy »

I'm moving more and more stuff into the database. Let me clarify my job:

- I use Postgres. This is important. Postgres has long been one of the most stable databases. It has multiversion concurrency control and even lets you make changes to tables within transactions, which makes such changes easier. It has many features, like check constraints, triggers, procedural languages, and JSON, which makes it easy to put move more stuff to your database.

- I have shell access to Linux virtual machines. I don't have layers of frameworks. I use the psql command-line tool and can log in as the superuser, postgres, when I need to. So my use of the database is not inhibited in any way.

- I write internal business apps. They can have complex business rules, but it is not web scale --- though I would be comfortable serving hundreds of requests per second for tens of thousands of users with my set-up, which is just a single virtual machine. I'm being conservative, and it could probably handle 100 times that.

I began 14 years ago as most web programmers do, keeping their database as a dumb data store and writing everything in their middle layer. But I have found maintenance easier, and the overall codebase much, much smaller, by keeping my database in the know as much as possible.

- Are the values in a column supposed to be from a limited set of choices? Tell it! Put those choices in a table, and link the column to that table with a foreign key.

- Is a number supposed to be within a certain range? Is the date in a column supposed to be after a date in another column? Tell it, such as with Check constraints.

- Is the data supposed to be sorted or summed or transformed before printed on the page? Tell it! Put the gob of SQL into a view, so that all your middle layer does is "select * from view".

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#47

Post by 6nf »

crispyambulance wrote: post #8

Isn't that the primary purpose of transactions? I mean, every time you write to the database, you open a transaction, do you stuff and close it. It either works or it doesn't. If it doesn't, then it rolls back. Isn't that enough? I guess I am saying that for complex integrity checks, it should be the responsibility of the application's data layer. On the other hand what is meant by "complex"? Is it "complex" because…

Yea I agree with you - if your database only ever has one concurrent user (one app and one person using that app at a time) then it doesn't matter that much how you implement it, either in the DB or in the app. The benefit of doing it in the DB is that multiple apps or people using the same data concurrently can be easier to manage depending on the situation. But if it's a single user app? Whatever you prefer is probably fine.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#48

Post by 6nf »

jhanschoo wrote: post #4

As mentioned by the other comment, it's standard to use database triggers to ensure consistency. There are two major problems with dealing with them in the application side 1. You can no longer guarantee consistency. 2. In the case where you need to do some things procedurally so that your application can no longer make a yes/no query to determine if a constraint is violated but need to make use of intermediate resul…

Yea the 'correct' approach is often to to both:

- You check for consistency issues on the client-side UI level, and give the user instant feedback when something is not right.

- You also create a db trigger to enforce the rule in the database itself. This way you, as a developer, can write your code with the assumption that the data is gonna be consistent when it comes from the database, even if something went wrong on the client UI level at some point.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#49

Post by jhanschoo »

scarface74 wrote: post #45
chatmasta wrote: post #44

Earlier quoted context omitted.

Why not? How is it any different than two web requests causing a multi-threaded application server to send two simultaneous writes to the database? If you use triggers for validation, you can rely on your database's MVCC to resolve any conflicts between triggers. Note that this is significantly more performant and robust than the application trying to resolve, or even notice, those conflicts.

Don’t do that, was in response to: But as soon as you add another service with write capability, you need to re-implement the validation logic. What if you forget? In context of application validation logic, the logic is only living in one place. Triggers are basically “spooky action at a distance”. It’s also much easier to promote, version, upgrade, and rollback application changes.

There are consistency guarantees that it's possible that you can't ensure with just application logic.

Suppose you are running a booking system for ridesharing. When you want to lock-in a passenger's seat, it's possible that consistency can be violated when you do it at the application level: both A and B request a seat after a query returns that there is a seat available, then simultaneously lock-in their reservation after seeing stale data.

It's also naive to think that validation in the application is sufficient. For, say, a webapp, there should be three places where that occurs: at the client where you can provide the most meaningful feedback, at the service to catch most consistency errors and guard against malicious actors while you can provide meaningful feedback, and at the database level for the most perennial and slow-moving constraints so that concurrency and your own bugs don't fuck up your source-of-truth.

edit: come to think if it, it's possible to do it without triggers as long as you can still express the constraint as a query: append a boolean field to each table with a TRUE check constraint, and use a table query mirroring the constraint to populate that field on every mutation.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#50

Post by jhanschoo »

crispyambulance wrote: post #8

Isn't that the primary purpose of transactions? I mean, every time you write to the database, you open a transaction, do you stuff and close it. It either works or it doesn't. If it doesn't, then it rolls back. Isn't that enough? I guess I am saying that for complex integrity checks, it should be the responsibility of the application's data layer. On the other hand what is meant by "complex"? Is it "complex" because…

Here's an example of a "complex" constraint. You have a restaurant reservation system, and reservations are stored as time intervals. Each restaurant has a capacity, and you need to ensure that a booking does not make it so that the restaurant exceeds capacity at any point of time.

One way to resolve it with triggers is to have a stored procedure fetch all the records that intersect the new booking's interval, and procedurally count the number of concurrent bookings at each record's start and end times; the constraint would be violated if and only if the count at any point plus the pax of the new booking exceeds capacity.

Post reply on HN