We're building an analytics service. One of our needs is to maintain ~100MM sets each with anywhere between one member to 1MM members, with each member represented as a long. We need to be able to very quickly:

-Add a member to a set, and know whether it was added

-Update a counter indicating the size of the set

We have a realtime processing loop that ingests new data and needs to be able to update some number of the sets as well as its counter.

At the moment, we have this implemented in PostgreSQL, distributed application side by set name. There's one table with two columns: the set name in the first and the value in the second, as well as an index on set name, value. Is there a better method than this? AFAIK it's not a problem with a standard solution.

  • Are you having some kind of issue or problem with how you are doing this now? Sounds like you're on the right track. When you say "very quickly" - what exactly do you mean? 100 million rows per second? – Max Vernon Nov 6 '13 at 2:53
  • 100k updates per second is what we're looking for. We were thinking of doing one table for all data. Would that make the index too large? Should we have multiple tables per database so that indicies don't grow too large? Is there some optimal table size? – sskates Nov 6 '13 at 4:36

Optimal table size would be environment specific. I had an almost exact similar environment. A billing/tracking/logging system was generating 2-3GB of data per day and was already at 1.5TB in 1 table alone. An ETL process fed data in so that was the only 'write', while thousands of users 'read' it.

It seems like you need to maximize both writes and reads like I did, so questions that comes to mind are what's your budget and what's the concurrency going to be like, as in how many requests per second on the same table so we can figure out if we also need to address locking. Also, what are your disk arrays currently configured like and do you have any baselines?

Read this first.
Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Each point is critical for success, at least in the 1.5 TB nightmare I had inherited. This article hits most of it on the head. Remember that you can have 'historic' databases mapped by views, and keep the 'live' databases small by purging the to the historic databases often so backups and performance on most recent data is fast.

Concurrency: Isolation Levels & Snapshot Level Isolation

This basically makes SQL Servers isolation level the same as Oracle. Reads don't block writes, writes still block reads. It comes at the expense of (I believe) 14 bytes added to each row and a copy of it stored in TempDB per update. This means your tempDB is going to be a big huge bottleneck if it isn't optimized for this so watch the metrics carefully.


To optimize writes you would want to optimize your transaction log first, IMO set the 'time to recovery' option to a higher number and let the lazywriter update the data when it can. This would mean throwing lots of IOPS at the tlog for writes and keeping writes in the Tlog drive under 5ms during full load. The reason we set the time to recover to a higher number is so the transaction log can update the data less, thus being less dependent on your disk arrays.

Record disk sec/writes on all your data/tlog drives.

  • Partitioning: Efficiently delete data by using partitioning. It's a metadata operation.


If you have high cardinality you can vastly improve read performance through the transparent compression option in SQL Server, at the expense of writes... and it's a 'enterprise only' option, not sure if you have that coming from Postgres.

Try to keep base table data in memory. Record disk sec/reads metrics to see how fast your responses are.

Ensure your queries are using partition elimination. Kim Tripp has a great video of it here.

This is just a 'brief overview' without knowing more about your environment, the link to the data warehouse article is very good and covers a lot of what I wanted to say. Feel free to give us more details or ask questions.

  • OP mentioned PostgreSQL and not SQL Server. Not sure how much of this is applicable in a PostgreSQL environment. – Iain Samuel McLean Elder Jan 6 '14 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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