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

PostgreSQL Rising

wekeroad.com

Post reply on HN
Print view View original

121–130 of 204 posts

Re: PostgreSQL Rising

#121

Post by rosser »

lusr wrote: post #109
rosser wrote: post #62

Earlier quoted context omitted.

First, thank you very much for the clarifications and corrections. I wasn't on the engineering team that fixed the problem, and I'm not a Python guy; I just found the problem and explained the consequences of what was happening to the engineers. It seems I was mistaken in the particulars, for which I do apologize, both to you and everyone who's ever contributed to SQLAlchemy, and to anyone who was misinformed by my p…

> if your application is architected such that it leaves transactions, implicit or otherwise, open for extended periods. Can you give any good reason why you need to leave transactions open for extended periods? In my experience, it only happens when the developer in question does not understand the semantics of the DBMS and therefore hasn't suitably designed their solution, in which case it's hardly the DBMS's fault…

Can you give any good reason why you need to leave transactions open for extended periods?

Offhand, other than xid-level consistency for a backup as mentioned by the sibling post, no. [1] No, this case was pretty clearly the engineers not understanding what they were doing, as evidenced by it being easier to turn off autocommit, and add explicit BEGIN statements where they were needed, than add explicit ROLLBACKs everywhere they were needed.

That makes an implicitly transactional adapter seem a bit of a foot-gun to me, though. At a minimum, I think it places an unnecessary burden on engineers to have to "roll back" every time they even ask the db something. That's how memory leaks happen, too, and that's why we generally think garbage-collected languages are a Good Idea.

[1] That said, my preferred backup strategy is to take a filesystem-level snapshot of the db volumes, mount that, and start a second Postgres instance against it. It'll "recover" from an "unclean shutdown", and then you can take your backup without incurring the penalty of holding open a transaction that long in the presence of concurrent writes.

You do pay some write-performance penalty for the snapshot while it's open, but upon releasing it, the disk pages are immediately gc-ed, and you're already taking your backup at a low-traffic point anyway, right? Disk pages in a Postgres cluster allocated because vacuuming was hindered by holding an xid for extended periods are merely autovacuumed, however; they're reclaimed for later use, not released.

Re: PostgreSQL Rising

#122

Post by epimenov »

einhverfr wrote: post #66
spudlyo wrote: post #3

The complaint that MySQL is by default loosey-goosey with your data is valid, but it's an easy default to change. Here is what happens when you run some of the commands shown in that 'Why Not MySQL?' video on a sanely configured MySQL system by setting SQL_MODE to TRADITIONAL. This mode also allows you to not have dates with zeroes, etc. mysql> alter table test change column my_money my_money decimal(2,0); Query OK,…

1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won't guarantee your data? 2) My larger frustration with MySQL is I have run into cases of single transactions deadlocking against themselves. These always happen when the following is true: * Executing an insert statement in the form of INSERT foo (bar) VALUES (1),…

There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...

Re: PostgreSQL Rising

#123

Post by jeltz »

newhouseb wrote: post #113
rickmb wrote: post #84

The primary target audience for hacker-to-hacker Postgres evangelism is MySQL users. Because let's face it, the choice for DBs like Oracle is usually made upstairs, and for very different reasons. So why do Postgres advocates insist on dissing MySQL with false and misleading arguments? The usual target is some default settings, when obviously there are three kinds of MySQL users: the ones that actually have a reason…

I'm not sure where in the article the author insults the _users_ of MySQL as stupid, but rather insults some decisions made by MySQL as ill-advised. Incidentally, I was not aware of issues around default settings with MySQL when I switched our infrastructure to Postgres - the primary reason we switched was transactional and much more efficient DDL changes. I was at a company previously that I felt was seriously hampe…

Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant.

Another interesting part about your benchmarks are that they throw serious doubts on the popular myth that count(*) is slow in PostgreSQL. And your benchmarks were made before PostgreSQL 9.2 which will add index-only scans.

Re: PostgreSQL Rising

#124

Post by einhverfr »

epimenov wrote: post #122
einhverfr wrote: post #66

Earlier quoted context omitted.

1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won't guarantee your data? 2) My larger frustration with MySQL is I have run into cases of single transactions deadlocking against themselves. These always happen when the following is true: * Executing an insert statement in the form of INSERT foo (bar) VALUES (1),…

There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...

That certainly is odd behavior however reading through the whole email thread that seems to be one complicated issue with a two processes obtaining different locks to the same row. Probably not ideal, but maybe I need to recalibrate my definition of braindead because in real-world examples, that would be highly annoying.

Re: PostgreSQL Rising

#125

Post by epimenov »

einhverfr wrote: post #124
epimenov wrote: post #122

Earlier quoted context omitted.

There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...

That certainly is odd behavior however reading through the whole email thread that seems to be one complicated issue with a two processes obtaining different locks to the same row. Probably not ideal, but maybe I need to recalibrate my definition of braindead because in real-world examples, that would be highly annoying.

It happened to us in the real world. And was fixed in 9.2

Re: PostgreSQL Rising

#126

Post by lusr »

rosser wrote: post #121
lusr wrote: post #109

Earlier quoted context omitted.

> if your application is architected such that it leaves transactions, implicit or otherwise, open for extended periods. Can you give any good reason why you need to leave transactions open for extended periods? In my experience, it only happens when the developer in question does not understand the semantics of the DBMS and therefore hasn't suitably designed their solution, in which case it's hardly the DBMS's fault…

Can you give any good reason why you need to leave transactions open for extended periods? Offhand, other than xid-level consistency for a backup as mentioned by the sibling post, no. [1] No, this case was pretty clearly the engineers not understanding what they were doing, as evidenced by it being easier to turn off autocommit, and add explicit BEGIN statements where they were needed, than add explicit ROLLBACKs eve…

I'm not familiar with PG but I am planning on using it in my current project, which is why I'm interested in understanding whether these are really DBMS issues.

In the .NET world, for all the database adapters I've used (MSSQL, SQLCE, Oracle, MDB), individual statements are implicitly wrapped in transactions when no explicit transaction has been specified - not sessions/connections.

I agree the "implicit transaction per session/connection" design is confusing and likely to result in bugs. While the standard .NET approach limits bugs, the downside is poorer performance when an unaware developer uses numerous implicit transactions for multiple statements where a single explicit transaction would suffice.

Experts in the area appear to recommend always using explicitly managing transactions (e.g. http://ayende.com/blog/3775/nh-prof-alerts-use-of-implicit-t... ), and this is the approach I follow in my own work now.

Re: PostgreSQL Rising

#127

Post by mgkimsal »

jeltz wrote: post #123
newhouseb wrote: post #113

Earlier quoted context omitted.

I'm not sure where in the article the author insults the _users_ of MySQL as stupid, but rather insults some decisions made by MySQL as ill-advised. Incidentally, I was not aware of issues around default settings with MySQL when I switched our infrastructure to Postgres - the primary reason we switched was transactional and much more efficient DDL changes. I was at a company previously that I felt was seriously hampe…

Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant. Another interesting part about your benchmarks are that they throw seri…

"... throw serious doubts on the popular myth that count() is slow in PostgreSQL."

Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count() from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right?

Whoah - just did a check on a real table with a whopping 2 million records - postgresql 9 just blazed through that in select count(*) from student; count --------- 2032609 (1 row)

w00t! 7 seconds! I'm not even sure how that myth got started, let alone why people still believe that hogwash.

Re: PostgreSQL Rising

#128

Post by arethuza »

rickmb wrote: post #84

The primary target audience for hacker-to-hacker Postgres evangelism is MySQL users. Because let's face it, the choice for DBs like Oracle is usually made upstairs, and for very different reasons. So why do Postgres advocates insist on dissing MySQL with false and misleading arguments? The usual target is some default settings, when obviously there are three kinds of MySQL users: the ones that actually have a reason…

I suspect the main reason for organisations running Oracle (and MS SQL Server) is because you want to run 3rd party applications.

Re: PostgreSQL Rising

#129

Post by gaius »

taligent wrote: post #119
epo wrote: post #104

Earlier quoted context omitted.

Typical fanboy bluster. What's dishonest about the criticism, be specific? What you really mean to say is that most MySQL users use it because it is there and take it as it has been configured for them. MySQL is the storage platform for people who do not know about databases and probably only use it because some blogging product requires them to.

> MySQL is the storage platform for people who do not know about databases. Exactly. I mean seriously what do Facebook, Twitter, Yelp, LinkedIn, Flickr etc know about storing lots of data.

If FB or twitter miss a status update, it's no big deal. If you bank misses your salary going in, it is a HUGE deal. Use the right tool for the job.

Re: PostgreSQL Rising

#130

Post by mgkimsal »

Something that tends to get overlooked in pg/mysql discussions is administration - more specifically account management. all of this is done in the 'mysql' database via SQL directly in mysql ('insert into user', 'grant permission', etc). Most tutorials on PG I've seen over the years show these things being done from the commandline, and assume a unix environment, and an understanding of connecting from various account shells and such.

i've been made aware that you that there are other ways of dealing with user/perm management in pg, but from the perspective of people running shared hosting, the default tutorials feel extremely burdensome compared to managing everything in one database via SQL (no need for multiple user accounts on the system - just multiple user accounts on the database).

Post reply on HN