2

I have ~1.5TB of json data, 200 million records that I need to import into a PostgreSQL database and would like some advice on the recommended way to partition the table. The target schema is fairly straightforward and will be a single table like:

widgets:
    id text,
    name text,
    description text,
    country text, -- (~100 unique values, with ~30% of records in one country)
    continent text, -- (6 unique values)
    link text,
    quality smallint,
    class text, -- (around 100 unique values)
    tags jsonb,
    properties_1 jsonb,
    properties_2 jsonb

This table will be for analytical queries, not transactional. The dataset is also stable so no need to worry about future inserts after initial load. Most queries would include a WHERE equality on continent or country, combined with various WHERE clauses on description, tags, properties_*

I initially considered partitioning by continent in order to help with query performance, since most queries would use a continent filter.

Partition using LIST on continent field:

CREATE TABLE widgets_north_america PARTITION OF widgets
    FOR VALUES IN ('North America')

CREATE TABLE widgets_north_america PARTITION OF widgets
    FOR VALUES IN ('Europe')

... 4 other continent partitions

However, the partitions would still be very large since it only splits the data into 6 partitions. And I expect 30% of the data to be in a single partition After reading this helpful answer: At What Point Should I Split or Partition a Very Large but Simple Table I am now considering whether I should force partitions into small enough chunks so that each can fit into memory, (100-200?). If so I’m thinking I’d use a HASH partition on the unique id fields with a modulo 100.

Partition using HASH on unique id field:

CREATE TABLE widgets_hash_1 PARTITION OF widgets
FOR VALUES WITH (MODULUS 100, REMAINDER 0);

CREATE TABLE widgets_hash_2 PARTITION OF widgets
FOR VALUES WITH (MODULUS 100, REMAINDER 1);

... (98 other partition tables)

The downside it seems to this approach is that I don't need to query at all on the id field so I get no benefit from the partition key, however having smaller partitions might allow the table to fit into memory during queries, and increase query performance.

Hardware: I have 32GB RAM, one 2TB SSD, and four 14 TB HDDs available for this.

How should I partition this table given my situation? Any advice on indices: multicolumn vs separate indices? This is my first time dealing with any database anywhere close to this size so having a hard time thinking what the practical trade-offs should be when considering this problem.

2 Answers 2

3

For the queries targeting continents, could you easily rewrite them to target countries instead? If so, partitioning by country might be the best option (but expect some blowback from the French and the Turks).

Partitioning by something that doesn't relate to anything else is pointless. It doesn't matter if it does fit in memory, if there is never a reason to have it (and only it) in memory.

Beyond that, you need to do dig into what your queries actually are, not just 30,000 foot summaries of them.

4
  • Ok you convinced me not to use HASH. Will do continents, with country subpartitions.
    – DevX
    Nov 11, 2021 at 12:32
  • One reason I wanted to be able to store each partitioned table in memory is to be able to make indexing process more efficient. When I started this project and built a huge table, the indexing process would have been prohibitively long. Some analysis indicated it'd be at least 9 days.
    – DevX
    Nov 11, 2021 at 21:44
  • What kind of indexes? This should not be a problem with btree indexes. GiST indexes use buffered build, which should solve the problem but I have not investigated it extensively. GIN indexes do indeed build their indexes using a process not much smarter than just inserting rows into the index (but only every time maintenance_work_mem is exceeded, at least), so that could be a problem that could indeed be solved by arbitrary partitioning.
    – jjanes
    Nov 11, 2021 at 22:39
  • I was initially dumping everything as a single json field and doing a GIN index, which is what was going very slow. Once I saw how long the indexing process would take, I've restructured the schema for multiple columns and will be trying BTree indexes on specific columns of interest.
    – DevX
    Nov 12, 2021 at 3:32
2

Maybe you shouldn't partition the table at all.

Data are not cached per table, but per 8kB data block, so it doesn't matter if the table or partitions are large or small.

Also, if the data are static, you can create as many indexes as you like to speed up access, and an index scan on a large table is no slower than one on a small table.

What may make sense is to create materialized views with pre-aggregated data to speed up queries, but that's a different story.

There is one possible reason to partition the table: if a lot of your queries GROUP BY a certain column, it can speed up those queries to partition on that column and set enable_partitionwise_aggregate to on.

Your Answer

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

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