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

PostgreSQL 9.1 released

postgresql.org

Post reply on HN
Print view View original

21–30 of 47 posts

Re: PostgreSQL 9.1 released

#21

Post by socratic »

Should I as a web app developer at a startup be looking for an RDBMS beyond PostgreSQL, probably a commercial one?

I have somewhat of a database background, so I see the obvious advantages of PostgreSQL over MySQL. In particular, things like more procedural language support, a better query optimizer, better concurrency control, etc. (Though things like Amazon RDS are compelling from a deployment perspective.)

I fundamentally believe that using an RDBMS, rather than a NoSQL data store is the right approach for rapid development of web apps. (Though, I primarily mean this as an attack on, e.g., MongoDB, since I think Redis is great, just not a replacement for an RDBMS.)

However, I have almost no experience with the more advanced end of the RDBMS spectrum, primarily because they tend to cost money (for the real, non-free versions). Should I be learning/looking at DB2? Should I be learning/looking at Oracle?

Or do the additional features of these more advanced RDBMS options require such specialized scenarios (a bank, a big enterprise) or such specialized hardware (weird clustered setups) that MySQL/PostgreSQL will always be just as good?

Re: PostgreSQL 9.1 released

#22

Post by wulczer »

socratic wrote: post #21

Should I as a web app developer at a startup be looking for an RDBMS beyond PostgreSQL, probably a commercial one? I have somewhat of a database background, so I see the obvious advantages of PostgreSQL over MySQL. In particular, things like more procedural language support, a better query optimizer, better concurrency control, etc. (Though things like Amazon RDS are compelling from a deployment perspective.) I funda…

A short, probably biased and not 100% precise answer would be: no, you don't need to look for a commercial RDBMS.

The real answer is: always evaluate options. Make sure the solution you choose supports everything you need and that you will be able to learn how to use it or hire people who can. Without knowing what your requirements are it's impossible to say, but in the vast majority of cases, PostgreSQL will be as good as Oracle, MS SQL Server or DB2.

If your evaluation indicates that you need to pay for one of these, double and triple check it... and only if you're 100% sure that's the case, shell out for a commercial RDBMS.

Re: PostgreSQL 9.1 released

#23

Post by jeffdavis »

saurik wrote: post #3

The only thing I feel is wrong with this (really: the /only thing/, which is fricken awesome... I love PostgreSQL from the bottom of my heart, and have been using it for almost all of my database needs since the late 90s) is "per-column collation": collation is not a problem "per-column", it is a problem "per-index", which means you really want it "per-operator class". Here's the use case: you have a website, and you…

"With per-column collation, you are being advocated to have two fields, one english_name, and one french_name, that have /the same content/, but are defined using a different collation, so that the ordering condition on them becomes language-dependent."

I don't think that's the intention. Think of column collation as the "default" for that column, and you override it with the COLLATE clause. So if most of your ordering/range queries are based on american english, set the column collation to "en_US", and when you have a query that requires french ordering, then use the COLLATE clause by the column reference, and the ordering will change for french.

I agree that SQL makes all of these things more confusing than they need to be, but supporting the standard for this concept has some value.

"CREATE INDEX my_index ON my_table (a english_collation_ops);"

I'm not sure if you're aware of this or not, but CREATE INDEX accepts a COLLATE clause in addition to the operator class. So the collation is kind of an extra parameter to the operator class (or something like that).

So, it's not a question of capability, because I think the capability you want is readily there. However, if you are saying that it's more confusing than it needs to be, then I agree.

Re: PostgreSQL 9.1 released

#24

Post by lhnn »

pbreit wrote: post #12
ConceitedCode wrote: post #6

I recently switched to PostgreSQL from MySQL and I couldn't be happier about it.

It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?

Because I spent $60 on an awesome MySQL reference guide and tutorial.

http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/re...

Re: PostgreSQL 9.1 released

#25

Post by socratic »

wulczer wrote: post #22
socratic wrote: post #21

Should I as a web app developer at a startup be looking for an RDBMS beyond PostgreSQL, probably a commercial one? I have somewhat of a database background, so I see the obvious advantages of PostgreSQL over MySQL. In particular, things like more procedural language support, a better query optimizer, better concurrency control, etc. (Though things like Amazon RDS are compelling from a deployment perspective.) I funda…

A short, probably biased and not 100% precise answer would be: no, you don't need to look for a commercial RDBMS. The real answer is: always evaluate options. Make sure the solution you choose supports everything you need and that you will be able to learn how to use it or hire people who can. Without knowing what your requirements are it's impossible to say, but in the vast majority of cases, PostgreSQL will be as g…

I think that's probably true, though I do wonder from a purely engineering standpoint what features Oracle/DB2/MS have at this point that PostgreSQL does not. Special index types? Query hints? Suggestions for physical layout on table creation?

Re: PostgreSQL 9.1 released

#26

Post by samstokes »

socratic wrote: post #21

Should I as a web app developer at a startup be looking for an RDBMS beyond PostgreSQL, probably a commercial one? I have somewhat of a database background, so I see the obvious advantages of PostgreSQL over MySQL. In particular, things like more procedural language support, a better query optimizer, better concurrency control, etc. (Though things like Amazon RDS are compelling from a deployment perspective.) I funda…

things like Amazon RDS are compelling from a deployment perspective

Check out http://postgres.heroku.com for an analogous deployment option for Postgres.

Re: PostgreSQL 9.1 released

#27

Post by goodside »

pbreit wrote: post #12
ConceitedCode wrote: post #6

I recently switched to PostgreSQL from MySQL and I couldn't be happier about it.

It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?

I have an unusual reason, but a strong one: I primarily use relational DBs for large-scale analysis of frozen snapshots of data, rather than transactional loads. PostgreSQL has MVCC features built into it at the most fundamental levels where they cannot be disabled, and as such it's not suitable for the types of queries that I frequently run against MySQL. In the most extreme (and trivial) case, you can't run a "SELECT COUNT(*)" on a table in PostgreSQL without a full sequential scan of your data, which can be a huge expense when your row counts are in the tens or hundreds of millions. MySQL, in contrast, can return a cached answer instantly, which isn't possible under PostgreSQL. Less trivially, MySQL is still drastically faster for aggregates on low-cardinality fields.

Yes, I know you could set something up with triggers, but the point is that it needs to be simple enough for frequent, ad-hoc usage. Usually the time I need to know how big a table is when I just made it, and I might well drop it five minutes later. I'm not going to set up an elaborate network of meta-data tables when MySQL will just do it for me for free.

Not trying to hate on Postgres, by the way. I fully get that it's superior in most regards, and I use it frequently just for the much stronger support of user-defined functions. But MySQL does still have a few tricks left in it.

Re: PostgreSQL 9.1 released

#28

Post by rbranson »

For a trivial, synthetic write benchmark that I usually use to benchmark hardware and/or config changes, I'm seeing slight slowdowns for non-concurrent loads, and solid improvements for concurrent loads with.

For 1-2 clients, I'm seeing ~8% slower.

For 4 clients, 7.2% faster.

8 clients, 15% faster.

16 clients, 16.4% faster.

32 clients, 11% faster.

64 clients, 10% faster. Aggregate performance starts to level off here, so I stopped.

These are just cycling super simple INSERTs/DELETEs against the same table, columns data is 1K string, 100 byte string, then a concatenation of the pid and current iterator count. No indexes or primary keys. Each client is just a fork that performs 10,000 INSERTs, then 10,000 DELETEs in a loop of 10,000 iterations.

For the record, that's around 6,729 writes per second with 32 clients. If I set synchronous_commit = OFF in each client before running the benchmark, it's 27,157/sec. Then, if I reduce the first column size to 100 bytes, it's 50,592/sec. Impressive. I'm sure the synchronous_commit improvement would be much more drastic on disks without BBU write caches.

Database server is a 4-core Nehalem-based Xeon with 16GB RAM and a SAS disk array. PostgreSQL configuration has been decently tuned and full write durability is retained all the way down to the disks.

Re: PostgreSQL 9.1 released

#29

Post by jeltz »

goodside wrote: post #27
pbreit wrote: post #12

Earlier quoted context omitted.

It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?

I have an unusual reason, but a strong one: I primarily use relational DBs for large-scale analysis of frozen snapshots of data, rather than transactional loads. PostgreSQL has MVCC features built into it at the most fundamental levels where they cannot be disabled, and as such it's not suitable for the types of queries that I frequently run against MySQL. In the most extreme (and trivial) case, you can't run a "SELE…

If I remember correctly it is only with MyISAM you get instant count(*) while InnoDB, just like PostgreSQL, has to look at every row of the table (or every row of the index since InnoDB also supports index only scans). This is due to both PostgreSQL and InnoDB being MVCC (multi-version concurrency control) database engines.

And MyISAM is only fast in very specialized workloads with low write and read/write concurrency.

Re: PostgreSQL 9.1 released

#30

Post by paisleyrob »

goodside wrote: post #27
pbreit wrote: post #12

Earlier quoted context omitted.

It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?

I have an unusual reason, but a strong one: I primarily use relational DBs for large-scale analysis of frozen snapshots of data, rather than transactional loads. PostgreSQL has MVCC features built into it at the most fundamental levels where they cannot be disabled, and as such it's not suitable for the types of queries that I frequently run against MySQL. In the most extreme (and trivial) case, you can't run a "SELE…

For something like developer testing against frozen snapshots of data, you don't have the CRUD aspects, so turn fsync off (fsync = off in postgresql.conf). You'll note a large speed increase when you're not waiting on the disk to confirm transactions.
Post reply on HN