Are their any benefits directly related to servoy by using PostgreSQL over other RDBMS?
I’m not looking for direct comparisons between the DB engines, I know those answers. More of “Feature A in Servoy 5.xx works better with PostgreSQL because of blah blah blah”.
The great benefit for me is that PostgreSQL is OpenSource with no strings attached.
Whatever I do, there is no license fee, whether I use it with Servoy and/or Rails or whatever.
PostgreSQL also scales more than I will ever need. I lost a client in my FileMaker days because their business kept doubling in size every year…
swingman
We’ve been using MySQL for many years for many projects. The benefits of OSS is pretty clear at this point. I think Ryan is asking specifically about PG + Servoy…meaning why PG over anything else.
What would switching from MySQL to PG for example do for Ryan as it specifically related to Servoy’s features and functionality.
Thanks for your reply!
PostgreSQL turns on and off when you start and stop developer
PostgreSQL is a decent database…
We’ve been using MySQL for many years for many projects. The benefits of OSS is pretty clear at this point
PostgreSQL, besides being open source, is also free, whereas MySQL isn’t. If you bundle it with your product, you have to buy licenses.
Paul
pbakker:
PostgreSQL, besides being open source, is also free, whereas MySQL isn’t. If you bundle it with your product, you have to buy licenses.Paul
Bundle is the key word there. Basically you only have to pay if you DISTRIBUTE MySQL binaries in an automated way with your product. If its a SaaS app, MySQL is free to use as it isn’t distributed. If its on-premis, MySQL is free if you install it separately and don’t bundle it all into one automated installer.
pbakker:
PostgreSQL, besides being open source, is also free, whereas MySQL isn’t. If you bundle it with your product, you have to buy licenses.Paul
No, it’s not as simple as that.
I could repeat everything on this page, but Baron Schwartz (whom everyone who has spent any time with MySQL should know) puts it out quite well [1].
Also, MySQL is licensed under GPLv2 with has the ‘famous’ SaaS loophole that I’m sure Servoy is aware of since you chose AGPL for Servoy. So since myself, and I imagine most others here that desire to make money off their Servoy solution are following a SaaS model we have have nothing to be concerned about by using MySQL. Google, who uses MySQL benefits from the fact that they are a SaaS type offering and MySQL is licensed under GPLv2 [1].
So, I guess to get back to my post since we all know this can devolve very quickly when talking GPL. I see the Servoy BV benefit of PostgreSQL in that they can ship it, with their product, are there any others besides what David mentioned? I’ve got close to 10 years of MySQL usage under my belt, but since the application that brings home my paycheck is written in servoy I would study like a mad man to start migrating to PostgreSQL if it afforded me even a sliver of something better.
[1] http://www.xaprb.com/blog/2009/02/17/wh … l-license/
[2] http://www.zdnet.com/blog/open-source/g … ffero/2408
Hi Ryan,
You didn’t switched to Sybase either when Servoy shipped it with it, did you. Servoy did even have built-in support for creating databases so that you didn’t had to resort to the command-line.
In that sense PostgreSQL (and any other RDBMS) has less benefits with Servoy than Sybase at this point.
But Servoy was always about being able to work with any SQL backend. It had always very rudimentary support for creating database objects just because the native tools are so much better at that to begin with.
Servoy never tried to reinvent the wheel by creating also a RDBMS in their product but do need one so it only makes sense they bundled one. First it was Firebird, then it became Sybase and now Servoy went OpenSource they decided it made sense to bundle an Open Source RDBMS, which could have been a number of products but they decided on PostgreSQL. Probably also because they have been using that product themselves even before the Servoy days.
So to answer your original question, are there any Servoy specific benefits with PostgreSQL. I would say no.
Servoy can connect to all SQL vendors equally wel. Supports their datatypes equally well (or equally bad if you will)
By design it doesn’t do anything special for those SQL vendors (other than now Developer will start AND stop the database server for you).
So what was true before the Sybase to PostgreSQL migration is still true. You can use any RDBMS (that has a proper JDBC driver) with Servoy and if you are already invested in one vendor you don’t have to throw away that investment and keep using your favorite flavor of RDBMS.
Hope this answers your question.
the hesitation we have, is maintenance. I read a lot of do vacuum this, do total vaccuum that. tuning this, tuning that.
With Sybase we had NEVER to maintain any of our servers/databases. Only when we reach thousand and thousand of records we had to set indexes in some fields.
Sybase central has a very nice tool for that, that will tell you which columns need indexes and which don’t.
Also upgrading is VERY easy, just run sybase central (new version), and open the old database and reload into a new one.
All is done in very easy click & go wizards…
We also don’t know, how well postgreSQL is performing versus Sybase 11. Someone else here on the forum, mentioned that on the forum also.
What we also don’t understand is, that Servoy does not provide some kind of universal migration dump & import tool (file or xml based or something)
(all of our solutions, don’t use triggers, stored procedures, or what so ever. just plain tables, that where defined in Servoy)
We have many customers running Sybase, and let do the conversion by postgreSQL Experts, is NOT an option, and from what I understand of the latest newsletter, we can’t legally use Sybase anymore, if we get new clients, or clients that one want more licenses!.
I suggested once to use the option: export/import sample-data within Servoy, but from what I heard, that option is NOT recommmended by Servoy. Why?
Harjo:
the hesitation we have, is maintenance. I read a lot of do vacuum this, do total vaccuum that. tuning this, tuning that.
Auto Vaccum is on by default in the more recent versions of PostgreSQL, so I have never had to do any maintenance of my databases…
Hi Harjo,
Harjo:
the hesitation we have, is maintenance. I read a lot of do vacuum this, do total vaccuum that. tuning this, tuning that.
Really? Where do you read this ?
Like Christian already mentioned PostgreSQL has auto-vacuum which takes care of this process. Sure you can run it yourself if you can’t wait a few minutes for the process to do it for you.
Here you can read what vacuum really does and what it is for.
http://www.postgresql.org/docs/8.4/stat … uming.html
As for tuning. What did you read about it?
PostgreSQL has a bunch of ‘knobs’ you can tweak (see postgresql.conf) but so has Sybase and any other RDBMS.
Some have more knobs than others, i.e. you can tweak them more than others.
The main question you have I think is do I really need to tweak any of those knobs. I would say no.
First of all PostgreSQL comes with a configuration file that is setup to be good for most installs. And if you have an installer like the EDB one-click installers it even adjusts this config to the machine you are installing it on (on Mac it even fixes the poorly set shared memory settings of your system!).
So most tuning knobs are already set for you.
Now if you are really getting large databases, use some funky data-models or have some ‘interesting’ queries than yes you need to start tuning. But I think for the average Servoy user you are fine with the current setup.
Harjo:
With Sybase we had NEVER to maintain any of our servers/databases. Only when we reach thousand and thousand of records we had to set indexes in some fields.
Wait, you said you NEVER did maintenance and in the next sentence you say you did. What is it ?
Also the fact that you ‘never’ maintained your database or tuned your database server doesn’t mean that you shouldn’t. It just tells me that performance was ‘good enough’ for you.
Harjo:
Sybase central has a very nice tool for that, that will tell you which columns need indexes and which don’t.
True, Sybase Central has a nice wizard for this.
Harjo:
Also upgrading is VERY easy, just run sybase central (new version), and open the old database and reload into a new one.
All is done in very easy click & go wizards…
I think PgAdmin does a good job as well. BackUp Server, then run resulting SQL file in new database cluster. Same principle.
Harjo:
We also don’t know, how well postgreSQL is performing versus Sybase 11. Someone else here on the forum, mentioned that on the forum also.
You are expecting dramatic differences in performance ? Don’t.
It’s not that straightforward to benchmark and then generally compare those SQL vendors with each other. There is a lot more to that.
But still you could say that in general (okay in a BROAD generalization that is) the major players in the RDBMS world (MSSQL, UDB, Oracle, MySQL, PostgreSQL, Sybase, etc) are about just as fast as one another. The only difference is price tag, features and ways to make them faster on specific hardware, setups and usage. You know, the tweaking/tuning, and then you might gain what, 10% more speed or so over the other in a specific use-case?.
Also with each new version of every SQL vendor the one or the other might have an edge over the rest in a certain area so it’s really a moving target anyway.
But if you really want to know if Pg 8.4/9.0 is faster than Sybase 11 for you. Test it.
Harjo:
What we also don’t understand is, that Servoy does not provide some kind of universal migration dump & import tool (file or xml based or something)
(all of our solutions, don’t use triggers, stored procedures, or what so ever. just plain tables, that where defined in Servoy)
That universal migration tool is called ‘Servoy’. I am sure you deployed your solutions to other RDBMS backends than Sybase, right? Same principle. You only need to make sure your custom SQL is compatible with the new backend and apply indices and foreign key constraints where needed (just like you have to when deploying to a new Sybase database).
Harjo:
We have many customers running Sybase, and let do the conversion by postgreSQL Experts, is NOT an option, and from what I understand of the latest newsletter, we can’t legally use Sybase anymore, if we get new clients, or clients that one want more licenses!.
In my view expert help (no pun intended) for migration it only needed when you are using database objects other than the usual suspects like stored procedures, triggers, custom functions and that sort of things.
Harjo:
I suggested once to use the option: export/import sample-data within Servoy, but from what I heard, that option is NOT recommmended by Servoy. Why?
I don’t recall Servoy recommending against this. The only thing I know is that some developers had some migration issues with early versions of 5.2 which were fixed (as far as I know) in later versions before 5.2 (or was it 5.2.1?) was released.
I don’t see any technical reason to not recommend this approach other then what I already mentioned in this post.
Robert,
thanks for your insight! Sorry for my dummy postgreSQL questions, I’m learning every day more.
I am sure you deployed your solutions to other RDBMS backends than Sybase, right?
Nope, Sybase only!
and apply indices and foreign key constraints where needed (just like you have to when deploying to a new Sybase database).
We have never used foreign key constraints in Sybase.
about the auto vacuum, I ask this, because everytime I open a table in pgadmin, it asks me to vacuum it.
I have installed both 8.4 and 9 with the EDB installers (on mac & windows.)
and if I look, autovacuum is ON, but not enabled. or is this a wrong assumption?
[attachment=0]Schermafbeelding 2010-09-26 om 13.40.31.png[/attachment]
I’m testing also different conversion tools, like EMS SQL dump for PostgreSQL, with good results.
If I export solution in Servoy from Sybase and import into Servoy with postgreSQL, timestamps, are made in postgreSQL with this:
starttime timestamp without time zone,
if I convert the database with EMS SQL Dump from Sybase to PostgreSQL , timestampt are made in postgreSQL with this:
starttime timestamp(0) without time zone,
what is difference between: timestamp and timestamp(0)?
Good question Harjo about the timezone in Postgres.
I too have to convert from Sybase to Postgres and need to know if the setting should be with or without timezone.
Does Sybase use timezone - I am assuming yes so I am converting using the “with timezone” qualifier.
Is this correct?
I dont think that is correct, I imported a solution using servoy from sybase to postgresql and that one is also timestamp without timezone.
my question is not with or without timezone, but what the difference is between timestamp and timestamp(0)
Hi Harjo,
Harjo:
about the auto vacuum, I ask this, because everytime I open a table in pgadmin, it asks me to vacuum it.
I have installed both 8.4 and 9 with the EDB installers (on mac & windows.)
and if I look, autovacuum is ON, but not enabled. or is this a wrong assumption?
No, it looks like autovacuum is on. The not-enabled bit just means it’s commented out in the config file and thus uses the default setting (== ON).
Anyway, I tried to reproduce the thing you see by selecting a table while autovacuum is running but I don’t see this message.
Can you, in PgAdmin, select the Server node (when connected) and see in the Properties pane if autovacuum is running or not.
Harjo:
what is difference between: timestamp and timestamp(0)?
I have never seen the datatype timestamp(0). I know of the datatype timestamp though. That is a timestamp array and not the datatype you want to use with Servoy.
In what tool do you see this timestamp(0) datatype ? Can you see with PgAdmin or in psql what the real datatype is in the database ?
Hi Tom,
Thomas Parry:
Does Sybase use timezone - I am assuming yes so I am converting using the “with timezone” qualifier.Is this correct?
A quick search on the web tells me that Sybase ASA 12 has support for it but 11 doesn’t.
ASA 11: http://infocenter.sybase.com/help/index … /dtdt.html
ASA 12: http://infocenter.sybase.com/help/index … /dtdt.html
So you could convert without timezones (unless you want it with of course, your choice).
Hope this helps.
In what tool do you see this timestamp(0) datatype ? Can you see with PgAdmin or in psql what the real datatype is in the database ?
I’m seeing this both in pgadmin!
Okay, the IRC channel (#postgresql on irc.freenode.net) to the rescue.
The timestamp(0) means the number of precision it uses after the seconds (i.e. the milliseconds). The bad news is that this doesn’t truncate but it rounds UP to the nearest second!
So your logged time might actually be half a second in the future. But if that is not an issue for you then keep the datatype as it is.
Hope this helps.
oke, I checked, and Sybase also does not store the millisecond part in the database, so timestamp(0) should be oke…