Postgres is eating the database world(medium.com) |
Postgres is eating the database world(medium.com) |
- The codebase is old and huge, accruing some heavy technical debt, making it a less than ideal foundation for iterating quickly on a new paradigm like AI and vector databases.
- Some ancient design decisions have aged poorly, such as its one connection per process model, which is not as efficient as distributing async tasks over thread pools. If not mitigated through an external connection pooler you can easily have real production issues.
- Certain common use cases suffer from poor performance; for example, write amplification is a known issue. Many junior developers mistakenly believe they can simply update a timestamp or increment a field on a main table with numerous columns.
So, yes, PG is one of the best compromises available on the database market today. It's robust, offers good enough performance, and is feature-rich. However, I don't believe it can become the ONE database for all purposes.
Using a dedicated tool best suited for a specific use case still has its place; SQLite and DuckDB, for instance, are very different solutions with interesting trade-offs.
Regarding wide updates, I believe that HOT updates already partially solve this problem.
Oracle uses the same model by default on Linux.
Since 19 (or maybe earlier) it is configurable though, but the default is still one process per connection if I'm not mistaken.
At least it has an optimization that if the insert ends up in the same page, it won't need to update the index https://www.postgresql.org/docs/current/storage-hot.html
Replication has a similar amplification issue. Historically postgres has favored physical replication over per-row logical replication, that means that replication needs to transfer every modified page, including modified indexes, instead of just the new value of the modified row. (I think logical replication support has improved over the last couple of years).
There is the OrioleDB project, which attempts to improve on the design flaws in postgres's storage engine, but it's definitely not production ready yet.
they have several ways to write extensions: extensions and fdw, so you can build your cool AI stuff without digging into PgSQL sources much.
I played with postgresql a while ago to implement search. It's not horrible. But it's nowhere near Elasticsearch in terms of its capabilities. It's adequate for implementing very narrow use cases where search ranking really doesn't matter much (i.e. your revenue is not really impacted by poor precision and recall metrics). If your revenue does depend on that (e.g. because people buy stuff that they find on your website), you should be a bit more careful about monitoring your search performance and using the right tools to improve performance.
But for everything else you only have a handful of tools to work with to tune things. And what little there is is hard to use and kind of clunky. Great if that really is all you need and you know what you are doing but if you've used Elasticsearch and know how to use it properly you'll find your self missing quite a few things. Maybe some of those things will get added over time but for now it simply does not give you a lot to work with.
That being said, if you go down that path the trigram support in postgres is actually quite useful for implementing simple search. I went for that after trying the very clunky tsvector support and finding it very underwhelming for even the simplest of use cases. Trigrams are easier to deal with in postgres and you can implement some half decent ranking with it. Great for searching across product ids, names, and other short strings.
https://github.com/dolthub/doltgresql/
We're doing this because our main product (Dolt) is MySQL-compatible, but a lot of people prefer postgres. Like, they really strongly prefer postgres. When figuring out how to support them, we basically had three options:
1) Foreign data wrapper. This doesn't work well because you can't use non-native stored procedure calls, which are used heavily throughout our product (e.g. CALL DOLT_COMMIT('-m', 'changes'), CALL DOLT_BRANCH('newBranch')). We would have had to invent a new UX surface area for the product just to support Postgres.
2) Fork postgres, write our own storage layer and parser extensions, etc. Definitely doable, but it would mean porting our existing Go codebase to C, and not being able to share code with Dolt as development continues. Or else rewriting Dolt in C, throwing out the last 5 years of work. Or doing something very complicated and difficult to use a golang library from C code.
3) Emulation. Keep Dolt's Go codebase and query engine and build a Postgres layer on top of it to support the syntax, wire protocol, types, functions, etc.
Ultimately we went with the emulation approach as the least bad option, but it's an uphill climb to get to enough postgres support to be worth using. Our main effort right now is getting all of postgres's types working.
But if it allows me to use my existing Postgres tools, drivers, code, and knowledge then I’d consider it.
I'm not a database developer, and last time I researched this (a few years ago) I found many good reasons for not enabling this from postgres contributors. But it would still be very useful.
So Postgres and Python were not the obvious choices back then.
Despite me not knowing much about databases it seemed like an obvious choice.
The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance.
Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.
It means complex views that could take minutes or hours to calculate from scratch can be kept fresh in realtime.
And a surprising amount of other stuff (similar to lisp inner platforms) converges on half-hearted, poorly-implemented replications of Postgres features… in this world you either evolve to Cassandra/elastic or return to postgres.
(not saying one or the other is better, mind you… ;)
I am not aware of such machine in a single Node unless it is talking about vCPU / Thread. Intel Sierra Forest 288 Core doesn't do dual socket option. So I have no idea where the 512 x86 core came from.
The author pulled it from an article linked in the previous sentence. The numbers don't even add up unless it was a mistake or I'm missing something.
So in a dual-socket setup, 2 x EPYC 9754 would indeed yield 512 threads (logical cores), which are backed by 256 physical cores.
All recent projects in my company are PostgreSQL based (> 2000 production applications), and we have far fewer troubles with PostgreSQL than with Oracle, not to mention the licensing.
But I don't use PG because my needs are not so heavy nor have I the data sizes or advanced features that makes PG a more reliable choice over sqllite.
I have been stating this since at least 2020 if not earlier.
We are expecting DDR6 and PCI-E 7.0 Spec to be finalised by 2025. You could expect them to be on market by no later than 2027. Although I believe we have reach the SSD IOPS limits without some special SSD with Z-NAND. I assume ( I could be wrong ) this makes SSD bandwidth on Server less important. In terms of TSMC Roadmap that is about 1.4nm or 14A. Although in server sector they will likely be on 2nm. Hopefully we should have 800Gbps Ethernet by then with ConnectX Card support. ( I want to see the Netflix FreeBSD serving 1.6Tbps update )
We then have software and DB that is faster and simpler to scale. What used to be a huge cluster of computer that is mentally hard to comprehend, is now just a single computer or a few larger server doing its job.
There is 802.3dj 1.6Tbps Ethernet looking at competition on 2026. Although product coming through to market tends to take much longer compared to Memory and PCI-Express.
AMD Zen6C in ~2025 / 2026 with 256 Core per Socket, on Dual Socket System that is 512 Core or 1024 vCPU / Thread.
The future is exciting.
yet, their db can't handle many cases where data doesn't fit into memory, and PgSQL always does large writes in single thread..
But at a certain point, a 10,000 core 5 petabyte single megamachine starts to practically encounter CAP from the internal scale alone. It already ... kind of ... does.
And no matter how big your node scales, if you need to globally replicate data ... you have to globally replicate it over a network, and you need Cassandra (DynamoDB global replication is shady last I looked at it, I have no idea how row-level timestamps can merge-resolve conflicting rows updated in separate global regions)
One thing I've always liked about MySQL is that it pretty much looks after itself, whereas with Postgres I've had issues before doing upgrades (this was with brew though) and I'm not clear on whether it looks after itself for vacuuming etc.
Should I just give it a go the next time I'm upgrading? It does seem like a tool I need to get familiar with.
25+ years ago MySQL was fast and easy to admin but didn't have rollback and a bunch of other features. At the same time Postgres had the features but was horrible for performance and usability. Those days are LONG gone. Mysql obviously has all the features and PG is great to admin and the auto-vacuum works well out of the box.
I run a bunch of clusters of pg servers around the world and they need almost no maintenance. In place upgrades without needing to go the dump/restore route work well, 5 minutes on a TB sized database, just make very VERY sure you do a reindex afterwards or you will be in a world of pain.
What do you use for it? Is there anything like phpmyadmin for postgres with similar simplicity?
You don’t need to dump and re import the database since a long long time…
But Postgres is a work of art, and compared to all the other relational database options, if it's ultimately crowned the king of them all, it'd be well deserved.
I'd also say that the PG protocol and the extensions ecosystem are as important as the database engine.
The analytics part should scale independently. Often this is only needed occasionally, so scale-to-zero (like Snowflake) would be great.
Clickhouse supports it too https://clickhouse.com/docs/en/sql-reference/table-functions...
I have seen a lot of people praising Postgres over e.g. MariaDB. But more often than not it seems to be people how lack knowledge.
Take this linked post, where the author points out "The untuned PostgreSQL performs poorly (x1050)" later followed by "This performance can’t be considered bad, especially compared to pure OLTP databases like MySQL and MariaDB (x3065, x19700)".
Frist of all, those are not pure OLTP databases. And if the author took a better look at the benchmark he would see that MariaDB using ColumnStore is at x98. That's 10x the performance of Postgres out of the box, and 200x faster than the author stated.
How does one go about finding paying customers when developing a new database tool? How does one figure out the size of the market, and pricing structure?
And then after a couple of years people will realise that Postgres can do everything the trendy database can do and come back to Postgres. Happens every decade. This is at least 'hype cycle' 3 for Postgres since I started my career.
You're not wrong here, although you could just as easily say "99% of people who recommend $DB barely know how to use it."
Databases remain a mysterious black box to entirely too many people, despite the three largest (SQLite, Postgres, MySQL) being open source, and having extensive documentation.
I've come to the conclusion that most devs don't care about infra in the slightest, and view a DB as a place to stick data. When it stops working like they want, they shrug and upsize the instance. This is infuriating to me, because it's the equivalent of me pushing a PR to implement bogosort, and when told that it's suboptimal, dismissing the criticism and arguing that infra just needs to allocate more cores.
[0] https://www.postgresql.org/docs/current/pgtrgm.html
[1] Reciprocal Ranked Fusion: https://supabase.com/docs/guides/ai/hybrid-search
The point of that of course being that the target audience for this stuff is actually people that for whatever reason are a bit shy using the right tools for the right job here and are probably lacking a lot of expertise. The intersection of people with the expertise that would be happy with this narrow subset of functionality is just not a lot of people.
The article is referring to the ParadeDB extension, not the built-in full text search
---
Just did a HN submission for it (https://news.ycombinator.com/item?id=39712211) now too.
For many queries, the order in which you specify the joins doesn't really matter. But there are a number of classes where the join order dramatically affects how fast the query can actually run and nothing the query planner does will change this.
I came across this problem around 30 years ago. By accident, I discovered what the problem cause was - the order of the joins. The original query was built and took 30 - 40 minutes to run. I deleted particular joins to see what intermediate results were. In reestablishing the joins, the query time went to down to a couple of seconds.
I was able to establish that the order of joins in this particular case was generating a Cartesian product of the original base records. By judicious reordering of the joins, this Cartesian product was avoided.
If you are aware of this kind of problem, you can solve it faster than any query planner ever could.
You can raise the limit at the risk of causing query planning times going up exponentially, or refactor your schema, or, as you did, rewrite it to be more restrictive out of the gate. That way, those join paths will be found first and so will be the best found when the planner gives up.
Another thing to consider is table fragmentation. Fragmentation > bad row count estimation > bad query plan.
Examining statistics for your tables / indices can be quite helpful in determining the issue.
That's why I assume it's not in vanilla postgres: adding the future with so many caveats would not make for a great experience for the full breadth of postgres users.
The easy way is to rebuild everything if any “from table” as been modified.
The manual way is to create triggers that perform the minimal updates.
Streaming frameworks like Kafka Streams and Flink have incrementally updating tables in memory.
Materialize is built around the concept with a Postgres compatible API.
ClickHouse materialized views act like insert triggers which update when the base table is updated.
That is a BIG lift. Joins don't really practically scale at the Cassandra/Dynamo scale, because basically every row in the result set is subject to CAP uncertainty. "Big Data SQL" like Hive/Impala/Snowflake/Presto etc are more like approximations at true scale.
Relational DBMS is sort of storage-focused in the design and evolution: you figure out the tables you need to store the data in a sensible way. They you add views and indexes to optimize for view/retrieval.
Dyanmo/Cassandra is different, you start from the views/retrieval. That's why it is bad to start with these models for an application because you have not fully explored all your specific data structuring and access patterns/loads yet.
By the time Postgres hits the single node limits, you should know what your highest volume reads/writes are and how to structure a cassandra/dynamo table to specifically handle those read/writes.
These are all wildly different products that should not be considered for the same purposes.
Frankly if you zoom out far enough they're all systems suitable for use as your primary online datastore that you build your application on (each with their own caveats of course). There are places where they compete.
> First, a whole army of developers writing JavaScript for the browser started self-identifying as “full-stack”, diving into server development and asynchronous code.
> ...early JavaScript was a deeply problematic choice for server development. Pointing this out to still green server-side developers usually resulted in a lot of huffing and puffing.
[0]: https://renegadeotter.com/2023/09/10/death-by-a-thousand-mic...
Combination of two joins, filtering on all tables, and sorting by the LEFT one. Performance was fine, until we hit the scale when it suddenly became unpredictable.
In hindsight, given the variability of the queries and table structure, I don't think any query planner could have done a good job. The natural answer was to denormalize. But the journey to get there was a little unpredictable.
Dbeaver is more useful. But you can do everything from the command line. You use sql to write database migrations anyway, no?
Postgres has been great all around.
It is kind of depressing that 25 years later, no other language has even attempted to compete with PHP in the “easy to get started on bargain-basement-tier shared web hosts” space D:
PHP has been very good at reinventing itself and being its own biggest competitor. Zend outcompeted PHP3, PHP5 outcompeted PHP4, PHP7 outcompeted PHP5 and so on. Compare a site written in modern idiomatic PHP8 using something like Laravel to a site written in classic PHP3 and they might as well be two different languages.
Not fashionable by any means - but still feasible to maintain unlike Windows IIS / C++ or Oracle / Perl / Flash.
* Usually I've been able to force query plans by disabling whole operations for a session, such as disallowing "sort" to make it use an index. The real fix in this case, for example, was to use CLUSTER to re-order data on disk, so the correlation statistic was close to 1 and postgres wanted to use the index instead of table scan + sort.
And here we see the benefit of clustered indices, á la MySQL. Assuming, of course, your PK is k-sortable.
SET enable_seqscan = OFF;
If you're on an SSD and not a spinning disk, or have a lot of memory and can expect it to all be cached, there's a separate setting you can change to adjust the random access penalty - see random_page_cost on https://www.postgresql.org/docs/current/runtime-config-query...
Postgresql doesn't have this, however, I've rarely missed this feature - tables with good indexes, regular db hygiene, etc, almost always perform excellently. The query planner is very, very smart nowadays.
There's DB internals/performance course which I'm a TA for and we lean heavily on being able to force Oracle's rule-based or cost-based optimisers --- because then we can get the students to analyse and compare the plans.
As a user, postgres is a far more ergonomic database, but things like this show good old insane oracle is still strongly ahead on some of these points.
In practice, postgres is not as bad as you'd expect in the large, but it can still be anoying.
> In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.
I think that unless you're only doing table scans, or your rows are inserted in no discernible order, you should see a speedup. If the latter though, then yes of course, page jumps are page jumps.
Happy to test this to find out.