What are the possible performance implications of dropping a not used ‘surrogate key’ column? 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?
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.