Final specs for the server is just an ordinary desktop fitted w/ 3
7200rpm 500GB drives & 1 7200 80GB drive / 1 GB ram / 2G processor
(single core)

number of records will be between 3 to 30 million rows.

Currently the process is

1. pull from mssql
2. \copy into PG temp table
3. insert into final table.

current tables are distributed via tablespaces. (current test server is
my laptop w/ 2 5400rpm drives hda & hdc.) and I'm already seeing the
strain of the concurrent select/delete/insert/update and the additional
"client" pull.

So, I wanted to ask the list for advice on how to tread w/ regard to the
server.

Scenario 1.

1. temp table in it's own tablespace
2. final tables in it's own tablespace
3. pgxlog in the OS tablespace

scenario 2
1. temp table in it's own tablespace
2. final tables in it's own tablespace (Read Only Copy)
3. final tables in it's own tablespace (Read write Copy)
4. pgxlog in the OS tablespace

the idea of read and read/write copy is obtained from some
presentation/article I read whereby, all updates of new data is inserted
into the read/write copy up until 1 point (say lunch time / 5pm etc)
whereby the read-write copy[3] will be "renamed" and made into a
read-only copy and the previous read-only copy[2] will be made into a
read-write copy.

The only thing I can't wrap my head around is how to keep these 2 copies
in sync. eg: when everything is being updated to [3] and users are
querying [2] and then at the switch over instance, how will the new data
be inputted to the read only copy? because if I switch back and forth
like this, there's bound to be gaps in the data.

Any comments appreciated.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Anyone? I know this is a low-end server so have to make the best out of
it..

On Tue, 2007-09-18 at 16:06 +0800, Ow Mun Heng wrote:
> Final specs for the server is just an ordinary desktop fitted w/ 3
> 7200rpm 500GB drives & 1 7200 80GB drive / 1 GB ram / 2G processor
> (single core)
>
> number of records will be between 3 to 30 million rows.
>
> Currently the process is
>
> 1. pull from mssql
> 2. \copy into PG temp table
> 3. insert into final table.
>
> current tables are distributed via tablespaces. (current test server is
> my laptop w/ 2 5400rpm drives hda & hdc.) and I'm already seeing the
> strain of the concurrent select/delete/insert/update and the additional
> "client" pull.
>
> So, I wanted to ask the list for advice on how to tread w/ regard to the
> server.
>
> Scenario 1.
>
> 1. temp table in it's own tablespace
> 2. final tables in it's own tablespace
> 3. pgxlog in the OS tablespace
>
> scenario 2
> 1. temp table in it's own tablespace
> 2. final tables in it's own tablespace (Read Only Copy)
> 3. final tables in it's own tablespace (Read write Copy)
> 4. pgxlog in the OS tablespace
>
> the idea of read and read/write copy is obtained from some
> presentation/article I read whereby, all updates of new data is inserted
> into the read/write copy up until 1 point (say lunch time / 5pm etc)
> whereby the read-write copy[3] will be "renamed" and made into a
> read-only copy and the previous read-only copy[2] will be made into a
> read-write copy.
>
> The only thing I can't wrap my head around is how to keep these 2 copies
> in sync. eg: when everything is being updated to [3] and users are
> querying [2] and then at the switch over instance, how will the new data
> be inputted to the read only copy? because if I switch back and forth
> like this, there's bound to be gaps in the data.
>
> Any comments appreciated.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings