Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pBXeE-0001Rv-Pj for pgsql-general@arkaria.postgresql.org; Sat, 31 Dec 2022 08:55:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pBXeD-0007aT-9B for pgsql-general@arkaria.postgresql.org; Sat, 31 Dec 2022 08:55:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pBXeC-0007aK-Rm for pgsql-general@lists.postgresql.org; Sat, 31 Dec 2022 08:55:20 +0000 Received: from mail-qt1-x831.google.com ([2607:f8b0:4864:20::831]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pBXe6-0008R1-6j for pgsql-general@lists.postgresql.org; Sat, 31 Dec 2022 08:55:20 +0000 Received: by mail-qt1-x831.google.com with SMTP id h21so18717918qta.12 for ; Sat, 31 Dec 2022 00:55:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=IM0Nhas3OvXi1LGVREeoQqhOYcJIg9uuUcv6FFTsTEs=; b=Z/Fx9P1DuF2LJItcp6ppms9xKYk5dzMhGLaXoZlWByiiv80O8TK8ANf6jTFpilzXJd vtI3vLTR47iROjlaZ7iEt0tOq0Gj6sYjGvH3PU99r63bL6GgY534zorS6yaIEhMNT22w OcnQzC2W75QQdscLOW6i1adKv1EmRQaKFAmME6/uFQ2f+Sf0jJJPFKfvo0+zkOD1tphJ ni3Q5+a7zU8B+KPQooc63TZTyoi1xEbmlQQLHuiZd1hs4q6EjIeVeUHwMuhtcJ+wYWb6 3UPEuuzA+6amGE8LvJ5uc2IMtyXiZ0K461+tKuSoXWJocu4paLoqguWE38/vIenKvXDG 8iPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=IM0Nhas3OvXi1LGVREeoQqhOYcJIg9uuUcv6FFTsTEs=; b=w5PE+GP5mbyKie3rI5oZVDqiorE/l7UWYSZmHrGXN3DwKL6BCD2spEIh6VyhPjUK6V LAyb8G3Po9q/RdTAxNzuW6MmSaUy0uXr6LOQt9JT/M8Bq70rtONmO+k83Pnhv+cZxB3d WjoRRYFnI/cNmB+X39QNVUZJtPMaZVooI54YC1fblyUZCx/60BixMXdcas0QiH9m2/Nb 9a0s3+I6487YoUudMf9bnAPjuhu+i8Uy9E5wN2MCb37FamoyiaAqQpBE9ky6CWrS6MjL 6o42M8NCrefs/VzjDWu6qnUcV9IHFqG1CQvt6BZAZTP2uLfUXDlx4uMbQ4SQ+3+5+Ewj jIfw== X-Gm-Message-State: AFqh2kq01s0fnO1JvjI2rYxKbAnWPHjKHjrdDlk7Q8Pqn5CHl0I0HfOw Go+HCqS/GXeyPrfYzs0Qsb6dq3SL/uyebw== X-Google-Smtp-Source: AMrXdXvclTL4+Pkp/h7kbXh9v2Z/R6N8L04KMFhpQvaYU/SycyztxPOKpBrVivJwNoXW2AIpbobvVg== X-Received: by 2002:ac8:707:0:b0:3a9:6b96:f14b with SMTP id g7-20020ac80707000000b003a96b96f14bmr50607906qth.38.1672476912079; Sat, 31 Dec 2022 00:55:12 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id x16-20020ac84a10000000b003a68af60591sm14367949qtq.70.2022.12.31.00.55.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 31 Dec 2022 00:55:11 -0800 (PST) Content-Type: multipart/alternative; boundary="------------CO0esvDwzrsRA61G9wjd2qr7" Message-ID: <348ac840-800a-7402-c2ea-e64167a68e2d@gmail.com> Date: Sat, 31 Dec 2022 02:55:10 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Inserts create new records in reporting table Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <5a52b569-e54c-98f5-9c23-937186ad8477@machinemanagement.com> From: Ron In-Reply-To: <5a52b569-e54c-98f5-9c23-937186ad8477@machinemanagement.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------CO0esvDwzrsRA61G9wjd2qr7 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 200K *total* records, or 200K records per day/month/quarter/etc?  Because 200K records isn't that much on modern hardware.  Heck, it wasn't much 20 years ago on "Enterprise" hardware. And you don't mention the window for loading the data into "the" table, and then into the reporting table. A trigger will slow down each insert into "the" table, since each operation will do 2x (or more) work.  Only you know whether it's more important to get the data into "the" table ASAP and then then load into the reporting table at your leisure, or get it into both tables ASAP. No matter what you do, create as few indices as possible before the load phase.  Add the rest afterwards. Lastly, remember "locality of data".  That makes caches much more effective.  A single query on "the" table when it's clustered on an index tuned to support the reporting table will run Really Fast, whereas a query (or trigger) which bounces all around the table in each fetch and insert won't be nearly as fast. Really lastly: *test each method*. On 12/31/22 01:02, Bret Stern wrote: > Love the forum, > > I'm bringing 100k - 200k of AR transactions into a table from several > separate ERP companies. > > As I insert the records, does it make sense to run a trigger event to > build or insert data into > > a reporting table (to save generating a query to build the reporting table > later.) > > > For example, I bring in 120k records which contain invoice dates from the > beginning of time > > in the ERP system. (eg; records with dated transactions from 2010...2022) > > > Conceptual data > > [Location][InvoiceDate][Amount] > > Dallas        2012-01-09    7500.00 > Dallas        2012-11-19    1200.00 > Dallas        2012-04-02    500.00 > Phoenix       2012-01-03    1200.00 > Phoenix        2012-04-12    7500.00 > > Would it be possible, or would it make sense to create a trigger which > populated another > > table like below after each insert...or is is a resource killer..or just > unnecessary, and smarter to create a query > > that performs calculated on the fly columns for the dates of invoice? > > > [Location][InvoiceDate][Amount]    [Jan]            [Feb] [Mar]            > [Apr]          [May]             [Jun]      [Jul]         [Aug]           > [Sep]            [Oct]     [Nov] [Dec] > > Dallas         2012-01-09                   7500.00 > > Dallas 2012-11-19 1200.00 > > Dallas 2012-04-02 500.00 > > Phoenix    2012-01-03                    1200.00 > > Phoenix 2012-04-12 7500.00 > > > Bret > > > -- Born in Arizona, moved to Babylonia. --------------CO0esvDwzrsRA61G9wjd2qr7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 200K total records, or 200K records per day/month/quarter/etc?  Because 200K records isn't that much on modern hardware.  Heck, it wasn't much 20 years ago on "Enterprise" hardware.

And you don't mention the window for loading the data into "the" table, and then into the reporting table. 

A trigger will slow down each insert into "the" table, since each operation will do 2x (or more) work.  Only you know whether it's more important to get the data into "the" table ASAP and then then load into the reporting table at your leisure, or get it into both tables ASAP.

No matter what you do, create as few indices as possible before the load phase.  Add the rest afterwards.

Lastly, remember "locality of data".  That makes caches much more effective.  A single query on "the" table when it's clustered on an index tuned to support the reporting table will run Really Fast, whereas a query (or trigger) which bounces all around the table in each fetch and insert won't be nearly as fast.

Really lastly: test each method.

On 12/31/22 01:02, Bret Stern wrote:
Love the forum,

I'm bringing 100k - 200k of AR transactions into a table from several separate ERP companies.

As I insert the records, does it make sense to run a trigger event to build or insert data into

a reporting table (to save generating a query to build the reporting table later.)


For example, I bring in 120k records which contain invoice dates from the beginning of time

in the ERP system. (eg; records with dated transactions from 2010...2022)


Conceptual data

[Location][InvoiceDate][Amount]

Dallas        2012-01-09    7500.00
Dallas        2012-11-19    1200.00
Dallas        2012-04-02    500.00
Phoenix       2012-01-03    1200.00
Phoenix        2012-04-12    7500.00

Would it be possible, or would it make sense to create a trigger which populated another

table like below after each insert...or is is a resource killer..or just unnecessary, and smarter to create a query

that performs calculated on the fly columns for the dates of invoice?


[Location][InvoiceDate][Amount]    [Jan]            [Feb]     [Mar]            [Apr]          [May]             [Jun]      [Jul]         [Aug]           [Sep]            [Oct]     [Nov]         [Dec]

Dallas         2012-01-09                   7500.00

Dallas 2012-11-19 1200.00

Dallas 2012-04-02 500.00

Phoenix    2012-01-03                    1200.00

Phoenix 2012-04-12 7500.00


Bret




--
Born in Arizona, moved to Babylonia.
--------------CO0esvDwzrsRA61G9wjd2qr7--