pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedvery large table
3+ messages / 3 participants
[nested] [flat]
* very large table
@ 2005-05-31 09:37 Praveen Raja <praveen.raja@netlight.se>
0 siblings, 2 replies; 3+ messages in thread
From: Praveen Raja @ 2005-05-31 09:37 UTC (permalink / raw)
To: pgsql-performance
Hi
I'm trying to move an existing solution from MySQL to PostgreSQL. As it
is now the solution has 4 tables where data in inserted by an
application. At regular intervals (10min) data from these tables is
consolidated and moved to another table for reporting purposes. There
exist many instances of these reporting tables and in total they are
expected to hold about 500 million rows. There are about 200 of these
reporting tables at the moment with data split among them. When a
request comes in all these tables are searched. While moving to
PostgreSQL is it a good idea to move from using multiple tables to one
table for so many rows?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: very large table
@ 2005-05-31 14:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Praveen Raja <praveen.raja@netlight.se>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-05-31 14:06 UTC (permalink / raw)
To: Praveen Raja <praveen.raja@netlight.se>; +Cc: pgsql-performance
"Praveen Raja" <praveen.raja@netlight.se> writes:
> I'm trying to move an existing solution from MySQL to PostgreSQL. As it
> is now the solution has 4 tables where data in inserted by an
> application. At regular intervals (10min) data from these tables is
> consolidated and moved to another table for reporting purposes. There
> exist many instances of these reporting tables and in total they are
> expected to hold about 500 million rows. There are about 200 of these
> reporting tables at the moment with data split among them. When a
> request comes in all these tables are searched. While moving to
> PostgreSQL is it a good idea to move from using multiple tables to one
> table for so many rows?
If the multiple tables represent a partitioning scheme that makes sense
to your application (ie, you can tell a priori which tables to look in
for a given query) then it's probably worth keeping. But it sounds like
they don't make that much sense, since you mention searching all the tables.
In that case you should think about consolidating.
There is lots of stuff in the recent list archives about partitioned
tables; might be worth reading, even though much of it is talking about
features we don't yet have. It would point out the issues you need
to think about --- for example, do you periodically discard some of the
data, and if so do the tables correspond to the discard units? DROP
TABLE is a lot quicker than trying to DELETE and then VACUUM a portion
of a very large table.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: very large table
@ 2005-06-01 08:57 Simon Riggs <simon@2ndquadrant.com>
parent: Praveen Raja <praveen.raja@netlight.se>
1 sibling, 0 replies; 3+ messages in thread
From: Simon Riggs @ 2005-06-01 08:57 UTC (permalink / raw)
To: Praveen Raja <praveen.raja@netlight.se>; +Cc: pgsql-performance
On Tue, 2005-05-31 at 11:37 +0200, Praveen Raja wrote:
> I’m trying to move an existing solution from MySQL to PostgreSQL. As
> it is now the solution has 4 tables where data in inserted by an
> application. At regular intervals (10min) data from these tables is
> consolidated and moved to another table for reporting purposes. There
> exist many instances of these reporting tables and in total they are
> expected to hold about 500 million rows. There are about 200 of these
> reporting tables at the moment with data split among them. When a
> request comes in all these tables are searched.
> While moving to PostgreSQL is it a good idea to move from using
> multiple tables to one table for so many rows?
No. All of the same reasoning applies.
Try to keep each table small enough to fit easily in RAM.
Make sure you specify WITHOUT OIDS on the main data tables.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-01 08:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-31 09:37 very large table Praveen Raja <praveen.raja@netlight.se>
2005-05-31 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-01 08:57 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox