Or put another way, what are some cutting edge OLTP database techniques/features/architectures that PostgreSQL and MySQL would have a hard time supporting?
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.
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?
In the most general case, sure - although there are workarounds for some specific cases (e.g., including previously-known-as names in the declarative schema to allow automatically planning renames). But 99% of the time, you're adding and removing tables and columns in a way that's very well defined. This is one of those areas where the best solution is to legislate away the hard problems - a tool that covers 98% of schema changes automatically (the provably safe ones), and then fails/requires a human to approve the last 2% is still dramatically better than having humans manually write and sequence change plans all the time.
Data migrations will require human effort, but you can sequence the changes and isolate the parts that need different kinds of work. If you're changing a string to an integer, for instance, you can make it clear in your change history that you (1) add the new column, (2) start dual writes, (3) backfill, (4) move readers to the new column, (5) stop the dual write, (6) drop the old column. You can do that with checked-in migration code, but think about what you end up with at the end - with imperative migrations, you have the clutter from all of that history; with declarative schema definitions, you just have the final state, just like how it works for code.
Declarative schemas also usually come with nice ancillary properties - for instance, they can give you automatic rollbacks, because you can pack up the whole schema definition as a static artifact.
>Why not use a dedicated and feature rich queue such as Rabbit MQ or, if you want to get really fancy, Kafka?
Atomicity. It's really, really powerful to be able to write a row and send or receive a message as a single exactly-once transaction. Imagine a queue of events. The writer writes the event data, and sends a message as a notification that it exists; the consumer will keep some sort of aggregate (count, filtered total, etc.). With a separate queueing system, you have to explicitly manage the timing and error cases bridging different atomicity domains, usually reimplementing some sort of idempotency log on your own. If it's all in one place, you just write a transaction that reads the old value, consumes the queue message, and writes a new value.
Any links to clear, actionable and reasonably comprehensive examples of these would be most helpful. Obviously abstract statements of the required semantics are also needed, but I also need to see what actual code would look like.
TIA
If your queue is in the same database as your data, you can do "ack message/mark job as complete" and "commit result of job to database" in the same transaction. That simplifies a lot of the harder parts of things like materialization pipelines or BI systems.
It feels like the industry has largely failed at this in regards to the traditional RDBMSes. Solutions like TiDB attempt to remedy this in a transparent way, but there are simply too many limitations for it to be a feasible alternative for many projects out there: https://docs.pingcap.com/tidb/stable/mysql-compatibility#uns...
I've only ever seen PostgreSQL/MySQL/MariaDB/Oracle be deployed successfully as single instances in prod that are scaled vertically, because attempts to do otherwise either create issues with performance and resiliency, or make the entire system only have eventual consistency, which is as problematic as it is inevitable when it comes to horizontally scaled and distributed systems.
I don't think that there is a good answer to this, honestly. You will get either horizontal scalability OR data consistency - pick one.
> 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.
That's a good idea, but personally i've seen that almost all abstractions are leaky in one way or another. In the day of containers and highly customizable software that can scale from tens or hundreds of megabytes of memory with a fraction of a CPU core to an entire server with a hundred gigabytes of memory and dozens of CPU cores, it feels somewhat unnecessary to risk these abstractions.
Just make sure that your process for initializing the DB schema and seeding it with data for testing is fully automated and reproducible, and then spin up a disposable database container as a part of your CI process. Either that, or just don't test the actual DB implementation, which i've also seen be done to pretty horrendous results.
Edit: Perhaps my past experience makes me have certain biases - i've also seen manually set up DB instances which eventually leads to there being no good way to test N parallel feature branches, each of which needs a different schema, because that also necessitates N DB instances to be present, half or more of which could in theory break. I don't see any good options apart from containers here, especially when you need extensions which these in-memory implementations wouldn't play nicely with.
Sometimes the interface just has too large of a surface area for it to be simulated properly.
For example, suppose that you have an Oracle stored procedure, which, when given about 20 parameters, goes through a decision tree in a PL/SQL block that spans a few thousands of lines and along the way uses both DBMS_OUTPUT.PUT_LINE, as well as DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS, any number of methods from DBMS_PREDICTIVE_ANALYTICS as well as any number of other packages that are included in the full release: https://docs.oracle.com/en/database/oracle/oracle-database/1... which may also include logic that makes network requests, sends e-mails or even triggers other pieces of code to be executed.
All of that would be almost impossible to fake well enough because of the scope of functionality that's used, especially if methods are not only called, but their output is also checked and the execution depends on this. Should systems be simpler and do less? Probably, but that's not the objective reality in many projects out there.
That said, i agree that it's a good thing to have if you treat your DB as a data store and not much more, which in my opinion is a pretty good and stress free way to go about things overall. But somehow that's also like saying "Just use an ORM to have your system be DB agnostic!" which never actually works out in practice because of implementation details that leak through.
I think the importance of horizontal scaling is overhyped. 99% of PostgreSQL applications are at a size where a single machine can easily handle the workload.
To enable horizontal scaling, you need to make so many tradeoffs that I don't think it's worth it for most applications.
Not just application data, There is also a whole lot of analytical data collected at every step of the product usage cycle.
Generally you offload that to purpose built systems that favor those aforementioned tradeoffs or onto services that run them for you, i.e BigQuery, Snowflake, etc.
Your main application database is unlikely to need sharding unless you really do have a phenomenal amount of customers or you need regional sharding to meet legal requirements about data sovereignty for example.
From my experience TimescaleDB is fast and takes a lot of data in. If you're multi tenant, it's usually easy to shard.
And we do dataware housing on BigQuery, no need to have your own machine and manage the database.
Of course there are people who need unlimited horizontal scaling.
I will just leave this link here ....
https://letsencrypt.org/2021/01/21/next-gen-database-servers...
I think those examples are missing the point. One of the examples uses a Redis cluster, which is a sign that the DB alone can't support the workflow.
Another example is using a database for certificate issuance. Which I suspect is using one table or multiple that can be shard and it doesn't suffer from lock contention or joints of multiple tables.
I wouldn't be brave enough to count the number of cases where horizontal scaling is needed but I would say it is definitely not zero, especially for read replicas when high availability is needed
Did you try Stolon?. https://github.com/sorintlab/stolon
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.
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
you probably mean impedance mismatch, right?
https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
Nowadays I would still use it because I assume it is the dumbest database system and that's exactly what I need for my 1-5 user app.
I couldn't find anything decent for postgres, while MariaDB/MySQL have that built-in, with some differences in implementation. Especially for a customer who refuses to pay for his software, because there are some commercial solutions.
I've been using Postgres since 1998, and I tried getting MySQL up first. There was more documentation available for the latter, so it should have been simple. Failed. It just didn't work.
Out of frustration I then tried Postgres, because I just wanted a decent database for my project. It was surprisingly easy, I only had to learn about pg_hba.conf to get to a functional state. Everything else was in place out of the box.
I've been a happy user ever since. MySQL may have had the mindshare (thanks to prevalence of LAMP) but everything outside the magic happy path was confusing and fragile.
Does this include informacion hiding/encapsulation? (to prevent saved objects' internal representation from being exposed).
Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch.
This is important because it is a good practice for client code to make no assumptions about the internal representation, accessing data only via the a public interface.
If it happens to be exposed by the database, the clients can use it in their queries. If the internal representation changed later on, such clients would be broken.
Of course, this can be solved by only allowing data access via, say, well designed restful apis (that don't expose internal details), but this would still provide no guarantees.
How about another reason for impedance mismatch, that of storing objects that belong to a class hierarchy?
If your internal representation and API start to differ then it adds complexity fast. Its far better to have as close to a 1-1 mapping for your backend and frontend data models as possible.
It actually does, those are views and functions.
The real problem with impedance mismatch is that SQL is declarative (you say what you want and database figures out how to get it) when most programming languages are iterative (you say what should be done).
The issue is that you have two very different languages. For one you have powerful IDE with type checking auto completion and refactoring capabilities, the SQL often is sent as a string and don't have these benefits. The various ORM are attempts to use iterative and object oriented language to access relational objects using a declarative language.
I think JetBrains is addressing the problem the right way. They added Data Grip functionality to their IDEs like PyCharm for example. What it does is that if you connect the IDE to a database and let it download the schema you get the same functionality for the data. Basically it will detect SQL statements in the string and offer the same capability for it as you have with the primary language.
At that point the impedance mismatch no longer feels like a mismatch. You basically have two languages, one to obtain data you need and another to process/present it. You can get database to return exact fields you need for your projects and even the object mapping starts feeling unnecessary.
Why data is stored in a relational way? Because that's most optimal way to store the data and the way it is stored allows multiple applications access the same data differently.
For example with NoSQL you need to know how the data will be used so you correctly plan how it will be stored. If application changes you might need to restructure the entire data.
Ultimately the data is the most important thing businesses and it stays, while applications that use it come and go.
This point is very important, and well explained in Stonebraker's paper "What Goes Around Comes Around". What is most interesting is that he is actually talking about half a century old pre-relational IMS IBM databases, but they had exactly the same issue, hence the paper's title. Codd invented the relational model after watching how developers struggled with the very problem you mentioned.
Stonebraker famously quipped that "NoSQL really stands for not-yet-SQL".
He also addresses the impedance matching issue in the "OO databases" section; there is actually a lot more to it, and he gives it all an insider's historical perspective.
Honestly, SQL has this problem too, but it presents itself not in the way you store, but in the way you query. There are simple schemas and complex ones, and irrespective of that there are obvious query sets and unplanned ones (i.e. written at runtime as part of the data analysis process). SQL and its autoplanning is required only for complex+unplanned cases, in my opinion. In all other cases I know my data and I’d better walk through the indexes myself rather than writing 4-story queries to satisfy the planner. At the end of the day, nested loops through the indexes is what RDBMS does. There is no declarative magic at the fetch-and-iterate level.
Iow, it would be nice to have “extql” a direct access to indexes and rows, in sort of a way EXPLAIN works, and skip SQL completely.
function get_items(store_id) {
for (var item in items.id) {
var res = item.{name, code}
var item_id = i.id
res.price = prices.any({item_id, store_id})?.price
if (!res.price) continue
res.props = todict(props.all({item_id}).{name, value})
yield res // or collect for a bigger picture
}
}
The thing is that what worked at specific time can change. For example if you have simple join with two tables, let say A and B. You search by column in target A to get value from column in table B. Now if both tables are large then it makes sense to lookup in A by an index, then use foreign key and index to find the row in table B.
Now if A and B have few elements. Even if there is an index on both of them, it actually is faster just to scan one or both tables.
It might be actually more beneficial to ensure that tables are properly analyzed, have right indices and preferences in the query planner are tuned.
If you need to override query planner, you don't have to make sophisticated queries, you can just use this[1] extension. Though if things aren't working right it is either lack of data, mis-configuration or a bug.
I'm glad there's more.
Edit: actually what you mentioned is slightly different. This is what I'm talking about: https://youtu.be/_FlpiNno088?t=2863
It's about data modeling, one models data using relations, the other models data in a hierarchical way (using maps, arrays, objects, etc...). They are two different ways to structure your data, hence the impedance mismatch.
Because of that, developers started to abstract that with code and objects that were then populated with data.
With IDEs understanding the SQL that's no longer necessary. I can construct a specific SQL to get the exact structure my program needs. Even if it is hierarchical I can use various jsonb aggregation functions. That's a game changer to me.
I see the problem here being that too many manual optimizations need to be done when implementing a schema.
You start with a logical schema (ERD diagram) and then implement it via a physical schema with denormalizations added for efficiency (usually because the relational model has scaling limits with number of joins, or because db migrations are too difficult, or handling unstructured data without introducing a ton of new tables). The db should do the denormalization automatically, allowing the user to interface with their logical schema directly.
Another reason is we can't use SQL in the browser - we have to go through many caching layers and API layers which complicate things.
The Graph model can be made available through extensions. See AGE: https://age.incubator.apache.org/ They plan to support OpenCypher.
The JSONB type allows for No-SQL like development if that's what you really want.
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.
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
At the moment yes.
The final decision has not been made yet, but I think the reality is we're getting tired of evaluating all these distributed databases that claim Postgres compatibility only to find its the usual clickbait marketing speak.
So the most likely outcome is we're going to stick with Postgres and give the emerging distributed tech a couple more years to pull their socks up. We're not going to go round changing all our Postgres code and schemas just to shoehorn into the limitations of a random definition of "postgres 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. Postgres extensions are of course working. Orafce, postgis (with extra work needed for gin indexes, afair...), custom extensions.
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.
For clarification: I'm not associated with Yugabyte in any way other than very happy user and contributor. Yugabyte organizes the Distributed SQL Summit later this month: https://distributedsql.org/. Might be a good opportunity to learn more about it. Second clarification: I'll be speaking at the event.
[1] https://www.cockroachlabs.com/docs/stable/demo-serializable....
[1] https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...
Heh... The Pinto, for safety? https://en.wikipedia.org/wiki/Ford_Pinto#Fuel_system_fires,_...
Thankfully sharding works great for a large number of applications (or in other cases you can accept eventual consistency).
What you really want is databases like CRDB/Yugabyte/TiDB, which are sharding+raft. Tables are sharded into 128MB chunks, and each chunk has their own raft. The database handles transactions, distributed queries, and auto-balancing transparently.
YugabyteDB recently added support for table spaces on steroids where table spaces are allocated to physical nodes in the cluster. This enables geo-replication features where tables or rows can be placed within selected geo locations.
All the data shifting is done transparently by the database.
So if you have 6 server and 2 partition you could have 3 servers for partition number #1 and 3 different servers for partition number #2.
If you want extra performance you could make the server simply store key->value mapping using quorum write like Cassandra is doing but to keep data consistency you still have 2 choice
#1 use Optimistic concurrency (an app performing an update will verify if the data has changed since the app last read that data).
#2 using some kind of Lease, elect one machine to be the only one allowed to write to that partition for some time period.
Option #1 do not give faster transaction throughput but could offer lower tail latency.
Option #2 bring you back to square one of having a leader so you better just use (Paxos/Raf)
You can use CRDT's to give a formally correct semantics to these "inconsistent" scenarios. And they might well be something that's best explored in a not-purely-relational model, since the way they work is pretty unique and hard to square with ordinary relational db's.
You can dance around all you want but you just can't beat laws of nature.
Sure you can always design something that works for your particular case but generic solution is not possible.
https://www.youtube.com/watch?v=hUd_9FENShA
(CAP is a very important result about a very strong assumption of consistency: linearizable events, but for that you can't lose any messages [if I remember it correctly], otherwise the system will become inconsistent)
— https://cloud.google.com/blog/products/databases/inside-clou...
Even better, sometimes you can change which guarantee you need.
We can do better then "pick two"
Given the need for Partitions, you must choose between prioritizing Consistency or Availability.
There's a really interesting suggestion in The Mythical Man Month. He suggests that instead of hiring more programmers to work in parallel, maybe we should scale teams by keeping one person writing all the code but have a whole team supporting them.
I don't know how well that works for programming, but with databases I think its a great idea. CPUs are obnoxiously fast. Its IO and memory which are slow. I could imagine making a single CPU core's job to be simply ordering all incoming writes relative to each other using strict serialization counters (in registers / L1 cache). If the stream of writes coming in (and going out) happened over DPDK or something, you could probably get performance on the order of 10m-100m counter updates per second.
Then a whole cluster of computers sit around that core. On one side you have computers feeding it with writes. (And handling the retry logic if the write was speculatively misordered.) And on the other side you have computers taking the firehose of writes, doing fan-out (kafka style), updating indexes, saving everything durably to disk, and so on.
If that would work, you would get full serializable database ordering with crazy fast speeds.
You would hit a hard limit based on the speed of the fastest CPU you can buy, but I can't think of much software on the planet which needs to handle writes at a rate faster than 100m per second. And doesn't have some natural sharding keys anyway. Facebook's analytics engine and CERN are the only two which come to mind.
Sounds like mob programming!
For instance: consider an application that looks at the current value of a counter and adds 1 if the counter is less than 100. You can execute this on the primary (resource bottleneck because you need to see current state and only the primary has the full picture) or do the operation on a local node and try to submit it to the primary for ordering (coordination required over the network, e.g., to ensure data are current).
There are other approaches but they generally result in the same kind of conflicts and consequent slow-downs. Or they limit the operations you can handle, for example by only allowing conflict-free operations. That's what CRDTs do. [1]
[1] https://hal.inria.fr/hal-00932836/file/CRDTs_SSS-2011.pdf
I think you could add ACID support, while the process doing ordering still not caring about the data. You do something like this:
- Split the keyset into N buckets. Each bucket has an incrementing version number. (The first change is 1, then 2, then 3, and so on). The whole system has a vector clock with a known size (eg [3, 5, 1, 12, etc] with one version per bucket.)
- Each change specifies a validity vector clock - eg "this operation is valid if bucket 3 has version 100 and bucket 20 has version 330". This "validity clock" is configured on a replica, which is actually looking at the data itself. The change also specifies which buckets are updated if the txn is accepted.
- The primary machine only compares bucket IDs. Its job is just to receive validity vector clocks and make the decision of whether the corresponding write is accepted or rejected. If accepted, the set of "write buckets" have their versions incremented.
- If the change is rejected, the secondary waits to get the more recent bucket changes (something caused it to be rejected) and either retries the txn (if the keys actually didn't conflict) or fails the transaction back to the end user application.
So in your example:
> consider an application that looks at the current value of a counter and adds 1 if the counter is less than 100
So the write says "read key X, write key X". Key X is in bucket 12. The replica looks at its known bucket version and says "RW bucket 12 if bucket 12 has version 100". This change is sent to the primary, which compares bucket 12's version. In our case it rejects the txn because another replica had a concurrent write to another key in bucket 12. The replica receives the rejection message, checks if the concurrent change conflicts (it doesn't), then retries saying "RW bucket 12 if bucket 12 has version 101". This time the primary accepts the change, bumps its local counter and announces the change to all replicas (via fan-out).
The primary is just doing compare-and-set on a small known array of integers which fit in L1 cache, so it would be obscenely fast. The trick would be designing the rest of the system to keep replica retries down. And managing to merge the firehose of changes - but because atomicity is guaranteed you could shard pretty easily. And there's lots of ways to improve that anyway - like coalescing txns together on replicas, and so on.
1.) It requires applications to specify the entire transaction in advance. ACID transactions allow you to begin a transaction, poke around, change something, and commit. You can derive the transaction by running it on a replica, then submitting to the primary, in which case this becomes an implementation of optimistic locking including transaction retries.
2.) As you pointed out the trick is to keep the array small. However, this only works if you have few conflicts. Jim Grey, Pat Helland, and friends pointed this out in 1996. [1] That in turn seems to imply a very large number of buckets for any non-trivial system, which seems like a contradiction to the conditions for high performance. In the limit you would have an ID for every key in the DBMS.
3.) Finally, what about failures? You'll still need a distributed log and leader election in case your fast machine dies. This implies coordination, once again slowing things down to the speed of establishing consensus on the network to commit log records.
Incidentally Galera uses a similar algorithm to what you propose. [2] There are definitely applications where this works.
[1] https://dsf.berkeley.edu/cs286/papers/dangers-sigmod1996.pdf
[2] https://galeracluster.com/library/documentation/certificatio...
Calvin is an interesting alternate design that puts "reach global consensus on transaction order" as its first priority, and derives pretty much everything else from that. Don't even need to bottleneck through a single CPU.
http://cs-www.cs.yale.edu/homes/dna/papers/calvin-sigmod12.p...
Like VoltDB, the one huge trade-off is that there's no `BEGIN ... COMMIT` interaction where the client gets to do arbitrary things in the middle. Which would be fine, if programming business logic in the database was saner than with Postgres.
I’m not trying to nitpick but what GP describes aligns to what Mnesia provides.
The distributed part of modern sql is fairly recent.
It's pretty much the opposite of what the parent described IMHO. It's meant more like a configuration store than a proper DB. That's how RabbitMQ uses it for instance, it stores metadata in Mnesia and messages in a separate store, but they're working on replacing Mnesia with their own more modern store based on Raft.
I think this is accurate. There are some references to a masters project to do SQL with mnesia, but afaik, it's not supported or used by anyone.
> can't scale horizontally (each node has a full copy of the DB + writes hit all nodes),
This isn't accurate, each table (or fragment, if you use mnesia_frag) can have a different node list; the schema table does need to be on all the nodes, but hopefully doesn't have a lot of updates (if it does need a lot of updates, probably figure something else out)
> needs external intervention to recover from netsplits,
This is true; but splits and joijs are hookable, so you can do whatever you think is right. I think the OTP/Ericsson team uses mnesia in a pretty limited setting of redundant nodes in the same chasis, so netsplit is pretty catastrophic and they don't handle it. Different teams and tables have different needs, but it would probably be nice to have good options to choose from. There's also no built-in concept of logging changes for a split node to replay later, which makes it harder to do the right thing, even if you only did quorum writes.
> and expects a static cluster.
It's certainly easier to use with a static cluster, especially since schema changes can be slow[1], but you can automate schema changes to match your cluster changes. Depending on how dynamic your cluster is, it might be appropriate to have a static config defined based on 'virtual' node names, so TableX is on db101, but db101 is a name that could be applied to different physical nodes depending on whatever (but hopefully only one node at a time or you'll have a lot of confusion).
[1] The schema changes themselves are generally not actually slow, especially if it's just saying which node gets a copy, ignoring the part where the node actually gets the copy which could take a while for large enough tables or slow enough networks; it's that mnesia won't change the schema until all of the nodes are not currently doing a 'table dump' where up to date tables are written to disk so table change logs on disk can be cleared and those dumps can take a while to complete and any pending schema operations need to wait.
I'd like to hear more about the magic.
The implementation effectively just creates real tables that get cleaned up; they have all the same durability and distributed state despite not being accessible outside of the current session.
Getting something done here turned out to be a big deal in order to get ORM and driver tests to run, which is extremely high value.
A better implementation would just store the data locally and not involve any of the distributed infrastructure. If we did that, then temp tables wouldn't run into the other schema scalability bottlenecks I'm raising above.
A few things I'm missing from it though:
- Advisory Locks (only exist as a shim/no-op today)
- LISTEN/NOTIFY
- CTEs (Common Table Expressions)
- Introspection and management features (for example pg_locks, pg_stat_activity, pg_backend_pid(), pg_terminate_backend())
They are making good progress though, and more recently they've spent effort on making adapters for well, e.g. their ActiveRecord adapter.
I think this is clever, since the deciding factor for many companies will simply be "Can I easily replace this with PostgreSQL in my Rails/Django/etc. project?"
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)
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
I understood the question as:
> What can those newer database systems that are not beholden to RDBMS conventions do for us that Postgres and MySQL (and Oracle and SQL Server and DB2) can not?
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.
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 .
https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...
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)
With Postgres/Oracle MVCC model, readers don't block writers and writers don't block readers.
It's true that an awareness of the data concurrency model, whatever it is, is essential for developers to be able to write transactions that work as they intended.
So much complexity and stress and work at the moment comes from the fear of data loss or corruption. Schema updates, migrations, backups, all the distributed computing stuff where every node has to assume every other node could have mutated the data .... And then there are countless applications full of "history" type tables to reinstate audit trails for the mutable data. It's kind of ridiculous when you think about it.
It all made sense when storage was super expensive but these days all the counter measures we have to implement to deal with mutable state are far more expensive than just using more disk space.
You probably need a drastically different strategy, like moving old records to separate cold storage instead (assuming you might ocassionally want to query it. Otherwise you can just retain your WAL files forever).
FWIW this is available in ClickHouse (which is an analytics database, though)
https://clickhouse.tech/docs/en/engines/table-engines/merget...
[1] https://www.cybertec-postgresql.com/en/postgresql-zheap-curr...
https://www.postgresql.org/docs/current/explicit-locking.htm...
Doesn't make sense to me. Oracle supports MVCC. SQL Server doesn't scale as well as Oracle.
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.
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')
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.
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)
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.
Do you mean tablespaces? https://www.postgresql.org/docs/10/manage-ag-tablespaces.htm...
They'll allow you to have some parts on database on faster devices for instance.
I learned about the possibility a good decade ago when working on Oracle. For high performance use we used to have two areas:
- one small SSD-backed area where new inserts happened (Server class SSD was new and relatively expensive back then.)
- one (or more) areas for permanent storage, based on ordinary disks with good storage/price
Then we had processes that processed data from the SSD Pool and wrote them to long term storage asynchronously.
Edit: I should say I was very happy to find the same feature on Postgres, Oracle is a real hassle in more than one way.
I feel the same. Streaming is so lacking in most dbs today. RethinkDB's `.changes()` was really cool. I wonder if PSQL will eventually do it, or whether a new DB will take over. Everyone went to Mongo then ran back to PSQL, but maybe after lessons-learned there is room for a new db optimized for in-memory usage and with great first-class streaming support.
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.
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.
3) Fine tuned control over query plans if I want it.
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.
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").
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.
Hasn't happened often, but the few occasions have of course been at the worst possible time.
The interesting point is in that regard MySQL is really better than PostgreSQL. MySQL can give you more execution plans it evaluated and tell you why it didn't use them (cost value is higher) and even tell you why it didn't use a specific index it. Combined with the enforcing specific indexes you can sometimes trick it into a more efficient query plan even if it believed to be worse.
But to be honest, PostgreSQL query planner is a lot more intelligent and does stupid things very seldom. And the ability to instruct PostgreSQL collecting more statistics on some attributes (https://www.postgresql.org/docs/13/sql-createstatistics.html) is a huge improvement to getting PostgreSQL make more intelligent plans.
What i really miss in PostgreSQL is:
* Seeing other query plans it discarded as you described to get a feeling how to hint PostgreSQL into a direction if the query planner is doing stupid things
* MySQLs query profile showing the execution time of multiple subtasks of the query: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
* The ability to enforce specific query plans. The PostgreSQL devs stated they are against this but pg_hint_plan is really usefull and i was able to drastically improve some very complex queries.
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.
> 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.
Sure, slots (can be) expensive; GCP doesn't offer slots but all the others do as far as I know (and I see that as a GCP issue, not a Supabase issue); a lot of writes can definitely fill the WAL.
Stepping back, the main comment I can give is that we each approach problems with a slightly different philosophy. Hasura tackles problems using middleware and Supabase tackles problems using the database. Both are fine.
Our approach is important (to us) as we evolve the product. For example, we are adding Row Level Security to our Realtime instance. Because it all sits in the database it's essentially just a Postgres extension, minimizing chatter between a middleware server and the database. Also because it's at the "bottom of the stack", other vendors/integrations can make use of the functionality we provide.
(supabase cofounder)
These implement pub/sub and reactive queries using the MySQL binary log as the event source:
By the way subscriptions seems a good idea in theory, in practice polling is often the best solution. There are not a lot of applications where you need the data that real time (I mean with a latency that is less of a couple of seconds), and for that applications you should build something custom.
Subscriptions to work are based on websockets that have its problems, and also requires a constant connection to the server. The application then needs to handle the incoming data properly, another thing that is not entirely obvious.
Another thing is that most of the times you don't have a 1:1 mapping between the database schema and the API (and if you do, you shouldn't, since a change of the internal database representation will require a change of the API and will break clients). You have in practice an application server in between. Well is that application server that can send subscriptions to clients. And it can do that without subscribing to updates on database tables (assuming that the tables are only changed by the application itself, that seems reasonable). The application when updates the data can publish it to whatever subscription system it wants to notify clients.
Given the spirit of the original post, I think avoiding "build something custom" is the point.
It should be a toggle in the database.
> you don't have a 1:1 mapping between the database schema and the API (and if you do, you shouldn't, since a change of the internal database representation will require a change of the API and will break clients)
I'd argue this is a huge problem. When DB and API get out of sync it creates so much complexity, especially when working in a relational model, and things become impossible to change, or make client caching really difficult.
> The application when updates the data can publish it to whatever subscription system it wants to notify clients.
This is so much complexity though, compared with the experience of Firebase. Good for keeping more devs employed though.
The solution of Google is not less complex. Rather the complexity is hidden, that if all goes well is good, when things start to not work as expected it means weeks spent in debugging and trying to work around the issue. And we are not even talking about the possibility that Google changes API or closes down the service entirely, or it makes it more expensive, the so called vendor lock-in.
At the other side a custom solution takes more time to develop initially, but then is entirely under your control, if something doesn't work you know how to fix it because you built it, you are not bounded to a particular platform, and there is not the possibility that Google decides to change the API in an incompatible way and you have to do extra work just to make things work as they did before, you decide when and if to update the software.
Disclosure (Supabase cofounder)
The dev experience and API were great. It was the "relational mongodb".
Though no one feels daring enough to part from PSQL as a backend these days. With all the cloud deployment options and optimizations in PSQL, it's hard to justify writing your own db engine, but for a great real-time experience, that's what needs to happen.
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/RhodiumToad ; 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/
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)
For example: https://redis.io/commands/BLPOP
If I could do something like:
Select id, msg
From job
Where id > 1224
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)
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.
- 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.
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.
> What do you mean by this?
Consider this app pseudocode:
resultset = db.query("SELECT ... WHERE ...")
for row in resultset
if some_complex_condition(row)
do_something_with(row)
At the same time, consider the case of the table being selected being almost read-only. In this case, and if it fits, it may make sense to move the data directly to the app, keep it in sync when it changes, and have the query processing (the `SELECT ... WHERE ...`) happen directly in the app.
This hints at what I meant by blurring the line: dynamically shifting where computation happens, and where the data lives, depending on the available resources, the workload, and the data.
This is partially doable today e.g. with Hazelcast, in that it allows app instances to be part of the "database" (or, as they call it, the "in-memory grid").
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.
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.
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.
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.
Also… you don’t want to build an API layer because postgrest builds one for you. One that is close to on par with GraphQL (arbitrary joins / filters)
This avoids some big problems with temporal data but I can't see it ever being efficient to do.
Here's some documentation I just found (at 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.
Right, so the way this works is that the database collects instrumentation data and, over time, automatically applies strategies (indexing improvements being one of them) to improve its performance.
Edit: Which I guess is basically what the sibling commenters said.
I believe PostgreSQL and MySQL will support this within a few years.
Also there is at least one plugin for Postgres that I know of, PGVector, but no idea how it performs.
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.
- 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.
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.
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/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).
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.
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)
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
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.)
https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-e...
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?
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.
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.
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.
Unfortunately, I saw too many teams that end up implementing an informally-specified, bug-ridden, slow implementation of half of PostgreSQL.
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.
Calvin (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.
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).
* 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.)
Edit - reread the question and it mentions OLTP workloads. Snowflake and Redshift are specialized analytics DBs so they don’t meet that criteria.
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.
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-intensiv...
I sorely miss them on PG.
MariaDB has them https://mariadb.com/kb/en/bitemporal-tables/
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
Linear relationships must be defined. Your database doesn't do much for you. You must define every relationship between tables.
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.
I think it's a question of having the right tooling.
It would be cool if you could dump an unstructured blob of data in a psql table. And then later you can add a schema to this defining relationships. So like `post.comments` is an array of `Comment`. And then you just run a command that runs a migration that normalizes the data into a `comment` table. And then it would map `post.comments` to a join. Although psql's jsonb support and indexing is pretty good.
The difficulty though is that if you change your mind it becomes much harder to change because now you have multiple tables and relationships. So what would be nice is if you can go back from the relational model to the unstructured model with ease.
I think what is needed is a visual tool to design your db schema and migrations that just works, and is also aware of unstructured data and that it can have a json schema.
Ideally we want to be designing a logical schema (and any unstructured data would implicitly be given a logical schema too), and the physical schema is automatically created.
Also, you don't actually need to define relationships. Rails for eons never did this at the database level.
The argument that "it will slow us down" seems so vague, I can't really see how defining the schemas and relationships slow you down. You need to know what data you are dealing with anyway even when prototyping, how else would you create views for the users? Tables? Are profile fields for users also open ended? I can add any number of fields with random values?
And "Your database doesn't do much for you?" So what exactly does NoSQL database do for me that PostgreSQL (or MySQL) won't? I'm really curious, I'm not attacking anyone, I just want to know, maybe I've been using the wrong database my whole life.
But I think that database as anything else in your project should be selected by best fit not by mere "it's a cool buzzword, lets use that". Most of the projects out there have relational data and should be using relational database because it fits the core data model.
I don't have anything against NoSQL databases, they obviously have their usage and place but I have against choosing them because they are "cool" and "fast to prototype on" if the project itself has 100% relational data.
Also the tools fastest to prototype with are the ones you know best, it doesn't mean that tool is best fit for the project.
Exactly. At some point, somewhere, you need to understand your data and how different attributes or objects relate to one another. You can do this in a variety of ways, but if you don't understand this you don't understand what you are building.
The relational model always requires a judgement call about how normalized to go. If I have a deep nested json object of data, you could argue to define all the relations and normalize. But then to query the data you now have an 6-way join and the query planner starts to struggle a bit. And then if you decide to change that structure, you now have a very tricky migration script to run.
Whereas if you just kept it as a json object, you have avoided a lot of unnecessary pain, and you can just write some quick re-mapping code in the application layer to handle the older version of data, and you can actually start storing data in the new schema immediately. Then you can scan over the collection and modify the existing data of the old schema, and remove your re-mapping layer.
So yes, your data has been relational the whole time, but the query complexity and migration complexity increased dramatically. It became very difficult to change the model.
The relational model is essentially a constraint on how you need to structure your data in order to allow relational algebra to be used to help optimize query plans. When you release these constraints, you find things like Datalog that provide purer ways to represent your data and relationships, in the sense that they can map closer to the real-world - but then this comes at the cost of automated query optimization.
So the fact that relational data models are usually less representative of the real-world because of adherence to the relational model, usually denormalized to optimize the underlying database engine, and difficult to migrate, create complexity and slow down shipping some features (although they can also speed up many others greatly - like analytics).
That being said, SQL dbs are probably the best dbs we have today for solving business problems, and I think the migration and modeling problems could be solved with better tooling.
- 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 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.
Everyone wants this and most production environments need it.
Then I would argue that most production environments don't need it.
Unfortunately I do not have numbers but my rough estimation is that average application is maybe 10k users. Where my mobile phone would be overkill to host such application.
My estimation is based on that most of the web-apps are not even close to Alexa top 500 - where top 50 is insane and they need scalability and availability - where I expect amount of people visiting sites is governed by "power law" so those top 50 get 80% of traffic and the rest gets 20%. Don't even start on all those intranet applications that are having maybe 100 to 500 users at all and I would expect there are much more of that kind of applications in the world than there is google search engines :)
I live in the enterprise sector where you can get many rdbms living alonside (+ cloud) and I know for a fact "scalability" is close to zero in the list of actual needs.
I don't mean to say scalability is not importan, but is a NICHE: You only need when you truly start to get to certain ceilings.
My customers (a few big companies in my country) consider 10-30 GB rdbms "big". And "slow". Is far far more problems is terrible schema designs (like, actually do everything in their power to make rdbms look bad, and not be that successfully).
---
So, my point is that exist far more quality of life to be done at the core/fundamentals before worry much about what to do when I have 100 TB databases across continents...
- Expose lower-level access to MVCC etc. Something finer grained than global transactions.
- Similarly, make indexes more first-class and visible.
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.
[1]: http://static.wiki/
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.
Yeah, well, no big wonder: Many database systems were created long before the Web.
I will say my criticism doesn't apply to Firebase; they definitely do a lot of the things I mentioned. My only issue with Firebase is that it's very much intended to be used directly with a realtime frontend.
Bah, I can't recall them all. Just go to https://en.wikipedia.org/wiki/Comparison_of_relational_datab... and click the table header to sort by "First public release date".
Persistent queries, where any change to the answer is propagated, like a subscription to a feed.
Of course, it's best to do both, to avoid orphan records
ON DELETE CASCADE ON UPDATE CASCADE
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
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.