Postgres is eating the database world | Hacker News Reader

Postgres is eating the database world

medium.com

281 pointsVonng142 comments

Postgres is far from perfect:

- 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.

I believe that there are contributors currently working on a one thread per connection version of PostgreSQL. It's a huge amount of work so I wouldn't expect it to be released tomorrow.

Regarding wide updates, I believe that HOT updates already partially solve this problem.

Can you tell me more about the write amplification issue?

Postgres handles updates as insert+delete, and its secondary indexes reference the physical location of the row, instead of the primary key. This means that whenever an update results in an insert to a different page, the index needs to be updated as well, even if the indexed column hasn't been modified.

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.

It’s worse than that, unfortunately – since the entire row has to be updated due to MVCC, if a single index is updated, then _all_ indexes are updated. If you have a wide table with a lot of indexes, each UPDATE is N*index writes. This becomes even worse with multiple updates if indexed columns aren’t k-sortable (like UUIDv4), since now the DB will probably have to jump around multiple pages despite the rows being logically sequential.

> Some ancient design decisions have aged poorly, such as its one connection per process model

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.

It's actually quite impressive they were willing & able to make such a drastic change in such an old and conservative codebase.

Not sure how drastic it was to be honest. Under Windows, multi-threading was always the only option. So in theory, they already had code for that.

> new paradigm like AI and vector databases.

they have several ways to write extensions: extensions and fdw, so you can build your cool AI stuff without digging into PgSQL sources much.

> not to mention its ElasticSearch grade full-text search capabilities.

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.

That's true for the kernel, How about extensions such as ParadeDB BM25 https://www.paradedb.com/ + PGroonga https://pgroonga.github.io/ + PG Bigm https://github.com/pgbigm/pg_bigm ?

also with pg_trgm[0] (mentioned by OP) and pgvector for semantic search you have a pretty powerful search toolkit. for example, combining them for Hybrid Search [1]

[0] https://www.postgresql.org/docs/current/pgtrgm.html

[1] Reciprocal Ranked Fusion: https://supabase.com/docs/guides/ai/hybrid-search

the difference is cached/pre-calc results in a (big on disk, expensive to compute) inverted index.. you cannot beat that at runtime

Still very limited and frankly all a bit low level primitives. Unless you are a search expert, you won't be able to do much productive with this stuff. If you are, it might fit a few use cases. But then, why limit yourself to just this stuff?

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.

> 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.

The article is referring to the ParadeDB extension, not the built-in full text search

We're writing a postgres-compatible database that doesn't use any postgres code:

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.

I can totally understand this. Your feature set isn’t compelling enough for me to switch to MySQL when, as the original article states, Postgres basically does everything under the sun, well enough that I can keep my stack simple.

But if it allows me to use my existing Postgres tools, drivers, code, and knowledge then I’d consider it.

Right. We're betting there are a bunch of people like you, hence the investment.

Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major pain point.

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.

I’m currently learning the basics of this. Currently struggling with multiple similar scenarios where switching from a left to an inner join, or any equivalent, kills performance. But these are aggregation queries so there are only 5 records returned. I could just filter in my app code no problem. But why the hell does adding “where foo.id is not null” in SQL make it O(N*M)??? CTEs are not helping.

Have you checked with EXPLAIN ANALYZE VERBOSE?

Yup, used a nice EXPLAIN GUI tool as well to try and help.

Every time stuff like this comes up I wonder how much the people having issues would be willing to share - because every time I've fought with the postgres query planner, it eventually turned out what I wanted to do had massively worse performance* because of something I didn't take into account that postgres did. And each time, once I learned what that thing was, I was able to fix it the right way and get the query plan I was expecting, but also with the performance I was expecting.

* 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.

I always set this:

SET enable_seqscan = OFF;

If you hit the case I did, this will make your queries perform worse: The reason postgres insisted on table scan + sort was the random access time jumping around the disk and constantly invalidating the cache when it did an index scan. Using CLUSTER on the table made the index order match the table order so the index scan didn't jump around and there was no random access penalty - it instead worked with the disk cache.

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...

> CLUSTER

And here we see the benefit of clustered indices, á la MySQL. Assuming, of course, your PK is k-sortable.

It may use the same name, but reading https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.h... this doesn't really look like the same thing and wouldn't help here. InnoDB's clustered index (usually on the primary key) is used for fast row lookups by that primary key. It only has an advantage if the primary key is the order you want, but otherwise would have the exact same problem postgres's query planner was protecting me against, but without any way to fix the problem.

From the same page:

> 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.

If it does an index scan on the secondary index, unless it's an index-only scan it'll still have to jump around to different pages on the clustered index, so it can't really take advantage of the disk cache unless the whole thing is cached. Or if there's a high correlation between the secondary index's order and clustered index's order, which is what the postgres CLUSTER command does.

It shouldn’t. At worst, if id doesn’t have a usable index, a sequential scan of foo, so O(N).

I haven’t used Postgres but is this the issue you’re talking about? https://github.com/launchbadge/sqlx/pull/1539

No this is a fundamental concept in postgres. If you do EXPLAIN ANALYZE on a query, you get the query plan, which is influenced by the query, indexes, table structure, etc. But the QP may decide to do a silly thing like a sequential scan where a better path exists, and adding an index to avoid the scan would be cost prohibitive. So if you could just override the QP and say "Use this index and do this type of sort and then this type of scan, damn the consequences!" it would give the query writer full control. I don't understand why you can't just force the system to follow a path - you as the developer pay for it regardless.

I wonder if for non-trivial use cases we should just go back to imperative programming.

The way I see it traditional databases are frameworks, and we need to switch to something more like libraries - use the high-level interface when we need it, but be able to dig underneath. Postgres has taken some small steps in this direction with e.g. making the parser available as a separate library; some newer systems (e.g. distributed-first datastores that combine LevelDB with some higher-level layer) go further.

Oh that’s unfortunate, thanks for explaining it. Postgres has been in my list to check out but haven’t done any personal projects that I’d need it for… yet

Just make sure you test for your scale and with representative data and queries. There will be various tipping points with any technology. But you can find them with experimentation.

Nobody does actually. Postgres not having deterministic query plans is a big pain and a good reason not to use it. The same query may use different query plans depending on the estimated number of affected rows, very frustrating.

It's been many years since I've had to use an Oracle db, but they definitely allowed SQL developers to forcibly specify a query-plan - called "query hints", wherein you could specify it to prefer using (or avoiding) certain indexes, certain join-strategies (hash, loop, etc) - this was done via comments immediately before the SQL query - see https://docs.oracle.com/cd/B13789_01/server.101/b10752/hints...

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.

The query hints thing is actually quite useful in an educational context.

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.

I still find myself comparing it to oracle on these points.

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.

pg_hint_plan —— Give PostgreSQL ability to manually force some decisions in execution plans. https://github.com/ossc-db/pg_hint_plan

Cool, that looks super useful. Hadn't come across it before. :)

---

Just did a HN submission for it (https://news.ycombinator.com/item?id=39712211) now too.

MySQL offers this via use/force index (…). Similar problem where the QP will inexplicably decide one day to make a query really slow and you gotta override it.

We have a couple queries where using the correct index they'll take milliseconds, using the wrong index it'll take minutes - and mysql occasionally enough to be a noticeable load on the database chooses the wrong one and we've specified "USE INDEX" even though I really hate having to do so.

I'm curious about the query and schema that you're using

The problem is not the query planner per se. There is a much more subtle problem and it is related to how you have created the query in the join structure.

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.

Would love to see a practical example of this.

Another thing to consider is table fragmentation. Fragmentation > bad row count estimation > bad query plan.

We hit some weird query plans. I don't have forensic evidence but here's an example: https://crossref.gitlab.io/engineering/decision-records/dr-0...

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.

Usually that kind of problem is a result of exceeding {from,join}_collapse_limit, which defaults to 8. If you have more tables than that in a query, Postgres doesn’t exhaustively try all ordering to determine the best, and instead uses its genetic algorithm, which can be worse.

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.

IME, many query flips occur due to inadequate analysis, inadequate vacuuming, or table / index bloat.

Examining statistics for your tables / indices can be quite helpful in determining the issue.

Someone who picked their tools with good tech judgement 25 years ago can be using the same today (eg PG, Python, Linux) without corporate control of them, it's pretty great.

That feels a bit like hindsight talking. Linux perhaps, but were Python and Postgres really the obvious good judgement choices 25 years ago? Every other choice was poor judgement?

Well 25 years ago was pretty much (December 1998) when "LAMP"[1] was defined and that was originally Linux, Apache, MySQL and PHP.

So Postgres and Python were not the obvious choices back then.

[1] https://en.wikipedia.org/wiki/LAMP_(software_bundle)

In my personal experience of being around back then, postgres and python were still considered "technically better", but such a massive pain in the ass to install (especially on cheap shared hosting where it was often actually impossible to install) that only the most masochistic people would even try. I myself wrote a fastcgi implementation in PHP which would allow a web server which only supported php to call python under the hood and forward the inputs and outputs :P

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:

no other language has even attempted to compete with PHP

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.

IIRC the big break was betweem PHP 4 and PHP 5, they had very different object model. Otherwise the it's not that different.

It's amazing how that's not a terrible choice of tech stack even today after 26 years.

Not fashionable by any means - but still feasible to maintain unlike Windows IIS / C++ or Oracle / Perl / Flash.

Still, by 15 years ago Linux, Nginx, Postgres, Python had become a clearly better choice than the default LAMP stack for more complex applications.

That's how you airdrop straight into the worst of the python 2 3 fiasco, probably the worst time in history to start using python, meanwhile when Ruby on Rails and Java were probably the kings of that era.

Postgres really took off when Heroku became popular in the late 00s.

MySQL didn't have transactions (for many years after that), so PG over MySQL would have been the case for "good technical judgement" though maybe not the majority choice. For Python, maybe I'm biased - but you could have went for PHP in the argument if you swing that way and it still works.

Around 20 years ago when I was trying to decide on what database to use my requirement were that it should store data reliably. I learned that MySQL in contrast to PostgreSQL: 1. wasn't ACID 2. didn't have foreign key constraints 3. could loose/corrupt committed data (no WAL)

Despite me not knowing much about databases it seemed like an obvious choice.

Hell, 10 years ago (in a multi database project) I got bit by MySQL not supporting check constraints, but returning successfully when I ran the create check constraint statements.

Postgres has been great all around.

It finally supports them, which is nice. It doesn't support deferred checks to the commit, like postgres, but are otherwise good. I've appreciated using it to enforce json schema at the db level

Neither of those three would have been particularly good judgement 25 years ago, without a crystal ball.

25 years ago... PostgreSQL 6, Python 1.5, Linux 2.2? I don't know if picking those tools was good judgement then...

Why not?

Linux is absolutely corporate controlled, sadly. Just look at how decisions like systemd and wayland get made.

Postgres is such a great tool.

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.

Materialized views with pg_cron to refresh them? (Or even just cron and your usual interface, if you don't want to install something extra)

This solves the same problem, just not as well, the idea of an incrementaly maintained view is that only the update needs to be computed, so a count will increment or decrement as rows are inserted or deleted.

It means complex views that could take minutes or hours to calculate from scratch can be kept fresh in realtime.

I’ve built a few of these in snowflake for time series data (with dbt, which is fine but I don’t love it). My feeling so far is that the incremental bits are kinda fiddly and often domain-specific. Is it possible to define a single “shape” that would solve >90% of these incremental refresh scenarios?

https://github.com/sraoss/pg_ivm

https://github.com/sraoss/pg_ivm does what you need

pg_ivm has a ton of restrictions though (as do other PG incremental view refresh mechanisms, like timescale hypertables).

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.

Did you used this in production, how do you rate it?

Is this feature even possible? How can the database unravel a complex queries with derived data to minimal updates from its normalized parts?

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.

There are lots of projects who have managed to achieve this.

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.

I'm not familiar with ClickHouse materialized views, but the other tech you list (as I roughly understand them) seem more concerned with streaming SQL, which is a related but different end user experience from incrementally refreshed materialized views.

I believe differential data flow techniques (Materialize, Noria) enable this kind of stuff

An "incremental view" is just an index over some custom query. So you're pretty much asking for improvements in Postgres' index support.

He means a materialized view, I believe.

MERGE was interested in 15, if that's helpful: https://www.postgresql.org/docs/current/sql-merge.html

In the same vein as “is your product a business, or is it just a feature”, Postgres has really raised the bar to “is your product a database or an index in postgres”. There’s a few databases that make compelling cases for their existence, like Cassandra or Elastic/Solr, but surprisingly many databases really don’t offer anything that can’t be replicated with a GIN or GIST on Postgres. It is the amorphous blob swallowing up your product and turning it into a feature. JSON handling or json column types, are no longer a distinctive feature anymore, for example.

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… ;)

Postgres is still single-node-first, and while Citus exists I'm skeptical that it can ever become as easy to administer as a true HA-first datastore. For me the reason to use something like Cassandra or Kafka was never "big data" per se, it was having true master-master fault tolerance out of the box in a way that worked with everything.

That looks like apples vs oranges.

Patroni has native HA support for citus horizontal cluster since v3. Which means your can create a HA citus cluster as simple as: https://pigsty.io/docs/pgsql/config/#citus-cluster

> Postgres… Kafka… Cassandra

These are all wildly different products that should not be considered for the same purposes.

If your premise is that Postgres is eating the datastore world, then you're talking about using it as a replacement for Kafka and Cassandra.

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.

But that's the gist of the article here, right? That Postgres is taking over all db-like use cases. It doesn't claim that it can replace Kafka but https://www.amazingcto.com/postgres-for-everything/ certainly does. Of course it's not a full replacement, but it might be good enough.

If you are going to multi-node Postgres, you need to start planning for Cassandra/Dynamo.

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.

You should look at CocroachDB: it tries to be PG compatible (not there yet), but true HA-first.

At often 10x the latency and significant query compatibility issues. That's a hard pill to swallow

latency increase is likely payment for distributed consistency regardless of specific DB: doing consensus between nodes is much slower than dumping block of data on local nvme.

>In 2024, a single x86 machine can have 512 cores

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.

>This year’s new EPYC 9754 goes even further, offering a single CPU with 128 cores and 256 threads. This means a standard dual-socket server could have an astonishing 512 cores!

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.

I think the terminology is a bit muddied here because AMD has historically referred to physical cores as "modules" and logical cores as "cores" (although their current spec sheet [1] seems to use "cores" and "threads" in the way that most understand them).

So in a dual-socket setup, 2 x EPYC 9754 would indeed yield 512 threads (logical cores), which are backed by 256 physical cores.

[1] https://www.amd.com/en/products/cpu/amd-epyc-9754

Did a quick search and found this 480-core server. Not exactly 512, but not far off. https://lenovopress.lenovo.com/lp1729-thinksystem-sr950-v3-s...

Alright I'm convinced, I'm using postgres for my next project. Anyone have any experiences with it and Entity Framework Core?

Well supported in EF core, great experience. Having said that, I’ve not used EF core with any other DB so have nothing to compare to.

Npgsql has been good for me, although each major release there are a few teething issues.

I am using it in a hobby code first project. Had no issues with it, but my data model is admittedly not complex and the the data is small in size.

Coming from the MS ecosystem i can definitely vouch for EF core, but I am more of a Dapper person myself.

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'm using EF Core with SQL Server and PostgreSQL for two different production projects; it works like a charm, and the performance is great.

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.

I have used it in multiple projects and it works great. The Npgsql.EntityFrameworkCore.PostgreSQL provider is always quickly updated when new versions of EF Core are shipped, and its primary maintainer @roji is also extremely talented and responsive. All around has been a pleasure to work with!

I'm managing two medium sized projects on that stack, using both EF core and Dapper. I'm about to switch a third over from Oracle because I'm so tired of all the issues with Oracle.

>As DuckDB’s manifesto “Big Data is Dead” suggests, the era of big data is over.

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.

I'm not sure what your point is here, it seems like you are just listing off announced hardware.

The threshold for Cassandra / Dynamo scaling is increasing is probably the only point. "Big data is dead" is pretty stupid to say, typical clickbait marketing by a database that will probably be chucked away by something else trendy in another year.

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)

The point is Big Data or Hard to Scale aren't as much of a thing with Hardware technology moving forward.

> As DuckDB’s manifesto “Big Data is Dead” suggests, the era of big data is over.

yet, their db can't handle many cases where data doesn't fit into memory, and PgSQL always does large writes in single thread..

I have a handful of sites I run on a VPS with a basic setup, including MySQL.

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.

The price is right and auto-vacuuming is a thing.

Its got a lot better over the years.

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.

> PG is great to admin

What do you use for it? Is there anything like phpmyadmin for postgres with similar simplicity?

Pretty much the same as mysql. https://wiki.postgresql.org/wiki/PostgreSQL_Clients#Web_Clie...

Coming myself from MySQL to Postgres I found PgAdmin (https://www.pgadmin.org/screenshots/#7) easy to use

I guess it's better now. Last time I tried years ago it was horrible

Pgadmin has gotten lots of bad feedback. I'm using dBeaver or the IntelliJ sidebar to connect to the database.

Dbeaver is more useful. But you can do everything from the command line. You use sql to write database migrations anyway, no?

Postgres updates are definitely a pain. MySQL is usually just a matter of upgrading the package and restarting the server for the projects I run, but postgres is a full dump and import process.

I manage many PostgreSQL databases, the only time it was a pain, it was due to PostGIS upgrade but not the postgresql cluster itself…

You don’t need to dump and re import the database since a long long time…

Great article! We also tried lots of databases in the past at SWCode, but then ended up using Postgres for almost all our usecases. There really must be a good argument for using something else which can‘t be done with some Postgres extension.

Yes, the title is click-baity. Yes, Postgres isn't perfect and not the "best" choice for every possible use case in the universe.

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.

I would like to keep my data in Postgres (OLTP purposes), but run analytical queries against the same datafiles in Snowflake/DuckDB fashion.

The analytics part should scale independently. Often this is only needed occasionally, so scale-to-zero (like Snowflake) would be great.

You have been able to do that with DuckDB for 18 months. https://duckdb.org/2022/09/30/postgres-scanner.html

Clickhouse supports it too https://clickhouse.com/docs/en/sql-reference/table-functions...

Is there an extension that can make it compete with TigerBeetle for transaction processing speed?

Why downvoted? Genuine question.

That would surely be impossible. TB is designed from the ground up to do one thing and do that one thing well. Postgres is a swiss army knife, and TB is a knife.

Yes I think this is the one area where specialized DBs will win over PG

This post was very wrong and misleading on multiple points.

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 about: PostgreSQL tuned (x47). PostgreSQL + Hydra Extension (x42) PostgreSQL + ParadeDB Extension (x10.7)

Having used Postgres for many projects, yet never having used any of the other tools in the ecosystem, I'm surprised by how many tools there are!

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?

I truly like postgres

It's not a best practice, it's a fad. 99% of people who recommend or use Postgres barely know how to use it. Another trendy database will come along and you'll stop seeing all these posts about it. Happens every decade. I'll link back to this post in a few years with "I told you so".

It’s been 40 years for Postgres. A database is not some trendy ReactJS library.

Correct, but a lot of pieple treat it that way.

You're off by 30 years already...

Another trendy database will come along and you'll stop seeing all these posts about it. Happens every decade.

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.

> 99% of people who recommend or use Postgres barely know how to use it.

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.

I feel this on a soul level. I wrote about it: https://renegadeotter.com/2023/11/12/your-database-skills-ar...

that was a profound article. thanks

My god, are you me? I also thoroughly enjoyed this diatribe from [0]:

> 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...

Reply on news.ycombinator.com