Ideas to improve the user and developer experiences of databases | Hacker News Reader

Ideas to improve the user and developer experiences of databases

dnlhg.com

89 pointsdnlhg55 comments

I want a branch-able schema. Migrations aren't a great for that. If you switch branches on your codebase, you'd need to revert the migrations back to the common ancestor, then apply the migrations in the branch. I'd like schema branching that is as easy as git branching.

Interesting, that would imply merge-able schema (and the ability to resolve merge conflicts in data too).

This author is spot on.

Raise your hand if you've committed the PostgreSQL to memory for looking at the DDL for a table, or identifying slow queries. Too many database management operations require highly specialized knowledge about a given database's internals.

Folks are far too willing to spend huge money on expensive licenses for db analytics tools to tell them when queries are slow or suboptimal.

Love the idea of making the db responsible for migrations. Downside there could be that db lock-in becomes a concern, and it might add cognitive overhead connecting application code to the current schema. How do you know if app code is up to date with the db without a live connection to prod?

Why would I want to have the examine DDL or slow query query committed to memory? I need to know that the concept exists and I can google the syntax in 90 seconds. I’m never within 90 seconds of disaster avertable with this knowledge.

Of course anyone can look anything up. The point I think the author is making is that the database is better positioned to tell us about potential performance issues. Would be wonderful if we could avoid reading through slow query logs, or paying big money for APM tools, if the db exposed more user friendly information about perf issues or other useful metadata.

The UX of 'damn I have to look that one weird query up again' to look up some metadata is not as good as it could be.

You dont, you just have so many times that it happens (me.)

> Downside there could be that db lock-in becomes a concern

Lock-in is often mentioned as a downside to tech, but in my experience the budget never exists to change tech past a certain point anyways.

Honestly it always seems like an argument for better upfront planning, to me. Start out by assuming you're going to be locked-in to your choices forever, and make those choices with care.

The main thrust seems to be tooling in the db to give developers some idea of the performance impact of the code they write.

But most programmers are using frameworks and ORMs and things that hide away what is actually happening with the database. A normal developer can look at a chunk of code on their side and have no real idea of what is happening on the database behind them.

What webdevelopers need is backend profiling information e.g. actual SQL queries and timings, shown beside all the frontend profiling information (e.g. in webdeveloper tools in the browser).

This profiling introspection can come from tooling in front of the database(s).

I recall seeing a blog about how the Stackoverflow team have nice tooling that does this for them. (Impossible to google for words like "stackoverflow profiling" and things!)

what we need is to kill ORM stile framework (think hybernate) and move the world toward mapper style frameworks (think mybatis)

sure, inserts are slightly more inconvenient. but the clarity and performance control on reads unparalleled.

You could easily instruct the orm to add a comment in front of the sql query so the database can profiling of a complete http request and show you all the bad things your orm is doing.

Something like: /* dbxperience:request=9a7cd2a6 */ SELECT ....

I have not tested it, but this is something google cloud sql recently promoted: https://cloud.google.com/blog/products/databases/get-ahead-o...

"Easily", not sure that is always easy to do. Is a clever approach to be sure, but to the author's point there could probably be better UX.

You don’t need to go this path; modern(-ish) APM systems like Datadog will let you trace SQL commands in the context of the request being made. You can group by endpoint, or group by sql (merging IDs that differ), or chop and filter the data however you need. The DB is not in a position to give you all the stats you need; instead the DB should support e.g. exporting plan info to further enrich the whole-request metrics that are already being collected.

I use this in prod; it’s great. Expensive though.

https://www.datadoghq.com/blog/mysql-monitoring-with-datadog...

I've used Datadog but if you need per query tracing from the database logs, I think the only way is to embed a trace ID in a SQL comment (or in an no-op column) like the grandparent showed.

Datadog is tracing the request from the application akin to something like

    openTrace("Query Name", queryStr)
    db.Query(queryStr)
    closeTrace()

The gap with that style of application-level tracing is that the database logs give no indication of where a query came from, hence the need for embedding a comment in the query with the trace ID.

I would love for a better mechanism than SQL comments for distributed tracing all the way to the database.

I guess what I'm getting at is questioning why you need to look at the database logs (beyond what your APM system can pull from the DB's metrics).

What are you getting from the DB logs that you can't get elsewhere?

Sure, off the top of my head:

- Explain plans for slow queries, via auto_explain in Postgres. You could get really fancy here and convert the pieces of an explain plan (Aggregate, ModifyTable, FullTableScan) into proper distributed traces. Hard to tell, but it looks like GCP might offer that.

- Various errors logs associated with queries that require more detail than a SQL state code. For example, lock timeouts.

- Debugging, especially root cause analysis when you're trying to figure out why something broke. The trace IDs help build up context.

Re: Stackoverflow and showing profiling information on the front end, you are likely talking about Miniprofiler.

https://github.com/MiniProfiler/dotnet

Apollo's GraphQL server has a VSCode plugin that shows you the average response time of a query. This type of tooling is difficult to implement but I think can be super useful.

https://marketplace.visualstudio.com/items?itemName=apollogr...

I’d like a tool that automatically suggests which indexes to add/remove, including use of different types of indexes and partial indexes, based on the queries being performed. Even better if it can automatically test the results of that on a production workload.

Or suggest schema changes that would improve normalization or performance.

RavenDB (1) got you covered but it's a document database. It automatically creates indexes and has a lot of built-in features to improve operability.

1 - https://ravendb.net/features#indexes

The Azure SQL database services are pretty nifty in this regard.

I wrote a "linter" for database (postgres) schemas. It doesn't currently say anything about indices but I am looking into that because that seems like something that you could make qualified guesses about.

[link redacted]

> I’d like a tool that automatically suggests which indexes to add/remove, including use of different types of indexes and partial indexes, based on the queries being performed

The data to do this is built into Microsoft SQL Server, and the open source sp_BlitzIndex does exactly what you're asking for.

If you're using Postgres, check out dexter[0].

[0]: https://github.com/ankane/dexter

I played around making a stupid thing a bit like sqlfiddle but it would animate the executuon plan to visually show how rows were joined etc. (Say showing 10-20 rows of data at each stage), could also show stats for full dataset being processed.

I thought it would be handy for those learning sql, but i dont think its a pro tool.

In anycase i am mainly posting this comment as it seems a simple thing to do but ive never seen another tool that does it.. maybe if i had taken the project a few steps further i would have found a reason its a terrible idea.

I also played around with this. I wrote a little Postgres pooler/proxy in Go that used the actual Postgres frontend code hacked in as a library to parse queries into an AST. I registered the pgfiddle.com domain.

I lost interest in it though. I'm working some of the better things into a new project of mine, sqljoy.com (nothing there yet.) I've been developing it for over 8 months.

RethinkDB hit many of those spots. Schemaless, built-in management interface, metrics, sharding, streaming...

The closest thing right now is ArangoDB but it seems to swing too hard in the other direction (a boatload of features including a built-in web server).

Schema less causes more problems that it solves. You still have an implicit schema, it's just now scattered all over your app.

That sounds a lot like Fauna.

I opened this article, ctrl+f'd for "branches" and was disappointed.

My #1 feature request from PostgreSQL would be a way to branch the database easily with my git feature branches that is smarter than keeping N copies of everything and syncing them as needed.

A couple things i'd love to have:

* native partitioning

* something like Materialized (where it's easy to specify data that is derived or denormalized).

Regarding the thoughts on migrations, which I 100% agree with, one strategy for cutting down the steps in a migration is to make the application compatible with the schema both before and after the migration. This can either be done by checking the migration sequence number, or by detecting the actual schema change. Using the author's example, where the migration involves a transition from an old table to a new table, you would make the application able to read/write using any combination of the tables. Then you can run your migration and/or move data. Later on, clean up the conditionals in the application code.

---

And on a different topic:

The Java SQL/database library, jOOQ [1] comes with a code generator that allows you to generate Java classes corresponding to your schema. This is pretty cool because it enables type-safe query building. It's a bit like connecting Java's type system to the database's type system. I find this to be really useful for ensuring correctness.

And if you're taking the approach from the first half of my comment, you can generate code any versions of the schema you need in the application.

[1] https://www.jooq.org/ jOOQ is really cool for a lot of reasons. The code generator is just one piece of it. For example, it can be used to translate between different vendors' dialects.

I big red background when you are connected to production. I know it's bad practice, but don't pretend it doesn't happen.

I never liked it enough to use it regularly, but this was something I really appreciated about DBeaver. You tell it if a connection is prod, and then it adds red borders and such, very visually distinctive.

I also appreciate pgcli asking for confirmation when running a destructive command.

Lambdas would be nice, as the author says there are external options. Postgres has the notify function that lets you build this sort of thing.

https://gist.github.com/colophonemes/9701b906c5be572a40a84b0...

Thanks, I didn't know about this.

I've been trying in vain to find something similar for MySQL. I think Lambdas are the killer feature that's missing from databases, you'd basically be able to handle all cache-invalidation / notification systems etc easily from the database layer, it would drastically simplify large numbers of common CRUD web-app problems.

The problem with notify is it can miss notifications if the connection drops (e.g. you restart the application process, server, or database), and I think also if there are too many notifications.

It's only really useful for things that should happen, not for things that must happen.

For me I wish the language was more standardized between vendors.

It would be nice if I could run my mssql statements against sqllite for quick testing.

You don’t have to go deep before incompatibilities with ansi sql... top/limit statements.

It would be nice, but at the same time, running mssql in a docker container is a thing now, and it goes pretty far towards solving those issues, at least for my use cases.

>You don’t have to go deep before incompatibilities with ansi sql... top/limit statements. Neither TOP nor LIMIT are ANSI. We didn't get syntax in ANSI SQL for constraining rows until SQL 2008, with FETCH FIRST N ROWS. You could do it in SQL 2003 with window functions, but that was a bit wordy.

NewSQL is focusing on improving the infrastructure of databases.

I want NextSQL to be about application development. Versioning, audit trailing, soft-deletes, privacy and data retention.

I like the article but I wish the author mentioned more real-world solutions for comparison.

Thanks! I didn’t want to bring up existing tools unless I knew them in enough detail be thorough and accurate. That said, Vitess can apply query limits as described under Insights. Migrations seem to have dozens of solutions from home-grown to enterprise. Not sure about reducing deadlocks, I’ve seen academic articles in passing that I don’t think have been commercialized yet. Lambdas are common with NoSQL databases like Firebase and DynamoDB. Supabase is working on a Functions feature which is interesting because it’s on Postgres, but they haven’t released many details.

What I’d really like to see is how the combination of features could be more than the sum of its parts.

The author doesn't state what RDMS he's used, but I think Microsoft's SQL Server Platform covers a lot of these, and third party tools, such as Redgate's amazing suite of DB tools, fill in a lot of the gaps.

Which of those ideas does it cover? I’m working with SQL Server as well, and I’d say none.

I think TerminusDB is interesting in this space. https://terminusdb.com/

You are correct!

Better support for hierarchical data structures. You can dump in schemaless json, but then loose a lot of the benefits of relational databases.

This, plus some form of sum types would be amazing to have imho.

I'm worked on the side in a relational language (https://tablam.org) and have used FoxPro, that in a lot of ways is superior to any RDBMS of today.

The ideas here are very good! But exist many other things that could have a greater impact:

1. We need an "wasm" for sql.

SQL is not a good language to transpile to.

ALL ORM ARE TRANSPILERS!

The relational model allow to do so much with so little (you don't even need to create a foreign procedural language if you add basic programming constructs). In FoxPro, you DB lang, your query lang and your programming lang was one and the same.

Much better.

Now today this could not fly -for some-, but a "wasm/llvm little" tailored to rdbms could be a very good idea.

Is exactly what things like GraphQL are, sadly, GraphQL is not made for DBs and is a hostile target for them.

1a: "But who will replace SQL, that is nuts!"

This is the major excuse. But things like graphql show is possible.

The "trick" is that this new query lang is SERVER SIDE. Developers will use it if is nice, and the major work is on libraries.

Solve this is not as hard as people think. In fact, is done MANY TIMES BEFORE!. But most not see it because not understand that what we are doing with SQL is making ad-hoc compilers.

And is done to JS with wasm. If is possible to do it for JS, sql is piece of cake.

2- We need Algebraic types: eliminate NULLS from their last bastion

Have algebraic types as first class will be a very good improvement here. No more null shenanigans, and will match what we know as today as best practiques.

Plus, will allow to fulfill better the ideal of a DB of "data modeling" of business requirements.

3- The auth support of RDBMS is for a bygone era.

The article show it, but the truth is that "nobody" use the auth support of rdbms outside some niches. A modern rdbms only need the equivalent of JWT and the ability to do custom auth checks. This will allow to actually plug the auth that is tailored to the app via:

4- We need "sql install simple-auth" aka: Package manager

As I said, I use fox pro. it was a full feature programming environment. This mean I have frameworks/libraries that I could distribute or integrate. A DB can benefit to install packages and declare its dependencies like with rust.

This could be great for example, to install a well vetted auth support for the db, or logging, or tracing, or extra functionality like fts.

And how about use wasm as the binary interface?

String parsing is not an appreciable part of the query execution cost. I'm not sure what the goal here would be otherwise?

SQL, alike JS, is not a good target for the myriad of languages(and orms) than target it, and it have intrinsic limitations that are historical artifacts.

Is not the cost of string concatenation, is to provide the benefits of a good byte code that could allow, for example, type check input, schemas and other stuff on client side before touch the db. Is similar to how GraphQL unlock great tooling because is a specification that was designed for be a target of said tooling.

Yeah, that would be nice to have.

My dad used to say that, FoxPro is the best in MSDos and visual Foxpro killed it and it was so fast in handling large amount of data.

Being able to write FROM table SELECT xxx instead of SELECT xxx FROM table (just like with relational theory, and to have autocompletion that works) would be just great!

no more SQL strings.

Wow so cool

Reply on news.ycombinator.com