Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New in PostgreSQL 10

wiki.postgresql.org

Post reply on HN
Print view View original

121–130 of 258 posts

Re: New in PostgreSQL 10

#121

Post by mst »

adrianratnapala wrote: post #86
gshulegaard wrote: post #9

Just...wow. * Native partitioning * Parallel query Honestly, some very welcome quality of life improvements for use cases even outside of what I would consider "Big Data". > This means that users no longer need to create triggers for routing data; it's all handled by the system. Trigger routing has always been a performance foot gun...to the point that it's sometimes better to handle table routing in application logi…

I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.

This is a completely reasonable worry though I must note that postgres has done it way more rarely than anything else I've dealt with which may explain the voting response to this comment.

Re: New in PostgreSQL 10

#122

Post by mst »

flukus wrote: post #98
adrianratnapala wrote: post #86

Earlier quoted context omitted.

I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.

This is a valid concern, I've seen simple automatic optimizations backfire before. An example I remember from MS Sql Server (older versions) of a very simple optimization of using indexes can backfire with the dynamic filter pattern: where ((@foo is null) or (foo = @foo)) and ((@bar is null) or (bar = @bar)) Depending on statistics, order of execution, indexes in place and phase of the moon this could produce fast re…

Modern pg libraries only cache the plan with the query params involved as well so this generally doesn't happen.

(I made it happen with an older version of things and ... yes, argh)

Re: New in PostgreSQL 10

#123

Post by mst »

GoodbyeEarl wrote: post #119

Hey, HN! Would you recommend me a practical introduction to data modelling with Postres?

Learn normalisation from wikipedia, then read "The Art of SQL" to get a feeling for the shape of the weirdnesses, then read the postgresql documentation from end to end.

(disclaimer: worked for me, may not for anybody else)

Re: New in PostgreSQL 10

#124

Post by mst »

deepsun wrote: post #114
dk8996 wrote: post #99

Earlier quoted context omitted.

Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.

Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.

You're talking about DDL.

They're talking about an in-place rewrite of the value of a single column, which, yes, InnoDB will do with way less write load than postgresql.

Re: New in PostgreSQL 10

#125

Post by mst »

dk8996 wrote: post #118
deepsun wrote: post #114

Earlier quoted context omitted.

Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.

UPDATE client set enabled = true; So something like this will rewrite the whole table because of MVCC. MySQL will update the record in place without rewriting the whole table.

MVCC is almost always a feature for my workloads but it's well remembering it's a trade-off.

Thanks for reminding HN of this point.

Re: New in PostgreSQL 10

#126

Post by mcdoh »

Can anyone recommend a good resource for learning PostresQL? I have a decent understanding of SQL, I've used MySQL in the past, but every time I see the latest features of Postres I'm left thinking I'll need to buy a book and take a pretty deep dive to figure out how to apply the new features.

Re: New in PostgreSQL 10

#127

Post by phaedryx »

mcdoh wrote: post #126

Can anyone recommend a good resource for learning PostresQL? I have a decent understanding of SQL, I've used MySQL in the past, but every time I see the latest features of Postres I'm left thinking I'll need to buy a book and take a pretty deep dive to figure out how to apply the new features.

I'd start with the docs: https://www.postgresql.org/docs/

They are pretty good.

Re: New in PostgreSQL 10

#128

Post by grzm »

mcdoh wrote: post #126

Can anyone recommend a good resource for learning PostresQL? I have a decent understanding of SQL, I've used MySQL in the past, but every time I see the latest features of Postres I'm left thinking I'll need to buy a book and take a pretty deep dive to figure out how to apply the new features.

You'll likely find the PostgreSQL docs[0] very useful. They're organized differently than the MySQL reference making it easier (in my experience) for reading stand-alone.† They're quite well-written and up-to-date.

[0]: https://www.postgresql.org/docs/

† The MySQL reference is very useful particularly when you're looking to compare feature differences between versions.

Re: New in PostgreSQL 10

#129

Post by manigandham »

zackelan wrote: post #102
manigandham wrote: post #88

Earlier quoted context omitted.

SQL Server on Linux in a container is just as easy now: docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password' -p 1433:1433 -d microsoft/mssql-server-linux EDIT: Or with apt-get too: sudo apt-get install -y mssql-server https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...

That strikes me as being comparable only at the most superficial level. I started that SQL Server container and it's using 825mb of RAM (according to systemd-cgtop), just sitting there idle with no data in it. I started a Postgres 9.6 container also using their official Docker container and it's sitting idle at 58mb of RAM. Insult to injury, if you're using Docker in a VM (as you must on a Mac) the instructions on th…

SQL Server does use it's own mini-OS layer and this what lets it virtualize and run on Linux too, so there are higher system requirements.

It seems you're just guessing on that last part though - SQL Server has been in production for decades and already has lots of tooling and there are even new CLIs available for the Linux environment. Production SQL Server comes with support from MS, you're definitely not on your own nor will they just tell you to run it on Windows, this is simply not how enterprise support works and they would have a hard time staying in business with answers like that.

We can discuss support issues but it's best to leave the assumptions and hyperbole out of it.

Re: New in PostgreSQL 10

#130

Post by dk8996 »

mst wrote: post #124
deepsun wrote: post #114

Earlier quoted context omitted.

Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.

You're talking about DDL. They're talking about an in-place rewrite of the value of a single column, which, yes, InnoDB will do with way less write load than postgresql.

Yes, column updates. Although, using MySQL now, I really miss DDL migrations of PostgreSQL. It's a trade off.
Post reply on HN