I'm running a long running query; it's been running since this morning. It's
inserting data into a table. I'm trying to estimate how many rows it has
inserted so far and how much longer it'll run. For one thing I'm running out
of space on this disk...
How do I measure the amount of space a record takes in a table?
The table structure it's inserting into looks like this:
Column | Type | Modifiers
-------------------+------------------------+-----------
str | character varying(100) |
foo_id | integer |
bar_id | integer |
dist | double precision |
geom2 | box |
The last two columns are actually being populated with nulls. The first three
columns are being populated with data. The string in the first column is
actually always 12 characters.
Incidentally, if I set the field to a maximum of 12 will it save space? (It
doesn't in Oracle for example.) And are there any table parameters that allow
postgres to pack data more densely or avoid problems if I fill in the null
fields later with updates?
So far it's inserted quite a bit of data:
-rw------- 1 postgres postgres 1.0G Feb 1 13:35 /var/lib/postgres/data/base/992336/7992140
-rw------- 1 postgres postgres 1.0G Feb 1 14:51 /var/lib/postgres/data/base/992336/7992140.1
-rw------- 1 postgres postgres 1.0G Feb 1 16:14 /var/lib/postgres/data/base/992336/7992140.2
-rw------- 1 postgres postgres 1.0G Feb 1 17:27 /var/lib/postgres/data/base/992336/7992140.3
-rw------- 1 postgres postgres 1.0G Feb 1 18:45 /var/lib/postgres/data/base/992336/7992140.4
-rw------- 1 postgres postgres 1.0G Feb 1 20:23 /var/lib/postgres/data/base/992336/7992140.5
-rw------- 1 postgres postgres 1.0G Feb 1 21:34 /var/lib/postgres/data/base/992336/7992140.6
-rw------- 1 postgres postgres 1.0G Feb 1 22:47 /var/lib/postgres/data/base/992336/7992140.7
-rw------- 1 postgres postgres 577M Feb 1 23:30 /var/lib/postgres/data/base/992336/7992140.8
I think it'll insert a total of 107,535,604 records.
What I'm trying to figure out is, a) How much space will I need to make sure I
have for this table and b) will it finish within the next 24 hours or should I
just give up and rethink my plan.
I guess I have other questions like are there any tips on making inserting
lots of records fast. (Does turning off fsync help on big inserts or only when
you have lots of small queries?)
I'm also going to have to make at least one index on this table. I'm dreading
that. How do I estimate how much space the index will take up?
--
greg
