Postgres 11 – A First Look • Hacker News Board

Re: Postgres 11 – A First Look

#131

Post by threeseed » Sat, Sep 22, 2018, 12:50 PM UTC

gaius wrote: post #129

threeseed wrote: post #116

Earlier quoted context omitted.

This is simply untrue. MongoDB is a much better JSON store. It's ridiculously faster, has much richer update semantics and it's simply not comparable when it comes to the ease of clustering. As for time series unless I am confused but PostgreSQL doesn't have any OOTB capabilities.

MongoDB is a much better JSON store That hasn't been true for a long time, if it even ever really was when you consider that the key role of a database is to reliably persist your data https://www.enterprisedb.com/node/3441 (2014)

Think about this for a second. You just posted a link that is over 4 years old.

It's completely irrelevant. Especially since I am referring to version 3.0+ where they completely replaced their entire engine with WiredTiger. And from that point on the performance for tuple level updates for example is faster than I've seen from ANY database.

And this nonsense about reliable persistence of data was a case of bad defaults many, many years ago. It was also fixed many, many years ago and so these tired memes just make you look bad.

Re: Postgres 11 – A First Look

#132

Post by reitanqild » Sat, Sep 22, 2018, 12:57 PM UTC

mrep wrote: post #60

ma2rten wrote: post #46

Earlier quoted context omitted.

MySQL used to be more popular. It was the M in the LAMP stack, which virtually everyone used to use. It also used to be more scalable, because PG's replication story used to be poor. This is why Google and (I assume) Amazon used MySQL internally. This is all a long time ago, but I suspect this is the reason more people are familiar with MySQL than Postgres at these companies. Keep in mind that Spanner was developed a…

> This is why Google and (I assume) Amazon used MySQL internally. Amazon actually uses a lot of Oracle with some scattering of MySQL.

That's weird.

But maybe at their scale even Oracle can be made tolerable.

That, and they have some serious leverage in negotiations.

(I used to deal with Oracle DBs a lot a few years ago but thankfully not anymore.)

Re: Postgres 11 – A First Look

#133

Post by SOLAR_FIELDS » Sat, Sep 22, 2018, 1:03 PM UTC

sergiotapia wrote: post #13

mmartinson wrote: post #2

Thank you pg maintainers, you've made such a wonderful thing.

It really is incredible how we can get something as fundamental as a database for free that is so well made and production ready. Can you imagine if there was no postgres? We'd have to use mysql or oracle or gasp mssql.

If MSSQL wasn't tied to the Microsoft ecosystem, I don't think it would get such a bad rap - When I was a dev on the Microsoft product stack I found MSSQL quite a joy to use - reasonably fast, pretty powerful admin tools (SSMS) and pretty easy to interface with as long as you stayed within the MS ecosystem. I did find myself missing SSMS specifically when I moved to non MS stack development, since Postgres' 'official' open source options aren't nearly as robust. There are a few proprietary tools that can rival SSMS for Postgres, but then you are back into the realm of proprietariness.

Re: Postgres 11 – A First Look

#134

Post by twic » Sat, Sep 22, 2018, 1:06 PM UTC

pmontra wrote: post #87

jpalomaki wrote: post #81

Earlier quoted context omitted.

Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).

Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message. I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function. A MySQL DBA solved that problem in that project I worked o…

Given:

    create table conversations (
      message_id integer primary key,
      conversation_id integer,
      timestamp timestamp,
      user_id integer,
      message varchar
    );

Then:

    select distinct on (conversation_id) *
    from conversations
    order by conversation_id, timestamp desc;

Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL.

Now, what i don't know is how this is planned for a large table, or what indices would let you get a sensible plan. Maybe just an index on (conversation_id, timestamp)?

Re: Postgres 11 – A First Look

#135

Post by gaius » Sat, Sep 22, 2018, 1:16 PM UTC

threeseed wrote: post #131

gaius wrote: post #129

Earlier quoted context omitted.

MongoDB is a much better JSON store That hasn't been true for a long time, if it even ever really was when you consider that the key role of a database is to reliably persist your data https://www.enterprisedb.com/node/3441 (2014)

Think about this for a second. You just posted a link that is over 4 years old. It's completely irrelevant. Especially since I am referring to version 3.0+ where they completely replaced their entire engine with WiredTiger. And from that point on the performance for tuple level updates for example is faster than I've seen from ANY database. And this nonsense about reliable persistence of data was a case of bad defaul…

You just posted a link that is over 4 years old.

Yes, to make my point that it hasn't been true "for a long time". Yes Mongo had JSON support before PG did, but PG soon surpassed it.

these tired memes just make you look bad

Nah, they make me look like what I am, an extremely experienced data guy. Maybe Mongo has some use cases in webdev, I wouldn't know.

Re: Postgres 11 – A First Look

#136

Post by spion » Sat, Sep 22, 2018, 1:18 PM UTC

philliphaydon wrote: post #89

manigandham wrote: post #47

Earlier quoted context omitted.

MySQL was easier to run. Both for beginners where it was packaged with PHP and available with basically every webhost, and for advanced users that took advantage of the simple and reliable replication. There were a lot of problems with it, but the novice users didnt run into them and the advanced users knew how to deal with it. These decades of mainstream use carry a lot of momentum and many large companies continue…

PostgreSQL has been useable Long before 9.x, however I think features of 9.x have made it much more popular as of late. I think I first started using it at v6 or 7. I liked it could I could run it from a .bat file and spin it up for tests without installing it.

Replication is still a pain.

Re: Postgres 11 – A First Look

#137

Post by StreamBright » Sat, Sep 22, 2018, 1:19 PM UTC

Avamander wrote: post #113

gaius wrote: post #88

Earlier quoted context omitted.

With Postgres you don't need MongoDB, InfluxDB, or any other trendy thing, Postgres does it all, and better than all the wannabes.

Could you please elaborate how to use Postgres as a time series database (like InfluxDB)?

Create a table and have a timestamp for your data? Not sure what you are asking.

On hand we have: 15+ years old mature product that is widely supported and it can be used as a time series database

Other hand: n+1 young database engine with all of the problems that any young data storage engine has (only exception is FoundationDB because those guys actually understood the challenges of writing a reliable data store)

The question is, what can InfluxDB offer over Postgres for storing time series data?

Re: Postgres 11 – A First Look

#138

Post by chinhodado » Sat, Sep 22, 2018, 1:26 PM UTC

GordonS wrote: post #96

dtech wrote: post #94

Earlier quoted context omitted.

MySQL workbench is still 10.000 miles ahead of PgAdmin. About three years ago the latest iteration PgAdmin 4 came out, which I tried on and off for a few months but was so buggy it was unusable for me. It might have gotten better since then. It's a shame, psql is awesome once you know enough to use it, but GUIs is how people start using RDMSes, and it's about the only thing that MySQL is clearly superior to PostgreSQ…

PgAdmin recently became an in-browser tool, and is much improved - in particular, it no longer uses GBs of memory and 90% of my CPU!

pgadmin 4 is still horribly slow, both when starting up and during operation, horribly bloated in size (100mb compared to pgadmin 3's 10mb). It still hogs CPU and RAM like hell.

Re: Postgres 11 – A First Look

#139

Post by stareatgoats » Sat, Sep 22, 2018, 1:35 PM UTC

threeseed wrote: post #118

gaius wrote: post #105

Earlier quoted context omitted.

Check out what Citus are doing with PG https://www.citusdata.com

That's a fork of PostgreSQL. So it doesn't help you when it comes to using cloud hosted services e.g. AWS or Azure. It's also a commercial product that isn't exactly cheap ($890/node).

It's not a fork (according to their promotion), it's an 'extension', but not sure I can explain the difference. Their solution looks interesting but pricing wasn't very visible on their website, what you quote is prohibitive for me (where does it say?). But I at least assumed the 'open source' would be free.

Re: Postgres 11 – A First Look

#140

Post by Shish2k » Sat, Sep 22, 2018, 1:51 PM UTC

paulddraper wrote: post #40

grzm wrote: post #26

Earlier quoted context omitted.

Ctrl-D still works. The new sql commands additions.

Right, but now I can't Ctrl+C to cancel a query without worrying instead I will exit the shell instead.

You're worried that when you try to hit ctrl-c you might accidentally type "exit"? I'm not sure how else the addition of exit as an alias for ctrl-d would affect your ctrl-c work...