Hacker News Reader

Comment by rtpg | Hacker News Reader

For those stuck on older versions of Postgres, I highly recommend paying the downtime to upgrade. Going from 9.x to 11 will get you a measurably large performance gain for free.

View on HN

Out of interest (SQL Server guy mainly, so only partly keep up with what other engines are doing), what changes significantly affect performance (without making changes to your own code/configuration to make use of new features) in 10.x & 11.x?

The query planner in 10 got a lot better at enforcing row-level security constraints efficiently for some common scenarios, like 10-20x speedups. See https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1... and the linked mailing list thread for more info, if you're curious.

One of the huge ones was the ability to use > 1 cpu core for big aggregations or huge select queries. That and a massively better query planner.

There is usually a bunch of small improvements in every release, and those can add up over time. In Postgres 10 and 11 a lot of stuff happened related to parallel queries, and many more queries can be run in parallel now. 11 added a JIT compiler to the query planner, but I'm not sure whether that is enabled by default yet.

It kinda bugs me that people say "SQL Server" to mean "Microsoft SQL Server". I mean, there are other sql servers.

SQL Server is a registered trademark of Microsoft at least in the US and I’m sure other jurisdictions. Using SQL Server to refer to Microsoft’s relational database product is perfectly reasonable and pretty much every will know what you mean. The generic term seems to be RDMS or some variation on that.

Referring to a RDBMS as a "SQL Server" would probably confuse people. Besides the fact that it's already the name of a specific product, the overall genre of software doesn't even serve SQL.

Same here. FWIW, I informally refer to it as "MS-SQL" to differentiate.

When I do that, the less technical people I report to get it confused with MySQL which is not what I want.

M$-SQL would be appropriate, but it might be confused with Oracle...

Good to know! Can you give an example? When I google "SQL Server" it seems like most results are related to MS SQL Server

An example of other SQL servers? PostgreSQL would be one, which is what this thread is about.

> It kinda bugs me that people say "SQL Server" to mean "Microsoft SQL Server". I mean there are other sql servers.

I don't mind that; sure, there are other RDBMS servers that support SQL as their main or exclusive language, but unless you are talking about ancient Sybase products (for which there is a very good reason for the shared branding) “SQL Server” is a clear, exclusive Microsoft product identity and not any worse of a label, say, FTP (yes, there are other file transfer protocols).

OTOH,what does bug me is when people say “SQL” to mean “Microsoft SQL Server”.

https://en.wikipedia.org/wiki/SQL_Server

Same as "FTP Server" doesn't mean proftpd, "HTTP Server" doesn't mean apache and "C compiler" doesn't mean gcc. SQL is a language. Server is a generic term.

Another example: I may ask my colleague the following: "Which SQL server should we use in our new project?". Does this mean, i would like to know the edition and version of the MS SQL Server or maybe (and from my point of view more likely) i am looking for PostgreSQL, MySQL or MSSQL. If i'd like to know which edition and version i should use, i'd ask "Which MS SQL Server should i use?". That's a difference.

Might be that our views differ but i can understand the parents points.

But httpd does mean Apache, and sh means Bourne shell.

It also helps that nobody says "The SQL server is down" when their mysql instance is down. Even when using a generic term it's "the database is down"

> ... and sh means Bourne shell.

1. Except on Android, stock Debian/Ubuntu/etc., and in the bootloader and initramfs of my embedded OS;

2. I'm guessing you actually meant 'Bourne Again Shell' instead of 'Bourne Shell'.

Eh... sh only refers to the generic specification of a bourne-family shell, usually bash or dash; almost nobody is actually using the original Bourne shell.

> It also helps that nobody says "The SQL server is down" when their mysql instance is down.

I've heard such sentences many times from many people.

sh refers to a POSIX shell. The bourne shell (bsh) is not entirely compatible with the Bourne Against Shell (bash), and bash will switch to a compatibility mode if called as sh.

Additionally, many distros, including Ubuntu and Debian, do not use bash as their default shell. (Ubuntu/Debian use dash, Arch uses zsh, Alpine uses busybox sh)

In openbsd, httpd means their own webserver. In plan9, there is also a webserver called httpd.

I find that the bigger problem for me is the number of RDBMs that use 'SQL' in the name somewhere means that non-technical people get confused between 'knowing SQL', as in knowing how to develop SQL code, and 'knowing SQL' as in knowing how to manage a particular database product (usually SQL Server). That's how I got turned into a DBA without realizing it.

Out of interest ;) SQL Server is such an expensive beast, ~$15K per core, what are your reasons for prefering it over PG?

I quite like Postgres and use it preferentially, but your numbers are off. SQL Server Enterprise costs more like $7500/core; that $15K pack, as far as I am aware, comes with two core licenses. SQL Server Standard 2016 costs $931 for a license if you use CALs ($209 a pop), or $3700/core.

Also bear in mind that almost nobody pays list price for any of this.

$7500 per core, but only $15,000 for two cores sounds like a sweet deal though.

There are no more server CPUs with less than 6 cores though.

We could happily manage with just 2 dedicated to SQL server, but you have to license all the cores you have. So 3K worth of low end hardware ends up costing you several multiples of that in licensing.

What features in SQL Server Enterprise do you need? More than 24 cores? More than 128GB of RAM?

I ask because SQL Server Standard is significantly cheaper when you're using CALs instead of unlimited connections. Ten servers fronting a SQL Server Standard install is $3K, which at a large enterprise is often within a director-level's discretionary equipment budget.

User CAL is only cheap if you only deal with small amount of users (internal app). Please note a user is not SQL User account, but represents actual individual person. If you have a webserver (with one db user) but a thousand of visitors, then Microsoft treats it as thousand user CALs . Following MS doc explains this in detail http://download.microsoft.com/download/6/F/8/6F84A9FE-1E5C-4... , look for keyword 'multiplexing'

Huh, TIL. I literally had a Microsoft sales rep tell me otherwise. Anything to get the sale, I guess.

> but you have to license all the cores you have

Is that the case? I'm sure I've seen people running on limited cores for licencing reasons rather than having to licence every core. Not sir if that was enforced by the engine refusing to use more, or via seeing processor affinities.

I imagine a VM is a good solution to that.

Windows also let's you set CPU affinity per process - could that be used as a simple workaround?

I laugh when people have to deal with these problems when others are solving real problems using free tools.

For someone who grew up with decent open source solutions in the software stack, that deal simply sounds insane and it should be the majority's thinking soon enough.

And if those paidwares don't solve your problems exclusively, I doubt people would choose to use it when they can solve their problems with the tools they grew up with.

Having to spend time dealing with the MS licensing mess, rather than building software, seems wasteful and in my experience can be likened to sitting on a cactus for an extended period of time.

I agree. (My own stuff is pretty much all Postgres and the occasional MySQL.) But when you're in an organization where your needs might merit SQL Server, I'd hope you'd have somebody to deal with that for you. ;)

Day job. Never had to pay for it out of my own pocket! I've worked at MS shops for quite some time.

Also while we can do what we like with our newer SaaS offerings, getting some of our on-prem clients to use PG over SQL Server would be an uphill struggle so not considering other options isn't entirely our decision.

That $15K sounds like an Enterprise Edition costing too, IIRC Standard is significantly cheaper. Most don't need Enterprise, especially since 2016Sp1 when a lot of previously Enterprise-only features became available in all editions (including the free Express edition in many cases). Developer edition is currently free (licensed for development use only, of course) and is essentially Enterprise edition with different licensing terms.

It's about $7000 per core for enterprise - and if you do something like license a whole blade and put VMs on top of it, you could fairly easily run 10-20+ SQL VMs on one decent sized blade for < 150k. And anything pre-prod is free. It's a small cost for most larger size companies of this scale. If you have one or two servers, then no, enterprise is probably out of reach.

I use MS-SQL for my day job, and Postgres for personal projects. Pros of MS-SQL are mainly 1) easy integration with the universe of Microsoft dev tools, frameworks, and infrastructure, and 2) great database tooling in itself:

* SSMS - SQL Server Management Studio

* The MS BI stack: SSRS (reporting), SSAS (analysis), SSIS (integration)

The tooling is indeed sweet, and in a big company I could see it paying for itself in saved dev time.

Sounds like a victim of vendor lock in. I assume you will bleed hard to get out of it.

How is this any different from the sunk cost that's a consequence of going with _any_ particular technology stack?

I mean, if you chose Python/Postgres/etc., presumably, you'd have to rewrite/retool everything if you went to a different stack.

If you're talking about open vs closed source: the C# compiler (Roslyn) is completely free and open source, as are a number of .NET components: CoreFX - Core foundational libraries, CoreCLR - runtime.

As well as PowerShell, VS Code, Typescript, ASP.NET Core, MVC, Blazor, F#, Z3 Theorem Prover, etc.

And that's from just the first few pages of their Open Source page: https://opensource.microsoft.com/

Analysis Services, Integration Services, Clustered Index

Big parallelism updates that the query planner can take advantage of.

I believe also updates to index seek or scan in that time.

I've just upgraded my hobby app from 9.6 to 11 and some of my old queries are now timing out :-|

I'll update this thread when I find out why.

    analyze

fixed pretty much everything :)

what do you mean? You looked at the analyse report and changed the query to avoid some unnecessary computation?

Reply on news.ycombinator.com