1

In the latest years the "Insert Only" methodology came more and more popular. For those who use SQL DB you probably know that in high volume with a lot of update queries the DB is locking the rows and you starting to get a "bottleneck". the Insert Only mode is to use only insert (without updates) and always retrieve the latest item in the DB.

The issue I'm facing is with the SELECT queries since there is a field that can be common for multiple records in the DB and if I will want to query by it I will never know when I got all of the latest records for the field above (unless I'm using GROUP and this will not be efficient)

Scheme Example:

let say I have the following scheme: 

CREATE TABLE users
(
  id         SERIAL    NOT NULL
    CONSTRAINT users_pkey
    PRIMARY KEY,

  first_name VARCHAR(255),
  last_name  VARCHAR(255),
  username   VARCHAR(255),
  email      VARCHAR(255),
  password   VARCHAR(255),
  account_id INTEGER,
  created_at TIMESTAMP NOT NULL
);

Now let say I have the following users that's related to account number 1 (using account_id): 1. John Doe 2. Jain Doe

If I will want to edit John Doe last name in the Insert Only mode I will insert a new record and when I will want to retrieve it I will run the following query:

SELECT * from users WHERE email='jhon.doe@test.com' ORDER BY created_at Desc limit 1;

The issue is what I need to to if I want to retrieve all account 1 users ? how can I prevent from executing poor query with group by

The following query will return 3 records although I have only 2 users

SELECT * from users WHERE account_id=1;
Shalom Balulu
  • 379
  • 1
  • 9
  • 20
  • 2
    Your code is Postgres code so I removed the MySQL tag. And you need a `user_id` in your data model to identify the actual users. – Gordon Linoff May 29 '19 at 11:37
  • *"Does SQL database can support “insert only” mode?"* Well MySQL would had more or less support for it with [The ARCHIVE Storage Engine](https://dev.mysql.com/doc/refman/8.0/en/archive-storage-engine.html) where DELETE and UPDATE are not allowed as the question was tagged with MySQL. – Raymond Nijland May 29 '19 at 11:39
  • it would be possible to use [SQL/MED](https://wiki.postgresql.org/wiki/Foreign_data_wrappers) (PostgreSQL called them Foreign data wrappers) to program you own table engine in PostgreSQL to give PostgreSQL the support of MySQL's ARCHIVE engine i geuss to make a pure insertable table only.. But now i think of it just simply grant the SELECT/INSERT and make sure to revoke the UPDATE/DELETE privilege for the user that is going to use it.. – Raymond Nijland May 29 '19 at 11:43
  • Possible duplicate of [Efficient latest record query with Postgresql](https://stackoverflow.com/questions/1684244/efficient-latest-record-query-with-postgresql) – Serg May 29 '19 at 11:49
  • I don't understand the problem you are trying to solve. What's the problem with an UPDATE locking the row? You can still read that row without being affected by the lock. Queries on "insert only" tables will have a **lot** more overhead compared to occasionally locking a single row –  May 29 '19 at 13:13
  • Please say you're _not_ storing user passwords in plain text… – Zack May 29 '19 at 13:23

1 Answers1

4

The answer to your question is distinct on (in Postgres). However, it is unclear how you define a user. I would expect a user_id, but perhaps email is supposed to serve this purpose.

The query looks like:

select distinct on (email) u.*
from users u
where account_id = 1
order by email, created_at desc;

For performance, you want an index on users(account_id, email, created_at desc).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786