4

I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second.

The first time a device sends the data the server script should register (INSERT) that device id in the device table and add the data to the data table.

My question is, what would be faster?

  1. To do a SELECT EXISTS query to determine whether an INSERT is required on the device table. If SELECT EXISTS returns false then execute an INSERT, otherwise do nothing.

  2. To execute always an INSERT statement. Considering the device id is primary key on the table, if the device already exists it would return an error, otherwise it would insert it.

Only the first time a device sends data would require an actual INSERT, after that no INSERT would ever be required.

The PostgreSQL version is 9.4.

The table of devices is defined as follows:

CREATE TABLE common.tag
(
  customer integer,
  hostname character varying(150),
  description text,
  model integer,
  configprofile integer,
  id character varying(150) NOT NULL,
  host integer,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_fk_client FOREIGN KEY (customer)
      REFERENCES common.customer (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_configprofile FOREIGN KEY (configprofile)
      REFERENCES common.configprofile (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_tagmodel FOREIGN KEY (model)
      REFERENCES common.tagmodel (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
  • 2
    Just a note: even when using A. you still need to handle a possible error. – jkavalik Jan 14 '16 at 11:40
  • You mean malformed SQL statement? otherwise what error could there be? won't the answer always be either 't' or 'f'? – user313631 Jan 14 '16 at 11:43
  • Imagine the situation when the script getting "first" data from a given device is slow for some reason and the second package of data arrives and is processed too, and gets to the db at the same time - both selects find nothing so both start inserting - and only one may succeed, the other generates the "duplicate PK" error in that case. – jkavalik Jan 14 '16 at 11:45
  • Your version of Postgres is essential to the question. It's also essential to know whether you have an auto-generated PK in the device table, and which constraints you have. Please provide table definitions showing all data types and constraints. And do you want to update any columns of the device table if the row already exists? – Erwin Brandstetter Jan 14 '16 at 13:29
  • Thanks for the feedback, updated the main thread with those details. And no, we don't need to update anything if the device already exists in the table. – user313631 Jan 21 '16 at 11:08
4

In Postgres 9.5, the most elegant, simple, safe and fast solution is the new UPSERT implementation:

INSERT INTO device (device_id) VALUES (1234567)
ON CONFLICT (device_id) DO NOTHING;

-- now we have either inserted the device or it was there already
INSERT INTO data (device_id, data)
VALUES (1234567, 'some data);

Obviously, there must be a some kind of unique index or exclusion constraint on (device_id).
Details in the manual.

For older versions or for auto-generated serial IDs:

| improve this answer | |
  • This is assuming the simplest possible case. Your question is a bit light on the details ... – Erwin Brandstetter Jan 14 '16 at 14:41
  • Added the missing info on the main thread. Our version is 9.4. – user313631 Jan 21 '16 at 11:11
0

I always try to register devices first and then only they can send data.But in your case ,you can try some thing like below

if exists(select 1 from devices where deviceid=@deviceid)
insert into datatable
else
insert into devicestable
insert into datatable

Exists is always faster,but you may to have see if the column which we check for existence is indexed.Your second option requires error handling (along with rollback,over head)and then do an insert.So its good to use above pseudo code model

| improve this answer | |
-2

In my opinion Select exists should be good in terms of performance. As select is faster than insert in terms of performance.Select only reads data and return the result, The insert query must read that data, insert in the table, then it check for primary key and found guilty of duplicate, then it revert the changes and raise an error.

| improve this answer | |
  • Downvotes may be because you seem to be guessing; you don't provide links do sources, or seem to be doing any of your own testing or benchmarking. – Chris Harrington Jan 31 '16 at 18:42
  • Note that your assumption can be easily inverted: Imagine a storage backend where writes are cached, and selects are random and unlikely to be fulfilled by cache. – Chris Harrington Jan 31 '16 at 18:46

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.