PostgreSQL: No More Vacuum, No More Bloat
orioledb.com
orioledb.com
- 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.
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).
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?
That sounds like a monumental feat
this is NOT a new database
> 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.
That would be really the perfect outcome
maybe don't come in so hot next time.
There've been quite a few postgres forks that have either died or stayed based on 7.x versions and when you're replacing the entire storage engine - and hence also the on-disk format - migrating away from it if circumstances require it later is going to be annoyingly non-trivial.
So while I think I agree with "don't come in so hot", "absurdly aggressive" may nonetheless be over-egging it slightly given the context.
This phase adds significant enhancements to make the OrioleDB extension feasible and aggressively performant.. and the delta code to be committed upstream is less than 2K LOC.
Comparing this project to earlier forks that got stuck at 7.x and 8.x would be a huge disservice to the maturity and extensibility of the Postgres project.
On your latter point, OrioleDB does not “replace” the built-in storage engine (which works quite well for many many use-cases), it “augments” the core capabilities with an additional storage engine optimised for many use-cases where the legacy engine struggles.
HTH
Then mainline it?
The last commit that /postgres/postgres and /orioledb/postgres share (1) is 6 months old for 15.2?
I mean, this is literally what I'm pointing out in my comment; you're chasing a moving target. Every change postgres makes, you have to merge in check it doesn't conflict, roll out a new patch set...
...and, you're already falling behind on it.
So, going forward, how do you plan to keep up to date...? ...because it looks to me, like a < 2K LOC isn't a fix for this problem; it hasn't solved it for you, as time goes forwards, it will continue not to be a solution to the problem; annd...
> Currently the changes needed to Postgres core are less than a 1000 lines of code. Due to the separate development schedules for Postgres and OrioleDB, these changes cannot be unstreamed in time for v.15. (2)
^ They were < 1000 lines a year ago, apparently. So the problem is getting worse over time.
Seems like the solution is mainlining those changes... not just intending to mainline them.
Until then... I really just see this as a postgres fork.
[1] - https://github.com/postgres/postgres/commit/78ec02d612a9b690... vs https://github.com/orioledb/postgres/commit/78ec02d612a9b690...
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.
And of course MySQL avoids vacuum by giving a giant middle to concurrency considerations.
But I might be out of date.
But I don't believe SQL Server does.
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
- 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.
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.
That's pretty cool!
> (It doesn't yet)
What's missing?
Slide 45 specifically lists:
* Extended table AM
* Custom toast handlers
* Custom row identifiers
* Custom error cleanup
* Recovery & checkpointer hooks
* Snapshot hooks
[1] https://www.slideshare.net/AlexanderKorotkov/solving-postgre...
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.
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.
This is my first time hearing of "union keys", and I can't seem to find it using DDG or arxiv. Would you mind explaining the concept (or pointing me in the right direction)?
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?
Never thought I would see a fellow ukrainian rewriting my fav db.
2. Columnar?
3. Async-io oriented redesign
4. Interesting new features ala subscriotions to table changes
5. Zero copy client bindings
create table xyz(...) using orioledb;
select create_hypertable(xyz, ts);
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.
I'd think the CPU will drop proportionally to the TPS, they just want to show how high it can go here.
"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."
Checks out.
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.
It may be reasonable to suggest that for a new code base that is cpu bound there’s a good chance there is low hanging fruit for cpu optimizations that may further increase the throughput gap. It’s also the case however that the prior engines tuning starting life on much older computer architectures, drastically different proportional syscall costs and so on, it very often means that there’s low hanging fruit in configuration to improve baseline benchmarks such as these. High io time suggests poor caching which in many scenarios you’d consider a suboptimal deployed configuration.
It’s not just the devil that’s in the details, it’s everything.
Parallelizing IO is a lot different from scaling up CPU power, though. I'd imagine DB server IO performance has a lot less lower-hanging fruit than CPU/software performance.
Similarly in the cloud on AWS fro example, you have publicly available scalability options starting from 5k IOPS up to 2M IOPS, >400x or 3 orders of magnitude. By contrast you're going from 1vcpu to 192 cores, about half the raise, and a lower performance scaling due to the increased cost of cross-package shootdowns.
Yup, they're different, for sure, but the implication that CPU is easier is not all that clear. In either case, with a database style workload, and with either of these engines in practice you're going to hit a limit at the bus in practice long before you hit a limit on compute or disk io, for any sustained workload - bursts are different.
Not in real life concurrent systems where latency matters. In addition to the queuing/random request arrival rate reasons, all kinds of funky latency hiccups start happening both at the DB and OS level when you run your CPU average utilization near 100%. Spinlocks, priority inversion, etc. Some bugs show up that don’t manifest when running with lower CPU utilization etc.
[0] https://brooker.co.za/blog/2021/05/24/metastable.html / https://archive.is/6Qtet
If you tested both systems with the same workload (eg. a specific number of queries per second), then the average CPU usage would be much lower for the more efficient engine.
The low CPU usage in this benchmark is just a sign that the performance is not CPU bound, but limited by other factors like locking or IO.
If you want lower max CPU load, just limit its resources (e.g., CPU quota, cpuset limitation) or load it less.
Only if your load is very predictable. If there is a chance of a spike, you often want enough headroom to handle it. Even if you have some kind of automated scaling, that can take time, and you probably want a buffer until your new capacity is available.
In that sense, you want to be able to have your database be able to use all the resources available: all the IOPS, all the CPU cycles, etc.
And, of course, the real thing is the amount of work you get done: this thing does more work-- partially by using more CPU cycles, and partially by doing more work per CPU cycle.
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.
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?
In fact, I'd argue that many of the most effective ways to mislead people involve sticking rigidly to literal truth, because it makes them so much harder to counter. When there's no literal untruth to correct, it's natural to end up implying bad faith _without having any definitive proof_, and that is mighty unstable ground from which to argue.
However I will not that the author in question refers to himself as "Rick Branson", and the article title is "10 Things I Hate About PostgreSQL". So I think it's just the person who made the link who is being a bit cheeky.
My comment was going off on a wild tangent. :)
Certainly it wouldn't've occurred to me to think it was the businessman rather than a name collision.
But, eh, agreed on tangent, and I'm not intending to criticise either.
Oh, not that one.
Hopefully it all gets through the hurdles eventually, becoming a new storage engine shipped by default in PG. Maybe even becoming the new default. :)
So 60% of code committed to PG 16 already?
I think you’re correct about the existence of an economic incentive for the cloud providers, but I anticipate it would be offered as a distinct product to “vanilla” (at least in the sort term).
Things get interesting though because this space of database products has trended towards restricting who can host in their license terms (TimeScale, ClickHouse, etc). If that’s Orioles cash-in play then maybe cloud providers can’t use it anyway.
I suspect the fate of the engine will be determined by its funding source
Heh, about that.. Hasn't AWS already crossed that threshold with Aurora RDS, Redshift, and etc?
https://github.com/orioledb/orioledb/blob/main/doc/docker_us...
The oldest I can find is from 1998 (PostgreSQL 6.3), but it was probably in use even before.
> Postgres offers substantial additional power by incorporating the following four additional basic concepts in such a way that users can easily extend the system:
classes inheritance types functions
Other features provide additional power and flexibility:
constraints triggers rules transaction integrity
These features put Postgres into the category of databases referred to as object-relational
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?
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
that's exactly what OP company is doing: they are building storage engine for postgres.
It gets harder as you delve into high concurrency and ensuring ACID: if you are using an established database, these are simply problems you don't have to deal with (or rather more truthfully, there are known ways to deal with them like issuing an "UPDATE x=x+1" instead of fetching x and then setting it to x+1).
Still, writing an application expecting the datastore to ensure consistency is one thing, and ensuring that consistency are different problems requiring a different mindset (you are thinking of hard problems of your business logic, but you also have to think of hard problems common to db engines at the same time?).
> 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.
The benefit of languages as ubiquitous as SQL is that once you need something that you did not think of, SQL already enables it. But plenty of non-relational databases provide their own non-SQL APIs already (ElasticSearch, Redis, MongoDB, DynamoDB...), and as you suggest, developers cope with them just fine.
However, people used to expressiveness of SQL (even if we all know it's imperfect), always miss what they can achieve with a single query moving performance (and some correctness) considerations to the database. The idea is as old as programming: transfer responsibilities for accessing data performantly to whatever is managing that data, even if we know that there are always cases where it's an uphill battle.
It's that combination of good-enough performance, good-enough expressiveness, impressive consistency and correctness, and relational databases (and SQL) are a great choice for most applications today.
> you are thinking of hard problems of your business logic, but you also have to think of hard problems common to db engines at the same time?
YES! everyone is complaining these days about slow software in our beefy machines. I guess the core of my rant is that it feels like all of us programmers should start caring a lot more about data organization, code size, minimizing dependencies, data oriented design and "mechanical sympathy". Advances in languages, tooling and accessibility to information should demystify the how-to of managing our own application data ourselves.
However, I think our applications are not slow due to database access, but one too many layers of indirection otherwise: eg even ORMs usually introduce a huge performance and complexity cost.
Just like we are trying to come up with better and less error prone concurrency models in code (async/await, coroutines...), I get that you are trying to come up with better tooling support for data access, and we should.
But we also need to be aware that some people simply want to solve a problem more efficiently, but not most efficiently (look at most ML code and you can barf at it — yet it still makes a huge progress in one area they care about).
Oracle db
It’s
Oriole db
Totally different
Oracle
Oriole
cough
- 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.
The throughput is way, way higher, so it’s using less cpu per transaction. If this were showing equal numbers of transactions the CPU usage would be lower.
Ideally, in a benchmark, I think we’d be seeing basically 100% cpu usage because that would mean the test hardware is being fully utilized and the software being tested isn’t being bottlenecked in some way.
Please don't get me wrong. 4x tps speedup is nice achievement already. It's great enough to congratulate the author and be happy. But it's also presentation of the result that matters, if there are inconsistencies, or the author based his claims on a different measurements than what is shown, then it's natural that it can make one to raise in eyebrow. It doesn't solidify the trust, as opposed to presenting the conclusions matching the graphs exactly.
So bigger CPU load and bigger performance mean that parallelising of tasks is more efficient, which is a clear benefit.