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.
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.
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.
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.
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.
