Question about performance - Postgres 9.5

Also...

if something is changed inside the visits table (delete/insert/update), the
visits_logs table will be logging the change.

However, some joins between those three tables will become necessary in
some point, as visits and works tables are related somehow...

On Mon, Jun 13, 2016 at 8:37 AM, Patrick B <patrickbakerbr@gmail.com> wrote:

Hi guys,

In the db I'm working one, it will be three tables:

visits, work, others.

Everything the customer do, will be logged. All inserts/updates/deletes
will be logged.

Option 1: Each table would have its own log table.
visits_logs, work_logs, others_logs

Option 2: All the logs would be stored here...
log_table

Can you please guys tell me which option would be faster in your opinion,
and why?

Did you mean that, you will be auditing the activities happening on those 3
tables ? If yes, can you clarify on what you will be exactly logging ?

What will be the volume of transactions all the 3 tables will be receiving
over a business day ? if the volume is manageable, then one table for
logging all the actions across 3 tables would be good.

If you are auditing and size of the data is manageable then, even one table
would also be good. A separate audit table for each table would generally
be a good idea, which makes it easy for tracking activities.

Regards,
Venkata B N

Fujitsu Australia

On Wed, 17 Aug 2016, 1:36 p.m. Venkata B Nagothi, <nag1010@gmail.com> wrote:

On Mon, Jun 13, 2016 at 8:37 AM, Patrick B <patrickbakerbr@gmail.com>
wrote:

Hi guys,

In the db I'm working one, it will be three tables:

visits, work, others.

Everything the customer do, will be logged. All inserts/updates/deletes
will be logged.

Option 1: Each table would have its own log table.
visits_logs, work_logs, others_logs

Option 2: All the logs would be stored here...
log_table

Can you please guys tell me which option would be faster in your opinion,
and why?

Did you mean that, you will be auditing the activities happening on those
3 tables ? If yes, can you clarify on what you will be exactly logging ?

What will be the volume of transactions all the 3 tables will be receiving
over a business day ? if the volume is manageable, then one table for
logging all the actions across 3 tables would be good.

It will also depends on what you would be using the log entries for. What
kind of queries? Retention period? If you will query most often on date
range and also purge by date, then better log all in one table and
partition by date.

You can log old and new records in json format in one column that way you
don't need to worry about changing structure of underlying tables.

In the triggers which you use for auditing, you can transform rows to a
json document.
You can have columns for tableName, Action (insert/update/delete),
NewRecord (json), oldRecord (json), datetime

If you are auditing and size of the data is manageable then, even one
table would also be good. A separate audit table for each table would
generally be a good idea, which makes it easy for tracking activities.

Regards,
Venkata B N

Fujitsu Australia

--

--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350

Skype: sameer.ashnik | www.ashnik.com