5 ms·

Better late than never - this is a welcome alternative to Amazon's RDS support for postgresql, the lack of which has been the primary reason I've kept personal

Better late than never - this is a welcome alternative to Amazon's RDS support for postgresql, the lack of which has been the primary reason I've kept personal workloads off of Azure. I'm excited to take this for a spin! Azure SQL Server has been great at work; operationally, it couldn't be simpler, and the point-in-time restore feature saved our bacon at least once. The only drawback has been... SQL Server itself. It's a solid performer, but T-SQL is a bear to work with. If MSFT can apply the same operations magic to postgres, they'll have a real winner on their hands.

thom 9y ago
What kind of things have been annoying you about T-SQL?
- pianoben 9y ago
  As a preface, let me say that SQL Server is great so far. It's rock-solid, and the query planner is wonderful. T-SQL itself is my only real gripe. That said, here are a few things that annoy me: The major bummer during development was, for me, the lack of a command to drop a schema and all the things in it. I've found the ability to `DROP SCHEMA IF EXISTS foo` to be invaluable when iterating on DB designs. In T-SQL, the alternative is to maintain a "drop-all.sql" file, which is tedious and error-prone when making use of foreign-key relationships. (more generally, postgres' DDL seems more ergonomic) I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use. Table-valued params are an adequate substitute in T-SQL for postgtresql array parameters, but driver support outside of .NET is middling at best, so our Java code gets a little dodgy when using them. Despite the more recent introduction of JSON support in SQL Server, there is no true equal to `json` or `jsonb` types. There's simply no substitute in Azure SQL for the variety of procedural languages available in postgresql; T-SQL, as a procedural language, is just not very easy to work with. I'd much prefer python, perl, or even plpgsql itself. Finally, I miss the ability to add comments on tables, columns, procs, etc, directly in SQL. Those are the things that immediately impact me; others may not be bothered at all.
  - petergeoghegan 9y ago
    > I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use. I believe that SQL Server's MERGE does not actually support what you might call UPSERT semantics: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Ra...
    - pianoben 9y ago
      Oh, I've never seen that before! We've never encountered this kind of MERGE-related race condition before, but I'm not sure that we'd recognize it if we did. Probably it would surface as a failed transaction, but our merges are pretty low-volume so it hasn't happened yet. Thanks for the link!
      - philliphaydon 9y ago
        We were using merge for a year before load increased and we ended up having deadlocks. After finding this blog and a couple of others, we removed the merge statements, deadlocks went away. I have no faith in merge anymore. :(
  - thom 9y ago
    It's interesting, I've done a lot of work on SQL Server in the past and agree with you (better query planner, better partitioning support etc). But I mostly work on Postgres day to date now though, and have been pretty productive. I actually prefer T-SQL's MERGE, partly because it's easier to get the ID out of the matched row than with Postgres, which ignores a RETURNING clause if nothing changed, and if you _force_ an UPDATE creates a new row version and increments any sequences involved. So I suppose I've been annoyed by both. Mostly agree on the rest though. In addition to the language support, just being able to create an aggregate in SQL is a bonus for me inside Postgres, without having to do it on the CLR in SQL Server. I think if MS are quick to support 10.0 when it's out, I agree this'll be a intriguing platform.
- kbenson 9y ago
  I know one of the the biggest annoyances I have is the cumbersome support for some aggregate functions (e.g. group_concat in MySQL and the numerous Postgres equivalents). Having to do a subselect, interpret it as XML and pick out parts is crazy[1]. 1: https://news.ycombinator.com/item?id=14051381 https://news.ycombinator.com/item?id=14051381
dhd415 9y ago
I'm a fan of PostgreSQL, but I've found certain T-SQL features that pg does not have such as manual transaction management within stored procedures to be very useful in certain applications.
- koolba 9y ago
  It's doable but quite hackish if you use a loopback FDW or dblink. Kind of a kludge to get autonomous transactions.
- rosser 9y ago
  Would SAVEPOINTs offer a viable alternative for meeting that need?
rattray 9y ago
Just curious, do you use .NET at work? If so, does it work well with Postgres? (eg; EntityFramework smoothness)
- bpicolo 9y ago
  I just started trying out pgsql with entityframework core the other day via http://www.npgsql.org/ http://www.npgsql.org/. Super easy to set up and get working with. I've never used EF6 , so I can't really comment there, but I've seen mention that entity framework core is missing features relative to ef6, so that's something to think about. Dealing with not-builtin types like jsonp/hstore is a bit tricky in EF so far it feels like, but doable? Doesn't seem like you can get a real first class experience with that yet though.
  - nikcub 9y ago
    > not-builtin types like jsonp/hstore is a bit tricky in EF so far it feels like, but doable? and no support for spatial data types. it's why i'm still mostly using SQLAlchemy and Python - i'm a big fan of .NET Core, though.
    - bpicolo 9y ago
      Hmm. Npgsql supports polygons in some sense, but you have to add some custom bits on top I suppose? I'm not sure how querying works though. SQLAlchemy is pretty much the only framework I've seen do this pretty well, though I have plenty of other qualms with it. Still probably my favorite ORM overall.
  - ahoka 9y ago
    We use jsonb with ef core. Just use a string property with Column(TypeName="jsonb") and deserialize with newtonsoft. I think you could use raw queries to do some advanced stuff.
    - bpicolo 9y ago
      Yeah, that's what I'm doing atm in a new project :) I just wish I could make it a JToken or whatever and give EF a db serializer. Can do similar with getters / setters I guess?
kyrra 9y ago
Google Cloud got Postgres beta a couple months back. When it was announced, the HN discussion[0] raised the question of when Azure would get it. [0] https://news.ycombinator.com/item?id=13831277 https://news.ycombinator.com/item?id=13831277

thom 9y ago

What kind of things have been annoying you about T-SQL?

pianoben 9y ago
As a preface, let me say that SQL Server is great so far. It's rock-solid, and the query planner is wonderful. T-SQL itself is my only real gripe. That said, here are a few things that annoy me: The major bummer during development was, for me, the lack of a command to drop a schema and all the things in it. I've found the ability to `DROP SCHEMA IF EXISTS foo` to be invaluable when iterating on DB designs. In T-SQL, the alternative is to maintain a "drop-all.sql" file, which is tedious and error-prone when making use of foreign-key relationships. (more generally, postgres' DDL seems more ergonomic) I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use. Table-valued params are an adequate substitute in T-SQL for postgtresql array parameters, but driver support outside of .NET is middling at best, so our Java code gets a little dodgy when using them. Despite the more recent introduction of JSON support in SQL Server, there is no true equal to `json` or `jsonb` types. There's simply no substitute in Azure SQL for the variety of procedural languages available in postgresql; T-SQL, as a procedural language, is just not very easy to work with. I'd much prefer python, perl, or even plpgsql itself. Finally, I miss the ability to add comments on tables, columns, procs, etc, directly in SQL. Those are the things that immediately impact me; others may not be bothered at all.
- petergeoghegan 9y ago
  > I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use. I believe that SQL Server's MERGE does not actually support what you might call UPSERT semantics: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Ra...
  - pianoben 9y ago
    Oh, I've never seen that before! We've never encountered this kind of MERGE-related race condition before, but I'm not sure that we'd recognize it if we did. Probably it would surface as a failed transaction, but our merges are pretty low-volume so it hasn't happened yet. Thanks for the link!
    - philliphaydon 9y ago
      We were using merge for a year before load increased and we ended up having deadlocks. After finding this blog and a couple of others, we removed the merge statements, deadlocks went away. I have no faith in merge anymore. :(
- thom 9y ago
  It's interesting, I've done a lot of work on SQL Server in the past and agree with you (better query planner, better partitioning support etc). But I mostly work on Postgres day to date now though, and have been pretty productive. I actually prefer T-SQL's MERGE, partly because it's easier to get the ID out of the matched row than with Postgres, which ignores a RETURNING clause if nothing changed, and if you _force_ an UPDATE creates a new row version and increments any sequences involved. So I suppose I've been annoyed by both. Mostly agree on the rest though. In addition to the language support, just being able to create an aggregate in SQL is a bonus for me inside Postgres, without having to do it on the CLR in SQL Server. I think if MS are quick to support 10.0 when it's out, I agree this'll be a intriguing platform.
kbenson 9y ago
I know one of the the biggest annoyances I have is the cumbersome support for some aggregate functions (e.g. group_concat in MySQL and the numerous Postgres equivalents). Having to do a subselect, interpret it as XML and pick out parts is crazy[1]. 1: https://news.ycombinator.com/item?id=14051381 https://news.ycombinator.com/item?id=14051381

dhd415 9y ago

I'm a fan of PostgreSQL, but I've found certain T-SQL features that pg does not have such as manual transaction management within stored procedures to be very useful in certain applications.

koolba 9y ago
It's doable but quite hackish if you use a loopback FDW or dblink. Kind of a kludge to get autonomous transactions.
rosser 9y ago
Would SAVEPOINTs offer a viable alternative for meeting that need?

rattray 9y ago

Just curious, do you use .NET at work? If so, does it work well with Postgres? (eg; EntityFramework smoothness)

bpicolo 9y ago
I just started trying out pgsql with entityframework core the other day via http://www.npgsql.org/ http://www.npgsql.org/. Super easy to set up and get working with. I've never used EF6 , so I can't really comment there, but I've seen mention that entity framework core is missing features relative to ef6, so that's something to think about. Dealing with not-builtin types like jsonp/hstore is a bit tricky in EF so far it feels like, but doable? Doesn't seem like you can get a real first class experience with that yet though.
- nikcub 9y ago
  > not-builtin types like jsonp/hstore is a bit tricky in EF so far it feels like, but doable? and no support for spatial data types. it's why i'm still mostly using SQLAlchemy and Python - i'm a big fan of .NET Core, though.
  - bpicolo 9y ago
    Hmm. Npgsql supports polygons in some sense, but you have to add some custom bits on top I suppose? I'm not sure how querying works though. SQLAlchemy is pretty much the only framework I've seen do this pretty well, though I have plenty of other qualms with it. Still probably my favorite ORM overall.
- ahoka 9y ago
  We use jsonb with ef core. Just use a string property with Column(TypeName="jsonb") and deserialize with newtonsoft. I think you could use raw queries to do some advanced stuff.
  - bpicolo 9y ago
    Yeah, that's what I'm doing atm in a new project :) I just wish I could make it a JToken or whatever and give EF a db serializer. Can do similar with getters / setters I guess?

kyrra 9y ago

Google Cloud got Postgres beta a couple months back. When it was announced, the HN discussion[0] raised the question of when Azure would get it. [0] https://news.ycombinator.com/item?id=13831277 https://news.ycombinator.com/item?id=13831277