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

PostgreSQL 10 Beta 1 Released

postgresql.org

Post reply on HN
Print view View original

51–60 of 172 posts

Re: PostgreSQL 10 Beta 1 Released

#51

Post by lima »

jdubs wrote: post #12
alfalfasprout wrote: post #5

The native table partitioning makes me so happy. I'd been doing this for years with really hacky external modules and tons of triggers. Sadly, even then there were always weird edge cases. Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...

Every time I see a job post mentioning mysql I realize they just haven't discovered postgres, or they have some really gross problem. :/

I personally prefer PostgresQL, too, but MySQL does have one massive advantage: operations tooling and clustering options.

There's a whole lot of documentation and an ecosystem of operations tooling (think Percona) and MySQL experts are much more numerous.

MariaDB's Galera cluster is really solid and had years of production use now.

Postgres is catching up, but for now, MySQL/MariaDB win in that regard.

Re: PostgreSQL 10 Beta 1 Released

#52

Post by anarazel »

crgwbr wrote: post #35

Will having logical replication make doing a DB version upgrade in production easier? We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime.

> Will having logical replication make doing a DB version upgrade in production easier?

Yes, that's one of the major goals.

> We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime.

Unfortunately on RDS your options are a bit more limited than on plain postgres. Otherwise you could use londiste, pglogical, .. to keep the time to switch over to something very small.

Re: PostgreSQL 10 Beta 1 Released

#53

Post by fnayr »

htsh wrote: post #46
fnayr wrote: post #38

Earlier quoted context omitted.

I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much…

A small independent game is where I've used it before as well. And currently I'm working on an app/game sort of thing where it makes a lot of sense because we're iterating often and fast. I like that it kinda gets out of my way and just works, though perhaps I would not so much had I experienced the data loss others speak of.

Yeah I've read some horror stories about that too. I think that the improvements to concurrency (ala WiredTiger), will help wth that, as well as making sure to think about possible concurrency issues from the outset, as I've tried to do.

Re: PostgreSQL 10 Beta 1 Released

#54

Post by anarazel »

snuxoll wrote: post #41

I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. I know, I know, "databases are bad for files" - but let's take something like an ECM suite where images and documents are literally part of a transaction, having to synchronize those between filesystem and database breaks the Atomic constraint in so many ways. PostgreSQL has LOB support, but oid's being…

Postgres dev here.

> but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM.

What sizes of files and such are you interested in? What kind of read/write patterns?

I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches.

It'd be cool if you could comment on the pgsql-hackers list.

Re: PostgreSQL 10 Beta 1 Released

#55

Post by jakobegger »

The biggest news for me is ICU support for collations (text sorting).

Previous versions of PostgreSQL relied only on strcoll, which is horribly broken on BSD and macOS. On platforms where it wasn't completely broken, it had the potential for subtle data corruption bugs (eg. an update to glibc might change sort order, causing indexes to become corrupt).

Now, you can optionally use ICU for collations, which gives you reliable, versioned collations. This is a big step forward!

ICU collations are not the default, you need to add them with CREATE COLLATION. You have a lot more collations available to choose from, but I think it's not yet possible to change any of the advanced settings that ICU provides.

(Also, when I tried it, it seems that the ICU collations are case insensitive -- but I think case insensitive collations aren't fully supported yet.)

Re: PostgreSQL 10 Beta 1 Released

#56

Post by walrus »

pilif wrote: post #13

While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-sta... ) is the one…

What's the significance of the `update .. set (..) = row (..) ..` syntax?

  update comment set modified = now(), body = 'edited comment' where id = 123;
vs

  update comment set (modified, body) = row (now(), 'edited comment') where id = 123;
It doesn't seem to provide any new functionality, just a minor difference in syntax.

Re: PostgreSQL 10 Beta 1 Released

#57

Post by colanderman »

walrus wrote: post #56
pilif wrote: post #13

While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-sta... ) is the one…

What's the significance of the `update .. set (..) = row (..) ..` syntax? update comment set modified = now(), body = 'edited comment' where id = 123; vs update comment set (modified, body) = row (now(), 'edited comment') where id = 123; It doesn't seem to provide any new functionality, just a minor difference in syntax.

EDIT: Turns out the below is not true, though you can achieve the same effect with the sub-select syntax.

The row value can be a single value from some other query. (Rather than having to pick apart each column from the row value.) That said I think the feature has been there for a while, and now simply the "ROW" keyword is optionally allowed.

Re: PostgreSQL 10 Beta 1 Released

#59

Post by walrus »

colanderman wrote: post #57
walrus wrote: post #56

Earlier quoted context omitted.

What's the significance of the `update .. set (..) = row (..) ..` syntax? update comment set modified = now(), body = 'edited comment' where id = 123; vs update comment set (modified, body) = row (now(), 'edited comment') where id = 123; It doesn't seem to provide any new functionality, just a minor difference in syntax.

EDIT: Turns out the below is not true, though you can achieve the same effect with the sub-select syntax. The row value can be a single value from some other query. (Rather than having to pick apart each column from the row value.) That said I think the feature has been there for a while, and now simply the "ROW" keyword is optionally allowed.

Thanks for the explanation!

  update comment set (modified, body) = (select now(), 'edited comment') where id = 123;
You're right, it works the same without the `row` keyword in 9.6.

Re: PostgreSQL 10 Beta 1 Released

#60

Post by jakobegger »

thejosh wrote: post #25
ubertaco wrote: post #14

Earlier quoted context omitted.

Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that SELECT id FROM users WHERE email='foo@example.com' returns the same result as SELECT id FROM users WHERE email='Foo@example.com'

Because you don't always want to match 'Foo' to 'foo'?

That's what per-column collations are for. Ideally you should be able to choose from case sensitive and case insensitive collations. Unfortunately PostgreSQL doesn't support case insensitive collations (for some reason the string comparison routines use memcmp as a tie-breaker when the collation says strings are equal).
Post reply on HN