Postgres 11 – A First Look • Hacker News Board

Re: Postgres 11 – A First Look

#161

Post by wink » Sat, Sep 22, 2018, 5:42 PM UTC

Jedd wrote: post #34

craigkerstiens wrote: post #22

Earlier quoted context omitted.

Original author here. Yes, it's great to learn Ctrl + D. But losing out on beginners that aren't familiar in favor of things that are naturally intuitive is a great way to make those who are new to programming and especially databases feel like they're dumb or not capable of it. I still recall the first time I was in vi and I couldn't figure out how to get out of the damn thing, and don't get me started on arrow keys…

You use the word intuitive a couple of times, and suggest there's a natural way to exit interactive cli programs (and it's not ctrl-d). Yet for me, ctrl-d is my usual first way of trying to exit any shell or cli utility. Whether it's natural or not, I couldn't say. Arguably the nipple is the first and only intuitive interface, everything after that is learned. After many years on my/maria I find the backslash shortcu…

I find it highly unintuitive that ctrl-d only works on an empty line. So despite the fact I've known it for years (decaded at this point?) I still regularly want to exit mid-typed line. And no, it doesn't matter why this is a good thing or not - I'm just saying even if you've known it for years it's still not intuitive that an "end of a file" has to be on a new line.

Re: Postgres 11 – A First Look

#162

Post by paulddraper » Sat, Sep 22, 2018, 5:56 PM UTC

Shish2k wrote: post #140

paulddraper wrote: post #40

Earlier quoted context omitted.

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...

Nevermind I misread.

Re: Postgres 11 – A First Look

#163

Post by marcosdumay » Sat, Sep 22, 2018, 6:24 PM UTC

evanelias wrote: post #151

rgbrenner wrote: post #67

Earlier quoted context omitted.

In fairness, some of this is historical baggage that MySQL got stuck with from it's early popularity. mSQL was a free/low cost SQL database in the early 90s. Originally it was an SQL translator built on top of Postgres (that used POSTQUEL). That was too slow (because Postgres had higher system requirements), so a new lightweight engine was developed for it.. and that's what later became MySQL. The point was a lightwe…

> The compromises to make mSQL fast were inherited by MySQL, and they can't be easily changed without breaking the ecosystem that was developed around mSQL/MySQL. This doesn't sound accurate to me. Which compromises are you referring to? Historically, MySQL's major source of criticism related to leniency of type safety / automatic data conversion -- which is unrelated to performance. It's also essentially a solved pr…

The main selling point of MySQL at the earlier 2000's was that it used an in-memory storage with only opportunistic disk writes. That made for an incredibly fast database, with obvious downsides that many people refused to notice.

Re: Postgres 11 – A First Look

#164

Post by IshKebab » Sat, Sep 22, 2018, 6:30 PM UTC

jacques_chester wrote: post #119

alecco wrote: post #108

Earlier quoted context omitted.

> “build it safe and then make it fast” I have a lot of respect for the PostgreSQL team. But I think they don't care about the second part, or the constraints of the first part make it impossible. A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Also to note, PostgreSQL has too many features for my taste. I get it's…

> A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Why is this surprising?

I assume he means bad in comparison to other DBs.

Re: Postgres 11 – A First Look

#165

Post by jacques_chester » Sat, Sep 22, 2018, 7:20 PM UTC

IshKebab wrote: post #164

jacques_chester wrote: post #119

Earlier quoted context omitted.

> A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows. Why is this surprising?

I assume he means bad in comparison to other DBs.

So do I. But it's a bit like criticising a car for being slow because you didn't push the accelerator pedal. That's not really the car's fault.

Re: Postgres 11 – A First Look

#166

Post by solarengineer » Sat, Sep 22, 2018, 7:41 PM UTC

Jedd wrote: post #144

craigkerstiens wrote: post #38

Earlier quoted context omitted.

The background of what you learned on is key to whats intuitive or natural. If you came up on an unix based system then yes you'd expect this. If you learned databases via MySQL then you'd expect show database to work. Today though many more are coming to development without an academic or linux background. If you install Postgres via Postgres.app there is a chance you're not at all familiar with a commandline. What…

> The background of what you learned on is key to whats intuitive or natural. That's pretty much the summary of my post. > If you install Postgres via Postgres.app ... I had to go lookup what postgres.app actually is -- evidently it's an easy way for Mac users to install postgres. I don't have a Mac -- which is also true for most people -- so I can't speak to the average mac user's experience or expectations. As I ha…

Mac users either don't stop the service at all, or they use the GUI to do so (if they installed via the postgres.app tool)

Re: Postgres 11 – A First Look

#167

Post by jitans » Sat, Sep 22, 2018, 7:56 PM UTC

mrep wrote: post #37

kstrauser wrote: post #31

A million things like this are why PostgreSQL is the only relational database I consider for, well, pretty much anything. Their approach of “build it safe and then make it fast” has been paying off in spades for a couple of decades now. Thanks for everything you do, psql maintainers!

100% naive question: why is mysql and their similars so popular then? Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?

There is who cares about his data and who uses MySQL

Re: Postgres 11 – A First Look

#168

Post by evanelias » Sat, Sep 22, 2018, 8:04 PM UTC

marcosdumay wrote: post #163

evanelias wrote: post #151

Earlier quoted context omitted.

> The compromises to make mSQL fast were inherited by MySQL, and they can't be easily changed without breaking the ecosystem that was developed around mSQL/MySQL. This doesn't sound accurate to me. Which compromises are you referring to? Historically, MySQL's major source of criticism related to leniency of type safety / automatic data conversion -- which is unrelated to performance. It's also essentially a solved pr…

The main selling point of MySQL at the earlier 2000's was that it used an in-memory storage with only opportunistic disk writes. That made for an incredibly fast database, with obvious downsides that many people refused to notice.

What storage engine are you referring to? I've been using MySQL since the earlier 2000s, and your description doesn't match MyISAM (which uses the filesystem cache for data blocks, only using its own caching for indexes) nor InnoDB.

MyISAM does offer amazing write performance, at the cost of not being crash-safe. But given its reliance on the fs cache, it would be unusual to describe it as "in-memory storage". Anyway, the GP was talking about compromises that "can't be easily changed without breaking the ecosystem", which doesn't describe MyISAM either -- MyISAM is basically deprecated in modern MySQL deployments.

Re: Postgres 11 – A First Look

#169

Post by davidgould » Sat, Sep 22, 2018, 9:23 PM UTC

rgbrenner wrote: post #100

davidgould wrote: post #95

Earlier quoted context omitted.

I've been involved with postgres from before it had SQL, at Ilustra we added SQL in a parallel effort to the postgresql open source project and I'm not sure I'd agree with your points: Specifically, POSTQUEL was not really obsolete, it's just that the market had picked SQL by then. Compare the fates of git versus Mercurial. Mercurial is not obsolete, but git is the clear popularity winner. Second, I don't recall at a…

Sure, of course QUEL still worked... but Postgres was one of the last databases to use QUEL. It was pretty clear SQL won even when Postgre was just getting started (mid-80s). The Ingres project (that developed QUEL) ended by the time Postgres started (obviously, since the name is Postgres = Post Ingres). Ingres influenced Sybase, which switched to SQL in the late 80s. Ingres Corp even switched to SQL in the late 80s.…

I agree that commercially SQL was winning by the time postgres got started, but I think it was not yet obvious there was room for only one query language. I also suspect that the early implementation shared a lot of Ingres code as the focus was on the "post-relational" topics. Building SQL would have been a distraction. Perhaps also Stonebraker did not want to Osborne Effect Ingres. I'm sure even early Oracle marketing would have had fun with "even the founder of Ingres agrees QUEL is obsolete". Finally there was a lot to like in QUEL and SQL has quite a few warts.

Thanks for the link. Fast path was mostly about the function manager and only incidentally about data access. As in, "if you call all these functions that are part of the infrastructure, you can get at data". But that's not really a navigational interface (get first of set), more a side effect of exposing the internals via the function manager.

I started at Sybase in 1988, there were a lot Britton Lee alums, who had also worked on the ingres project as well. I ran in the the rest at Illustra in the 90s.

Re: Postgres 11 – A First Look

#170

Post by ht85 » Sat, Sep 22, 2018, 9:28 PM UTC

tormeh wrote: post #121

pmontra wrote: post #87

Earlier quoted context omitted.

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…

Stored procedures are plaintext code running on production servers and is, in general, never cleanly redeployed. So by default they discourage source control. This is my primary problem with them. Also that (at least MySQL) has shitty error messages when you're trying to build/debug the things.

Maintaining and migrating stored procedures can be a pain, especially when you're running a single tenant infrastructure, and even more if you don't migrate everyone at the same time.

I've had a lot of success by creating a small build system for the database logic, that can be as simple as running a bunch of SQL files in order.

The most important aspect is for the build to be "pure", rebuilding the whole thing every time instead of incrementally changing the schema.

A practical way to do this is to have all your stored procedures in a separate schema from your data, and starting your build process with `DROP SCHEMA procs CASCADE`.