Postgresql does not have real clustered index (one that is automatically maintained) that Ms SQL has (and other DBs). Such feature is important for a lot of popular workloads.
Ask HN: What could a modern database do that PostgreSQL and MySQL can't
41–50 of 326 posts
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#42CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance t…
> It has broad PGSQL language compatibility Depends how you define broad. :) Many key features are missing, including but not limited to: - UDFs and sprocs. - Useful datatypes such as TSTZRANGE - More limited constraints I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.
Are you still using postgres? Thinking about a similar transition, would really appreciate any shared learnings :)
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#43CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance t…
> It has broad PGSQL language compatibility Depends how you define broad. :) Many key features are missing, including but not limited to: - UDFs and sprocs. - Useful datatypes such as TSTZRANGE - More limited constraints I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.
May I suggest looking at YugabyteDB, which is a distributed SQL database built on actual PostgreSQL 11.2 engine? It's way ahead of CockroachDB in terms of feature support.
YugabyteDB has UDFs, stored procedures, distributed transactions, the range types are working from what I can tell, at least the example from here: https://wiki.postgresql.org/wiki/Extract_days_from_range_typ... works right out of the box, just copy paste. 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.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#44Does your data model change often? PostgreSQL and MySQL well be very difficult to make fundamental changes. If you need to make changes to your data model, the structure of relational databases will make it difficult for you to move fast. Linear relationships must be defined. Your database doesn't do much for you. You must define every relationship between tables.
As some of the commenters here have already voiced, I also struggle a bit to understand the issue with using relational databases if the data is actually relational. Which, for probably like 95% projects it is.
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.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#45Subscriptions. Databases like Firebase will automatically push changes to query results down to clients. You can add this to Postgres with tools like Hasura, but it's poll based and not very efficient. It's a super-useful feature for keeping UIs in sync with database state.
Can't you add something like this to MySQL using triggers or some similar system?
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#46CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance t…
It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?
There are two ways I see databases doing paxos.
The basic way, like some databases like Percona, basically is a single raft across the whole database. It can help with high availability, but the database scale is still kind of constrained to the capability of a single writer.
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.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#47One thing PostgreSQL would likely not be able to adapt to, at least without significant effort, is dropping MVCC in favor of more traditional locking protocols.
While MVCC is fashionable nowadays, and more or less every platform offers it at least as an option, my experience, and also opinions I have heard from people using SQL Server and similar platforms professionally, is that for true OLTP at least, good ol’ locking-based protocols in practice outperform MVCC-based protocols (when transactions are well programmed).
The “inconvenient truth” [0] that maintaining multiple versions of records badly affects performance might in the future make MVCC less appealing. There’s ongoing research, such as [0], to improve things, but it’s not clear to me at this point that MVCC is a winning idea.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#48I realise I'm straying a bit from core OLTP stuff but also I think removing the historical need for a separate OLAP database is something modern systems should address. Off the top of my head: 1) Incremental materialized view maintenance, à la Materialize (bonus points for supporting even gnarly bits of SQL like window functions). 2) Really ergonomic and scalable pub/sub of some sort, à la RethinkDB. 3) Fine tuned co…
Another interesting approach is HyPer[1]. HyPer uses many new techniques to combine OLTP and OLAP in one database. For example, to achieve good OLAP performance, a columnar storage layout is used, but the columns are chunked for locality to achieve good OLTP performance at the same time. OLTP queries are executed in memory, but cold data that is not used for OLTP is automatically compressed and moved to secondary storage for OLAP.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#49Earlier quoted context omitted.
It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?
There are two ways I see databases doing paxos. The basic way, like some databases like Percona, basically is a single raft across the whole database. It can help with high availability, but the database scale is still kind of constrained to the capability of a single writer. What you really want is databases like CRDB/Yugabyte/TiDB, which are sharding+raft. Tables are sharded into 128MB chunks, and each chunk has th…
Definitely, YugabyteDB falls into the second category. Tables are split into so called tablets. This splitting can be controlled by choosing the hashing algorithm for the primary key (or transparent row key hash, if no primary key for a table exists). Each tablet is replicated and has its own raft group. Different tables can have different replication factors, the number of tablets to split the table into can be selected and modified.
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.
Re: Ask HN: What could a modern database do that PostgreSQL and MySQL can't
#50I realise I'm straying a bit from core OLTP stuff but also I think removing the historical need for a separate OLAP database is something modern systems should address. Off the top of my head: 1) Incremental materialized view maintenance, à la Materialize (bonus points for supporting even gnarly bits of SQL like window functions). 2) Really ergonomic and scalable pub/sub of some sort, à la RethinkDB. 3) Fine tuned co…
3) Fine tuned control over query plans if I want it. I feel like when most people say this, what they really want is a better query planner. The optimum query plan depends on a lot of dynamically changing factors: system load, free RAM, and of course the data in the tables themselves. Any hints we give the query planner are going to help at certain times and be somewhere between "suboptimial" and "disasterous" at mos…
It's true a better query planner is what I want, i.e. one that always does what I want it to do. That not being the case, I will settle for being in control, footguns and all.
I agree the config is hard to wrangle though, you effectively find yourself doing grid search with a bunch of common workloads, it does feel like something the machine should be doing for me (the most common config variable we end up tweaking is "how much money we give Amazon").