Postgresql – Trying to understand how to use postgresql generate_series() for this operation

insertpostgresqlquery

First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence
This is the old question: Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?

But I will rephrase the question again:

Those are my initial tables

Users: id(int) PK, weeks_to_live(int)
Calendar: id(int) PK, user_id (int) (FK -> Users(id))*
CalendarField: id(int) PK, text(text), rating(int), calendar_id(int) (FK ->Calendar(id))*, week_number(int)

Point is, when I generate my calendar in my app, actually my Users, and Calendar tables get filled properly.
Upon a generation they will end up looking like this

users

id   weeks_to_live
1    3
2    2

calendar

id   user_id
1    1
2    2

That part is fine, but I need my CalendarField table to be autogenerated too, and thats where supposedly generate_series() should come into action, so they will end up looking like this

CalendarField

id    text     rating    calendar_id   week_number
1     ''       0         1             0
2     ''       0         1             1
3     ''       0         1             2
4     ''       0         2             0
5     ''       0         2             1

As you can observe:

id: is autoincremental
text: always defaults to "",
rating: always defaults to 0,
calendar_id (fk) : is the relation with each calendar, who has an amount of calendarFields row equaling to the number up to weeks_to_live in users
week_number: this number, should begin at 0, and keep incrementing while its < than weeks_to_live in the users table

Now my question is, how do I generate that last insert so my CalendarField table ends up filling with that info?. I suppose that with generate series, I wont need to create an enormous insert with == weeks_to_live values in case it gets huge. But I don't understand how to do it.

Or should I do a giant Insert then?

Best Answer

You need to join users and calendar and then cross join that with a generate_series().

insert into calendar_field (text, rating, calendar_id, week_number)
select '', 0, c.id, g.wn
from calendar c
  join users u on u.id = c.user_id
  cross join generate_series(0, u.weeks_to_live) as g(wn);

Online example

Related Solutions

Postgresql – generate_series for multiple record types in postgresql

I usually solve such problems by setting up a table for all the possible data points (here the pests and dates). This is easily achieved by a CROSS JOIN, see the WITH query below.

Then, as the finishing step, I just (outer) join the existing measurements, based on the pest ID and date - optionally giving a default for the missing values via COALESCE().

So, the whole query is:

WITH data_points AS (
    SELECT id, name, i::date
    FROM pests
    CROSS JOIN generate_series('2015-01-01'::date, '2015-01-05', '1 day') t(i)
) 
SELECT d.name, d.i, COALESCE(p.cnt, 0) 
FROM data_points AS d 
LEFT JOIN pest_counts AS p 
    ON d.id = p.pest_id 
    AND d.i = p.count_date;

Check it at work on SQLFiddle.

Note: when either the table(s) or the generated series are big, doing the CROSS JOIN inside a CTE might be a bad idea. (It has to materialize all the rows, regardless of there is data for a given day or not). In this case one should do the same in the FROM clause, as a parenthesized sub-join instead of the current reference to data_points. This way the planner has a better understanding about the rows affected and the possibilities for using indexes. I use the CTE in the example because it looks cleaner for the sake of the example.

Postgresql 9.6 best settings for large admin work (min_wal and max_wal)

I guess you only want to test if the restoration of the dump works and nothing more, which means you can do some unsafe configuration changes. Let's first start with your settings.

These one are a good call:

fsync = off
synchronous_commit = off
full_page_writes = off
wal_level = minimal
autovacuum = off

These three are only important if you are using replication, and because you already set wal_level to minimum, you are not using it, so they are not important:

wal_keep_segments = 0
archive_mode = off
max_wal_senders = 0

You have a lot of RAM which won't be used for anything, I would bump this one up even more:

maintenance_work_mem = 3GB

I would leave wal_buffers to it's default:

wal_buffers = -1

and bump shared_buffers (wall_buffers will be automatically calculated):

shared_buffers: 4GB

What you should try to concentrate is to have no, or as less checkpoints as you can during your restore. Checkpoints are controlled by max_wal_size and checkpoint_timeout. First of bump checkpoint_timeout to something like 20h, so that a timed checkpoint never happens while you restore:

checkpoint_timeout = 20h

Then you can set max_wal_size to a value as high as your disk space allows you. If your restored DB is 200GB and your disk 500GB, you should be safe to set max_wal_size to 100GB because Postgres can store up to two checkpoints of wals (which is 2xmax_wal_size):

max_wal_size = 100GB

min_wal_size won't matter that much in your case, but you can probably bump it to 10GB

min_wal_size = 10GB

I would also recommend that you use pg_restore with --jobs=NUM where NUM would probably be the number of CPU cores, but it also depends on the speed of your SSD, so you can play with this parameter.

Besides Postgres settings, I would also recommend that if possible you add an additional SATA drive (7200RPM will be fine) and symlink pg_wal directory to that SATA disk. That is the directory where Postgres saves WALs, and because they are written in append, SATA is fast enough for them. It will reduced the load on the SSD, but will also mean you will be able to bump max_wal_size even more (depending on the size of the SATA disk).

Finally don't forget to restore your settings to the sane values after the dump is restored.

Best Answer

Related Solutions

Postgresql – generate_series for multiple record types in postgresql

Postgresql 9.6 best settings for large admin work (min_wal and max_wal)

Related Question