Is it worth switching to Postgresql?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it worth switching to Postgresql?

Rob Jonson
Hi Folks,

I have been using Mysql for forever, and broadly it works fine for me, with two complaints
1) occasional issues with mismatches in encoding/collation on strings
2) hours lost on every mac upgrade trying to get mysql working properly again

I know that people are generally favouring postgresql these days, I'm wondering if anyone can comment on whether it's worth putting in the effort to learn a new db?

all the best

Rob

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/495925de-561d-4561-bb70-dfe9549219a8%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Is it worth switching to Postgresql?

Ariel Juodziukynas
Usually ActiveRecord takes care of most of the differences, and postresql has a lot of similarities with mysql. I guess it would be a different call if you ask about learning a completely different db engine like mongo or graphql, but learning postgresql shouldn't take too much time knowing MySQL, at least to be proficient. I even have a project that I used sqlite to prototype and postresql when deploying it to heroku for tests and I had to do nothing to make it work on both databases thanks to AR.

Also, heroku prefers postrgres for example and heroku is really popular for rails hosting.

El sáb., 14 mar. 2020 a las 10:58, Rob Jonson (<[hidden email]>) escribió:
Hi Folks,

I have been using Mysql for forever, and broadly it works fine for me, with two complaints
1) occasional issues with mismatches in encoding/collation on strings
2) hours lost on every mac upgrade trying to get mysql working properly again

I know that people are generally favouring postgresql these days, I'm wondering if anyone can comment on whether it's worth putting in the effort to learn a new db?

all the best

Rob

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/495925de-561d-4561-bb70-dfe9549219a8%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAPS3bcBqX0Y3R_63yfarpdV%2B0qesh3VGzeTzP2bV0inbGMxoOQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Is it worth switching to Postgresql?

Phil Edelbrock

I think Ariel's reply is spot-on.  Rails is basically DB agnostic, however I think the trend is that PostgreSQL is the more favored backend (except for stuff like Wordpress).  We're doing a lot more SQL procedural stuff these days with our rails apps (e.g. kicking out JSON from a single query can be literally 1,000x times faster in cases vs. asking Rails to query a model and format the same thing).  I find PostgreSQL a lot easier to admin as well, but to each their own.

Good luck!


Phil


On Mar 14, 2020, at 7:37 AM, Ariel Juodziukynas <[hidden email]> wrote:

Usually ActiveRecord takes care of most of the differences, and postresql has a lot of similarities with mysql. I guess it would be a different call if you ask about learning a completely different db engine like mongo or graphql, but learning postgresql shouldn't take too much time knowing MySQL, at least to be proficient. I even have a project that I used sqlite to prototype and postresql when deploying it to heroku for tests and I had to do nothing to make it work on both databases thanks to AR.

Also, heroku prefers postrgres for example and heroku is really popular for rails hosting.

El sáb., 14 mar. 2020 a las 10:58, Rob Jonson (<[hidden email]>) escribió:
Hi Folks,

I have been using Mysql for forever, and broadly it works fine for me, with two complaints
1) occasional issues with mismatches in encoding/collation on strings
2) hours lost on every mac upgrade trying to get mysql working properly again

I know that people are generally favouring postgresql these days, I'm wondering if anyone can comment on whether it's worth putting in the effort to learn a new db?

all the best

Rob

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/495925de-561d-4561-bb70-dfe9549219a8%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAPS3bcBqX0Y3R_63yfarpdV%2B0qesh3VGzeTzP2bV0inbGMxoOQ%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/BE54840B-0F95-4AB6-831B-90A3205AD2A6%40gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Is it worth switching to Postgresql?

Rob Jonson-2
thanks folks,

I guess I'm asking more about the practical pain points. - As you say, I trust rails to deal with most of the day-to-day.

do you end up battling to get your postgres install working after upgrading mac (or postgres?)
do you have problems with string encoding incompatibilities in obscure email addresses?
do you have a convenient gui for browsing (like SequelPro)
are there other similar annoyances, or does it 'just work'

whatever I use, I like to stick with the same db in dev & production, ( using mysql or sqlite for my tests and postgres on the server seems like it is asking for trouble)

cheers,

Rob




On Sat, 14 Mar 2020 at 15:17, Phil Edelbrock <[hidden email]> wrote:

I think Ariel's reply is spot-on.  Rails is basically DB agnostic, however I think the trend is that PostgreSQL is the more favored backend (except for stuff like Wordpress).  We're doing a lot more SQL procedural stuff these days with our rails apps (e.g. kicking out JSON from a single query can be literally 1,000x times faster in cases vs. asking Rails to query a model and format the same thing).  I find PostgreSQL a lot easier to admin as well, but to each their own.

Good luck!


Phil


On Mar 14, 2020, at 7:37 AM, Ariel Juodziukynas <[hidden email]> wrote:

Usually ActiveRecord takes care of most of the differences, and postresql has a lot of similarities with mysql. I guess it would be a different call if you ask about learning a completely different db engine like mongo or graphql, but learning postgresql shouldn't take too much time knowing MySQL, at least to be proficient. I even have a project that I used sqlite to prototype and postresql when deploying it to heroku for tests and I had to do nothing to make it work on both databases thanks to AR.

Also, heroku prefers postrgres for example and heroku is really popular for rails hosting.

El sáb., 14 mar. 2020 a las 10:58, Rob Jonson (<[hidden email]>) escribió:
Hi Folks,

I have been using Mysql for forever, and broadly it works fine for me, with two complaints
1) occasional issues with mismatches in encoding/collation on strings
2) hours lost on every mac upgrade trying to get mysql working properly again

I know that people are generally favouring postgresql these days, I'm wondering if anyone can comment on whether it's worth putting in the effort to learn a new db?

all the best

Rob

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/495925de-561d-4561-bb70-dfe9549219a8%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAPS3bcBqX0Y3R_63yfarpdV%2B0qesh3VGzeTzP2bV0inbGMxoOQ%40mail.gmail.com.

--
You received this message because you are subscribed to a topic in the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/rubyonrails-talk/HjmTuU1QoNM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/BE54840B-0F95-4AB6-831B-90A3205AD2A6%40gmail.com.


--





Hobbyist Software is a trading name of Hobbyist Software Limited. Registered office 12 Fraley Rd, Bristol, BS93BS. Registered in England. Company no:7876492

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACTOy%2BrggoJWMrmsUZiFFst5wNpKvEM7eRtzjfBxKwXtJEqvOA%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Is it worth switching to Postgresql?

Walter Lee Davis


> On Mar 14, 2020, at 12:17 PM, Rob Jonson <[hidden email]> wrote:
>
> thanks folks,
>
> I guess I'm asking more about the practical pain points. - As you say, I trust rails to deal with most of the day-to-day.
>
> do you end up battling to get your postgres install working after upgrading mac (or postgres?)

You can install Postgres as a user-space application (with an icon and everything), so this can be a non-issue.

> do you have problems with string encoding incompatibilities in obscure email addresses?

If it doesn't fit into UTF-8, then you may have a problem. Do you have a problem now, in MySQL? What encoding and charset are you using now?

> do you have a convenient gui for browsing (like SequelPro)

TablePlus is pretty nice.

> are there other similar annoyances, or does it 'just work'

Like another commenter posted, I have used it on Heroku with SQLite on the dev side, and no issues.

>
> whatever I use, I like to stick with the same db in dev & production, ( using mysql or sqlite for my tests and postgres on the server seems like it is asking for trouble)

It could be a problem, or it could be a sign that you are depending on a database feature to hide a bug. At work, we use SQLite for testing (including the CI stuff on GitLab) and Oracle on the production server. We try for 100% test coverage.

Walter

>
> cheers,
>
> Rob
>
>
>
>
> On Sat, 14 Mar 2020 at 15:17, Phil Edelbrock <[hidden email]> wrote:
>
> I think Ariel's reply is spot-on.  Rails is basically DB agnostic, however I think the trend is that PostgreSQL is the more favored backend (except for stuff like Wordpress).  We're doing a lot more SQL procedural stuff these days with our rails apps (e.g. kicking out JSON from a single query can be literally 1,000x times faster in cases vs. asking Rails to query a model and format the same thing).  I find PostgreSQL a lot easier to admin as well, but to each their own.
>
> Good luck!
>
>
> Phil
>
>
>> On Mar 14, 2020, at 7:37 AM, Ariel Juodziukynas <[hidden email]> wrote:
>>
>> Usually ActiveRecord takes care of most of the differences, and postresql has a lot of similarities with mysql. I guess it would be a different call if you ask about learning a completely different db engine like mongo or graphql, but learning postgresql shouldn't take too much time knowing MySQL, at least to be proficient. I even have a project that I used sqlite to prototype and postresql when deploying it to heroku for tests and I had to do nothing to make it work on both databases thanks to AR.
>>
>> Also, heroku prefers postrgres for example and heroku is really popular for rails hosting.
>>
>> El sáb., 14 mar. 2020 a las 10:58, Rob Jonson (<[hidden email]>) escribió:
>> Hi Folks,
>>
>> I have been using Mysql for forever, and broadly it works fine for me, with two complaints
>> 1) occasional issues with mismatches in encoding/collation on strings
>> 2) hours lost on every mac upgrade trying to get mysql working properly again
>>
>> I know that people are generally favouring postgresql these days, I'm wondering if anyone can comment on whether it's worth putting in the effort to learn a new db?
>>
>> all the best
>>
>> Rob
>>
>> --
>> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
>> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
>> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/495925de-561d-4561-bb70-dfe9549219a8%40googlegroups.com.
>>
>> --
>> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
>> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
>> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAPS3bcBqX0Y3R_63yfarpdV%2B0qesh3VGzeTzP2bV0inbGMxoOQ%40mail.gmail.com.
>
>
> --
> You received this message because you are subscribed to a topic in the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/rubyonrails-talk/HjmTuU1QoNM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to [hidden email].
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/BE54840B-0F95-4AB6-831B-90A3205AD2A6%40gmail.com.
>
>
> --
>
>
>
>
>
> Hobbyist Software is a trading name of Hobbyist Software Limited. Registered office 12 Fraley Rd, Bristol, BS93BS. Registered in England. Company no:7876492
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACTOy%2BrggoJWMrmsUZiFFst5wNpKvEM7eRtzjfBxKwXtJEqvOA%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/61EC1866-A39A-4331-A215-B98AF43716BF%40wdstudio.com.
Reply | Threaded
Open this post in threaded view
|

Re: Is it worth switching to Postgresql?

Ariel Juodziukynas
Sorry, using different db on different environment is not something I'm recommending, it was just to point out that the transition should be really easy. I do think it's faster to prototype using sqlite as long as it fits the needs, but in a more mature projects I would use the same db everywhere so I can use all the features I need.

I didn't have that many projects with postgres to talk about upgrading versions and those things, but personally I never had issues with MySQL either.

About encodings and collations, I guess it depends on the error but if you have encoding or collation errors I guess the problem is the configuration of your tables and the input and not the actual db, you may encounter problems with any db if you try to handle incompatible encodings at the same time.

I've used PgAdmin, but didn't try others to compare or recommend anything.

One more thing you can do if you don't want to struggle with updates, version, etc, is to use docker maybe, you just run the image on any system and you can replicate the actual production environment anywhere.

El sáb., 14 mar. 2020 a las 14:07, Walter Lee Davis (<[hidden email]>) escribió:


> On Mar 14, 2020, at 12:17 PM, Rob Jonson <[hidden email]> wrote:
>
> thanks folks,
>
> I guess I'm asking more about the practical pain points. - As you say, I trust rails to deal with most of the day-to-day.
>
> do you end up battling to get your postgres install working after upgrading mac (or postgres?)

You can install Postgres as a user-space application (with an icon and everything), so this can be a non-issue.

> do you have problems with string encoding incompatibilities in obscure email addresses?

If it doesn't fit into UTF-8, then you may have a problem. Do you have a problem now, in MySQL? What encoding and charset are you using now?

> do you have a convenient gui for browsing (like SequelPro)

TablePlus is pretty nice.

> are there other similar annoyances, or does it 'just work'

Like another commenter posted, I have used it on Heroku with SQLite on the dev side, and no issues.

>
> whatever I use, I like to stick with the same db in dev & production, ( using mysql or sqlite for my tests and postgres on the server seems like it is asking for trouble)

It could be a problem, or it could be a sign that you are depending on a database feature to hide a bug. At work, we use SQLite for testing (including the CI stuff on GitLab) and Oracle on the production server. We try for 100% test coverage.

Walter

>
> cheers,
>
> Rob
>
>
>
>
> On Sat, 14 Mar 2020 at 15:17, Phil Edelbrock <[hidden email]> wrote:
>
> I think Ariel's reply is spot-on.  Rails is basically DB agnostic, however I think the trend is that PostgreSQL is the more favored backend (except for stuff like Wordpress).  We're doing a lot more SQL procedural stuff these days with our rails apps (e.g. kicking out JSON from a single query can be literally 1,000x times faster in cases vs. asking Rails to query a model and format the same thing).  I find PostgreSQL a lot easier to admin as well, but to each their own.
>
> Good luck!
>
>
> Phil
>
>
>> On Mar 14, 2020, at 7:37 AM, Ariel Juodziukynas <[hidden email]> wrote:
>>
>> Usually ActiveRecord takes care of most of the differences, and postresql has a lot of similarities with mysql. I guess it would be a different call if you ask about learning a completely different db engine like mongo or graphql, but learning postgresql shouldn't take too much time knowing MySQL, at least to be proficient. I even have a project that I used sqlite to prototype and postresql when deploying it to heroku for tests and I had to do nothing to make it work on both databases thanks to AR.
>>
>> Also, heroku prefers postrgres for example and heroku is really popular for rails hosting.
>>
>> El sáb., 14 mar. 2020 a las 10:58, Rob Jonson (<[hidden email]>) escribió:
>> Hi Folks,
>>
>> I have been using Mysql for forever, and broadly it works fine for me, with two complaints
>> 1) occasional issues with mismatches in encoding/collation on strings
>> 2) hours lost on every mac upgrade trying to get mysql working properly again
>>
>> I know that people are generally favouring postgresql these days, I'm wondering if anyone can comment on whether it's worth putting in the effort to learn a new db?
>>
>> all the best
>>
>> Rob
>>
>> --
>> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
>> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
>> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/495925de-561d-4561-bb70-dfe9549219a8%40googlegroups.com.
>>
>> --
>> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
>> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
>> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAPS3bcBqX0Y3R_63yfarpdV%2B0qesh3VGzeTzP2bV0inbGMxoOQ%40mail.gmail.com.
>
>
> --
> You received this message because you are subscribed to a topic in the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/rubyonrails-talk/HjmTuU1QoNM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to [hidden email].
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/BE54840B-0F95-4AB6-831B-90A3205AD2A6%40gmail.com.
>
>
> --
>
>
>
>
>
> Hobbyist Software is a trading name of Hobbyist Software Limited. Registered office 12 Fraley Rd, Bristol, BS93BS. Registered in England. Company no:7876492
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACTOy%2BrggoJWMrmsUZiFFst5wNpKvEM7eRtzjfBxKwXtJEqvOA%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/61EC1866-A39A-4331-A215-B98AF43716BF%40wdstudio.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAPS3bcCtgM8hOF-sgLuYPBqWAp9MgZ1o923w70ShFrpkvJiG-w%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Is it worth switching to Postgresql?

San Ji
In reply to this post by Rob Jonson
No, it isn't.
If you notice only those 2 downside, you'd better stick with you current choice.
MySQL 8 has better encoding support, I am assuming you're mentioning about emoji encodings.
And you can also learn Docker for ease of development to address the other issue you have.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/c2e76d0c-7fe5-43b3-98c0-57886a498c7c%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Is it worth switching to Postgresql?

David Gleba
I also recommend running at least the DB in Docker. That should prevent most fiddling if your Mac OS changes. I use Docker for as much as I can. It ends up making things easier once you get up to speed with Docker. 

Furthermore, I just use an ubuntu virtual machine running in VirtualBox. That way did doesn't matter if you are using a Mac or Windows. The docker setup is constant in the ubuntu VM. I use Cyberduck/Filezilla/WinSCP to visualize the files and edit them locally with one click on the Mac or Windows machine.


On Saturday, March 14, 2020 at 3:45:10 PM UTC-4, San Ji wrote:
No, it isn't.
If you notice only those 2 downside, you'd better stick with you current choice.
MySQL 8 has better encoding support, I am assuming you're mentioning about emoji encodings.
And you can also learn Docker for ease of development to address the other issue you have.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/bf2ae0db-db5c-4c08-b10f-b7f49638f444%40googlegroups.com.