StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You haven't provided much background as to what your queries actually look like, but I will walk you through how to <strong>estimate the ballpark</strong> of whether your expectations are realistic, using PostgreSQL as example.</p> <p>Preparing a dummy table with 10M rows, and 80 bytes of filler data per row:</p> <pre><code>create table foo as select generate_series(1,10000000) as foo_id, repeat('a', 80) as filler; create unique index foo_foo_id on foo (foo_id); vacuum analyze foo; </code></pre> <p>This table is 1400 MB total, including the index, so it fits entirely into my OS cache, but not PostgreSQL's shared buffers.</p> <p>Creating a custom <a href="http://www.postgresql.org/docs/9.0/static/pgbench.html">pgbench</a> script to fetch 70000 rows ordered by an index:</p> <pre><code>\setrandom key 1 9000000 SELECT * FROM foo WHERE foo_id > :key ORDER BY foo_id LIMIT 70000; </code></pre> <p>Here are the results from running the benchmark on my 4-core desktop computer (AMD Phenom II X4 955) for 1 minute:</p> <pre><code>% pgbench -j 4 -c 4 -T 60 -n -f script.pgb transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 4 number of threads: 4 duration: 60 s number of transactions actually processed: 3922 tps = 65.309954 (including connections establishing) tps = 65.316916 (excluding connections establishing) </code></pre> <p>Note that here the client (pgbench) and server are on the same physical machine. In reality they would be different, so things like network overhead and throughput come into play.</p> <p>This naive configuration can handle ~65 such queries per second. Much lower than "few hundred requests per second", so you'd need <em>a lot</em> more powerful server to handle this kind of workload. Replication with multiple slaves is an option.</p> <p>To get a more realistic result, you should tweak the pgbench script and test data to match your workload closer.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload