2

I have a very large (>10m rows) table in a Postgres 9.5.4 database that collects readings from sensors. This table handles many inserts (~2k/minute) but isn't queried too often--only during a cache miss or when rebuilding the cache. Said table is essentially ‘append-only’--there are no updates. However, the queries that I do run are often fairly complex, and I care about optimizing read performance as cache misses can be very slow to a user.

In the business context of relevance, there are sensors that collect energy and power quality data from buildings; there can be multiple sensors for buildings, and a common query is aggregating facility level data. The table schema looks like so:

                                   Table "public.measurement"
    Column     |            Type             |                        Modifiers                         
---------------+-----------------------------+----------------------------------------------------------
 id            | integer                     | not null default nextval('measurement_id_seq'::regclass)
 meter_id      | integer                     | 
 timestamp     | timestamp without time zone | 
 value         | double precision            | 
 _reading_type | smallint                    | 
 facility_id   | integer                     | 
Indexes:
    "measurement_pkey" PRIMARY KEY, btree (id)
    "ix_measurement_timestamp_meter_id__reading_type" btree ("timestamp", meter_id, _reading_type)
    "ix_measurement_timestamp_facility_id__reading_type" btree ("timestamp", facility_id, _reading_type)
Foreign-key constraints:
    "measurement_meter_id_fkey" FOREIGN KEY (meter_id) REFERENCES meter(id)
    "measurement_facility_id_fkey" FOREIGN KEY (facility_id) REFERENCES facility(id)

I am considering dropping the id column, which is a meaningless ‘surrogate key’, as I never, ever query it, but I'm curious what the performance implications would be.

I essentially run two different types of queries on this table:

  • "Give me all of the measurements between time start and time end of _reading_type 0 for meter_id X", or
  • "Give me all of the measurements between time start and time end of _reading_type 0 for facility_id X" (I believe the facility_id column denormalizes the table, but saves what was an expensive join).

Questions

  • What would happen if I do not declare a primary ke?

  • The combination of (timestamp, meter_id, _reading_type) is guaranteed to be unique, so: what would be the implication of dropping the multicolumn index on these columns and defining a composite primary key?

5
  • 2
    If you're not having any other table referencing this one through the (surrogate) Primary Key, you probably don't need it. Surrogate integer primary keys are not mandatory. Your natural key is the composite you're considering using. Removing id will make your table take less space.
    – joanolo
    Commented Feb 12, 2017 at 23:04
  • That's what I figure--the table is so long that an unnecessary column adds up. I guess my question is really: should I add a composite primary key on those three columns, or should I keep the existing index and have no primary key at all?
    – nrlakin
    Commented Feb 12, 2017 at 23:19
  • 1
    The only practical difference will be that making it a PRIMARY KEY will guarantee that you don't have NULL values, and that the composite is UNIQUE. I think it makes sense. It also, somehow, documents how rows can be identified. Some databases (not PostgreSQL) cluster data by default. If that were the case, some other considerations would have to be taken to avoid fragmentation. I would try defining the PK in several orderings (timestamp, meter, reading_type) or (meter, reading_type, timestamp) and see which one works faster for your queries...
    – joanolo
    Commented Feb 12, 2017 at 23:23
  • 1
    I'm confused at what you want in the way of an answer. Nothing bad will happen if you drop a surrogate key. You don't need to have one. In your case, you're not using one. End of story. As my answer implies. What more are you looking for? Commented Feb 13, 2017 at 16:59
  • 1
    @nrlakin - even at 2k rows per minute, that equates to 2.8 million rows per day, which means your table is less than 4 days old, if it only has 10 million rows. Are you deleting/truncating data from the table?
    – Hannah Vernon
    Commented Feb 14, 2017 at 16:09

2 Answers 2

2

What would happen if there is no primary key? The combination of timestamp-meter_id-_reading_type is guaranteed to be unique; what would be the implication of dropping the multicolumn index on these columns and making a composite primary key?

You don't need a surrogate key, but you can still use a PRIMARY KEY. It sounds to me like the Primary Key here is

meter_id, reading_id, timestamp

Also you have two of the same keys, drop one of them

"ix_measurement_timestamp_meter_id__reading_type"    btree ("timestamp", meter_id, _reading_type)
"ix_measurement_timestamp_facility_id__reading_type" btree ("timestamp", meter_id, _reading_type)

That's got to be a typo error, but you're just slowing down inserts. At 10 million rows, you may also want to look at partitioning in the upcoming 9.7 or inheritance if you can't upgrade.

This table handles many inserts (1k-2k/second)

You're also not likely doing 2,000 INSERTs a second. That would be 7.2 MILLION an hour. Unless your table gets truncated every 1.5 hours.

2
  • Correct on both typos--the insert rate and the second index. Both fixed in the original question. Partitioning is next task in the queue, although I've been putting it off until I resolve my schema issues.
    – nrlakin
    Commented Feb 13, 2017 at 2:47
  • 2
    @nrlakin partitioning has it's own overhead and pain: don't do it unless you have to. Actual use case: you need to remove large amounts of data periodically and DELETE is too slow or generates too much logging. Commented Feb 14, 2017 at 15:46
4

I have a very large (>10m rows)

Tables with billions of rows in them are common — don't fixate on the size of the table, it is much more important to know how many blocks you scan to query it, which depends on the query and on indexes. You do not want to consider partitioning, or anything exotic, just because you have 10 million rows in a table.

I essentially run two different types of queries on this table:

  • "Give me all of the measurements between time start and time end of _reading_type 0 for meter_id X", or
  • "Give me all of the measurements between time start and time end of _reading_type 0 for facility_id X" (I believe the facility_id column denormalizes the table, but saves what was an expensive join).

Then your indexes would be better off as:

"ix_measurement_timestamp_meter_id__reading_type" btree (_reading_type, meter_id, "timestamp")
"ix_measurement_timestamp_facility_id__reading_type" btree (_reading_type, facility_id, "timestamp")

… or better still make them partial indexes where _reading_type=0:

create index on measurement(meter_id,"timestamp") where _reading_type=0;

Calling a column "timestamp" is not a good idea, change it if you can.

I am considering dropping the id column, which is a meaningless ‘surrogate key’, as I never, ever query it, but I'm curious what the performance implications would be.

Any impact on performance is going to be minimal compared to tailoring your indexes to make them more selective.

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.