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

71–80 of 204 posts

Re: PostgreSQL Rising

#71

Post by mb_72 »

pvh wrote: post #29
gfodor wrote: post #21

I'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's usually not a good idea to use too many esoteric database features when building an app, since it couples your system with a particular database. That said, even if you don't use PostgreSQL's whiz bang features, its stability, performance, and outright sanity with regards to handling data make it the right database to reach for in many cases. An…

I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors…

And I, also, disagree. I'm using the Developer Express ORM, XPO, and it provides 'drop-in portability' between a range of databases. I've successfully run my small-mid scale app on three different backends - MS SQL, MySQl and Postgres - simply by changing the connection string. Yes, it's .NET / Windows only, but drop-in portability is definitely not a myth.

Re: PostgreSQL Rising

#72

Post by einhverfr »

kstrauser wrote: post #34
marquis wrote: post #26

What are the scaling differences between MySQL and PostgreSQL? That's the main reason we haven't shifted and we have a new project coming up that I've been interested to use PostgreSQL with as one our developers prefers it, but are we opening a whole new can of worms on that front?

For vertical scaling, PostgreSQL is generally seen to be much faster. That varies by workload, of course, and I'm sure you could tailor a benchmark that shows MySQL to be dramatically faster than PostgreSQL. But in every benchmark or sample set I've come across, PostgreSQL tends to scale linearly with the number of processor cores and handles ridiculously unlikely concurrency demands with ease. MySQL has a reputation…

I have one customer who uses MySQL because they wrote their app around the query cache. The data is aggregated and then fed into PostgreSQL for real processing, and eventually reports generated and shipped back to MySQL....

Re: PostgreSQL Rising

#73

Post by einhverfr »

chimi wrote: post #32
marquis wrote: post #26

What are the scaling differences between MySQL and PostgreSQL? That's the main reason we haven't shifted and we have a new project coming up that I've been interested to use PostgreSQL with as one our developers prefers it, but are we opening a whole new can of worms on that front?

PostgreSQL is massively more scalable than MySQL.

Obviously it depends on what you are doing. However, if you are trying to keep everything in one consistent environment it does win out.

Also I expect that with 9.2 we will probably see a release of Postgres-XC which will kick the pants off any MySQL sharding solution out there.

Postgres-XC is a way to abstract database shards such that referential integrity etc. is enforced between shards. The best comparison to date would be to Teradata's clustering system. Better yet, Postgres-XC is just a set of patches on PostgreSQL, and is on-the-wire compatible, so you can swap it out when your database gets too big. This will also bring an ability to concurrently query different shards and aggregate the results without the application having to know anything about the sharding....

Re: PostgreSQL Rising

#74

Post by reidrac »

pjmlp wrote: post #68
pvh wrote: post #42

People often ask us at Heroku -- why Postgres? The short answer is: we needed to do something, and it's the best.

> we needed to do something, and it's the best . For such a statement I would rather take one of DB2, Oracle or SQL Server.

In my experience you don't want to provide X as a service based on a third party closed product (meaning: anything not open source & open community).

Re: PostgreSQL Rising

#75

Post by spudlyo »

tensor wrote: post #54
spudlyo wrote: post #33

Earlier quoted context omitted.

Definitely. When your application needs hundreds of read slaves in order to scale the load, PostgreSQL has always been everyone's first choice due to it's mature and historically awesome replication system. That's why companies who need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc) all have hundreds (if not thousands) of PostgreSQL machines in their infrastructure.

The fact that everybody uses a particular technology does not in fact answer the question of which scales better or which cannot scale. I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity.

The fact that everybody uses a particular technology does not in fact answer the question of which scales better or which cannot scale.

The fact that many large companies and organizations have successfully scaled a particular technology is a useful yardstick, and while it does little to answer the question of which scales better, it does, by the process of elimination, help answer the question of which cannot scale.

I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity.

I had hoped that others here would enjoy the sarcasm in the context of the full-throated yet fact-free advocacy of the parent, the text of which somehow still remains legible on an off-white background.

Re: PostgreSQL Rising

#76

Post by jeffdavis »

gfodor wrote: post #61
pvh wrote: post #29

Earlier quoted context omitted.

I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors…

It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom data types, for example, is quite weak, when considering the tradeoffs. They move complicated logic into the database, are unfamiliar to most developers, and end up needing to be reverse engineered if you want to move your data into different storage. I think…

"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, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators.

Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension".

If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.

Re: PostgreSQL Rising

#77

Post by jeffdavis »

gfodor wrote: post #61
pvh wrote: post #29

Earlier quoted context omitted.

I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors…

It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom data types, for example, is quite weak, when considering the tradeoffs. They move complicated logic into the database, are unfamiliar to most developers, and end up needing to be reverse engineered if you want to move your data into different storage. I think…

"it's a matter of reducing the complexity of migration"

You put the code in your application so you can switch out the database, but why do you want to switch databases?

You can't switch out the database for one with more features, because then you're not using the lowest common denominator any more, and you can't switch back.

It can't be licensing costs, because postgresql licenses are free.

The only other reason I can think of is performance. But trying to avoid database features just because you might want to migrate to a less-featureful database later seems more likely to result in performance problems than prevent them.

I agree you don't want to go out on a limb with crazy features just because you can. But those features are there for a reason, and might drastically simplify portions of your application if you use them. And a lot of them are SQL standard, just not supported properly in all database systems.

Also, I'd like to point out that database migrations themselves are quite rare once they become established in an organization. You might be able to migrate one fairly simple application if you bend over backwards trying to use only the simplest features; but once a few applications are depending on it, it's just too expensive.

Re: PostgreSQL Rising

#78

Post by jeffdavis »

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

Semantic changes in configuration files are problematic in MySQL for the same reasons they are problematic in PHP (the only other language that seems to do that):

* Your application then no longer works properly on a default install. Possibly in very subtle ways that you won't notice.

* Upstream will never make your settings the default. It would break too many applications, so you are on your own with those settings pretty much forever.

* Few users use non-default settings, so you are more likely to run into strange behaviors that are either bugs or can't be readily explained in a forum.

* Just imagine if you are trying to support multiple applications that each have their own settings they prefer. Especially if you want to integrate your data together at all, which is one of the main purposes of a database system.

Re: PostgreSQL Rising

#79

Post by pjmlp »

reidrac wrote: post #74
pjmlp wrote: post #68

Earlier quoted context omitted.

> we needed to do something, and it's the best . For such a statement I would rather take one of DB2, Oracle or SQL Server.

In my experience you don't want to provide X as a service based on a third party closed product (meaning: anything not open source & open community).

I imagine you don't do business in the enterprise world.

Re: PostgreSQL Rising

#80

Post by jeffdavis »

AaronBBrown wrote: post #50

I just watched a 10 minute video whereby nearly all the issues pointed out can be solved with one line in my.cnf or dynamically by setting SET GLOBAL server_sql_mode=TRADITIONAL. Yeah the default is no good. Learn your RDBMS and the problem goes away. Watching him point and click and move windows around also made it very difficult to follow. PostgreSQL is an awesome RDBMS, but adoption will never eclipse MySQL until…

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

"That, and the ability to reliably upgrade your binary without performing a massively time-consuming dump and reloading."

pg_upgrade has been available and you can upgrade from 8.3 to the latest without the dump/reload cycle. It has been a little rocky (by postgres standards) and some people have been hesitant, but it gets the job done and it's been improving a lot. A talk at a recent conference spoke quite highly of pg_upgrade despite running into some challenges (like a library versioning issue related to some perl functions they had and some multibyte characters I think). Not for the faint of heart, but if you really need this and don't mind reading a little (or hiring a consultant), you can make it work.

Every issue or every missing feature seems like a showstopper if you don't step back for a minute. No DBMS is perfect. Everyone has their list of "postgres is missing X,Y, and Z". The funny thing to me is that X, Y, and Z change with each release because the previous X and Y were added (often with greater flexibility than originally imagined), and there's a new alternative to Z.

Postgresql puts out a very high quality release every year with major features. Usually, it's a good balance of features requested by (potential?) users like you and new innovative features that move the database world ahead.

Post reply on HN