9

Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with.

My project is about collecting data from devices (up to 1000 or more devices), where every device is sending one data block every second, which makes about 3 million rows per hour.

Currently I've got one big table where I store the incoming data of every device:

CREATE TABLE data_block(
    id bigserial
    timestamp timestamp
    mac bigint
)

Because there are several types of data a data block can (or can not) include, there are other tables which reference the data_block table.

CREATE TABLE dataA(
    data_block_id bigserial
    data

    CONSTRAINT fkey FOREIGN KEY (data_block_id) REFERENCES data_block(id);
);
CREATE TABLE dataB(...);
CREATE TABLE dataC(...);
CREATE INDEX index_dataA_block_id ON dataA (data_block_id DESC);
...

It is possible that in one data_block there is 3x dataA, 1x dataB, but no dataC.

The data will be kept for some weeks, so I'm going to have ~5 billion rows in this table. At the moment, I have ~600 million rows in the table and my queries take a really long time. So I decided to make an index over timestamp and mac, because my select statements always query over time and often also over time+mac.

CREATE INDEX index_ts_mac ON data_block (timestamp DESC, mac);

...but my queries still take ages. For example, I queried data for one day and one mac:

SELECT * FROM data_block 
WHERE timestamp>'2014-09-15' 
AND timestamp<'2014-09-17' 
AND mac=123456789
Index Scan using index_ts_mac on data_block  (cost=0.57..957307.24 rows=315409 width=32) (actual time=39.849..334534.972 rows=285857 loops=1)
  Index Cond: ((timestamp > '2014-09-14 00:00:00'::timestamp without time zone) AND (timestamp < '2014-09-16 00:00:00'::timestamp without time zone) AND (mac = 123456789))
Total runtime: 334642.078 ms

I did a full vacuum before query run. Is there an elegant way to solve such a problem with big tables to do an query <10sec?

I read about partitioning, but this won't work with my dataA, dataB, dataC references to data_block_id right? If it would work somehow, should I make partitions over time or over mac?

I changed my index to the other direction. First MAC, then timestamp, and it gains a lot of performance.

CREATE INDEX index_mac_ts ON data_block (mac, timestamp DESC);

But still, queries take >30sec. Especially when I do a LEFT JOIN with my data tables. Here is an EXPLAIN ANALYZE of the query with the new index:

EXPLAIN ANALYZE SELECT * FROM data_block WHERE mac = 123456789 AND timestamp < '2014-10-05 00:00:00' AND timestamp > '2014-10-04 00:00:00'
Bitmap Heap Scan on data_block  (cost=1514.57..89137.07 rows=58667 width=28) (actual time=2420.842..32353.678 rows=51342 loops=1)
  Recheck Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on index_mac_ts  (cost=0.00..1499.90 rows=58667 width=0) (actual time=2399.291..2399.291 rows=51342 loops=1)
        Index Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
Total runtime: 32360.620 ms 

Unfortunately my hardware is strictly limited. I'm using an Intel i3-2100 @3.10Ghz, 4GB RAM. My current settings are as following:

default_statistics_target = 100
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 512MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 2GB
max_connections = 20
random_page_cost = 2
1

This may reflect my MS SQL bias, but I'd try clustering the table by timestamp. If you're frequently pulling data for a specific time span, this will help because the data will be physically stored contiguously. The system can seek to the start point, scan to the end of the range, and be done. If you're querying for a specific hour, that's just 3,600,000 records.

If your query (which is...?) is for a specific machine, Postgres will then need to filter out 99.9% of those 3.6 M records. If this one-in-a-thousand filter is more selective than a typical date range fitler, you should use the more selective mac field as the first component of your index. It may still be worth clustering.

If that still doesn't do it, I'd partition by the same field you're indexing, either timestamp or mac.

You didn't give the data types. Are they appropriate to the data? Storing dates as text will needlessly bloat your table, for example.

  • 2
    Postgres doesn't have clustered indexes (although it can cluster a table along an index - but that needs to be done manually and won't "stay") – a_horse_with_no_name Oct 2 '14 at 13:24
  • thank you for the advice. now it runs faster than before, but still at a very low performance >30sec per query. i also did clustering, but as @a_horse_with_no_name said: in postgres this is a one-shot. my data types are right i think. i added them in the question – manman Oct 2 '14 at 14:48
  • Without clustered tables, my next recommendation for range queries would be partitioning. – Jon of All Trades Apr 24 '15 at 14:48
-2

I worked on an application that had billions of readings from electric meters and executed most queries in well under 10 seconds.

Our environment was different. Microsoft SQL Server on a server class machine (4 cores, 24 GB memory). Any chance to upgrade to a server?

One big issue is that ingesting the readings one at a time had a big performance impact on the database. Writing data required locks and queries would wait. Can you do inserts in batches?

With your schema, you'll have 4 very large tables. It'll be important that all your joins use indexes on both tables. A table scan will take forever. Is it feasible to merge them to 1 table with null able fields?

  • inserts in batches: i could do bulk-inserts but at the moment im working on a test database, where no inserts are made at all while a query is running. but thank you i will think of that later :) indices: i have indexes on every tables. on the data tables an index on the id, on the data_block table on (mac, timestamp). the problem is also there when im searching for dataA per left-join but ther is no. even with index it searchs the data tables. nullable fields: are not possible because a data_block can have more than one data of one kind. 1xdata_block --> 4xdataA e.g. – manman Oct 2 '14 at 14:40
  • Does your DB tool give you a query analyzer? You might need an index on data_block based on id. – KC-NH Oct 2 '14 at 20:26
  • i'll try, but i dont understand why this can help!? – manman Oct 6 '14 at 6:40
-2

You are hitting the inherent scalability limits of Postgres (or any other RDBMS).

Remember that an RDBMS index is a B-Tree. A B-Tree is O(log n) for both average and worst case. This makes it a nice, safe, predictable choice for reasonable values of N. It breaks down when N gets too big.

NoSQL databases are (for the most part) hash tables. A hash table is O(1) in the average case and O(n) in the worst case. Assuming you can avoid the worst case, it performs really well for very large values of N.

Additionally, a hash table is easy to parallelize and a b-tree is not. This makes hash tables more suitable for a distributed computing architecture.

When you start getting to billion row tables, it's time to consider switching from RDBMS to NoSQL. Cassandra would probably be a good choice for your use case.

  • 2
    Lots of RDBMS have many more options than B-tree indexes (hash, bitmap and others). Some DBMS are storing rows and some are storing columns. And O(logn) is not bad, even for billions of rows. And they can't possibly be hitting any limit when they are using a 4GB memory machine. – ypercubeᵀᴹ Apr 24 '15 at 8:53

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.