CODEWITHSUNDEEP
phppostgresqlautomation
Rahul Prasad
Rahul Prasad

Reputation: 8222

How do I schedule an SQL to execute later @database level

---------Specification---------
Database: PostgreSQL
Language: PHP

---------Description---------
I want to create a table to store transaction log of the database. I just want to store brief information.

I think that during heavy concurrent execution, adding data (transaction log from all table) to single log table will bottleneck performance.

So I thought of a solution, why not add the SQL for transaction log to a queue which will execute automatically when there is NO heavy pressure on database.

---------Question---------
Is there any similar facilities available in PostgreSQL. OR How can I achieve similar functionality using PHP-Cron job or any other method. Note: Execution during LOW pressure on DB is necessary

---------Thanx in advance---------

EDIT:
Definition
Heavy Pressure/heavy concurrent execution: About 500 or more query per sec on more than 10 tables concurrently.
NO heavy pressure: About 50 or less query per second on less than 5 tables concurrently.
Transaction log table: If anything is edited/inserted/deleted into any table, its detail must be INSERTED in transaction log table

Upvotes: 0

Views: 185

Answers (1)

user330315
user330315

Reputation:

I think that during heavy concurrent execution, adding data (transaction log from all table) to single log table will bottleneck performance.

Don't assume. Test.
Especially when it comes to performance. Doing premature optimization is a bad thing.

Please also define "heavy usage". How many inserts per second to you expect?

So I thought of a solution, why not add the SQL for transaction log to a queue which will execute automatically when there is NO heavy pressure on database

Define "no heavy pressure"? How do you find out?

All in all I would recommend to simply insert the data and tune PostgreSQL so that it can cope with the load.

You could move the data to a separate hardd disk so that IO for the regular operations is not affected by this. In general insert speed is limited by IO, so get yourself a fast RAID 10 system.

You will probably also need to tune the checkpoint segments and WAL writer.

But if you are not talking about something like 1000 inserts per second, you'll probably don't have to do much to make this work (fast harddisk/RAID system assumed)

Upvotes: 1

Related Questions