311–320 of 326 posts
Earlier quoted context omitted.
> 3. Tiered storage. 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.
Very cool - I had no idea this existed. I wonder if this could be useful for using ramdisks when working with temp tables.
Earlier quoted context omitted.
> Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. 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 differ…
Your view of what impedance mismatch is doesn't sound accurate. It's not about declarative or imperative or syntax or strings, etc... 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.
Earlier quoted context omitted.
> Like real distribution, when you add more servers writes distribute as well? 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 id…
What you suggest would work if you just want to order writes. However, it won't support an ACID transaction model, which a lot of applications expect, or even simpler models where you can read values before you write. 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 t…
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.
One thing PostgreSQL would likely not be able to adapt to, at least without significant effort, is dropping MVCC in favor of more traditional locking protocols. While MVCC is fashionable nowadays, and more or less every platform offers it at least as an option, my experience, and also opinions I have heard from people using SQL Server and similar platforms professionally, is that for true OLTP at least, good ol’ lock…
The downside of "good ol' locking" is that you can end up with more fights and possibly deadlocks over who gets access and who has to wait. 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.
Oh I've been thinking a lot about this! It might not be the answer you're looking for since they're all very UX-related and not particularly database-specific, however I have a few ideas. Sometimes I feel like databases were created by people who never built a website before. Most websites are pretty similar, and databases historically have never felt (to me at least) "modern". I always feel like I'm fighting against…
Yeah, well, no big wonder: Many database systems were created long before the Web.
One thing PostgreSQL would likely not be able to adapt to, at least without significant effort, is dropping MVCC in favor of more traditional locking protocols. While MVCC is fashionable nowadays, and more or less every platform offers it at least as an option, my experience, and also opinions I have heard from people using SQL Server and similar platforms professionally, is that for true OLTP at least, good ol’ lock…
I sort of want the opposite. Except for extremely high velocity mutable data, why do we ever drop an old version of any record? I want the whole database to look more like git commits - completely immutable, versionable, every change attributable to a specific commit, connection, client, user. So much complexity and stress and work at the moment comes from the fear of data loss or corruption. Schema updates, migratio…
Oh I've been thinking a lot about this! It might not be the answer you're looking for since they're all very UX-related and not particularly database-specific, however I have a few ideas. Sometimes I feel like databases were created by people who never built a website before. Most websites are pretty similar, and databases historically have never felt (to me at least) "modern". I always feel like I'm fighting against…
> Sometimes I feel like databases were created by people who never built a website before. 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.
1. High performance read/write of Scylla/Cassandra with high availability[1]. It has some limitations for OLTP workloads and require careful planning. Postgres without Citus is not really HA and even then companies would often invent custom sharding/cluster management system. 2. Powerful graph query language like Cypher. It might not perform well in real life, but my personal experience left me amazed[2]. There is a…
Postgres has many HA solutions, and it's getting better all the time. Postgres has good performance, benchmarks need to show the various trade-offs systems make so that informed decisions can be made. Feel free to post a link. 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…
Earlier quoted context omitted.
Why can't you do that with triggers? 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 requi…
> and for that applications you should build something custom. 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…
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.
Earlier quoted context omitted.
> Sometimes I feel like databases were created by people who never built a website before. Yeah, well, no big wonder: Many database systems were created long before the Web.
I mean, Mongo came out in 2009 and Amazon predates both MySQL and Postgres. 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".