Обсуждение: Postgres for SQL Server users
Next month I'll be making a presentation about Postgres to a SQL Server crowd in L.A. at their SQL Saturday event.
I was wondering if anyone has any tips that are specific for SQL Server users? Best features? Known issues? Common rebuttals?
Thanks,
Igal
> I was wondering if anyone has any tips that are specific for SQL Server users? Best features? Known issues? Common
rebuttals?
Are you talking about SS to PG migration.
Generally SQLServer shops use SS specific functions and T-SQL heavily since they provide very good functionality.
For example:
1, convert function is used heavily in SQL to format various datetime formats.
2. SS does case insensitive searches. Converting it to PG involves modifying the SQL. Not an easy job to do.
3. SS has 3 part structures. DB.SCHEMA.TABLE which can be used in SQL. PG can go only up to 2 part SCHEMA.TABLE.
3 part is possible, but using FDW or other cumbersome mechanism.
Ravi,
On Mon, May 6, 2019 at 12:28 PM Ravi Krishna <ravi_krishna@aol.com> wrote:
> I was wondering if anyone has any tips that are specific for SQL Server users? Best features? Known issues? Common rebuttals?
Are you talking about SS to PG migration.
Generally SQLServer shops use SS specific functions and T-SQL heavily since they provide very good functionality.
For example:
1, convert function is used heavily in SQL to format various datetime formats.
2. SS does case insensitive searches. Converting it to PG involves modifying the SQL. Not an easy job to do.
3. SS has 3 part structures. DB.SCHEMA.TABLE which can be used in SQL. PG can go only up to 2 part SCHEMA.TABLE.
3 part is possible, but using FDW or other cumbersome mechanism.
Thank you. I am referring more to features, functionality, scalability, etc.
I want to get them interested in giving Postgres a try instead of SQL Server. Surely the savings on licenses can be substantial (I have no idea how much, TBH), but I want to instill confidence in them that anything they do with SQL Server can be done with Postgres.
Igal
On 5/6/19 2:47 PM, Igal Sapir wrote: > but I want to instill confidence in them that anything they do with SQL > Server can be done with Postgres. Right off the top of my head, here are some things you can't (easily and trivially) do in Postgres: - Transparent Data Encryption - Block level full, differential and log backups that do more than same-version DR. -- Angular momentum makes the world go 'round.
Hi Igal,
One relevant comment I found interesting a couple of years ago...
A New Zealand Govt agency was installing an institutional GIS system (several thousand potential users). It supported different back-end spatial databases. Previous installs of this system for other clients had used MS SQL Server, this was the first that was installed on a Postgres/Postgis back end.
As it was about to go into action as a live, production system, I was speaking to one of the team doing the installation, and asked how they had found working with Postgres instead of SQL Server. The reply was worth a few formal benchmarks:
"It's so easy to connect to and sh*t it's fast!"
Might be a useful comment for your presentation :-) A few years on & it is still working fine...
Cheers...
Brent Wood
On Tuesday, May 7, 2019, 7:21:40 AM GMT+12, Igal Sapir <igal@lucee.org> wrote:
Next month I'll be making a presentation about Postgres to a SQL Server crowd in L.A. at their SQL Saturday event.
I was wondering if anyone has any tips that are specific for SQL Server users? Best features? Known issues? Common rebuttals?
Thanks,
Igal
more: 1. No db level backup/restore in PG, at least no easy way. 2. No cross db query.
I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.
The lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.
> I think the main "gotcha" when I moved from SQL Server to Postgres was > I didn't even realize the amount of in-line t-sql I would use to just get stuff done > for ad-hoc analysis. T-SQL is an exceptionally powerful SQL based language. Add to it, the many functions SS has. I recently had to write an equivalent of UNPIVOT. > Postgres doesn't have a good way to emulate this. Yeah, no where close.
Ravi Krishna schrieb am 06.05.2019 um 23:56: > I recently had to write an equivalent of UNPIVOT. UNPIVOT is actually quite easy with Postgres: https://blog.sql-workbench.eu/post/unpivot-with-postgres/ Thomas
On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback@gmail.com> wrote:
I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.
Just ruminating here, and this has probably already been discussed in the past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any serious problem with creating/dropping functions like you mentioned.
-Michel
The lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.
I have to agree on the geospatial (GIS) features.
I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS.
The PostGIS ecosystem of open source / 3rd party tools is also far bigger, for anything to do with mapping. Openstreetmaps.org stores their world dataset on Postgresql / PostGIS, and there a ton of mostly open source-based tools and organizations that work with it or any other PostGIS data to provide a complete GIS solution.
My first sS implementation had me backing out of storing geographic points in the relevant SQL Server datatype as the performance hit during loading was just too big. Doing the same thing in Postgresql / PostGIS is nardly noticeable.
Another feature in Postgres is that you are not restricted to just plpgsql as an internal procedural language.
I am not an expert, but it also seems far easier to create, install and work with major extensions to Postgresql than SQL Server. I found installing the GIS featureset in SS to be a bit of a pain back oin the day..
On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier.michel@gmail.com> wrote:
On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback@gmail.com> wrote:I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.Just ruminating here, and this has probably already been discussed in the past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any serious problem with creating/dropping functions like you mentioned.-MichelThe lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.
Brent,
On Mon, May 6, 2019 at 1:44 PM Brent Wood <pcreso@yahoo.com> wrote:
Hi Igal,One relevant comment I found interesting a couple of years ago...A New Zealand Govt agency was installing an institutional GIS system (several thousand potential users). It supported different back-end spatial databases. Previous installs of this system for other clients had used MS SQL Server, this was the first that was installed on a Postgres/Postgis back end.As it was about to go into action as a live, production system, I was speaking to one of the team doing the installation, and asked how they had found working with Postgres instead of SQL Server. The reply was worth a few formal benchmarks:"It's so easy to connect to and sh*t it's fast!"Might be a useful comment for your presentation :-) A few years on & it is still working fine...
This is a great quote, but can I quote a real source? Otherwise it's just something I heard from Brent on the mailing list.
Best,
Igal
Ron,
On Mon, May 6, 2019 at 12:54 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 5/6/19 2:47 PM, Igal Sapir wrote:
> but I want to instill confidence in them that anything they do with SQL
> Server can be done with Postgres.
Right off the top of my head, here are some things you can't (easily and
trivially) do in Postgres:
- Transparent Data Encryption
- Block level full, differential and log backups that do more than
same-version DR.
I'll be sure to try and avoid these topics then lol.
I am trying to make the opposite case.
Thanks,
Igal
--
Angular momentum makes the world go 'round.
Tony,
On Mon, May 6, 2019 at 10:35 PM Tony Shelver <tshelver@gmail.com> wrote:
I have to agree on the geospatial (GIS) features.I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS.The PostGIS ecosystem of open source / 3rd party tools is also far bigger, for anything to do with mapping. Openstreetmaps.org stores their world dataset on Postgresql / PostGIS, and there a ton of mostly open source-based tools and organizations that work with it or any other PostGIS data to provide a complete GIS solution.My first sS implementation had me backing out of storing geographic points in the relevant SQL Server datatype as the performance hit during loading was just too big. Doing the same thing in Postgresql / PostGIS is nardly noticeable.Another feature in Postgres is that you are not restricted to just plpgsql as an internal procedural language.I am not an expert, but it also seems far easier to create, install and work with major extensions to Postgresql than SQL Server. I found installing the GIS featureset in SS to be a bit of a pain back oin the day..
GIS is a good feature but it's a niche feature, so while I'll mention it with extensions I am looking for more general-purpose comparisons and areas where Postgres is as-good or better than SQL Server.
Thanks,
Igal
For me, another very useful featureset in Postgres is the extensive set of datatypes and functions, including the strong JSONB support.
Also, i would focus on the widespread support of Postgresql by services such as Amazon, Google, Heroku,
Another place to focus on would be the really extensive list of extensions for datatypes, languages, specialized functions and so on. Things like PostGIS (already mentioned), PipelineDB and so on, plus Postgres-based things like Timescale, Citusdata and so on.
On Tue, 7 May 2019 at 07:35, Tony Shelver <tshelver@gmail.com> wrote:
I have to agree on the geospatial (GIS) features.I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS.The PostGIS ecosystem of open source / 3rd party tools is also far bigger, for anything to do with mapping. Openstreetmaps.org stores their world dataset on Postgresql / PostGIS, and there a ton of mostly open source-based tools and organizations that work with it or any other PostGIS data to provide a complete GIS solution.My first sS implementation had me backing out of storing geographic points in the relevant SQL Server datatype as the performance hit during loading was just too big. Doing the same thing in Postgresql / PostGIS is nardly noticeable.Another feature in Postgres is that you are not restricted to just plpgsql as an internal procedural language.I am not an expert, but it also seems far easier to create, install and work with major extensions to Postgresql than SQL Server. I found installing the GIS featureset in SS to be a bit of a pain back oin the day..On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier.michel@gmail.com> wrote:On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback@gmail.com> wrote:I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.Just ruminating here, and this has probably already been discussed in the past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any serious problem with creating/dropping functions like you mentioned.-MichelThe lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.
Igal Sapir schrieb am 07.05.2019 um 07:58: > GIS is a good feature but it's a niche feature, so while I'll mention > it with extensions I am looking for more general-purpose comparisons > and areas where Postgres is as-good or better than SQL Server. I have a comparison of various DBMS products on my homepage: http://www.sql-workbench.eu/dbms_comparison.html Note that I don't claim that list is complete! I do try to keep it up-to-date and correct, but as I am primarily a Postgres and (unfortunately) Oracle user, there mightbe inaccuracies for other DBMS products. I collected several blogpost regarding that topic. Be aware: most of them are pretty old! * https://www.datachomp.com/archives/top-10-reasons-i-like-postgres-over-sql-server/ * https://facility9.com/2011/12/ten-reasons-postgresql-is-better-than-sql-server/ * http://blog.ringerc.id.au/2011/12/postgresql-great-even-when-you-can-see.html * http://www.alandmoore.com/blog/2013/02/28/five-reasons-why-i-choose-postgresql/ * https://www.pg-versus-ms.com/ (Attention! the site uses an expired SSL certificate) Thomas
> Next month I'll be making a presentation about Postgres to a SQL > Server crowd in L.A. at their SQL Saturday event. > > I was wondering if anyone has any tips that are specific for SQL > Server users? Best features? Known issues? Common rebuttals? PosgreSQL is more than a "database", it's more of an ecosystem. The combination of open source,well-described interfacs, and simplicity of "create extension" allows us all to develop and add features, functionality to PG. One example: "exclude using gist" with timestamp ranges make time-series databases nearly trivial. PostGIS is another. Another is that PG is written and developed by people who actually use it. Wander through psql: things like \gset and \gexec with format() make introspective tasks quite doable with SQL. pgadmin is another nice "addon" that exists partly because PG was designed from the ground up to support external access. Hell, throw in EnterpriseDB if you want even more addons; there aren't third- party SQL Server support distributions (I'm a capatilist: competition can be Good Thing). However much people rag on it, a *tunable* vacuum system is quite helpful. The "I don't to worry about that with <other DB>" starts to fall apart when the system starts unncessary cleanups in the middle of a large daily dump... Being able to control the process makes it a whole lot easier to forget. Foreign Data Wrappers are a nice feature, the byproduct of a system that was designed for open access. Instead of waiting for MS to decide that any one external resource is profitable, we have quite a few quite usable interfaces that support what people need. The combination of extensions and FDW also gives companies to add whatever they need when they need it. If anyone tells you that "X isn't available in PG" the simplest answer is "Fine, then add it!" For the cost of a MS product license you can pay someone to write whatever you want as an extension or special-purpose FDW. Pay them for support and you can have whatever you need. Which gets back to the ecosystem: The PG *product* includes a level of flexability not available in SQL Server -- or Oracle for that matter. SQL Server was heavily influenced by Sybase, which was based on Ingres, which worked because it was an open, flexible platform. Maybe you could just tell them PG gives them the chance to evolve full-circle :-) -- Steven Lembark 3920 10th Ave South Workhorse Computing Birmingham, AL 35222 lembark@wrkhors.com +1 888 359 3508
I have not used SS for spatial data, but I don't have a Postgres database without Postgis installed. The OSGEO ecosystem and synergies with other FOSS GIS tools is fantastic.
And it does not stop with the Postgis extension. For time series data (anything from fleet management to sensor data) Postgres has the (new) TimescaleDB extension. I ran this very effectively with a 600,000,000 record database of sensor readings from a research vessel - on a $400 laptop (with an SSD) for testing/prototyping. The sensor data was stored in Timescaledb Hypertables & the location data in Postgis geometry columns in those tables. Significantly better performance than native Postgres.
Also consider language support for database functions... pl/R supports some very nice capabilities, especially supporting websites. Instead if running a Postgres query to return the data to plot via the web page, or storing static plots in your CMS that need updating when you get new data, you can use Postgres functions in pl/R to render the plot of the data in a file, and return the name of the file. The web site does no rendering, just invokes the SQL & displays the file that is returned. So the DB can return the data and/or the graphic. Back up your database & back up your functions. This paradigm can work very effectively...
Generally, the FOSS ecosystem around Postgres offers an incredible array of tools and capabilities that I don't think any other db - FOSS or not - can provide. I have had limited exposure to Oracle, SQL Server, Sybase, Empress, Teradata, Netezza, DB2, Sqlite/Spatialite, Interbase & Informix. Of these, Postgres & Sqlite3 (which one depends on use cases) are all I use these days.
On Tuesday, May 7, 2019, 5:36:00 PM GMT+12, Tony Shelver <tshelver@gmail.com> wrote:
I have to agree on the geospatial (GIS) features.
I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS.
The PostGIS ecosystem of open source / 3rd party tools is also far bigger, for anything to do with mapping. Openstreetmaps.org stores their world dataset on Postgresql / PostGIS, and there a ton of mostly open source-based tools and organizations that work with it or any other PostGIS data to provide a complete GIS solution.
My first sS implementation had me backing out of storing geographic points in the relevant SQL Server datatype as the performance hit during loading was just too big. Doing the same thing in Postgresql / PostGIS is nardly noticeable.
Another feature in Postgres is that you are not restricted to just plpgsql as an internal procedural language.
I am not an expert, but it also seems far easier to create, install and work with major extensions to Postgresql than SQL Server. I found installing the GIS featureset in SS to be a bit of a pain back oin the day..
On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier.michel@gmail.com> wrote:
On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback@gmail.com> wrote:I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.Just ruminating here, and this has probably already been discussed in the past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any serious problem with creating/dropping functions like you mentioned.-MichelThe lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.
Brent,
On Tue, May 7, 2019 at 12:42 PM Brent Wood <pcreso@yahoo.com> wrote:
I have not used SS for spatial data, but I don't have a Postgres database without Postgis installed. The OSGEO ecosystem and synergies with other FOSS GIS tools is fantastic.And it does not stop with the Postgis extension. For time series data (anything from fleet management to sensor data) Postgres has the (new) TimescaleDB extension. I ran this very effectively with a 600,000,000 record database of sensor readings from a research vessel - on a $400 laptop (with an SSD) for testing/prototyping. The sensor data was stored in Timescaledb Hypertables & the location data in Postgis geometry columns in those tables. Significantly better performance than native Postgres.
Excellent information with impressive numbers.
Also consider language support for database functions... pl/R supports some very nice capabilities, especially supporting websites. Instead if running a Postgres query to return the data to plot via the web page, or storing static plots in your CMS that need updating when you get new data, you can use Postgres functions in pl/R to render the plot of the data in a file, and return the name of the file. The web site does no rendering, just invokes the SQL & displays the file that is returned. So the DB can return the data and/or the graphic. Back up your database & back up your functions. This paradigm can work very effectively...
Is there any tutorial/example code for rendering pl/R images on a website? Cool feature
Generally, the FOSS ecosystem around Postgres offers an incredible array of tools and capabilities that I don't think any other db - FOSS or not - can provide. I have had limited exposure to Oracle, SQL Server, Sybase, Empress, Teradata, Netezza, DB2, Sqlite/Spatialite, Interbase & Informix. Of these, Postgres & Sqlite3 (which one depends on use cases) are all I use these days.
Yep. agreed.
Thanks,
Igal
On Tuesday, May 7, 2019, 5:36:00 PM GMT+12, Tony Shelver <tshelver@gmail.com> wrote:I have to agree on the geospatial (GIS) features.I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS.The PostGIS ecosystem of open source / 3rd party tools is also far bigger, for anything to do with mapping. Openstreetmaps.org stores their world dataset on Postgresql / PostGIS, and there a ton of mostly open source-based tools and organizations that work with it or any other PostGIS data to provide a complete GIS solution.My first sS implementation had me backing out of storing geographic points in the relevant SQL Server datatype as the performance hit during loading was just too big. Doing the same thing in Postgresql / PostGIS is nardly noticeable.Another feature in Postgres is that you are not restricted to just plpgsql as an internal procedural language.I am not an expert, but it also seems far easier to create, install and work with major extensions to Postgresql than SQL Server. I found installing the GIS featureset in SS to be a bit of a pain back oin the day..On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier.michel@gmail.com> wrote:On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback@gmail.com> wrote:I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.Just ruminating here, and this has probably already been discussed in the past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any serious problem with creating/dropping functions like you mentioned.-MichelThe lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.