Re: Moving to postgresql and some ignorant questions

On 14/08/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> writes:
>
> > Though other threads I have learned that multiple inserts or updates
> > can be sped up with:
> >
> > [QUOTE]
> > - BEGIN TRANSACTION;
> > - INSERT OR UPDATE queries, ideally PREPAREd;
> > - COMMIT;
> > [/QUOTE]
> >
> > QUESTION1:
> > Is this it? Or am I missing something in terms of execution? We use
> > Perl on our site but may be gradually switching to PHP at some point.
> > Will the above plan of execution be ok?
>
> A transaction is a bunch of queries which you want to all get committed or
> aborted together. The expensive step in Postgres is the COMMIT. Postgres does
> an fsync which forces the data to go to disk (if you're using good hardware,
> and don't have fsync=off). That takes from 5-15ms depending on how much data
> and how fast your drives are.

If I am reading this right, does this mean it is probably better to
leave fsync as "fsync=off" on production machines? Also, is COMMIT
automatic for my queries? In some minor testing I did (not scientific
I did at all) some queries through Perl did not update the database at
all. I had "fsync=off" in my conf file, and there was no COMMIT etc in
my SQL, just plain SQL. So I am a bit confused. What's a good starting
point?

> Grouping more work into a single transaction makes the delay for the fsync at
> COMMIT time less of a problem.

Agree. That's what I am trying to do. Include as many UPDATEs etc into
the same TRANSACTION block, but my worry is when I read about
autocommit and how it is enabled by default in postgresql 8.
Specifying explicit BEGIN and COMMIT blocks should only commit when I
want the DB to, or will each UPDATE in the middle of this block get
executed?

Sorry if this is a naive question. I am reading up as much as I can.

> Also having more connections (but not too many,
> more than a few per processor is probably not helping, more than 100 and it's
> probably slowing you down) also means it's less important since another
> process can do some of its work while you're waiting for the fsync.

So, again, in the conf file, is this what you recommend:

fsync=off
max_connections=100

?

> Yes, it's a pain. Running vacuum frequently will be necessary. You may also
> have to raise your fsm settings to allow Postgres to remember more free space
> between vacuums.

Thank you for your comments about autovacuum. I have these FSM and
memory type settings settings in my conf (picked off the internet :))
--

max_fsm_relations = 1500
max_fsm_pages = 80000
shared_buffers = 21000
effective_cache_size = 21000
sort_mem = 16348
work_mem = 16348
vacuum_mem = 16348
temp_buffers = 4096
authentication_timeout = 10s
ssl = off

Do these sound right?

> Incidentally, most data integrity checks are handled with CHECK constraints
> and FOREIGN KEY constraints rather than manual triggers. They're both easier
> and cheaper.

The problem with simple CHECK constraints is that they can only
reference the primary key in another table. What if I want more than
one column to be the same as the referenced table, but do not want to
have a compound primary key in the referenced table? From reading and
re-reading the manual, I dont think FKs allow for this. Only primary
key references are supported.

> Sorry, this is one of the main deficiencies in Postgres. You will probably
> have to convert your data to utf8 across the board and hopefully you'll find a
> collation which satisfies all your needs.

I am happy to do this, but I don't see an ALTER DATABASE command. I
would really like not to have to execute the CREATE DATABASE command
again! Can I make the entire DB utf8 using some command now? Have not
been able to find it. ALl manual and google stuff seems to point to
the CREATE DB command only.

Many thanks!