Nowdays we can have json APIs in relational databases (at least in Oracle and SQLServer). These APIs allows reading and writing of single value in nested level too. I think at least check-points could have been avoided.
Wrong ways to use the databases, when the pendulum swung too far
81–90 of 110 posts
Re: Wrong ways to use the databases, when the pendulum swung too far
#82> The checkpoint system worked like this: every time we needed to perform a write, we would generate a UUID, store this UUID into a “checkpoint” for the current flow.... >Logically, that was… fine. In practice, writes into the same database which previously required 5 IO round trips, now required almost double the number of trips for the extra checkpointing operations... So this sounds like trying to invent a form of…
The KV store had etag support for conditional writes. Etags are only useful to make sure the data didn't change underneath between your read and your write. Storing the checkpoints along with the mutation was for idempotency. If the checkpoint was in the document, that meant the mutation had succeeded and a retry should be no-op
Hm, I misunderstood, I thought the checkpoint system was also concurrency control to make sure nobody else had changed it from underneath you between read and write, since you had to read and write the whole (mega) document even though you only wanted to change a part (sub-document).
Doesn't the KV provide idempotency on it's own -- so long as you're checking that no changes have happened between read and write, why wouldn't doing the same write twice produce an idempotent result? A change happenening between read and write seems the only reason that would be a problem.
But clearly it's complicated and we don't have the whole picture as to business needs. Definitely sounds awful.
Re: Wrong ways to use the databases, when the pendulum swung too far
#83Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when re…
I am not going to dismiss your experience here. Stored Procedures can turn into wild monsters. Pair it with Triggers and you are in for chasing a noodle. But it's also a reality, that relational databases often become the main integration point in companies. In those environments it’s hard (next to impossible) and dangerous to use something like ORMs. Often enough I don't "own the tables" and I don't "own the columns…
That pattern, databases being the main integration point in companies, is returning with data lakes like snowflake, databricks, and ducklake (poor man’s snowflake). Where better to get your integration data than in a unified, quality controlled, central location. No need to call multiple services, no need to unify different data models.
Re: Wrong ways to use the databases, when the pendulum swung too far
#84Earlier quoted context omitted.
How many DBAs have you dealt with that only give you SELECT and EXECUTE? That seems somewhat crazy. It doesn't cut down the surface area for bugs at all (the same amount of SQL is still required and it'd be the same code as the app was going to execute anyway as a normal query). What scenarios are they worried about here?
I have seen many actually. Imagine having a huge amount of applications all using the same database, which is, like I said, often enough the main integration point in a company. The applications you are maintaining are written in, let’s say Java, C#, Python and C++. They are too important, too large and a rewrite is way too risky. How would you start to get this under your control? By moving common logic in a shared…
Same here, usually no direct access to tables, read access only via views, write access only via stored procedures, to ensure data is always validated in the same way. The stored procedures are basically just database deployed APIs, don’t see the big problem with this, forces you to consider your data as schema as a data service.
Re: Wrong ways to use the databases, when the pendulum swung too far
#85Not Dan Luu, is it? The site seems to leave that intentionally ambiguous.
Not Dan Luu. And to the other comment, not intentionally hiding my identity. Just that for most of my writing, who I am is irrelevant.
I sometimes wonder what it would be like to be named something like Madonna Tucci or Madonna Bianchi and be born in the late 60s, slowly coming to the realization, as you grew up, that your name would never be fully yours again; that, if you were to write a book or perform in a play as just "Madonna", half the audience would assume you were that damned blonde from New York.
Re: Wrong ways to use the databases, when the pendulum swung too far
#86Earlier quoted context omitted.
The KV store had etag support for conditional writes. Etags are only useful to make sure the data didn't change underneath between your read and your write. Storing the checkpoints along with the mutation was for idempotency. If the checkpoint was in the document, that meant the mutation had succeeded and a retry should be no-op
Hm, I misunderstood, I thought the checkpoint system was also concurrency control to make sure nobody else had changed it from underneath you between read and write, since you had to read and write the whole (mega) document even though you only wanted to change a part (sub-document). Doesn't the KV provide idempotency on it's own -- so long as you're checking that no changes have happened between read and write, why…
> why wouldn't doing the same write twice produce an idempotent result
you can imagine this:
```
var thing = KVStore.Get(...);
if (things.Checkpoints.Contains(myUuid) == false) {
thing.Counter += 1;
KVStore.Update(thing); ```
having an etag doesn't help you with retries, where we expect that `thing` could be mutated by another flow between your retries.
Re: Wrong ways to use the databases, when the pendulum swung too far
#87Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a…
Most teams that “use raw SQL” end up accidentally writing ORMs anyway. You have a bunch of similar SQL queries so someone writes a SQL generator. Someone notices that if we are using one field from the account table, then we usually want the others, so they create an Account object and share it to reduce duplication.
You’re usually better off starting with a good standard ORM at the beginning.
Re: Wrong ways to use the databases, when the pendulum swung too far
#88Earlier quoted context omitted.
I'm convinced that the solution to stored procedures that people are afraid to update is automated tests . Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. For some reason stored procedures and automated testing seem to often not go togethe…
In good resource on testing stored procedures ?
I'd test them the same way I test other database stuff: run automated tests which spin up a database server for the duration, then run migrations to install the schema and stored procedures, then execute tests using your programming language of choice.
I still mostly use Django for PostgreSQL projects and the Django default test framework is great at running a temporary PostgreSQL (or MySQL or SQLite).
Re: Wrong ways to use the databases, when the pendulum swung too far
#89Earlier quoted context omitted.
I'm not hugely experimented, and SQL has always been enough for me, perhaps due to my simple requirements. But so far I hold the opinion that ORM is always a mistake. I find a lot of programmers don't know how to write an array of floats to disk, if you forced me to choose between an ORM or No DB at all, I would choose no DB all day. ORM feels like an abstraction on top of an abstraction. I don't trust that those who…
> ORM feels like an abstraction on top of an abstraction. I don't trust that those who chose ORMs have studied and exhausted the possibilities of the two underlying layers, I feel more often than not they recourse to higher layers of technology without understanding the lower level. I agree with that. However I feel that teams that choose not to use an ORM end up having one somehow reimplemeted by the seniors, and ju…
Surely it would be an ORM designed specifically for the business domain, instead of a generic ORM.
Re: Wrong ways to use the databases, when the pendulum swung too far
#90Earlier quoted context omitted.
I'm not hugely experimented, and SQL has always been enough for me, perhaps due to my simple requirements. But so far I hold the opinion that ORM is always a mistake. I find a lot of programmers don't know how to write an array of floats to disk, if you forced me to choose between an ORM or No DB at all, I would choose no DB all day. ORM feels like an abstraction on top of an abstraction. I don't trust that those who…
I know both and ORMs are fine. They save a lot of time at the cost of some inefficiency. However some of the newer ones are like Entity Framework Core for dotnet are significantly smarter than older models.
Is that time saved initially but paid later as tech debt? Or time saved on the long run?