Re: database 1.2G, pg_dump 73M?! - Mailing list pgsql-general

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@abc.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@abc.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@abc.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@abc.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