PostgreSQL is the worlds’ best database • Hacker News Board

Re: PostgreSQL is the worlds’ best database

#31

Post by mulmen » Fri, Apr 03, 2020, 9:12 AM UTC

throwaway_pdp09 wrote: post #19

mulmen wrote: post #15

Earlier quoted context omitted.

MSSSQL people seem really proud of their CTEs. What is it that makes CTEs in MSSQL so great? I wonder if that really disqualifies Postgres from the same task. Are MSSQL CTEs just the hammer for your proverbial nail? Can you use a different approach in Postgres to solve the problem by leveraging its strengths? My day job is a lot of Redshift. We use CTEs and temp tables depending on what we need. It’s based on Postgre…

Here's the last line of my post: "Not that I'm knocking PG, 'best' depends upon the problem" I did not want to get into a war with people emotionally involved in their favourite programming tool. The article said 'best'. By that measure PG was worse. Point is it doesn't matter, it's about what problem you are trying to solve and at what price. In many cases PG is best for that - do you understand? I knew people would…

It was an honest question, professional to professional. I did not interpret your post as a personal attack nor did I intend my post as an attack on you.

The last time I used MSSQL in anger I was an intern and Michael Jackson was alive. It’s been a while.

My question is: “what is it about your work that makes (or made) MSSQL a better choice than Postgres or something else, specifically because of CTE differences.”

Re: PostgreSQL is the worlds’ best database

#32

Post by woutgaze » Fri, Apr 03, 2020, 9:14 AM UTC

mcny wrote: post #28

Let’s say I have a table for reservations: 1. Item ID 2. Reservation duration What I would like is if I have an item ID of 1 and a reservation duration of today 10AM to 11AM and I try to add another record of item ID 1 and duration 10:39AM to 11:39AM the insert should fail. Apparently it is straightforward in db2 but not in postgresql?

Something like this will do the job in PostgreSQL:

  CREATE TABLE reservations (
    item_id uuid,
    duration tsrange,
    EXCLUDE USING GIST (item_id WITH =, duration WITH &&)
  );

Re: PostgreSQL is the worlds’ best database

#33

Post by forinti » Fri, Apr 03, 2020, 9:18 AM UTC

I manage Oracle and Postgresql instances and I agree with this, except for one small thing: Postgresql doesn't have packages.

Otherwise, it is much simpler to manage than Oracle (which really is a system turned inside out). The documentation is really good and straight to the point (Oracle can't help but turn everything into an ad - nvl uses the best null handling technology on the market and such).

Re: PostgreSQL is the worlds’ best database

#34

Post by polskibus » Fri, Apr 03, 2020, 9:20 AM UTC

It would be if it had real clustered indexes like MS SQL, ie. Tables as btrees that are maintained with each update.

Re: PostgreSQL is the worlds’ best database

#35

Post by laumars » Fri, Apr 03, 2020, 9:23 AM UTC

INTPenis wrote: post #29

My first job in IT, I was 18, it was the early 2000s. I was a nerd but in professional IT I was essentially a blank slate. I lucked up and ended up with a real hacker for a boss. As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. He was a BSD guy and a postgres guy. He made me into a fanboy of both those technologies. So out of sheer luck I've preferred Postgres for over 15…

> Unlike BSD, which often disappointed me once I learned how much easier and mature Linux was to use.

I was with you right until that last sentence. I'm not going to offer a counterargument because your statement is extremely genralised and ripe for flamewars but I will say it's not as clear cut as you stated.

Re: PostgreSQL is the worlds’ best database

#36

Post by redis_mlc » Fri, Apr 03, 2020, 9:25 AM UTC

Ironically, the blog post shoots itself in the foot by starting with Postgres security.

Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL.

https://www.cvedetails.com/metasploit-modules/vendor-336/Pos...

The reasons are:

- Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-admin read-only users. By contrast, MySQL's are grants are simple to use and simple to understand.

- Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things.

- Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid.

- Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute.

The simple fact is, if you use Postgres, you almost certainly have a security compliance problem.

I could make the same arguments about replication, or online schema changes, multi-master writes, or any enterprise database feature.

Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO, and look at the metasploit options and see what can be done ASAP.

I'd appreciate if you're itching to write a hasty response that you actually check your facts first.

If you're thinking, "How is it possible that everybody else is wrong about Postgres being the best?", just remember the decade of Mongo fanboism on HN. I cringed during that era, too.

Source: MySQL and Postgres DBA.

Re: PostgreSQL is the worlds’ best database

#37

Post by throwaway_pdp09 » Fri, Apr 03, 2020, 9:31 AM UTC

mulmen wrote: post #31

throwaway_pdp09 wrote: post #19

Earlier quoted context omitted.

Here's the last line of my post: "Not that I'm knocking PG, 'best' depends upon the problem" I did not want to get into a war with people emotionally involved in their favourite programming tool. The article said 'best'. By that measure PG was worse. Point is it doesn't matter, it's about what problem you are trying to solve and at what price. In many cases PG is best for that - do you understand? I knew people would…

It was an honest question, professional to professional. I did not interpret your post as a personal attack nor did I intend my post as an attack on you. The last time I used MSSQL in anger I was an intern and Michael Jackson was alive. It’s been a while. My question is: “what is it about your work that makes (or made) MSSQL a better choice than Postgres or something else, specifically because of CTE differences.”

Sorry. When you said "really proud" and "so great", it was perhaps an emotive choice of words.

In the end the semantics of CTEs should be much the same throughout. There may be small differences, and perhaps larger ones such as being able to update through a CTE, something like

  with x as (...)
  update x set ...

I don't know if PG supprts this, and it wouldn't break my heart if it didn't. It would be easy to work around.

Also IIRC PG has 'materialized' and 'recursive' keywords. No biggie.

So the semantics are substantially the same. Difference is, how the optimiser treats it. That means you will get the same results back but the time difference may be enormous. This explains it: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

In my previous work, the predicate pushdown (which is the posh term for the optimiser rewriting the query as the above link demonstrates) made CTEs usable. Without them, performance would have destroyed the company.

Can we get round it? Mostly, yes, but it would have been more work for us (meaning more human hours) which the DB optimiser should have saved us from.

The only unique thing CTEs bring to the party is recursion. Otherwise they are no more than a convenience, albeit a great one.

HTH

EDIT: MSSQL has a good optimiser when it works. I recently wrote a trivial CTE and had it take a minute to run, WTF? Looked at the query plan, something was very wrong there, pulled out the CTE part and put it into a temp table, ran in one second. 60x speedup. No idea why, was not impressed though.

Re: PostgreSQL is the worlds’ best database

#38

Post by nicoburns » Fri, Apr 03, 2020, 9:32 AM UTC

woutgaze wrote: post #32

mcny wrote: post #28

Let’s say I have a table for reservations: 1. Item ID 2. Reservation duration What I would like is if I have an item ID of 1 and a reservation duration of today 10AM to 11AM and I try to add another record of item ID 1 and duration 10:39AM to 11:39AM the insert should fail. Apparently it is straightforward in db2 but not in postgresql?

Something like this will do the job in PostgreSQL: CREATE TABLE reservations ( item_id uuid, duration tsrange, EXCLUDE USING GIST (item_id WITH =, duration WITH &&) );

Can this be made to work if the `id` and `duration` are in different tables (with a joining key)?

Re: PostgreSQL is the worlds’ best database

#39

Post by halayli » Fri, Apr 03, 2020, 9:35 AM UTC

redis_mlc wrote: post #36

Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-…

your statements are nothing but opinions. It's also dishonest to make them sound like facts.

If you care this much about security, you should have taken a look at CVE reports of each db. You'll find that historically MySql has almost double the number of vulnerabilities.

Re: PostgreSQL is the worlds’ best database

#40

Post by JacKTrocinskI » Fri, Apr 03, 2020, 9:35 AM UTC

Jonnax wrote: post #2

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

Oracle is currently moving in the direction of autonomous databases. It's no longer just about having great automation features but about the database being able to do things for you like create or drop an index in order to improve ETL performance, and it's all done autonomously.