GeekNights Monday - PostgreSQL

Tonight on GeekNights, we consider PostgreSQL, the open-source relational database. It's basically the one to use. Most people just call it "postgres." If you're not sure what we're on about, check out our previous episode about relational databases. In the news, new laws mean new COBOL code, John Conway was taken from us, there's a gravity clock (sort of), and Discord is adding group video chat so you can replace Zoom. Also, Brian Swords of York has been rediscovered.

Things of the Day

Episode Links

On Patreon:

I remember when Scott pooh-poohed postgres as way too complex (or something), why don’t you just use mysql? Rym was all “no it’s really good”.

Be curious to know when it switched. I’m sure Oracle played a big part.

I love postgres, it is my favorite of the RMDBs.
I’m currently at a mostly windows shop where we use MSSQL for our back end.
It’s frustrating at the best of times.

I do have interest from management in one day writing a plug in for our system to use postgres and move us over to that, if for nothing else to save on the licensing costs of mssql!

Back in the day Postgres was much more difficult to use than MySQL. That has changed a great deal. My opinion at that time was that if your use case didn’t need fancy DB stuff, like a blog, then MySQL is the way to go since you are actually gaining by not having to deal with Postgres.

That was so many years ago. MySQL doesn’t seem to have moved much at all, and Postgres has continued to power forward. So even for the simplest of application, why not?

As I mentioned in the show, you can use a foreign data wrapper in Postgres. We use one at work for MSSQL, and it works perfectly. Basically, you setup a postgres server and tell it how to connect to the MSSQL server. Then you can make tables in postgres that are exact copies of the MSSQL tables. Ta da!

Now you can just use postgres to query your data on a read-only basis. If you want to write, well… that’s more work. If you like your schema, and you just want to get off of MSSQL, you can just create perfect clones of all your tables trivially. If you want to redesign your schema, you can build an ETL entirely on the postgres side by writing code that selects from the foreign tables and inserts into the new real postgres tables. Then run it and abandon MSSQL.

what hurdy gurdy kit are you working on?

I agree, but usually carve out an exception for using SQLite until you can’t. Can’t beat a single file!

SQLite does not compete with client/server databases. SQLite competes with fopen().

- Appropriate Uses For SQLite

1 Like

Yeah, yeah. When I’m using Django it does.

Only for local dev. A web app is inherently a multi-user application. You can’t use SQLite on the actual production server.

Also, let’s say your application uses even one of the PostgreSQL features like PostGIS. Suddenly SQLite is useless for local dev since part of your application doesn’t work.

Watch me!

And it’s only a change away from upgrading to postgres when you need it :slight_smile:

Pretty sure it’s going to fail if you have more than one user at a time.

Every application is different, right? So it depends on your load.

The main restriction is that writes lock the whole db. So if you’re not timing out writes and can tolerate the latency, you’re probably good to go chief.

I can’t think of a single app I’ve ever made that could survive on SQLite. Even the rarely visited would fail.

Really?? has what, two writes a week on average?

There are more writes than it appears. It’s not just saving one row when there’s a new episode. There’s all sorts of asynchronous operations that are reading and writing.

If I need a really simple localized data store, I use things like H2.

As for the hurdy gurdy, I’m using this kit:

Aesthetically I’m into it. The build is fun like a 3D puzzle.

I don’t care how good it sounds, but my second project will be to see if I can improve the sound. I’m not super up on stringed instruments, so this is a good way for me to learn the fundamentals.

1 Like

The website I built runs on SQLite. However there is nothing changing or adding to the database ever on the production server, so writing isn’t an issue.

I’d change it to Postgres or whatever but it has never been a limitation on what I want to do, nor is the website popular enough to get any loading or simultaneous user issues.

1 Like

Yeah, if the database is built in and invisible to your use case, you can and should just ignore it. An embedded mysql or whatever is fine.

Postgres is really for if you have to actually touch the database itself and have a reason to want to.