Given two simple, contrived example tables
create schema test; create table test.exchange ( id serial primary key, created_at date not null ); create index on test.exchange(created_at); create table test.exchange_content ( id serial primary key, content text not null, exchange integer not null references "exchange"(id) ); create index on test.exchange_content(exchange);
with a lot fake data
INSERT INTO test.exchange(id,created_at) SELECT id, NOW() + (random() * (NOW()+'360 days' - NOW())) + '30 days' FROM generate_series(1,10000000) id; INSERT INTO test.exchange_content(id,content,exchange) SELECT id, md5(random()::text), id % 10000000 + 1 FROM generate_series(0,30000000) id;
Attempting this query...
select count(*) from exchange left join exchange_content ec on exchange.id = ec.exchange where created_at > '2021-10-14';
runs a sequential scan on exchange_content (for Postgres 9.6)
With these tables and large queries frequently being executed on them together, what are the strategies for ensuring that the queries will scale well, as the tables grow?
(Table partitions seem like the best option so far, is there any other options that I'm missing?)
Update: Based on comments it looks like upgrading to Postgres 12 would be best, but I'm still wondering if there are idiomatic strategies for designing tables with this structure and large amounts of data.
What have I considered:
- Composite indexes but they don't work across multiple tables
- Duplicating the created_at field over to exchange_content and giving it its own index
- Using a materialized view with the created_at field and the data I need from exchange_content
- It may be using Seq Scan because Seq Scan may be faster than using the index
- Partial indexes, but I don't think they apply given my requirements
- Using a table partition for date ranges