What looks compelling about the PostgreSQL offering as compared to AWS RDS is that it looks like you get a PostgreSQL cluster rather than a single database in a shared cluster. At least reading the documentation for creating a DB implies that (https://docs.microsoft.com/en-us/azure/postgresql/quickstart...).
For me, it might remove some blockers to moving some clients to a cloud managed database solution that had always been there with AWS RDS. If that follows through and pays off for having a proper PostgreSQL super user in the database environment available for use (what I really want), that can make certain things much more do-able (brings new things you have to watch out for, too, but... pick your battles).
Thank you for your feedback. We indeed offer a whole server (database cluster) and you can create multiple databases in it and have full control of the resources allocated to that cluster.
The admin user provisioned for the server is not a full superuser, but it is close to that. We are trying to find the right set of permissions to provide to our customers without jeopardizing our ability to manage this server for you. Please submit your feedback here: https://feedback.azure.com/forums/597976-azure-database-for-... if you see a problem with the permissions.
macieksarnowicz is shadowbanned for whatever reason, but he answered:
Thank you for your feedback. We indeed offer a whole server (database cluster) and you can create multiple databases in it and have full control of the resources allocated to that cluster.
The admin user provisioned for the server is not a full superuser, but it is close to that. We are trying to find the right set of permissions to provide to our customers without jeopardizing our ability to manage this server for you. Please submit your feedback here: https://feedback.azure.com/forums/597976-azure-database-for-... if you see a problem with the permissions.
We do not support automatic autoscale at this point, but it is definitely a feature we will be considering in the future. For now, you can scale on-demand in the portal or using Azure CLI.
it cannot do autoscale up and down now. But i think you can query for usage metrics and trigger a scale up/down with azure cli/powershell. This is a good feature we should have later in the public preview
What makes you think that in RDS you are part of a "shared cluster"? I believe that with RDS, your database instance runs on EC2 instance(s) dedicated to you.
The nature of the limitations AWS impose on the administrative database user account, especially because they are focused on an individual database rather than the cluster.
Shared or not is absolutely an assumption on my part. If they're spinning up an individual cluster for you, then some of the restrictions on RDS don't make much sense to me. They make more sense if it were shared.
Unfortunately, for most of the clients I work with, the limits (shared or not) are show-stoppers so I've only dealt with building a system backed by AWS RDS/PostgreSQL once and the need to dive into the underlying mechanics just wasn't there at the time.
Indeed, not only can one create multiple databases on one RDS instance, but it's also OK start an RDS instance with no user database at all, and create it later.
We're heavily using multiple databases on a single Postgres RDS. Yes, occasionally it's annoying not having a true superuser, but now they've improved it so you can still have 'fake superuser' reserved connections it's ok.
Unfortunately, still not available in the India datacenter.
This is going to be huge in India. Microsoft has massive mindshare in India with the govt because of the regulatory landscape.
I have it on firsthand info that several banks in India are going to make their first ever transition to the cloud on Azure because of Microsoft's willingness to go the extra mile around whatever security dances that the banks want them to do.
Plus Azure has India datacenter which takes of the PII problem for healthcare and financial data.
It is truly unfortunate that the management console has the worst UX ever.
Our plan is to be in all the Azure regions before we GA the service. We will be gradually expanding our presence over the next months, based on available capacity. Please speak up if you have a region where we are not present yet.
I think you have not suffered Azure then. You will cry in relief when you are allowed to go back to AWS UI.
The information architecture is fairly broken. And the UI that slides is even worse - the only reason a UI like that exists because it's highly stateful: you need to remember what you did in the previous screen. And with the whole information architecture, it's crazy hard.
If Azure builds a Google cloud like UI and calls it azure-lite, I would throw all my money at them.
Fair enough.. I still consider AWS worse than Azure, and yeah, I've used both.. only dipped my toes into Google's but so far it's been better than either... however, if you're automating, it makes much less difference.
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.
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.
I just started trying out pgsql with entityframework core the other day via 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.
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.
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?
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.
I write JVM-based server code, so I can't really comment on the experience of using postgresql on .NET. I think that I tried it a few years back, and had an OK time of it, but I wasn't using EF. Sorry!
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.
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.
> the alternative is to maintain a "drop-all.sql" file
If you really want to drop a whole schema (i.e. all its constituant objects) and recreate, then you could write a procedure to do so by cursoring through sys.objects (joined to other system tables as needed, sys.schemas to filter by name rather than schema ID for a start) and issuing "DROP <OBJECT> <name>" for each via "EXEC (@VarContainingDropStatement)". Having a built-in might be nice, but writing your own reusable procedure (rather than a more hard-coded script) should not be difficult.
> tedious and error-prone when making use of foreign-key relationships
If following the "cursor through sys.objects" approach that can be managed by making sure FK constraints are dropped first, either by forcing the objects listed out of the cursor into a particular order or by a separate loop first.
> T-SQL has MERGE, which is vastly more complicated and verbose.
I'm with you there. It also has some odd locking issues that you need to be aware of before they bite your rear.
I'd agree generally that T-SQL has some room for improvement. Date and string manipulation functions are sometimes lacking I find, though as I use little other than SQL Server I can't comment on other environments being better/worse. Being able to easily define a new aggregate procedure as I believe you can in postgres is something I could have made good use of in the past.
> 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.
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.
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 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].
Just a word of caution: be very careful to check the latency between the DB tier and the VM tier.
We had to abandon a multi-month deployment to Azure because the latency was too high and it caused all sorts of issues with our app. To be honest, our DB access approach wasn't great (heaps of really small requests), but we couldn't re-architect it in the timeframe we had. We were also doing this in Sydney, so maybe it was just a localised issue (the Australian data centres are relatively new).
Anyway, hopefully they have that sorted - we need more competition!
It seems it's still better to run mySQL on a VM if you're running multiple databases for smaller sites. But for single sites that need to scale, it's way better than ClearDB, which is hot garbage if you ask me.
If you're asking why I think it's still better to run a VM for multiple databases, my answer is pricing.
If you're asking why I think ClearDB is hot garbage, it's because of pricing, in ability to scale down (you literally cannot scale down ClearDB in Azure), connection limits on the DB that make no sense with the pricing, etc.
No, this is a PaaS offering that includes high-availability, fault-tolerance and disaster recovery, and uses a customized version of PostgreSQL behind the scenes.
To me (and, i think, in general product naming conventions), "Azure for Postgres" would mean some sort of azure service plugin that you can add to any arbitrary postgres installation. "Postgres for Azure" would mean Postgres support on Azure's PaaS.
So is a "compute unit" the same as the "Azure Compute Units" that are used to measure VMs? 800 ACU roughly comes out to 4 cores. At $0.78/hr, that is hardly comparable to the RDS offerings. Didn't see an indication of memory.
Much like SQL Server DTUs and DWUs, the spacebucks measurements only seem to useful for hiding the actual price comparisons.
At least they stopped the DTU/DWU non-sense for these services.
Compute units are a measure of computing throughput for your instance with pre-configured amount of RAM. We do not count hyperthreads as core. So 100 compute units is 1 core, 200 compute units is 2 cores, etc.
/works at Microsoft and managed PostgreSQL service /
The landing page says "With Azure Database for PostgreSQL, you can scale the performance of your database with no application downtime". I don't see an option to scale the Postgres database in my Azure Portal.
You can scale by clicking on the Pricing tier. However note that scaling operations currently only support within a service tier. i.e, scaling up/down within Basic and scaling up/down within standard. Ability to scale across service tiers will come soon. /works at Microsoft and the managed PostgreSQL service /
The application should have logic to re-try connections. When you scale, there is a brief moment ~30-45seconds when the app wont be able to connect to the database and then resumes if you re-try connection.
/I work at Microsoft on the managed PostgreSQL service/
How do you accomplish no application downtime if for 45 seconds you can't connect to the database?
In background processes you might get away with retrying connections for a minute but most users will probably consider your site broken if page load time is 45 seconds.
For me, it might remove some blockers to moving some clients to a cloud managed database solution that had always been there with AWS RDS. If that follows through and pays off for having a proper PostgreSQL super user in the database environment available for use (what I really want), that can make certain things much more do-able (brings new things you have to watch out for, too, but... pick your battles).