Ask HN: Starting to love RDBMS-first design. Anyone else?

[thookipodu]

I have been working on my side project for the last 10 months. My initial focus was on language, frameworks (elixir, liveview) during the initial months. Designing tables in rdbms (postgres) was out of necessity. For the last 2 months, usage of Stored procedures and postgresql functions just clicked. The feel of working with raw data inside the database instead of running around ORM hoops felt very liberating. Just realizing loops and conditionals are made unnecessary by SQL and the fact that abstraction layers can be built using views is quite addictive. I have deleted much of my UI code that I painstakingly wrote in elixir to SQL and it feels good. Interested to hear if someone has went down this path. Would like to hear if there are any gotchas in this approach ?

[cpburns2009]

There's a few gotchas.

1. Stored procedures are not automatically in an SCM. It takes good discipline to make sure they're properly committed, and synchronized with your databases.

2. Don't try to do too much with one view or query in general. This has bitten me many times. Don't be afraid to split-up a query. That can simply be a few separate queries, or creating a temporary table that's built up over multiple steps. It's more work up front, but the increased performance can be necessary.

I've come to adopt creating temporary stored procedures in my processes when I needed them. I find that's a nice balance when complex processing is more efficient and convenient to do in PostgreSQL rather than a round trip to Python.

[taylodl]

Once you realize an application's primary function is providing data-driven visualizations, creating new data, updating existing data, or deleting existing data - all of which are data management functions - then you realize your data model and its handling are the most important parts of your system architecture.

Once you've crossed that bridge, you see that an RDBMS delivers two important capabilities: durably persisting the data and managing the data. There are other ways to achieve these capabilities, an RDBMS just happens to be very good for many use cases. The point, though, isn't the RDMS. It's about data. You're talking about Data-Oriented Architecture and Data-Oriented Design.

But let's stick with RDBMSs, since that's what you're currently enamored with. As you're proceeding building-out your application and all your tables that are going to be supporting it, what happens if you need to change those tables for any reason, like say performance? Since your application is issuing queries against those tables it now needs to be modified and tested. Not good. Especially if, as time goes on, there are a suite of applications built around that data. Now you have to coordinate schedules across several teams, etc., etc., etc.

The mistake being made in this scenario is the application is tied directly to the physical data model. One common way people resolve this problem is using APIs. Several applications can use the same API and if the physical data model changes, just change the API implementation and you're good to go. But that's not the route you want to take. You want to work with the RDBMS. Do we have another option?

Yes! Materialized views! Materialized views comprise your logical data model and should be thought of as the application interface to the RDBMS. The materialized views manifest all the Join logic, all the foreign keys, all that stuff. All your application has to do is Select from the materialized view. For all the application knows, a single Select from the materialized view may result in half a dozen Joins behind the scenes. The application doesn't know, and it doesn't care. That logic is kept out of the application and is pushed onto the RDBMS. If the underlying physical data model has to change, then the materialized view implementation needs to be modified. The application is not affected.

Materialized views offer other advantages as well. You have a complex query that needs a DBA to tweak the query? The materialized view has the tweak. All the applications using that view have no knowledge or care. Materialized views are far more performant than an API. Need to pull a lot of data? I mean a lot? APIs are a horrible way to go. An RDBMS? Easy-peasy! Especially when using a materialized view! Materialized views are also pre-compiled by your RDBMS query compiler - so they run faster and won't blow out the cache on your query compiler. In fact, other users who may be executing ad-hoc queries against your database won't cause the performance of your queries to suddenly start degrading in performance because their compilation has been blown out of the query cache. You don't have to employ the CQRS pattern to maintain your performance!

But here's the best thing: both Oracle and PostgreSQL (those are the RDBMS engines I use and am most familiar with) allow Updates/Deletes via a materialized view! That means while the materialized view is the RDBMS' means of delivering data to applications, applications can view that logical data model as being the data model. They can do their Select, Update and Delete through the materialized view. Put your materialized views in a separate schema from the one containing your physical tables and only allow the applications to access the schema containing your materialized views. Boom! Applications can no longer directly access the physical data model!

I've been using this approach for the past 15 years on several mission-critical systems for Fortune 200 companies. It works well, and keeps you focused on what's most important - the data!