8 ms·
PostgreSQL: No More Vacuum, No More Bloat
- emmanueloga_ 3y agoExperimental 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. ---- 1: https://dataintensive.net/ https://dataintensive.net/ 2: https://www.postgresql.org/docs/7.3/plpgsql-examples.html https://www.postgresql.org/docs/7.3/plpgsql-examples.html 3: https://github.com/tidwall/buntdb https://github.com/tidwall/buntdb 4: https://tile38.com/ https://tile38.com/ 5: https://www.hytradboi.com/ https://www.hytradboi.com/
- riku_iki 3y ago> 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 that's exactly what OP company is doing: they are building storage engine for postgres.
- emmanueloga_ 3y agoI doubt this initiative is gonna make Postgres easier to use, smaller in terms of dependencies, simplicity of its codebase or resource usage.
- necovek 3y ago> 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. 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.
- daenney 3y agoI 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
- adsharma 3y agoIt's not clear if the CPU cost per tx is any worse. Was OrioleDB doing 5x the transactions at this CPU usage?
- deleted 3y ago[deleted]
- jklehm 3y agoMy 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.
- pella 3y agoWith 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."
- waterproof 3y agoEyeballing 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.
- gary_0 3y ago5x tps with 2x CPU is 2/5 = 0.4x the CPU (ie. it's more efficient per transaction).
- pella 3y agosimple OrioleDB docker build tutorial : https://github.com/orioledb/orioledb/blob/main/doc/docker_usage.md https://github.com/orioledb/orioledb/blob/main/doc/docker_us...
- ccleve 3y agoIs there any documentation on the "extensibility patches"? What did you have to do to core Postgres to get this new approach to work?
- akorotkov 3y agoPlease, check this. https://supabase.com/blog/postgres-pluggable-strorage https://supabase.com/blog/postgres-pluggable-strorage https://www.pgcon.org/events/pgcon_2023/schedule/session/470-future-of-table-access-methods/ https://www.pgcon.org/events/pgcon_2023/schedule/session/470... Pushing patches to PostgreSQL Core requires a lot of work. But there is a progress already: 5k lines patchset to PG 14, and 2k lines patchset to PG 16.
- justinclift 3y agoAwesome. Thanks heaps for your efforts on this! Hopefully it all gets through the hurdles eventually, becoming a new storage engine shipped by default in PG. Maybe even becoming the new default. :)
- ksec 3y ago>Please, check this. https://supabase.com/blog/postgres-pluggable-strorage https://supabase.com/blog/postgres-pluggable-strorage So 60% of code committed to PG 16 already?
- mlyle 3y ago60% of the code needed to run it as a normal outside extension.
- soleri17 3y agogood job!
- oaiey 3y agoI 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
- feike 3y agoPostgreSQL has used this term for decades! 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 https://www.postgresql.org/docs/6.3/c0101.htm https://www.postgresql.org/docs/6.3/c0101.htm
- oaiey 3y agoThanks. Very much a buzzword then.
- brazzy 3y agohttps://stackoverflow.com/questions/45865961/what-does-postgresql-to-be-ordbms-mean https://stackoverflow.com/questions/45865961/what-does-postg...
- grzm 3y agoIt's been object relational (and described as such) going way back. I think the most visible (if infrequently used) object-oriented feature that it has is inheritance: https://www.postgresql.org/docs/current/tutorial-inheritance.html https://www.postgresql.org/docs/current/tutorial-inheritance...
- nieve 3y agoIt's because PostgreSQL has inheritance and has almost certainly used the term object relational since before you heard of it.
- 3y ago
- mynonameaccount 3y ago[dead]
- glogla 3y agoI 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.
- paulddraper 3y agoSQL 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.
- glogla 3y agoI'm pretty sure SQL Server and MySQL use locking instead of MultiVersion Concurrency Control so they don't keep more copies of data around. No vacuum needed but there's a possibility of things blocking. But I might be out of date.
- paulddraper 3y agoYes, MySQL has read locks. But I don't believe SQL Server does.
- evanelias 3y agoInnoDB (MySQL's default storage engine) implements MVCC using undo logging and background purge threads. It scales to highly concurrent OLTP workloads quite well. It doesn't work well with OLAP workloads / long-running transactions though. The oldest active transaction will block purging of anything newer than that transaction's snapshot.
- ruuda 3y agoVacuum does more than removing dead tuples though, there is still a need to update statistics and summarize BRINs.
- javajosh 3y agoYes. The (psql 15) docs are well written: https://www.postgresql.org/docs/15/routine-vacuuming.html https://www.postgresql.org/docs/15/routine-vacuuming.html
- wild_egg 3y agoYou're thinking of ANALYZE which is a separate operation that's commonly run during vacuuming but can be invoked independently
- rickette 3y agoThe 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.
- rcme 3y agoRick is a nickname for Richard.
- inhumantsar 3y ago[flagged]
- jeffparsons 3y agoTrue words can still be clickbait. 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.
- mattl 3y agoSee the guy on Bluesky who is called Steve Wozniak and isn’t trying to pretend to be Woz and yet has issues while Bluesky also let someone with a racial slur username get an account
- rcme 3y agoI get what you’re saying, but imagine your name was Richard Branson. You’d hear no end to the jokes. At what point can you consider this an internalized behavior of the author? Is it still clickbait if the author believes his main raison d’etre is to have a meme name?
- jeffparsons 3y agoI think I basically agree with you. And this example is pretty benign — I'm not actually meaning to criticize anyone here. 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. :)
- eduction 3y agoOh I see it’s not Oracle db It’s Oriole db Totally different Oracle Oriole cough
- deleted 3y ago[deleted]
- resist_futility 3y agoBut also not to be confused with OLEDB
- eyegor 3y agoSince 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); [0] https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb
- ctippett 3y agoThe 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?
- waplot 3y agoThese changes are way too big to integrated into postgresql's engine itself. It fundamentally changes how MVCC is done.
- fuy 3y agoAlexander 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/presentations/solving-postgres-wicked-problems.pdf https://www.socallinuxexpo.org/sites/default/files/presentat..., see esp. slides 9-11
- akorotkov 3y agoI'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.
- tarasglek 3y agoJust curious, are you ukrainian? Never thought I would see a fellow ukrainian rewriting my fav db.
- tarasglek 3y agoCurious if you could share further roadmap. Potential interesting directions: 1. plans for integration with object store ala neon? 2. Columnar? 3. Async-io oriented redesign 4. Interesting new features ala subscriotions to table changes 5. Zero copy client bindings
- wokwokwok 3y agoGood 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?
- justinclift 3y agoAs a data point, that's already been answered: :) https://news.ycombinator.com/item?id=36742001 https://news.ycombinator.com/item?id=36742001
- fshbbdssbbgdd 3y agoReasonable 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.
- klysm 3y ago> a company with the resources to maintain a Postgres fork That sounds like a monumental feat
- mjlawson 3y agoI'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. [1] https://github.com/orioledb/orioledb https://github.com/orioledb/orioledb
- newaccount74 3y ago> 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.
- nextaccountic 3y ago> this is designed to work as an extension for an unforked PostgreSQL. That's pretty cool! > (It doesn't yet) What's missing?
- HHad3 3y agoThe PostgresBuild 2021 slides of OrioleDB [1] (also linked in the GitHub project's readme) mention that there is a 1K LoC patch that adds features to the extension interface. I guess the patch is larger by now in 2023. 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-postgresql-wicked-problems https://www.slideshare.net/AlexanderKorotkov/solving-postgre...
- pmontra 3y agoIf 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?
- klysm 3y agoI’m not sure the risk of an immature engine is worth it to them. Customers pay for hosted Postgres because they want to not worry about doing it themselves for cheaper. They are paying for reliability. 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
- Rapzid 3y ago> I’m not sure the risk of an immature engine is worth it to them Heh, about that.. Hasn't AWS already crossed that threshold with Aurora RDS, Redshift, and etc?
- hlandau 3y agoIs 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.
- dboreham 3y agoThis sounds unachievable on its face, unless you are willing to give up significant performance. On disk format is only changed for good reasons.
- mattashii 3y ago> 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.
- jeltz 3y agoNot 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.
- kunley 3y agoThanks 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.
- jpgvm 3y agoI think you might have misread the graphs. The graph is showing a 4x peak/~6x average improvement in TPS. Because of this the load is less I/O bound and thus CPU is able to be fully utilised. If you want to measure efficiency you would instead measure at constant TPS.
- wryanzimmerman 3y agoI’m not sure the cpu load being higher is a bad thing, isn’t that basically showing that it is using less IO so it can use more of the cpu? 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.
- mst 3y agoGiven the higher TPS I think you'd be better conceptualising it as "CPU utilisation" rather than "CPU load."
- kunley 3y agoIt is not about my "conceptualising", I was simply referring to the fact that the conclusions refer to something different than the graphs. The author himself uses the term "load", so I guess we should stick to what he meant. Still, graph is showing the absolute value of the load, not per-transaction value. Then it takes an extra effort to actually realize that maybe the author's claim is valid, but in a per-transaction context. Why then the graph wasn't made to plot per-transaction values if that was author's point? It adds unnecessary confusion. That confusion is perceived just after reading another inconsistency - that there is supposed 5x tps speedup while we see 4× (visible 80k divided by visible 20k is 4 not 5). So why 5x? Was this 5x based on a median perhaps, or on some percentile- then why such a median or percentile wasn't shown on the graph? ..and so on. 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.
- mattashii 3y agoOriole'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.
- akorotkov 3y ago> 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.
- ivoras 3y agoYeah, 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-postgresql/ 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.
- akorotkov 3y ago> - 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 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 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).
- gosha81 3y agoin the end it will be oracle with its undo tablespace .
- orthoxerox 3y agoYes, that's exactly the goal. Some workloads prefer vacuum, some prefer undo logs.
- notorandit 3y agoLedger-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?
- user6723 3y agoI 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.