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

PostgreSQL Tips and Tricks

blog.gtuhl.com

Post reply on HN
Print view View original

21–29 of 29 posts

Re: PostgreSQL Tips and Tricks

#21

Post by gtuhl »

figital wrote: post #4

So glad to see PostgreSQL bubbling up these day's as it's been my DB of choice for many years. I'd love to see a more portable version and of course something like mySQL's group_concat(). If you are game for trying it out be sure to check out the tools at sqlmanager.net (unfortunately they don't offer Linux versions anymore).

I used to be a huge MySQL fan until a coworker forced us to use PostgreSQL for a big project. At the start I wasn't happy about it but have since completely changed sides. It is an amazing piece of software with some exceptional code and engineering behind it. When you are faced with high concurrency (especially if you have nontrivial write volume) PostgreSQL is a beast.

We peak at 1000s of transactions a second on an OLTP database that is over 100GB in size and PostgreSQL handles it like a champ.

Re: PostgreSQL Tips and Tricks

#22

Post by rjurney »

smithjchris wrote: post #18
rjurney wrote: post #14

Earlier quoted context omitted.

Because if you're going to do that - why not drop the SQL and the ORM altogether? Most shops at that level aren't doing analysis on their production SQL anyway - they dump that into a data warehouse. So why not do your CRUD on NoSQL and dump that some place ppl can run SQL on it?

We do it the other way round to your suggestion. SQL Server + NHibernate does handle the CRUD stuff, custom fields and DDD-based domain logic very well. NoSQL (CouchDB in our case) doesn't handle transactions, locking and business rules effectively. However it excels at providing extremely fast views on schemaless data such as our core domain model plus custom fields.

Definitely interesting :)

Re: PostgreSQL Tips and Tricks

#23

Post by jwilliams »

rjurney wrote: post #14
jwilliams wrote: post #7

Earlier quoted context omitted.

Agreed. Most of the serious commercial databases I've worked with/near/on have been de-normalised to some extent. e.g. SalesForce run massive amounts of data. Their implementation really boils down to one de-normalised table; with a dozen or so supporting tables. When I hear about some (not all) NoSQL implementations I occasionally wonder why they just didn't adopt a de-normalised SQL solution.

Because if you're going to do that - why not drop the SQL and the ORM altogether? Most shops at that level aren't doing analysis on their production SQL anyway - they dump that into a data warehouse. So why not do your CRUD on NoSQL and dump that some place ppl can run SQL on it?

Because often that's going too far. Quite often you still need transactions, established technologies, some structured-data, , etc, etc.. Sometimes a regular SQL database, but with some de-normalised tweaks is all you need.

There is definitely a place for NoSQL. However, many projects simply don't need to bite this off. They could start with SQL, from there you can decide how rigid/structured you want to be... I definitely agree that this comes with baggage; but if you're prepared to be pragmatic it's often got a lot of upside.

Re: PostgreSQL Tips and Tricks

#24

Post by olefoo »

berntb wrote: post #17
rudd wrote: post #6

Earlier quoted context omitted.

Yes, I'm pretty sure charset support in Postgres is at the database level, not the table level. See here: http://www.postgresql.org/docs/8.4/interactive/multibyte.htm...

Ah, thanks. Sigh. (No book recommendations for something like e.g. "High Performance MySQL"? Well, nice with no fanatical fanboys in this area, at least. :-)

If you are looking for a book on Postgresql the Douglas book http://www.amazon.com/PostgreSQL-Developers-Library-Korry-Do... is pretty good introduction to most of the topics specific to postgres, it includes a sample implementation of a postgres accessing script in most of the languages commonly used for the task, which can be a bit repetitive (perl, python, php, java, C, same tune different instrument) but that does make it a fairly good reference.

Re: PostgreSQL Tips and Tricks

#25

Post by abalashov »

rudd wrote: post #5
psnj wrote: post #2

Some reasonable advice in there, but #3? Ruh roh.

It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.

The difference is in a matter of degree. I agree that denormalisation can be beneficial - even necessary - in certain scenarios, particularly when other design optimisations are exhausted. But that's a far cry from "the normal forms you learned about in school are an utter waste of time that will throw your database performance in the gutter."

Re: PostgreSQL Tips and Tricks

#26

Post by abalashov »

#4 seems like a zealous overgeneralisation. JOINs get a lot of crap.

Yes, it's no secret that large joins on huge amounts of data can often be very intensive. From that does not follow, however, the exhortation to simply avoid them categorically. That's a little too much blanket statement for me.

For instance, joins are often used in situations where there is a numeric type column that refers to a very small table of enumerated values that have a textual or other translation, and there is a need to produce the latter in a single query. There's nothing wrong with that join from a performance standpoint, even for very large values of n.

Re: PostgreSQL Tips and Tricks

#27

Post by gtuhl »

abalashov wrote: post #26

#4 seems like a zealous overgeneralisation. JOINs get a lot of crap. Yes, it's no secret that large joins on huge amounts of data can often be very intensive. From that does not follow, however, the exhortation to simply avoid them categorically. That's a little too much blanket statement for me. For instance, joins are often used in situations where there is a numeric type column that refers to a very small table of…

JOINs rightly get a lot of crap - they are massive performance bottlenecks if you are working with large tables.

Having a single awkward example does not detract from that.

I'll add to this by noting the post does say "For smaller tables it doesn’t matter but as tables get bigger avoid joining when you can" which is sound advice.

I'd say joining scales up to perhaps a million or two rows unless you have a lot of RAM (you can see join spills to disk in the EXPLAIN ANALYZE output). I often am working with tables in the 10-30 million row range so my perspective is probably a little slanted towards the negative.

Re: PostgreSQL Tips and Tricks

#28

Post by gtuhl »

abalashov wrote: post #25
rudd wrote: post #5

Earlier quoted context omitted.

It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.

The difference is in a matter of degree. I agree that denormalisation can be beneficial - even necessary - in certain scenarios, particularly when other design optimisations are exhausted. But that's a far cry from "the normal forms you learned about in school are an utter waste of time that will throw your database performance in the gutter."

Agreed it was too strong of a statement, but denormalization really is something you do all the time in big databases. It is not some targeted last resort technique.

If you have 30 million rows in a table you absolutely cannot do joins so you bring everything in that you commonly need.

Re: PostgreSQL Tips and Tricks

#29

Post by gtuhl »

berntb wrote: post #17
rudd wrote: post #6

Earlier quoted context omitted.

Yes, I'm pretty sure charset support in Postgres is at the database level, not the table level. See here: http://www.postgresql.org/docs/8.4/interactive/multibyte.htm...

Ah, thanks. Sigh. (No book recommendations for something like e.g. "High Performance MySQL"? Well, nice with no fanatical fanboys in this area, at least. :-)

Thought I was add that the PostgreSQL documentation is really, really good for learning. It is a bit dense but overall very well written, includes nontrivial examples, and you can really get a solid dive into a topic reading it.

The current 8.3 documentation is here: http://www.postgresql.org/docs/8.3/static/

I especially enjoy the sections on indexes: http://www.postgresql.org/docs/8.3/static/indexes.html

Post reply on HN