There are endpoints in a simplified form on the server:

GET / users
POST / users - create a user
GET / users / {id}
DELETE / users / {id}

Server and client are on the same server. They have a common database for testing sqlite. Let's say I have a test for each endpoint. In each test, an http request is sent to the endpoint using the http client, as if it were a consumer (consumer), and the response is checked for correctness. Before the very beginning of the tests, an empty database is created and the migration is rolled. After finishing the tests, the database is deleted.

My problem is that before each test I need to create 7 users with fake data. And after the test, so that they are "deleted".

First, I did the database through transactions, the test starts and a transaction is started on the client, 7 users are created, in the test there is an http request for GET / users, but there is no transaction on the server and an empty database. Therefore, this option is eliminated.

If you do without a transaction, then shared database data is the same on the client and the server due to the fact, but I need only 7 users before each test so that previous manipulations with the database do not distort the data. Therefore, this option is also eliminated.

I tried the option where the database is deleted and migrated before each test. It works but for a very long time (it takes a minute to check 4 endpoints). Database in memory would speed up a lot here, but here is the same problem as with a transaction.

I just can't find a solution. What can you advise? I want to test all endpoints not through functional tests inside the code, but as if from the outside (as an interface is tested using selenium).

set only changes the value for the current session. It is lost when you disconnect. If you want to persist the change across sessions (and Postgres restarts) you need to use ALTER SYSTEM:

alter system set track_functions to 'all';
SELECT pg_reload_conf();

I have a situation that I am running into after upgrading my Postgres from 10.7 to 13.6. I have flyway scripts that date over several years that build up the history of the database. The changes to the schema shuffled around PKs, and while doing so it had to create some constraints.

V1__PLM.sql

create table artifacts_registry (
    artifact_id varchar(36) not null,
    artifact_name varchar(255) not null,
    ...);
alter table artifacts_registry 
    add constraint UK_t5rk0b5yybufi8pkkxato2e11 unique (artifact_name);

V17__PLM.sql

alter table artifacts_registry
    add column p_artifact_name varchar(255);

V19___PLM.sql

alter table artifacts_registry
    add constraint artifacts_registry_pkey primary key (artifact_name);

At this point I noticed that I had a duplicate index on a single column - artifacts_registry_pkey and uk_t5rk0b5yybufi8pkkxato2e11.

My flyway script tries to drop this extra constraint.

V78__PLM.sql

ALTER TABLE artifacts_registry 
    DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11;

and this fails on the new Postgres 13.6 version.

Migration V78__PLM.sql failed
-----------------------------
SQL State  : 2BP01
Error Code : 0
Message    : ERROR: cannot drop constraint uk_t5rk0b5yybufi8pkkxato2e11 on table artifacts_registry because other objects depend on it
  Detail: constraint fkhg85thdq13hf91f7h6mbd5dza on table artifacts_registry depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fk1afmjb153f2dgxs4roj2pzez9 on table artifacts_relation depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fkafh3xt7w1oyg7sz2r6c7jo8nk on table artifacts_relation depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fk89pg0m2cqgnoidk3uxuxex76x on table execution_artifacts depends on index uk_t5rk0b5yybufi8pkkxato2e11
  Hint: Use DROP ... CASCADE to drop the dependent objects too.
Location   : db/migration/schema/V78__.sql (/Users/dobrim1/dev/P-PLM/target/classes/db/migration/schema/V78__PLM.sql)
Line       : 1
Statement  : ALTER TABLE artifacts_registry DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11

Confusingly, this all used to pass on previous versions of Postgres and AWS Aurora 10.7, 11.x, and 12.x.

It tries to give me the hint to use DROP CASCADE. However, I don't really want to do this because those indexes are useful and referencing a valid relationship. The problem here is that Postgres has two identical unique constraints, but it is choosing uk_t5rk0b5yybufi8pkkxato2e11 as the "main" one, against which it is enforcing deletion constraints.

Question

How can I mark the p_artifact_name index as the main index, such that I can delete uk_t5rk0b5yybufi8pkkxato2e11?

Related info -

• These changes are already deployed in Production against AWS Aurora 13.6. Each time the containers start up they validate the migration history and don't have any issues. So the production server did not choose the main index in the same way as Postgres does locally.
• The issue arises locally when I spin up a Postgres 13.6 container and start my application, which then has to run the full migration history starting from V1__PLM.sql.
• Baseline and undo migrations are not an option due to paid nature.

I've PostgreSQL database table named worker_activity following structure

   Column      |              Type              | Collation | Nullable |                        
---------------+--------------------------------+-----------+----------+
 id            | bigint                         |           | not null | 
 workerName    | character varying(255)         |           | not null |
 workerId      | integer                        |           | not null |
 action        | text                           |           | not null |
 created_at    | timestamp(0) without time zone |           |          |
 updated_at    | timestamp(0) without time zone |           |          |

This table has almost 30 millions of rows and I have the query that creates weekly reports

select * from "worker_activity" 
  where "created_at" between $1 and $2 
  and ("action" = $3 or "action" = $4) order by "id" asc

So, that "plain" query executes nearly 3 minutes, it's rather slow, isn't it? How can I speed it up using all features PostgreSQL provides? Are window functions or some other aggregation method applicable in my case?

Additional info as members pointed out:

• Real row from the table

id| algorithmName|algorithmId |action |created_at|updated_at
3 | LiquidityMaker|     1     | {"step_1":{"action":"openOrderSell","orderSum":0.032,"orderPrice":2.3049,"counter":1},"step_2":{"action":"workTime","time":0.03714489936828613}}|2021-07-06 06:49:26|2021-07-06 06:49:26

• psql (PostgreSQL) 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
• Hardware KVM VPS, 32 Gb of RAM, 8 Core CPU, SSD Drive.

Here is the execution plan:

explain (analyze, buffers)
select * from "worker_activity"
where "created_at" between '2021-07-06 06:49:25' and '2021-07-07 00:00:00'
  and ("action" = 'Start Work' or "action" = 'End Work')
order by "id" asc;

 Sort  (cost=4138424.27..4138424.29 rows=9 width=903) (actual time=235525.506..235571.749 rows=0 loops=1)
   Sort Key: worker_activity_liquidity_maker_1.id
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=120287 read=3750619
   ->  Gather  (cost=1000.00..4138424.12 rows=9 width=903) (actual time=235525.494..235571.733 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=120287 read=3750619
         ->  Append  (cost=0.00..4137423.22 rows=9 width=903) (actual time=235521.443..235521.462 rows=0 loops=3)
               Buffers: shared hit=120287 read=3750619
               ->  Parallel Seq Scan on worker_activity_liquidity_maker_1  (cost=0.00..577947.97 rows=1 width=883) (actual time=30871.374..30871.375 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_2  (cost=0.00..505967.24 rows=1 width=935) (actual time=29927.739..29927.740 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_3  (cost=0.00..409953.82 rows=1 width=978) (actual time=24560.669..24560.670 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_4  (cost=0.00..517571.53 rows=1 width=912) (actual time=30845.683..30845.684 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_5  (cost=0.00..412771.36 rows=1 width=969) (actual time=24726.327..24726.328 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_6  (cost=0.00..505008.02 rows=1 width=950) (actual time=30233.641..30233.642 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_7  (cost=0.00..452042.03 rows=1 width=871) (actual time=26881.948..26881.949 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_8  (cost=0.00..126317.74 rows=1 width=757) (actual time=199.055..199.056 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at   Parallel Seq Scan on worker_activity_liquidity_maker_9  (cost=0.00..629843.52 rows=1 width=873) (actual time=37274.984..37274.985 rows=0 loops=3)
                     Filter: ((created_at >= '2021-07-06 06:49:25'::timestamp without time zone) AND (created_at 

I have geometries represented as strings (Oracle 18c):

with cte as (
select 'LINESTRING ( 1.0 2.0, 3 4)'                               as txt from dual union all
select 'LINESTRING M ( 1 2 3, 4 5 6.0)'                           as txt from dual union all
select 'LINESTRING ( 1 2, 3 4, 5 6)'                              as txt from dual union all
select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)'               as txt from dual union all
select 'LINESTRING M ( 1 2 3.0, 4 5 6)'                           as txt from dual union all
select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt from dual
)
select
    txt
from
    cte
TXT
LINESTRING ( 1.0 2.0, 3 4)

LINESTRING M ( 1 2 3, 4 5 6.0)

LINESTRING ( 1 2, 3 4, 5 6)

LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)

LINESTRING M ( 1 2 3.0, 4 5 6)

MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))

• Coordinates are separated by spaces (X Y M).
• Vertices are separated by commas.
• Groups/multi-parts are wrapped in brackets and separated by commas.

Using SQL, is there a way to remove the third coordinate (the "M" coordinate) from each vertex?

Result:

TXT                                                     
--------------------------------------------------------
LINESTRING ( 1.0 2.0, 3 4)
LINESTRING M ( 1 2, 4 5)
LINESTRING ( 1 2, 3 4, 5 6)
LINESTRING M ( 1 2, 4 5, 7 8.00)
LINESTRING M ( 1 2, 4 5)
MULTILINESTRING M (( 1 2, 4 5),( 7 8, 10 11))

It would be ok to round out the .0s. Or not.

Assuming data files are on and Log files are on E:

Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server.

Upon startup, SQL Server will realize that the .mdf files are present, but the log files are absent. Due to missing log files, the SQL server will be unable to do any of its undo/redo recovery steps and it will place the database as a suspect.

Note: I tried this and notice that database goes into suspect mode. The best practice here is to use the backups (full + tlog) to restore the database. In my situation I have the full backup from 12am but no tlog backups. If I restore from the 12am backup then I loose entire days data (assuming crash happened mid-day). I am trying to think how to proceed in such situation by utilizing the existing data file.

1. How to recover the database in this scenario?
2. Will it be correct to say that data loss will be only those transactions that were in the tlog file but not written to the disk?

A junction table isn't going to affect your performance here, especially with a process that only runs at a frequency of every hour.

NoSQL doesn't buy you any advantage here either, and IMO should only be used when there's a use case for it that can't be accomplished in a regular RDBMS.

And as far as why I think denormalization is unnecessary here: Data integrity is the most important thing in a database, IMO. Denormalizing typically leads to data redundancy that is at higher potential risk against integrity. It also leads to heavier tables which can have their own performance implications as well. I don't really see any reason for you to denormalize away a single simple junction table here. The extra work to maintain it should be trivial and not a performance bottleneck.

An example of where data integrity can come into question is when you redundantly store data such as a subscriber's phoneNumber multiple times, because you would have the same record multiple times (for each site they're subscribed to) in the denormalized table. When their phoneNumber changes, you need a transactionally consistent way to update all instances of their phoneNumber in the table, otherwise you lose data integrity.

Sure, in your simple example, the easy answer is to write an update statement by the subscriberId, and you'll usually be covered. (This is just one simple example, because your use case is pretty simple.) But now you're also updating many records instead of one record to change the phoneNumber which means more rows need to be located, loaded off disk, locked (which may result in lock escalation on the entire table - not sure if this happens in MariaDB), updated, and transactionally written back to disk. This is one example of performance implications of a denormalized table.

There's a lot of similar and overlapping terms these days (Data Lake, Data Swamp, Data Warehouse, etc) that I wouldn't get too hung up over, IMO.

Data Lakes are informal places to centralize different sources of data. They can be flexible and don't necessarily need to adhere to a fixed schema but can follow one.

Data Warehouses are more formally defined and unify those different sources of data into a common structure, such that it's easy to build consuming applications and reports off of.

So the answer to your question is it just depends on your use cases, how many different types of data and sources you need to consume, and if having a Data Lake as an intermediary step makes it easier to accomplish your use cases before applying the ETL (really the Transform part) processes to that data.

If all of your sources of data already follow a rather common schema, then usually you can just ETL straight into your Data Warehouse and skip the Lake altogether. But sometimes it's good to use a Data Lake to preserve the original data as it was extracted, in case some level of reconcilation and debugging is needed later on. It adds a layer of what the data looked like before you touched it to transform it into the Warehouse.

might seem a stupid question, but I'm pretty new to the db world. Is that a good practice to have two oracle schemas which have synonyms pointing to each other ?

• like SCHEMA_A owns a SYNONYM_1 pointing to the TABLE_1 in the SCHEMA_B
• and the SCHEMA_B owns a SYNONYM_2 pointing to the TABLE_2 in the SCHEMA_A

Apparently technically is possible, but is that a good practice ? Does not sound as a kind of circular reference if you compare this to the "Software modules" concept ?

Thanks in advance for your responses.

I’m not sure about the size of the table, but have you looked for nightly https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/aa337544(v=sql.105) as an option. You could run delete on source separately by batching.

DROP TABLE IF EXISTS [tempdb].[dbo].sourceTable
DROP TABLE IF EXISTS [tempdb].[dbo].staging_SourceTable
DROP TABLE IF EXISTS [tempdb].[dbo].targetTable
GO
--Your source table

CREATE TABLE [tempdb].[dbo].sourceTable(id int identity(1,1) Primary Key, fname char(10), lname char(10), createddate datetime2(0) default getdate())

GO

INSERT INTO [tempdb].[dbo].sourceTable(fName, lName) VALUES ('fName1','lName1')

INSERT INTO [tempdb].[dbo].sourceTable(fName, lName) VALUES ('fName2','lName2')

INSERT INTO [tempdb].[dbo].sourceTable(fName, lName) VALUES ('fName3','lName3')

GO
--Stage data before BCP out, so you can use this to delete from source

CREATE TABLE [tempdb].[dbo].staging_SourceTable(id int)

GO

INSERT INTO [tempdb].[dbo].staging_SourceTable

SELECT ID --Choose primary key or any unique column

FROM [tempdb].[dbo].sourceTable
--BCP out command, I’m using staging table in my query

EXEC master..xp_cmdshell

'bcp "SELECT s.id, s.fname, s.lname, s.createddate FROM [tempdb].[dbo].sourceTable s JOIN [tempdb].[dbo].staging_SourceTable stg on s.id = stg.id" queryout C:\temp\outputFile.bcp -S localhost -c -T -b 1000'
--Create schema on target instance

CREATE TABLE [tempdb].[dbo].targetTable(id int, fname char(10), lname char(10), createddate datetime2(0))

GO
--BCP in command

EXEC master..xp_cmdshell

'bcp [tempdb].dbo.targetTable in C:\temp\outputFile.bcp -S localhost -c -T -b 1000'
--At this point you are safe to delete from source

WHILE 1 = 1

BEGIN

SELECT TOP 1000 ID

INTO #temp

FROM tempdb.dbo.staging_SourceTable
IF @@ROWCOUNT = 0
    BREAK

DELETE s
FROM tempdb.dbo.sourceTable s
JOIN #temp t on s.id = t.id

DELETE stg
FROM tempdb.dbo.staging_SourceTable stg
JOIN #temp t on stg.id = t.id

DROP TABLE #temp

END

The average wait time for a specific event is probably going to be low unless you have timeouts frequently because of it. The DMV you have identified will let you work out the average wait times by dividing the time spent waiting on it (wait_time_ms) by the amount of waits (waiting_tasks_count).

Extended Events will definitely help you identify what exactly is slow https://www.sqlshack.com/using-sql-server-extended-events-to-monitor-query-performance/ and https://www.mssqltips.com/sqlservertip/5752/configuring-extended-events-to-find-locking-issues-in-sql-server/ are good places to start.

The query would be something like this:

select (wait_time_ms / waiting_tasks_count) / 1000 AS "avg time in seconds", (max_wait_time_ms) / 1000 AS "maxtime in seconds",
   * from sys.dm_db_wait_stats where wait_type like 'LCK%'