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

This page summarizes the projects mentioned and recommended in the original post on news.ycombinator.com

Our great sponsors
  • Appwrite - The Open Source Firebase alternative introduces iOS support
  • SonarQube - Static code analysis for 29 languages.
  • Scout APM - Less time debugging, more time building
  • Hasura

    Blazing fast, instant realtime GraphQL APIs on your DB with fine grained access control, also trigger webhooks on database events.

    Hasura evaluated Postgres' listen/notify feature to power their subscriptions, but chose polling instead:

    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. I'd like to see more DBs bake that feature in, like RethinkDB did.

  • realtime

    Listen to your to PostgreSQL database in realtime via websockets. Built with Elixir.

    The Superbase (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.

  • Appwrite

    Appwrite - The Open Source Firebase alternative introduces iOS support . Appwrite is an open source backend server that helps you build native iOS applications much faster with realtime APIs for authentication, databases, files storage, cloud functions and much more!

  • yugabyte-db

    The high-performance distributed SQL database for global, internet-scale apps.

    May I suggest looking at YugabyteDB, which is a distributed SQL database built on actual PostgreSQL 11.2 engine? It's way ahead of CockroachDB in terms of feature support.

    YugabyteDB has UDFs, stored procedures, distributed transactions, the range types are working from what I can tell, at least the example from here: https://wiki.postgresql.org/wiki/Extract_days_from_range_typ... works right out of the box, just copy paste.

    YugabyteBD == Postgres. The query planner, analyzer and executor are all Postgres. Mind you, some features are not readily available because handling them properly in a distributed manner takes effort. Those unsupported features are disabled on the grammar level, before being worked on. But - unsupported features will not corrupt your data. Missing features are enabled very fast.

    For example, I have recently contributed foreign data wrapper support: https://github.com/yugabyte/yugabyte-db/pull/9650 (enables postgres_fdw at a usable level) and working on table inheritance now.

    Yugabyte is an amazing bit of technology and more people should know about it. By the way - it's Apache 2 with no strings attached.

  • TablaM

    The practical relational programing language for data-oriented applications

    Too much focus in the "scalability" that only matter for a very narrow niche, 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, 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 that:

    -- The inner and only only black box is the full ACID storage layer, that is concerned in manage PAGEs, WALs, etc

    -- The user-facing/high-level storage layer is above this. I think this will allow to code it in the above and:

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

    ----

    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

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

  • sqlite3vfshttp

    Go sqlite3 http vfs: query sqlite databases over http with range headers

    There's a bunch of projects that have implemented this. I wrote a SQLite VFS in Go that lets you query a read-only SQLite db over http (including from s3) [0].

    The VFS API offers the possibility for weirder storage solutions, if thats the type of thing you're into. Recently I've been moving some of my personal websites hosted on AWS Lambda over to use a read/write sqlite db backed by DynamoDB[1]. There are a bunch of limitations to this type of thing (like it uses a global write lock), but it works nicely for DBs that have low write frequency.

    [0]: https://github.com/psanford/sqlite3vfshttp

    [1]: https://github.com/psanford/donutdb

  • donutdb

    Store and query a sqlite db directly backed by DynamoDB.

    There's a bunch of projects that have implemented this. I wrote a SQLite VFS in Go that lets you query a read-only SQLite db over http (including from s3) [0].

    The VFS API offers the possibility for weirder storage solutions, if thats the type of thing you're into. Recently I've been moving some of my personal websites hosted on AWS Lambda over to use a read/write sqlite db backed by DynamoDB[1]. There are a bunch of limitations to this type of thing (like it uses a global write lock), but it works nicely for DBs that have low write frequency.

    [0]: https://github.com/psanford/sqlite3vfshttp

    [1]: https://github.com/psanford/donutdb

  • meteor-mysql

    Reactive MySQL for Meteor

    I'm the defacto maintainer of the Meteor MySQL integration and the Node.js package mysql-live-select.

    These implement pub/sub and reactive queries using the MySQL binary log as the event source:

    https://github.com/vlasky/meteor-mysql

  • SonarQube

    Static code analysis for 29 languages.. Your projects are multi-language. So is SonarQube analysis. Find Bugs, Vulnerabilities, Security Hotspots, and Code Smells so you can release quality code every time. Get started analyzing your projects today for free.

  • mysql-live-select

    NPM Package to provide events on updated MySQL SELECT result sets (by vlasky)

  • list-of-tech-migrations

    list of public tech migrations

  • absurd-sql

    sqlite3 in ur indexeddb (hopefully a better backend soon)

    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

  • lovefield

    Lovefield is a relational database for web apps. Written in JavaScript, works cross-browser. Provides SQL-like APIs that are fast, safe, and easy to use.

  • stolon

    PostgreSQL cloud native High Availability and more.

  • citus

    Distributed PostgreSQL as an extension

    Maybe I'm reading it wrong, but that looks like yet another multi-master high-availability system, which is also an important property for a system to have but more or less unrelated tow hat I was talking about.

    As far as open-source add-ons for true horizontal scaling, I think [Citus](https://github.com/citusdata/citus) is the most well-known and sophisticated, but I don't have enough experience with it in production to have a particularly strong opinion yet. It might work quite well, but I still fundamentally doubt that it will ever be as good as a system that was designed to support horizontal sharding from the ground up.

  • dolt

    Dolt – It's Git for Data

    You should check out dolt, does exactly what you're describing, and is a drop-in MySQL replacement:

    https://github.com/dolthub/dolt

NOTE: The number of mentions on this list indicates mentions on common posts plus user suggested alternatives. Hence, a higher number means a more popular project.

Suggest a related project

Related posts