Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ideas to improve the user and developer experiences of databases

dnlhg.com

Post reply on HN
Print view View original

41–50 of 56 posts

Re: Ideas to improve the user and developer experiences of databases

#41

Post by bluefirebrand »

planxty wrote: post #16

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…

> 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.

Re: Ideas to improve the user and developer experiences of databases

#42

Post by pier25 »

ricardobeat wrote: post #33

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).

That sounds a lot like Fauna.

Re: Ideas to improve the user and developer experiences of databases

#44

Post by claytonjy »

collyw wrote: post #7

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.

Re: Ideas to improve the user and developer experiences of databases

#45

Post by oftenwrong »

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.

Re: Ideas to improve the user and developer experiences of databases

#46

Post by sa46 »

theptip wrote: post #35
tpetry wrote: post #13

Earlier quoted context omitted.

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...

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 metri…

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.

Re: Ideas to improve the user and developer experiences of databases

#47

Post by eloff »

goodlinks wrote: post #9

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 neve…

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.

Re: Ideas to improve the user and developer experiences of databases

#48

Post by eloff »

mamcx wrote: post #32

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…

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

Re: Ideas to improve the user and developer experiences of databases

#49

Post by eloff »

xupybd wrote: post #4

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...

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.

Re: Ideas to improve the user and developer experiences of databases

#50

Post by mamcx »

eloff wrote: post #48
mamcx wrote: post #32

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…

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.

Post reply on HN