I think automatically maintaining materialized views isn’t well supported for a lot of reasons. But I also don’t think that it’s impossible for something like Postgres to support it more in the future.
Ask HN: What could a modern database do that PostgreSQL and MySQL can't
241–250 of 326 posts
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#242Earlier quoted context omitted.
The title seems fair to me. Anything that's actually used has certain commitments it made earlier in its lifecycle from which it now can't deviate, even if later developments made the commitments problematic.
>Anything that's actually used has certain commitments it made earlier in its lifecycle from which it now can't deviate Perhaps I'm misunderstanding your comment, but MySQL has definitely deprecated and removed features over the years. https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
Sure. There are probably also things they wish they could deprecate, but realistically can't. User expectations they wish they could replace and can't. I think that's the charitable interpretation of things asked for.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#243Earlier quoted context omitted.
The title seems fair to me. Anything that's actually used has certain commitments it made earlier in its lifecycle from which it now can't deviate, even if later developments made the commitments problematic.
The question is fair if it applies to database systems that aren't actually used?
Right, just like how any new system starts off. It's asking about how one might greenfield differently with the OLTP knowledge we have today.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#244Lots of people are citing cutting-edge bells and whistles for advanced query plans and data types, but after working at $BIGCO and seeing what exists there (and has existed for years), I get frustrated by the lack of ergonomic and operational essentials in the public-cloud options.
1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete.
2. Strong commit timestamps, again a la Spanner/Cockroach. Globally-ordered commit times and read snapshots aren't something you'll need in every schema, but when you do, they're magical - suddenly, you can use your OLTP system for strong lease assignment (conditioning other transactions on lease validity), you can construct arbitrary application-level transaction semantics (non-transactional read followed by transactional compare-and-swap is pretty powerful in some scenarios), and all sorts of other things.
3. Interface abstraction. Databases that ship language-support drivers should also ship a fake in-memory implementation for testing purposes that supports the same interfaces and options. There's no reason why I should have to start a subprocess and emulate a network connection to tell whether a query functions properly or not, or whether I've set isolation properties correctly for a pipeline.
4. Declarative schemas, and a well-defined update process (that isn't human-written DDL), are essential at any sort of organizational scale. Every time I see an "ALTER TABLE" statement checked into a repository, I tear my hair out.
5. Queue support, where message publishing/consumption is transactional with other read/write operations. You can always build a queue with a time-keyed table, but it's kind of a pain in the ass to get all the edge cases right. It's much better as a first-class concept supporting automatic sharding, retries, batching, and bounded-out-of-order delivery with a global cursor.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#245Automatic indexes. Adding indexes is a guessing game. It's a bit of abstraction leakage. Imagine a product engineer did not have to think about how the data is laid out on disk
I could see it as taking a typical day's worth of transactions, creating new test db from this log, and then letting a tool try out different indexes based on some common heuristics and re-run the queries and benchmark. Because often it requires experimentation to get the right indexes.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#246Earlier quoted context omitted.
A lot of the points you mention are really interesting to me, as I've been coming to similar conclusions recently. What are good choices that solve these particularly in the context of js/clientside apps?
absurd-sql[1] is the coolest thing I've seen in this space so far. sqlite.js brings sqlite to the browser via wasm, and then absurd-sql implements the storage layer in IndexedDb. So now we have an sqlite compatible db in the browser to play with. There is also alasql[3] which is implemented in js, and lovefield[2] from Google which seems like an experiment that is now abandoned. First, you could implement a REST/Grap…
Thank you for the links, I've heard about some of these projects but will check out others. I've actually been working on a project that is running fully on front-end side and I'm leaning towards indexeddb+observability, but in my case there would be a lot of different data in one view and I wanted to see if I can materialize a view in worker and then push the deltas using messages. Also, while exploring it, I found out you could use service workers to create a virtual server [0] that would cache or produce views as needed, but that solution doesn't seem to be very popular.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#247- Blurring (safely!) the line between database and the app using it: transparently switch between bringing data to compute, or compute to data. - Comprehensive auto tuning: automatic index creation, automatic schema tuning, dynamically switching between column/row-oriented, etc. User specifies SLOs, database does the rest. - Deeply related to the previous two points: perfect horizontal scalability - Configurable per-…
> Blurring (safely!) the line between database and the app using it What do you mean by this? > Comprehensive auto tuning...automatic schema tuning You should be able to maintain a logical database schema, and then flip a toggle for things you want to have denormalized (and eventually have the db just do it automatically). Or maybe even take a blob of unstructured data and automatically normalize it.
> > Blurring (safely!) the line between database and the app using it
> What do you mean by this?
Consider this app pseudocode:
resultset = db.query("SELECT ... WHERE ...")
for row in resultset
if some_complex_condition(row)
do_something_with(row)
At the same time, consider the case of the table being selected being almost read-only. In this case, and if it fits, it may make sense to move the data directly to the app, keep it in sync when it changes, and have the query processing (the `SELECT ... WHERE ...`) happen directly in the app.
This hints at what I meant by blurring the line: dynamically shifting where computation happens, and where the data lives, depending on the available resources, the workload, and the data.
This is partially doable today e.g. with Hazelcast, in that it allows app instances to be part of the "database" (or, as they call it, the "in-memory grid").
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#248I realise I'm straying a bit from core OLTP stuff but also I think removing the historical need for a separate OLAP database is something modern systems should address. Off the top of my head: 1) Incremental materialized view maintenance, à la Materialize (bonus points for supporting even gnarly bits of SQL like window functions). 2) Really ergonomic and scalable pub/sub of some sort, à la RethinkDB. 3) Fine tuned co…
3) Fine tuned control over query plans if I want it. I feel like when most people say this, what they really want is a better query planner. The optimum query plan depends on a lot of dynamically changing factors: system load, free RAM, and of course the data in the tables themselves. Any hints we give the query planner are going to help at certain times and be somewhere between "suboptimial" and "disasterous" at mos…
> It would be cool if EXPLAIN ANALYZE also perhaps showed other query plans the planner considered but discarded, and why. Imagine if the planner tried multiple plans and adjusted itself accordingly.
The interesting point is in that regard MySQL is really better than PostgreSQL. MySQL can give you more execution plans it evaluated and tell you why it didn't use them (cost value is higher) and even tell you why it didn't use a specific index it. Combined with the enforcing specific indexes you can sometimes trick it into a more efficient query plan even if it believed to be worse.
But to be honest, PostgreSQL query planner is a lot more intelligent and does stupid things very seldom. And the ability to instruct PostgreSQL collecting more statistics on some attributes (https://www.postgresql.org/docs/13/sql-createstatistics.html) is a huge improvement to getting PostgreSQL make more intelligent plans.
What i really miss in PostgreSQL is:
* Seeing other query plans it discarded as you described to get a feeling how to hint PostgreSQL into a direction if the query planner is doing stupid things
* MySQLs query profile showing the execution time of multiple subtasks of the query: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
* The ability to enforce specific query plans. The PostgreSQL devs stated they are against this but pg_hint_plan is really usefull and i was able to drastically improve some very complex queries.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#249Earlier quoted context omitted.
Postgres comes with the building blocks for both sharding and HA out of the box, and they're extensively discussed in the docs. You don't need proprietary addons other than as pure convenience.
don't underestimate the importance of convenience. I'm convinced one of the reasons MySQL had so much more mindshare than postgres back in the day was that it was far easier to get up and running, even if postgres might have been easier to use once everything was set up correctly.
That's funny, I must have been an outlier then.
I've been using Postgres since 1998, and I tried getting MySQL up first. There was more documentation available for the latter, so it should have been simple. Failed. It just didn't work.
Out of frustration I then tried Postgres, because I just wanted a decent database for my project. It was surprisingly easy, I only had to learn about pg_hba.conf to get to a functional state. Everything else was in place out of the box.
I've been a happy user ever since. MySQL may have had the mindshare (thanks to prevalence of LAMP) but everything outside the magic happy path was confusing and fragile.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#250Does your data model change often? PostgreSQL and MySQL well be very difficult to make fundamental changes. If you need to make changes to your data model, the structure of relational databases will make it difficult for you to move fast. Linear relationships must be defined. Your database doesn't do much for you. You must define every relationship between tables.
> Does your data model change often? PostgreSQL and MySQL well be very difficult to make fundamental changes. I hear this argument a lot and I struggle with it. It is an argument that, at least for me, falls into the same category as "you should design your schema in a portable manner". The "portable schema" argument is easy to disprove because if you don't design your schema in accordance with the features available…
Wanting to make changes to your schema easier, doesn't necessarily mean you need NoSQL.
I think it's a question of having the right tooling.
It would be cool if you could dump an unstructured blob of data in a psql table. And then later you can add a schema to this defining relationships. So like `post.comments` is an array of `Comment`. And then you just run a command that runs a migration that normalizes the data into a `comment` table. And then it would map `post.comments` to a join. Although psql's jsonb support and indexing is pretty good.
The difficulty though is that if you change your mind it becomes much harder to change because now you have multiple tables and relationships. So what would be nice is if you can go back from the relational model to the unstructured model with ease.
I think what is needed is a visual tool to design your db schema and migrations that just works, and is also aware of unstructured data and that it can have a json schema.
Ideally we want to be designing a logical schema (and any unstructured data would implicitly be given a logical schema too), and the physical schema is automatically created.