0

Assume I have the following table:

CREATE TABLE t1 (
    id int NOT NULL PRIMARY KEY,
    rank int NOT NULL,
    phrase varchar(100) NOT NULL
);

with 10 billion records in a relational database like PostgreSQL.

It will take about 1TB of disk space, right? (each record is about 100B)

As far as I can guess, PostgreSQL operates by pages of size 8KB, so I have about 134 217 728 pages (2^40 / (8*1024))

And if I have 64 GB RAM I can easily create an index on id or rank in memory, because I have 512 bytes per page (64*2^30 / 134217728) and handle a query like

SELECT * FROM t1 WHERE id=12435678;

with a single disk read operation and so handle about 50 000 queries per second on a modern SSD drive, right?

Are the above calculations correct?

How do I calculate the more accurate memory size required for the index on id or rank?

What if I create a full text search index on phrase? What memory size do I need?

EDIT1:

I am not sure about the index on id, because it is a primary key, probably it is a clustered index.

Index on rank:

CREATE INDEX rank_index ON t1(rank);

Also I am not sure how do I create a full text index, but a quick Goodle search gives me something like this:

ALTER TABLE t1 ADD COLUMN search_vector tsvector; 
UPDATE t1 SET search_vector = to_tsvector('english', phrase);
CREATE INDEX t1_search_vector_idx ON t1 USING gin(search_vector);

and then I search for search terms with a query like this (not sure about the syntax):

SELECT id, phrase, rank * ts_rank(search_vector, to_tsquery('english', 'search terms')) AS final_rank
FROM t1
ORDER BY final_rank DESC
LIMIT 50;
3
  • Why do you think that you need to hold the index in memory when you create it? Feb 27 at 7:20
  • @LaurenzAlbe I am not an expert in indices, but my idea is that I probably find the position of the required page using in-memory index and do one read operation from the disk when I do SELECT * FROM t1 WHERE id=12435678;, so if disk can do 50 000 reads per second I can handle 50 000 queries per second. Feb 27 at 7:46
  • Ah, I was confused. I thought you were concerned about index creation. Feb 27 at 7:49

1 Answer 1

0

Let's start with the page size assumption, as you don't need to guess it: 8kB is the usual size but because this places a limit on the tuple size it can be adjusted. Your... rather simple ... record structure doesn't require that.

You can check your calculations using a set of commands to generate and format the size of the table. You don't have to generate all of the records, just enough to sanity check the values you've got.

For example:

SELECT pg_size_pretty(pg_total_relation_size('yrtblname')) 

to get the size of a table.

For the whole DB look at: pg_database_size()

What kind of indexes are you trying to create?

2
  • Hi Nick, the answer section is not for asking questions. We know you are not able to leave a comment. This is because you are not yet trusted. Trying to get around the rules is not going to help. Feb 27 at 6:01
  • see EDIT1 where I provided some sample queries. Feb 27 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.