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

171–180 of 204 posts

Re: PostgreSQL Rising

#171

Post by jeffdavis »

AaronBBrown wrote: post #105
jeffdavis wrote: post #80

Earlier quoted context omitted.

Your concerns are constructive and well-placed. It happens that they are all either done or actively being improved, but that's not obvious unless you follow very closely, so keep 'em coming. "a scalable replication model that allows tiered replication" They do in 9.2 (currently beta), it's called cascading replication. The other replication features you mention are under active development by a team of reputed hacke…

As I mentioned, I do like PostgreSQL for its consistency and I want to like it even more. Cascading replication looks interesting - does it support the topology that I mention where the master has a subset of the from the slaves and the slaves can be written to? The documentation is difficult to decipher and lacks examples. I don't mean this to be MySQL vs PostgreSQL, but MySQL has had these features for over 10 year…

"Cascading replication looks interesting - does it support the topology that I mention where the master has a subset of the [tables??] from the slaves and the slaves can be written to?"

No. Cascading replication basically means that you can replicate from slaves to other slaves, rather than always replicating from the master. That allows you to form hierarchies, which is I thought what you meant by "tiered".

The current built-in replication is a form of phsyical replication, meaning that data pages are (more or less) identical on the master and slave. What you want is logical replication, which is being actively worked on now as a part of core postgres.

In the meantime, there are external logical replication systems like Slony, Bucardo, and Londiste. Slony is deserving of a reputation for being complex, but it's being actively developed and can be used for everything you mention (based on the very high level descriptions that you gave). I understand that "not in core, and hard to use" are pretty damning for a lot of cases, but it really does get the job done. And hopefully there will be in-core solutions in later versions of postgres.

"I don't mean this to be MySQL vs PostgreSQL, but MySQL has had these features for over 10 years and the kinks are already worked out."

I'm glad to hear that it's working for you.

"My X,Y, and Z list has been unchanging for 5 years"

For future reference, all of these features will fall under the heading "logical replication". When you see that, if it doesn't have all of the features you need, then they are probably going to arrive soon.

Postgres has had external logical replication (e.g. Slony) this whole time, which I think has somewhat reduced the demand to invest huge amounts of work in core. It looks like it fell short for your use case, unfortunately; and hopefully the current logical replication in core project will not.

Re: PostgreSQL Rising

#172

Post by jeffdavis »

einhverfr wrote: post #118
jeffdavis wrote: post #76

Earlier quoted context omitted.

"Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTre…

The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really…

"Now, it's rare that you are likely to get that into phone numbers..."

That was part of my point -- if your entire business revolves around phone numbers, because you are a phone company or something, then maybe a custom data type makes sense. Then do it, and do it right.

But if you think of a phone number as "something to call" then all you need to do is display it to the right person at the right time, and ensure that it reasonably matches something you can type on a phone. And that sounds more like a domain over a text field with a constraint, to me.

"I use custom types a lot for defining output tuples for stored procedures"

I don't really consider composite types "custom" types any more than an enum is a custom type. Maybe I misunderstood gfodor's statement.

Re: PostgreSQL Rising

#173

Post by ondrae »

The recent release of PostGIS 2, the spatial extension of Postgres, has been a huge benefit to geo software. It has increased the ease and speed at which we can now work with large data sets and do spatial computation on them.

My favorite part of PostGIS 2, besides much of the cleaned up syntax, is the new Geography datatype. It has the spatial projection built in to it, so it automatically does great arc computations. Before we had to write more clunky SQL to achieve this. Was a significant barrier for my learning postgis, which has now been removed.

Re: PostgreSQL Rising

#174

Post by jeffdavis »

arjn wrote: post #98

My opinion is contrary to most of the comments here. About a year ago we switched a multi-tiered app from MySQL/MSSqlServer to Postgres/Postgres. While everything works fine, after months of using it I find writing programs for and with MySql or MSSqlServer seems easier. One specific complaint I have is that there is no transaction control within pgsql stored procedures (functions). I had to kludge the db link module…

"One specific complaint I have is that there is no transaction control within pgsql stored procedures (functions). I had to kludge the db link module to get transactions to work."

Yes. Functions work within a transaction, which is usually a blessing. But there are cases where you want to do the transaction control in the procedure itself. This is a known request that is discussed as two different features: "stored procedures" (which is probably what you want) and "autonomous transactions".

Re: PostgreSQL Rising

#175

Post by j_s »

davyjones wrote: post #103
yorhel wrote: post #95

Earlier quoted context omitted.

Just tried to compile from git, but the qt-psql requirement wasn't very easy to satisfy on Arch. Your Ubuntu binary worked fine. Looks quite nice, and there certainly is a need for more PostgreSQL GUIs. Some feedback from the minute I used it: - You don't handle bytea columns very nicely. I'd expect those to be displayed in hex or so. - Browsing a table is very slow on tables with large columns. I was testing on a ta…

Thanks a lot for your response! > qt-psql requirement wasn't very easy to satisfy on Arch Whoops! My primary dev machine is Arch. Let me fire up a clean VM and try and reproduce the problem. Are you rolled to the latest? > - You don't handle bytea columns very nicely. I'd expect those to be displayed in hex or so. Unfortunately, the database types to Qt types get mapped in a pretty undesirable way (for this use case…

> - Browsing a table is very slow on tables with large columns.

Last time I did this I used a fixed-width column when displaying text fields. Everything built-in was able to handle things smoothly when clipping to just the first __ characters. (Of course you can't see it all then, but I've found it useful enough... and can paste a cell elsewhere to see all of it.)

Re: PostgreSQL Rising

#176

Post by AaronBBrown »

jeffdavis wrote: post #171
AaronBBrown wrote: post #105

Earlier quoted context omitted.

As I mentioned, I do like PostgreSQL for its consistency and I want to like it even more. Cascading replication looks interesting - does it support the topology that I mention where the master has a subset of the from the slaves and the slaves can be written to? The documentation is difficult to decipher and lacks examples. I don't mean this to be MySQL vs PostgreSQL, but MySQL has had these features for over 10 year…

"Cascading replication looks interesting - does it support the topology that I mention where the master has a subset of the [tables??] from the slaves and the slaves can be written to?" No. Cascading replication basically means that you can replicate from slaves to other slaves, rather than always replicating from the master. That allows you to form hierarchies, which is I thought what you meant by "tiered". The curr…

Thank you for the clarification about the PostgreSQL terminology. That will help me keep track of the progress. I am very pleased that the PostgreSQL community has finally started implementing native logical replication, which is really one of the biggest obstacles to widespread adoption.

Slony, in a word, is awful. It's probably as good as it can be given the limitations of working within a trigger-based world, but in the 3 years I spent maintaining Slony clusters, I lost count of the number of times that I had to take a site offline (sometimes for hours) in order to rebuild from scratch because of a failed schema change. Many times, I was able to recover by careful manipulation of the sl_events table, but far too frequently, the error was unrecoverable. Slony is slow, unreliable, not tolerant of high latency connections, and fragile. It was always one of my greatest frustrations that the PostgreSQL community saw it as a solution given how completely unreliable it is. Perhaps things have changed in the 2 years since I last touched it, but I am skeptical.

Please try to understand that my few objections to PostgreSQL come from actual real world experience with both MySQL and PostgreSQL, not from reading blog posts. Both products have great features as well as unpolished turds...it's just a matter of deciding which color of poop you are willing to clean out of the diaper. :)

Re: PostgreSQL Rising

#177

Post by adrianhoward »

yread wrote: post #90
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…

> usual target is some default settings for me it would be missing common table expressions, weak implementation of functions and SPs (recursion) and horrible .NET connector (connections dying, int can get returned as byte[]). And I'm not even mentioning the gazillions of advanced features that pgsql (and Oracle or SQL Server) has that mysql doesn't.

My personal all time mysql fave - values that are both null and not-null

http://use.perl.org/use.perl.org/_Adrian/journal/32627.html

Re: PostgreSQL Rising

#178

Post by jvdongen »

pestaa wrote: post #159
jvdongen wrote: post #106

Earlier quoted context omitted.

I actually have implemented a multi-tenant application based on Postgres 3 years ago and we have been using it in production ever since (for paying customers that is). As such I have some experience in this field and I'm actually in the process of moving (back) to a model where each customer has its own database (and application process). For context: I have a relatively low number of tenants (tens) which in turn eac…

Considering the low number of tenants in your application, I believe automation was not an important requirement for you (if at all), but how would you go about creating virtualenvs on-the-fly?

We actually do automate as much as possible (small team, lots to do). We've a Fabric script that sets up a complete new environment for a customer/tenant in one go. That includes creating and populating the virtualenv, creating a new database + database user etc. If the command is finished the customer basically is able to log in. And that script is actually only about a 200 lines of code or so.

The nice thing of Fabric is that it makes automating something hardly more work than doing something in a one-off fashion.

Re: PostgreSQL Rising

#179

Post by smsm42 »

I was reading this article with great interest, exactly up to the part where it claims MySQL is stupid. Maybe it's just me but I've had enough of articles that are written with the idea that the best way to promote your product is to bash your perceived competition and call it stupid, worthless, idiotic and broken. People, here's the news for you: people don't use your competition (or your beloved tool's competition) because they are stupid. They use it because of variety of reasons, but it almost never involves them being stupid, and calling their choice stupid is almost never a good way to get them to consider changing their preferences.

Even more disappointing, when the author proceeds from showing how stupid MySQL is (random collection of choices which can be plausibly decided in any way) to showing how much better Postgres it, he doesn't show anything useful. He just shows random collection of features that may be useful in some use case he probably encountered in his practice, but no compelling picture of a superior product. Nobody changes DB server because one has "tomorrow" keyword and another does not.

And when performance comparison is done - no numbers at all are quoted except for initial data size. Just bare claims "it performed well" - how well? Are these results comparable to what I can expect on my data with my setup? How much manual work (mentioned in passing as 2 experienced DBAs were working on it - but how complex was what they did?) would it require to make it perform? How much better would it perform if I hired a very good DBA? These are very important questions to consider when you choose the DB for your project, but completely omitted in the article.

Re: PostgreSQL Rising

#180

Post by AaronBBrown »

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),…

1. Yes, applications can set sql_mode themselves at the session level. An admin cannot prevent this. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Post reply on HN