PostgreSQL 11 and Just in Time Compilation of Queries
citusdata.com
citusdata.com
[0] https://h50146.www5.hpe.com/products/software/oe/linux/mains...
PostgreSQL 10 New Features With Examples https://h50146.www5.hpe.com/products/software/oe/linux/mains...
Personally, I already view PostgreSQL comparable (for many use cases) to paid options[1].
Interesting, for queries such as:
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM sort1 ORDER BY 1) AS a LIMIT 5 ;
[1] https://austingwalters.com/fast-full-text-search-in-postgres...
[2] https://h50146.www5.hpe.com/products/software/oe/linux/mains...
- Not distributable
- Poor resiliency
- Hard to upgrade
- Schemas remains at postgres core
Every solution to address any of these points in PG are hacks (bucardo, londist, slony, pgbouncer, things relying on triggers or proxies). Quite honestly, PG contributors are doing an impressive job. PG is arguably the best relational database in the market as of today and still improving very fast. However it's also one of the most (if not the most) bloated. And despite all these features nobody needs and after all these years, it's still lacking some very basic yet essential items anyone would ask for in any modern application. It feels like things haven't changed that much since the 90's. DBAs have to hack around things and root issues never gets addressed. Implementing JIT optimizations looks like fun. It will undoubtedly improve some queries by 2x, 4x or even 10x, but it won't be a game changer anyways. It'll just increase the postgres bloat and the overall complexity of the system. Meanwhile, we'll still be lacking essential things that would make PG suitable for pretty much any use-case.
It may turn out that your essential, and a lot of other users essential aren't exactly the same.
I worked on the JIT stuff because there was quite some concern about query speed (yes, from actual users). And after some other micro-optimizations that was the right point to attack the performance.
If you have strong feelings what you want to be worked on, you'd be more than welcome to help. Or alternatively some of the dev companies around PG certainly would appreciate business.
- "Poor resiliency" Do you mean something by this beyond as an aspect of distributability?
- "Hard to upgrade" The story here has been much improved, both with pg_upgrade and though features such as logical replication.
- "Schemas remains at postgres core" Would you elaborate what you mean by this?
I would also ask you to elaborate what you mean by "However it's also one of the most (if not the most) bloated." In terms of binary size? Feature set? Actual on-disk data representation? If so, what are you comparing it to?
Similarly, "It feels like things haven't changed that much since the 90's.": Having used Postgres over the years, I can certainly say that things have become much easier. What in particular are you thinking of?
I also struggle to square what appears to be complaints of capability while also expressing frustration with "postgres bloat and overall complexity of the system." As with all things, there are tradeoffs and resource limitations. What would you in particular are you looking for? And given the nature of Postgres as an open source project, what are you doing to ensure that those features you desire are being worked on? There are plenty of developers and organizations that are willing to work on bespoke features given the appropriate support.
Jepsen: https://jepsen.io
> This is something that's actively being worked on. It's also useful to look at the systems that are more easily distributable, and compare other features they posses with Postgres. Aphyr has provided a wonderful service with Jepsen showing just how difficult correctness in general (with and without the added complexity of distributability) actually is.
Thanks. I'll definitely have a look into this. I'm definitely not saying that everything would be easy and ideal to implement in such a robust and mature software (I am even sure it would be a nightmare for the developers to develop and stabilize and that for this reason it's unlikely to ever happen). Other databases provides this out of the box, postgres just don't, and that's a good reason "why would anyone use anything else". Still, not a requirement for everybody, but more frequent than JIT IMO.
> Do you mean something by this beyond as an aspect of distributability?
I mean that physical replication is great, but you always get into troubles when it comes to failover. Pretty much any modern DB system can be put in front of a basic HTTP LB, and replicate data on n nodes. It's indeed tightly coupled with distribution, but I meant distribution in term of performance and sharding (horizontal scalability) and resiliency in term of reliability. Relying on DNS for failover induces a downtime, relying on a proxy induces a hack. I'm wondering why it would be so hard to fix this in postgres though.
> The story here has been much improved, both with pg_upgrade and though features such as logical replication.
This is definitely true. While I think logical replication could/should go even further to really fix the upgrade issue, PG 10 is a huge move in the right direction. Thanks for pointing that out.
> "Schemas remains at postgres core" Would you elaborate what you mean by this?
Maybe it's more a general feeling than something really concrete, but for instance, would you create a table with only one JSONB column. It would feel weird, like abusing/hacking around the schema. From the user perspective, it's impractical, not natural, and it doesn't look like a document store at all (as we see in nosql DBs). In this respect, I don't think I will ever use postgres as a document-only database because I feel like it's just not how postgres is built (unless I'm missing some great feature?). That's a subject were document-only database may have the point.
> In terms of binary size? Feature set? Actual on-disk data representation? If so, what are you comparing it to?
I'm only speaking in term of feature set. A lot of documentation comes with them. A lot of side-effects. A lot of wrong expectations from the user. A lot of complexity. A lot of maintenance burden which makes postgres more complex to evolve on huge topics.
Also, this is a common problem across the most used relational DBMS (MySQL, Oracle, SQL Server and Postgres). To give another perspective, ElasticSearch dropped a massive amount of features in version 6 just so that they can focus on the core things. I personally think it was a good move.
> Having used Postgres over the years, I can certainly say that things have become much easier. What in particular are you thinking of?
I have also used PG for years and while I reckon you are completely right (a lot of improvements have been made in a tremendous amount of places), I was pointing out that the core topic I listed were still such a pain to handle, while so important since day 1.
> I also struggle to square what appears to be complaints of capability while also expressing frustration with "postgres bloat and overall complexity of the system." As with all things, there are tradeoffs and resource limitations. What would you in particular are you looking for?
Ok, let me fix this: I'm not complaining at all, really really not. PG guys are doing a fantastic job, PG is a great software, no doubt about it. I would even go further: in my opinion postgres is the currently the best mature RDBMS in the place. I am just saying that postgres is not and probably never will be the answer to all the DB use-cases in the world. More than that: postgres is even failing on very basic stuff needed in almost every modern app (mostly because it was started at a time were HA was not really a concern). Put another way: some modern DBs are solving some modern problems better than postgres and might disqualify postgres for some of these use-cases. I did not meant anything else, believe me, and I apologize sincerely to all the developers that have worked on the project if my comment was taken like that.
No system is the absolute best for every use case. No one is saying it is. But please be fair and lay all the cards on the table so we can all see and judge for ourselves.
† As an aside, this use of "modern database" sticks in my craw a bit. It's very imprecise. It seems you might be using it to mean anything non-relational or perhaps non-SQL. The so-called NoSQL database systems are often quite different even amongst themselves: lumping them together does a disservice to the individual implementations. Then again, perhaps I'm misinterpreting what you mean.
I also know that distributed systems don't come without their own issues and complexity (CAP mostly, but also distributed systems = more complex = more bugs, and also younger = less mature = more bugs, and configuration issues, sharding issues). Some databases are very clear about those bugs and limitations (https://www.elastic.co/guide/en/elasticsearch/resiliency/cur...) some aren't (I don't think MongoDB documents them). Behind "modern databases", I am thinking of ElasticSearch, CouchDB (they solved a lot of issues regarding scalability recently by merging BigCouch in v2), MongoDB (arguably one of the worst way to address all the mentioned problems, but whatever), DynamoDB, for the databases I've been using or I'm currently using in production. I've also played around with AWS Aurora (yes, AWS is forking MySQL and Postgres to solve those issues at root which is a good proof that there is actually a demand), and also more specific databases like InfluxDB or key-value stores like Consul's. They all have their own solutions and tradeoffs. But I'm not sure mentioning them is very relevant for the argumentation.
Sure. To make it more practical, I'd use a primary-key column too (like most document-only databases do).
We actually do use a (INTEGER PK, JSONB) table in Pg, for a variable-depth permission-model store. The permissions (per PK) are encoded into a JSON document and queried using a 'get json object at path' lookup. Turns out, the way json & jsonb operators and functions are designed in Pg, it feels exactly like a document store, just available in your SQL (which is a plus, in my book).
I think the larger theme here is your feeling that this is abusing or hacking around "the schema", but really, what is "the schema"? I've seen people use MySQL as just a store, using plain, NULLABLE VARCHAR and INT columns, and enforce a "schema" at the application layer, serializing and de-serializing their data from the VARCHAR columns. I've seen projects reinvent MVCC using an INT 'version' column in their tables.
Databases can only allow defining and enforcing a schema at the database layer, they can't force it. Some databases allow a lot of control and many ways to define a schema, some allow little, but there's no requirement that one MUST use all the ways when available. In fact, even with rich databases like Postgres, sometimes even the db-level enforceable schema is not sufficient and one has to make do with enforcing some of it at application-level, and I'm talking about just the traditional data types here.
Using JSON columns and operators and functions is — at the end of the day — using just another data type. Given Postgres is an object-relational database (it IS, after all, post-INGRES), it's only natural to use Postgres with data types like JSON (and arrays, and ranges, and GIS).
Partial jsonb indexing (\w full-text), transactional fast joins on jsonb fields, and higher Jepsen rating than most noSQL DBs is pretty killer for me.
Please do speak for yourself.
What?
PostgreSQL still is lacking with its horizontal scalability story and there is no reason to choose it over something like Solr/ElasticSearch right now largely because of this.
If you had to choose between PostgreSQL or ElasticSearch then you have no idea what you're doing.
It might be possible to get it to work without PG, but I imagine it would require a huge engineering effort (akin to an entire product on its own, which, IIRC, is what CrateDB is).
(Although I believe I understand your point, I fear that your tone and ad-hominem language detracts from it).
Also PostgreSQL is not hard to update, the only issue with PostgreSQL and upgrades is that it is hard to upgrade with zero downtime (pg_upgrade is very fast but still requires a small bit of downtime).
I can agree with the complaint about distribution, but that is a common issue for most databases not built from the start around distribution and nothing I would call "fundamentals".
We had to go to a slony hack (and put auto increment columns on a lot of tables temporarily) because of that.
So he's totally right, the upgrade path is not easy.
threeseed has summed my thoughts up. Solar/ES are solving issues that postgres just don't address at all at the moment, and it's definitely not only about text search (which is also a point). Saying that postgres should be/can be used for any use-case apart from TS is an absolute nonsense and that's what made me react in the first place. Moreover, the feeling that the community don't largely agree just on the fact that postgres doesn't meet these four points is indeed a bit appalling. OK, saying that postgres should try to solve them is another debate, but let's just agree on the fact that if you absolutely need a very resilient OR distributed OR document-based OR easy-to-upgrade DBMS (although for this last point, I have to agree that things have changed recently, partly thanks to the Uber gate I think), postgresql is probably not a good choice. There's definitely a HUGE trade-off and we should agree on this. Postgres don't come even close to Solar/ES in term of horizontal scalability. I'd really like it could, but as of today it can't.
Could you expand on what you mean here? By 'users, permissions', I assume you meant management of users and access control to records at the database layer. If so, I should tell you I use database-enforced roles to manage access to various areas of the schema I manage, and RLS to protect access to records. If not, I'd very much like to know what exactly you meant.
Could you expand a bit on what exactly you mean by "indices creations/deletion"?
> things have changed recently, partly thanks to the Uber gate I think
Logical replication in core was in the works long before Uber's post, and already available as a plugin for at least two releases at the time. I remember, because we ourselves used it (for syncing data between environments, not for upgrades) in 9.6 (the then latest release).
Even that standard results in people giving up on PG too early. It's possible to get it to continue working out, but it can require effort beyond a naive implementation and often more hardware knowledge than is common any more.
The other options can make naive implementations much easier, at least initially, and on almost any hardware. Of course, that usually ends up costing far more, even at modest scale.
> PostgreSQL still is lacking with its horizontal scalability story
To the extent this is true, it may still be irrelevant for many environments. The ability to increase performance by just adding more servers (what I believe is usually meant by "horizonatal scalability") is not, despite its popularity, a desirable end in and of itself.
It's only desirable as a means of scalability, if scaling a "single" server (a.k.a. vertical) would reach the limit of available hardware, or if even approaching that limit becomes prohibitively expensive.
My impression from talking to managers (including startup founders) is that they often erroneously believe they fall into the latter category. Specifically, they believe that the overhead inherent in distributed systems is, essentially, negligible, so that they're avoiding the 2-4x price premium of a high-end single system. The reality is not so negligible, especially considering inefficiencies unique to a particular distributed database, regardless of if it's inescapable due to its nature.
The former can, of course, be a real concern, especially for VC-funded startups, for whom a lack of fast enough growth is its own failure. However, even in that situation, there's reason for skepticism, since most of the original "web scale" horror stories tended to involve MySQL (which had, and maybe still has, reduced capabilities for performance improvements through hardware) and database servers that hadn't even maxed out their I/O capabilities (not trivial to do naively pre-SSD).
> there is no reason to choose it over something like Solr/ElasticSearch right now largely because of this.
Even the OC points recognizes [1] that these distributed systems aren't without their drawbacks. Those drawbacks are additional reasons to choose Postgres over them.
What would be the difficulties to have these in PG, outside of "getting developer interested in implementing it" (i.e is there some current architecture decision in PG that prevent it, like it's the case for "why vanilla PG is not suitable as a column store")
It is an open source project at the end of the day, which means the features that get added are those that are wanted by either the developers or the sponsors, not the the general user base
BTW - elastic.co is just going for an IPO. There's a lot of money in search!
Parallelism can help however, depending on what your bottleneck is (if the query produces a bitmap indexscan the heap access part can be parallelized).
Author is currently stuck and I hope someone with the right knowledge can help him out so he can continue the work.
This makes me very sad.
There's no hinting because the planner is so smart, and yet there is mandatory hinting on a legitimately useful language construct that in some cases is the only way to do something (e.g. recursive queries).
Just a very weird philosophy.