Comment by paffdragon | Hacker News Reader

Comment by paffdragon | Hacker News Reader

Parent Full thread

> the interface being consumed is the database, which you do not need to design or implement

You absolutely should design and implement it, exactly because it is now your interface. In fact, it will add more constraints to your design, because now you have different consumers and potentially writers all competing for the same resource with potentially different access patterns. Plus the maintenance overhead that migrations of such shared tables come with. And eventually you might have data in this table that are only needed for some of the services, so you now need to implement views and access controls at the DB level.

Ideally, if you have a chance to implement it, an API is cleaner and more flexible. The problem in most cases is simply business pushing for faster features which often leads to quick hacks including just giving direct access to some DB table from another service, because the alternative would take more time, and we don't have time, we want features, now.

But I agree with your thoughts in the last paragraph. It happens very often that people don't want to undertake the effort of a whole new design or redesign to match the evolving requirements and just patch it by adding a new table to an existing DB, then another,...

> In fact, it will add more constraints to your design, because now you have different consumers and potentially writers all competing for the same resource with potentially different access patterns. Plus the maintenance overhead that migrations of such shared tables come with. And eventually you might have data in this table that are only needed for some of the services, so you now need to implement views and access controls at the DB level.

PostgreSQL, to name one example, can handle every one of these challenges.

It's not that it is not possible, but whether it's a good idea.

The usual problem is that some team exposes one of their internal tables and they don't have control over what type of queries are run against it that could impact their service when the access patterns differ. Or when the external team is asking for extra fields that do not make sense for the owning team's model. Or adding some externally sourced information. Or the team moving from PostgreSQL to S3 or DynamoDB. And this is not an exhaustive list. An API layer is more flexible and can remain stable over a longer time than exposing internal implementation depending on a particular technology implemented in a particular way at the time they agreed on sharing.

This is, of course, not a concern inside the same team or very closely working teams. They can handle the necessary coordination. So, there are always exceptions and simple use cases where DB access works just fine. Especially, if you don't already have an API, which could be a bigger investment to set up for something simple if it's not even known yet the idea will work etc.

> Plus the maintenance overhead that migrations of such shared tables come with.

Moving your data types from SQL into another language solves exactly 0 migration problems.

Every migration you can hide with that abstraction language you can also hide in SQL. Databases can express exactly the same behaviors as your application code.

I’m generally pro SQL-as-interface, but this is just wrong.

Not only are there all sorts of bizarre constraints imposed by databases on migration behavior that application code can’t express (for example, how can I implement a transaction-plus-double-write pattern to migrate to use a new table because the locks taken to add an index to the old table require unacceptably long downtime? There are probably some SQL engines out there that can do this with views, but most people solve it on the client side for good reason), but there are plenty of changes that you just plain can’t do without a uniform service layer in front of your database. Note that “uniform service layer” doesn’t necessarily mean “networked service layer”, this can be in-process if you can prevent people from bypassing your querying functions and going directly to the DB.

Either triggers or create index concurrently? Do most people solve that on the client side? Doesn't e.g. percona use triggers?

As the sibling comment mentioned, this is a solved problem. MySQL and MariaDB take a very brief lock on the table at the very end of index creation that you will not notice, I promise. Postgres does the same if you use the CONCURRENTLY option for index builds.

If for some reason you do need to migrate data to a new table, triggers.

If somehow these still don’t solve your problem, ProxySQL or the equivalent (you are running some kind of connection pooler, right?) can rewrite queries on the fly to do whatever you want.

> this is a solved problem

It really isn't. The addition of an index was an example, and a relatively straightforward/simple one, of general schema migration pain. And even index additions aren't a solved problem: CONCURRENTLY (postgres only) and other affordances don't work in every case, as their documentation abundantly illustrates--what about overlapping indices, foreign key "inherited"/implicit indexes, UNIQUE constraints, and so on? Even in postgres with a DDL expert on hand, things get hairy fast.

In MySQL/maria, that "very brief lock" at the end of index creation can turn out to be anything but, if the table is high-write-volume, since InnoDB locks aren't FIFO--at least, not in the way people expect them to be.

And again, that's a simple case for which mitigations exist! More complex schema migrations hit immense amounts of trouble at even medium sizes of data and query volumes.

Online-schema-change tools that use triggers/cutovers are extremely limited: not only do they take locks to set up and manage their triggers/replication systems internally (meaning that they can cause severe disruption, or fail to migrate, in the face of continuous $thousands/sec of writes to a table), but their underlying mechanism is also often one of duplicating a table entirely, bulk-copying data from old to new, using triggers to stream changes during the migration, and then cut over. That mechanism is flawed for large tables (2x the data might not fit), flawed for high write volumes (2x the update operations is an unacceptable latency cost in plenty of circumstances), and just plain risky (that final cutover/drop/rename to point things at the new table is and probably will always be extremely lock-ful).

To be clear,the availability of advanced database facilities like CREATE INDEX ... CONCURRENTLY, triggers, and online-schema-change tools are all good things! My point is that this is very, very far from a solved or painless problem space--one that starts to become quite painful at even moderate scale.

> Note that “uniform service layer” doesn’t necessarily mean “networked service layer”, this can be in-process if you can prevent people from bypassing your querying functions and going directly to the DB.

You can take it one step further and implement the “uniform service layer” in the database itself - using stored procedures and views.

This has downsides, like strong coupling with the specific DBMS, and difficulty of development in a comparatively primitive SQL dialect, but protects the database from “naughty” clients and can have tremendous performance advantages in some cases.

Reply on news.ycombinator.com