[HN Gopher] Making database systems usable
___________________________________________________________________
Making database systems usable
Author : jamesblonde
Score : 50 points
Date : 2024-08-20 18:13 UTC (4 hours ago)
(HTM) web link (muratbuffalo.blogspot.com)
(TXT) w3m dump (muratbuffalo.blogspot.com)
| jamesblonde wrote:
| 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/
|
| 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
| randomdata wrote:
| _> 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 wrote:
| 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 wrote:
| _> 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 wrote:
| 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 wrote:
| _> 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 wrote:
| 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.
| big_whack wrote:
| 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.
| sgbeal wrote:
| > there is no need to cling to SQL. It is merely an
| implementation detail.
|
| It is, in fact, also the interface. To use your example
| of SQLite bytecode: once your tool generates it, there is
| no way to feed that into SQLite. The bytecode is an
| implementation detail, with SQL being the public
| interface.
| randomdata wrote:
| But, to stick with your example, you can then modify
| SQLite to accept byte code input - or straight up write
| your own database engine that uses said byte code. We
| already know how to solve that kind of problem. This is,
| comparatively speaking, child's play.
|
| It is recognized that SQL as a target would theoretically
| provide a less labour intensive path for reasons of
| integrating into what already exists, but that only holds
| if natural language to SQL gets solved, and is not enough
| harder to solve than an alternative target.
|
| A reasonable stretch goal, but if another target gets you
| there first, it would be foolhardy to cling to SQL.
| Replacing the database interface is a _much_ simpler
| problem to solve.
| 7thpower wrote:
| 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 wrote:
| 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#prom...
| jamesblonde wrote:
| Bird-bench has gemini on 69.03% on the test set. That is a
| long way from something you can build on.
| Spivak wrote:
| 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 wrote:
| 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.
| jiggawatts wrote:
| 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 wrote:
| 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 wrote:
| It's shocking how many developers are afraid of SQL, it hurts
| my soul.
| codr7 wrote:
| 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 wrote:
| 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.
| nine_k wrote:
| In other words, people would rather make many tiny, seemingly
| easy steps than a few really impactful but seemingly arduous
| steps.
|
| Teach SQL a spoonful at a time then, I suppose. Do not start
| from having the students to read Codd's original papers.
|
| OTOH SQL is not the best _language:_ it 's both too wordy and
| too terse, it's not composable in many important cases, it does
| not map nicely to set-theoretic operations. But a transpiler to
| SQL that would offer a vastly better experience is yet to take
| the world by storm.
| otoolep wrote:
| >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
|
| [2] https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-
| Oe...
|
| Disclaimer: I'm the creator of rqlite.
| otoolep wrote:
| 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-...
| nine_k wrote:
| 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.
| spinningslate wrote:
| yes, definitely. I find it helpful to think of usability
| and friction as an inverse square law relation [0]. Small
| increases in friction (x-axis) cause dramatic drop-off in
| usability (y-axis) to begin with, then correspondingly less
| so. Specific user tolerance will vary, but adoption broadly
| follows a similar path - exponential drop off.
|
| I've never seen any data to back this up in a quantitative
| sense (though interested if anyone has?). Nevertheless,
| I've still found it useful as a qualitative rule of thumb
| in a positive sense: shaving off small edges of friction
| can have non-linear return in adoption and satisfaction.
|
| [0]: https://en.wikipedia.org/wiki/Inverse-square_law
| makmanalp wrote:
| Add schema migrations and bulk loads to this. So many systems
| crap out doing things like schema migrations at scale. Query
| latencies degrade over time due to internal structures keeping
| track of things, stuff runs out of buffer / log space in memory
| or on disk, you have a traffic spike but you can't pause or
| throttle a 40 hour long ALTER, things that should never lock do
| lock for indeterminate times during cutover, stuff craps out
| after cutover due to surprising behavior but there is no
| rollback, or even worse I've seen things just flat out crash
| with some random assert fail or segfault.
|
| It's a world of pain, and there are so much scar tissue of
| third party tooling doing crazy stuff dealing with this problem
| among large companies that really should be the DB vendor's
| problem.
| SoftTalker wrote:
| > 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 wrote:
| > wondering if I'm really getting the best prices
|
| Curious why you assume you were getting the best price before?
| nine_k wrote:
| 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 wrote:
| Yes, the paradox of having too many choices. Sounds like a
| good thing, but it causes anxiety.
| nine_k wrote:
| As usual:
|
| * Relegate it to an agent: have to trust the agent, have to pay
| the agent, saves you time.
|
| * DIY: have to spend time, have to have some expertise, saves
| you money.
|
| A middle ground existed back in the day, too: you could visit a
| physical office of an airline and buy a physical ticket there,
| at the cost of some time and the narrow choice, but it saved
| you some money compared to an agent, and the clerk could
| provide a limited assistance.
| flowerlad wrote:
| > _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
| minkles wrote:
| 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 wrote:
| 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.
| josephg wrote:
| 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 wrote:
| 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.
| jeeyoungk wrote:
| +1
|
| For example, BigQuery has natural support for arrays and nested
| data, and it's quite nice / essential for good data modeling.
| For example, "tags" can be stored as `Array<Struct<Key,
| Value>>`, and this can be used to implement things like,
| "search with fields with particular tags".
|
| This reduces the cognitive burden of remembering which tables
| join with which, especially if we know that a relationship is
| solely relevant in one context. I.e. Tags can only be joined to
| the main table, and no other joins are sensical.
| minkles wrote:
| I think this is a poor understanding and laziness. SQL is type
| and schema first and people hate that because it makes things
| hard and complicated up front. Table explosions are rare if you
| know what you are doing. Many people don't any more.
|
| As for join heavy, complexity, this is not necessarily a
| problem in reality. It's incredibly easy to scale this out to
| huge systems (relatively) cheaply.
|
| Believe me as they scale up, they look way less hard and less
| complicated than arbitrary and poorly enforced schemas in
| document databases. I could write an essay on how to fuck up
| MongoDB (or any document store) because I spent nearly 2 years
| unfucking one.
| stult wrote:
| Your argument is a variant of the object-relational impedance
| mismatch problem[1]. It's easier for us to reason about objects
| (or functions and types) than it is to think in terms of SQL
| tables, so it is much easier to develop complex logic or domain
| models in general purpose programming languages. Yet data
| storage and retrieval is much, much more efficient with
| relational databases, and it is generally a good practice to
| logically separate the data storage layer from the rest of the
| system anyway. But implementing that layer to translate from
| the relational model to the object model frequently involves
| tons of finicky, manually crafted SQL statements embedded in
| general purpose code where the linting/type checking/static
| analysis tooling often isn't great for the embedded SQL. The
| only other alternatives are using an ORM that automagically
| handles most if not all of the relational-object mapping or a
| NoSQL solution like Mongo that avoids the mismatch altogether.
| Both those alternatives typically involve sacrificing
| performance and scalability, however, and the limitations and
| quirks of each frequently contribute to bugs (e.g., schema
| drift in document DBs, or implementation details of the ORM
| that make it hard to map nested relationships).
|
| [1]
| https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
| pjs_ wrote:
| 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
| big_whack wrote:
| It's not really a problem of there being combinatorially many
| ways to join table A to table B, but rather that unless the
| join is fully-specified those ways will mostly produce
| different results. Your tool would need to sniff out these
| ambiguous cases and either fail or prompt the user to specify
| what they mean. In either case the user isn't saved from
| understanding joins.
| rawgabbit wrote:
| 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.
___________________________________________________________________
(page generated 2024-08-20 23:01 UTC)