Hi guys, I have some questions about postgres and would be glad if you could help me... Here ist what I am planning to do: Build a database with 1 Billion (10^9) rows describing geometric properties: create table steric_descriptor( distance0 bit(4), distance1 bit(4, distance2 bit(4, distance3 bit(4), distance4 bit(4), distance5 bit(4), distance6 bit(4), distance7 bit(4), comp_desc BLOB); Question Nr ONE --- Concerning row size Is it correct that I"ll need: 27 Byte (rowdescriptor) +4Byte (OID) + 4 Byte (for the distances) + BLOB_SIze (say 30 BYTE) => 65 Byte per Tupel dending on the storage space of the BLOB (in BLOB_Space or the tupel itself)? Is it possible to index the row if I disable the ROWID to save storage space in order to get more tupels per page? QUESTION NR. TWO --- Indexing My query will look like this: SELECT p from steric_descriptor WHERE p.distance0 <= threshold0 AND ... ... ... ... AND p.distance7 <= threshold7; Acutally I was planning to use 8 distinct Bitmap-Indices with 15 Bits (2Byte) each per row (16 Byte in total) but as far as I know postgres does not offer a real disk-resident Bitmap-Index... Is that true? If yes, I would have to use composite B*trees which do not perform well for composite range queries. This leads to the new Bitmap-Scan of PostgreSQL => 8 B*Trees with a 4Bit key and blocksize/rowsize +1 pointer (4Byte each). This means: approx. twice the size for the B*Tree-Version just to use the same functionality as a real Bitmap-Index would offer... Is there a way to avoid this or are my calculations somewhat wrong (maybe even totally)? I would appreciate it if someone could verify my thoughts or give me a hint how to proceed... I know that it will be quite a big database and I am not even sure if PostgreSQL can handle such large tables efficiently. Question Nr. THREE: If I do need OIDs for indexing and I have 10^9 Rows in my DB, do the Indices need Oids as well... If yes I might get problems with the 4 Billion size limit of the Oids, is that correct? I don"t know how the physical structure of an Index looks like, so I might be wrong on this as well... Thanks in advance, Chuck
