3 ms·

Ever since the war of stored procedures, I've been very reticent to put any more logic than absolutely necessary into the database. The simple truth of the matt

by nurple 2y ago

Ever since the war of stored procedures, I've been very reticent to put any more logic than absolutely necessary into the database. The simple truth of the matter is that the DB server codebases are awful, complicated, places to develop this kind of functionality. I strongly believe these features should be handled at higher layers. At my last job, in fintech, we used application-level shard routing with each aggregate root served by a particular RPC service (users, accounts, transactions, etc). When one of these aggregate services were asked to pull data, they would query a central routing service for the server and shard the requested data resided in. Between them and the DB servers we had a cluster of pgbouncer instances to handle connection oversubscription and the routing of queries around DB instances in the face of failures or maintenance. While this was pretty effective, the work to manage sharded Postgres, DDL updates, caching, locating, and balancing data was still very complicated and error prone, and was the root of many production outages. I didn't design this system, but after a couple years leading a platform migration, which necessitated getting into all this at a deep level, I would do it differently. Instead of query routing and DB sharding, I would shard the aggregate root services themselves. Each "sharded" set of these instances would have a simple PG instance (with replica chain) behind it that knows nothing about any other set. At this point, instead of routing being done by the aggregate root services locating and querying data from DB shards on huge vertically-scaled servers, each set only pulls data from their dedicated DB. A routing facade is placed in front of the set of sets that sends requests from consumers to the one holding the desired data. With this architecture, the complexity of sharding and vertically scaling at the DB layer, and handling connection oversubscription with a query router like PGbouncer, just falls away. I would keep these sets, and their DBs, rather small to also reap the benefits of small datasets. One of the biggest issues we had with huge DBs is the time it takes to do anything; as an example, restarting replication in a failed chain could take many hours because of the huge amount of data, and any hiccups in connectivity would quickly overrun our capability to "catch" replication up and we'd have to fall back to zfs-send to resnap. A larger number of smaller DBs would not improve the total time needed to do something like backups or DDL mutations, but it would significantly reduce the time for any particular instance which reduces overall risk and blast radius of a DB failure. Another thing I think small DBs can help with is data locality, DB automation, and possibly making the instances effectively ephemeral. When your dataset is small, bringing an instance up from a hot backup can take on the order of a few seconds, and could allow you to schedule the root services and their DB on the same host. For geographical distribution, the routing facade can also send writes to the set serving that shard in some other region.

sgarland 2y ago
> Ever since the war of stored procedures, I've been very reticent to put any more logic than absolutely necessary into the database. Counterpoint: the fewer round trips to the DB you have to do, the faster your app is. My belief is that as DBAs more or less went away thanks to cloud providers and the rise of Full Stack Engineering, the quiet part that no one wanted to admit was that RDBMS are hideously complicated, and you really need SMEs to use them correctly. Thus, the use of stored procedures, triggers, advanced functions etc. went away in favor of just treating the DB as a dumb store, and doing everything in app logic. As more and more companies are discovering that actually, you do need DB SMEs (I’m a DBRE; demand for this role has skyrocketed), my hope is we can push some of the logic back out to the DB. I am all for VCS, automated migrations, canary instances, and other careful patterns. I’d just like to see less of treating RDBMS as a document store, and embracing the advantages of the tech already in place.
- nurple 2y ago
  I kind of agree with your assessment, it would even speak to the popularity of document DBs coming up around the same time. However, I would argue that the decline in deep RDBMS integrations is exactly because of their excessive complexity. Modern software development methods left them behind because their change velocity is awful. This is doubly apparent when you look at features like TFA is talking about, exactly because it's extremely complex to stand up, manage, and make changes to. I personally left in the era of ORMs exactly because it let me build systems in high-productivity languages and frameworks, but also because the cost benefit of RDBMS integration just wasn't there. The DB is absolutely the highest-risk place to put logic in any stack, if something goes wrong _everything_ breaks and it's the most difficult place to fix things. I don't know what the future looks like for DBs, but I think we'll continue to see them become even more transparent. Devs just want to persist entities, and integrating at the RDBMS level is a slog.
  - sgarland 2y ago
    I’m obviously biased because of my career speciality, but I love everything about RDBMS. I love mastering the million knobs they have, their weird quirks, and how if you use them as designed with properly normalized schema, your data will ALWAYS be exactly what you told it to be, with no surprises. To this end, “devs just want to persist entities” makes me both sad and frustrated. Everyone seems to default to “just use Postgres,” but then don’t want to actually use an RDBMS. If you want a KV store, then use one. If you want a document store, then use one. Don’t subject a relational database to storing giant JSON blobs with UUIDv4 keys, and then complain that it’s slow because you’ve filled the memory with bloated indices, and saturated the disk bandwidth with massive write amplification.

by nurple 2y ago

sgarland 2y ago

> Ever since the war of stored procedures, I've been very reticent to put any more logic than absolutely necessary into the database. Counterpoint: the fewer round trips to the DB you have to do, the faster your app is. My belief is that as DBAs more or less went away thanks to cloud providers and the rise of Full Stack Engineering, the quiet part that no one wanted to admit was that RDBMS are hideously complicated, and you really need SMEs to use them correctly. Thus, the use of stored procedures, triggers, advanced functions etc. went away in favor of just treating the DB as a dumb store, and doing everything in app logic. As more and more companies are discovering that actually, you do need DB SMEs (I’m a DBRE; demand for this role has skyrocketed), my hope is we can push some of the logic back out to the DB. I am all for VCS, automated migrations, canary instances, and other careful patterns. I’d just like to see less of treating RDBMS as a document store, and embracing the advantages of the tech already in place.

nurple 2y ago
I kind of agree with your assessment, it would even speak to the popularity of document DBs coming up around the same time. However, I would argue that the decline in deep RDBMS integrations is exactly because of their excessive complexity. Modern software development methods left them behind because their change velocity is awful. This is doubly apparent when you look at features like TFA is talking about, exactly because it's extremely complex to stand up, manage, and make changes to. I personally left in the era of ORMs exactly because it let me build systems in high-productivity languages and frameworks, but also because the cost benefit of RDBMS integration just wasn't there. The DB is absolutely the highest-risk place to put logic in any stack, if something goes wrong _everything_ breaks and it's the most difficult place to fix things. I don't know what the future looks like for DBs, but I think we'll continue to see them become even more transparent. Devs just want to persist entities, and integrating at the RDBMS level is a slog.
- sgarland 2y ago
  I’m obviously biased because of my career speciality, but I love everything about RDBMS. I love mastering the million knobs they have, their weird quirks, and how if you use them as designed with properly normalized schema, your data will ALWAYS be exactly what you told it to be, with no surprises. To this end, “devs just want to persist entities” makes me both sad and frustrated. Everyone seems to default to “just use Postgres,” but then don’t want to actually use an RDBMS. If you want a KV store, then use one. If you want a document store, then use one. Don’t subject a relational database to storing giant JSON blobs with UUIDv4 keys, and then complain that it’s slow because you’ve filled the memory with bloated indices, and saturated the disk bandwidth with massive write amplification.