4

I tried to have this as detailed as possible. Sorry about the length!

Background

I created the following partitioned table protein_snp_assoc on a PostgreSQL (version 12.13) database:

CREATE TABLE protein_snp_assoc (
  protein_id    int not null,
  snp_id        int not null,
  beta          double precision,
  se            double precision,
  logp          double precision
) PARTITION BY RANGE (snp_id);

I then created 51 partitions, each containing roughly 150 million lines (total lines 7.65 billion), based on the following template:

CREATE TABLE IF NOT EXISTS protein_snp_assoc_(x) PARTITION OF protein_snp_assoc
  FOR VALUES FROM (y) TO (z);

where x ranged from 1 to 51, and y, z defined intervals, each of length 150,000. As an example, the first two and last partitions are:

protein_snp_assoc_1 FOR VALUES FROM (1) TO (150001),
protein_snp_assoc_2 FOR VALUES FROM (150001) TO (300001), ...
protein_snp_assoc_51 FOR VALUES FROM (7500001) TO (7650001)

The variable column protein_id has 1,000 unique values (1 to 1,000) and snp_id has 7,500,000 unique values (1 to 7,650,001). As the pair (snp_id, protein_id) uniquely determines a row in the table, I used the two columns to create a BTree index, with snp_id as the left-most variable:

CREATE INDEX ON protein_snp_assoc (snp_id, protein_id);

This will be a static database. It currently has about 20% of the total data that will be on it (since I'm prototyping), but once all the data has been added to the database, no further rows will be added (nor deleted).

Typical queries

The most common queries will be (a) single SNP/protein queries, (b) single protein, multiple SNPs queries, and (c) multiple proteins and multiple SNPs queries.

Example queries where I use VALUES as I read on this site that it can increase performance when IN(...) has multiple values.

-- Single SNP/protein
SELECT 
  * 
FROM 
  protein_snp_assoc
WHERE
  snp_id IN (VALUES (1))
AND
  protein_id IN (VALUES(1));
  
-- Multiple SNPs, single protein
SELECT 
  * 
FROM 
  protein_snp_assoc
WHERE
  snp_id IN (VALUES (1), (2))
AND
  protein_id IN (VALUES(1));
  
-- Multiple SNPs, multiple proteins
SELECT 
  * 
FROM 
  protein_snp_assoc 
WHERE
  snp_id IN (VALUES (1), (2))
AND
  protein_id IN (VALUES (1),(2));

The EXPLAIN ANALYZE for each query can be seen here (pastebin links):

Single SNP/protein (pastebin), Multiple SNPs, single protein (pastebin), Mulitple SNPs, mulitple protein (pastebin).

Benchmarking and comparison to Arrow/parquet

I ran 1,000 queries for multiple SNP/protein combinations, where the SNPs and proteins were randomly drawn before being inserted into the query. To get some sort of reference, I converted the raw data files I used to populate the database to .parquet files and ran similar queries using R and the arrow package. The results can be seen in the table below (all times are in milliseconds, lq and uq are the 25% and 75% percentiles, respectively).

index n_snps n_proteins min lq mean median uq max
postgres 1 1 0.05900 14.71125 18.02112 17.92850 21.14350 45.2850
arrow 1 1 34.31822 44.62842 49.30316 46.29033 48.07222 577.1411
postgres 1 2 4.07100 20.97125 25.40618 25.15250 29.35375 68.7700
arrow 1 2 47.61873 61.47562 67.87060 63.99824 65.68011 629.5121
postgres 10 1 118.18900 167.11100 181.76304 180.50250 196.41475 262.9640
arrow 10 1 138.73902 164.25678 177.47847 167.69684 176.15489 704.3115
postgres 10 2 168.10500 231.74825 248.74577 248.45400 264.95825 330.2810
arrow 10 2 219.73495 269.54206 287.34815 281.79291 286.22803 819.4827
postgres 10 10 731.77300 893.28625 940.90282 941.69650 989.38625 1162.4810
arrow 10 10 930.18264 1038.39510 1089.43522 1080.01131 1100.22580 2313.4975
postgres 50 1 665.23800 799.89600 849.73860 850.91900 898.27900 1050.0710
arrow 50 1 682.10049 711.62065 766.24498 735.49283 750.97367 1335.6018

As you can see, as the number of SNPs or proteins (or both) were increased, PostgreSQL and Arrow started performing similarly (although the worst-case for Arrow was consistently worse).

Hardware

CPU (pastebin). The HDD is a Seagate IronWolf 10TB (ST10000VN0008). Memory is 64GB but I can't see the specific type since I don't have sudo privileges on the machine. Operating system: Ubuntu 22.04.1 LTS.

My question

The results of the benchmark make me believe that my database is not optimized. I'm worried that as I start adding more data to the database, performance will suffer. Is there any way I can speed up queries that involve multiple proteins and SNPs, either with better design, queries or some other sort of tuning?

Update 2023-03-12

Thanks to Erwin and everyone else who has engaged. I followed Erwin's directions exactly (the only exception being that I couldn't update from v12 to v15) and then redid the benchmarks for this new table. Here are the results (compared to the original design), where index_order = snp_first is the original design and index_order = protein_first is the design proposed by Erwin:

index_order n_snps n_proteins min lq mean median uq max
snp_first 1 1 0.059 14.71125 18.02112 17.9285 21.14350 45.285
protein_first 1 1 0.060 20.96200 24.87686 26.3945 30.31275 126.046
snp_first 1 2 4.071 20.97125 25.40618 25.1525 29.35375 68.770
protein_first 1 2 2.764 37.02300 44.31820 45.7595 52.30925 84.515
snp_first 10 1 118.189 167.11100 181.76304 180.5025 196.41475 262.964
protein_first 10 1 29.754 215.37700 221.30159 255.3445 276.62725 380.930
snp_first 10 2 168.105 231.74825 248.74577 248.4540 264.95825 330.281
protein_first 10 2 88.473 320.08475 417.07273 461.6155 501.66350 593.604
snp_first 10 10 731.773 893.28625 940.90282 941.6965 989.38625 1162.481
protein_first 10 10 1189.058 1906.78050 2040.40170 2054.9985 2194.80550 2595.215
snp_first 50 1 665.238 799.89600 849.73860 850.9190 898.27900 1050.071
protein_first 50 1 200.521 910.52700 934.64351 1091.5340 1149.79875 1319.777

As you can see, the original design is considerably faster, especially on the most time-consuming queries. I'll have a chat with the sys admin about updating to v15 this week, to see if that improves performance. In any case, I think this experiment has demonstrated that this is either a query problem (the queries I wrote are probably suboptimal, see comments on how I use VALUES) or a hardware problem (the server is old).

Answers to some questions in comments

jjanes: See this pastebin: https://pastebin.com/qQR3GtZ4

a_horse_with_no_name: The VALUES idea came from here: Optimizing a Postgres query with a large IN

I wrote a test query:

EXPLAIN
WITH value_list (protein_id, snp_id) as (
  values
    (1, 1),
    (1, 2)
)
SELECT 
  *
FROM protein_snp_assoc AS p
INNER JOIN
value_list v on (p.protein_id, snp_id) = (v.protein_id, v.snp_id);

I thought this gave me the same query plan as WHERE/IN but I see now I was wrong. I'll look into this and see if it's better. Edit: it seems to perform on par with WHERE/IN and VALUES. So I guess this isn't the real bottleneck.

bobflux: I can't share the data but you can simulate it easily. Here's a quick example in R:

sim_data <- function(i, n_snps) {
  data.frame(
    protein_id = rep(i, n_snps),
    snp_id = 1:n_snps,
    beta = rnorm(n = n_snps, mean = 0, sd = 1),
    se = abs(rnorm(n = n_snps, mean = 0, sd = 1)),
    logp = abs(rnorm(n = n_snps, mean = 2, sd = 1))
  )
}
protein_id <- 10
n_snps <- 7650000
sim_data(protein_id, n_snps)

nz_21: I wrote custom scripts in R and bash.

15
  • 2
    Well written question, though I'm not sure the arrow stuff is particularly necessary. Partitioning isn't designed to improve performance AFAIK. How did you time your tests? The database execution time reported in the explain plans seems lower than the mean values reported. Seems like the execution time increases roughly linearly with the number of results retrieved which doesn't feel unreasonable? Mar 11, 2023 at 1:05
  • I ran EXPLAIN (FORMAT JSON, ANALYZE) ... and pulled the execution time from the resulting JSON. The pastebin queries might be faster due to caching. Edit: I forgot to mention, I have the arrow comparison here because I expected Postgres to perform better? Maybe that's a poor assumption.
    – jommi
    Mar 11, 2023 at 1:09
  • 1
    I am curious: why write ` snp_id IN (VALUES (1), (2))` rather than snp_id in (1,2) (not that it would matter for performance though)
    – user1822
    Mar 11, 2023 at 7:54
  • If the data isn't "top secret", could you share a slice of it, perhaps a gigabyte? I'd like to try something with Clickhouse. I will need all 5 columns (including the three floats) because that will be important for data compression.
    – bobflux
    Mar 11, 2023 at 10:53
  • Great question. How did you run all the benchmarks? Did you invoke a custom script? Did you some framework?
    – nz_21
    Mar 11, 2023 at 13:01

1 Answer 1

10

Based on this:

  • protein_id has 1,000 unique values
  • snp_id has 7,500,000 unique values
  • the pair (snp_id, protein_id) uniquely determines a row in the table

Typical queries:
(a) single SNP/protein queries
(b) single protein, multiple SNPs queries
(c) multiple proteins and multiple SNPs queries

Table is read-only.

I don't see the benefit of partitioning. Drop partitioning and use a plain table instead:

CREATE TABLE protein_snp_assoc (
  protein_id int
, snp_id     int
, beta       double precision
, se         double precision
, logp       double precision
);

After filling the table, add this PK:

ALTER TABLE protein_snp_assoc
ADD CONSTRAINT protein_snp_assoc_pkey
    PRIMARY KEY (protein_id, snp_id) WITH (FILLFACTOR = 100);

Notably, the PK replaces your index protein_snp_assoc, but with leading protein_id, since that is more commonly the single value in your queries. With FILLFACTOR = 100 since your table is read-only. Default for B-tree indexes is 90. See:

Apart from the 10 % savings, size of the underlying index is the same. Two integer columns is pretty ideal for your multicolumn index. See:

Once populated, cluster the table once on the PK:

CLUSTER TABLE protein_snp_assoc USING protein_snp_assoc_pkey;

Expensive for the huge table, but do it once. This rewrites table and index, so that you have both in pristine condition.

Or (better) if at all possible, fill the table with sorted data to begin with. Ideally use COPY with FREEZE:

COPY protein_snp_assoc FROM '/path/to/filename' WITH (FREEZE);

.. after creating or truncating the table in the same transaction. Read about FREEZE in the manual. And this:

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal as all commands must write WAL otherwise.

You must be superuser, and have access to the server filesystem, which should be the case. Else, the next-best option is \copy instead of COPY.

Either way, add the PK only after filling the table. That's a lot cheaper, and you get an index in pristine condition right away. Read the manual about populating a database.

Turn off autovacuum for this read-only table right from the start. And run a single VACUUM ANALYZE protein_snp_assoc after populating it. (Or VACUUM FREEZE ANALYZE protein_snp_assoc if you didn't go the COPY ... FREEZE route.) With your even data distribution, column statistics are not very critical, but you still need them, and the updated visibility map.

Either way, and crucially, rows with the same leading protein_id are now physically clustered, which minimizes the number of data pages that have to be read to satisfy your queries.

The table will have around 420 GB in pristine condition and the index a little over 140 GB. 7.5 billion rows x 20 bytes per index tuple (plus some overhead) or 60 bytes per table row. See:

After we have now minimized and optimized the disk footprint, caching the index will be crucial, especially with your 3.5" rotating disk. 64 GB of RAM is not exactly overkill. Postgres (in cooperation with the underlying OS) will still cache the most frequently accessed parts of index and table, and if you happen to focus on a subset of protein_id at a time, then that will still cover most of what needs to be cached. If your queries are all over the place all the time, 256 GB or more would help (a lot).

Update: You revealed in comments that there are 5 times as many rows. And read patterns are completely random. So you are back to disk-reads dominating the cost. You must get faster storage. SSD instead of HDD, there is no good alternative to that. Plus, all the RAM you can get to at least cache the most frequented parts of the index.

Do all of this with the latest version of Postgres (Postgres 15.2 at the time of writing) since there has been a steady flow of performance improvements, especially for big data.

4
  • Nice answer, +1 for sorting data before insertion
    – bobflux
    Mar 11, 2023 at 10:54
  • 1
    I think you should recommend VACUUM (FREEZE) rather than disabling autovacuum. Anti-wraparound autovacuum cannot be disabled. Mar 12, 2023 at 13:12
  • Thanks for the response. I've updated my original post with the benchmark results on a table created per your specifications. It looks like design is not the problem but rather how I write the queries or hardware.
    – jommi
    Mar 12, 2023 at 19:52
  • @LaurenzAlbe: Yes, FREEZE is another improvement! Even better directly with COPY ... (FREEZE). But in addition to disabling autovacuum for the table, not instead. (Even if anti-wraparound isn't turned off by that completely.) Mar 12, 2023 at 22:19

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.