-1

Consider this table, but with thousands of records, on PostgreSQL:

| id |    uuid | color    | ... |          deleted_at |
|----|---------|----------|-----|---------------------|
| 1  | 4fc1... | red      | ... | 2020-01-01 13:00:00 |
| 2  | 4fc1... | gray     | ... | 2020-01-01 13:00:00 |
| 3  | 4fc1... | blue     | ... |                null |
| 4  | 4fc1... | red      | ... |                null |
| 5  | 4fc1... | blue     | ... | 2019-12-03 00:45:00 |

The purpose:

  • id (autoincrement) is used as a primary key and main hook to use JOIN with other tables.
  • uuid is a public-facing value to identify the record.
  • deleted_at (timestamp|null) is used to check when the record was deleted, and filter accordingly.

80% of queries are done using:

  • WHERE [id] = ? AND WHERE [deleted_at] NOT NULL
  • WHERE [uuid] = ? AND WHERE [deleted_at] NOT NULL

When the database grows bigger, I can see a performance degradation because the these columns are not indexed, except for the id which is set as primary key (autoincrement) but used along the deleted_at column.

INSERT/UPDATE operations are somewhat minimal, and DELETE are pretty much non-existent.

I thought of composite primary keys and making two indexes of id+deleted_at and uuid+deleted_at, but I'm not sure since it may hinder INSERT/UPDATE operations considering there are now two indexes instead of just a primary key.


How I could accelerate queries to this table?

Update 1: I considered to use ID and UUID since the I have tables that are connected to three tables, so three UUIDs = 128×3 bits, while three ID = 64×3 bits. But I can trade 192 bits for consistency.

4
  • 1
    Why are you using uuid to identify the row if the PK is id? – McNets Jan 3 '20 at 15:40
  • I'd use a trigger and put "deleted" records into an archive table. Then, as has been suggested, a UNIQUE index on your UUID column, although @McNets ' comment is valid - why have both? There are sometimes valid security reasons not to use sequential ids (I seem to remember a snafu somewhere over publicly accessible sequential account number keys - can't remember the bank but it was a biggie!). Maybe also a normal index on colour if you search regularly on that? p.s. welcome to the forum! :-) – Vérace Jan 3 '20 at 16:30
  • 1
    Thanks for the warm welcome? The color is just an example, there are lot of different values. I'll update the question again. – DarkGhostHunter Jan 3 '20 at 16:42
  • 1
    Why are you assuming there's going to be a problem with updates and inserts? Have you load tested it? – George.Palacios Jan 3 '20 at 18:13
4

A retrieval by primary key using where id = ? is pretty much constant in time regardless of the size of the table. Adding a condition with AND won't change that (using OR would be a different thing though)

If you need the same performance for queries using the condition on the uuid column, create a (unique) index on that as well.

If you always query with = there is no need to add additional columns to the index. Creating an additional composite index that contains (id, deleted_at) won't improve the query speed.

If at all, a filtered (partial) index might help:

create unique index on (id) where deleted_at is null;
create unique index on (uuid) where deleted_at is null;

But that only helps if you have much more deleted rows, than not-deleted rows.


Making id, deleted_at the primary key seems to be completely wrong as that means you can have more than one row with the same value for id - hardly what you expect.

1
  • Thanks, I edited the question with the primary column index. – DarkGhostHunter Jan 3 '20 at 16:34
1

I take this table structure to mean the records are never actually deleted just marked as deleted so these are are excluded from the searches. This is not the best design to use datestamp field for this

consider

For a composite index the size of the index will be big plus overhead

  • UUID is 128 bits + 32bits for timestamp = 160
  • ID is int 32 bits + 32 bits for timestamp = 64 bits
  • ID is bigint 64 bits 32 bits for timestamp = 96 bits

I do not know the rest of the logic but bool type would make far more sense and easier to wrap ones head around instead of using a null to track not deleted

Composite index would be

  • UUID is 128 bits + 1 for bool = 129
  • ID is int 32 bits + 1 for bool = 33 bits
  • ID is bigint 64 bits + 1 for bool = 65 bits

There is additional overhead i'm not including but should communicate the idea

Composite Indexes are created when doing searches for specific records. So if the common search key using is UUID+Delted_At or ID +Deleted_at then make the indexes

Plus you can make this indexes include only when Null is true. this would affect index size a bit ..

The more indexes one has the slower Inserts, Updates and delete will become.

I would not worry about the performance until hundreds of thousands to millions of records are in the table.

for a Primary key use either the UUID or ID column do not use both or composite key that includes a Date, that is asking for problems

12
  • > I would not worry about the performance until hundreds of thousands to millions of records are in the table. Do you mean, overall? or just INSERT/UPDATE/DELETE operations? – DarkGhostHunter Jan 3 '20 at 16:32
  • "is not the best design to use datestamp field for this" - if you want to know when a row was deleted then this is quite OK. And I find a condition where deleted_at is null not much harder to read then where not deleted The composite index on (id, deleted_at) won't really improve the performance if there is already a unique index on id (it would be different if id was not unique though) – a_horse_with_no_name Jan 3 '20 at 16:41
  • @DarkGhostHunter tuples/records in the table, offcourse if this table has tens of thousands of read-only a second to small number of insert/updates a second that is different problem to solve. – zsheep Jan 3 '20 at 16:48
  • @a_horse_with_no_name I disagree, for the cost of 1 bit to know if a records is deleted communicates very clearly vs a NULL = not deleted. I did not mean to suggest removing the timestamp field. What i should have clearly stated its a bad to turn NULL into a logical bit. NULL means UNKNOWN STATE or EMPTY SET lots of people have hard enough understanding NULLs in DBs and every program languages treats NULLs differently. – zsheep Jan 3 '20 at 16:56
  • I need to know when the record was deleted, so we can leave the timestamp alone. If I use the id as primary key, then only using uuid+deleted_at as index should have better results than using id+deleted_at plus uuid+deleted_at? – DarkGhostHunter Jan 3 '20 at 16:58
0

Move the deleted rows to a new table, that is the same structure as the current table. Use a UNION if you want to select both deleted and non-deleted items

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.