Styjun

Garage door sticks on a bolt

Re-entering the UK after overstaying in 2008

Can the President of the US limit First Amendment rights?

Airport Security - advanced check, 4th amendment breach

How do we know neutrons have no charge?

What does a textbook look like while you are writing it?

Lighthouse Alternatives

Are there types of animals that can't make the trip to space? (physiologically)

Could Boris Johnson face criminal charges for illegally proroguing Parliament?

What's the global, general word that stands for "center tone of a song"?

Bothered by watching coworkers slacking off

Why has Speaker Pelosi been so hesitant to impeach President Trump?

Can Familiars read and use spell scrolls?

Get the exact size of files retrieved by find output

Phonetic distortion when words are borrowed among languages

Giving a good fancy look to a simple table

GPLv3 forces us to make code available, but to who?

How important is knowledge of trig identities for use in Calculus

Can I bring this power bank on board the aircraft?

MaxCounters solution in C# from Codility

What is the difference between increasing volume and increasing gain?

Does the 'java' command compile Java programs?

What is the use of command?

How do my husband and I get over our fear of having another difficult baby?

Best practice to keep track of User's last API use date-time

Why is iterating through a large Django QuerySet consuming massive amounts of memory?Best practices for SQL varchar column lengthRDBMS - Lock Table to prevent InsertsIs there any way to know what rows are changed in statement level trigger in postgreSQL?Fire SQL Trigger only when a particular user update the rowPostgres: How to prevent read with explicit lock?how do concurrent reads affect Postgres?postgres acquire row-level lockBest way to update a HTML table in real timeatomically compare and update multiple rows using postgres transaction with maximum concurrency

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

0

I have API service that users with given API-key can use with Postgres database.

Currently, I'm updating 'users' table's "last_usage_date" column on every usage of API.

But now I see the 'users' table gets pretty busy all the time because there are so many APIs fired. (the table is almost always updating)

I have three questions

1. If a row of the table is updated, will it lock the whole table? Will it slow down(or delay) next look-ups or insert/update? Any performance concerns?




2. If postgres supports row-level locking, will the fact that the user-row is being updated makes other APIs to wait(delay) if it's about to take a look at that row??




3. If this is a bad design to keep track of the last_api_usage_date, what will be the best practice?

Thanks in advance!

postgresql

share|improve this question

edited Mar 29 at 15:38

Andre Song

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  This question is tagged with two different database systems. Which one are you using? If mysql, which storage engine are you using? Whether row-level locking is supported will be relevant to this question.
  
  – Craig Meier
  Mar 28 at 20:45
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If you want to sacrifice a bit of accuracy for less contention you could 'UPDATE users SET last_usage_date=NOW() WHERE id=X and last_usage_date > DATE_SUB(NOW(), INTERVAL 1 HOUR)'
  
  – danblack
  Mar 28 at 23:05
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You'll have to store the timestamp somewhere, and a database is as good a place as any. Your question boils down to "how many writes per second can my database handle", which has no easy answer. If done properly, the answer is hopefully a lot.
  
  – deceze♦
  Mar 29 at 15:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the comment guys! I updated my tags. I use Postgresql at the moment. And I don't quite get what u mean by 'which storage engine I am using'. Is there multiple versions of storage engine per database language?? Also, I'm not so sure if postgres has row-level locking. How do i check that??
  
  – Andre Song
  Mar 29 at 15:36
  

  
  
  
  

add a comment
 | 

0

I have API service that users with given API-key can use with Postgres database.

Currently, I'm updating 'users' table's "last_usage_date" column on every usage of API.

But now I see the 'users' table gets pretty busy all the time because there are so many APIs fired. (the table is almost always updating)

I have three questions

1. If a row of the table is updated, will it lock the whole table? Will it slow down(or delay) next look-ups or insert/update? Any performance concerns?




2. If postgres supports row-level locking, will the fact that the user-row is being updated makes other APIs to wait(delay) if it's about to take a look at that row??




3. If this is a bad design to keep track of the last_api_usage_date, what will be the best practice?

Thanks in advance!

postgresql

share|improve this question

edited Mar 29 at 15:38

Andre Song

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  This question is tagged with two different database systems. Which one are you using? If mysql, which storage engine are you using? Whether row-level locking is supported will be relevant to this question.
  
  – Craig Meier
  Mar 28 at 20:45
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If you want to sacrifice a bit of accuracy for less contention you could 'UPDATE users SET last_usage_date=NOW() WHERE id=X and last_usage_date > DATE_SUB(NOW(), INTERVAL 1 HOUR)'
  
  – danblack
  Mar 28 at 23:05
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You'll have to store the timestamp somewhere, and a database is as good a place as any. Your question boils down to "how many writes per second can my database handle", which has no easy answer. If done properly, the answer is hopefully a lot.
  
  – deceze♦
  Mar 29 at 15:36
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the comment guys! I updated my tags. I use Postgresql at the moment. And I don't quite get what u mean by 'which storage engine I am using'. Is there multiple versions of storage engine per database language?? Also, I'm not so sure if postgres has row-level locking. How do i check that??
  
  – Andre Song
  Mar 29 at 15:36
  

  
  
  
  

add a comment
 | 

I have API service that users with given API-key can use with Postgres database.

Currently, I'm updating 'users' table's "last_usage_date" column on every usage of API.

But now I see the 'users' table gets pretty busy all the time because there are so many APIs fired. (the table is almost always updating)

I have three questions

1. If a row of the table is updated, will it lock the whole table? Will it slow down(or delay) next look-ups or insert/update? Any performance concerns?




2. If postgres supports row-level locking, will the fact that the user-row is being updated makes other APIs to wait(delay) if it's about to take a look at that row??




3. If this is a bad design to keep track of the last_api_usage_date, what will be the best practice?

Thanks in advance!

postgresql

share|improve this question

edited Mar 29 at 15:38

Andre Song

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges

share|improve this question

edited Mar 29 at 15:38

Andre Song

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges

share|improve this question

edited Mar 29 at 15:38

Andre Song

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges

asked Mar 28 at 20:39

Andre SongAndre Song

591111 bronze badges