13 ms·

Making database systems usable

150 points by jamesblonde 2y ago

jamesblonde 2y ago
Prof Murat is calling for examples of how LLMs can help make DBs more usable. I note that Julius.ai is making data analytics easier - just upload some data and ask for some charts and it does an ok job. The problem of going from natural language to SQL is still a far from solved problem. The main benchmark for this is Bird-Bench and accuracy is only in the high 60s - https://bird-bench.github.io/ https://bird-bench.github.io/ I have been working on making tables queryable using function calling, where you a fixed number of canned queries based on the primary key and an event-time column. PyData talk on this - https://www.youtube.com/watch?v=dRcjTe5qgwM https://www.youtube.com/watch?v=dRcjTe5qgwM
- randomdata 2y ago
  > The problem of going from natural language to SQL is still a far from solved problem. Is there any improvement going from natural language to something other than SQL? Certainly SQL can be cut out of the picture if it is what confuses these systems.
  - zerodensity 2y ago
    What would that "something other" be? If the goal is to talk to a SQL database the output from the LLM would benefit from being SQL.
    - randomdata 2y ago
      > What would that "something other" be? Depends. Where is the problem? Is it the quirkiness of SQL? Perhaps something like QUEL or Datalog would yield better results? Is it declarative programming that it struggles with? GPT, for example, seems to be optimized for Python in particular. Perhaps an imperative approach is easier for the LLM to understand? It doesn't even have to be a language suitable for humans. Perhaps it would fare better with something like SQLite's byte code language? > If the goal is to talk to a SQL database While being able to talk to an existing SQL database would no doubt simplify the problem in a lot of cases, which is of merit, I doubt that is the actual goal. The user doesn't care about the technology, as they say. Getting the expected results out of the database is undoubtedly the actual goal. SQL as a target is all well and good if it works reliably, but the claim was that it doesn't. If some other target performs better, there is no need to cling to SQL. It is merely an implementation detail.
      - big_whack 2y ago
        I think the problem is the quirkiness on the English side, not the SQL side. You could translate datalog to SQL or vice versa, but understanding intention from arbitrary english is much harder. And often query results must be 100% accurate and reliable.
        randomdata 2y ago
        > I think the problem is the quirkiness on the English side While likely, the question asked if there was any improvement shown with other targets to validate that assumption. There is no benefit in thinking. > And often query results must be 100% accurate and reliable. It seems that is impossible. Even the human programmers struggle to reliably convert natural language to SQL according to the aforementioned test study. They are slightly better than the known alternatives, but far from perfect. But if another target can get closer to human-level performance, that is significant.
        yuliyp 2y ago
        When I find someone claiming a suspicious data analysis result I can ask them for the SQL and investigate it to see if there's a bug in it (or further investigate where the data being queried comes from). If the abstraction layer between LLM prompt and data back is removed, I'm left with (just like other LLM answers) some words but no way to know if they're correct.
        randomdata 2y ago
        1. How would the abstraction be removed? Language generation is what LLMs do; a language abstraction is what you are getting out, no matter what. There is no magic involved. 2. The language has to represent a valid computer program. That is as true of SQL as any other target. You can know that it is correct by reading it.
        big_whack 2y ago
        Once you have SQL, you have datalog. Once you have datalog, you have SQL. The problem isn't the target, it is getting sufficiently rigorous and structured output from the LLM to target anything.
- 7thpower 2y ago
  This was basically the only reasonable way I found to create a consistent user experience. I think of them as natural language BI, where you have canned reports to answer common categories of questions.
- jalcazar 2y ago
  Gemini generating SQL queries from natural language could be an example of AI making DBs more usable. There is more people speaking natural language than SQL https://cloud.google.com/bigquery/docs/write-sql-gemini#prompt_to_generate_sql_queries https://cloud.google.com/bigquery/docs/write-sql-gemini#prom...
  - jamesblonde 2y ago
    Bird-bench has gemini on 69.03% on the test set. That is a long way from something you can build on.
Spivak 2y ago
I think there's a huge difference between how you design: 1. A database that's meant to be understood by programmers, make queries by the application efficient in space and time, and provide strong referential integrity. 2. A database that's meant to be played with by humans, where duplication is fine, referential integrity is a nice-to-have, every column is okay to be nullable, tables should contain complete objects as understood by the user that map 1-1ish to the real world, that eliminate as many opaque ids as feasible, and foreign keys might exist but aren't enforced in any direction. The latter database is far more ergonomic and you won't run up against a user frustratingly bashing their keyboard because the database just refuses to do what they want. The stakes in #2 style databases are extremely low-- let the user destroy their copy of the database it's fine, we can reload from the last save. The nice thing is that it seems very possible to go from #1 -> #2 mechanically, and hand that off to the users who want to play with it.
- refset 2y ago
  The 4GL dream hinges on figuring out how to transition from #2 -> #1 seamlessly. The spectrum is wider and more complex than "Excel -> Postgres" but even solving that journey would be a good start. It could save us all from a whole a bunch of ~needless engineering work. > it seems very possible to go from #1 -> #2 mechanically, and hand that off to the users who want to play with it NocoDB seems like a reasonable attempt of taking this approach for Postgres.
  - feoren 2y ago
    > The 4GL dream hinges on figuring out how to transition from #2 -> #1 seamlessly I guarantee you there is no algorithmic way to do this. Going from "big flat file" to "nicely organized 3+NF schema" adds a lot of underlying domain knowledge and information to the system. You're asking for "enhance!" on the database schema. It will always require expert understanding.
- jiggawatts 2y ago
  Some technical design elements can enable this style of non-programmer usage that most databases engines do not currently support. First, treat each column separately in the physical engine (column store). Users ought to never need to worry about column count limits, row byte limits, or sparsity. Similarly, schema operators such as adding, removing, or reordering columns ought to always be instant atomic changes. Imagine how much faster it would be possible to explore the schema design space if changes were instant and didn’t require “migration scripts” or data copies from an old table to a new one. It would make the database feel more like a spreadsheet! Next, there ought to be Git-style forking and merging so that creating a test environment should also be a lightweight operation just like creating a local branch in a Git repo. Merging changes can be either schema only or schema+data -- the latter to support slowly-changing “master data” editing workflows. Currently, few if any database support the equivalent of a pull request with reviews to merge data. Hence the excessively complex access controls that could all be replaced with a single "peer review" operation. I’ve seen a fancy category-theoretic approach where forks of the database can receive live data updates from production. This would allow UAT and similar environments to be evergreen with zero infrastructure code such as nightly ETL sync jobs. Many operational tasks can be eliminated by tying live environments of both the code and data to Git branches of a single (mono) repo. Now the code and data schema can’t go out of sync! Literally impossible. No need for scaffolding, or ORMs, or any of those layers of overcomplicted abstractions! The schema is just “there”, in the repo, always representing reality. Sprinkle on a Microsoft Access style form designer but with HTML5 and guest user support and you have a billion dollar product.
trollied 2y ago
I think the core of this is the age old problem that people would rather invent crappy technologies that end up being a pain instead of taking some time to learn a standard, SQL. SQL is not going to go away. It's relatively easy to learn.
- morkalork 2y ago
  It's shocking how many developers are afraid of SQL, it hurts my soul.
  - codr7 2y ago
    I honestly don't get it, it's not rocket science, and compared to freakin MongoDB it's a Sunday walk in the park.
  - minkles 2y ago
    20+ years ago no one was afraid of it. Our front end web folk were doing SQL quite happily. Now they can't even consume an API without problems. I think the demand for staff has lowered standards considerably.
    - yunolearn 2y ago
      20 years ago (or more like 30-40), software actually did something for the user and wasn't just a ZIRP hack to make billions courtesy of Google and friends. Once the money became the entire point, once line go up, you just need to be able to fog a mirror long enough to get acquired. The number of supposedly senior developers who send me screenshots of stack traces they haven't even read makes me want to day drink heavily. I'm thinking of starting a bakery.
      - minkles 2y ago
        Yeah nailed it there. Totally agree. I was going to start a grilled cheese van!
- nine_k 2y ago
otoolep 2y ago
>They care less about impressive benchmarks or clever algorithms, and more about whether they can operate and use a database efficiently to query, update, analyze, and persist their data with minimal headache. Hugely important, and I would add "backup-and-restore" to that list. At risk of sounding conceited, ease of use is a primary goal of rqlite[1] -- because in the real world databases must be operated[2]. I never add a feature if it's going to measurably decrease how easy it is to operate the database. [1] https://www.rqlite.io https://www.rqlite.io [2] https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-OeMGoij22oMZ_xhyeRTrQnn6o/edit#slide=id.ge8e3d69953_0_35 https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-Oe... Disclaimer: I'm the creator of rqlite.
- otoolep 2y ago
  Or, I should say, I don't add the feature until I can figure out how it can made be easy and intuitive to use. That's assuming the feature is even coherent with the existing feature set of the database. Of course, it's easy for me to do this. I am not developing the database for commercial reasons, so can just say "no" to an idea if I want. That said, I've found that many ideas which didn't seem interesting to me when an end-user first proposed them become compelling once I think more about the operational pain (and it's almost always operational) they are experiencing. Automatic backups to S3[1] was such a feature. I was sceptical -- "just run a script, call the backup API, and upload yourself" was my attitude. But the built-in support has become popular. [1] https://www.philipotoole.com/adding-automatic-s3-backups-to-rqlite/ https://www.philipotoole.com/adding-automatic-s3-backups-to-...
  - nine_k 2y ago
    The distance between a large effort and a moderate effort is not very long; from the user's perspective, both things are in the realm of the Hassle. The chasm between a small required effort and zero effort is vast, from the user's perspective. Any product person will tell you that. Hitting the right zero-effort target is what separates a runaway success from a tepid reaction.
    - 2y ago
SoftTalker 2y ago
> You youngins may not remember, but pre-2005 we had to call a travel agent to book our flights and get paper tickets in return. This sucked, we don't have any transparency in to the process, we couldn't explore options (price, convenience, dates, airports) and customize our trip. Having access to flight booking via web was really a great improvement for user experience. I'm going to disagree. I could call my travel agent and say "I am going to Chicago on <date> I need to be there by <time> call me back with a couple of nonstop options. And I'll need a room at the Hilton and Towers for two nights." vs. today I can spend my own time navigating travel sites, avoiding their dark patterns, wondering if I'm really getting the best prices, making sure I understand the terms (is this refundable? are any checked bags included?) etc and then do the same for the hotel booking. If you work in a business with a travel department or assistants who can manage your travel you have an idea, compared to the hassle of doing it all yourself.
- ComputerGuru 2y ago
  > wondering if I'm really getting the best prices Curious why you assume you were getting the best price before?
  - nine_k 2y ago
    But "before" you did not have an option and thus the need to wonder! "One who has a watch always knows what time is it. One who has two watches is never certain about anything."
    - SoftTalker 2y ago
      Yes, the paradox of having too many choices. Sounds like a good thing, but it causes anxiety.
      - nine_k 2y ago
        Making a choice means making decisions. Making decisions can definitely be taxing, especially when yourself or your social circle insists that decisions were "good", "smart", or otherwise met a standard of excellence. Suddenly you end up in a situation where you need to prove yourself, else the smartass cousin or your own resentful self will constantly remind you how you played the fool when you could have made a much better choice, obvious in hindsight. An agent (human or machine) that chooses the tickets for you because you can't be bothered with such mundane trivialities may be quite desirable for some.
flowerlad 2y ago
> Users cannot interact with the database directly It is not super hard to find UI designed for end users, these days. If you know the basics such as what a relational database is, then here’s is a good UI: https://visualdb.com https://visualdb.com
- minkles 2y ago
  People have forgotten we had this back in 1997 with MS Access. I built a whole ERP system with it, single handedly including the hardware, software, networking and the workstation deployments without really breaking a sweat. We have gone off the rails somewhere.
  - flowerlad 2y ago
    Right but Access is outdated. Microsoft tried to make a web version of Access, but gave up. Now they recommend Power Apps but it’s support for databases is pretty weak.
    - minkles 2y ago
      It still works and it doesn't matter. Only the problem solved matters.
    - yunolearn 2y ago
      It's old so it's bad?
      - flowerlad 2y ago
        For starters it is not web-based, does not work on Mac, does not support LLM query generation, etc.
josephg 2y ago
Something thats always bugged me about relational database modelling is how you have to use table relationships for everything. Humans have a special category for ownership (eg Order owns DeliveryAddress), which works differently from other kinds of relationships. Eg Order references Products. This problem is heightened by the fact that a SQL table typically can't store composite types. Like, you can't make a column of lists-of-strings. Every modelling problem is solved by using more tables. And any nontrivial application ends up with a table explosion. You have to study your database very closely, then write complex, slow, join-heavy queries, just to get the information about simple stuff - like an order. Solving every problem with tables might seem clever from a technical perspective, but its just not how humans think. There's a reason document databases like mongodb are popular. Even if they are worse technically, they're much easier to use and reason about. There's no reason SQL databases couldn't support nested data. Postgres sort of does already via JSON fields. But it feels like you have to fight the database to do it.
- codr7 2y ago
  Easier to reason about in that they don't force you to clarify your thoughts, which will come back to bite your head off. And there's a pretty significant long term price to be paid. I would rather write Cobol than deal with MongoDB.
  - Spivak 2y ago
    This is all nice and good but I've never once seen someone correctly clarify their thoughts ahead of time that doesn't end up being the wrong model as the code evolves. The only schemas that survive long term are the ones that create such a tangled ball of foreign keys that migrations are infeasible. Great advice for anyone looking to leave a legacy.
    - codr7 2y ago
      At least they exist, and force you to refactor the code to match reality. Document databases tend to take on a life of their own; and since they encourage shortcuts, the slope is typically down.
pjs_ 2y ago
A huge fraction (not 100%, but maybe 80%) of my frustration in trying to get technical people to use a database is that they have such a hard time understanding JOINs. People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns. I'm actually sympathetic to that reticence but what I am not sympathetic about is this: why, in 2024, can't the computer figure out multi-table joins for me? Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. When I started using SQLAlchemy, I naively assumed that a trivial functionality of such a sophisticated ORM would be to implement `apple.join(house)`, automatically figuring out that `box` is the necessary intermediate step. SQLAlchemy even has all the additional relationship information to figure out the details of how those joins need to work. But after weeks of reading the documentation I realized that this is not a supported feature. In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that. I'm not a trained software engineer but it seems like this must be a very generic problem -- is there a name for the problem? Are there accepted solutions or no-go-theorems on what is possible? I have searched the internet a lot and mostly just find people saying "oh we just type out all combinatorially-many possible queries"... apologies in advance if I am very ignorant here
- rawgabbit 2y ago
  Most databases have the concept of foreign keys. You declare how the tables relate to each other. You can then write a script that queries this metadata to write the join for you. I did this sort of thing over twenty years ago.
  - Spivak 2y ago
kak3a 2y ago
Author talked about usability problem with database, it's oxymoron usability problem in this site where all the text are in gray which makes reading very painful. Had to use "Darken Text" Chrome ext so it is not killing my eye!
delifue 2y ago
> “Find all flights from Detroit to Beijing” ... which must be joined twice with flight info to express our query Actually this can be done by nested query, without joining. I prefer nested query than joins. Because join is "global" and nested query is "local" (global means interfering with the whole SQL statement). The local one is easier to understand.
hi41 2y ago
How do you create the correct indexes? If we create index for all the conditions in a where clause wouldn’t the number of indexes become too many? I don’t fully grasp how to best create indexes. Does anyone have good reference and examples?
- beart 2y ago
  Creating good indexes is a nuanced problem to solve. It is highly dependent on the primary use case of the data. For instance, if you have a table representing varieties of beer, you are likely to present an interface for searching by brand, or type (ipa, pilsner). Those would be good targets for indexes. However, you probably won't often search by color, so it won't be worth indexing that column. Typically I would add any indexes initially that I know will be heavy hitters, then refine as needed based on actual use. Most databases provide tools to help identify missing indexes for common queries.
  - valiant55 2y ago
    I think a good baseline is just include indexes for foreign key relationships and then adjust from there. Also, if anyone reading this is using SQL Sever, do not use the recommended missing indexes it provides. They have a whole host of issues, including the ordering of columns and straight up recommend completely unnecessary indexes.
    - hi41 2y ago
      Yes, we are using MS SQL. Will keep your advice in my mind.
- golergka 2y ago
  Mostly where EXPLAIN ANALYZE points to.
- mike_hearn 2y ago
  Some databases can do it autonomously. Oracle is an example. It can notice if you keep doing inefficient queries and add the needed indexes by itself.
boxed 2y ago
For me the join problem doesn't exist anymore, because I use the excellent Django ORM for work and hobby projects. This is a SELECT with 5 joins: Foo.objects.filter(a__b__c__d__e=3) Types are respected, valid relations exist and invalid relations do not. Clean.
- Izkata 2y ago
  I believe that's 4 joins, "e" should be a column on "d"
  - boxed 2y ago
    Yea, my bad.
leandrod 2y ago
Those who do not learn the relational model are bound to flounder. For most use cases, there is no reducible complexity in the relational model.

150 points by jamesblonde 2y ago

Prof Murat is calling for examples of how LLMs can help make DBs more usable. I note that Julius.ai is making data analytics easier - just upload some data and ask for some charts and it does an ok job. The problem of going from natural language to SQL is still a far from solved problem. The main benchmark for this is Bird-Bench and accuracy is only in the high 60s - https://bird-bench.github.io/ https://bird-bench.github.io/ I have been working on making tables queryable using function calling, where you a fixed number of canned queries based on the primary key and an event-time column. PyData talk on this - https://www.youtube.com/watch?v=dRcjTe5qgwM https://www.youtube.com/watch?v=dRcjTe5qgwM

randomdata 2y ago
> The problem of going from natural language to SQL is still a far from solved problem. Is there any improvement going from natural language to something other than SQL? Certainly SQL can be cut out of the picture if it is what confuses these systems.
- zerodensity 2y ago
  What would that "something other" be? If the goal is to talk to a SQL database the output from the LLM would benefit from being SQL.
  - randomdata 2y ago
    > What would that "something other" be? Depends. Where is the problem? Is it the quirkiness of SQL? Perhaps something like QUEL or Datalog would yield better results? Is it declarative programming that it struggles with? GPT, for example, seems to be optimized for Python in particular. Perhaps an imperative approach is easier for the LLM to understand? It doesn't even have to be a language suitable for humans. Perhaps it would fare better with something like SQLite's byte code language? > If the goal is to talk to a SQL database While being able to talk to an existing SQL database would no doubt simplify the problem in a lot of cases, which is of merit, I doubt that is the actual goal. The user doesn't care about the technology, as they say. Getting the expected results out of the database is undoubtedly the actual goal. SQL as a target is all well and good if it works reliably, but the claim was that it doesn't. If some other target performs better, there is no need to cling to SQL. It is merely an implementation detail.
    - big_whack 2y ago
      I think the problem is the quirkiness on the English side, not the SQL side. You could translate datalog to SQL or vice versa, but understanding intention from arbitrary english is much harder. And often query results must be 100% accurate and reliable.
      - randomdata 2y ago
        > I think the problem is the quirkiness on the English side While likely, the question asked if there was any improvement shown with other targets to validate that assumption. There is no benefit in thinking. > And often query results must be 100% accurate and reliable. It seems that is impossible. Even the human programmers struggle to reliably convert natural language to SQL according to the aforementioned test study. They are slightly better than the known alternatives, but far from perfect. But if another target can get closer to human-level performance, that is significant.
        yuliyp 2y ago
        When I find someone claiming a suspicious data analysis result I can ask them for the SQL and investigate it to see if there's a bug in it (or further investigate where the data being queried comes from). If the abstraction layer between LLM prompt and data back is removed, I'm left with (just like other LLM answers) some words but no way to know if they're correct.
        randomdata 2y ago
        1. How would the abstraction be removed? Language generation is what LLMs do; a language abstraction is what you are getting out, no matter what. There is no magic involved. 2. The language has to represent a valid computer program. That is as true of SQL as any other target. You can know that it is correct by reading it.
        big_whack 2y ago
        Once you have SQL, you have datalog. Once you have datalog, you have SQL. The problem isn't the target, it is getting sufficiently rigorous and structured output from the LLM to target anything.
7thpower 2y ago
This was basically the only reasonable way I found to create a consistent user experience. I think of them as natural language BI, where you have canned reports to answer common categories of questions.
jalcazar 2y ago
Gemini generating SQL queries from natural language could be an example of AI making DBs more usable. There is more people speaking natural language than SQL https://cloud.google.com/bigquery/docs/write-sql-gemini#prompt_to_generate_sql_queries https://cloud.google.com/bigquery/docs/write-sql-gemini#prom...
- jamesblonde 2y ago
  Bird-bench has gemini on 69.03% on the test set. That is a long way from something you can build on.

Spivak 2y ago

I think there's a huge difference between how you design: 1. A database that's meant to be understood by programmers, make queries by the application efficient in space and time, and provide strong referential integrity. 2. A database that's meant to be played with by humans, where duplication is fine, referential integrity is a nice-to-have, every column is okay to be nullable, tables should contain complete objects as understood by the user that map 1-1ish to the real world, that eliminate as many opaque ids as feasible, and foreign keys might exist but aren't enforced in any direction. The latter database is far more ergonomic and you won't run up against a user frustratingly bashing their keyboard because the database just refuses to do what they want. The stakes in #2 style databases are extremely low-- let the user destroy their copy of the database it's fine, we can reload from the last save. The nice thing is that it seems very possible to go from #1 -> #2 mechanically, and hand that off to the users who want to play with it.

refset 2y ago
The 4GL dream hinges on figuring out how to transition from #2 -> #1 seamlessly. The spectrum is wider and more complex than "Excel -> Postgres" but even solving that journey would be a good start. It could save us all from a whole a bunch of ~needless engineering work. > it seems very possible to go from #1 -> #2 mechanically, and hand that off to the users who want to play with it NocoDB seems like a reasonable attempt of taking this approach for Postgres.
- feoren 2y ago
  > The 4GL dream hinges on figuring out how to transition from #2 -> #1 seamlessly I guarantee you there is no algorithmic way to do this. Going from "big flat file" to "nicely organized 3+NF schema" adds a lot of underlying domain knowledge and information to the system. You're asking for "enhance!" on the database schema. It will always require expert understanding.
jiggawatts 2y ago
Some technical design elements can enable this style of non-programmer usage that most databases engines do not currently support. First, treat each column separately in the physical engine (column store). Users ought to never need to worry about column count limits, row byte limits, or sparsity. Similarly, schema operators such as adding, removing, or reordering columns ought to always be instant atomic changes. Imagine how much faster it would be possible to explore the schema design space if changes were instant and didn’t require “migration scripts” or data copies from an old table to a new one. It would make the database feel more like a spreadsheet! Next, there ought to be Git-style forking and merging so that creating a test environment should also be a lightweight operation just like creating a local branch in a Git repo. Merging changes can be either schema only or schema+data -- the latter to support slowly-changing “master data” editing workflows. Currently, few if any database support the equivalent of a pull request with reviews to merge data. Hence the excessively complex access controls that could all be replaced with a single "peer review" operation. I’ve seen a fancy category-theoretic approach where forks of the database can receive live data updates from production. This would allow UAT and similar environments to be evergreen with zero infrastructure code such as nightly ETL sync jobs. Many operational tasks can be eliminated by tying live environments of both the code and data to Git branches of a single (mono) repo. Now the code and data schema can’t go out of sync! Literally impossible. No need for scaffolding, or ORMs, or any of those layers of overcomplicted abstractions! The schema is just “there”, in the repo, always representing reality. Sprinkle on a Microsoft Access style form designer but with HTML5 and guest user support and you have a billion dollar product.

trollied 2y ago

I think the core of this is the age old problem that people would rather invent crappy technologies that end up being a pain instead of taking some time to learn a standard, SQL. SQL is not going to go away. It's relatively easy to learn.

morkalork 2y ago
It's shocking how many developers are afraid of SQL, it hurts my soul.
- codr7 2y ago
  I honestly don't get it, it's not rocket science, and compared to freakin MongoDB it's a Sunday walk in the park.
- minkles 2y ago
  20+ years ago no one was afraid of it. Our front end web folk were doing SQL quite happily. Now they can't even consume an API without problems. I think the demand for staff has lowered standards considerably.
  - yunolearn 2y ago
    20 years ago (or more like 30-40), software actually did something for the user and wasn't just a ZIRP hack to make billions courtesy of Google and friends. Once the money became the entire point, once line go up, you just need to be able to fog a mirror long enough to get acquired. The number of supposedly senior developers who send me screenshots of stack traces they haven't even read makes me want to day drink heavily. I'm thinking of starting a bakery.
    - minkles 2y ago
      Yeah nailed it there. Totally agree. I was going to start a grilled cheese van!
nine_k 2y ago

otoolep 2y ago

>They care less about impressive benchmarks or clever algorithms, and more about whether they can operate and use a database efficiently to query, update, analyze, and persist their data with minimal headache. Hugely important, and I would add "backup-and-restore" to that list. At risk of sounding conceited, ease of use is a primary goal of rqlite[1] -- because in the real world databases must be operated[2]. I never add a feature if it's going to measurably decrease how easy it is to operate the database. [1] https://www.rqlite.io https://www.rqlite.io [2] https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-OeMGoij22oMZ_xhyeRTrQnn6o/edit#slide=id.ge8e3d69953_0_35 https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-Oe... Disclaimer: I'm the creator of rqlite.

otoolep 2y ago
Or, I should say, I don't add the feature until I can figure out how it can made be easy and intuitive to use. That's assuming the feature is even coherent with the existing feature set of the database. Of course, it's easy for me to do this. I am not developing the database for commercial reasons, so can just say "no" to an idea if I want. That said, I've found that many ideas which didn't seem interesting to me when an end-user first proposed them become compelling once I think more about the operational pain (and it's almost always operational) they are experiencing. Automatic backups to S3[1] was such a feature. I was sceptical -- "just run a script, call the backup API, and upload yourself" was my attitude. But the built-in support has become popular. [1] https://www.philipotoole.com/adding-automatic-s3-backups-to-rqlite/ https://www.philipotoole.com/adding-automatic-s3-backups-to-...
- nine_k 2y ago
  The distance between a large effort and a moderate effort is not very long; from the user's perspective, both things are in the realm of the Hassle. The chasm between a small required effort and zero effort is vast, from the user's perspective. Any product person will tell you that. Hitting the right zero-effort target is what separates a runaway success from a tepid reaction.
  - 2y ago

SoftTalker 2y ago

> You youngins may not remember, but pre-2005 we had to call a travel agent to book our flights and get paper tickets in return. This sucked, we don't have any transparency in to the process, we couldn't explore options (price, convenience, dates, airports) and customize our trip. Having access to flight booking via web was really a great improvement for user experience. I'm going to disagree. I could call my travel agent and say "I am going to Chicago on <date> I need to be there by <time> call me back with a couple of nonstop options. And I'll need a room at the Hilton and Towers for two nights." vs. today I can spend my own time navigating travel sites, avoiding their dark patterns, wondering if I'm really getting the best prices, making sure I understand the terms (is this refundable? are any checked bags included?) etc and then do the same for the hotel booking. If you work in a business with a travel department or assistants who can manage your travel you have an idea, compared to the hassle of doing it all yourself.

ComputerGuru 2y ago
> wondering if I'm really getting the best prices Curious why you assume you were getting the best price before?
- nine_k 2y ago
  But "before" you did not have an option and thus the need to wonder! "One who has a watch always knows what time is it. One who has two watches is never certain about anything."
  - SoftTalker 2y ago
    Yes, the paradox of having too many choices. Sounds like a good thing, but it causes anxiety.
    - nine_k 2y ago
      Making a choice means making decisions. Making decisions can definitely be taxing, especially when yourself or your social circle insists that decisions were "good", "smart", or otherwise met a standard of excellence. Suddenly you end up in a situation where you need to prove yourself, else the smartass cousin or your own resentful self will constantly remind you how you played the fool when you could have made a much better choice, obvious in hindsight. An agent (human or machine) that chooses the tickets for you because you can't be bothered with such mundane trivialities may be quite desirable for some.

flowerlad 2y ago

> Users cannot interact with the database directly It is not super hard to find UI designed for end users, these days. If you know the basics such as what a relational database is, then here’s is a good UI: https://visualdb.com https://visualdb.com

minkles 2y ago
People have forgotten we had this back in 1997 with MS Access. I built a whole ERP system with it, single handedly including the hardware, software, networking and the workstation deployments without really breaking a sweat. We have gone off the rails somewhere.
- flowerlad 2y ago
  Right but Access is outdated. Microsoft tried to make a web version of Access, but gave up. Now they recommend Power Apps but it’s support for databases is pretty weak.
  - minkles 2y ago
    It still works and it doesn't matter. Only the problem solved matters.
  - yunolearn 2y ago
    It's old so it's bad?
    - flowerlad 2y ago
      For starters it is not web-based, does not work on Mac, does not support LLM query generation, etc.

josephg 2y ago

Something thats always bugged me about relational database modelling is how you have to use table relationships for everything. Humans have a special category for ownership (eg Order owns DeliveryAddress), which works differently from other kinds of relationships. Eg Order references Products. This problem is heightened by the fact that a SQL table typically can't store composite types. Like, you can't make a column of lists-of-strings. Every modelling problem is solved by using more tables. And any nontrivial application ends up with a table explosion. You have to study your database very closely, then write complex, slow, join-heavy queries, just to get the information about simple stuff - like an order. Solving every problem with tables might seem clever from a technical perspective, but its just not how humans think. There's a reason document databases like mongodb are popular. Even if they are worse technically, they're much easier to use and reason about. There's no reason SQL databases couldn't support nested data. Postgres sort of does already via JSON fields. But it feels like you have to fight the database to do it.

codr7 2y ago
Easier to reason about in that they don't force you to clarify your thoughts, which will come back to bite your head off. And there's a pretty significant long term price to be paid. I would rather write Cobol than deal with MongoDB.
- Spivak 2y ago
  This is all nice and good but I've never once seen someone correctly clarify their thoughts ahead of time that doesn't end up being the wrong model as the code evolves. The only schemas that survive long term are the ones that create such a tangled ball of foreign keys that migrations are infeasible. Great advice for anyone looking to leave a legacy.
  - codr7 2y ago
    At least they exist, and force you to refactor the code to match reality. Document databases tend to take on a life of their own; and since they encourage shortcuts, the slope is typically down.

pjs_ 2y ago

A huge fraction (not 100%, but maybe 80%) of my frustration in trying to get technical people to use a database is that they have such a hard time understanding JOINs. People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns. I'm actually sympathetic to that reticence but what I am not sympathetic about is this: why, in 2024, can't the computer figure out multi-table joins for me? Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. When I started using SQLAlchemy, I naively assumed that a trivial functionality of such a sophisticated ORM would be to implement `apple.join(house)`, automatically figuring out that `box` is the necessary intermediate step. SQLAlchemy even has all the additional relationship information to figure out the details of how those joins need to work. But after weeks of reading the documentation I realized that this is not a supported feature. In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that. I'm not a trained software engineer but it seems like this must be a very generic problem -- is there a name for the problem? Are there accepted solutions or no-go-theorems on what is possible? I have searched the internet a lot and mostly just find people saying "oh we just type out all combinatorially-many possible queries"... apologies in advance if I am very ignorant here

rawgabbit 2y ago
Most databases have the concept of foreign keys. You declare how the tables relate to each other. You can then write a script that queries this metadata to write the join for you. I did this sort of thing over twenty years ago.
- Spivak 2y ago

kak3a 2y ago

Author talked about usability problem with database, it's oxymoron usability problem in this site where all the text are in gray which makes reading very painful. Had to use "Darken Text" Chrome ext so it is not killing my eye!

delifue 2y ago

> “Find all flights from Detroit to Beijing” ... which must be joined twice with flight info to express our query Actually this can be done by nested query, without joining. I prefer nested query than joins. Because join is "global" and nested query is "local" (global means interfering with the whole SQL statement). The local one is easier to understand.

hi41 2y ago

How do you create the correct indexes? If we create index for all the conditions in a where clause wouldn’t the number of indexes become too many? I don’t fully grasp how to best create indexes. Does anyone have good reference and examples?

beart 2y ago
Creating good indexes is a nuanced problem to solve. It is highly dependent on the primary use case of the data. For instance, if you have a table representing varieties of beer, you are likely to present an interface for searching by brand, or type (ipa, pilsner). Those would be good targets for indexes. However, you probably won't often search by color, so it won't be worth indexing that column. Typically I would add any indexes initially that I know will be heavy hitters, then refine as needed based on actual use. Most databases provide tools to help identify missing indexes for common queries.
- valiant55 2y ago
  I think a good baseline is just include indexes for foreign key relationships and then adjust from there. Also, if anyone reading this is using SQL Sever, do not use the recommended missing indexes it provides. They have a whole host of issues, including the ordering of columns and straight up recommend completely unnecessary indexes.
  - hi41 2y ago
    Yes, we are using MS SQL. Will keep your advice in my mind.
golergka 2y ago
Mostly where EXPLAIN ANALYZE points to.
mike_hearn 2y ago
Some databases can do it autonomously. Oracle is an example. It can notice if you keep doing inefficient queries and add the needed indexes by itself.

boxed 2y ago

For me the join problem doesn't exist anymore, because I use the excellent Django ORM for work and hobby projects. This is a SELECT with 5 joins: Foo.objects.filter(a__b__c__d__e=3) Types are respected, valid relations exist and invalid relations do not. Clean.

Izkata 2y ago
I believe that's 4 joins, "e" should be a column on "d"
- boxed 2y ago
  Yea, my bad.

leandrod 2y ago

Those who do not learn the relational model are bound to flounder. For most use cases, there is no reducible complexity in the relational model.