I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player.
These days, I do myself a favor and always avoid Oracle and MySql/MariaDB.
Postgres is amazing, and the two big things I wished it had:
1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection.
2. Synchronously updated materialized views (Sql Server calls them indexed views). These are incredible tools in complex data situations. I saw a project struggle with complex technical implementations that would be elegant, trivial and always correct with indexed views.
Sql Server can be costly, but in many cases the benefits it provides are totally worth the cost.
Choosing the data store carefully prevents lots of future trouble.
SQLite and MSSQL are my two solutions for relational storage problems.
If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass.
If I am going to pay any amount of money at all, I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages.
I wouldn't ever advocate for standing up a new Oracle or DB2 machine, but if one was already in place I'd probably die on the hill of not trying to refactor it away. These databases typically come with multi-volume ghost stories attached. Reinventing all those weird effects on a new engine will typically kill the business if there are no other options available.
>> The developer experience around MSSQL is untouchable.
This may be the case for MS-centric, application & human developers, but I'm not convinced moving forward. Microsoft's BI story is pretty thin and out of date. Postgres has some solid columnar support/functions (which probably why Snowflake is writing about it) which means you can potentially use it for both you transactional and analytical workflows. As more development shifts to agentic workflows I'd bet Postgres shines when the overall ecosystem is more important than the human tools that were essential for the past 20 years. I loved Redgate's value-add but I don't think agents care about the UI which was the big win. SQL Server will continue to live in the enterprise andf where MS can sell lucrative support contracts or build for their clients, but I'm not seeing any net-new projects where the builders have any choice to not use SQL Server.
>I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages.
Try Postgresql. I was previously SQL Server and the move has been great.
SSMS doesn't offer much over alternatives, both PGAdmin and others. VS is dying, VSCode is the future.
Redgate doesn't offer anything that is essential or not available elsewhere for postgresql
The money you spend on Microsoft licenses could be put towards more ram on the server.
One of the biggest advantages is that a lot of people in the business are already comfortable with the ecosystem. I know that from the perspective of HN that SSMS, pgAdmin, DBeaver, DB Browser for SQLite, et. al. are mostly isomorphic, but from the perspective of everyone else in the business, these are substantially different things to think about.
Whether the popularity of the MS ecosystem is good or not is a separate problem. If we are solving for "make the business go well and I get paid more", the strategy is usually obvious. We can still advocate for OSS and not-so-many-eggs in the Microsoft basket while we get paid for using these technologies. Again, SQLite is the preferred engine in my tool belt. I don't want to have to manage a hosted sql machine. But, sometimes the problem absolutely insists upon it.
Also, if you are using C#/.NET, integrating with MSSQL is always a little bit easier than the other providers. SQLite (and some others) have lackluster types for things like time. MSSQL has DateTime2 and DateTimeOffset that map exactly into the CLR types.
Oracle = Pain, Suffering, High Costs, Litigation, and Human Misery. If it wasn't for non-technical middle management that likes the perks of buying high cost software from vendors that throw nice parties they'd be out of business.
Postgresql is the better product, but doesn't have the horizontal scaling of MySQL/Maria though, so if you want an easy to setup cluster MySQL for high volume online retail store or similar has a use case still.
1. "materialize" the view as a full table, then index that. Any reasonable pipeline/ETL tool can provide incremental updates between tables. Obviously, anything materialized requires considerations around storage, replication, backup/restore, I/O, etc.
2. use a regular VIEW and index (precisely) the underlying expressions mentioned in the view, i.e. so when the view is used, then the indexes get used.
Both require rewriting SQL, though I've used VIEWs to make the change transparent.
> lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection.
Pg connections are definitely heavy, but usually on resources other than memory in my experience. If you configure reasonable dirty reclamation and recycling, the memory numbers are often overstated due to Linux tools’ deceptive fork accounting and shared buffers. Ofc, if you’re averaging lots of heavy queries per connection it’ll be truly heavy, but many times the numbers overstate the impact.
Care to share some examples where SQL Server's indexed views would shine?
In my eyes they're similar to triggers, which incur a high performance overhead in OLTP systems and are shunned by developers. In OLAP systems custom ETL code will likely outperform them.
In OLTP, it's very difficult to guarantee correctness with triggers (very easy to have a race condition in concurrent environment). On a flip side, materialized views tend to lock more than you'd expect, especially when aggregates are involved.
The sweet spot is if you have a read-mostly database and use SNAPSHOT transaction isolation for the readers (which is SQL Server's implementation of MVCC). That way, writers may still block writers, but writers can never block readers, even when indexed views are being maintained.
Another neat trick is to "abuse" indexed views as multi-table CHECKs. The idea is to make a JOIN that would produce duplicated rows (and fail the indexed view's key) if some multi-table condition is not met.
Indexed views are much faster than trying to achieve the same result with triggers. Triggers have serious concurrency limitations, and you do recalculations even when the fields you depend on are not touched.
Indexed views are not much worse than indexes. Of course, when they refer to other tables there are underlying data lookups, but in our experience when we moved from triggers to indexed views, large scale data ingestion went way faster.
Where we used it: While revamping a large scale sales program, we stored the warehouse in/out in one table, and several things like current stock were calculated using indexed views.
Bonus: Using Snapshot concurrency control, you can do many things concurrently, and only when they both updates to a certain product in the same store you'll get the second transaction failing (which could be retried on the backend).
The fact that they are completely in-sync with your data is amazing.
PG has had incremental view maintenance on the horizon for many years. I expect it to remain on the horizon for a long time.
What you're describing is amazing, and I wish I had it available to us. We've hand rolled far too many triggers to achieve the same thing, with all the expected problems you'd assume. I'm sure it could be abused/misused, but a batteries-included approach like that would be huge.
These things exist to eliminate the risk of ever serving stale information from a materialised view. I.e., their benefit is political/reputational as much as they are technical in the sense that they save you effort like remembering to invalidate a MV after an ingest operation.
Stale MV is a thing you only ever burn your fingers on once. Like how "It's not DNS" is a common meme in networking.
I am currently fighting my way off SQL Server towards PostgreSQL.
Windows Server is a real pain to operate and the SQL Server ecosystem expects you to run a lot of add-ons on the server alongside your database. Those don’t translate to managed database services, so you lose a lot of functionality if you jump to RDS or similar.
The first party tools are also aging poorly. SSIS and SSRS are not fun. SSMS is ok for what it is but can’t compete with the ecosystem around PostgreSQL.
Maybe I’m missing something but I can’t wait to ditch it.
Agree about Windows Server. You can run SqlServer on Linux though. I'm not aware of your specific addons, but the Sql Server itself works perfectly well on Linux.
Don't know of anything wrong with MariaDB, but there used to be plenty wrong with MySQL. To give the most egregious example (THANKFULLY fixed in MariaDB, but was present in MySQL for the longest time), inserting the value 128 into a TINYINT column (signed 8-bit int) would clamp the value rather than returning an error. Which might be what you want... except if that was a primary key column. Marvel at the following, which used to be how MySQL behaved:
CREATE DATABASE foo;
USE foo;
CREATE TABLE one ( id TINYINT NOT NULL PRIMARY KEY ) TYPE=InnoDB ;
CREATE TABLE two (
id TINYINT NOT NULL PRIMARY KEY,
INDEX (id),
CONSTRAINT id_fkey FOREIGN KEY (id) REFERENCES one(id)
) TYPE=InnoDB ;
Now that we've created both tables, let's insert a record into table one:
INSERT INTO one VALUES (127);
And now let's insert a record with a different primary key into table two:
INSERT INTO two VALUES (128);
MariaDB will give you an error at this point (ERROR 1264 (22003): Out of range value for column 'id' at row 1), but MySQL (at least back when I tried this about ten years ago, which was the last time I was forced to work with MySQL — and I am so glad I never have to go back!) would return no error message and just say "Query OK, 1 row affected (0.009 sec)".
Now let's select the value we inserted into table "two":
SELECT * FROM two;
And what do we see? The value 127, even though we inserted 128. Which has created a foreign-key relationship to table "one" that we never intended to put in there.
There are other reasons why MySQL was inadequate, but I no longer remember them. Probably MariaDB has fixed them by now. But I no longer have to use MySQL/MariaDB for anything, and I never want to go back. I have a VERY strong averse reaction, caused by past pain, when I think of using MariaDB. (I actually spun up a virtual machine to test what I wrote here, because there's no way I was going to install MariaDB on my primary work machine).
4. Many ways to lock out instant add table, meaning you can’t add a column without a full table write, which can lock the table for minutes at a time in even moderately sized tables.
5. Dealing with legacy mysql databases often means dealing with utf8mb3, which used to be the default utf8 data type despite not storing all utf8
6. Dealing with all but the most recent mysql databases means dealing with non strict mode which means your NOT NULL column won’t require a value.
And although you didn't ask, I'll list what's wrong with Oracle. It's very simple.
Oracle treats empty strings as being NULL.
Anyone who's never used Oracle before in their life is probably wondering if I'm making it up. I'm not. In Oracle, inserting '' in a VARCHAR column is exactly the same as inserting NULL. And if there's a NOT NULL column, you're not allowed to store the empty string in there.
Which means that in Oracle, you do not have any way of distinguishing "I don't know the person's middle name" vs. "I know what the person's middle name is: he/she doesn't have one".
There are apparently historical reasons for this, but I don't care. The empty string is NOT the same as NULL, and any software that treats them as the same IS BUGGY!
Sorry. Had to get that off my chest. I know I'm answering a question you didn't ask, but that has been bothering me for nearly 25 years (I first learned about this misfeature of Oracle's in 2002 or 2003), and I just had to vent to somebody who would understand.
Just a set of things too minor to move off of it but annoying enough to not want to start with it.
My list:
No `explain (analyze,buffers)`. Instant DDL has some warts (e.g. fk, metadata locks). Query planning bugs (actually... query planning in general is disappointing). Exiting the repl doesn't stop queries. Implicit type casting. Replication lag from large DDL (e.g. creating an index). Lack of two phase DDL (creating constraints NOT VALID and then VALIDATE later). Lack of extensions (e.g. pg_vector). No safe access to inspect buffer cache. AWS Aurora seems to only add shiny new things to Postgres. And more.
Again, none of this is quite enough to migrate off of it for an established system, but certainly enough to avoid it on a new project.
Speaking as long-term (>15 years) user of Postgres in science, I am getting worried about the lack of columnar type of storage in Postgresql. As the datasets become bigger and bigger, the limitations of PG's storage are becoming more and more significant. I know there are various extensions (i.e. cetus) that may offer such functionality, but then you depend on that extension being supported in the future, as well additional complexity.
> You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category.
I don't want a columnar database, I have a few tables which would be excellent fits for columnar storage. And the relational model doesn't say anything about the way data is stored.
Postgres's whole origin story is basically to think outside the box and don't be constrained by existing thinking. Stonebraker thought existing databases were too limited in terms of their data types and expressiveness. He started Postgres as an evolution of Ingres (Postgres = Post Ingres) that added rich custom data types and a rewriting system based on rules.
Columnar and all the other fun stuff (JSON, GIS, inverted indexes, embedding vectors) is a natural progression of that thinking. With TimescaleDB, Hydra, Citus, pg_mooncake, etc. becoming very popular the last few years, there is a clear demand for an integrated experience.
(Stonebraker also thought one database shouldn't do everything, as described in his early 2000s "One Size Does Not Fit All" paper, and Stonebraker branched out into HStore/Vertica for columnar. In hindsight, I think that was appropriate for the time, but no longer a significant concern.)
PostgreSQL was a right tool for my task for many years. It is a question for PostgreSQL can adapt to a new reality of much bigger datasets or I have to switch to a new tool. And I am not the only user of Postgresql in this context. So it is easy to say in vacuum 'you are using the wrong database', but it's not something that can be easily changed with 100s of Tb of data, existing user workflows etc.
Columnar vs row based is a large architectural distinction. In theory it'd be great to support both, but this has also been an elusive and complex problem to solve. You're not going to see a simple "columnar type" on a row based system like this any time soon, that's trivializing the situation.
Andy Pavlo teaches about this and has done real work in this space, and certainly considers it to be non-trivial. His YouTube channel is spicy and worth looking into if you want to dive deep on the topic.
"Not something that can be easily changed" is not a valid rebuttal to "the tool no longer supports how the task I am working on has evolved over many years". This isn't 'saying in a vacuum'; 'has this tool ceased to be the right answer and how do I move to a better solution' is an exercise responsible app owners should engage in all the time. Shouldn't be different than things I presume your job touches on: if your research evolves to need to see in radio waves, do you complain the optical telescope you started with back in the day doesn't see radio waves and expect someone else to make that happen, or do you find a radio telescope?
You can either 1) accept the limits of the tool, at least until it becomes a priority for the tool developer to add whatever feature you want, 2) pay someone to develop the features you want, or 3) buck up and do what vast numbers of ops folks have done before you and move to something that does what you need.
Age old product question. The Honda Civic is much larger today than the original, because the original targeted young people on a budget. As they aged and had kids, they needed more room in the car.
Most peoples’ insinct is to buy a newer version of the car they love, so the civic grew to accommodate the aging market.
No real point here other than an observation about how the installed base’s needs change, across industries.
Just because it's easy to say doesn't mean it's wrong.
By the same logic, you could say Microsoft Access should have all the capabilities of Postgres because it's painful for small businesses to move off of it when it's no longer a good fit for their needs.
The thing is that PG had introduced pluggable storage engines exactly for the reason I am talking about, and there have been few implementations of columnar storage using PG functionality, it's just they always stayed out of the tree.
So I wasn't talking about some functionality that is completely out of scope.
But I agree in the end I may be forced to move elsewhere...
I mean I don't disagree with you, but they did just add a graph database feature, which is about as orthogonal to relational database design as you can get.
I don't agree, a relational database is not required to have a particular storage engine or format, that is an implementation detail.
There are other relational databases that have both kinds of storage engines and some use both on the same table (row based insert with column based migration and secondary column store indexes: https://learn.microsoft.com/en-us/sql/relational-databases/i...
Just like you can have b-tree based table storage vs heap in the case of index organized tables / clustered indexes (which pg doesn't have) you can choose column based instead the logical data model is still the same relational model.
From a computer science perspective, I'm not exactly sure how a transactional database would implement a columnar type. Postgres + CDC + an actual analytical database like ClickHouse would be your strongest bet at scale.
A bit of a shameless plug, but I've been working on this for a few months in the form of an extension: https://github.com/xataio/deltax
When I started I thought there's too much inherent overhead in using Postgres tables for storage and using the Postgres executor, so figured it would be pretty cool to match Timescale in performance. I didn't think it would be possible to get close to dedicated analytical DBs. But as the project progressed and the performance got better and better, I'm now firmly in the camp of doing analytics with Postgres + an extension.
I think the better way is to use Postgresql for new data and routinely archive off older data to data warehouse type database, to keep the Postgres one small.
(Many companies also now use a RDBMS alongside either a KV database or document store in main app)
The first was using SQL Server alongside CouchDB, an older company. CRUD Data into SQL, documents into Couch. (used separately, document Url in Couch stored in SQL and brought in by front end)
The second, startup with funding, was using Azure SQL Server (and complaining of the cost) and using Azure Cosmo (Key Value NoSQL) alongside it. Same relationship, no link between the two, it's the front end that drew data from both.
Just look up the pro and cons of each database, some are insert only, some are distributed, some are faster read then write etc. LLM will give you a quick comparison.
Wow. Incredible how this was not mentioned in the OP. I had done it with tcn triggers and adding "_archive" shadown tables manually with tcn (https://www.postgresql.org/docs/current/tcn.html), but doing it natively is gonna be, as per most postgresql implementations, wonderful.
i like the COPY and logical replication improvements. Currently I back up my PG database with a sidecar Databasus instance that is heavier than my entire backend + DB + Caddy!
(LLM writing rant below)
---
> That alone tells you something: Users had a real need, and the ecosystem filled the gap.
> This sounds straightforward, but it solves a real operational problem.
> None of these change the world. All of them make day-to-day data workflows better.
> The easy thing to do here is list planner changes and call it done. But the more useful takeaway is this: Postgres keeps getting better at recognizing the shape of common queries and doing less unnecessary work.
> [Proceed to list planner changes]
If Orwell were alive today, he might declare himself illiterate in English and learn Klingon just to avoid having to read these.
The graph database feature looks interesting, but I wonder...
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders]->(o IS orders WHERE o.ordered_when = current_date) COLUMNS (c.name AS customer_name));
That is _awful_ syntax; it is reminiscent of neo4j, which is surely not a tool anyone serious should copy from outright in 2026.
And of course the final thing I am left wondering is if it's fast. Row-level security is such a useful feature and yet only a fool would contemplate building anything serious with Postgres', as the planner goes haywire and does per-row-matching, nuking performance.
It's great to see GROUP BY ALL introduced which, AFAIK, was a concept introduced by DuckDB.
> Several of these features were first introduced by DuckDB, while some are inspired by other systems. Many of the features originally introduced by DuckDB (e.g., GROUP BY ALL) have been since adapted by other systems.
I can't decide whether this person writes in the type of style that was apparently overrepresented in LLM training, or whether they heavily used AI to spruce up their writing. I'm learning towards the latter.
Spruce up is unreasonably charitable. I'm more irritated that the authorship information is misleading. craig-kerstiens is not available on Huggingface, and yet not a single sentence in this article seems to have been typed on a keyboard.
When Claude writes things like "as someone who has spent a lot of time doing X", I think this is also a kind of failure of alignment. LLMs shouldn't write as if they had personal experience. It's something a person might say in the training data, but I just think LLMs shouldn't claim life experience they don't have, even if that's a statistically likely sequence of tokens.
These low effort constant comments about style or formatting are against Hackernews guidelines for discussions and something needs to be done to clean up the comment section. Getting to a ridiculous point
If comments about how "this blog post could have been a link to this github repo" are in-bounds, so are comments about how "this could have been a link to a LLM session." HN has always tried to work out if a submission is novel and interesting work or is just a slick coat of paint on mundane work (sometimes if good work is obscured by insufficiently clever presentation). Highlighting that content was generated by an LLM and asking if that impacts how to understand it is entirely in keeping with our culture and standards.
They aren't actually. They make me feel like I'm not going crazy - that I'm not the only person noticing that the quality of the average article on hacker news has dropped off a cliff in the last 6 months. Links from different people with different cultures, life experiences, and languages have the same tone of voice, the same sentence structure, and the same breathless, boring, staccato yet arrhythmic, emotive yet soulless style.
I hope everyone keeps pointing it out. Even better, change the site guidelines to make AI generated articles a flaggable offense. It's already been done for comments.
It has extremely low false positive rate in various tests (e. g. https://arxiv.org/html/2501.15654v1), I've never seen it mark something I knew was human written as AI.
I get it's in vogue to take stabs at whether or not AI was used. But I think the more useful approach is to instead be critical of the end product, if you have criticisms of it.
This actually gave me an amusing idea: a book review club that strictly reviews the cover art, book binding, hand feel, paper weight, font, etc. of the book.
I'm wondering. Just wondering? Will they ever support multiple storage engines like MariaDB? Having a storage engine that support OLTP or OLAP or append-only would be cool. I totally understand if they don't want to do that.
I'm dreaming of block compression in Postgresql, instead of only row compression, too limited to be effective.
I know you can store your data on a Zfs pool with block compression, but having it native would remove the burdain of setting this up and maybe better perf.
Interesting, I was wondering why Snowflake was investing in PostgreSQL. Looks like Snowflake bought Crunchy Data and Databricks bought NEON… so the two leading DWaaS companies have managed PostgreSQL offerings now.
I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player.
These days, I do myself a favor and always avoid Oracle and MySql/MariaDB.
Postgres is amazing, and the two big things I wished it had:
1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection.
2. Synchronously updated materialized views (Sql Server calls them indexed views). These are incredible tools in complex data situations. I saw a project struggle with complex technical implementations that would be elegant, trivial and always correct with indexed views.
Sql Server can be costly, but in many cases the benefits it provides are totally worth the cost.
Choosing the data store carefully prevents lots of future trouble.
[delayed]
SQLite and MSSQL are my two solutions for relational storage problems.
If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass.
If I am going to pay any amount of money at all, I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages.
I wouldn't ever advocate for standing up a new Oracle or DB2 machine, but if one was already in place I'd probably die on the hill of not trying to refactor it away. These databases typically come with multi-volume ghost stories attached. Reinventing all those weird effects on a new engine will typically kill the business if there are no other options available.
>> The developer experience around MSSQL is untouchable.
This may be the case for MS-centric, application & human developers, but I'm not convinced moving forward. Microsoft's BI story is pretty thin and out of date. Postgres has some solid columnar support/functions (which probably why Snowflake is writing about it) which means you can potentially use it for both you transactional and analytical workflows. As more development shifts to agentic workflows I'd bet Postgres shines when the overall ecosystem is more important than the human tools that were essential for the past 20 years. I loved Redgate's value-add but I don't think agents care about the UI which was the big win. SQL Server will continue to live in the enterprise andf where MS can sell lucrative support contracts or build for their clients, but I'm not seeing any net-new projects where the builders have any choice to not use SQL Server.
>I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages.
Try Postgresql. I was previously SQL Server and the move has been great.
SSMS doesn't offer much over alternatives, both PGAdmin and others. VS is dying, VSCode is the future.
Redgate doesn't offer anything that is essential or not available elsewhere for postgresql
The money you spend on Microsoft licenses could be put towards more ram on the server.
Can you expand on what is better with MSSQL?
One of the biggest advantages is that a lot of people in the business are already comfortable with the ecosystem. I know that from the perspective of HN that SSMS, pgAdmin, DBeaver, DB Browser for SQLite, et. al. are mostly isomorphic, but from the perspective of everyone else in the business, these are substantially different things to think about.
Whether the popularity of the MS ecosystem is good or not is a separate problem. If we are solving for "make the business go well and I get paid more", the strategy is usually obvious. We can still advocate for OSS and not-so-many-eggs in the Microsoft basket while we get paid for using these technologies. Again, SQLite is the preferred engine in my tool belt. I don't want to have to manage a hosted sql machine. But, sometimes the problem absolutely insists upon it.
Also, if you are using C#/.NET, integrating with MSSQL is always a little bit easier than the other providers. SQLite (and some others) have lackluster types for things like time. MSSQL has DateTime2 and DateTimeOffset that map exactly into the CLR types.
Litestream solves the backup issue. I use it and it’s great.
Oracle = Pain, Suffering, High Costs, Litigation, and Human Misery. If it wasn't for non-technical middle management that likes the perks of buying high cost software from vendors that throw nice parties they'd be out of business.
Postgresql is the better product, but doesn't have the horizontal scaling of MySQL/Maria though, so if you want an easy to setup cluster MySQL for high volume online retail store or similar has a use case still.
two techniques I use with pg:
1. "materialize" the view as a full table, then index that. Any reasonable pipeline/ETL tool can provide incremental updates between tables. Obviously, anything materialized requires considerations around storage, replication, backup/restore, I/O, etc.
2. use a regular VIEW and index (precisely) the underlying expressions mentioned in the view, i.e. so when the view is used, then the indexes get used.
Both require rewriting SQL, though I've used VIEWs to make the change transparent.
> lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection.
Pg connections are definitely heavy, but usually on resources other than memory in my experience. If you configure reasonable dirty reclamation and recycling, the memory numbers are often overstated due to Linux tools’ deceptive fork accounting and shared buffers. Ofc, if you’re averaging lots of heavy queries per connection it’ll be truly heavy, but many times the numbers overstate the impact.
Care to share some examples where SQL Server's indexed views would shine?
In my eyes they're similar to triggers, which incur a high performance overhead in OLTP systems and are shunned by developers. In OLAP systems custom ETL code will likely outperform them.
In OLTP, it's very difficult to guarantee correctness with triggers (very easy to have a race condition in concurrent environment). On a flip side, materialized views tend to lock more than you'd expect, especially when aggregates are involved.
The sweet spot is if you have a read-mostly database and use SNAPSHOT transaction isolation for the readers (which is SQL Server's implementation of MVCC). That way, writers may still block writers, but writers can never block readers, even when indexed views are being maintained.
Another neat trick is to "abuse" indexed views as multi-table CHECKs. The idea is to make a JOIN that would produce duplicated rows (and fail the indexed view's key) if some multi-table condition is not met.
Indexed views are much faster than trying to achieve the same result with triggers. Triggers have serious concurrency limitations, and you do recalculations even when the fields you depend on are not touched.
Indexed views are not much worse than indexes. Of course, when they refer to other tables there are underlying data lookups, but in our experience when we moved from triggers to indexed views, large scale data ingestion went way faster.
Where we used it: While revamping a large scale sales program, we stored the warehouse in/out in one table, and several things like current stock were calculated using indexed views.
Bonus: Using Snapshot concurrency control, you can do many things concurrently, and only when they both updates to a certain product in the same store you'll get the second transaction failing (which could be retried on the backend).
The fact that they are completely in-sync with your data is amazing.
PG has had incremental view maintenance on the horizon for many years. I expect it to remain on the horizon for a long time.
What you're describing is amazing, and I wish I had it available to us. We've hand rolled far too many triggers to achieve the same thing, with all the expected problems you'd assume. I'm sure it could be abused/misused, but a batteries-included approach like that would be huge.
These things exist to eliminate the risk of ever serving stale information from a materialised view. I.e., their benefit is political/reputational as much as they are technical in the sense that they save you effort like remembering to invalidate a MV after an ingest operation.
Stale MV is a thing you only ever burn your fingers on once. Like how "It's not DNS" is a common meme in networking.
I am currently fighting my way off SQL Server towards PostgreSQL.
Windows Server is a real pain to operate and the SQL Server ecosystem expects you to run a lot of add-ons on the server alongside your database. Those don’t translate to managed database services, so you lose a lot of functionality if you jump to RDS or similar.
The first party tools are also aging poorly. SSIS and SSRS are not fun. SSMS is ok for what it is but can’t compete with the ecosystem around PostgreSQL.
Maybe I’m missing something but I can’t wait to ditch it.
What are some of the add-ons that you run on the server? We run ours in a pretty bare-bones manner so I'm interested to hear what you're doing.
The linux version runs very cleanly, too.
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux...
SSMS has been rebuilt as a framework within Visual Studio, now. It includes Copilot AI and a bunch of other niceties.
Agree about Windows Server. You can run SqlServer on Linux though. I'm not aware of your specific addons, but the Sql Server itself works perfectly well on Linux.
>I do myself a favor and always avoid Oracle and MySql/MariaDB.
So what's wrong with MySQL or MariaDB?
Don't know of anything wrong with MariaDB, but there used to be plenty wrong with MySQL. To give the most egregious example (THANKFULLY fixed in MariaDB, but was present in MySQL for the longest time), inserting the value 128 into a TINYINT column (signed 8-bit int) would clamp the value rather than returning an error. Which might be what you want... except if that was a primary key column. Marvel at the following, which used to be how MySQL behaved:
Note: the below taken nearly verbatim from https://sql-info.de/mysql/referential-integrity.html#3_5
Now that we've created both tables, let's insert a record into table one: And now let's insert a record with a different primary key into table two: MariaDB will give you an error at this point (ERROR 1264 (22003): Out of range value for column 'id' at row 1), but MySQL (at least back when I tried this about ten years ago, which was the last time I was forced to work with MySQL — and I am so glad I never have to go back!) would return no error message and just say "Query OK, 1 row affected (0.009 sec)".Now let's select the value we inserted into table "two":
And what do we see? The value 127, even though we inserted 128. Which has created a foreign-key relationship to table "one" that we never intended to put in there.There are other reasons why MySQL was inadequate, but I no longer remember them. Probably MariaDB has fixed them by now. But I no longer have to use MySQL/MariaDB for anything, and I never want to go back. I have a VERY strong averse reaction, caused by past pain, when I think of using MariaDB. (I actually spun up a virtual machine to test what I wrote here, because there's no way I was going to install MariaDB on my primary work machine).
1. No transactional DDL
2. No MERGE statement
3. No partial indices
4. Many ways to lock out instant add table, meaning you can’t add a column without a full table write, which can lock the table for minutes at a time in even moderately sized tables.
5. Dealing with legacy mysql databases often means dealing with utf8mb3, which used to be the default utf8 data type despite not storing all utf8
6. Dealing with all but the most recent mysql databases means dealing with non strict mode which means your NOT NULL column won’t require a value.
And although you didn't ask, I'll list what's wrong with Oracle. It's very simple.
Oracle treats empty strings as being NULL.
Anyone who's never used Oracle before in their life is probably wondering if I'm making it up. I'm not. In Oracle, inserting '' in a VARCHAR column is exactly the same as inserting NULL. And if there's a NOT NULL column, you're not allowed to store the empty string in there.
Which means that in Oracle, you do not have any way of distinguishing "I don't know the person's middle name" vs. "I know what the person's middle name is: he/she doesn't have one".
There are apparently historical reasons for this, but I don't care. The empty string is NOT the same as NULL, and any software that treats them as the same IS BUGGY!
Sorry. Had to get that off my chest. I know I'm answering a question you didn't ask, but that has been bothering me for nearly 25 years (I first learned about this misfeature of Oracle's in 2002 or 2003), and I just had to vent to somebody who would understand.
> Oracle treats empty strings as being NULL.
That is ridiculous. Do not they know the difference between "" and NULL?
Just a set of things too minor to move off of it but annoying enough to not want to start with it.
My list:
No `explain (analyze,buffers)`. Instant DDL has some warts (e.g. fk, metadata locks). Query planning bugs (actually... query planning in general is disappointing). Exiting the repl doesn't stop queries. Implicit type casting. Replication lag from large DDL (e.g. creating an index). Lack of two phase DDL (creating constraints NOT VALID and then VALIDATE later). Lack of extensions (e.g. pg_vector). No safe access to inspect buffer cache. AWS Aurora seems to only add shiny new things to Postgres. And more.
Again, none of this is quite enough to migrate off of it for an established system, but certainly enough to avoid it on a new project.
What's wrong with MariaDB?
Last I looked, MariaDB still implemented JSON columns as LONGTEXT under the covers, making it a non-starter for any serious use of said type.
I also miss clustered indexes, datetimeoffest, plan caching and query hints from MSSQL.
Speaking as long-term (>15 years) user of Postgres in science, I am getting worried about the lack of columnar type of storage in Postgresql. As the datasets become bigger and bigger, the limitations of PG's storage are becoming more and more significant. I know there are various extensions (i.e. cetus) that may offer such functionality, but then you depend on that extension being supported in the future, as well additional complexity.
You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category.
It’s like saying that you’re getting worried Apple doesn’t sell washing machines.
> You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category.
I don't want a columnar database, I have a few tables which would be excellent fits for columnar storage. And the relational model doesn't say anything about the way data is stored.
Postgres's whole origin story is basically to think outside the box and don't be constrained by existing thinking. Stonebraker thought existing databases were too limited in terms of their data types and expressiveness. He started Postgres as an evolution of Ingres (Postgres = Post Ingres) that added rich custom data types and a rewriting system based on rules.
Columnar and all the other fun stuff (JSON, GIS, inverted indexes, embedding vectors) is a natural progression of that thinking. With TimescaleDB, Hydra, Citus, pg_mooncake, etc. becoming very popular the last few years, there is a clear demand for an integrated experience.
(Stonebraker also thought one database shouldn't do everything, as described in his early 2000s "One Size Does Not Fit All" paper, and Stonebraker branched out into HStore/Vertica for columnar. In hindsight, I think that was appropriate for the time, but no longer a significant concern.)
PostgreSQL was a right tool for my task for many years. It is a question for PostgreSQL can adapt to a new reality of much bigger datasets or I have to switch to a new tool. And I am not the only user of Postgresql in this context. So it is easy to say in vacuum 'you are using the wrong database', but it's not something that can be easily changed with 100s of Tb of data, existing user workflows etc.
Columnar vs row based is a large architectural distinction. In theory it'd be great to support both, but this has also been an elusive and complex problem to solve. You're not going to see a simple "columnar type" on a row based system like this any time soon, that's trivializing the situation.
Andy Pavlo teaches about this and has done real work in this space, and certainly considers it to be non-trivial. His YouTube channel is spicy and worth looking into if you want to dive deep on the topic.
"Not something that can be easily changed" is not a valid rebuttal to "the tool no longer supports how the task I am working on has evolved over many years". This isn't 'saying in a vacuum'; 'has this tool ceased to be the right answer and how do I move to a better solution' is an exercise responsible app owners should engage in all the time. Shouldn't be different than things I presume your job touches on: if your research evolves to need to see in radio waves, do you complain the optical telescope you started with back in the day doesn't see radio waves and expect someone else to make that happen, or do you find a radio telescope?
You can either 1) accept the limits of the tool, at least until it becomes a priority for the tool developer to add whatever feature you want, 2) pay someone to develop the features you want, or 3) buck up and do what vast numbers of ops folks have done before you and move to something that does what you need.
Age old product question. The Honda Civic is much larger today than the original, because the original targeted young people on a budget. As they aged and had kids, they needed more room in the car. Most peoples’ insinct is to buy a newer version of the car they love, so the civic grew to accommodate the aging market.
No real point here other than an observation about how the installed base’s needs change, across industries.
Just because it's easy to say doesn't mean it's wrong.
By the same logic, you could say Microsoft Access should have all the capabilities of Postgres because it's painful for small businesses to move off of it when it's no longer a good fit for their needs.
The thing is that PG had introduced pluggable storage engines exactly for the reason I am talking about, and there have been few implementations of columnar storage using PG functionality, it's just they always stayed out of the tree. So I wasn't talking about some functionality that is completely out of scope.
But I agree in the end I may be forced to move elsewhere...
I mean I don't disagree with you, but they did just add a graph database feature, which is about as orthogonal to relational database design as you can get.
I don't agree, a relational database is not required to have a particular storage engine or format, that is an implementation detail.
There are other relational databases that have both kinds of storage engines and some use both on the same table (row based insert with column based migration and secondary column store indexes: https://learn.microsoft.com/en-us/sql/relational-databases/i...
Just like you can have b-tree based table storage vs heap in the case of index organized tables / clustered indexes (which pg doesn't have) you can choose column based instead the logical data model is still the same relational model.
From a computer science perspective, I'm not exactly sure how a transactional database would implement a columnar type. Postgres + CDC + an actual analytical database like ClickHouse would be your strongest bet at scale.
A bit of a shameless plug, but I've been working on this for a few months in the form of an extension: https://github.com/xataio/deltax
When I started I thought there's too much inherent overhead in using Postgres tables for storage and using the Postgres executor, so figured it would be pretty cool to match Timescale in performance. I didn't think it would be possible to get close to dedicated analytical DBs. But as the project progressed and the performance got better and better, I'm now firmly in the camp of doing analytics with Postgres + an extension.
"As the datasets become bigger and bigger,"
I think the better way is to use Postgresql for new data and routinely archive off older data to data warehouse type database, to keep the Postgres one small.
(Many companies also now use a RDBMS alongside either a KV database or document store in main app)
Which document store do you prefer? And do you use it via Postgres using a foreign wrapper?
Two most recent companies:
The first was using SQL Server alongside CouchDB, an older company. CRUD Data into SQL, documents into Couch. (used separately, document Url in Couch stored in SQL and brought in by front end)
The second, startup with funding, was using Azure SQL Server (and complaining of the cost) and using Azure Cosmo (Key Value NoSQL) alongside it. Same relationship, no link between the two, it's the front end that drew data from both.
Just look up the pro and cons of each database, some are insert only, some are distributed, some are faster read then write etc. LLM will give you a quick comparison.
Also: https://db-engines.com/en/ranking
Fine by me, as a 25 years user. More is not necessarily better, see redis.
I was about to post something similar.
There was a big fanfare about orioledb a while ago, and i think it got bought by people that wanted to push that into mainstream postgres?
Did it die somewhere along the road?
Supabase customers run it (marked alpha).
https://supabase.com/blog/orioledb-launch
And they continue to work on it.
https://supabase.com/blog/orioledb-patent-free
No word that PostgreSQL 19 introduces native application-time temporal data support based on the SQL:2011 standard? https://www.depesz.com/2026/04/02/waiting-for-postgresql-19-...
Wow. Incredible how this was not mentioned in the OP. I had done it with tcn triggers and adding "_archive" shadown tables manually with tcn (https://www.postgresql.org/docs/current/tcn.html), but doing it natively is gonna be, as per most postgresql implementations, wonderful.
Nor more than a mention of Query Hints, which had some interesting discussion under a similarly-titled submission.
https://news.ycombinator.com/item?id=48413655
They're a cool feature but honestly a bit tricky to use well, IMHO. And be careful with PII lingering in a temporal void somewhere for a long time :-)
i like the COPY and logical replication improvements. Currently I back up my PG database with a sidecar Databasus instance that is heavier than my entire backend + DB + Caddy!
(LLM writing rant below)
---
> That alone tells you something: Users had a real need, and the ecosystem filled the gap.
> This sounds straightforward, but it solves a real operational problem.
> None of these change the world. All of them make day-to-day data workflows better.
> The easy thing to do here is list planner changes and call it done. But the more useful takeaway is this: Postgres keeps getting better at recognizing the shape of common queries and doing less unnecessary work.
> [Proceed to list planner changes]
If Orwell were alive today, he might declare himself illiterate in English and learn Klingon just to avoid having to read these.
The graph database feature looks interesting, but I wonder...
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders]->(o IS orders WHERE o.ordered_when = current_date) COLUMNS (c.name AS customer_name));
That is _awful_ syntax; it is reminiscent of neo4j, which is surely not a tool anyone serious should copy from outright in 2026.
And of course the final thing I am left wondering is if it's fast. Row-level security is such a useful feature and yet only a fool would contemplate building anything serious with Postgres', as the planner goes haywire and does per-row-matching, nuking performance.
That is not a homebrew sytax that Postgres came up with themselves.
It's SQL/PGQ, which was derived from the Cypher language for Neo4J and now is part of the SQL standard.
Fair --- but I feel my criticism stands. It's awful. Don't get me started on 80% of all deployment uses of neo4j...
It's great to see GROUP BY ALL introduced which, AFAIK, was a concept introduced by DuckDB.
> Several of these features were first introduced by DuckDB, while some are inspired by other systems. Many of the features originally introduced by DuckDB (e.g., GROUP BY ALL) have been since adapted by other systems.
https://duckdb.org/docs/lts/sql/dialect/friendly_sql
It's funny that, "GROUP BY ALL" makes a lot of sense but it had never crossed my mind before!
I can't decide whether this person writes in the type of style that was apparently overrepresented in LLM training, or whether they heavily used AI to spruce up their writing. I'm learning towards the latter.
Spruce up is unreasonably charitable. I'm more irritated that the authorship information is misleading. craig-kerstiens is not available on Huggingface, and yet not a single sentence in this article seems to have been typed on a keyboard.
When Claude writes things like "as someone who has spent a lot of time doing X", I think this is also a kind of failure of alignment. LLMs shouldn't write as if they had personal experience. It's something a person might say in the training data, but I just think LLMs shouldn't claim life experience they don't have, even if that's a statistically likely sequence of tokens.
You don't need to be charitable, Snowflake laid off technical writers citing AI to replace them: https://snowflake.help/snowflake-layoffs-2026-technical-writ...
These low effort constant comments about style or formatting are against Hackernews guidelines for discussions and something needs to be done to clean up the comment section. Getting to a ridiculous point
If comments about how "this blog post could have been a link to this github repo" are in-bounds, so are comments about how "this could have been a link to a LLM session." HN has always tried to work out if a submission is novel and interesting work or is just a slick coat of paint on mundane work (sometimes if good work is obscured by insufficiently clever presentation). Highlighting that content was generated by an LLM and asking if that impacts how to understand it is entirely in keeping with our culture and standards.
It's not about style or formatting, people are tired of reading slop.
These comments are worse than the slop.
They aren't actually. They make me feel like I'm not going crazy - that I'm not the only person noticing that the quality of the average article on hacker news has dropped off a cliff in the last 6 months. Links from different people with different cultures, life experiences, and languages have the same tone of voice, the same sentence structure, and the same breathless, boring, staccato yet arrhythmic, emotive yet soulless style.
I hope everyone keeps pointing it out. Even better, change the site guidelines to make AI generated articles a flaggable offense. It's already been done for comments.
Hear hear. I can't believe people are content to consume slop like this.
Pangram says the text is entirely AI generated but I don't know how trustworthy Pangram is. (I would love to hear what others think about it.)
It has extremely low false positive rate in various tests (e. g. https://arxiv.org/html/2501.15654v1), I've never seen it mark something I knew was human written as AI.
I get it's in vogue to take stabs at whether or not AI was used. But I think the more useful approach is to instead be critical of the end product, if you have criticisms of it.
This IS a criticism of the end product.
This actually gave me an amusing idea: a book review club that strictly reviews the cover art, book binding, hand feel, paper weight, font, etc. of the book.
How weird a book club that actively ignores the style of writing would be!
I'm wondering. Just wondering? Will they ever support multiple storage engines like MariaDB? Having a storage engine that support OLTP or OLAP or append-only would be cool. I totally understand if they don't want to do that.
I'm dreaming of block compression in Postgresql, instead of only row compression, too limited to be effective. I know you can store your data on a Zfs pool with block compression, but having it native would remove the burdain of setting this up and maybe better perf.
The Authors avatar looked familiar, crunchydata ?
Snowflake acquired Crunchy Data yes and Craig was at Crunchy Data.
Why did databricks and snowflake buy Postgres vendors?
Interesting, I was wondering why Snowflake was investing in PostgreSQL. Looks like Snowflake bought Crunchy Data and Databricks bought NEON… so the two leading DWaaS companies have managed PostgreSQL offerings now.
& ClickHouse has managed Postgres in open beta (which I work on)
I am looking forward to the day it supports table access methods that enables variety of use cases out of box.
Something like rocksdb as PG backend would be fantastic. Yugabyte does this but it's not PG.
Salesforce runs Salesforce on postgres with LSM:
https://vldb.org/cidrdb/2026/a-multi-tenant-relational-oltp-...
Likely their own fork, not plain PG.
OrioleDB seems interesting, not sure it's what you're after but worth a look.
It is, but it's not usable right now without some patches to postgres. At least that was the case as of PG17, and very likely 18 too.
Context: https://www.orioledb.com/docs#:~:text=OrioleDB%20currently%2...
Oh my, finally a reason to upgrade from 16.
The Async I/O in PG18 is what got me to jump from 16
This looks amazing, we've been using Postgres in production for the last two years on a fairly high-volume system and it's been fantastic.