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

31–40 of 47 posts

Re: PostgreSQL 9.1 released

#31

Post by DrJokepu »

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…

As a PostgreSQL fan myself, the main thing I miss in PostgreSQL (as opposed to DB2 or Oracle) is nice and simple hot backups with point-in-time recovery. It is certainly possible but (at least in 9.0) it requires a non-trivial level of understanding of the WAL archiving process to set it up and run it smoothly and safely. Given how important backups are, I still tend to stay away from messing with WAL logs and just use SQL dumps (pg_dump) for backups for the time being. I really miss the simplicity of dealing with transaction logs and roll forwarding in DB2.

Re: PostgreSQL 9.1 released

#32

Post by megaman821 »

socratic wrote: post #25
wulczer wrote: post #22

Earlier quoted context omitted.

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?

Materialized views and query hints are the big ones the PostgreSQL is lacking.

Re: PostgreSQL 9.1 released

#34

Post by [deleted] »

netghost wrote: post #14
samstokes wrote: post #13

Earlier quoted context omitted.

Can anyone give an example use case for this? I'm not sure I fully understand what it means.

In spatial queries, find the 5 restaurants closest to me. In full text search, find the 5 words closest to "fuschia" so you can spell check, or try to search for what you __think__ the user meant to type.

[deleted]

Re: PostgreSQL 9.1 released

#35

Post by samstokes »

samstokes wrote: post #13
jgavris wrote: post #11

KNN indexing!

Can anyone give an example use case for this? I'm not sure I fully understand what it means.

Nice, thanks for all the examples and explanations!

Anyone know if it's possible to define a custom distance metric for use with this? We don't currently use full-text or spatial indices, but I can think of some cool things we could do with a generalised notion of "distance".

Re: PostgreSQL 9.1 released

#36

Post by Maascamp »

DrJokepu wrote: post #31
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…

As a PostgreSQL fan myself, the main thing I miss in PostgreSQL (as opposed to DB2 or Oracle) is nice and simple hot backups with point-in-time recovery. It is certainly possible but (at least in 9.0) it requires a non-trivial level of understanding of the WAL archiving process to set it up and run it smoothly and safely. Given how important backups are, I still tend to stay away from messing with WAL logs and just u…

If you read the release notes I believe the new pg_basebackup tool satisfies your needs.

Re: PostgreSQL 9.1 released

#37

Post by kiwidrew »

samstokes wrote: post #35
samstokes wrote: post #13

Earlier quoted context omitted.

Can anyone give an example use case for this? I'm not sure I fully understand what it means.

Nice, thanks for all the examples and explanations! Anyone know if it's possible to define a custom distance metric for use with this? We don't currently use full-text or spatial indices, but I can think of some cool things we could do with a generalised notion of "distance".

Yes, it is possible. Postgres supports custom data types and operators on those data types, and (using the GiST subsystem) you can create indexes using those custom operators.

Unfortunately, there is a caveat: this will more than likely require writing custom C code. I've never tried to implement GiST indexing for a new data type, so I don't know just how much effort would be required.

What kind of distances did you have in mind?

Re: PostgreSQL 9.1 released

#38

Post by simcop2387 »

saurik wrote: post #10
jvdongen wrote: post #9

Earlier quoted context omitted.

For my multi-lingual-content-needs I work with a main table which only has the non-translatable data items and a subtable which contains the translatable content, one row per language per row from the main table. Then I do a join to get a single full 'object' in the language of choice. And per the postgres docs I can choose collation at query time. Thus the following query would do the trick if I'm correct: select *…

Most content is not "translated", it just needs to be differently collated. If you have a company directory, you have the names of every user in the company in a table, and you need to display that information in a collation based on the locale of the viewing user. Having to have a new table for every language that contains the same data as the main table is just pointless overhead. Also, while you can choose the col…

Simply out of curiosity for this same topic, do you happen to know of a good resource for finding out even just some of the less trivial differences that this solves? I'm sure it does but off hand I don't know them (I'm not all that multilingual).

I understand it'll bring in glyph orderings that don't exist in en_US or whatever you've got the default set to, such as 'Ç' in french among others.

Re: PostgreSQL 9.1 released

#39

Post by simcop2387 »

megaman821 wrote: post #32
socratic wrote: post #25

Earlier quoted context omitted.

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?

Materialized views and query hints are the big ones the PostgreSQL is lacking.

Query hints are not likely to happen, at least not in the way that they do in other databases. There's been a number of discussions about it and you can see some of the aftermath at http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

As for Materialzed Views, there's some options there currently, though it looks like native support would be a lot nicer: http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized...

Re: PostgreSQL 9.1 released

#40

Post by saurik »

simcop2387 wrote: post #38
saurik wrote: post #10

Earlier quoted context omitted.

Most content is not "translated", it just needs to be differently collated. If you have a company directory, you have the names of every user in the company in a table, and you need to display that information in a collation based on the locale of the viewing user. Having to have a new table for every language that contains the same data as the main table is just pointless overhead. Also, while you can choose the col…

Simply out of curiosity for this same topic, do you happen to know of a good resource for finding out even just some of the less trivial differences that this solves? I'm sure it does but off hand I don't know them (I'm not all that multilingual). I understand it'll bring in glyph orderings that don't exist in en_US or whatever you've got the default set to, such as 'Ç' in french among others.

I do not have a good resource, however, I know a few off the top of my head: 1) characters with modifiers, like umlauts, sometimes collate the same, and sometimes collate differently; 2) multiple characters may collate as a single character, such as "ll" (I just did a search to verify that this was the case in Spanish, and found the Collation page on Wikipedia, which you might find interesting); and 3) different locales may choose to collate numbers using different algorithms (in English we usually expect "1,000" to sort after "200", but if "," is a decimal point, then you might not).
Post reply on HN