5 ms·
I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba
by adekmm 11y ago
I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons:
MS SQL:
- The tools included like Management Studio are just great. This is totally next level to the Postgres tools.
- Using multiple CPU cores for a single query is really helpfull in my scenario.
- Easy continous backup to the cloud.
- Included Integration Services, Analysis services are also easy and enugh for my usage.
Postgres:
- Is running on linux, to it's cheaper. Even when we use SQL Server as Bizspark (for free now), the Azure Windows VM for it costs us much more, than VM with Linux. And of course when we want to cluster our DB, Postgres is even cheaper.
- Great JSON support. There are parts of our project where it's helpfull.
- Better configurability, like WAL, checkpoints etc. We have much better write performance on postgres than in sql server (probably just our case).
The other things really do not much difference. Both DB's can be extended, and extensions may be written in many languages. Both achieve great overall performance, both have strong community and a lot of documentation.
- MagicWishMonkey 11y agoThe closest thing to SQL Server Management Studio I've found is Navicat. It's not as solid as Management Studio, and it's incredibly expensive, but it is much better than most of the other tools out there. If you can expense the cost of the license you should definitely check it out.
- sleepyhead 11y agoIf you are on Mac check out https://eggerapps.at/postico/ https://eggerapps.at/postico/
- MagicWishMonkey 11y agoThis looks pretty nice, thanks!
- kidmenot 11y agoAgreed on Navicat. pgAdmin doesn't quite cut it just yet. Which sometimes made me wonder if, with Postgres becoming more popular by the day, there would be a market for a management tool cheaper than Navicat.
- msl09 11y agoPostgresql have such an amazing documentation and CLI that I prefer to use it over pgAdmin.
- toolz 11y agoThis is why I prefer postgres. Jumping into the CLI is easy and much quicker than mousing around some clustered up feature overloaded GUI.
- mmgutz 11y agoLast I tried Navicat it didn't offer much over pgAdmin. The UI was more streamlined but that wasn't reason enough to switch to it. What features in navicat are must have and not found in pgAdmin3?
- danneu 11y agoI use both Navicat and pgAdmin3 these days. One thing I like about pgAdmin3 is the ability to only execute the query you have highlighted and also the scratchpad. It's nice for exploratory querying. In Navicat, I like switching between various tabs where pgAdmin3 makes me switch between windows. Both have some hilarious idiosyncrasies. When one locks up for no reason, I generally just alt-tab to the other. Recently I was using pgAdmin3 and set my production database color to red. I happened to notice that the color didn't repaint back to green when I switched to my dev database. And Navicat seems to consistently hang whenever the network dies or burps mid-action, requiring a command-line kill. Idiosyncrasies. :)
- ironchef 11y agoAquaDataStudio was my choice at the time. Expensive, but worked on pretty much all the DBMS I had to use at the time (which included oracle, pg, teradata, mssql, mysql to name a few).
- _JamesA_ 11y agoI have found DbVisualizer [1] to be a superior tool and it supports almost every database in existence, Postgres and SQL Server included. Plus it's completely cross-platform and runs on Linux, Windows and OS X. [1]: http://www.dbvis.com http://www.dbvis.com
- MagicWishMonkey 11y agoThat's pretty slick, and it's much more competetively priced than Navicat.
- edgarvm 11y agoTOAD DBA Suite for SQL Server is nice too, very expensive though
- dphase 11y agoI've been a fan of Aqua Data Studio for years and it doesn't seem to get mentioned often. Cross platform and supports a myriad of dbs. http://www.aquafold.com/aquadatastudio.html http://www.aquafold.com/aquadatastudio.html
- viggity 11y agowhat I find sad is how much better Sql Server Management Studio is compared to the competition and yet how completely effin' frustrating it is. If They spent 10% as much effort on SSMS as they do on Visual Studio it would be ridiculously amazing.
- MichaelGG 11y agoYeah, like the Outlook team, someone could learn how to make the UI continue to pump messages, while waiting for remote operations to complete...
- ssmoot 11y agoThe write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introduced in v9.3). No view update support. No partitioned view support. No sane backup/restore process. It's a great database if your primary concern is licensing cost. But if your primary concern is operational cost and even just multi-gigabyte data sets it's really frustratingly rudimentary compared to what MSSQL delivered over a decade ago. But that's just me. It's free. And I'm thankful for that. I just find it really frustrating that PostgreSQL supports querying on JSON, but doesn't support backing up and restoring the database in binary format.
- deleted 11y ago[deleted]
- hahainternet 11y ago> No view update support 9.3 > No partitioned view support Inheritance (ish) > No sane backup/restore process wat > doesn't support backing up and restoring the database in binary format http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.html http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.h... is half of what you want, but I expect you're wanting something I don't expect.
- ssmoot 11y agoI want to be able to say: BACKUP clientdb_a, get a clientdb_a.bak file, and be able to restore it without statement generation. Which seems like a reasonable baseline expectation if you were to ask a lay-person what they'd imagine a database backup to be. pgbasebackup can't do that without some serious constraints (like, you only have a single database in your PGDATA).
- dragonwriter 11y ago> PostgreSQL has -no- materialized views (I stand corrected! Introduced in v9.3). No view update support. Like materialized views, automatic update support for simple views was introduced in 9.3.
- MichaelGG 11y agoBoth TSQL and pgsql miss stuff. But yesterday I wanted to turn a (customerid, key, value) table into a JSON object, one per customer. Simple one liners in other systems, but a mess in TSQL, requiring "FOR XML PATH" hackery. There's also an unsupported/deprecated/undocumented trick of updating a var inside a select (select @a = @a + column).
- asnyder 11y agoEMS SQL Manager for PostgreSQL is much better than SQL Management Studio IMHO. Same goes for their MSSQL tool.