Re: [GENERAL] Table size for partitioned setup

[Rob Sargent]

On 03/28/2016 02:55 PM, Mat Arye wrote:

This will run on EC2 (or other cloud service) machines and on ssds.
Right now runs on m4.4xlarge with 64GiB of ram.
Willing to pay for beefy instances if it means better performance.


On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent > wrote:




On 03/28/2016 02:41 PM, Mat Arye wrote:

Hi All,

I am writing a program that needs time-series-based insert
mostly workload. I need to make the system scaleable with many
thousand of inserts/s. One of the techniques I plan to use is
time-based table partitioning and I am trying to figure out
how large to make my time tables.

Does anybody have any hints on optimal table sizes either in
terms of rows or in terms of size? Any rule of thumbs I can
use for table size in relation to amount of memory on the
machine? Is the size of the index more important than the size
of the table (if queries mostly use indexes)?

Basically, I am asking for pointers about how to think about
this problem and any experiences people have had.

Thanks,
Mat

P.S. I am aware of limits listed here:
http://www.postgresql.org/about/. I am asking about practical
size limits for performance consideration.

Your current hardware, or hardware budget might play into the answer.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Those who supply real answers on this list um, er, discourage 
top-posting. (Not my fave, but there you go)

Re: [GENERAL] Table size for partitioned setup

[Mat Arye]

This will run on EC2 (or other cloud service) machines and on ssds.
Right now runs on m4.4xlarge with 64GiB of ram.
Willing to pay for beefy instances if it means better performance.


On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent  wrote:

>
>
> On 03/28/2016 02:41 PM, Mat Arye wrote:
>
>> Hi All,
>>
>> I am writing a program that needs time-series-based insert mostly
>> workload. I need to make the system scaleable with many thousand of
>> inserts/s. One of the techniques I plan to use is time-based table
>> partitioning and I am trying to figure out how large to make my time tables.
>>
>> Does anybody have any hints on optimal table sizes either in terms of
>> rows or in terms of size? Any rule of thumbs I can use for table size in
>> relation to amount of memory on the machine? Is the size of the index more
>> important than the size of the table (if queries mostly use indexes)?
>>
>> Basically, I am asking for pointers about how to think about this problem
>> and any experiences people have had.
>>
>> Thanks,
>> Mat
>>
>> P.S. I am aware of limits listed here: http://www.postgresql.org/about/.
>> I am asking about practical size limits for performance consideration.
>>
> Your current hardware, or hardware budget might play into the answer.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Table size for partitioned setup

[Rob Sargent]

On 03/28/2016 02:41 PM, Mat Arye wrote:

Hi All,

I am writing a program that needs time-series-based insert mostly 
workload. I need to make the system scaleable with many thousand of 
inserts/s. One of the techniques I plan to use is time-based table 
partitioning and I am trying to figure out how large to make my time 
tables.


Does anybody have any hints on optimal table sizes either in terms of 
rows or in terms of size? Any rule of thumbs I can use for table size 
in relation to amount of memory on the machine? Is the size of the 
index more important than the size of the table (if queries mostly use 
indexes)?


Basically, I am asking for pointers about how to think about this 
problem and any experiences people have had.


Thanks,
Mat

P.S. I am aware of limits listed here: 
http://www.postgresql.org/about/. I am asking about practical size 
limits for performance consideration.

Your current hardware, or hardware budget might play into the answer.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Table size for partitioned setup

[Mat Arye]

Hi All,

I am writing a program that needs time-series-based insert mostly workload.
I need to make the system scaleable with many thousand of inserts/s. One of
the techniques I plan to use is time-based table partitioning and I am
trying to figure out how large to make my time tables.

Does anybody have any hints on optimal table sizes either in terms of rows
or in terms of size? Any rule of thumbs I can use for table size in
relation to amount of memory on the machine? Is the size of the index more
important than the size of the table (if queries mostly use indexes)?

Basically, I am asking for pointers about how to think about this problem
and any experiences people have had.

Thanks,
Mat

P.S. I am aware of limits listed here: http://www.postgresql.org/about/. I
am asking about practical size limits for performance consideration.

Re: [GENERAL] table size is not changed

[Scott Marlowe]

On Wed, Feb 3, 2010 at 2:43 AM, AI Rumman  wrote:
> I deleted 7 rows from a table and then execute
> vacuum analyze .
>
> But table size not yet changed.
>
> I am using Postgresql 8.1.
>
> Could anyone please tell me what the problem is?


http://www.postgresql.org/docs/8.1/static/maintenance.html#VACUUM-FOR-SPACE-RECOVERY

explains it pretty well.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size is not changed

[Grzegorz Jaśkiewicz]

try reindexing table.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] table size is not changed

[AI Rumman]

I deleted 7 rows from a table and then execute
vacuum analyze .

But table size not yet changed.

I am using Postgresql 8.1.

Could anyone please tell me what the problem is?

Re: [GENERAL] table size and storage location

[Tom Lane]

"Andrej Ricnik-Bay" <[EMAIL PROTECTED]> writes:
> On 25/03/2008, chuckee <[EMAIL PROTECTED]> wrote:
>> Thanks but I still get the error 'ERROR:  relation "capture" does not exist'
>> when trying these two alternative functions you mention above. There is
>> definitely a table called 'capture' in my database!

> Are you sure you're connected to the right database
> when running that?

Case-sensitivity is the other standard gotcha ... maybe the table is
really named "Capture" or something like that?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size and storage location

[Andrej Ricnik-Bay]

On 25/03/2008, chuckee <[EMAIL PROTECTED]> wrote:

> Thanks but I still get the error 'ERROR:  relation "capture" does not exist'
>  when trying these two alternative functions you mention above. There is
>  definitely a table called 'capture' in my database!
Are you sure you're connected to the right database
when running that?



-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size and storage location

[paul rivers]

chuckee wrote:

paul rivers-2 wrote:
  

chuckee wrote:


1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR:  tablespace "capture" does not exist
  
  
You're looking for pg_relation_size('capture') or 
pg_total_relation_size('capture'). A tablespace is a named location for 
creating objects.






Thanks but I still get the error 'ERROR:  relation "capture" does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!
  


Is the schema for capture in your search_path? If not, include that in 
the function call: function('yourschema.capture'). Otherwise, what 
version are you on? I don't know when these functions were added; 
perhaps you're stuck doing the math yourself on page counts in pg_class.


Paul




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size and storage location

[chuckee]

paul rivers-2 wrote:
> 
> chuckee wrote:
>> 1) how do I find out the size, in MB, of a particular table (called
>> 'capture' in this case).
>> I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
>> The result was the following:
>> ERROR:  tablespace "capture" does not exist
>>   
> 
> You're looking for pg_relation_size('capture') or 
> pg_total_relation_size('capture'). A tablespace is a named location for 
> creating objects.
> 
> 

Thanks but I still get the error 'ERROR:  relation "capture" does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!
-- 
View this message in context: 
http://www.nabble.com/table-size-and-storage-location-tp16230697p16249922.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size and storage location

[paul rivers]

chuckee wrote:

1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR:  tablespace "capture" does not exist
  


You're looking for pg_relation_size('capture') or 
pg_total_relation_size('capture'). A tablespace is a named location for 
creating objects.




2) how do I find out where the actual files for the database are stored on
my system?
Where is the default storage location?
  


The complete story is laid out in the docs here:
http://www.postgresql.org/docs/8.3/interactive/storage-file-layout.html


HTH,
Paul


-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] table size and storage location

[chuckee]

Hi,
I have two questions:

1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR:  tablespace "capture" does not exist

2) how do I find out where the actual files for the database are stored on
my system?
Where is the default storage location?

Thanks!
Charlie
-- 
View this message in context: 
http://www.nabble.com/table-size-and-storage-location-tp16230697p16230697.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Webb Sprague]

>  I meant, "I did not know such facility exists"

When you use pgautodoc, it automatically grabs those comments and puts
them in the web page it crreates...  more coolness!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Pavan Deolasee]

On Fri, Mar 21, 2008 at 10:25 PM, Pavan Deolasee
<[EMAIL PROTECTED]> wrote:

>
>  Oh cool.. I did not such facility exists.
>


I meant, "I did not know such facility exists"

Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Pavan Deolasee]

On Fri, Mar 21, 2008 at 10:12 PM, Andreas Kretschmer
<[EMAIL PROTECTED]> wrote:

>
>  Comments on objects can set by:
>
>  comment on ... is 'comment';
>

Oh cool.. I did not such facility exists.

Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Andreas Kretschmer]

Pavan Deolasee <[EMAIL PROTECTED]> schrieb:

> On Fri, Mar 21, 2008 at 3:03 PM, lak <[EMAIL PROTECTED]> wrote:
> > I have two questions.
> >  How can I enter comments into a table? Where the comments are stored?
> >
> 
> What do you mean by comments in a table ?

Comments on a table or a column or on other objects. 

Comments on objects can set by:

comment on ... is 'comment';

Please read within psql the output from '\h comment' to learn more.
Comments are stored in pg_description:
http://www.postgresql.org/docs/current/interactive/catalog-pg-description.html

> 
> >  In  psql How can I know the size of a single table?
> >
> 
> Select pg_relation_size('mytable');

More about that:
http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Shane Ambler]

Pavan Deolasee wrote:

On Fri, Mar 21, 2008 at 3:03 PM, lak <[EMAIL PROTECTED]> wrote:

I have two questions.
 How can I enter comments into a table? Where the comments are stored?



What do you mean by comments in a table ?


I think what you are referring to is detailed in
http://www.postgresql.org/docs/8.3/interactive/sql-comment.html

The comments are stored in pg_description (and pg_shdescription) but you 
should use the comment command instead of manipulating it manually.



 In  psql How can I know the size of a single table?



Select pg_relation_size('mytable');


Thanks,
Pavan





--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Craig Ringer]

lak wrote:

I have two questions.
How can I enter comments into a table? Where the comments are stored?
  

Assuming you want comments on the table schema definitions, use COMMENT ON.

CREATE TABLE sometable (
-- definition
);

COMMENT ON TABLE sometable IS "This is a table";

If that's not what you're after, you might need to be more specific.

--
Craig Ringer


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Erik Jones]

On Mar 21, 2008, at 4:33 AM, lak wrote:


I have two questions.
How can I enter comments into a table? Where the comments are stored?


Comments are created with the COMMENT sql command and, in pg, are  
stored in pg_description.



In  psql How can I know the size of a single table?



pg_relation_size()

Often called as pg_size_pretty(pg_relation_size('some_table')) for  
more readable output.


Use pg_total_relation_size() for the size with indexes and toast tables.

Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table size

[Pavan Deolasee]

On Fri, Mar 21, 2008 at 3:03 PM, lak <[EMAIL PROTECTED]> wrote:
> I have two questions.
>  How can I enter comments into a table? Where the comments are stored?
>

What do you mean by comments in a table ?

>  In  psql How can I know the size of a single table?
>

Select pg_relation_size('mytable');


Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Table size

[lak]

I have two questions.
How can I enter comments into a table? Where the comments are stored?

In  psql How can I know the size of a single table?

If know pls replay.
Thanks i advance



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size in 8.3

[Tom Lane]

Alex Vinogradovs <[EMAIL PROTECTED]> writes:
> I've created 2 sample tables with 1 column each - type
> char(1) and type integer. After inserting equal number
> of rows (4M or more) tablesizes are exactly the same, while
> I would expect table with char(1) to be slighly smaller...
> What's causing it ? Thanks!

Alignment padding ... rows are always padded out to a MAXALIGN multiple.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] table size in 8.3

[Alex Vinogradovs]

Guys,

I've created 2 sample tables with 1 column each - type
char(1) and type integer. After inserting equal number
of rows (4M or more) tablesizes are exactly the same, while
I would expect table with char(1) to be slighly smaller...
What's causing it ? Thanks!

Server version is 8.3.


Best regards,
Alex Vinogradovs

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table size performace

[A. Kretschmer]

am  07.09.2005, um  1:01:11 -0700 mailte Matthew Peter folgendes:
> How many rows does it take for select performance on a
> table to degrade? I hope this question isn't to
> ambiguous (ie lollipop licks). But seriously, 100,000?
> 1,000,000? 10,000,000? With just a regular lookup on
> an unique index. Nothing crazy or aggregate.
> 
> EX: select * from bigtable where id = 123456789

Read http://www.postgresql.org/docs/faqs.FAQ.html#4.4.

If you have a index, a simple select like your example is very fast,
independent of the rows count.


Regards.
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

[GENERAL] table size performace

[Matthew Peter]

How many rows does it take for select performance on a
table to degrade? I hope this question isn't to
ambiguous (ie lollipop licks). But seriously, 100,000?
1,000,000? 10,000,000? With just a regular lookup on
an unique index. Nothing crazy or aggregate.

EX: select * from bigtable where id = 123456789

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] table size/record limit

[Dennis Gearon]

Great Idea! When I get that far, I will try it.
Gaetano Mendola wrote:

For partion in some way I don't mean only split it in more tables. You
can use some available tools in postgres and continue to see this table
as one but implemented behind the scenes with more tables.
One usefull and impressive way is to use the inheritance in order to obtain
a vertical partition
0) Decide a partition policy ( based on time stamp for example )
1) Create an empty base table with the name that you want see as "public"
2) Create the partition using the empty table as base table
3) Create a rule on the base table so an insert or the update on it is
~   performed as a insert or an update on the right table ( using the 
partition
~   policy at step 0 )

in this way you are able to vacuum each partition, reindex each 
partition and
so on in a more "feseable way" I do not immagine vacuum full or reindex a
3,600,000,000 records table...

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] table size/record limit

[Gaetano Mendola]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Dennis Gearon wrote:
| Gaetano Mendola wrote:
|
|> Dennis Gearon wrote:
|>
|>> I am designing something that may be the size of yahoo, google, ebay,
|>> etc.
|>>
|>> Just ONE many to many table could possibly have the following
|>> characteristics:
|>>
|>>3,600,000,000 records
|>
|> This is a really huge monster one, and if you don't partition that
|> table in some way I think you'll have nightmares with it...
|>
|> Regards
|> Gaetano Mendola
|>
| thanks for the input, Gaetano.
For partion in some way I don't mean only split it in more tables. You
can use some available tools in postgres and continue to see this table
as one but implemented behind the scenes with more tables.
One usefull and impressive way is to use the inheritance in order to obtain
a vertical partition
0) Decide a partition policy ( based on time stamp for example )
1) Create an empty base table with the name that you want see as "public"
2) Create the partition using the empty table as base table
3) Create a rule on the base table so an insert or the update on it is
~   performed as a insert or an update on the right table ( using the partition
~   policy at step 0 )
in this way you are able to vacuum each partition, reindex each partition and
so on in a more "feseable way" I do not immagine vacuum full or reindex a
3,600,000,000 records table...

Regards
Gaetano Mendola










-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBeLiK7UpzwH2SGd4RAh+TAJ4w89SvkFWgt9DGhQx/aUR6j2wDtwCgtut5
FN0OuoycbI37a8Wouvo3icw=
=Wb6h
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] table size/record limit

[Gaetano Mendola]

Dennis Gearon wrote:
I am designing something that may be the size of yahoo, google, ebay, etc.
Just ONE many to many table could possibly have the following 
characteristics:

   3,600,000,000 records
This is a really huge monster one, and if you don't partition that
table in some way I think you'll have nightmares with it...

Regards
Gaetano Mendola
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] table size/record limit

[David Helgason]

On 21. okt 2004, at 01:30, Dennis Gearon wrote:
I am designing something that may be the size of yahoo, google, ebay, 
etc.
Grrr. Geek wet-dream.
Just ONE many to many table could possibly have the following 
characteristics:

   3,600,000,000 records
   each record is 9 fields of INT4/DATE
I don't do this myself (my data is only 3 gig, and most of that is in 
blobs), but people have repeatedly reported such sizes on this list.

Check
http://archives.postgresql.org/pgsql-admin/2001-01/msg00188.php
... but the best you can do is just to try it out. With a few commands 
in the 'pql' query tool you can easily populate a ridiculously large 
database ("insert into foo select * from foo" a few times).

In few hours you'll have some feel of it.
Other tables will have about 5 million records of about the same size.
There are lots of scenarios here to lessson this.
What you'll have to worry about most is the access pattern, and update 
frequency.

There's a lot of info out there. You may need any of the following:
 • clustering, the 'slony' project seems to be popular around here.
 • concurrency of updating
 • connnection pooling, maybe via Apache or some java-thingey
 • securing yourself from hardware errors
This list is a goldmine of discussions. Search the archives for 
discussions and pointers. Search interfaces at

http://archives.postgresql.org/pgsql-general/
http://archives.postgresql.org/pgsql-admin/
 or download the list archive mbox files into your mail-program and 
use that (which is what I do).

d.
--
David Helgason,
Business Development et al.,
Over the Edge I/S (http://otee.dk)
Direct line +45 2620 0663
Main line +45 3264 5049

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

Re: [GENERAL] table size/record limit

[Robby Russell]

On Wed, 2004-10-20 at 23:01 -0700, Joshua D. Drake wrote:
> Dennis Gearon wrote:
> 
> > Google probably is much bigger, and on mainframes, and probably Oracle 
> > or DB2.
> 
> Google uses a Linux cluster and there database is HUGE. I do not know 
> which database
> they use. I bet they built their own specifically for what they do.

...actually, I heard they were running it off a flat file database on 7
386 machines in some guys garage off a dsl connection. I could be wrong
though. ;-)

-Robby

-- 
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | [EMAIL PROTECTED]
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
/



signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] table size/record limit

[Dennis Gearon]

Actually, now that I think about it, they use a special table type that the INDEX is 
also the DATUM. It is possible to recover the data, out of the index listing. So go 
down the index, then decode the indexing value - voila, a whole step saved. I have no 
idea what engine these table types are in, however.
Joshua D. Drake wrote:
Dennis Gearon wrote:
Google probably is much bigger, and on mainframes, and probably Oracle 
or DB2.

Google uses a Linux cluster and there database is HUGE. I do not know 
which database
they use. I bet they built their own specifically for what they do.

Sincerely,
Joshua D. Drake

But the table I am worried about is the one sized >= 3.6 GIGA records.
Tino Wildenhain wrote:
Hi,
Am Do, den 21.10.2004 schrieb Dennis Gearon um 1:30:
I am designing something that may be the size of yahoo, google, 
ebay, etc.

Just ONE many to many table could possibly have the following 
characteristics:

   3,600,000,000 records
   each record is 9 fields of INT4/DATE
Other tables will have about 5 million records of about the same size.
There are lots of scenarios here to lessson this.
BUT, is postgres on linux, maybe necessarily a 64 bit system, 
cabable of this? And there'd be 4-5 indexes on that table.


Sure. Why not? 3...5mio records is not really a problem.
We had bigger tables with historic commercial transactions
(even on an old dual PIII/1000) with fine performance.
I bet however, yahoo, google at least are much bigger :-)
Regards
Tino


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] table size/record limit

[Joshua D. Drake]

Dennis Gearon wrote:
Google probably is much bigger, and on mainframes, and probably Oracle 
or DB2.
Google uses a Linux cluster and there database is HUGE. I do not know 
which database
they use. I bet they built their own specifically for what they do.

Sincerely,
Joshua D. Drake

But the table I am worried about is the one sized >= 3.6 GIGA records.
Tino Wildenhain wrote:
Hi,
Am Do, den 21.10.2004 schrieb Dennis Gearon um 1:30:
I am designing something that may be the size of yahoo, google, 
ebay, etc.

Just ONE many to many table could possibly have the following 
characteristics:

   3,600,000,000 records
   each record is 9 fields of INT4/DATE
Other tables will have about 5 million records of about the same size.
There are lots of scenarios here to lessson this.
BUT, is postgres on linux, maybe necessarily a 64 bit system, 
cabable of this? And there'd be 4-5 indexes on that table.

Sure. Why not? 3...5mio records is not really a problem.
We had bigger tables with historic commercial transactions
(even on an old dual PIII/1000) with fine performance.
I bet however, yahoo, google at least are much bigger :-)
Regards
Tino


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] table size/record limit

[Dennis Gearon]

Google probably is much bigger, and on mainframes, and probably Oracle or DB2.
But the table I am worried about is the one sized >= 3.6 GIGA records.
Tino Wildenhain wrote:
Hi,
Am Do, den 21.10.2004 schrieb Dennis Gearon um 1:30:
I am designing something that may be the size of yahoo, google, ebay, etc.
Just ONE many to many table could possibly have the following 
characteristics:

   3,600,000,000 records
   each record is 9 fields of INT4/DATE
Other tables will have about 5 million records of about the same size.
There are lots of scenarios here to lessson this.
BUT, is postgres on linux, maybe necessarily a 64 bit system, cabable of 
this? And there'd be 4-5 indexes on that table.

Sure. Why not? 3...5mio records is not really a problem.
We had bigger tables with historic commercial transactions
(even on an old dual PIII/1000) with fine performance.
I bet however, yahoo, google at least are much bigger :-)
Regards
Tino


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] table size/record limit

[Tino Wildenhain]

Hi,

Am Do, den 21.10.2004 schrieb Dennis Gearon um 1:30:
> I am designing something that may be the size of yahoo, google, ebay, etc.
> 
> Just ONE many to many table could possibly have the following 
> characteristics:
> 
> 3,600,000,000 records
> each record is 9 fields of INT4/DATE
> 
> Other tables will have about 5 million records of about the same size.
> 
> There are lots of scenarios here to lessson this.
> 
> BUT, is postgres on linux, maybe necessarily a 64 bit system, cabable of 
> this? And there'd be 4-5 indexes on that table.

Sure. Why not? 3...5mio records is not really a problem.
We had bigger tables with historic commercial transactions
(even on an old dual PIII/1000) with fine performance.
I bet however, yahoo, google at least are much bigger :-)

Regards
Tino



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] table size/record limit

[Dennis Gearon]

I am designing something that may be the size of yahoo, google, ebay, etc.
Just ONE many to many table could possibly have the following 
characteristics:

   3,600,000,000 records
   each record is 9 fields of INT4/DATE
Other tables will have about 5 million records of about the same size.
There are lots of scenarios here to lessson this.
BUT, is postgres on linux, maybe necessarily a 64 bit system, cabable of 
this? And there'd be 4-5 indexes on that table.

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

Re: [GENERAL] table size

[Shridhar Daithankar]

Hi,

> I have a table in my database which can grow very quickly. Is
> there some way to partition the table so that when it reaches a certain
> size the
> information in it is copied to a temporary table and the
> original table is free again.

You can create a view and update the view definition periodically to
add/remove tables from view definition. Your data insertion should be
pointed to a new table after specific period.

That way you can achieve table partitioning. Since DDLs in postgresql are
transactable, it should be absolutely transparent to the application and
other users.

HTH

 Shridhar

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] table size

[Jason Godden]

Hi David,

I'd say that if it is a new app develop it with 7.4 and use statement level 
triggers otherwise you could use normal triggers and perform a count each 
time but that will slow things down dramatically.

Other option is to use cron and write a daemon/script to periodically check 
the size of the table.  Without statement level triggers this would probably 
be the most efficient.

Rgds,

Jason

On Tue, 23 Sep 2003 07:35 pm, David McLoughlin wrote:
> I have a table in my database which can grow very quickly. Is
> there some way to partition the table so that when it reaches a certain
> size the information in it is copied to a temporary table and the
> original table is free again.
>
> Or is this possible at all. If not is there any sort of perfomance
> update I can add to speed up the queries on such a large table.
>
> thanks
>
> D
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org


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

[GENERAL] table size

[David McLoughlin]

I have a table in my database which can grow very quickly. Is 
there some way to partition the table so that when it reaches a certain size the 
information in it is copied to a temporary table and the 
original table is free again.

Or is this possible at all. If not is there any sort of perfomance 
update I can add to speed up the queries on such a large table.

thanks

D



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] Table Size/Performance

[[EMAIL PROTECTED]]

Is there a way to figure out which file represents which table?

IE:  I have a file 21691 and I want to know what table it is.

Also, I've heard that pg splits tables when they get to about 1 gig.

I have a table that could grow to that.  It is 700+ megs now.  Will
performance/indexes be effected if/when it's size is split.

Thanks
Dennis


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl