I have been a long time user of mysql. Switching to Postgresql because
the true features included in 5.1 (as of this moment) are nothing to
write home about. The InnoDB stuff is highly advocated but it has its
own set of issues, and when one looks at things like backup/restore
etc, it is clearly targeted at expensive installs with full DBAs and
such, which we cannot afford.
So I have been reading up feverishly on PGSQL and it surely is a more
serious DB, which is good, but also a bit steep in its learning curve.
I have some pretty silly questions to ask below. Just to confirm that
I manage my switch as smoothly as possible!
By way of information, we have new double AMD Opterons with 3GB of
RAM. The postgres that has been installed is 8.2.3. But our DB is not
as large as some of the discussions on pgsql-general. No table is more
than 10 million records or likely to exceed that anytime soon. But I
have some heavy simultaneous user connections much like any web
application for a busy website.
In particular I have a table that needs very high availability: it has
bout 10,000 INSERTS a day, about 500,000 SELECTS a day (with or
without joins), but most importantly about 1 million UPDATEs. (It is
the UPDATE that is bothering the MYSQL engine of "MYISAM" type with
frequent data corruption).
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?
My queries are all optimized and indexed well. But the defragmentation
resulting from UPDATEs can be a pain. I wonder if PGSQL's autovacuum
will help. As for autovacuum we have every intention of leaving it on.
Will the following settings be alright?
[QUOTE]
autovacuum = on
vacuum_cost_delay = 30
stats_start_collector = on
stats_row_level = on
autovacuum_vacuum_threshold = 100
autovacuum_analyze_threshold = 50
[/QUOTE]
I am hoping that the frequent vacuum thresholds will help, but:
QUESTION 2:
Are these settings too aggressive? While an autovacuum is running in
the background, will it lock tables or anything? Can the tables still
be operational, and the autovacuum will automatically resume from
whatever point it was at? I am worried about how autovacuum will
perform when
QUESTION 3.
Data integrity checks in MySQL world were very patchy, relying on CGI
stuff to make sure, which does not always work. We are trying our best
to get rid of them. With postgresql, I realize we can have triggers as
well as foreign keys. But searching through old threads on this group
suggests that triggers also present performance problems. On tables
that are infrequently updated, can I write triggers without worrying
about performance? Or, how can I make sure their performance is as
best as it can be, i.e., which of the config vars is responsible for
that?
QUESTION 4:
Character sets: In MySQL we had utf-8 as our default character set.
Yet, on some tables we had the collation of latin1_general_ci, and
only on specific columns we had "utf8_general_ci" (where non-english
text needed to be stored). How should I do this in pgsql? When I do a
mysqldump of these tables, and then COPY them back into pgsql, I
always see the error "ERROR: invalid byte sequence for encoding
"UTF8": 0xa7". So I do a
\encoding latin1
And then my COPY import works. But this is not what I want. How can I
set up one of my columns in this table to be utf-8, and the rest to be
latin? Then I would like to import with "\encoding utf8". Can this be
somehow done?
Sorry for this long post, but as exciting as this switch is, it is
also daunting because I feel like I am moving into serious databases
territory and I don't want to goof up. I have read up a lot and am
continuing to, but it would be great if someone can shed some light on
the above to begin with.
TIA!
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
