PostgreSQL is the Database Management System of the Year 2024(db-engines.com) |
PostgreSQL is the Database Management System of the Year 2024(db-engines.com) |
Migration isn't easy, as this venerable application uses ADO.NET Datasets with TableAdapters and plenty of stored procedures. The syntax is almost compatible though. But not enough unfortunately.
For our next product, we're sure to bet on PostgreSQL instead.
https://en.wikipedia.org/wiki/SQL/PSM
The syntax for this does come largely from Oracle.
"SQL/PSM is derived, seemingly directly, from Oracle's PL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on the US Department of Defense's Ada programming language."
In any case, I have thousands of lines of PL/SQL written by many people which are currently useless for SQL Server applications.
SQL Server should implement SQL/PSM. The sooner, the better.
For those trying to escape the licensing costs of SQL Server, Babelfish may be an option.
Why is this a problem? I've always enjoyed T-SQL. Right from the start it had a "scripty" feel and stored procedures were easy to code with it. We thought about implementing PSM at Sybase in the 1990s but there was little user demand. (Unlike row level locking, the lack of which undid Sybase SAP implementations...) Internally many of the engineers thought PSM was pretty awful to use. I never liked it myself.
"Goodbye Microsoft SQL Server, Hello Babelfish" - https://aws.amazon.com/blogs/aws/goodbye-microsoft-sql-serve...
Or the Opensource project: https://babelfishpg.org/
This ^^^
A small businness I (no longer) work for was using Windows Servers, SQL Server, Classic ASP, .NET and other things. It was expensive!
I tried sooo hard to migrate to get them to realise the savings moving over to Linux and Postgres, and get their DATED software over over afterwards!
Well, it was Linux and MySQL/MariaDB but I have slowly grown fond of Postres over the last couple of years.
I will always remember (and find funny) when we purchased a server a third-party no longer wanted to support for us anymore (linux+php) and my boss said "they only pay £300 a year for that server" -- yep.
I too was impressed with SQL Server last time I used it (big note: SQL Server is one of the few commercial DB's that does real nested transactions; PG does not), but I get it.
More or less. The default ROLLBACK behaviour is to roll back the whole nest. You have to use SAVE TRANSACTION instead of BEGIN TRANSACTION and specify the name in ROLLBACK. If doing this in a procedure that may or may not be part of a nested transaction (an explicit transaction wasn't started before it was called) you have to test to see if you need to BEGIN or SAVE¹ and what to do if a rollback is needed (you likely don't want to ROLLBACK everything if you used SAVE, but have no option if you used BEGIN). Not exactly intuitive. And saved transactions can cause excessive lock escalation, impacting concurrent performance.
SQL Server is generally a damned fine product overall, both generally and compared to a lot of other things emitted by Microsoft, but it isn't even close to perfect in some areas.
----
[1] ref: https://learn.microsoft.com/en-us/sql/t-sql/language-element...
Not sure where this myth keeps coming from, but no, it does not:
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a...
(Gist contains a bunch of Postgres ecosystems things, if anything is missing, please let me know)
Managed Postgres on Google Cloud is a fantastic product (I'm sure other cloud's offerings are also similarly good). Backups, replication, point-in-time recovery, managed updates, plus quick & easy system & query insight tooling. All for less than £1/day.
Anything new that you particularly enjoy?
Otherwise, PostgreSQL is fantastic.
- Number of mentions of the system on websites, measured as number of results in search engines queries. […]
- General interest in the system [via] Google Trends.
- Frequency of technical discussions about the system [on] Stack Overflow and DBA Stack Exchange.
- Number of job offers, in which the system is mentioned [on] Indeed and Simply Hired.
- Number of profiles [mentioning] the system [on] LinkedIn.
- Relevance in social networks [by counting] Twitter (X) tweets, in which the system is mentioned.
Seems like a reasonable approach to me. They should add more social networks now that Twitter/X is hemorrhaging marketshare. Mastodon, Bluesky and Threads should be included.
Honestly I see that all over… so many sites have Twitter integrations that haven’t even been update to X yet. Gonna take a while for the broader ecosystem to shift.
- Number of search results for each DB's name
- Google Trends
- Mentions in Stack Overflow etc.
- Mentions in Tweets
- Mentions in people's LinkedIn profile
Probably gives a vague idea of popularity of the system, but the measure is otherwise pretty useless.
There's a lot of new articles of how to improve its performance and I love that I don't have to care about the n+1 problem so much.
It is now the default production database for Rails for a good reason.
initially the performance was too poor, but after a bunch of reading and some changes in how I was using SQLite I got it to easily do more than 100k rows per second of insertions (the db wasn't very wide). On an old embedded device, mind you. Didn't need that much, but wowza! was my expression at the time. I've had a love for it in my heart every since.
Just saying that you run network I/O and blaming the kernel / network latency for all the delays in other SQL drivers is easy, but with SSDs getting so much lower latency than HDDs, one has to seriously consider throwing out network based databases for lots of programming tasks.
"Methodology We calculate the DBMS of the year by subtracting the January 2024 from the January 2025 scores. We use the difference between each DBMS’s score, rather than a percentage growth, which would favor systems with lower popularity at the beginning of the year. The DMBS of the year is an illustration of how much each system increased in popularity during the year. You can read more about our exact methodology here ( https://db-engines.com/en/ranking_definition ), but in short we are ranking popularity of DBMS based on how much people are discussing and communicating about each system, including job offers, professional profile entries and citations on the web. job offers, professional profile entries and citations on the web. "
Postgresql +15
Snowflake +28
MSSQL -78
So DB of the year seems to be based on feels rather than their numbers.
Although by that measure Snowflake should be #1 not Postgres, so who knows.
Seems like they are just subjectively picking what is more internet popular.
Also, where is ValKey?
From the article:
“We calculate the DBMS of the year by subtracting the January 2024 from the January 2025 scores. We use the difference between each DBMS’s score, rather than a percentage growth, which would favor systems with lower popularity at the beginning of the year. The DMBS of the year is an illustration of how much each system increased in popularity during the year”
Their methodology, even as stated, seems to be pretty flawed.
Yes, you can drive a Ford Falcon coast-to-coast, but how much gas are you going to spend? Postgres is a solid database but has roots in the 80s. Optimizing for modern hardware would reduce hosting costs and improve efficiency. [3]
This isn’t criticism of Postgres as a database but of the idea that it’s inherently sufficient for all use cases.
--
1: https://www.cs.cmu.edu/~pavlo/blog/2025/01/2024-databases-re...
2: https://blog.kuzudb.com/post/what-every-gdbms-should-do-and-...
3: https://cedardb.com/blog/ode_to_postgres/#why-is-everyone-st...
The algorithms detailed in the graph DB post can be run on top of PostgreSQL, though one has to do some of the work of the DBMS themselves (maintaining indexes, transforming query to correct form). It's extra work and requires some knowhow, but up to some extent it works "well enough".
Similarly, the CedarDB comments, that could be boiled down to the lack of column major batched executor model, are mostly applicable to analytical queries. Point query and traversal type workloads are not as different. There are various projects that work around this to some extent by sticking DuckDB into PostgreSQL . This gets vectorized execution on mass data scans, which is usually enough to get into the right ballpark.
The point being that the amount of use cases where Postgres is sufficient is highly dependent on the skill and time put into it. And at some point the extra organizational cost of going heterogeneous is going to be worth the payoff. The hard part is figuring that out ahead of time. Which is why my recommendations for starting developers is to just use Postgres until it starts to hurt, then optimize it to work well again and use the insight gained to see what functionality needs to be peeled off into a specialized system.
This is disingenuous. If you’re comparing Postgres to CedarDB, a more apt analogy would be Postgres is a late model Ford F-150. A utility vehicle with roots in the 80s but continuously improved since then. Not the most efficient vehicle or the fastest, but has lots of utility and great reliability. People have collectively driven F-150s for billions of miles.
And CedarDB is a concept car that some guy built in his garage. It might be efficient but it certainly isn’t proven to be reliable like Postgres is.
If I’m taking a coast-to-coast trip, I’m driving the F-150. I might not get there quickly or efficiently, but I’m sure I’ll get there. And if my F-150 breaks down, there are thousands of mechanics between the coasts that can help me get back on the road. If I take the CedarDB concept car, when it breaks down there are only one or two people that can help me, and they are thousands of miles away.
But for me writing stored functions is not that fun, instead it would be better to use a different language, and write code that generates all the pl/pgslq and manages stored functions for me.
Kind of like code in lisp, then deploy generated code to postgres.
I dont know about you guys but writing "create or replace function ..." boilerplate again and again manually gives me wrist pain.
That said, it’s not that hard to set up replication [0]. Properly tuning the various parameters, monitoring, and being able to fix issues is another story.
RDBMS is hard. MySQL is IMO the easiest to maintain up to a certain point, but it can still bite you in surprising ways. Postgres appears to be as easy on the surface, buoyed by a million blog posts about it, but as your dataset grows, so does the maintenance burden. Worse, if you don’t know what you should be doing, it just eventually blows up (txid wraparound from vacuum failures probably being the most common).
[0]: https://www.postgresql.org/docs/current/runtime-config-repli...
Hopefully OrioleDB can upstream all the necessary changes soon. For those who don't know, it's a storage engine for Postgres that uses undo logs instead of vacuuming old records.
And yes, I read the documentation and it is still cumbersome to have an HA setup that is easy to maintain. This is what I mean and hope it is clearer now.
You can however create your own health/status check service on top of pg_autoctl show state and use HAProxy if required.
I don't think there's something easier to setup and manage than pg_auto_failover, Patroni always appeared very complicated to me.
This video covers pretty much all the practical stuff. I timestamped the parts in a comment.
PgPool II Performance and best practices https://www.youtube.com/watch?v=bMnVS0slgU0
I thought patroni only did physical replication (only replicates across the same version of Postgres). But maybe I'm mistaken.
https://www.sap.com/products/technology-platform/sybase-ase....
SQLite's trigger syntax appears to be conformant.
Transact SQL has much fewer implementations, and standards conformance enables interoperability. The SQL Server family is lacking in this respect.
1. Dump a backup to disk, then restore from dump on new version;
2. Stop the old version, then run `pg_upgrade --link` on the data of the old version which should create a new data directory using hardlinks, then start the new version using the new data directory. This is rather quick; or
3. Use Logical Replication to stand up the new version. This has ... a few caveats.
From what I understood, sometimes the way data is written to disk differently between versions and they're not compatible. I guess due to optimizations or changes in the storage engine?
But yeah, looking forward to that day too!
[1] https://www.orioledb.com/blog/no-more-vacuum-in-postgresql
Gosh, haven't heard that in years. I remember a company I used to work for used it on their old system but the new system didn't use it for whatever reason.
There's also the incoming business argument in favor of not diverging too far from baseline.
If I'm AWS/Azure/GCP trying to attract a customer from a competitor service, 'completely rewrite your app to be able to use us' isn't a compelling pitch.
MS SQL Server and Oracle have different incentives, but the cloud services would probably prefer portability and decreased maintenance / fork support load.
I'm not one to compliment Microsoft software products, but Analysis Services and SQL Server, at least when I used them at the time over 10 years ago, seemed like darn fine products (which they purchased from someone else, I believe...)
SQL Server v7 was a rewrite of significant parts of the internals, so from that point it is probably fair to call it an MS product quite distinct from Sybase's (which itself continued to be separately developed including significant but different improvements/reengineering of the internals), though still showing some signs of its heritage.
Personally I'd like BDR to be in the main tree, or something else, equivalent to Galera:
It's not perfect [1], but it'll get you a good way for many use cases.
[1] https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster
It’s a key “primitive” missing from almost all high-level programming languages, with the notable exception of some SQL dialects.
To the best of my knowledge.
Note: Am not a DBA, merely a full stack dev that has worked with various databases over the years.
https://gist.github.com/pmarreck/970e5d040f9f91fd9bce8a4bcee...
Pasted it there for brevity here. Also includes an easy way to export a Claude conversation (made with the help of Claude, of course).
Savepoints are fully equivalent to nested transactions with the constraint that only one is concurrently active. This limitation is just from the SQL standard programming model. At least in PostgreSQL implementation it would be reasonably simple to support multiple concurrent subtransactions. They are even called subtransactions internally.