Re: moving from MySQL to pgsql

On Wed, Oct 10, 2012 at 1:47 AM, Vineet Deodhar <vineet(dot)deodhar(at)gmail(dot)com>wrote:

> Hi !
> At present, I am using MySQL as backend for my work.
> Because of the licensing implications, I am considering to shift from
> MySQL to pgsql.
> Typically, my apps are multi-user, web based or LAN based.
>
> 1) Read over the internet that ---
> Postgres is not threaded, but every connection gets it's own process. The
> OS will distribute the processes across the processors. Basically a single
> connection will not be any faster with SMP, but multiple connections will
> be.
>
> MySQL is multi-threaded server so it can use many processors. A separate
> thread is created for each connection.
> source:
> http://dcdbappl1.cern.ch:8080/dcdb/archive/ttraczyk/db_compare/db_compare.html#Comparison+of+Oracle%2C+MySQL+and+Postgres+DBMS
>
> In what way it might affect my app performance?
>

Not much. MySQL doesn't support intraquery parallelism to my knowledge.
You will get extra robustness due to process isolation however. There
might be some slight costs due to shared memory management overhead but
these are probably insignificant compared to other factors. PostgreSQL
has an excellent optimizer and the on-disk layout is completely different.
This will dwarf any changes due to threads vs queries. However be
prepared to rethink your indexing strategies.

>
> 2) I run MySQL from a USB stick.
> There is no installation required (on WinXP.). (not tried on Ubuntu)
> Is it the same for pgsql?
>
> You would want a user account created because PostgreSQL won't run as an
administrator but otherwise, yes.

> 3) Can I simulate MySQL's TINYINT data-type (using maybe the custom data
> type or something else)
>

I would use smallint (2 byte int), but if you have to "char" with quotes
should give you a one byte field. I don't know about utf8 tules on it
though.

Best Wishes,
Chris Travers