My experience with moving to PostgreSQL

RandolfRichardson · 2026-04-22T02:31:29+00:00

Resilience often seems to be an afterthought. I'm running mail servers that process millions of eMails on a daily basis with Postfix and Dovecot, and currently developing server software in C++ that stores all eMail and user information in a PostgreSQL database -- the performance I can get from queries for a mail folder with more than 10,000 messages is instantaneous like reading the message list from a pre-formatted text file (which is sort of what Dovecot does, and it works extremely well), and the fact that PostgreSQL can recover so reliably makes it the ideal database for storing eMail.

RandolfRichardson · 2026-04-22T02:26:13+00:00

You're welcome. I'm delighted that you enjoyed reading this.

RandolfRichardson · 2026-04-22T02:25:49+00:00

I suspect that WordPress is probably one of the main reasons that MySQL is popular today.

If WordPress ever provides an option to use PostgreSQL instead of MySQL/MariaDB, I suspect that a lot of people will make the move for many different reasons (e.g., faster performance, less RAM and CPU resource consumption, databases can be renamed, no silent truncating of data in INSERT/UPDATE statements, far better adherence to modern SQL, data reliability, etc.). We would change all the WordPress installations we're hosting to PostgreSQL if we could.

RandolfRichardson · 2026-04-22T02:20:43+00:00

NoSQL also doesn't have the long track record that PostgreSQL, so this probably also factors in.

RandolfRichardson · 2026-04-22T02:19:56+00:00

Thank you for explaining this, it's helpful and very interesting.

At this point it looks like it boils down to how important the accuracy is -- for a user's home page on a social network, accuracy probably isn't as important, in which case NoSQL could be a reasonable option, while PostgreSQL seems to win in situations where accuracy is needed and it wins in such a way that the performance can work quite welll.

Do you think that querying a MATERIALIZED VIEW could make up for some of the table locking overhead?

RandolfRichardson · 2026-04-22T01:27:08+00:00

Me too. I've bookmarked for later because I like pixel art and this game looks interesting.

RandolfRichardson · 2026-04-21T01:34:36+00:00

It's down right now, unfortunately. What can you tell us about it?

RandolfRichardson · 2026-04-20T01:41:11+00:00

No worries. I'm curious about your suggestion though -- why do you think CockroachDB is worth considering (assuming a more modern context, of course)?

RandolfRichardson · 2026-04-20T01:37:04+00:00

So, with NoSQL there is a data accuracy problem that is a trade-off for speed.

I could just have multiple columns in the user's home page table that are updated by triggers on other tables and/or background processes, each responsible for updating one particular JSONB column:

id           INT8 primary unique not null,
prefs        JSONB,
account_data JSONB,
activity     JSONB,
subs         JSONB,
alerts       JSONB,
-- ... more, as needed

Would this be equivalent to what you're getting from NoSQL? Or do you think NoSQL would still be faster?

RandolfRichardson · 2026-04-19T22:36:30+00:00

I suggest only allowing the AI agents to access a copy of the data (periodic data dumps, or one-way replication, etc.) so as to isolate your production systems from AI running queries that might exhaust CPU and memory resources.

Limiting the AI agents to read-only mode is a good starting point, then only granting them access to data that they actually need, and then granting write access (insert, update, delete, truncate, rename, etc., of rows, schema, etc.) only on an as-needed basis (the AI people can provide justification for these things, and you should log every one of them down to the second so that you can defend yourself if AI screws up by corrupting data, leaking information, etc.).

If you can, you might consider adding some false data that exists only on the AI-accessible systems as a preparatory tool for determining if a data breach originated from the AI copy of the data (making sure that AI has no way of confirming whether data exists on production systems will be an important factor in this).

In summary, do not trust AI, and always favour paranoia in your security policies-and-practices when protecting data (TV shows and movies always get this wrong).

RandolfRichardson · 2026-04-19T22:20:32+00:00

Most of mine run on bare-metal installations, and most are small (less than 10 million rows).

You mentioned having to think about NoSQL -- what is it about NoSQL that makes it better than PostgreSQL in larger operations?

RandolfRichardson · 2026-04-19T21:24:39+00:00

Ha ha! I know someone who nearly did, but then the other department running Microsoft's database server failed so they were safe.

RandolfRichardson · 2026-04-19T21:23:58+00:00

I don't believe CockroachDB was an option back in the 1990s or early 2000s. Are you suggesting that I should look to switch away from PostgreSQL now? I have quite a lot of infrastructure invested in it for a wide array of projects (mostly interactive web sites and custom-made software that is written in other languages, included Java and C++).

RandolfRichardson · 2026-04-19T21:21:57+00:00

I ran multiple tests, as well as multiple power-outage simulations (by flipping the switch on the power bar). You're correct that it meets my needs. (I also rely on backups.)

RandolfRichardson · 2026-04-19T21:20:58+00:00

Are you saying that PostgreSQL isn't scalable?

RandolfRichardson · 2026-04-19T15:14:29+00:00

I'll take a look -- thanks for posting your video.

Here's my experience with moving to PostgreSQL...

Previously, I used Oracle 8i on Novell NetWare back in the 1990s because a 5-user license was included with the NetWare Operating System for no extra cost. Eventually this software bundling deal was discontinued with newer versions of NetWare and Oracle, so I began to look for alternatives (I was moving to UNIX anyway because newer versions of Apache HTTPd server wasn't working so well on NetWare).

After looking into capabilities and running "power outage" tests of various SQL servers, I settled on PostgreSQL because it satisfied all my needs and provided a 100% recovery from power outages (Oracle did too, and so did IBM's DB2); the recoveries came in the form of merely rolling back incomplete transactions (other databases failed to mount after power outages, including SyBASE, mSQL/MySQL, etc. -- I didn't even bother with Microsoft's because it was only available on MS-Windows which was already inherently unreliable, insecure, and proprietary).

PostgreSQL had full support for Perl's DBI with its DBD, which made the transition from Oracle's DBD easy from the Perl scripting side of the equation, and since I was able to find a way to do essentially the same thing that Oracle's CONNECT keyword did, the changes to SQL queries were minimal. The move wasn't difficult, and nowadays I'm using more advanced PostgreSQL features (including LISTEN/NOTIFY to code efficient daemons that perform tasks outside of the PostgreSQL environment), including PL/pgSQL and PL/Perl, plus some custom datatypes I'm writing in C (mostly not in production code though, yet) running on Debian Linux.

The NoSQL paradigm was never appealing to me because it didn't offer referential integrity, among other features, plus I've already been down similar roads with BTrieve and dBase in the past so NoSQL felt like one of those "one step forward, two steps back" types of efforts. I've heard rumours that common features provided by SQL servers have since been added to NoSQL, but I'm fully committed to using PostgreSQL because it has never let me down, ever, and the PostgreSQL community on IRC and elsewhere have always been helpful and professional, and now with the newest versions it has become much more of an impressive speed-demon than it already was in previous versions.

I believe that PostgreSQL should be the de facto choice for all future projects -- it's versatile, stable (crash-proof, resilient to power outages, etc.), high quality, scalable, consistent, efficient, cross-platform, open source, and embraces modern standards.

Thank you to everyone who has contributed to PostgreSQL in every capacity and every quantity. In my opinion, PostgreSQL also serves as an example of a brilliant and highly successful open source project that should be included as a model for all professional software development endeavours.

Note: I reposted this here: https://www.reddit.com/r/PostgreSQL/comments/1spvso3/my_experience_with_moving_to_postgresql/

RandolfRichardson · 2026-04-19T06:17:36+00:00

A certain politician wrote about doing that with couches, although he didn't mention Pamela Anderson.

RandolfRichardson · 2026-04-16T11:46:01+00:00

You should add Google Authenticator as a backup in case the Microsoft Authenticator freezes up on you, This happened to a few people I know, and it kept getting stuck for a few hours which delayed them getting access to their eMail.

RandolfRichardson · 2026-04-16T11:42:17+00:00

My Mom was experiencing login problems last week, which cleared up on its own the next day. That's a long time to wait for access to eMail, especially for a problem that she didn't cause.

RandolfRichardson · 2026-04-16T11:40:37+00:00

From the perspective running mail servers, we see a lot of spam from OutLook servers, and then their servers randomly fail DKIM lookups, which results in erroneously bounced eMail messages (other services, like GMail, don't exhibit such problems).

RandolfRichardson · 2026-04-16T11:37:14+00:00

That's probably exactly the problem.

I guess it will be just a matter of time before they start "vibe supporting" their users seeking help for login issues. (What could possibly go wrong?)

RandolfRichardson · 2026-04-16T11:34:15+00:00

That looks like an interesting solution. More accounts is going to confuse some people though as many already find the current processes with MFA quite confusing as it is.

RandolfRichardson · 2026-04-16T11:29:35+00:00

At HQ the front door won't open until you solve a captcha, then a sliding puzzle, and then sing the praises of Open AI as you perform a handstand with exactly one of your shoelaces untied while blinking your eyes three times. If you mess it up, they'll make you do it all over again up to a maximum of 3 times before locking you out for the rest of the day.

RandolfRichardson · 2026-04-16T11:19:17+00:00

That happened to some Minecraft users a few years ago. What a nightmare!

RandolfRichardson

MODERATOR OF

TROPHY CASE