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

131–140 of 172 posts

Re: PostgreSQL 10 Beta 1 Released

#131

Post by matt4077 »

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 tried to switch, once. I actually gave up when, after spending entirely too much time trying to find the cli client, I couldn't figure out how to actually send queries (or it may have been the "show database" part–it's been a while)

I tend to think the HN groupthink is strong on this subject, and 90%+ wouldn't ever see an effect beyond placebo from switching. MySQL (or MariaDB, which I actually use these days) has also changed drastically since those 200x-years where most of today's folk wisdom originates.

Re: PostgreSQL 10 Beta 1 Released

#132

Post by combatentropy »

grzm wrote: post #129
combatentropy wrote: post #128

Earlier quoted context omitted.

Yes, it was a reference to that. My comment is currently sitting at -2. I'm still getting the hang of Hacker News' sense of humor. On Slashdot, I would be at +5. Ho hum. Anyway, just to make my intentions clear, I love Postgres, have used it for over a decade, and have yet to use MongoDB or any other NoSQL database.

In general, strive for substantive and constructive comments on HN. If you've got that, a little humor added in can be appreciated. Comments that are submitted only for humor value (which you knew yours was, given your parenthetical addendum) are likely to be less appreciated on HN than on other sites. Here's a recent thread where this has been discussed: https://news.ycombinator.com/item?id=13760333 There are likely…

So what I want to know is how the members of Hacker News act as one. Presumably these are the same people who appreciate humor on other sites, upvote it, and participate in it. But on Hacker News they somehow all know to downvote it. It's eerie.

Re: PostgreSQL 10 Beta 1 Released

#133

Post by grzm »

combatentropy wrote: post #132
grzm wrote: post #129

Earlier quoted context omitted.

In general, strive for substantive and constructive comments on HN. If you've got that, a little humor added in can be appreciated. Comments that are submitted only for humor value (which you knew yours was, given your parenthetical addendum) are likely to be less appreciated on HN than on other sites. Here's a recent thread where this has been discussed: https://news.ycombinator.com/item?id=13760333 There are likely…

So what I want to know is how the members of Hacker News act as one. Presumably these are the same people who appreciate humor on other sites, upvote it, and participate in it. But on Hacker News they somehow all know to downvote it. It's eerie.

I'm not sure I follow. People often behave differently depending on the context (e.g., work, home, school, out with friends), so acting differently on different sites doesn't seem very surprising. Also, different sites have different, though perhaps overlapping, populations, so the "average" behavior or culture is going to be different.

One example (mentioned in the HN guidelines) is Reddit. There are people who frequent both HN and Reddit, yet it's clear that members on HN—even those who use Reddit as well—don't want HN and Reddit to be the same. As such, they behave differently on each site. That's not to say one is better than any other: they're just different.

And HN members don't act as one—just as they don't act as one on any other site. If they did, your comment would have been downvoted to the point that it was flagged dead (given it's likely been seen by hundreds, if not thousands) or not flagged at all. Given it's current shade of gray, I suspect you've received only a few downvotes. (Edit to add: I see you did say it is at -2.)

Does that make sense? Or am I misreading you?

Anyway, this is quite off-topic now. I posted these in the hopes of providing a bit more insight into the HN community. I hope they've been more helpful than frustrating.

Re: PostgreSQL 10 Beta 1 Released

#134

Post by derefr »

snuxoll wrote: post #122
derefr wrote: post #119

Earlier quoted context omitted.

> so you're forced to less than optimal solutions like table inheritance for large numbers of records Would the "native table partitioning" feature introduced in this release be a solution (or, at least, more optimal) for this? Alternately, if you want to go off-heap, what about using Foreign Data Wrappers ( https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_... )? These two both sound like they might solve…

The native table partitioning removes one of the headaches of inheritance, but it mostly only relieves some of the administrative headaches - the foreign key issues remain. fdw's are great, but they don't participate in replication - which is a pretty big issue. Unfortunately, the "proper" way to do this would be with a custom type, but I can't find any way you could write one with the extension API that wouldn't be…

To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap?

As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL log and so forth. Just the space overhead of this management metadata will then be prohibitively costly in memory, if you have "tens of billions of files" to keep under management.

Personally, I think the pragmatic solution would be to replicate the files themselves outside of Postgres, and have Postgres just hold opaque references to their paths, which you would join through an FDW to fill a column with their contents.

As you say, the files are Write-Once-Read-Many—so you (presumably) aren't worried about how Isolated or Consistent writes to the file data would be without Postgres. Plain filesystems provide Atomicity and Durability guarantees all by themselves. It's less convenient to do things this way—you have to manage e.g. "WAL-E + a separate rsync" rather than just WAL-E—but it's not particularly bad in terms of the engineering trade-offs.

Re: PostgreSQL 10 Beta 1 Released

#135

Post by walrus »

colanderman wrote: post #65
walrus wrote: post #59

Earlier quoted context omitted.

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.

Actually now that I re-read the grammar, sub-selects are yet another accepted syntax. Looks like the ROW syntax doesn't support row expressions like I thought. Not really sure the benefit beside making it easier to programmatically construct UPDATEs.

Here's the commit where the "UPDATE .. SET ROW (col, ..) = (col, ..)" syntax was added as an alternative to "UPDATE .. SET (col, ..) = (col, ..)": https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

I don't quite understand the commit message, but I think the new syntax resolves some ambiguity in the grammar in an edge case. From the diff of src/test/regress/sql/update.sql:

  -- *-expansion should work in this context:
  UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j)
    WHERE update_test.a = v.i;
  -- you might expect this to work, but syntactically it's not a RowExpr:
  UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j)
    WHERE update_test.a = v.i;

Re: PostgreSQL 10 Beta 1 Released

#136

Post by MR4D »

ris wrote: post #118
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…

As exciting as this is (and it really is) it's yet another thing that explodes the number of possibilities for optimization. It's getting towards the point of unmanageability and I hope there will be a movement towards, if not auto-tuning databases, assistive tools for exploring both this and indexing possibilities.

I think the best way to handle this situation is for the default install to be generally performant on any given task, with the option to flip switches for those use cases where extreme performance are needed due to the specific workload.

If that's the case (which I suspect it is, having used prior PSQL versions), then most people will never need to optimize it, but those who have an extreme use case that can benefit from such tweaks will have such options available.

Re: PostgreSQL 10 Beta 1 Released

#137

Post by combatentropy »

grzm wrote: post #133
combatentropy wrote: post #132

Earlier quoted context omitted.

So what I want to know is how the members of Hacker News act as one. Presumably these are the same people who appreciate humor on other sites, upvote it, and participate in it. But on Hacker News they somehow all know to downvote it. It's eerie.

I'm not sure I follow. People often behave differently depending on the context (e.g., work, home, school, out with friends), so acting differently on different sites doesn't seem very surprising. Also, different sites have different, though perhaps overlapping, populations, so the "average" behavior or culture is going to be different. One example (mentioned in the HN guidelines) is Reddit. There are people who freq…

Thank you. No, you're not misreading me. I guess like you say, it could be worse than -2. (Although now it's at -3!)

I don't frequent Reddit. It sounds like it's so jocular that people come here to get away from it all.

Re: PostgreSQL 10 Beta 1 Released

#138

Post by snuxoll »

derefr wrote: post #134
snuxoll wrote: post #122

Earlier quoted context omitted.

The native table partitioning removes one of the headaches of inheritance, but it mostly only relieves some of the administrative headaches - the foreign key issues remain. fdw's are great, but they don't participate in replication - which is a pretty big issue. Unfortunately, the "proper" way to do this would be with a custom type, but I can't find any way you could write one with the extension API that wouldn't be…

To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap? As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL…

A filesystem may guarantee atomic and durable transactions, but then you're in a bind trying to keep things in sync with the database - either potentially ending up with orphans or missing data. In theory you could use a 2-phase commit approach, but that comes with its own headaches.

Consistency is actually a huge issue for DR purposes, right now with the DB+filesystem approach it's nearly impossible to get a clean restore from backups if needed. We could alleviate this a bit by using something like GlusterFS' geo-replication support and replicas offsite, then verifying the tail of the data on-disk, but it would be nice to have a solution that could just work correctly with PostgreSQL's built-in replication (just like FILESTREAM does with MSSQL).

Edit: I should add, PostgreSQL's existing LOB support already works great with binary replication. The biggest issue with them is simply the 4B record limit, due to the use of OID's. It'd be nice if it was hidden away automatically with a special BYTEA-variant like FILESTREAM does with MSSQL, but it's "good enough" without that limitation.

Re: PostgreSQL 10 Beta 1 Released

#139

Post by solidsnack9000 »

mgkimsal wrote: post #32
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'

not sure why you got downvoted so much. Everyone's answer is "just lowercase everything". I'll respond just a bit: 1. You don't always have control over all the queries that have been written against your database. 2. You would probably lose the ability to use ORMs without a moderate amount of customization. 3. If you're migrating from a different database, you may have checksums on your data that would all need to b…

> 4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it?

Maybe MySQL has special sauce for doing this comparison without lowercasing the query string? But there must be some overhead relative to exact search?

Re: PostgreSQL 10 Beta 1 Released

#140

Post by [deleted] »

colanderman wrote: post #30
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…

Has anything actually changed with the ROW syntax for UPDATE? Maybe I'm missing something but it looks like the functionality has always been there, but now you're allowed to type the word "ROW". E.g. compare [1] and [2]. [1] https://www.postgresql.org/docs/9.6/static/sql-update.html [2] https://www.postgresql.org/docs/10.0/static/sql-update.html

[deleted]
Post reply on HN