Re: database 1.2G, pg_dump 73M?!

Ross Boylan wrote:
> I'm not a DB admin; I only play one on my computer. I clearly need to
> figure out how to get regular vacuum, analyze, and reindexing going (if
> they aren't going already).
>
> Thanks for all your help.
> Ross
>
>
1. optimize your sql queries and 'understand' index usage,don't index
everything because 'somebody told You indexes are good to speed things
up',for example

db=> explain analyze select email from users where email='abc(at)abc(dot)com';
QUERY
PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..652.40 rows=1 width=42) (actual
time=3.467..3.467 rows=0 loops=1)
Filter: (email = 'abc(at)abc(dot)com'::text)
Total runtime: 3.497 ms

now we add index on table 'users' column 'email':

db=> create index users_email_idx on users (email);

repeat the query:

db=> explain analyze select email from users where email='abc(at)abc(dot)com';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using us_email on users (cost=0.00..8.05 rows=1 width=42)
(actual time=0.113..0.113 rows=0 loops=1)
Index Cond: (email = 'abc(at)abc(dot)com'::text)
Total runtime: 0.152 ms
(3 rows)

compare only 'Total runtime' for start: 0.152ms from second query
comparing to 3.497ms from first query,guess we have some improvements ?

Always try to use 'explain analyze' commands to see if there's any
difference in any change You do in Your database

2. do 'vacuum verbose analyze table' occasionally,depending on Your
database usage,meanining if You're deleting/updating stuff like that
frequently,You will have to do more often 'vaccum'. If You import all
Your data and You basically do 'select',meaning reading data,do 'vacuum'
on New Years Eve :)

3. You want to know the size of You tables,indexes ? You want to know
about their size before and after 'vacuum' command ? Example:

db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
5496 kB
(1 row)

then we do the ordinary 'vacuum':

sms=> vacuum users;
VACUUM
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
5520 kB
(1 row)

not much improvement ,ok we're going do the full 'vacuum':

db=> vacuum full users;
VACUUM
db=> select * from pg_size_pretty(pg_relation_size('users'));
pg_size_pretty
----------------
80 kB
(1 row)

How about indexes ? Example:

db=> select * from pg_size_pretty(pg_relation_size('users_pkey'));
pg_size_pretty
----------------
192 kB
(1 row)

for indexes there're not 'vacuum',but 'reindex' command,like :

db=> REINDEX INDEX users_pkey ;
REINDEX

we look at the index size after 'reindex' command:

db=> select * from pg_size_pretty(pg_relation_size('users_pkey'));
pg_size_pretty
----------------
16 kB
(1 row)

4. don't bother to 'analyze' anything :) if all those numbers and stuff
doesn't mean anything to You,guess Your life would be easier than trying
to apprehend all those 'analyzes' :) just do the regularly 'clean-up' on
Your DB,and You should be fine.

Unfortunately,PostgreSQL is not a database meant for people looking for
a 'black box' database solution,meaning plug-in-electricity-and-forget.
But,if You belong to that group of people who are willing to understand
better what their database solutions is up to and maximize the
proportion 'what do I get'/'for how much money',You're welcome by all
means :)

Sincerely

Dragan