I live in Żary (Tier-3 City), which is near Żagań. Quick overview:
- small city is quite cheap. Prices said by @burntoutfire are right
- there are around 2000 US soldiers hanging around here. So you won't be alone :) if someone told you that small cities in eastern europe may be intolerant (or unsafe or unwelcoming) for travelers - it's not here. Here Everyone thinks that every American met is a soldier with combat training ;)
- internet is cheap and stable. mobile is cheap and stable (comparing to US or western Europe). In almost every place you can pay using credit card.
- ecommerce is working great here. Delivery takes 1 working day. Parcel lockers are on every corner of Poland already :)
- it's close to Germany (10 miles to border, 100 miles to Berlin) so you can get two cultures in one visit.
- people in Poland are... mentally somewhere between Russians and Germans ;)
- most of the people <40yo understand english. But many of them have no practice and are just afraid to speak. The younger people you meet the bigger chance to speak english you have.
- it's the region with a lot of forests (like 70% of area). So the air quality is decent comparing to any large city.
- covid is not an issue here right now. We had 6 new cases in last 24h per 1 million people. There is no "next wave" coming. You should wear a mask in closed space, but except visits at hospital/doctors no one cares.
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.
The 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.
I 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).
You are correct about clustered indexes (1) if and only if the table clustering is selected well. Which is not always the case - especially with Azure SQL which doesn't allow for non-clustered tables or tables clustered by anything other than primary key.
Table clustering is mostly betting on "I think most access is going to happen using this columns" which might be good choice or it might not, but it's not something that should happen implicitly or by the only choice at all.
I think Markus (2) explains this much better than I do, so I'll just link his text.
(1): which is pretty stupid name, btw - Oracle call it "index organized tables" which is much clearer way to describe the concept.
PG now has materialized views, but they appear mostly useless. Their the equivalent of creating a table from a query. They don't update on every change to the source data. 9.4 I think added a way to force an update, but it just re-reads the entire source. To do it right you still have to use triggers.
Materialized views that refresh on demand are are far from useless. In my experience they are often used on Oracle for both "analytics cache" where you don't need the newest data and the select is very complex and slow, or as parts of ETL processes to decouple data transformation from loading, merging or exporting.
(this makes me realize that postgres can do some pretty cool ETL by itself - and you can express a lot of it in simple SQL terms with foreign data wrappers and materalized views. One on-demand refreshed materialized view can be able to fetch data from many different sources, transform it and provide local access)
MS SQL server, on the other hand, can't do materialized views that update on demand, which makes them hard to use on complex and large datasets, and it makes the database less predictable (inserting one row may be simple operation or it may change 10 tables, you'll never know).
Materialized views in MS SQL have rather bad support for aggregation functions and other computations - you can't compute average in them, for example, as AVG is not supported and doing SUM/COUNT is also not supported because of the division, etc.
I'd say that materialized views in MS SQL are more nice for caching or something like that, but you can't very well use them for analytics or ETL, like you usually see in Oracle, though Oracle can do both on-demand and automatic refresh, of course.
EDIT: the materialized views thing was one of my most amusing experiences with SQL Server. I tried to use materialized view instead of trigger for updating data dependent table in geographic application and I needed to compute average and sums for zoomed out map layers.
The way you add materialized view is by itself confusing - you add a view, then make index on that view, then select from it, but you have to use special keyword so the system uses the materialized view and not just the view.
I tried to use AVG but was told by the server "AVG is not supported, use SUM/COUNT". I tried SUM/COUNT but was told "COUNT is not supported, use COUNT_BIG". I tried SUM/COUNT_BIG and was finally told "using arithmetic in materialized view is not supported".
To this day, I have no idea why one error message suggested doing something that's impossible.
What's the difference between refresh on demand and just creating a table based on a SELECT? It seems like some minor syntactic sugar (which is great, I'm all for that), but not much of a feature.
You can read the materialized view while it is refreshing, you just get the old version of the data. You could do that by doing delete and insert as select in a transaction, but that's not practical for tens or hundreds of thousands of rows.
On Oracle, this is also useful because it works kind of like creating and dropping partition, instead of doing delete then insert, and that's better for the DB because of how Oracle deals with tablespace and blocks (and Oracle's truncate is non-transactional, I think), but that doesn't do much on SQL Server vs. postgres discussion :)
The 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.
I'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
Last 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?
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.
AquaDataStudio 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).
Both 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).
what 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.
I'm running SaaS startup in UE (in Poland). Because of these VAT changes, and also some consumer rights changes, I've decided to restrict my service to use only by companies.
I's not worth to me to update system to verify consumer address, create invoices with more than 10 possible vat rates and do additional accounting work.
If I would have a lot of B2C consumers, I'd just incorporate in US or other non-EU country for this. I could offer them cheaper service (no VAT) and I would have much less work to do.
I live in Żary (Tier-3 City), which is near Żagań. Quick overview:
- small city is quite cheap. Prices said by @burntoutfire are right
- there are around 2000 US soldiers hanging around here. So you won't be alone :) if someone told you that small cities in eastern europe may be intolerant (or unsafe or unwelcoming) for travelers - it's not here. Here Everyone thinks that every American met is a soldier with combat training ;)
- internet is cheap and stable. mobile is cheap and stable (comparing to US or western Europe). In almost every place you can pay using credit card.
- ecommerce is working great here. Delivery takes 1 working day. Parcel lockers are on every corner of Poland already :)
- it's close to Germany (10 miles to border, 100 miles to Berlin) so you can get two cultures in one visit.
- people in Poland are... mentally somewhere between Russians and Germans ;)
- most of the people <40yo understand english. But many of them have no practice and are just afraid to speak. The younger people you meet the bigger chance to speak english you have.
- it's the region with a lot of forests (like 70% of area). So the air quality is decent comparing to any large city.
- covid is not an issue here right now. We had 6 new cases in last 24h per 1 million people. There is no "next wave" coming. You should wear a mask in closed space, but except visits at hospital/doctors no one cares.