This is my current table definition in a Postgres 10.1-1 database:
CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15'); -- max length of enum names ~15
CREATE TABLE CUSTOMER(
CUSTOMER_ONE TEXT PRIMARY KEY NOT NULL, -- max 35 char String
ATTRIBUTE_ONE TEXT UNIQUE, -- max 35 char String
ATTRIBUTE_TWO TEXT, -- 1-80 char String
PRIVATEKEYTYPE CUSTOMER_TYPE -- see enum
);
It results in about 4.3x more database size compared to the size of the inserted data. (50 MB, 700.000 lines --> database size is 210 MB)
Attribute_One
is computed as hash(Customer_One)
.
Requirements: fast searches (using algorithms) for columns CUSTOMER_ONE
and ATTRIBUTE_ONE
. (That's why I think I need an index.)
Typical search query:
select * from customer
where Customer_One='XXX' OR Attribute_One='XXX';
Each SELECT
can find a maximum of 1 or 0 matching rows in millions of rows.
Is it possible to further decrease the DB size? I have been told to use an expression index but don't fully understand how this works. A short explanation with an example index or other solution would be great
Is the insert speed effected by those indexes? The faster the better. (To be clear: search speed is more important than insert speed.)
Attribute_One is calculated from Customer_One
. How exactly? And what "algorithms" do you use in your "fast searches"? Show a typical example query. Also: your version of Postgres, please.