Voice of the DBA

Tag: postgresql

  • PostgreSQL 19 Is Late

    “All software contains bugs.”

    That’s a quote from Robert Haas’s “Postgres 19 is late” post. He goes on to say that PostgreSQL has lots of bugs in it. It’s the way it’s been for a codebase that’s over 40 years old. He goes on to note that it’s easier to find bugs with LLMs, and easier to fix them. It’s not, however, as easy to do the latter as the former.

    The impact on PostgreSQL 19 is that they’ve had to revert a number of features much later in the process because the humans were reviewing and fixing bugs found by LLMs. This effort delayed other work and has affected the release.

    As someone who primarily works with SQL Server, I’m not that worried about delays in PostgreSQL or other platforms. I think it’s a fine choice, but I’m focused on SQL Server, which I think is more of a joy to work with.

    However, there are a few interesting things in here. First, I think SQL Server has had a lot of bugs found lately (likely by AI LLMs). I recently wrote about the frequency of patches, which is higher in 2026. This is probably due to LLM code review, and that’s good. It also means we likely will have more patches coming, not just in SQL Server or PostgreSQL but also in our other systems. What’s the likelihood that someone will run an AI review and find bugs in other COTS software, internal apps, or even those no-code/lo-code systems people have been deployed? That’s not even mentioning the vibe-coded prototypes that are suddenly important enough to put into production.

    The other interesting thing was that Robert accepts that LLMs are likely to find more bugs in the future. How will they find time to work on them? After all, from the article, fixes “still need to be reviewed and committed by humans.”

    Maybe the best part of the last paragraph. He says LLMs are going to require every developer to raise their game. Every feature is going to be scrutinized by AI. That’s a challenge and an opportunity, but the key is that developers need to get better. AI can help you and write code at amazing speeds, but it doesn’t think.

    We need sharp developers that think in order to cope with AI.

    Steve Jones

  • PostgreSQL Support in the Data API Builder

    Next week I’m at VS Live in San Diego (register and join me) and one of my talks is on the Data API Builder. I want to show how this gives you REST, GRAPHQL, and MCP access. As a part of the talk, I also wanted to show this working across platforms, so I’m showing this in PostgreSQL as well.

    I’ve been experimenting this in a few ways, but I decided to see how hard it was to add PostgreSQL support. I decided to follow a similar structure as my SQL Server demos, which are all in my GitHub repo.

    First, I added a .env file, which has the credentials. THIS IS A DEVELOPMENT structure, and not something you’d deploy from a repo. Secure your production stuff! This is my local file (in .gitignore), but it’s a simple connection string.

    2026-08_0375

    Next, I copied (well, I guided Claude to do this)  my demo01.cmd and edited it to say the type is “postgresql” instead of SQL Server. The new file went into a separate folder as (demo01.cmd)

    2026-08_0375

    I had it copy the demo02.cmd and alter this for the PostgreSQL Pagila sample db, which is installed and running on my machine.

    2026-08_0376

    Note, this errored out at first, with the note that DAB doesn’t support the PostgreSQL array type. I ended up having Claude create a view that skips this column and the column that is a vector type. Hopefully those will be supported soon by DAB.

    2026-08_0373

    Next, dab start, and I had a running API server over my PostgreSQL database in just a few minutes. Here’s my Bruno query of the GraphQL endpoint for Pagila.

    2026-08_0377

    Not enough people are using or trying the Data API Builder. This is truly an easy way to get a usable API over your database without a lot of data access layer work for developers.

  • Making a PostgreSQL Backup in a Container

    I needed to back up a PostgreSQL database as a part of the repro for an issue I had. I hadn’t ever made a backup of PostgreSQL, so this was a learning exercise for me. Plus, a container made it slightly more complex than SQL Server. This post shows what I did.

    In looking over the documentation and in searches, everyone seems to use pg_dump to make a backup. This looks hokey and immature to me, essentially a command line tool to script things out.

    There is also a file level backup and a PITR backup strategy,  but those are more complex for my use case.

    So, how do I run pg_dump?

    I found this article, which is helpful, but contains a lot of stuff. Essentially, I need to connect to my container and run from there. I’ll use the exec with the it switch from Docker to do this.

    So, first I run this to get a shell inside the container.

    docker container exec -it pgdev /bin/bash

    The image below shows me connected to the container with a bash shell.

    2025-02_0286

    Now I can run pg_dump. I’ll use this command, which connects to the db with a user and sends a database backup to the /usr location.

    pg_dump -U postgres -Fc bb_fullrestore > /usr/bbfull.dmp

    Once this is done, I can go check. First, I’ll ls this folder and I see my file.

    2025-02_0287

    Summary

    As technology advances and I use containers more, I’m hesitant to keep installing stuff on my machine that I don’t need to. I’d rather have scripts. Trying to just get pg_dump installed is a pain, so this post shows how you can access this in a container and create a backup.

    If you’ve mapped your folders in a container to your local machine, then you can easily find this file and most it elsewhere for a restore.

  • Should I Learn PostgreSQL

    I got asked this question recently:

    I constantly see PostgreSQL on Microsoft slides, email, ads, etc. My MCADAA exam started with an entire section on it. I’m trying to determine if it’s worth focused study and training time. Roughly how much of the Azure cloud database space does PostgreSQL occupy? Should I include it in my personal training program?

    It’s a good question, though I am assuming that MCADDA is the Microsoft Certified: Azure Database Administrator Associate cert. That page describing the exam only mentions Azure SQL (SQL Server), and not PostgreSQL, so while I think Azure SQL for PostgreSQL is good to learn, I think the cert preparation needs an update if that topic is on the exam.

    In any case, should you learn PostgreSQL?

    I’ll give you the DBA answer: it depends.

    Ask yourself some questions:

    • Does your org use PostgreSQL or are they planning to do so?
    • Are you going to stick with your company for a few more years?
    • Are there more things you should be learning in the area you do work now?
    • Are there things you should become more skilled at that your company values?
    • Do you know what the opportunities are for people that know PostgreSQL well?

    Depending on the answers, I may or may not recommend you spend time there. If you have other things to learn that might be better at your current job, either the job you have or one you might want, then focus there. If your company does (or doesn’t) value PostgreSQL, then that influences your choice as well.

    PostgreSQL is growing fast. DB-Engines shows growth across a few platforms, and PostgreSQL is doing well.

    2024-10_0140

    The StackOverflow developer survey shows similar results:

    2024-10_0141

    However, those are general results. Your specific situation is different. Think about it and ask the question above to lots of people.