On Wed, Jul 23, 2008 at 3:48 AM, admin <mick@mjhall.org> wrote:
> I'm convinced that PostgreSQL's performance is not an issue (both because
> it's improved and traffic will be relatively low anyway), and that the
> benefits of PostgreSQL's advanced features are too good to ignore. I'm
> hoping to shift quite a bit of data processing into the database.
My experience has been that pgsql is more than a match for MySQL for
real workloads (i.e. ones with 600 concurrent users and millions or
billions of rows)
> So anyway, life story aside, I have a couple of very newbie questions after
> tinkering with PostgreSQL 8.1.9 for a day converting some PHP/MySQL code:
Umm, as mentioned by someone else, you should be running 8.3.3 not
8.1.9. That version is 2.5 years or so old. There's been a LOT of
improvements since then, and 8.3.3 is very fast. We just upgraded a
production server from 8.1.9 to 8.3.3 and the load on that machine
dropped from 20 to 30 to 4 or 5.
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
This is one of the many SQL bad habits you've likely picked up from
using MySQL. I'd highly suggest reading the pgsql users manual cover
to cover, you'll pick up a lot of good info on how to drive
postgresql. Other things that work in mysql but fail in pgsql include
inserting things that are out of range.
create table test (a int);
insert into test (a) values (123456789012345678);
will fail in pgsql, because that number won't fit. It won't get
chopped off at 2^31-1, it'll just not get inserted.
So will inserting a string that's too long. Dates like 2008-02-30 will fail.
A trick you'll want to learn in pgsql is putting as much into a
transaction together as is reasonable. If you're inserting 10,000
rows that should all go together or not, then wrap them all in begin;
commit; . If one insert fails, they all fail, and you can start again
without cleaning up. If they all go in, they all go in much faster
than if you did them individually.