6 ms·

PostgreSQL 8.3 Released

34 points by lneves 19y ago

e1ven 19y ago
Looking over the XML datatye [1], at first it doesn't seem that this would be very useful.. It doesn't seem to index the XML, only store it in a string-like manner.. But when I look at the XML functions [2], I can start to see where the real value is. In Chron X, we're storing vast sums of XML in SQL as strings, so being able to parse it per-element and pull them could be very helpful. [1]http://www.postgresql.org/docs/8.3/static/datatype-xml.html http://www.postgresql.org/docs/8.3/static/datatype-xml.html [2]http://www.postgresql.org/docs/8.3/static/functions-xml.html http://www.postgresql.org/docs/8.3/static/functions-xml.html
- inklesspen 19y ago
  It doesn't index the XML because it also doesn't provide any comparison methods for XML. Largely because string comparisons on XML tend to be less than useful, I think.
  - bayareaguy 19y ago
    Although you can't index xml columns directly, you can define functional indexes over them. As long as you are careful to use the same function in your where clauses the index can be used. Here's an example taken from Nikolay Samokhvalov's XML Support in PostgreSQL paper[1]. CREATE INDEX i_table1_xdata ON table1 USING btree( xpath_array(xdata, '//person/@name') ); [1] - http://www.pgcon.org/2007/schedule/attachments/16-xml_in_postgresql.ps http://www.pgcon.org/2007/schedule/attachments/16-xml_in_pos...
- nradov 19y ago
  I'm not familiar with how they've done it in PostgreSQL, but DB2 does support XML indexes. You just have to write an XPath statement to represent what is stored in the index. Then you can write an XQuery statement and the query engine will use that index. It's quite fast.
sanj 19y ago
I've always been a fan of PostgreSQL. The one bit that appealed to me was its erstwhile ability to 'shard', or partition data across tables. http://enfranchisedmind.com/blog/2006/11/04/postgres-for-the-win/ http://enfranchisedmind.com/blog/2006/11/04/postgres-for-the... Any comments?
- lneves 19y ago
  If you haven't seen it yet, check this presentation about partitioning with PostgreSQL: http://images.omniti.net/omniti.com/talks/partitions-public.pdf http://images.omniti.net/omniti.com/talks/partitions-public....
- bayareaguy 19y ago
  Unfortunately the current PostgreSQL optimizer/executor doesn't do as much as it could when joining records from subtables partitioned with inherits clauses. If you have a query with a predicate that can be pushed down to a subtable index then you're probably ok. However if you are doing something more intensive like an unqualified aggregation then you'll be in for a disappointment since the executor doesn't know how to preserve ordering when it scans the subtables. It will always give you a hash join or a merge join involving an intermediate sort even when the sort could be avoided by using an index scan of the subtables with something like a priority queue. This can be worked around by doing your own incremental aggregations over the subtables and combining them, but it's a little bit of a pain.
aantix 19y ago
While I have enjoyed Postgres's reliability, the clients (for OSX) have left me feeling empty. A majority of the time I just want to see the tables. When using pgAdmin3, I am forced to expand Servers->Local Server->Databases-><the desired database>->Schema->Public->Tables And then when I finally get to the table I want, if I double click it, it brings up the properties for the table? Huh? So I figure out I must click the table icon on the toolbar to bring up the table. Instead of bringing up the data within the same pane, it pops up a second window? Grrr.... While the tool gets the job done, I'm starting to miss MySQL just for the sake of having CocoaMySQL at my side.
- lneves 19y ago
  > When using pgAdmin3, I am forced to expand Servers->Local Server->Databases-><the desired database>->Schema->Public->Tables Yes, that is annoying, but at least the 1.8.1 version of pgAdmin opens in the last db/schema used... that is the behavior I get. > So I figure out I must click the table icon on the toolbar to bring up the table. Instead of bringing up the data within the same pane, it pops up a second window? Grrr.... File -> Options -> Preferences. Unmark "Show object properties on doubleclick in treeview?" Does that help?
- Locke 19y ago
  Why not just use psql and '\d my_table'?
- jawngee 19y ago
  PGAdmin for OSX is a steaming pile. I use a combination of tools: Aqua Data Studio PgMaestro via Parallels
- pivo 19y ago
  I just started using Navicat Lite for PostgreSQL on OSX. Not open source, but free and seems pretty nice so far. Might be worth a look.
brlewis 19y ago
Who else here besides me is using PostgreSQL for a public web site? http://ourdoings.com/ http://ourdoings.com/
- jawngee 19y ago
  We are. http://www.massify.com http://www.massify.com
  - techbusiness 18y ago
    http://www.centraldesktop.com http://www.centraldesktop.com uses PostgreSQL
- bayareaguy 19y ago
  I believe Hi5 is a big PostgreSQL user. They gave a talk on how they use it about 7 months ago. POSTGRES AT HI5, Paul Lindner / Ram Gudavalli, June 12th, 2007 - http://www.inuus.com/talks/hi5_pug_06122007.pdf http://www.inuus.com/talks/hi5_pug_06122007.pdf
- danielha 19y ago
  Disqus does.
- gleb 19y ago
  We do: http://www.acunote.com http://www.acunote.com (Agile Project Management)
- emmett 19y ago
  Justin.tv does.
patrickg-zill 19y ago
Sloan School of Management at MIT uses PostgreSQL for their "dotLRN" based system... http://dotlrn.org http://dotlrn.org Harvard's Leadership for a Networked World uses PG - http://lnwprogram.org http://lnwprogram.org DIYPlanner.com uses Drupal/PG - http://diyplanner.com http://diyplanner.com PowerfulIntentions.com (lots of forums, separate user pages, etc. all dynamically generated from PG database) - http://www.powerfulintentions.com http://www.powerfulintentions.com
djingorix 19y ago
http://www.teenwag.com http://www.teenwag.com http://www.hi5.com http://www.hi5.com http://www.skype.com http://www.skype.com
techbusiness 18y ago
http://www.centraldesktop.com http://www.centraldesktop.com uses PostgreSQL

34 points by lneves 19y ago

e1ven 19y ago

Looking over the XML datatye [1], at first it doesn't seem that this would be very useful.. It doesn't seem to index the XML, only store it in a string-like manner.. But when I look at the XML functions [2], I can start to see where the real value is. In Chron X, we're storing vast sums of XML in SQL as strings, so being able to parse it per-element and pull them could be very helpful. [1]http://www.postgresql.org/docs/8.3/static/datatype-xml.html http://www.postgresql.org/docs/8.3/static/datatype-xml.html [2]http://www.postgresql.org/docs/8.3/static/functions-xml.html http://www.postgresql.org/docs/8.3/static/functions-xml.html

inklesspen 19y ago
It doesn't index the XML because it also doesn't provide any comparison methods for XML. Largely because string comparisons on XML tend to be less than useful, I think.
- bayareaguy 19y ago
  Although you can't index xml columns directly, you can define functional indexes over them. As long as you are careful to use the same function in your where clauses the index can be used. Here's an example taken from Nikolay Samokhvalov's XML Support in PostgreSQL paper[1]. CREATE INDEX i_table1_xdata ON table1 USING btree( xpath_array(xdata, '//person/@name') ); [1] - http://www.pgcon.org/2007/schedule/attachments/16-xml_in_postgresql.ps http://www.pgcon.org/2007/schedule/attachments/16-xml_in_pos...
nradov 19y ago
I'm not familiar with how they've done it in PostgreSQL, but DB2 does support XML indexes. You just have to write an XPath statement to represent what is stored in the index. Then you can write an XQuery statement and the query engine will use that index. It's quite fast.

sanj 19y ago

I've always been a fan of PostgreSQL. The one bit that appealed to me was its erstwhile ability to 'shard', or partition data across tables. http://enfranchisedmind.com/blog/2006/11/04/postgres-for-the-win/ http://enfranchisedmind.com/blog/2006/11/04/postgres-for-the... Any comments?

lneves 19y ago
If you haven't seen it yet, check this presentation about partitioning with PostgreSQL: http://images.omniti.net/omniti.com/talks/partitions-public.pdf http://images.omniti.net/omniti.com/talks/partitions-public....
bayareaguy 19y ago
Unfortunately the current PostgreSQL optimizer/executor doesn't do as much as it could when joining records from subtables partitioned with inherits clauses. If you have a query with a predicate that can be pushed down to a subtable index then you're probably ok. However if you are doing something more intensive like an unqualified aggregation then you'll be in for a disappointment since the executor doesn't know how to preserve ordering when it scans the subtables. It will always give you a hash join or a merge join involving an intermediate sort even when the sort could be avoided by using an index scan of the subtables with something like a priority queue. This can be worked around by doing your own incremental aggregations over the subtables and combining them, but it's a little bit of a pain.

aantix 19y ago

While I have enjoyed Postgres's reliability, the clients (for OSX) have left me feeling empty. A majority of the time I just want to see the tables. When using pgAdmin3, I am forced to expand Servers->Local Server->Databases-><the desired database>->Schema->Public->Tables And then when I finally get to the table I want, if I double click it, it brings up the properties for the table? Huh? So I figure out I must click the table icon on the toolbar to bring up the table. Instead of bringing up the data within the same pane, it pops up a second window? Grrr.... While the tool gets the job done, I'm starting to miss MySQL just for the sake of having CocoaMySQL at my side.

lneves 19y ago
> When using pgAdmin3, I am forced to expand Servers->Local Server->Databases-><the desired database>->Schema->Public->Tables Yes, that is annoying, but at least the 1.8.1 version of pgAdmin opens in the last db/schema used... that is the behavior I get. > So I figure out I must click the table icon on the toolbar to bring up the table. Instead of bringing up the data within the same pane, it pops up a second window? Grrr.... File -> Options -> Preferences. Unmark "Show object properties on doubleclick in treeview?" Does that help?
Locke 19y ago
Why not just use psql and '\d my_table'?
jawngee 19y ago
PGAdmin for OSX is a steaming pile. I use a combination of tools: Aqua Data Studio PgMaestro via Parallels
pivo 19y ago
I just started using Navicat Lite for PostgreSQL on OSX. Not open source, but free and seems pretty nice so far. Might be worth a look.

brlewis 19y ago

Who else here besides me is using PostgreSQL for a public web site? http://ourdoings.com/ http://ourdoings.com/

jawngee 19y ago
We are. http://www.massify.com http://www.massify.com
- techbusiness 18y ago
  http://www.centraldesktop.com http://www.centraldesktop.com uses PostgreSQL
bayareaguy 19y ago
I believe Hi5 is a big PostgreSQL user. They gave a talk on how they use it about 7 months ago. POSTGRES AT HI5, Paul Lindner / Ram Gudavalli, June 12th, 2007 - http://www.inuus.com/talks/hi5_pug_06122007.pdf http://www.inuus.com/talks/hi5_pug_06122007.pdf
danielha 19y ago
Disqus does.
gleb 19y ago
We do: http://www.acunote.com http://www.acunote.com (Agile Project Management)
emmett 19y ago
Justin.tv does.

patrickg-zill 19y ago

Sloan School of Management at MIT uses PostgreSQL for their "dotLRN" based system... http://dotlrn.org http://dotlrn.org Harvard's Leadership for a Networked World uses PG - http://lnwprogram.org http://lnwprogram.org DIYPlanner.com uses Drupal/PG - http://diyplanner.com http://diyplanner.com PowerfulIntentions.com (lots of forums, separate user pages, etc. all dynamically generated from PG database) - http://www.powerfulintentions.com http://www.powerfulintentions.com

djingorix 19y ago

http://www.teenwag.com http://www.teenwag.com http://www.hi5.com http://www.hi5.com http://www.skype.com http://www.skype.com

techbusiness 18y ago

http://www.centraldesktop.com http://www.centraldesktop.com uses PostgreSQL