▲ 416 points
back
121 comments
Yeah, but...
- Row-level anything introduces write alignment and fsync alignment problems; pages are easier to align than arbitrary-sized rows
- PostgreSQL is very conservative (maybe extremely) conservative about data safety (mostly achieved via fsync-ing at the right times), and that propagates through the IO stack, including SSD firmware, to cause slowdowns
- MVCC is very nice for concurrent access - the Oriole doc doesn't say with what concurrency are the graphs achieved
- The title of the Oriole doc and its intro text center about solving VACUUM, which is of course a good goal, but I don't think they show that the "square wave" graphs they achieve for PostgreSQL are really in majority caused by VACUUM. Other benchmarks, like Percona's (https://www.percona.com/blog/evaluating-checkpointing-in-pos...) don't yield this very distinctive square wave pattern.
I'm sure the authors are aware of these issues, so maybe they will write an overview of how they approached them.
> - Row-level anything introduces write alignment and fsync alignment problems; pages are easier to align than arbitrary-sized rows
OrioleDB uses row-level WAL, but still uses pages. The row-level WAL becomes possible thanks to copy-on-write checkpoints, providing structurally consistent images of B-tree. Check the architecture docs for details. https://github.com/orioledb/orioledb/blob/main/doc/arch.md
> - PostgreSQL is very conservative (maybe extremely) conservative about data safety (mostly achieved via fsync-ing at the right times), and that propagates through the IO stack, including SSD firmware, to cause slowdowns
This is why our first goal is to become pure extension. Becoming part of PostgreSQL would require test of time.
> - MVCC is very nice for concurrent access - the Oriole doc doesn't say with what concurrency are the graphs achieved
Good catch. I've added information about VM type and concurrency to the blog post.
> - The title of the Oriole doc and its intro text center about solving VACUUM, which is of course a good goal, but I don't think they show that the "square wave" graphs they achieve for PostgreSQL are really in majority caused by VACUUM. Other benchmarks, like Percona's (https://www.percona.com/blog/evaluating-checkpointing-in-pos...) don't yield this very distinctive square wave pattern.
Yes, it's true. The square patters is because of checkpointing. The reason of improvements here is actually not VACUUM, but modification of relevant indexes only (and row-level WAL, which decreases overall IO).
Good job! That's cool.
How do you plan to make your new project keep up to date with the release cadence of the parent project?
...because otherwise, I can't see how this is a good idea.
Look, I have the same reaction whenever someone does this.
If someone goes and forks rust and creates a new programming language call dust that solves I dunno, the fundamental async compatibility story, or adds (somehow) a zero cost native GC type back into the language, I'd say the same thing.
You've taken a big open source project, forked it and laid some significant changes on it, which you don't believe this be accepted upstream.
Ok...is this a toy that you made for fun?
...or a serious project you expect to maintain?
If the answer is 'serious project', please make explicit your plans to avoid becoming abandonware in the future, your plans to fold future release from (original project) into yours, or your plans to diverge henceforth into an entirely new project.
To be fair, I get it, this is an extension that seems like it could... probably... receive changes that are made upstream in postgres; but, if it was that easy, it belongs as part of the postgres projecct; so, I guess, it's not that easy.
So, serious? Or just for fun?
The author wrote this, answering the question in a reply on the post:
> Yes, sure! But that's the long way to go. Right now OrioleDB is an extension, which comes with PostgreSQL core patch. The mid-term goal for OrioleDB is to become a pure extension. The long-term goal is to make OrioleDB part of PostgreSQL core.
are you considering using this in production somewhere in the next few days? your reply comes off as absurdly aggressive, especially when you mention no intention of supporting the project monetarily. and that's on top of this question being addressed already, as other commenters pointed out.
maybe don't come in so hot next time.
As a data point, that's already been answered: :)
Reasonable points. As an onlooker who has run into VACUUM in years past, I have wondered: is this a fundamental necessity, or could it possibly be fixed? Seeing an example of it being fixed is certainly helpful. If I work at a company with the resources to maintain a Postgres fork, great! If not, we can evaluate whether the challenges of using this fork are worth the performance benefit.
I with people would stop with the "Uber migrated from Postgres to MySQL" thing. Uber migrated from Postgres used as relational database to something that is basically their own non-relational database using MySQL as distributed key-value store. It is not really situation applicable to most users of Postgres.
Anyway, this design of MVCC which moves older data into undo logs / segments is used by Oracle DB, so it definitely works. The common challenge with it is that reading older versions of data is slower, because you have to look it up in a log, and sometimes the data is removed from the log before your transactions finishes, getting the dreaded "Snapshot Too Old" error.
E: I don't see in the article when rows get evicted from the undo logs. If when they are no longer needed, I'm not sure where the improvement comes from because it should be similar amount of bookkeeping? If it's a circular buffer that can ran out of space like Oracle does it that would mean under high write load long-running transactions starts to fail which is pretty unpleasant.
> E: I don't see in the article when rows get evicted from the undo logs.
The undo records are truncated once they aren't needed for any transaction.
> If when they are no longer needed, I'm not sure where the improvement comes from because it should be similar amount of bookkeeping?
It depends on what exactly is "bookkeeping". If we consider amount of work, then improvement comes because old undo records can be just bulk deleted very cheap (corresponding files get unliked). No vacuum scan is needed. If we consider amount of space occupied, then indeed the same amount of versions take the same amount of space. But saving old versions of rows in the separate storage can save their primary storage from long-term degradation. Also, note that OrioleDB implements automatic merging of sparse pages.
> If it's a circular buffer that can ran out of space like Oracle does it that would mean under high write load long-running transactions starts to fail which is pretty unpleasant.
OrioleDB implements in-memory circular buffer for undo logs. Once circular buffer can't handle all the undo records, least recent records are evicted to the storage. Currently, we don't place limitation on the site of undo logs. Undo records are kept while any transaction can need them. So, no "Snapshot Too Old" errors. However, we can consider implementing this Oracle-like error as an option, which allows to limit the undo size.
Also, please, check the architecture documentation of github (if didn't already). https://github.com/orioledb/orioledb/blob/main/doc/arch.md
SQL Server avoids vacuum as well, it might be this way, I can't recall.
And of course MySQL avoids vacuum by giving a giant middle to concurrency considerations.
I'm excited about the title, but I have to say that my initial impression has left me frustrated. The main README on GitHub[1] smells of corporate-speak. So far I've learned that:
- OrioleDB is a new storage engine for PostgreSQL
- PostgreSQL is most-loved (whatever that means)
- OrioleDB is an extension that builds on.. other extensions?
- OrioleDB opens the door to the cloud!
In the wake of crypto and other Web 3.0 grift, this is not the tact that I'd take to release something that extends and improves on something as important as PostgreSQL.
> OrioleDB is an extension that builds on.. other extensions
I assume you are referring to this part:
> OrioleDB consists of an extension, building on the innovative table access method framework and other standard Postgres extension interfaces.
I don't know how they could be more clear? Table access methods were introduced in PostgreSQL to support alternative storage methods (like zheap, which tries to do something very similar, or possibly columnar data stores).
Mentioning this fact is important, because there are a bunch of forks of PostgreSQL with alternative data storage systems; this is designed to work as an extension for an unforked PostgreSQL. (It doesn't yet)
The Readme seems very clear if you are familiar with PostgreSQL.
Oriole's design seems to require transaction-aware indexes with point entry removal, which has its own cost.
E.g. a GiST equivalent (for e.g. spatial indexes) would be a hassle to maintain due to its nature of having no precise knowledge about the location of each index tuple, GIN (e.g. FTS indexing) could be extremely bulky due to a lack of compressibility in posting trees, and I can't imagine how they'd implement an equivalent to BRIN (which allows for quickly eliminating huge portions of a physical table from a query result if they contain no interesting data), given their use of index-organized tables. Sure, you can partition on PK ranges instead of block ranges, but value density in a primary key can vary wildly over both time and value range.
Does the author have any info on how they plan to implement these more complex (but extremely useful) index methods?
This doesn't even consider the issues that might appear if the ordering rules (collation) change. Postgres' heap and vacuuming is ordering-unaware, meaning you can often fix corruption caused by collation changes by removing and reinserting the rows that are in the wrong location after the collation changed, with vacuum eventually getting rid of the broken tuples. I'm not sure Oriole can do that, as it won't be able to find the original tuple that it needed to remove with point lookup queries, thus probably requiring a full index rebuild to fix known corruption cases in the index, which sounds like a lot of additional maintenance.
> Does the author have any info on how they plan to implement these more complex (but extremely useful) index methods?
Regarding GiST analogue my plan is to build B-tree over some space-filling curve. Also, I'm planning to add union keys to the internal pages to make search over this tree faster and simpler.
Regarding GIN analogue, it would be still possible to compress the posting lists. The possible option would be to associate undo record not with posting list item, but with the whole posting list.
Regarding BRIN, I don't think we can do some direct analogue since we're using index-organized tables. But we can do something interesting with union keys in the internal pages of PK.
> This doesn't even consider the issues that might appear if the ordering rules (collation) change.
You're right, collation issue is serious. We will need to stick every collation-aware index to particular libicu collation version, before we go to GA.
The article makes some convincing arguments and the benchmarks seem to corroborate their performance claims, but I don't understand the dichotomy between this proposed new storage engine (OrioleDB?) and PostgreSQL itself.
Besides the commercial motivations and wanting to profit from the innovations discussed in the article, is there any reason why this needs to be a whole new database marketed as OrioleDB versus contributing these improvements upstream?
I'm seeing OrioleDB as a future engine for PostgreSQL. I'd like to see it as the default engine.
However, the changes in OrioleDB are too big to be made incrementally. This is why I'm comparing the current PostgreSQL engine (with more than just heap, but many other subsystems as well) with OrioleDB.
Alexander Korotkov (OrioleDb author) idea, - based on his Postgres committer experience, I believe, - is that these changes are way too big to be ever accepted upstream, hence separate engine. More info https://www.socallinuxexpo.org/sites/default/files/presentat..., see esp. slides 9-11
These changes are way too big to integrated into postgresql's engine itself. It fundamentally changes how MVCC is done.
Since this is an engine extension, I wonder if it would have any effect when combined with others. For example, timescaledb [0] acts on underlying tables. I wonder if this would have some effect if you did something like
create table xyz(...) using orioledb;
select create_hypertable(xyz, ts);
Is OrioleDB interested in committing to a stable on-disk format removing the need for an upgrade process between Postgres major versions? Seems like an opportunity to solve this problem.
> stable on-disk format removing the need for an upgrade process between Postgres major versions?
The need for the PostgreSQL upgrade process doesn't generally arise from the low-level on-disk formats of Postgres' heap and OrioleDB's table access method, but from changes in Postgres' catalogs. Things like the addition of a new type and its support functions will need to be inserted by some upgrade process. Then there are other catalog changes that change the column layout of the catalog tables, which also requires a process to update the stored data between the versions.
Without an upgrade process, you cannot change the catalogs, which is why only minor version upgrades of PostgreSQL can be done with only the swap of a binary, and can be rolled back safely without issue. It would limit upgrades to only internal APIs, planner, and executor changes, which would severely limit development.
I doubt that OrioleDB would be able to remove this need for an upgrade process for you.
This sounds unachievable on its face, unless you are willing to give up significant performance. On disk format is only changed for good reasons.
Not sure how that would help. PostgreSQL already has basically already committed to a stable on disk format for tables and indexes which is why pg_upgrade works. Most of the work upgrading a cluster cones from rewriting the catalog tables. And under most workloads pg_upgrade is very quick so I am also unsure where you think the big gain would be.
I love the whole “2.3x less CPU overhead per transaction” where Postgres scales from 5% to 65% CPU usage and Oriole sits constantly at 90%. That doesn’t seem like a huge success to me? The predictability sure is nice, but moving the lower end up by 85% is something I’d be rather worried about
You generally want to keep your CPU fully utilized. It looks like Oriole is doing significantly more transactions and is CPU-bound, due to much lower IO requirements. The good news is that it implies you could get even more performance out of Oriole by vertically scaling to a more powerful CPU, whereas Postgres would not continue to increase in performance this way.
Those idle times on the Postgres server could be used for something else, if you're thinking in a desktop OS mindset. But for servers, you tend to want machines that are doing one thing and are optimized for that thing.
My read is that it's at 90% because they are saturating the CPU to that point with the TPS threshold they use for comparison, the TPS of Oriole is constant and way higher than pg in these charts at least.
I'd think the CPU will drop proportionally to the TPS, they just want to show how high it can go here.
Yes, but now that your CPU utilization is uncapped, you can more easily scale the utilization down and retain some form of proportional performance, so it doesn't matter. If you capped the system to 60% of your CPU, it might change the overall numbers, but say you're doing 1.8x more TPS at the same usage, it's a win either way. It's not a marketing trick; those numbers come across as "Very good", to me.
If Expensive Server CPU = X dollars per unit, and it's only used at 60% capacity and can realistically only be used at that capacity, then you have effectively just set .4*X amount of dollars on fire, per unit. If you can vertically take a workload and scale it to saturate 90% of a machine, it's generally easy to apply QOS and other isolation techniques to achieve lower saturation and retain some proportional level of performance. The reverse is not true: if you can only hit 60% of your total machine saturation before you need to scale out, then the only way to get to 90% or higher saturation is through a redesign. Which is exactly what has happened here.
With the same equipment, your performance is now five times better. (5X higher TPS) We need to test again with more hardware, but if you can maintain 3 times the performance at the lower end, it could be a good alternative for some users.
"As the cumulative result of the improvements discussed above, OrioleDB provides:
- 5X higher TPS,
- 2.3X less CPU load per transaction,
- 22X less IOPS per transaction,
- No table and index bloat."
It was a performance test, where presumably the objective was to apply the maximum possible load each DB engine could handle, and apply that load continuous for a long period of time.
The CPU load jumping up and down isn’t Postgres “scaling” it Postgres hitting performance bottlenecks on a regular basis, presumably driven by the need to perform vacuums which are very IO insensitive. So instead of using IO to serve queries, Postgres is using IO for janitorial work, and TPS (and thus CPU usage) crater.
Oriole on the other hand manages much higher throughput, and much more consistently than Postgres.
What would you prefer a car that does a constant 100mph when your foot’s down. Or one that wildly oscillates between 40mph and 70mph, despite you trying to put the pedal through the floor?
Eyeballing the tps graph, OrioleDB is doing 5x tps while using 2x the CPU. So about 5/2=2.5x the CPU per transaction.
Checks out.
It's not clear if the CPU cost per tx is any worse. Was OrioleDB doing 5x the transactions at this CPU usage?
Yes, because they are performing more transactions per second, by virtue of performing less I/O per transaction. This is a good thing.
The article contains a link with the rather curious title "10 things that Richard Branson hates about PostgreSQL".... Turns out the guy who wrote that blog is called Rick Branson, not Richard.
Rick is a nickname for Richard.
"Richard Branson" is not a unique identifier. Maybe we should all go by UUIDs
That caught my eye, too. What do I care what the Virgin CEO thinks of a database?
Oh, not that one.
Is there any documentation on the "extensibility patches"? What did you have to do to core Postgres to get this new approach to work?
Vacuum does more than removing dead tuples though, there is still a need to update statistics and summarize BRINs.
If this engine is so much better than the internal one shouldn't we expect that at least the big cloud providers will use it on their managed servers? They have an economic incentive to do so. If that happens eventually the PostgreSQL project itself will replace the default engine, or am I wrong?
simple OrioleDB docker build tutorial :
https://github.com/orioledb/orioledb/blob/main/doc/docker_us...
I read object relational? Can someone enlighten me? Entity relational fine but what makes it object relational? Has someone flipped on the buzzword in the years I did not pay attention
Ledger-like (only inserts and selects) table design and management just remove the need of vacuuming.
Vacuuming becomes important with large tables. In those cases, naive design (with row updates and deletions) instead of a ledger-like one (without) is the real culprit, IMHO.
If you have billion rows tables I can imagine all those data are relevant. So, why not using a ledger-like approach and also keep a history as an extra bonus?
I generally put Postgres WAL on nonvolatile RAM (battery backed thing) and the database on a bunch of NVMe RAIDz3 arrays striped with two 64-core AMD EPYC CPUs. Is fast.
in the end it will be oracle with its undo tablespace .
Experimental format to help readability of a long rant:
1.
According to the OP, there's a "terrifying tale of VACUUM in PostgreSQL," dating back to "a historical artifact that traces its roots back to the Berkeley Postgres project." (1986?)
2.
Maybe the whole idea of "use X, it has been battle-tested for [TIME], is robust, all the bugs have been and keep being fixed," etc., should not really be that attractive or realistic for at least a large subset of projects.
3.
In the case of Postgres, on top of piles of "historic code" and cruft, there's the fact that each user of Postgres installs and runs a huge software artifact with hundreds or even thousands of features and dependencies, of which every particular user may only use a tiny subset.
4.
In Kleppmann's DDOA [1], after explaining why the declarative SQL language is "better," he writes: "in databases, declarative query languages like SQL turned out to be much better than imperative query APIs." I find this footnote to the paragraph a bit ironic: "IMS and CODASYL both used imperative query APIs. Applications typically used COBOL code to iterate over records in the database, one record at a time." So, SQL was better than CODASYL and COBOL in a number of ways... big surprise?
Postgres' own PL/pgSQL [2] is a language that (I imagine) most people would rather NOT use: hence a bunch of alternatives, including PL/v8, on its own a huge mass of additional complexity. SQL is definitely "COBOLESQUE" itself.
5.
Could we come up with something more minimal than SQL and looking less like COBOL? (Hopefully also getting rid of ORMs in the process). Also, I have found inspiring to see some people creating databases for themselves. Perhaps not a bad idea for small applications? For instance, I found BuntDB [3], which the developer seems to be using to run his own business [4]. Also, HYTRADBOI? :-) [5].
6.
A usual objection to use anything other than a stablished relational DB is "creating a database is too difficult for the average programmer." How about debugging PostgreSQL issues, developing new storage engines for it, or even building expertise on how to set up the instances properly and keep it alive and performant? Is that easier?
I personally feel more capable of implementing a small, well-tested, problem-specific, small implementation of a B-Tree than learning how to develop Postgres extensions, become an expert in its configuration and internals, or debug its many issues.
Another common opinion is "SQL is easy to use for non-programmers." But every person that knows SQL had to learn it somehow. I'm 100% confident that anyone able to learn SQL should be able to learn a simple, domain-specific, programming language designed for querying DBs. And how many of these people that are not able to program imperatively would be able to read a SQL EXPLAIN output and fix deficient queries? If they can, that supports even more the idea that they should be able to learn something different than SQL.
----
2: https://www.postgresql.org/docs/7.3/plpgsql-examples.html
Oh I see it’s not
Oracle db
It’s
Oriole db
Totally different
Oracle
Oriole
cough
Thanks fof the benchmark graphs, but the conclusion from the graphs is bogus:
- CPU load on a graph is actually higher for OrioleDB, not lower
- the factors of supposed speedup are not matching what we see on the graphs.