23 ms·

Ask HN: What could a modern database do that PostgreSQL and MySQL can't

• spicybright 5y ago
  I'm extremely out of my expertise here, but I'll see if I can spark some conversation. While possible with older SQL's through your own code, distributed sharding and keeping multiple databases in sync I would think be useful at a DB level vs user code level. You can certainly argue that shouldn't be part of the database software though.
  • bob1029 5y ago
    I think it should be the job of the application logic to handle replication, validation and recovery of persisted business objects. Deferring this responsibility to some middleware is not something I am a big fan of. There are powerful arguments for using some off-the-shelf solution, but I also like being able to set breakpoints in the logic that ties all of the computers together.
    • MrStonedOne 5y ago
      There are things the db side would be better posed to handle and understand wrt replication. Moving it to application code requires a trade off and also everybody copying the same code in all of their applications for no reason other then somebody decided the application should be the one to handle it.
    • anticristi 5y ago
      I can't argue against that iff the team has this capability. Unfortunately, I saw too many teams that end up implementing an informally-specified, bug-ridden, slow implementation of half of PostgreSQL.
  • cornel_io 5y ago
    If you've ever used Spanner you'll quickly decide that it absolutely makes sense for the DB to handle almost all of this stuff itself, it's a wonderful system that is sadly prohibitively expensive for many (most?) use cases outside of Google.
• talolard 5y ago
  I think AWS Aurora is the most prominent thing that comes to mind. The key value proposition is the separation of storage from compute. That unlocks many promising features, “serverless” , a better parallelization story of OLAP queries etc
  • Jgrubb 5y ago
    I read that as "the key: value proposition.." so I probably need to take a break.
  • azinman2 5y ago
    Don’t both Postgres and MySQL do this?
  • thom 5y ago
    The copy-on-write workflow for clones in Aurora is particularly nice in a dev/staging environment.
• svcrunch 5y ago
  Support for vector indexes and vector similarity queries (dense, and sparse-dense). I believe PostgreSQL and MySQL will support this within a few years.
  • gk1 5y ago
    In the meantime there’s Pinecone (https://www.pinecone.io https://www.pinecone.io) to put alongside your DB. Also there is at least one plugin for Postgres that I know of, PGVector, but no idea how it performs.
• tdhz77 5y ago
  Does 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.
  • smitty1e 5y ago
    PostGreSQL provides JSON functions if your data have more of a document feel to them. https://www.postgresql.org/docs/9.5/functions-json.html https://www.postgresql.org/docs/9.5/functions-json.html
    • axegon_ 5y ago
      Postgres was first to the party but MySQL supports that too since 5.6 or 5.7, I can't remember.
  • traceroute66 5y ago
    > 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 in your database then you are setting yourself up for a big performance fail (e.g. for those of you familiar with the work of Tom Kyte of Oracle, a party trick of his was detailed evidence-based demonstrations of why you should use Oracle features in your schema design vs generic schemas ... but the same applies in the open-source world, e.g. Postgres[1]). The problem I have with the "use noSQL because your data model changes often" is that you then become heavily reliant on your upstream devs who are coding the app layer to behave themselves because you are no longer in a position to enforce or validate their actions at database layer. It also potentially puts you at risk of loosing the database's position as "source of truth" - because if upstream can change your data model at a whim, it means you could easily loose visibility of data elements overnight. To me, relational databases will always have a place in the world and I don't think people should blindly follow alternative models just because its the bandwagon of the day. ACID compliance is, AFAIK, not available anywhere else other than an RDBMS setting. NoSQL, graph databases etc. also have a place in the world of course, but only if you understand the limitations and tradeoffs you are accepting. It is quite possible that many people would be better off with RDBMS. [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.org/wiki/Don%27t_Do_This
• thom 5y ago
  I 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 control over query plans if I want it. 4) Probably very deep Apache Arrow integration.
  • JohnBooty 5y ago
    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 most other times. It's certainly true that the query planners in major RDBMS could be better or, at least, give insight into why they made the choices they did. 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. For Postgres in particular, I think the user-specified costs in pg.conf like `seq_page_cost` and `random_page_cost` feel like one obvious area for improvement: why am I guessing at these values? Postgres should be determining and adjusting these costs on the fly. But, I could be wrong.
    • thom 5y ago
      It's true a better query planner is what I want, i.e. one that always does what I want it to do. That not being the case, I will settle for being in control, footguns and all. I agree the config is hard to wrangle though, you effectively find yourself doing grid search with a bunch of common workloads, it does feel like something the machine should be doing for me (the most common config variable we end up tweaking is "how much money we give Amazon").
    • hamburglar 5y ago
      > The optimum query plan depends on a lot of dynamically changing factors Except … I’m part of a large organization with a very thorough incident post-mortem process and I’ve read a lot of analyses that end up blamed on the dreaded “query plan flip.” This ends up being an unplanned change that has an unexpected perf cost (and no real “rollback”) and causes an outage. The lesson I’ve seen learned over and over is to have very good understanding (and constant re-evaluation) of your hot queries’ perf , and to lock the query plan so it can only change when you intend it to.
• grobbie 5y ago
  CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance type interventions. PGSQL is designed for deployment as a single server with replica servers for HA. It's not really designed for horizontal scalability like Cockroach. You can do it - the foreign data wrappers feature and table partitioning can give you poor man's scale out. Or you can use Citus which won itself a FOSS license earlier this year. And there are other Foss and proprietary approaches too. MySQL is similar - you can do it, like with their recent router feature, but it has been retrofitted, and it's not as fluid as Cockroach. IIRC MySQL router is similar in configuration to Galera - that is, a static config file containing a list of cluster members. Listen I'm sure that the design approach of Cockroach could be retrofitted to PGSQL and MySQL, but I'm pretty sure that doing a good job of it would be a lot of work. So in answer to your question, I'm not sure that there's all that much RDBMS can't be made to do. Geospatial, Graph, Timeseries, GPU acceleration. Postgres has it all and often the new stuff comes to Postgres first. By the way I love MySQL and PostgreSQL, and the amazing extensions for PGSQL make it extra awesome. Many are super mature and make pgsql perfect for many many diverse use cases. For XXL use cases though, CockroachDB is taking a very interesting new path and I think it's worth watching.
  • traceroute66 5y ago
    > It has broad PGSQL language compatibility Depends how you define broad. :) Many key features are missing, including but not limited to: - UDFs and sprocs. - Useful datatypes such as TSTZRANGE - More limited constraints I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.
    • tuckerconnelly 5y ago
      Are you still using postgres? Thinking about a similar transition, would really appreciate any shared learnings :)
• kweinber 5y ago
  Automatic versioning of data and CDC (change data capture) broadcast of data deltas to external systems.
  • reilly3000 5y ago
    CDC is build-in via binlog; it’s the basis of most replication schemes but can be consumed by anything.
  • refset 5y ago
    > Automatic versioning of data More specifically, I would argue for the ability to run arbitrarily complex, non-locking, fully-consistent queries against all historic versions of the database, a.k.a. "the database as a value" (also "transaction time" or "system time" temporal queries)
    • sam_lowry_ 5y ago
      Isn't this part of the SQL standard already, to some extent?
      • sterlinm 5y ago
        Temporal queries have been part of the SQL standard for a while but are mostly not supported by the major databases. Some databases have partial support or extensions that add some temporal capabilities.
• klysm 5y ago
  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.
  • reilly3000 5y ago
    PipelineDB!
  • vaughan 5y ago
    https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...
• reilly3000 5y ago
  At least speaking for Postgres, it _is_ modern in that it’s very actively developed with all kinds of innovations layered on top of the core system. It can be a timeseries db, a real-time db, horizontally sharded, a self-contained REST/graphql API server, a graph db, an interface to many APIs via foreign data wrappers, and much more. In itself it has many analytics functions, and most cloud OLAP dbs use its syntax over a columnar storage scheme. I’m afraid most would-be database projects would be cloned by a Postgres extension before they could make a dent in its share.
  • anaganisk 5y ago
    My opinion is Postgres is Jack of many trades, but master of none. I would be more afraid of a DB that tries to be everything. Every feature adds a baggage.
    • reilly3000 5y ago
      It is a master of plain old rbdms, but able to accomplish more with specific extensions. Sure, it probably makes more sense to use Elastic than Postgres for full-text search, but there is a solid extension for it. In a lot of operational contexts it’s easier to host another Postgres instance with a specialized extension than to try deploy an entirely new DB.
      • anaganisk 5y ago
        I agree with you, when you say its a master of RDBMS. But each extension brings its own baggage. Specialised databases are written for a reason. For majority of the people the extensions may fit . But its not the best way once you want to optimise.
• pankajdoharey 5y ago
  High volume data ingestion and tunable consistency because Most sites start out with these two databases but eventually end of porting significant sections of their database to Cassandra.
• zinclozenge 5y ago
  1. Automatic backups to an S3 compatible storage, out of the box. 2. Progressive automatic scalability. As load increases or storage runs out, the DB should be able to automatically. NewSQL databases do this already. 3. Tiered storage. 4. Support streaming, stream processing, in memory data structures, etc. I feel like this is one of those weird things but I keep wishing this were possible when I work on side projects or startups. I don't want to have to spin up mysql/postgres, kafka/pulsar, flink/whatever/stream processing, redis, etc separately because that would be prohibitively expense when you're just getting off the ground unless you have VC money. So I find myself wishing I could deploy something that would do all of those things, that could also scale somewhat until the need to break everything into their own infrastructure, and if it was wire compatible with popular projects then that would be perfect. Will it happen? I doubt it, but it would be lovely assuming it worked well.
  • tlack 5y ago
    Great list. For #4, you should take a look at Erlang or Elixir - they have "just enough" concurrency with streaming primitives and a functional style that makes it easy to use. They make a lot of "bolt on" stuff you need for a regular startup (Redis, Celery, etc) superfluous.
    • dkh 5y ago
      Could you briefly elaborate on this? Are you suggesting the right structures within Elixir/Erlang are both concurrent and safe enough to negate the need for these things at some level? (And are you referring to things like OTP, or more general than that?) EDIT: For context, I'm familiar with the concurrency model and with how fairly bulletproof "processes" are in their context, but had never considered putting these to use in lieu of a Redis cache or certain other datastore use-cases. (My brief foray into Elixir was, however, when looking to improve reliability of a high-volume messaging system and various task queues attached to it, so that use-case I am at least aware of)
      • tlack 5y ago
        I wouldn't say Elixir is highly concurrent in the sense that you'll get something like a CRDT out of the box. You'll still have to design all of your distribution logic. I'm saying that the tools provided in that toolbox - like processes, OTP and ETS - let you build in-memory or disk backed structures that live inside the concern of the main application and are more responsive to your specific needs. For instance, let's say you need a scoreboard that persists in between page refreshes. With Node.js, you're left in a "hmm - not sure" space where you need to cobble together all the different pieces. At the end of the day you'll be facing cache consistency issues, problems saving state, abstraction leaks and communication overhead. In Elixir, it's trivial to use pubsub to collect events, a GenServer to store that state or act as a broker, and ETS to perform simple queries against it. It's like you're still given just a box of tools, but all of the tools work together better for dynamic, live, complex applications. Your mileage may vary! Let me know if I can help.
• m_ke 5y ago
  A combination of SQL, Redis, ES, and Columnar stores in a single package.
• rkarthik007 5y ago
  When building YugabyteDB, we reuse the "upper half" of PostgreSQL just like Amazon Aurora PostgreSQL and hence support most of the functionality in PG (including advanced ones like triggers, stored procedures, full suite of indexes like partial/expression/function indexes, extensions, etc). We think a lot about this exact question. Here are some of the things YugabyteDB can do as a "modern database" that a PostgreSQL/MySQL cannot (or will struggle to): * High availability with resilience / zero data loss on failures and upgrades. This is because of the inherent architecture, whereas with traditional leader-follower replication you could lose data and with solutions like Patroni, you can lose availability / optimal utilization of the cluster resources. * Scaling the database. This includes scaling transactions, connections and data sets *without* complicating the app (like having to read from replicas some times and from the primary other times depending on the query). Scaling connections is also important for lambdas/functions style apps in the cloud, as they could all try to connect to the DB in a short burst. * Replicating data across regions. Use cases like geo-partitioning, multi-region sync replication to tolerate a region failure without compromising ACID properties. Some folks think this is far fetched - its not. Examples: the recent fire on an OVH datacenter and the Texas snowstorm both caused regional outages. * Built-in async replication. Typically, async replication of data is "external" to DBs like PG and MySQL. In YugabyteDB, since replication is a first-class feature, it is supported out of the box. * Follower reads / reads from nearest region with programmatic bounds. So read stale data for a particular query from the local region if the data is no more than x seconds old. * We recently enhanced the JDBC driver to be cluster aware, eliminating the need to maintain an external load balancer because each node of the cluster is "aware" of the other nodes at all times - including node failures / add / remove / etc. * Finally, we give users control over how data is distributed across nodes - for example, do you want to preserve ASC/DESC ordering of the PKs or use a HASH based distribution of data. There are a few others, but this should give an idea. (Disclosure: I am the cto/co-founder of Yugabyte)
• sandGorgon 5y ago
  search - really the holy grail. Combine full text search (with tf-idf or bm-25 support. Not the kind that postgres/mysql does). This is a tricky engineering problem - have two kinds of indexes in the same database. But disk space is cheap. Network is expensive (especially if you're on AWS).
• aserafini 5y ago
  One thing I find really interesting from Redis that I would love in a relational database is the concept of ‘blocking’ queries, which block until they get a result. For example: https://redis.io/commands/BLPOP https://redis.io/commands/BLPOP If I could do something like: Select id, msg From job Where id > 1224 And have that query block until there actually was a job with an id > 1224, it would open up some interesting use cases.
  • natmaka 5y ago
    I would love to be able to "nice" queries, a high "nice" attribute meaning, for the engine, "work on this whenever it does not slow down anything else", especially on the I/O side (à la Linux "ionice"). A "niced" query blocks (and its working set may even be "swapped out") as long as: - the read buffer does not contain anything pertinent for it, - there are "too much" pending I/O requests (<=> submitting an I/O request useful for this query would slow down other less-niced queries) The "niceness" of a query may be dynamically modified by an authorized user. Bonus: - any role is associated to a minimal level of niceness (GRANT'able) - the underlying logic interacts with the OS corresponding logic in order to take into account all I/Os (this is especially useful if PG runs on a non-dedicated server)
    • eitland 5y ago
      Not exactly what you are asking for but worth mentioning anyway: https://wiki.postgresql.org/wiki/Priorities https://wiki.postgresql.org/wiki/Priorities
• nicoburns 5y ago
  Subscriptions. Databases like Firebase will automatically push changes to query results down to clients. You can add this to Postgres with tools like Hasura, but it's poll based and not very efficient. It's a super-useful feature for keeping UIs in sync with database state.
  • XCSme 5y ago
    Can't you add something like this to MySQL using triggers or some similar system?
    • spiffytech 5y ago
      Hasura evaluated Postgres' listen/notify feature to power their subscriptions, but chose polling instead: https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md https://github.com/hasura/graphql-engine/blob/master/archite... > Listen/Notify: Requires instrumenting all tables with triggers, events consumed by consumer (the web-server) might be dropped in case of the consumer restarting or a network disruption. It was substantially non-trivial for them to implement subscriptions that were both robust and efficient using this approach. Many applications need the extra step on top of listen/notify of relaying subscriptions to an untrusted client (e.g., a browser). I'd like to see more DBs bake that feature in, like RethinkDB did.
      • nicoburns 5y ago
        The Superbase (https://github.com/supabase/realtime https://github.com/supabase/realtime) approach is really interesting. It listens to the logical replication stream. Makes a lot of sense to me. Unfortunately our postgres instances hosted on heroku don't expose this, so I've been unable to try it out.
        • vaughan 5y ago
          Hasura discusses why they chose polling here (https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md#when-do-we-refetch https://github.com/hasura/graphql-engine/blob/master/archite...). > WAL: Reliable stream, but LR slots are expensive which makes horizontal scaling hard, and are often not available on managed database vendors. Heavy write loads can pollute the WAL and will need throttling at the application layer. Would like to hear Supabase's response to this.
• Torwald 5y ago
  Write a cheque for an Oracle consultant.
• jb1991 5y ago
  Look at Datomic.
• shekispeaks 5y ago
  Automatic 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
  • Upitor 5y ago
    What? I wouldn’t want this. How would you evne automate it? Creating the right indexes is the same as creating the right tables and columns in your data model: It depends on the business purpose and usage of the data.
    • iudqnolq 5y ago
      Business usage is observed over time instead of known up front though. In a way this is sort of like the query planner. Couldn't your usage be observed and used to determine appropriate indexes?
      • karmajunkie 5y ago
        i’d be more interested in an automatic index “suggester” based on observation and slow query analysis. there’s also the matter of new use cases where you’d absolutely want to be able to create them manually.
        • iudqnolq 5y ago
          Absolutely. I'm increasingly in favor of generated code you check into source control.
        • adrianmonk 5y ago
          It has been a million years since I've used it, but I think MS SQL Server has this. Here's some documentation I just found (at https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-dashboard?view=sql-server-ver15#remarks https://docs.microsoft.com/en-us/sql/relational-databases/pe...): > The Missing Indexes report shows potentially missing indexes that the Query Optimizer identified during query compilation. However, these recommendations should not be taken at face value. Microsoft recommends that indexes with a score greater than 100,000 should be evaluated for creation, as those have the highest anticipated improvement for user queries. > Tip > Always evaluate if a new index suggestion is comparable to an existing index in the same table, where the same practical results can be achieved simply by changing an existing index instead of creating a new index. For example, given a new suggested index on columns C1, C2 and C3, first evaluate if there is an existing index over columns C1 and C2. If so, then it may be preferable to simply add column C3 to the existing index (preserving the order of pre-existing columns) to avoid creating a new index.
• polskibus 5y ago
  Postgresql does not have real clustered index (one that is automatically maintained) that Ms SQL has (and other DBs). Such feature is important for a lot of popular workloads.
• lifepillar 5y ago
  One thing PostgreSQL would likely not be able to adapt to, at least without significant effort, is dropping MVCC in favor of more traditional locking protocols. While MVCC is fashionable nowadays, and more or less every platform offers it at least as an option, my experience, and also opinions I have heard from people using SQL Server and similar platforms professionally, is that for true OLTP at least, good ol’ locking-based protocols in practice outperform MVCC-based protocols (when transactions are well programmed). The “inconvenient truth” [0] that maintaining multiple versions of records badly affects performance might in the future make MVCC less appealing. There’s ongoing research, such as [0], to improve things, but it’s not clear to me at this point that MVCC is a winning idea. [0] https://dl.acm.org/doi/10.1145/3448016.3452783 https://dl.acm.org/doi/10.1145/3448016.3452783
  • truenindb 5y ago
    There is absolutely no reason at all that using a Model View Controller architecture should say anything about your persistence layer. Model View Controller is an abstraction for managing code that generates things that are put on a screen. It says that you should have code that represents underlying data separate from code that represents different ways to show the data to the user and also separate from code that represents different ways for the user to specify how they would like to view or modify the data. The Model portion of MVC should entirely encapsulate whether you are using a relational database or a fucking abacus to store your state. Obviously serializing and deserializing to an Abacus will negatively impact user experience, but theoretically it may be a more reliable data store than something named after a Czech writer famous for his portrayl of Hofbureaucralypse Now .
    • nextaccountic 5y ago
      What's being discussed is MVCC, or multiversion concurrency control https://en.wikipedia.org/wiki/Multiversion_concurrency_control https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...
  • Andys 5y ago
    Having time travel capabilities (eg. cockroachdb) is a really useful side effect of MVCC though. Postgres once had this capability. Need for garbage collection/vacuuming is a downside. I think it all depends on the pattern of reads, writes, and the types of writes. Mysqls innodb is often faster than postgres but under some usage patterns suffers from significant lock contention. (I have found it gets worse as you add more indexes)
• mamcx 5y ago
  Too much focus in the "scalability" that only matter for a very narrow niche and lateral to the DB engine, so I instead focus in real progress/improvements for RDBMS (one of my dreams is doing this): - Algebraic data types, removal of NULLs. - Including a relational language, not just a partial query language (SQL). (I making one at https://tablam.org https://tablam.org, just to get the idea) - So, is full relational (you can store tables in tables, you can model trees with table because above, etc) - SQL is a interface for compatibility and stuff, but the above is for the rest, because: - The engine is not a full black box but a composite of blocks so: -- The inner and only only black box is the full ACID storage layer, that is concerned in manage PAGEs, WALs, etc made in a lang like Rust. -- The user-facing/high-level storage layer is above this. I think this will allow to code it in the lang above because exist: -- A pluggable language interface (making a "WASM for database/VM") that others (like SQL) compile to. And probably WASM for stored procedures and/or extend it, THEN -- This will allow to compile "SELECT field FROM table" CLIENT-SIDE and check it! (after supplied with the schema definition), AND TOO: - Because it not have a limited query language but one that is full, you can code a new INDEX with it. Note how do it in any language (ignoring the complexity of storage and acid, this is where a high-level interface is needed) is simple, but impossible in current RDBMS. - Because the DB is truly, fully, relational, you can do "SELECT * FROM Index" - Then, you can add a cargo-like package manager to shared code to the community - Then, you can "db-pkg add basic-auth" to install stuff like auth modules that are actually used, not like the security that is included in old database for a use case not many care for - Allow to make real-time subscriptions to data/schema changes - Make it HTTP-native, so is already REST/GrapQL/WebSocket/etc endpoint-capable and - Go extra-mile with the idea of Apache Avro or similar and make the description of the DB-schema integral to it, so you can compile interfaces to the db - Store the schema changes, so it have in-built MIGRATION support (git-like?) - Then auto-generate DOCS with something like swagger? ---- In relational to the engine itself: - The storage is mixed row/columnar (PAX-like) to support mixed-workloads - The engine, like sqlite, is a single library. Server-support is another exe and the package manager is what install support for operation - The DB is stored in a single-file? - We want to store: -- Rows/Tables: BTrees + PAX like today, nothing out-of-ordinary -- LOGs/Metrics: is the same as the WAL!. A rdbms already have it, but is buried: Allow to surface that, so you can do 'SELECT * FROM my_wal" -- Vectors: Is the same as a PAX storage but one where is only 1 column -- Trees: Is something you can do if the DB is truly relational and allow to store tables/algebraic types on it IF the storage have a high-level interface and exist a full-featured language ("WASM-like") interace to it, you can add the optimizations to the query planner and the code that manipulate the data without demand to get into the deeps of the engine. This mean that people that want to disable the query planner, INSTEAD NEED to improve it! IF the query planner is a component of the engine that is surfaced, and can tweak it.
• mikewarot 5y ago
  Relationships between tables, as supported by Microsoft Access.. it did cascaded deletes and things automagically. Persistent queries, where any change to the answer is propagated, like a subscription to a feed.
  • majewsky 5y ago
    ON DELETE CASCADE has been in SQL since approximately forever.
    • mikewarot 5y ago
      Ok, so this is a new thing since 2003, thanks for letting me know. Of course, it's best to do both, to avoid orphan records ON DELETE CASCADE ON UPDATE CASCADE
• zz865 5y ago
  My biggest problem with databases is always versioning. IE renaming a column will break old clients. If there was a way you could have multiple schema versions so you could upgrade database then clients later it would be the best. EDIT: yes thanks for the comments, views and creating and API layers and adding instead of subtracting do all work, but I believe they're all workarounds for the underlying problem. Fixing versioning would make everyone's lives easier.
  • DanHulton 5y ago
    Consider using Postgres views! You can create a view into your data and use that for reading and writing. Then, when you need to change the underlying data model, you create a new view with the renamed column. Old clients will target the old view, new clients will target the new view. Then, when all old clients are removed, you can remove the old view safely.
    • topspin 5y ago
      "Consider using Postgres views!" The minute you create a view modifying the underlying table usually gets blocked, in many cases even for changes that have zero relevance to the view. You have to drop the view(s) to get the ALTER TABLE to go through. Absolute nightmare. Oracle, for example, does a far better job of handing this type of evolution and dependency management.
      • zozbot234 5y ago
        > You have to drop the view(s) to get the ALTER TABLE to go through. So? With Postgres you can use transactions in your DDL. So it's possible to seamlessly drop the view, alter the underlying table and recreate it, all in one step once the transaction is commited.
        • topspin 5y ago
          "it's possible to seamlessly drop the view, alter the underlying table and recreate it" And if that were the only problem it wouldn't be too bad, except that other databases don't inflict this task or limit the degree of the problem far better. Unfortunately the problem isn't limited to views. Materialized views, for instance, have to be dropped and recreated along with their indexes. That can involve a large IO operation; one that risks failure. Pretty soon an otherwise innocuous ALTER TABLE that conceptually has zero impact on dependencies becomes a major undertaking.
• deergomoo 5y ago
  I am yet to actually use it for anything, but I like the sound of what EdgeDB is doing [0][1]. The tl;dr is that its schema definitions and query language align much more closely to typical app logic, theoretically eliminating the draw of an ORM and being very good at things that are unwieldy with SQL, like retrieving sets of related records. [0] https://www.edgedb.com/showcase/data-modeling https://www.edgedb.com/showcase/data-modeling [1] https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql
• masa331 5y ago
  This title kind of implies that PG or MySQL aren't modern or modern enough which i think is is very wrong. Look what they bring in every update. I think they are quite modern!
  • kixiQu 5y ago
    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.
    • tmh88j 5y ago
      >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 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
      • deleted 5y ago
        [deleted]
      • kixiQu 5y ago
        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.
    • jffry 5y ago
      I think it's unfair. "Modern" is in no way equivalent to what the thread is really asking, which is what could you do if you built a new database today with all of the knowledge but none of the existing commitments of a large mainstream database.
      • Retric 5y ago
• truenindb 5y ago
  Basically they aren't going to impress your non-technical boss who is your boss for some inexplicable reason. Postgres has been able to function as key value store(which is basically what most NoSQL "engines" basically are, write once append only key value stores) for quite a long time now. Writing code to query your key-value store is another story. The rest of my admittedly very snarky, but arguably highly accurate commentary on various veins of discussion on this topic will be left in a time capsule below for future generations of people who actually have a legitimate interest in computer programming to discover after the Taliban are driven out of the computer industry. Theoretically you can replace hiring smart programmers with magical artificial intelligence that reads your mind and then uses the computer to bend the world to your iron will, but if someone else could produce such a magical artificially intelligent database why would they share it with you? Perhaps they are an omnipotent omnipresent omnibenevolent entity that loves you just a little bit more because of your good looks and high intelligence? Is this different from wanting to do a persistent query? Basically, since you have a programmable computer that you can program to talk to the database program, you write a computer program that periodically queries the database and then takes the action when the condition is met. SQL is an implementation of a mathematical descriptive language for relationships. The whole point is that temporal logic like "wait until this happens, then do this" can be kept somewhat separate from logic describing the data you are tracking. You have SQL that describes what it is you want to store, and particular questions you want to ask about what it is you want to store, and then the job of the database program is to figure out how to store the data safely and efficiently and answer your questions quickly. How you write the SQL that describes the way you want to store the data depends some on what kind of questions you want to ask, and this is what an actually skilled "Database Application Programmer" can figure out for you. Some proprietary(and probably also Postgres) databases do provide support for the kind of thing you are asking to do here in the form of what are called "Stored Procedures" . Your average corporation accumulates an utter shitload of these stored procedures that various non-technical technical question askers in different departments don't tell each other about and they are often doing the same thing in different ways at different times. Then later they crash the database and break the application itself because there is insufficient technical oversight and communication at the actual decision making levels of the corporate bureaucracy. Long story short, do all of this stuff in persistent queries done outside of the database and tracked in a shared medium like a wiki page, or even better a physical notebook routinely reviewed as part of management actually doing some managing. https://en.wikipedia.org/wiki/Gantt_chart https://en.wikipedia.org/wiki/Gantt_chart This is an indication that you are not using a Model View Controller approach to building your database client. There are some python ORMs that were doing this automatically correctly a decade ago, but there are presuambly still several corporations with hundreds of millions of dollars using Enterprise Java Beans in 2021, also maybe some people with nuclear arsenals as well so you shouldn't consider yourself too behind the times.
• gkoberger 5y ago
  Oh I've been thinking a lot about this! It might not be the answer you're looking for since they're all very UX-related and not particularly database-specific, however I have a few ideas. Sometimes I feel like databases were created by people who never built a website before. Most websites are pretty similar, and databases historically have never felt (to me at least) "modern". I always feel like I'm fighting against them, and making usability concessions for the sake of performance. First, the ability to subscribe to external data sets. I feel like I spend so much time writing crappy syncing code with external APIs (like Clearbit, GitHub, etc), and it would be so much nicer if I could just "connect" with them and know it will be fairly up to date. I also think there's so many things everyone finds themselves redoing for no reason. For example, almost every site on the internet as a user database with sessions, and each user has an email (that must be valid + verified), a password (that's encrypted + salted) and 2FA (which everyone is basically implementing themselves). It'd be so nice if the database just "knew" it was a user, and you were tweaking the presets rather than building it from scratch. Every single company has similar workflows they each solve themselves (often in insecure ways): they all have a production database, staging environments, migrations, direct access for customer support to fix things, local db access/clones for development, etc. I'd LOVE a database that was created with all these use-cases in mind... such as a way to connect to a DB locally but scrub sensitive data, take care of migrations seamlessly, etc. This might be a bit too "in a magical world"-y, but I'd love to not have to think about tradeoffs. Kind of like an automatic car, I'd love my database to be able to shift based on the types of data and amount of read/writes. At my company, we have 3-4 different databases for different reasons (Mongo, REDIS, ElasticSearch, ClickHouse), and it gets really difficult to keep all the data synced and connect them behind the scenes. I'd love to just never have to think about the low-level data store ever again, and have the DB do all the work without us having to worry. There's a number of primitives that I think are used a lot, and it'd be amazing if they were built in. For example, time. It'd be great to easily get the difference between two times, or total the times of a bunch of rows. Airtable has a time primitive, and it's amazing how much friendlier it is to use. Overall, I'd also love it to just feel a lot more like Airtable, including an Airtable-like interface for working with it (right down to the ability to create custom views and create on-the-fly forms people can submit data to). I honestly use Airtable for most of my DB needs these days (for one-off small projects), and it's such a delight to use. Maybe I'm underestimating the importance but... I feel like databases are pretty performant these days. I hope that we can start seeing dramatic UX improvements, since we don't have to optimize for performance the same way we have in the past.
• jake_morrison 5y ago
  VoltDB is a good example of rethinking relational databases for for modern technology. Traditional databases assume that everything is stored on disk, with a bit of RAM available for cacheing. VoltDB assumes that everything is stored in RAM first, using checkpointing to disk and replication to get durability. https://www.usenix.org/legacy/events/lisa11/tech/slides/stonebraker.pdf https://www.usenix.org/legacy/events/lisa11/tech/slides/ston... Have a look at Michael Stonebraker, he's a database genius, and keeps starting "NewSQL" companies which use the relational model, but specialized for different applications, e.g. column stores. https://en.wikipedia.org/wiki/Michael_Stonebraker https://en.wikipedia.org/wiki/Michael_Stonebraker
  • winrid 5y ago
    Neat. Storing everything in ram and checkpointing to disk is how a lot of game servers work, too. :)
  • hestefisk 5y ago
    Volt is indeed very cool. Michael Stonebraker wrote part of the original Ingres code, didn’t he?
• truculent 5y ago
  I would be interested in more sophisticated type definitions. That is, algebraic data types. One can achieve this to a degree by storing the data as JSON, but it would be nice to be able to remove the chance of introducing errors when converting to/from JSON.
• znpy 5y ago
  be multi-master and horizontally scalable out of the box.
• pornel 5y ago
  I'm missing something as easy to deploy as SQLite, but without its performance problems (like the stop-the-world write lock). Basically, I'd love an embeddable Postgres with a single-file storage.
  • psanford 5y ago
    If you are using SQLite and care about performance, be sure to run in WAL (Write Ahead Log) Mode: `PRAGMA journal_mode=WAL;`[0]. WAL mode is significantly faster and does not have a "stop-the-world write lock" ("Reading and writing can proceed concurrently"). WAL mode is also more efficient with disk I/O, which makes it faster for single user applications as well. [0]: https://sqlite.org/wal.html https://sqlite.org/wal.html
  • sterlinm 5y ago
    Check out DuckDB https://duckdb.org/ https://duckdb.org/
  • goughy 5y ago
    This: https://firebirdsql.org/ https://firebirdsql.org/
• pat2man 5y ago
  Cockroachdb: automatic multiple primaries + sharding.
• darksaints 5y ago
  Get rid of NULLs and ternary logic.
• ww520 5y ago
  What is modern? If we think how databases run better in the modern hardware, the trend goes toward plenty of RAM and fast disk (SSD/memory-disk) that doesn't have the disk seek problem of the old days. Modern databases being developed today aim for those environment.
• laurencerowe 5y ago
  More from a document database rather than an OLTP standpoint, but working with JSON data in Postgres I'd really like to see: * Exclusion Constraints support for GIN indexes (which supports Arrays and JSONB.) This would allow unique keys on array elements without having to use triggers to put the individual array elements in another table. * Array element foreign keys, which I think is a subset of the Inclusion Constraints proposal (which would additionally allow constraints into ranges.) * A faster procedural language. While plv8 is an improvement over plpgsql, it still seems substantially slower than running the equivalent in a separate NodeJS process. (Possibly just build issues for me.)
• Chyzwar 5y ago
  1. High performance read/write of Scylla/Cassandra with high availability[1]. It has some limitations for OLTP workloads and require careful planning. Postgres without Citus is not really HA and even then companies would often invent custom sharding/cluster management system. 2. Powerful graph query language like Cypher. It might not perform well in real life, but my personal experience left me amazed[2]. There is a number of issues with SQL that could be addressed[3], but current standard is way too much prevalent. 3. Zero impedance mismatch between database and application representation. In database like Smalltalk GemStone it is really seamless experience for developer to write database code and application code[4]. To some extent, MongoDB success can be attributed to this aspect. 4. Datomic temporal capabilities[5]. It is hard to maintain temporal tables in postgres. There are some use cases where you really want query in point of time. Strictly not an OLTP feature, but I can see this be usefully in many scenarios. [1] https://www.youtube.com/watch?v=Fo1dPRqbF-Q [2] https://www.youtube.com/watch?v=pMjwgKqMzi8&t=726s [3] https://www.edgedb.com/blog/we-can-do-better-than-sql [4] https://www.youtube.com/watch?v=EyBkLbNlzbM [5] https://www.youtube.com/watch?v=7lm3K8zVOdY Edited: independence -> impedance
  • pas 5y ago
    > independence mismatch you probably mean impedance mismatch, right? https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
  • zozbot234 5y ago
    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.
    • zem 5y ago
      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.
• deleted 5y ago
  [deleted]
• jen20 5y ago
  I’m somewhat surprised that “zero downtime failover” does not feature more prominently in responses to this - that would be the biggest thing I’d be looking for in a database for OLTP.
• zamalek 5y ago
  Write-expensive, read-cheap[1]: the exact opposite of the mentioned. Once your developers have completed an iteration, your DB will see the same queries over and over again (if it doesn't, then it should be an OLAP aggregate). These databases optimize for writes, and defer complexity to reads and, considering that you could see millions more reads than writes, makes no sense whatsoever. [1]: https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria
• bigblind 5y ago
  It's always been insane to me that databases aren't better at migrations. There are so many blog posts out there on how to do zero downtime migrations, and I feel like a lot more of that could probably be abstracted away.
  • vaughan 5y ago
    There are so many times I wish a blog post was an app that just had a nice gui to do guide me through the steps they suggest, instead of manually modifying config files and running bash scripts. SQL GUI tooling is lacking.
• estebarb 5y ago
  Materialize (https://materialize.com/ https://materialize.com/) is capable of performing SQL operations over a stream, using incremental calculation based on differential dataflow. CockroachDB is a distributed SQL database, with strong consistency. I think that Yugabyte is similar. Support for realtime changes, including queries over those changes, is better in other databases, like RethinkDB, OVSDB or Firebase. Relational is not always the best way to store your data. Sometimes a graph model is better. Or for full text search something specialized is better. In that sense dgraph or elastic search may be better OLTP databases in some cases. Columnar storage, like used in Vertical or BigQuery have several advantages in processing and data compression. But implementing it in PG or MySQL I think that would require almost a full rewrite.
  • strken 5y ago
    I really want to do streaming queries over updates and inserts in a normal SQL database. Imagine writing SUBSCRIBE SELECT customers.id, customers.name FROM EVENTS(customer_purchases) AS cpe LEFT OUTER JOIN customers ON cpe.row.customer_id = customers.id WHERE cpe.row.product_id = '123abc' AND cpe.type IN ('update', 'insert') in postgres itself and just getting every incoming purchase for a particular product.
    • gigatexal 5y ago
      I wonder if Materalize gets you close?
      • strken 5y ago
        Materialize excites me, but probably can't be used for the current problems I'm working on, because of the operational overhead we'd be putting on our clients. Streaming SQL queries inside an existing well-understood database would be the killer feature.
    • hchasestevens 5y ago
      https://ksqldb.io/ https://ksqldb.io/ is great for this, assuming you're already using Kafka.
• jasonhansel 5y ago
  Better consistency and transaction isolation models. The ANSI transaction isolation levels are ambiguous and confusing, and the different consistency models supported by NoSQL databases are often poorly-specified. A database with clearer abstractions around consistency and isolation would be a big win for applications where correctness and scalability are both high priorities.
• Kinrany 5y ago
  A better query language. It should provide control over execution for performance-sensitive queries.
• fnord77 5y ago
  druid / pinot - ingest-time aggregations, plus aggregation of sets on the server, like hyperloglog or theta sketches at either ingest or query time
• jsperson 5y ago
  I’m surprised no one has mentioned Snowflake. The biggest feature here is no worries. It scales in every dimension, is quite stable, and has few knobs to turn. I’m on a Redshift project now, and the differences are significant. It’s not hard to overload the cluster with a complex query. With Snowflake I would just create my own cluster with the same data shared on the back end. While it’s possible to do something similar with redshift, in practice the configuration and DBA culture make it much more difficult. Edit - reread the question and it mentions OLTP workloads. Snowflake and Redshift are specialized analytics DBs so they don’t meet that criteria.
• brainbag 5y ago
  Some thing I've encountered in practically every project in the last 10 years is a large number of calculated attributes that have to be in code or in triggers. I know MySQL and PostgreSQL both have generated/virtual columns but they are in-table only, and this can be accomplished with triggers, but I want to be able to simply define my table schemas based on schemas from other tables, and have the database system handle it automatically.
• jstgord 5y ago
  stored procedures/functions in javascript, out of the box ?
• bbertelsen 5y ago
  One really cool thing in the analytics space is the adoption of columnar data stores. PostgreSQL and MySQL don't do this. (If they can please correct me!). This is for embedded loads, situations where you might use SQLite, there are now really interesting alternatives like MonetDB and DuckDB. Paper: https://hannes.muehleisen.org/SIGMOD2019-demo-duckdb.pdf https://hannes.muehleisen.org/SIGMOD2019-demo-duckdb.pdf
• xupybd 5y ago
  This is a hard question to answer. You can do most anything with the wrong tool. I'd rather ask the question, when is PostgreSQL or MySQL the wrong tool for the job. I'm not sure I'm qualified to answer this, but I can point you in the direction of a book that has given me a much better understanding of the space. https://www.oreilly.com/library/view/designing-data-intensive-applications/9781491903063/ https://www.oreilly.com/library/view/designing-data-intensiv...
• dheera 5y ago
  MongoDB: Stashing unstructured JSON data that you don't really know how you might want to query later. Also, getting up and running with an investor demo ASAP with zero technical fuss, because you have a startup idea but you're broke and can't pay your next month's rent unless you either (A) finish this demo and get that investor money next week, or (B) quit working on your idea and take the Google offer. (Yes, I've actually been there.)
  • overgard 5y ago
    Postgres can store (and query) unstructured JSON quite well (and I don't mean as text, I mean as a mongodb style document)
  • acjohnson55 5y ago
    You can stash unstructured JSON data in Postgres just fine! https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/ https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-e...
    • spaetzleesser 5y ago
      I just wish the syntax was nicer :-(
  • kokizzu3 5y ago
    yup PostgreSQL's JSONB can do this
• OrvalWintermute 5y ago
  Postgresql can do everything I need, but not everything I want, and apologies to mysql fans, but I refuse to touch it. And, I think over the years they have been such an awesome community that has helped me irregularly when I popped into IRC every year or two for a problem I was having. https://github.com/davidfetter https://github.com/davidfetter ; https://github.com/RhodiumToad https://github.com/RhodiumToad ; https://github.com/bmomjian https://github.com/bmomjian - you are great peeps! What I would like to see are more security usability enhancements over and above what you find in https://www.crunchydata.com/products/hardened-postgres/ https://www.crunchydata.com/products/hardened-postgres/ Security Enhancements List: Attack Resistance Better protections for the DBMS itself in assume compromised scenarios, or malicious users Customized implementations per compliance/security regime Accelerators for various security configurations Self-audit, self-secure Functionality Enhancements List: Continuous Sync Postgres to other serious DB (not a one-way sync, not a DB move)
• acjohnson55 5y ago
  For Postgres: a simpler, scalable connection model.
• EGreg 5y ago
  Byzantine Fault Tolerance in distributed systems :)
• kokizzu3 5y ago
  easily add a new node in one line command and autorebalance i guess..
• cafxx 5y ago
  - 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-query ACID properties (e.g. delayed indexing) - All of the above while maintaining complex SQL features like arbitrary joins, large transactions, triggers, etc. Sure, some of these are in some form in some existing databases. But none offer all of them.
  • vaughan 5y ago
    > 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.
    • cafxx 5y ago
      > > 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) If some_complex_condition filters out a lot of rows then all the data movement has been wasted: it would be much more effective (if possible) if some_complex_condition was pushed down to the database. But if some_complex_condition is compute intensive then potentially pushing it down to the db may actually slow things down, as the db becomes the bottleneck... so the optimal solution, if it exists, 1) is likely to change over time because of changes in the workload and 2) is unlikely to be determinable before runtime. 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").
• kokizzu3 5y ago
  a hype XD https://github.com/kokizzu/list-of-tech-migrations https://github.com/kokizzu/list-of-tech-migrations
• gigatexal 5y ago
  Lots of talk of CockroachDB in this thread but no mention of vitess.io? Shame. While I don’t care for MySQL the folks at planetscaleDB (hosted vitess) are doing amazing work. So are the CRDB folks.
  • ksec 5y ago
    Because HN is predominantly a Postgres and Anti Oracle community, so the use of MySQL doesn't fit their ideology. Which is unfortunate because I think Vitess and Planetscale is quite nice.
    • gigatexal 5y ago
      re: Oracle Oracle's super heavy handed approach to sales and extracting every nickel from their cusotmers likely makes them a lot of enemies even though technologically the Oracle Enterprise DBs are likely very capable.
• lmm 5y ago
  - True master-master with easy autoscaling out of the box - Expose lower-level access to MVCC etc. Something finer grained than global transactions. - Similarly, make indexes more first-class and visible.
• vaughan 5y ago
  1. Streaming SQL Real-time queries should be the default. Some kind of smart query invalidation (similar to incremental view maintenance) as a result of DML statements. 2. Can run in a web browser / mobile phone Your client-side cache is usually a messy denormalization of your SQL database. For offline/local-first apps you essentially need to run your entire backend API in your browser. So you should be running an SQL database in the browser. For this to be seamless you need your database targetting JS during design stage. 3. Syncing To support offline/local/mobile use cases, your database needs to be able to sync changes efficiently. 4. Logical-first data schema You should be able to give your db a logical schema instead of a physical one, and the database can take care of the necessary denormalizations for performance reasons automatically. 5. Lower-level api for query planner Instead of having SQL as your only interface, there should be a way to interact with the query tree and the query plan directly. People are always creating new query languages hacked over SQL (like Google's Logica). 6. Graph-based queries Support a query language like Cypher. Provide better solutions for tree-based data structures - the queries get too unwieldy today.
  • iaml 5y ago
    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?
    • vaughan 5y ago
      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/GraphQL cache in SQL. This would require maintaining mappings of your API response fields to SQL tables. Going further, you could implement your backend API on the client, and have requests to it go directly to your browser SQL db. The benefit of this is you write your API once, and you get full offline support. If you don't need "local-first" then it's just a matter of figuring out when a query becomes invalid on the server. I could show instant results from local db, and then send a query to the server to ask if the data I already have in my cache can fulfill this request. Could optimize this on the server by listening to DML queries via WAL/LISTEN. WebSockets would be used to maintain real-time updates to only the data you are viewing. You could also just use SQL directly as your UI API and then trigger these queries on the backend (respecting security of course). What's doesn't feel optimal though is subscribing to sqlite.js updates in the browser. This makes me feel like we need an SQL db written in JS. Also, if our DB is running in the same execution environment as our frontend and has one consumer, we could store each row as a JS object, and then reference this directly in our views, and subscribe to updates on a row-by-row basis. So if you are rendering a large table, if a column in a single row changes, when this row is updated in the database, instead of re-rendering the entire table manually, (or smartly detecting if the query was invalidated), you just bind to updates on the row instance, which is what the db is actually storing. I think this would reduce huge amounts of code. Local-first is a little more difficult. There is some good exploration here[4]. I think writing a db in JS on the backend is not such a bad idea either. The hot paths would be written as Rust addons, but the majority of stuff people want today is just implementing the right logic to automatically handle denormalizations and subscriptions which we already hack together in our application layer using JS et al. [1]: https://github.com/jlongster/absurd-sql [2]: https://github.com/google/lovefield [3]: https://github.com/agershun/alasql [4]: https://actualbudget.com/blog/
• Glyptodon 5y ago
  This is pretty stupid, but I'd be happy to see a SQL dialect where a statement can start with a FROM clause. This is also pretty stupid, but there are also some cases where I wouldn't hate some sort of "JOIN AS CHILD" feature along with results that aren't 100% tabular.
  • eurasiantiger 5y ago
    You’re looking for a graph database.
• 7kmph 5y ago
  Make you think that there is hope for humanity after all
• dynamite-ready 5y ago
  CouchDB. - Very smart clustering/redundancy features - Can be queried over HTTP - Has a great API - Counterpart project (Pouch DB) can work on the client - Queries can be cached (it's HTTP) - Said to be 'schemaless' but can be structured The project is always overlooked, but I still feel it's one of the better NoSQL datastores.
• GeneralMayhem 5y ago
  Lots 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.
  • papaf 5y ago
    Two things stick out here that I don't fully understand. Declarative schemas, and a well-defined update process (that isn't human-written DDL), are essential at any sort of organizational scale. Isn't this impossible because some schema changes require data migration? A data migration cannot be declaratively automated as far as I know. Queue support Why not use a dedicated and feature rich queue such as Rabbit MQ or, if you want to get really fancy, Kafka?
• travisgriggs 5y ago
  Late to the discussion. As others have pointed out, PostgreSQL is plenty modern and capable. Whatever a “modern” database did that Postgres didn’t, would show up in Postgres soon after said “new” technology had stood the public test of “is it worth it.” There’s a variant of the question that interests me though. It’s said that much of large scale software now days is built the way we build our cities. One layer on top of another on top of another. Even though a city like London is large and capable and full featured, it has years of history below it. And sometimes that slow evolution even complicates things. Some might argue, that if we had unlimited resources, we could go find a nice new piece of virgin ground and build a New London, that could be simplified and not contend with its historical baggage. So by analogue, if we could respin a fresh new simplified/unified/rationalized version of postgresql today, what would it look like?
• jhoechtl 5y ago
  Bitemporal tables. I sorely miss them on PG. MariaDB has them https://mariadb.com/kb/en/bitemporal-tables/ https://mariadb.com/kb/en/bitemporal-tables/
• d--b 5y ago
  Built-in versioning and row-level permissioning
• ypcx 5y ago
  Learn a NN model incrementally on INSERT and then use abstraction and inference on SELECT.
• bullen 5y ago
  The problem with SQL databases is that the earliest where designed before the internet became widespread. So they have synchronized clients that do not use HTTP or JSON. You need asynchronous clients that do not consume thread context switches. Also you always want to replicate all data in more than one location in real-time because 100% read uptime is really easy to provide, while 100% write uptime is a complex nightmare. I made this database after using MySQL, Oracle and Postgres for 10 years (it's a 2000 line replacement for my needs that these databases filled, a very narrow subset of the features you'll find in legacy databases): http://root.rupy.se http://root.rupy.se
• sylvainkalache 5y ago
  Vertical integration with container technologies such as Kubernetes.
• benjaminjosephw 5y ago
  PostgreSQL and even things like SQLite are fantastic tools for programmers with time to invest in learning them an their quirks. What a modern database could do that they can't is be comprehensible to a power-user. Modelling and querying data could be far simpler than SQL makes it. A modern database language/interface would be far more useful to most than general purpose programming languages are. We can do better for end-users than forcing them to grapple with archaic `SELECT` statements. The crowd that have invested in learning the language already will disagree, of course, but far too many newbies self-select out of that group because it seems too complicated to them. It doesn't have to be so.
  • jeltz 5y ago
    Maybe, but I feel most modern databases move in the other direction by being even more focused on the power users.
• yencabulator 5y ago
  I would love to see support for compute near data ("stored procedures") that is compatible with today's practices: real programming languages, version control, testing, etc. Calvin (http://cs-www.cs.yale.edu/homes/dna/papers/calvin-sigmod12.pdf http://cs-www.cs.yale.edu/homes/dna/papers/calvin-sigmod12.p...) and VoltDB are examples of different architectures that trade off the ability to do SQL-style interactive transactions (`BEGIN`+run code client side+`COMMIT`), forcing everything through predefined procedures. Combining that with better stored procedures is interesting.
• yencabulator 5y ago
  One thing Firestore's JS client library does that is very interesting is that mutations by the in-browser client show up in persistent queries, even before they sync with the server, even offline. You can tell which changes are safe on the server and which not by metadata. This greatly improves latency and simplifies code. Trying to manually merge the mutations into all of your queries would be very awkward. Also, the whole concept of browser clients interacting directly with the database server is darn interesting. Combine that with better support for stored procedures etc for actually enforcing business logic (somewhat made possible by Firestore security rules, but very awkward).
• jfbaro 5y ago
  1) Native Bi temporal capabilities without hurting (too much) performance. 2) Distributed DB, like Spanner and CRDB. 3) Real time analysis of performance and then adapt the DB config accordingly, without requiring HUMAM intervention. 4) Multiple (joint?) storage types per table (Memory, Columnar or Rows, for example); 5) Native support for Copy On Write replicas (useful for ephemeral environments, Stage and development) 6) Serverless capabilities