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

• Hasura

  227 30,799 9.8 TypeScript

  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

  54 6,460 9.0 Elixir

  Broadcast, Presence, and Postgres Changes via WebSockets

  

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.

• SurveyJS

  surveyjs.io sponsored

  Open-Source JSON Form Builder to Create Dynamic Forms Right in Your App. With SurveyJS form UI libraries, you can build and style forms in a fully-integrated drag & drop form builder, render them in your JS app, and store form submission data in any backend, inc. PHP, ASP.NET Core, and Node.js.

  

• yugabyte-db

  87 8,486 10.0 C

  YugabyteDB - the cloud native distributed SQL database for mission-critical applications.

  

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

  151 183 0.0 Rust

  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

  5 173 1.8 Go

  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

  4 162 5.3 Go

  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

  2 36 0.0 JavaScript

  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

• InfluxDB

  www.influxdata.com sponsored

  Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.

  

• mysql-live-select

  2 3 0.0 JavaScript

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

• list-of-tech-migrations

  14 495 6.6

  list of public tech migrations

• absurd-sql

  24 4,050 2.5 JavaScript

  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

  6 6,839 0.0 JavaScript

  Discontinued 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

  8 4,525 2.2 Go

  PostgreSQL cloud native High Availability and more.

  

• citus

  61 9,801 9.5 C

  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

  93 16,924 10.0 Go

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

• WorkOS

  workos.com sponsored

  The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.

  

Suggest a related project