3 ms·

I would love to drop MySQL/MariaDB and go with PostgreSQL, but information like this makes me nervous that I would setup a footgun and be constantly debugging t

I would love to drop MySQL/MariaDB and go with PostgreSQL, but information like this makes me nervous that I would setup a footgun and be constantly debugging things because I am not a DB admin expert. Anyone care to comment on how PostgreSQL works out of the box without being an expert? (I've run MySQL/MariaDB for almost 20 years and there are very few issues I've been surprised by)

33degrees 5y ago
I’m definitely not an expert and I haven’t found running postgres any more difficult than mysql. There are some differences that take a bit of getting used to, though.
status_quo69 5y ago
Like a lot of other complex systems, there's a lot of tweaking you can do in the config, but generally postgres works out of the box pretty well. There's a few gotchas on setup (postgres has a very conservative default configuration for things like memory usage and parallelism), but it's still pretty good. I'd say the biggest footgun is really not knowing about the process per connection limitation, which is why the article mentions pg bouncer. Everything else in the article is pretty geared towards setting your db up for monitoring so you can head off issues.
dano 5y ago
I converted a large MySQL DB to PostgreSQL in 2004 and never looked back. Think about how many improvements have occurred in Pg releases since that time and the availability of information. It took my team about six months to do the conversion as there were any number of type conversion problems due to programming assumptions and loose typing in MySQL at that time. There's also an excellent support community on IRC at Freenode (you can google it) and they are very helpful to people of any level of experience. Optimizations as presented by many are not needed right up front and you'll learn them as you go along. Good luck!
yjftsjthsd-h 5y ago
> I would love to drop MySQL/MariaDB and go with PostgreSQL, but information like this makes me nervous that I would setup a footgun and be constantly debugging things because I am not a DB admin expert. Has MySQL gotten... a lot better, or are you just used to its quirks? I feel like the UTF encoding and ... date format? timezones? are/were all really big footguns.
- RobertRoberts 5y ago
  The encoding stinks, but it seems "fixed". (utf8mb4) I just migrated a few hundred databases between servers with the old dbs not having proper/modern encoding, and it was a bit messy to clean up. Date format... not sure, I just use datetime now, timezones I manage in app (never trusted db engine for that...) Maybe I avoided those by accident with unix time until I moved to datetime.
rrickardt 5y ago
There is reindexdb and vacuumdb in bin directory of postgresql instance. They cover 99% of maintenance use-cases when run from cron, mainly those mentioned in comments. For general purpose workload i find default settings reasonable.
- RobertRoberts 5y ago
  How would I know when to run these? Or if I need them? I know I can do research, get training, etc... but I have never done maintenance like this with MySQL/MariaDB, so it's the unknown unknowns that worry me.

33degrees 5y ago

I’m definitely not an expert and I haven’t found running postgres any more difficult than mysql. There are some differences that take a bit of getting used to, though.

status_quo69 5y ago

Like a lot of other complex systems, there's a lot of tweaking you can do in the config, but generally postgres works out of the box pretty well. There's a few gotchas on setup (postgres has a very conservative default configuration for things like memory usage and parallelism), but it's still pretty good. I'd say the biggest footgun is really not knowing about the process per connection limitation, which is why the article mentions pg bouncer. Everything else in the article is pretty geared towards setting your db up for monitoring so you can head off issues.

dano 5y ago

I converted a large MySQL DB to PostgreSQL in 2004 and never looked back. Think about how many improvements have occurred in Pg releases since that time and the availability of information. It took my team about six months to do the conversion as there were any number of type conversion problems due to programming assumptions and loose typing in MySQL at that time. There's also an excellent support community on IRC at Freenode (you can google it) and they are very helpful to people of any level of experience. Optimizations as presented by many are not needed right up front and you'll learn them as you go along. Good luck!

yjftsjthsd-h 5y ago

> I would love to drop MySQL/MariaDB and go with PostgreSQL, but information like this makes me nervous that I would setup a footgun and be constantly debugging things because I am not a DB admin expert. Has MySQL gotten... a lot better, or are you just used to its quirks? I feel like the UTF encoding and ... date format? timezones? are/were all really big footguns.

RobertRoberts 5y ago
The encoding stinks, but it seems "fixed". (utf8mb4) I just migrated a few hundred databases between servers with the old dbs not having proper/modern encoding, and it was a bit messy to clean up. Date format... not sure, I just use datetime now, timezones I manage in app (never trusted db engine for that...) Maybe I avoided those by accident with unix time until I moved to datetime.

rrickardt 5y ago

There is reindexdb and vacuumdb in bin directory of postgresql instance. They cover 99% of maintenance use-cases when run from cron, mainly those mentioned in comments. For general purpose workload i find default settings reasonable.

RobertRoberts 5y ago
How would I know when to run these? Or if I need them? I know I can do research, get training, etc... but I have never done maintenance like this with MySQL/MariaDB, so it's the unknown unknowns that worry me.