Trigger - Add to a date using a subquery

You can easily use a variable when doing something like this:

menu_date := menu_date + interval '1' year * num_years

Where num_years holds the number of years that should be added.

You get the error because date 'manu_date' is an incorrect date literal because the string (!) 'manu_dat' is not a valid date. The expression date 'xxxx' requires a date definition in ISO format e.g. date '2013-09-27'

Regarding your trigger:

The statement update extinguisher set life_date = new_date; will update all rows in the table extinguisher. I'm pretty sure that is not what you want. You are missing a where condition for the update.

You also cannot run an update on the table on which the trigger is fired. You simply assign the value to the new record.

I although I absolutely don't understand what you intend with your trigger, I think what you want is much simpler:

CREATE OR REPLACE FUNCTION ext_life_span()
  RETURNS trigger AS
$BODY$
DECLARE
    l_lifespan integer;
BEGIN

  -- note that this can fail, if type_designation is not unique!!!
  SELECT lifespan
    into l_lifespan
  FROM extinguisher_type
  WHERE type_designation = new.type_designation_extinguisher_type;

  new.life_date := new.manufacturing_date + interval '1' year * l_lifespan;

  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Edit: depending on your data volumn, you could also get rid of the trigger and the life_date column alltogether and simply calculate that value "on-the-fly" using a view:

select ex.extinguisher_id,
       ex.manufacturing_date,
       ex.manufacturing_date + interval '1' year * ed.life_span as life_date
from extinguisher ex
  join extinguisher_designation ed
       -- I'm not sure I understand your data model correctly.
       -- you need to setup a proper FK here e.g. by using the generated ID
    on ed.extinguihser_type = ex.extinguihser_type

That way the value will always be up-to-date and you never need to worry about triggers.

In a relational database, values that can be derived from existing values should not be stored.


MySQL : How to fill in empty data with custom text

You must convert your values list to rowset.
For example:
SELECT
   city,
   area.region,
   area.postcode
FROM ( SELECT 'Jakarta' city UNION ALL
       SELECT 'Jabodetabek' ) cities
LEFT JOIN area USING (city)

This query will give NULL for the row for which there is no city value in area table. If you need some custom string literal then use COALESCE() function, i.e. not SELECT city, ... but SELECT COALESCE(city, 'Empty') city, ....

Depends on MySQL version you may use another methods: ROW() function, JSON_TABLE() function, etc.


User is not showing for abc database under security folder but when we query from sys.databases it showing as a db owner

The database owner displayed in the files section of the database properties is not displayed in the Security branch of the database. This is by design.

Explanation

Database Owner

The database owner in the file setting of the database properties is the owner of the database and does not have to be explicitly listed in the security tab. This is normally the SQL Login that created the database.

If a Windows Account (e.g. DOMAIN\ACCOUNT) was logged in and using SSMS to create the database, then this will be the database owner.
If sa or another SQL Login was logged in and using SSMS to create the database, then this will be the database owner.

db_owner Role

The db_owner role can be assigned to individual database users. These can be Windows Accounts or SQL Server Logins.

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and SQL Data Warehouse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)

Reference: Database-Level Roles (Microsoft Docs)

These are similar to the database owner in that they have the same permissions, but are explicitly defined in the database. That is why they are displayed in the security branch of the database.

Solution

To change the database owner ...

Assign new database owner

To remove a Database Owner (not the db_owner role) you have to assign a new database owner. This can be achieved using the mentioned sp_changedbowner stored procedure (deprecated) or the ALTER AUTHORIZATION... syntax.

Reference: ALTER AUTHORIZATION (Transact-SQL) (Microsoft Docs)

ALTER AUTHORIZATION ON DATABASE::<DB_name> TO <new_owner>;

a. replace <DB_name> with the name of the database
b. replace <new_owner> with a Windows Account (e.g. DOMAIN\ACCOUNT) or a SQL Login (e.g. sa).
sp_chnagedbowner '<new_owner>'
b. replace <new_owner> with a Windows Account (e.g. DOMAIN\ACCOUNT) or a SQL Login (sa).

Remove old SQL Login

Now that the database has a new database owner, you can drop the SQL Login.

DROP LOGIN <old_account>

That's it. You're finished.


Restrict sysdba or system administrator from reading package in oracle 11g

I think the only solution to this case is wrap the PL/SQL code.

The WRAP Utility and the DBMS_DDL Package


Trigger to prevent update when Flag value is 1

CREATE TRIGGER dbo_Tmp_trg_AU
ON dbo.Tmp
AFTER UPDATE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    IF EXISTS
    (
        SELECT NULL
        FROM Deleted AS Del
        WHERE
            -- Originally, flag was 1
            Del.Flag = 1
            AND NOT EXISTS
            (
                -- Column(s) aside from Flag have changed
                SELECT
                    Del.[Type],
                    Del.TranDate
                INTERSECT
                SELECT
                    Ins.[Type],
                    Ins.TranDate
                FROM Inserted AS Ins
                WHERE
                    -- Same Id
                    Ins.Id = Del.Id
            )
    )
    BEGIN
        RAISERROR ('You can not update when Flag value is 1', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

This assumes the Id column cannot be updated (e.g. it is an IDENTITY column).


Combine multiple row's records in one row with MS Access Query?

Try the below query will solve your problem:

SELECT ID, STUFF(
     (SELECT ', ' + CAST(Item AS VARCHAR(20)) [text]
     FROM TBL12
     WHERE ID = t.ID
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') Items
FROM TBL12 t
GROUP BY ID

Since you add many tags in the post, I solved in MSSQL using STUFF.
Working fiddle: http://sqlfiddle.com/#!3/d805d/4


Why does everyone want me to create an alert for severity 025?

Severity 25 is a 'catch-all' fatal system error.

  A severity 25 error is a fatal system error. I have heard that
  severity 25 is more or less a catch-all for miscellaneous fatal
  errors. I have only seen this error when related to failed upgrades:
  something prevents one of the upgrade scripts from running, and a
  severity 25 error is thrown

http://sqlperformance.com/2015/04/sql-performance/dealing-with-high-severity-errors-in-sql-server


Sending SQL Server query as CSV via e-mail

Use database mail to send the email as that supports attachments and auth on SMTP.


Real time preview of SQL queries generated by applications

Profiler Trace has been deprecated but you can still use it. Additionally, there are still a few ways to see what's going on:

sp_WhoIsActive from Adam Machanic ... this is an excellent tool to use if you want to know what's going on right now
because I don't have sp_WhoIsActive created on all instances right now, I also use a less elegant script that I put together here http://gist.github.com/swasheck/11379471 that uses a combination of DMVs that may be of interest to you (also for right now).

Depending on your SQL Server version, you can use the system_health Extended Event session.

Here's a way to query system_health in SQL Server 2008:

        SELECT
            td.r.value('@name','sysname') event_name,
            td.r.value('@timestamp','datetime2(7)') event_timestamp,
            td.r.value('(data[@name="wait_type"]/text)[1]','sysname') wait_type,
            td.r.value('(data[@name="duration"]/value)[1]','bigint') wait_duration,
            td.r.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text,
            td.r.query('.') event_data
        into #xe
        FROM (
                    SELECT
                    CAST(target_data AS XML) target_data
                    FROM sys.dm_xe_sessions s
                    JOIN sys.dm_xe_session_targets t
                        ON s.address = t.event_session_address

                    WHERE s.name = N'system_health'
                        and target_name = 'ring_buffer'
                    ) base
        CROSS APPLY target_data.nodes('/RingBufferTarget/*') td(r);
        GO

        select
            event_name,
            dateadd(HOUR,-5,event_timestamp) event_timestamp,
            wait_type,
            wait_duration,
            sql_text,
            event_data
            from #xe
            order by event_timestamp desc
        GO
        DROP TABLE #xe;

SQL Server 2012 offers more information in it's system_health event session. I have a set of queries here that will parse most of the the constituent components.

Depending on what you're seeking, though, the information may not be contained in system_health unless it's waited longer than 15 seconds for a latch or other resource, it's waited for longer than 30 seconds on a lock, or if it's been waiting for something outside of the SQL Server API for longer than 5 seconds (a preemptive wait).


Actually in order to be blocking a session only has to hold a lock to a resource something else wants.  It does not actually have to be doing anything.  For example if you run the following

BEGIN TRANSACTION
    UPDATE MyTable SET Col1 = 'ABC'

And don't run a COMMIT then  you are going to leave a transaction open.  The session is not doing anything and in fact it will not show up in sys.dm_exec_requests.  However the locks are going to continue to be held and continue to block other sessions.  You can find the information on open transactions in sys.dm_tran_session_transactions.

Next point is the command you are seeing.  That is just the last batch of the session.  If you open a transaction and run several batches through it you may very well be holding locks from earlier commands that are not going to show up.

I wrote about both of these things in quite a bit more detail in the following links:

Transactions: Who What and Where

Using sys.dm_exec_sql_text to figure out blocking is sometimes flawed.

Also the first link will have information on how to find who is running/ran the offending transaction.  I frequently find that if the login is a shared one, or a SQL ID I can track the individual down by using the information in the host_name column of sys.dm_exec_sessions.


Insert into linked server fails with "The transaction manager has disabled its support for remote/network transactions"

Try enabling the DTC on the sq202 box, since that is the server initiating the distributed transaction. You will also need to have Enable Promotion of Distributed Transactions for RPC option set to True as well for the sq009 linked server located on the sq202 box; when this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.

Helpful information on T-SQL Distributed Transactions and the requirements:
https://technet.microsoft.com/en-us/library/ms191156(v=sql.105).aspx

Linked Server Options: https://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx


How to run EM of Oracle 11g, if i haven't emctl, netmgr, dbca, OracleDBConsole?

Nothing wrong with it, you installed 11g Express Edition, which is limited and does not include most features.

You can download other editions from here:

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html


Converting Python's date format to a MySQL

I found a solution for this.

As you can see the time format is;

2012-08-07T09:00:00

I could easily strip the "t" but that wouldn't work and could(?) give me some trouble. I found out that the lib I was using (feedparser) could also return me a Python time object, looking like this:

time.struct_time(tm_year=2012, tm_mon=8, tm_mday=7, tm_hour=19, tm_min=0, tm_sec=22, tm_wday=1, tm_yday=220, tm_isdst=0)

Basicly.. a time.struct_time type.
With an example, you can see what I have done (examples made with iPython)

In [83]: date
Out[83]: time.struct_time(tm_year=2012, tm_mon=8, tm_mday=7, tm_hour=15,tm_min=51, tm_sec=19, tm_wday=1, tm_yday=220, tm_isdst=0)
In [84]: date = datetime.datetime(*date[:-3]).strftime("%Y-%m-%d %H:%M:%S")

In [85]: date
Out[85]: '2012-08-07 15:51:19'

Voila!


Optimization of complicated sums and counts in related tables

Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.

The following query may help you understand this better:

SELECT p.customer_id
    , c.name
    , sum(t.points)
FROM products p
    INNER JOIN types t ON p.type_id = t.id
    INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
    , c.name;

Essentially, this should show you a list of customer_id, category name, and the total points each customer has in each category.  It accomplishes this through the use of an aggregate function (SUM) along with the GROUP BY clause.

(please take a look at my answer here for information about naming your ID columns)


Please explain the difference between row and page compression

This is a good reference and explanation for row v page (no pun intended) compression:
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/how-sql-server-data-compression/

In short though, page compression encompasses the algorithms contained with row level compression and then also covers Prefix compression and Dictionary compression.  Prefix and dictionary compression finds patterns in the data and replaces them with smaller values.  During decompression it will do the reverse and restore the data to its original values.

Row level makes easier compression decisions, like reducing metadata, and removing unused space, like the letter 'Y' stored in a CHAR(1000).

The article referenced explains this in much greater detail.


What is the best smallest data type size for 19 digit number?

For sure you don't want to use a varchar: you are trying to store a number, so storing it into a string is a non sense. Plus, regardless of what you are going to do it later, varchar is the field that allows you the less flexibility: really no point in using it.

Your want to avoid timestamp, too, as it is deprecated and anyway it's intended for a different use (it's just a synonym for rowversion)

Your choice is then between bigint and datetime2(5), but given your requested precision of 5 they will both use 8 bytes, so no clear winner here. The only edge that datetime2 may have is that being a datetime it's better handled by datetime functions. Bigint, instead, will allow you to represent negative years (in case you need it).


How to validate data before loading it to DB

Assuming that the RDBMS being used is Microsoft SQL Server (and version 2008 or newer), then Table-Valued Parameters (TVPs) are a great fit for this exact scenario. The idea is that you will read the file line by line in your .NET code but stream the data either all at once, or if that is too much for one transaction, broken up into batches, into SQL Server using a TVP. The TVP is essentially a table variable that is an input parameter to a stored procedure so this will be a set-based operation (as far as SQL Server is concerned), not row-by-row. Technically a stored procedure is not required as a TVP can be sent as a parameter to an ad hoc query, but working with a stored procedure is just a better approach anyway.

There are two main patterns for using a TVP while reading from a file (and each of them relies upon passing in a method that returns IEnumerable<SqlDataRecord> rather than a DataTable):

Execute "import" Stored Procedure, that in turn initiates the file open and reading, all rows are read (and can be validated at this point) and streamed into the Stored Procedure. In this approach, the Stored Procedure is executed only once, and all rows are sent in as a single set. This is the easier approach, but for larger data sets (i.e. millions of rows) it might not perform the best if the operation is to merge the data directly into a live table rather than simply loading into a staging table. The memory required for this approach is the size of 1 record.
Create a variable for int _BatchSize, open the file, and either:

Create a collection to hold the batch of records

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Store valid entry in the collection

Execute the Stored Procedure at the end of each loop, streaming in the collection.
The memory required for this approach is the size of 1 record * _BatchSize.
The benefit is that the transaction in the DB does not depend on any disk I/O latency or business logic latency.

Loop to execute the Stored Procedure until no more lines to read from the file

Execute the Stored Procedure

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Stream record into SQL Server

The memory required for this approach is the size of 1 record.
The downside is that the transaction in the DB depends on disk I/O latency and/or business logic latency, hence could be open longer, hence more potential for blocking.

I have a full example of pattern #1 over on StackOverflow in the following answer: How can I insert 10 million records in the shortest time possible?

For pattern #2.1 (a highly scalable approach), I have a partial example below (it is too late to finish so I will post the C# portion in the morning)...

Required database objects (using a contrived structure):

FIRST, you need a User-Defined Table Type.
Please note the use of UNIQUE, DEFAULT, and CHECK constraints to enforce data integrity before the records even hit SQL Server.
The UNIQUE Constraint is also how you create an index on a Table Variable :)

CREATE TYPE [ImportStructure] AS TABLE
(
    BatchRecordID INT IDENTITY(1, 1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    SKU VARCHAR(50) NOT NULL UNIQUE,
    LaunchDate DATETIME NULL,
    Quantity INT NOT NULL DEFAULT (0),
    CHECK ([Quantity] >= 0)
);
GO

SECOND, use the UDTT as an input param to an import stored procedure.
Hence "Tabled-Valued Parameter" (TVP).

CREATE PROCEDURE dbo.ImportData (
   @CustomerID     INT,
   @ImportTable    dbo.ImportStructure READONLY
)
AS
SET NOCOUNT ON;

UPDATE prod
SET    prod.[Name] = imp.[Name],
       prod.[LaunchDate] = imp.[LaunchDate],
       prod.[Quantity] = imp.[Quantity]
FROM   [Inventory].[Products] prod
INNER JOIN @ImportTable imp
        ON imp.[SKU] = prod.[SKU]
WHERE  prod.CustomerID = @CustomerID;

INSERT INTO [Inventory].[Products] ([CustomerID], [SKU], [Name], [LaunchDate], [Quantity])
    SELECT  @CustomerID, [SKU], [Name], [LaunchDate], [Quantity]
    FROM    @ImportTable imp
    WHERE   NOT EXISTS (SELECT prod.[SKU]
                        FROM   [Inventory].[Products] prod
                        WHERE  prod.[SKU] = imp.[SKU]
                       );
GO

App Code:

First we will define the class that will be used to store the batch of records:

using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private class ImportBatch
{
  string Name;
  string SKU;
  DateTime LaunchDate;
  int Quantity;
}

Next we define the method used to stream the data from the collection into SQL Server. Please note:

The SqlMetaData entry for BatchRecordID is defined even though it is an IDENTITY field. However, it is defined in such a way as to indicate that the value is server-generated.
The yield return returns the record but then control comes right back to the next line (which just goes back to the top of the loop).

private static IEnumerable<SqlDataRecord> SendImportBatch(List<ImportBatch> RecordsToSend)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("BatchRecordID", SqlDbType.Int, true, false,
                      SortOrder.Unspecified, -1),
      new SqlMetaData("Name", SqlDbType.NVarChar, 200),
      new SqlMetaData("SKU", SqlDbType.VarChar, 50),
      new SqlMetaData("LaunchDate", SqlDbType.DateTime),
      new SqlMetaData("Quantity", SqlDbType.Int)
   };

   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   // Stream the collection into SQL Server without first
   // copying it into a DataTable.
   foreach (ImportBatch _RecordToSend in RecordsToSend)
   {
      // we don't set field 0 as that is the IDENTITY field
      _DataRecord.SetString(1, _RecordToSend.Name);
      _DataRecord.SetString(2, _RecordToSend.SKU);
      _DataRecord.SetDateTime(3, _RecordToSend.LaunchDate);
      _DataRecord.SetInt32(4, _RecordToSend.Quantity);

      yield return _DataRecord;
   }
}

Finally, we define the overall import processing operation. It opens the Connection to SQL Server and the File, then it cycles through the file reading and validating the _BatchSize number of records for each cycle. The stored procedure parameters are only defined once as they do not change: the CustomerID value doesn't change, and the TVP parameter value is merely a reference to a method -- SendImportBatch -- that only gets invoked when the stored procedure is executed via ExecuteNonQuery. The input parameter to the method passed in as the TVP value is a reference type so it should always reflect the current value of that variable / object.

public static void ProcessImport(int CustomerID)
{
   int _BatchSize = GetBatchSize();
   string _ImportFilePath = GetImportFileForCustomer(CustomerID);

   List<ImportBatch> _CurrentBatch = new List<ImportBatch>();
   ImportBatch _CurrentRecord;

   SqlConnection _Connection = new SqlConnection("{connection string}");
   SqlCommand _Command = new SqlCommand("ImportData", _Connection);
   _Command.CommandType = CommandType.StoredProcedure;

   // Parameters do not require leading "@" when using CommandType.StoredProcedure
   SqlParameter _ParamCustomerID = new SqlParameter("CustomerID", SqlDbType.Int);
   _ParamCustomerID.Value = CustomerID;
   _Command.Parameters.Add(_ParamCustomerID);

   SqlParameter _ParamImportTbl = new SqlParameter("ImportTable", SqlDbType.Structured);
   // TypeName is not needed when using CommandType.StoredProcedure
   //_ParamImportTbl.TypeName = "dbo.ImportStructure";
   // Parameter value is method that returns streamed data (IEnumerable)
   _ParamImportTbl.Value = SendImportBatch(_CurrentBatch);
   _Command.Parameters.Add(_ParamImportTbl);

   StreamReader _FileReader = null;

   try
   {
      int _RecordCount;
      string[] _InputLine = new string[4];

      _Connection.Open();

      _FileReader = new StreamReader(_ImportFilePath);

       // process the file
       while (!_FileReader.EndOfStream)
       {
          _RecordCount = 1;

          // process a batch
          while (_RecordCount <= _BatchSize
                  && !_FileReader.EndOfStream)
          {
             _CurrentRecord = new ImportBatch();

             _InputLine = _FileReader.ReadLine().Split(new char[]{','});

             _CurrentRecord.Name = _InputLine[0];
             _CurrentRecord.SKU = _InputLine[1];
             _CurrentRecord.LaunchDate = DateTime.Parse(_InputLine[2]);
             _CurrentRecord.Quantity = Int32.Parse(_InputLine[3]);

             // Do validations, transformations, etc
             if (record is not valid)
             {
                _CurrentRecord = null;
                continue; // skip to next line in the file
             }

             _CurrentBatch.Add(_CurrentRecord);
             _RecordCount++; // only increment for valid records
          }

          _Command.ExecuteNonQuery(); // send batch to SQL Server

          _CurrentBatch.Clear();
       }
   }
   finally
   {
      _FileReader.Close();

      _Connection.Close();
   }

   return;
}

UPDATE:

Since it might not be apparently obvious to everyone reading the code, I should point out, especially with regards to comments on the Question suggesting to use a staging table or even staging database, that the method I outlined above is really just a variation of that approach. How so? Well, the table variable that is the TVP is the staging table. And in fact, as opposed to a typical staging table which is a permanent table, being temporary and not shared means that you do not need to worry about:

cleaning up the data after the process is done
separating multiple concurrent import processes

If the READONLY nature of the TVP prohibits some validation and/or transformation that is needed before being merged into the destination table, then the data in the TVP can be easily transferred to a local temporary table at the beginning of the Stored Procedure.


Group by unique items in an array column and count records that contain each item

You just need to unnest the arrays into a long form (which I do by joining against the "unnest" function call), and then re-aggregate them.

select item, count(distinct id) from items, unnest(items.items) as item group by item;

If you have to do this on a regular basis, you might want to rethink how you store the data in the first place.


Set the database to simple recovery. This way the transaction log is only used during active transactions. Once transaction is committed, the active portion of the log is release and can be reused, thus minimizing log growth. Long running transactions with lot of DML will still cause the log to grow.

ALTER DATABASE DBNAME SET RECOVERY SIMPLE;


Hive metrastore Database in Postgresql External

For me it looks like an issue with the VM and/or network and not Postgres, if it is true that nothing else has changed, but only the "dedication" to another machine.

We had a bad VM configuration - my VM was on the same rack as our Scientists run their stuff. So each time they did anything - all resources went there.

Look at your VM, how much RAM and Processing power does it really have?
If you can afford it - give your your postgres more RAM and config it to cache most of the DB (or all). And if you can afford it - buy a dedicated server for Postgres. You'll have one for below $1k (with couple of SSDs and 32GB RAM).

For the rest: listen to a_horse_with_no_name, and give us additional info (and hell, if you use pg91 - upgrade to pg96, if you use anything below pg96 - upgrade!)

-- should be a comment, but my reputation is not enough for it.


SSMS Permission Denied, Login with Windows Authentication

You are just a member of BUILTIN/Users when connecting to SQL Server, your USER\Administrator is not a member of sysadmin role, so you have no server permissions. Try to execute

select *
from sys.fn_my_permissions(null, 'server');

and all you see will be CONNECT SQL + VIEW ANY DATABASE.
Of course you cannot use DAC, you are NOT sysadmin to be able to do it.

What you should do instead is launch your server in single user mode, and as you are local admin, you'll be able to log in as sysadmin.
After that you should add yourself to sysadmin role.

Here are the steps to perform: Connect to SQL Server When System Administrators Are Locked Out


Why GRANT UPDATE on SEQUENCE in PostgreSQL

The documentation says:

nextval ( regclass ) → bigint
[...]
This function requires USAGE or UPDATE privilege on the sequence.

setval ( regclass, bigint [, boolean ] ) → bigint
[...]
This function requires UPDATE privilege on the sequence.


MySQL restore dump takes too long to complete, how DBAs deal/replicate large databases?

Large databases should be backed up physically (i.e by copying the physical database files) instead of  logically (i.e by exporting in SQL or something similar). If you use Oracle MySQL Community Edition, you should use xtrabackup. If you use MariaDB there is a integrated feature in the last releases: https://mariadb.com/kb/en/mariabackup/.


Selecting only when specific values exist in another select query

The easy answer would be to combine both queries, directly, using subqueries (IN). So, assuming the column on shipments is also called id (substitute it with the real name, if different):

SELECT *
FROM shipments
WHERE id IN ( SELECT DISTINCT id
              FROM customers
              WHERE country = 'Neverland')

It may be nice to learn a bit about JOINs, while those may seem more complicated at first, they may have in some cases a better query plan or logically less complex once you learn about those:

SELECT *
FROM shipments
JOIN customers
ON shipments.id = customers.id
WHERE shipments.country = 'Neverland'

(again, you may need to adapt it to the particular column names, and cardinality relation- show no duplicate shipments, etc.).


When evaluating clustered solutions you need to think about the read/write profile of your application and match that against available solutions.

For a heavy read based profile of an application, you would be able to scale up reads by having multiple MySQL replicas off of your MySQL master-master replication setup.

For write heavy profile, you are implementing some of the obvious choices in master-master replication.

When choosing between clustered MySQL options there are two technology options: NDB based Oracle MySQL Cluster and Galera based options from Percona (XtraDB Cluster) and MariaDB (MariaDB Cluster), and Galera Cluster for MySQL.  They are architected differently and have different advantages and disadvantages.

A good webinar on the distinction is here:

http://severalnines.com/blog/galera-cluster-mysql-vs-mysql-ndb-cluster-high-level-comparison-webinar-replay-slides

The formula mentioned is primarily used in measuring the projected per node requirements for a NDB cluster.  Originally MySQL Cluster, previously NDB Cluster, was a memory only database, which required all of the data to reside in the memory of available nodes.  If all your data was required to stay in memory, then if you didn't enough RAM to hold all your data, you are under provisioned in terms of hardware.

Nowadays, each node in MySQL cluster, namely a data node, is a MySQL instance.  Even though you should keep your data in memory for optimal performance, it is not required.  So, basically if you don't "obey" the formulas, you will end up dropping down to disk and would have to deal with all the IO performance of the particular hardware available to your nodes.


MySQL "Limit" clause grabbing more results than requested

This is happening because the LIMIT clause is in a subquery.

To fix it, move it to the end:

SELECT A.username, A.points
FROM users A
INNER JOIN (
   SELECT points
   FROM users
   WHERE points <> 0
   ORDER BY points DESC
)B
USING (points)
LIMIT $start, $limit

I suspect you'll also want to move the ORDER BY.


Transaction Log Backups Serial or Parallel?

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel?

I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time.

What could be possible drawbacks with running in parallel

Suppose you have 50 databases and you schedule transaction log backup of all the databases and they all start running in parallel this is definitely going to utilize lot of I/O. And if disk on which it is backing up files happen to have other data files you would see slowness. I have seen backup becoming slow when a poor query requesting lot of I/O runs along with backup job.
Again suppose you have 50 database would it not be difficult to manage 50 jobs in SQL Server agent and what would be condition if you have 100-200 databases I would just not like it when you open the SQL Server agent and see lots of job, just keep it simple. I am sure the same case would be with you.

  The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes).

Transaction log backups are mostly small and if you have a busy database producing lot of log records you might need to change the backup frequency. Mostly I have seen transaction log backup completing fine when frequency is 15 mins. I don't think should be matter of concern for you.

  Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case

I would say just don't worry about it. Transaction log backups just cannot fail unless you made some mistake. The mistakes can be

The owner running the job is removed from AD
Someone changed the recovery model of database.
Insufficient disk space

Apart from above I have not seen any reason for transaction log backup to fail. Its very robust you can rely on it.


Are problems with parallelism more common today? What issues can arise from parallelism?

I wrote about six potential issues with parallelism here:

https://sqlblog.org/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism

That was three years ago, and I'm sure other equally dangerous bugs have come up since I wrote that. I haven't gone through and validated any that have been reported as fixed, but the workaround is almost always to use MAXDOP 1.

And there's another one that is still an issue in SQL Server 2012, and that hasn't been fixed in CU1:

https://stackoverflow.com/questions/9523213/possible-bug-with-range-option-of-window-aggregates-and-parallel-plans-in-sql-se
http://connect.microsoft.com/SQLServer/feedback/details/728932/parallelism-bug-with-sum-over-and-range

The workaround in that case is, again, to use MAXDOP 1.

There are other issues that can be blamed on parallelism that aren't really parallelism's fault. For example if your statistics are way off you can observe behavior where parallelism is employed but a single thread is doing all the work. This is the case where people sound the alarms because they're seeing CXPACKET waits and they think that parallelism is causing the problem, when in fact it is the victim. The knee-jerk reaction in those cases is typically to employ MAXDOP instead of investigating further and resolving the statistics issue or other root cause.

It's not clear what you mean by "too much parallelism" or whether your deadlocks and other issues are in fact related in any way to parallelism. In general I agree that parallelism is a positive thing but it is important to be aware of the potential issues above and also to be able to discern symptom from cause.


Postgis distance filter does not work as expected

Fixed by converting distance to numeric and rounding:

round(ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756))::numeric, 2)

I've found it here http://postgis.net/docs/manual-1.4/ST_Distance_Sphere.html, under "Examples".


Upgrade postgres without messing with ports?

Given step 9 in the link you provided:

  9.Run pg_upgrade ... Obviously, no one should be accessing the clusters during the upgrade. pg_upgrade  defaults to running servers
  on port 50432 (sic) to avoid unintended client connections.  You can
  use the same port numbers for both clusters because the old and new
  clusters will not be running at the same time. ...

It looks like it is just an available option.


AWS RDS (PostgreSQL) - Master user lost all privileges after upgrading from v9.6 to v10.6-R1

So running these two statements resolved my issue (being unable to access anything in the public schema):

master_prod=> ALTER SCHEMA public OWNER to freetiersuperuser;
ALTER SCHEMA
master_prod=> GRANT ALL ON SCHEMA public TO freetiersuperuser;
GRANT

However my rds_superuser role's attributes haven't changed, and I would definitely like to understand if this is normal (I don't think it is):

     Role name     |                         Attributes                         |                          Member of
-------------------+------------------------------------------------------------+-------------------------------------------------------------
 freetiersuperuser | Create role, Create DB                                    +| {rds_superuser}
                   | Password valid until infinity                              |
 rds_iam           | Cannot login                                               | {}
 rds_password      | Cannot login                                               | {}
 rds_replication   | Cannot login                                               | {}
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until infinity                              |
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}


Benjamin.
If I understood you correctly, you want something like this:

SELECT TR1.Title AS SourceTitle,
       TR2.Title AS DestinationTitle
FROM [Result Map] AS RM
INNER JOIN Table_Result TR1 ON RM.Source_Id=TR1.Id
INNER JOIN Table_Result TR2 ON tr2.Id=RM.destination_id;

The query will return

SourceTitle DestinationTitle
ABC         DEF


Create nonclustered index on uniqueidentifier type

According to your sample query, you must have two separate NonClustered Indexes on Session_id column for both tables. Further it must contain columns that are being used by SELECT clause to avoid bookmark lookups.

Fill factor for indexes on uniqueidentifier: If only key column (like in your case) is uniqueidentifier then you can fill all the page (Fill Factor = 100), as there are no chances of value updates for this column.

But drawback for index creation on uniqueidentifier column is that GUID is a wide column (16 bytes to be specific) and contains a unique combination of 33 uppercase and numeric characters and are randomly generated by SQL Server hence huge chances of page split and index fragmentation. Using NEWSEQUENTIALID()  (SQL SERVER 2008 and above) to insert values instead of NEWID() can resolve this problem.


Is there a way to speed up JOIN ON LIKE?

The initial problem is the left join.  That restricts it to using things as the driving table, but once you do that there is no way to use the indexes, as pg_trgm and text_pattern_ops offer ways to index for texts matching a given pattern, not for patterns matching a given text.

In order to use an index on the left join, you would have to first do a nested loop with match_group_rules driving, memorize all the rows of things retrieved, and then go back and make up NULL rows for the things not found.  There is no inherent reason that I can see that PostgreSQL wouldn't be able to do this complex operation, it just isn't implemented.  You can do it yourself with a CTE and a UNION ALL:

with foobar as
(select t.id as t_id, name, r.id, r.group_id, r.rule
   from things t
   join match_group_rules r
     on t.name like r.rule || '%'
)
select * from foobar
    union all
select id, name, NULL, NULL, NULL from things t2 where not exists
 (select 1 from foobar where t2.id=foobar.t_id);


Push replicas from master to slave in postgresql

No,there is no mechanism where a master will find out slaves waiting for replication.Slave to master connection is a necessary requirement as slave needs to authenticate to master.
If you are sure the connection is not getting established due to a restricting firewall then you need to configure your firewall to allow connection.Otherwise,you need to allow slaves' ip in pg_hba.conf on master server.


A DATA_LENGTH of 22 is just the largest number of bytes it can take to store the largest possible number (Oracle uses 2 digits per byte). A NULL DATA_PRECISION implies the maximum of 38.

You can verify this with DBMS_METADATA:

SQL>  create table sotmp ( a int );

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','SOTMP')
  2  from dual;

DBMS_METADATA.GET_DDL('TABLE','SOTMP')
--------------------------------------------------------------------------------

  CREATE TABLE "PHIL"."SOTMP"
   (    "A" NUMBER(*,0)
   ) SEGMENT CREATION DEFER

SQL>

(The "*" in the above output denotes the maximum precision).

BINARY_INTEGER is not a data type that can be used in table columns. It can only be used as a PL/SQL variable datatype.


Table design question for a `Check all that apply` survey

Option 3 is classic 3NF design.  If you need to add data to the table quickly, it will likely be the highest performing approach for inserts, so if you need to insert the data quickly and have high concurrency concerns that may be the way to go.

To me, it sounds like you're trying to solve an analytical problem.  If that is the case, I'd go with option 2.  This is a de-normalized approach.    Your users are likely already thinking about their data in that way, so they'll appreciate the simplicity in it.  With the way that you're receiving your data, it could be the most difficult to achieve, but the effort to get it in simply will be worth it.  Your users will appreciate the elegance which is hopefully your goal.

Good Luck!


Prevent consecutive duplicate values without a trigger

The INSERT itself, without a trigger, can avoid inserting the same value as the last one for the same experiment in the order of IDs. For instance, if $1 is the experiment and $2 the value:
INSERT INTO new_samples(experiment,value)
SELECT $1, $2 WHERE NOT EXISTS
 (SELECT 1 FROM new_samples
   WHERE id=(SELECT max(id) FROM new_samples WHERE experiment=$1)
     AND value=$2);

If there were multiple sessions that inserted concurrently for the same experiment, there would be a potential race condition, because the SELECT within the INSERT won't see a just-inserted row from a concurrent transaction that hasn't committed yet. But there is the same race condition with a trigger or a check constraint that tries to look beyond the row being modified or inserted.
Race conditions can be handled by using the serializable isolation level (auto-aborting conflicting transactions) or pessimistic locking (lock out other writers before doing the operation, effectively forcing a serialization).


https://tiaashish.wordpress.com/2014/01/31/mysql-sum-for-distinct-rows-with-left-join/

Here is a blog post that describes exactly this situation and provides a very nice solution to it, using a simple formula:

SUM(leads.source_cost) * COUNT(DISTINCT leads.id) / COUNT(*)


High CPU almost always means inefficient queries.  The fix may be as simple as a better index (possibly composite), or rephrasing a SELECT.  Find out what queries are most commonly running and let's discuss them.

About the only tunable that might be incorrect is innodb_buffer_pool_size, which needs to be about 70% of available RAM.

You might be able to catch slow queries by using SHOW FULL PROCESSLIST; repeatedly.  But the slowlog is better.  See here.


SQL Server Error Log - Is It / Can It Be Encrypted?

Is it possible for the SQL Server Error Logs to be encrypted?

There is no option that "turns on errorlog encryption", so you'll need to use encryption at a lower layer, such as bitlocker or similar disk encryption utilities.

  Our SQL Server recently had a memory dump which it threw into the Error Log. While the dump was definitely useful to identify the issue, it got me wondering about the visibility of that dump to users who may have access to the server, but not necessarily SQL Server.

If they have access to the server then they have access to SQL Server. No two ways about it.

  I know TDE protects Data, Log, and Backup files at rest, but potentially sensitive information can be exposed via a memory dump - especially if it is intentionally engineered by a malicious source.

TDE wouldn't help here, and in general full disk encryption won't help either if the sources can already log into the server - regardless of having access to SQL Server or not.

Anyone that can log into the server can potentially read memory. Some permissions might not be locked down and debuggers can be attached. Other avenues also exist which I won't get into here but if they have access, it's already too late.

  I want to be proactive and protect against possible data breaches by encrypting the SQL Server Error Log if possible to reduce the chance of intentional memory dumps exposing sensitive data to non-authorized users.

I completely understand and you should be proactive, kudos! Sadly, there really isn't anything native inside of SQL Server to do this. Encrypting it through 3rd party solutions may cause other issues.

I would say the best thing is to make sure no one has access to the server and who has access to SQL Server. Anecdotal evidence: I once was able to see a bunch of data on a "locked down" server because there was a share that had "everyone" as read permissions.


MySQL: creating a user that can connect from multiple hosts

Use this query to creating user account that can be connect from the localhost or from another server

CREATE USER 'bob'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;


How to see what is cached in memory in SQL server 2008?

You can find whats stored in the buffer pool (data cache) using below query :

From here :

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Excellent reference : Inside the Storage Engine: What’s in the buffer pool? by Paul Randal.


Whenever you want to restore a backup/log from a file which has more than 1 file inside, you can always select the file you want to restore for example

{RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1
GO}


Can I use older MySQL workbench with newest MySQL server?

In  my experience workbench 6.3 works only bad with mysql server 8.0.(it can not use the new authorisation)
The same goes for workbench 8.0 and the old Server version, some things work other not. You get also a warning when you connect.
So i have installed a Mysql 8 version and have a 6.3 in a vm, which i use to access 5.x server.


How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

Scott,

Check this statement out:

SELECT
    [Database] = DB_NAME ( qt.dbid ),
    [Execution Count] = SUM ( qs.execution_count ),
    [Total Execution Time] = SUM (qs.total_elapsed_time / 1000000.0 ),
    [Total CPU Consumption] = SUM ( qs.total_worker_time ),
    [Total Reads] = SUM ( qs.total_physical_reads ),
    [Total Writes] = SUM ( qs.total_logical_writes ),
    [Average Execution Time] = SUM ( qs.total_elapsed_time / qs.execution_count / 1000000.0 ),
    [Average CPU Consumption] = SUM ( qs.total_worker_time / qs.execution_count / 1000000.0 ),
    [Average Reads] = SUM ( qs.total_physical_reads / qs.execution_count ),
    [Average Writes] = SUM ( qs.total_logical_writes / qs.execution_count )
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) qt
LEFT JOIN sys.databases o ON qt.objectid = qt.dbid
GROUP BY qt.dbid

This should give you all of the information you need.  If all you're interested in is disk usage just keep the total reads/writes and average reads/writes.


Calculate Employees' Total Working hours using SQL Server

This is quite a common requirement and I have seen many business need the same type of query. The below is just something I have knocked up super quick but it'll work for you:

CREATE TABLE #logs
(
    [empid] int,
    [Time] DATETIME,
    [In_Out] VARCHAR(3)
);
INSERT #logs
VALUES
(1, '2019-01-01 09:00:00', 'In'),
(1, '2019-01-01 12:00:00', 'Out'),
(1, '2019-01-01 13:00:00', 'In'),
(1, '2019-01-01 17:00:00', 'Out'),
(1, '2019-01-02 23:00:00', 'In'),
(1, '2019-01-03 07:00:00', 'Out');

WITH t
AS
(
    SELECT COALESCE(i.[empid], o.[empid]) AS [empid]
         , COALESCE(i.[Date], o.[Date]) AS [Date]
         , COALESCE(i.[Time], CAST(o.[Time] AS DATE)) AS [in]
         , COALESCE(o.[Time], DATEADD(DAY, 1, CAST(i.[Time] AS DATE))) AS [out]
         , RANK() OVER (PARTITION BY i.[Time] ORDER BY o.[Time]) AS r
      FROM (SELECT [empid], CAST([Time] AS DATE) AS [Date], [Time]
             FROM #logs
            WHERE [In_Out] = 'In') AS i
 FULL JOIN (SELECT [empid], CAST([Time] AS DATE) AS [Date], [Time]
             FROM #logs
            WHERE [In_Out] = 'Out') AS o
        ON i.[empid] = o.[empid]
       AND i.[Date] = o.[Date]
       AND i.[Time] < o.[Time]
)
SELECT [empid], [Date], SUM(DATEDIFF(HOUR, [in], [out])) AS [Hours]
  FROM t
 WHERE r = 1
GROUP BY [empid], [Date]
ORDER BY [empid], [Date]

DROP TABLE #logs;

It works by doing a self join where each side of the join is pre-filtered for the specific entry type you are looking for. Then, you rank the results to ensure you dont count double entries. After that, its simple maths.


You are correct you cannot use indexed views. Sadly what you have here is a business problem not a database problem. If you need to know the PKs and indexes from the other database and you don't have even viewing access, then require the DBAs who do have that access to provide the information you need to do your job or require them to write and performance tune the queries. No one should be writing queries against a database they do not have access to. If they won't give you access, then the work belongs to them too. I would have my boss talk to their boss and ask them to solve this set of problems for you. They created the problem it's up to them to fix it.


Azure Elastic Pool - is it supported for MySQL?

Essentially, yes.  An Azure Database for MySql is similar to an Azure SQL Database Elastic Pool or Azure SQL Database Managed Instance.

Within an Azure Database for MySQL server, you can create one or
multiple databases. You can opt to create a single database per server
to use all the resources or to create multiple databases to share the
resources. The pricing is structured per-server, based on the
configuration of pricing tier, vCores, and storage (GB). For more
information, see Pricing tiers.

https://docs.microsoft.com/en-us/azure/mysql/concepts-servers
An Azure SQL Database Elastic Pool is similarly:

Azure SQL Database elastic pools are a simple, cost-effective solution
for managing and scaling multiple databases that have varying and
unpredictable usage demands. The databases in an elastic pool are on a
single server and share a set number of resources at a set price.
Elastic pools in Azure SQL Database enable SaaS developers to optimize
the price performance for a group of databases within a prescribed
budget while delivering performance elasticity for each database.

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
Only Azure SQL Database has the feature to have lots of databases with separate physical resources in the same logical server.  With Azure Database for MySql if you want two databases to have their own dedicate resources, you need two seperate Azure Database for MySQL servers.


Data Warehouse: Staging Area, DWH databases and ETL tool on one server?

Your "all your eggs in one basket" scenario seems ripe for unintended consequences. I try to decouple processes so that something that runs amok doesn't propagate a negative effect to other parts of your overall DW architecture. Using separate disks is definitely good practice and even splitting , bear in mind that memory and CPU constraints are just as important points of contention.

Here's a scenario that I've seen in designs like yours ----

A sql plan changes (due to data quantity or quality changes) and you come back in the next morning and realize the ETL's still going and the additional CPU and memory draw is affecting query performance. This puts you in the unsavory situation wherein you're wondering whether it's far enough along to finish the load or you should terminate it and do a catch up load later (which may have even more data) to let users get at their data.

Decoupling some of your architecture gives you the freedom to get your staged data a second look to review issues, run validation checks, and most importantly give you the flexibility of troubleshooting during the work day without worrying about sharing resources.

In any case, if you do decide to run via the single box route (which I'd highly recommend against), consider:

Making sure the right tables use the right engine for the appropriate requirement
CPU limiting certain processes so that they don't starve the server
Ensuring alerts and notifications are properly set up to allow you to keep track of errors, completions, and long running processes
Test, test, and.... TEST. test with different data sizes. test with a variety of data. and test by simulating issues like memory pressure, etc.

It should be self-evident that what I've said is a conjunction of a few best practice recommendations and my personal experience. You should account for your business requirements and its capacity for risk. i.e.: In some industries, having a data warehouse loaded with the previous day's data is a highly critical requirement. In others, just having access to the data warehouse is the most critical requirement.


After looking at split_part() from PostgreSQL String Functions, and assuming that column is the name of the "compound attribute" that is of the form "acronym codenum", then I'd say you should try something like this

SELECT
  split_part (column, ' ', 1) AS acronym,
  split_part (column, ' ', 2) AS codenum
FROM table


Possible approaches:

If your problem is well represented by a graph (and I think it is), I'd suggest that you consider using a Graph Database. It's optimised for this kind of scenarios: you have pages (nodes), you have links between pages (verticies), and you have some attributes associated to either one of them. Check Neo4J or OrientDB to get a feeling for what they could do in your scenario.

If you want to save some space, consider a way to represent menus in a compact way. That is, there will be a very large number of cases like:

page1 - links_to - home
page1 - links_to - legalese
page1 - links_to - the_company
page1 - links_to - our_products
page1 - links_to - our_services
page1 - links_to - contacts

page1 - links_to - something unique or not common 1
page1 - links_to - something unique or not common 2

and then again

(same for page2)
(same for page3)

if you identify this structure, you can create some "meta-structure" such as "standard_menu". (The difficult part of this approach is deciding what collection of links should be considered a menu and which are not, and small differences such as a page containing a menu with a slight variations [such as "I don't link to myself"]).

This data structure should help you:

CREATE TABLE urls
(
    id_url serial PRIMARY KEY,
    url text NOT NULL,
    UNIQUE(url)
) ;

CREATE TABLE menus
(
    id_menu serial PRIMARY KEY
) ;

CREATE TABLE menu_links
(
    id_menu integer not null REFERENCES menus(id_menu),
    id_url_to integer not null REFERENCES urls(id_url),
    link_text text not null,
    PRIMARY KEY (id_menu, id_url_to)
) ;

CREATE TABLE from_page_to_page
(
    id_url_from integer not null REFERENCES urls(id_url),
    id_url_to integer not null REFERENCES urls(id_url),
    link_text text not null,
    PRIMARY KEY (id_url_from, id_url_to, link_text)
) ;

CREATE TABLE from_page_to_menu
(
    id_url_from integer not null REFERENCES urls(id_url),
    id_menu integer not null REFERENCES menus(id_menu),
    PRIMARY KEY (id_url_from, id_menu)
) ;

And finally, you can have a view that puts together the equivalent of your existing table by making a UNION of the "menu links" and the "non-menu links":

CREATE VIEW all_links AS

SELECT
    id_url_from, id_url_to, link_text
FROM
    from_page_to_page
UNION ALL
SELECT
    id_url_from, id_url_to, link_text
FROM
    from_page_to_menu
    JOIN menu_links ON menu_links.id_menu = from_page_to_menu.id_menu ;

Alternatively, you could always change the name "menu" by "link_group", every time you have something like:

page1 - links_to - page_a
   page1 - links_to - page_b
   page1 - links_to - page_c
   page1 - links_to - page_z

page2 - links_to - page_a
   page2 - links_to - page_b
   page2 - links_to - page_c
   page2 - links_to - page_y

page3 - links_to - page_a
   page3 - links_to - page_b
   page3 - links_to - page_c
   page3 - links_to - page_x

(ignoring text labels, these are actually 12 pairs of numbers)

you could convert it into:

link_grup_1  - page_a
   link_grup_1  - page_b
   link_grup_1  - page_c

page_1 - link_grup - link_grup_1
   page_1 - links_to - page_z
   page_2 - link_grup - link_grup_1
   page_2 - links_to - page_y
   page_3 - link_grup - link_grup_1
   page_3 - links_to - page_x

(these are only 9 pairs of numbers).

The savings will grow as soon as the number of items common to many pages is more than just two or three.


Unable to connect to AWS redshift database

I managed to connect using the full endpoint as given by AWS (less the port number), and then following the instructions to add your IP address to your EC2 vpc security group here:

https://stackoverflow.com/questions/19842720/cant-connect-to-redshift-database/19861418#19861418


Why would I download CA certificate when starting a managed Postgres cluster on DigitalOcean?

You can use it with TLS to verify that you are connecting to the correct server.
The connection string for that would look like:
host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-certificate.crt

If you cannot specify the connection string that way, you can use the environment variables PGSSLMODE and PGSSLROOTCERT.
Then the PostgreSQL client library will verify that the server's certificate is actually signed by the CA certificate and that the server name is correct.
Of course that requires that your PostgreSQL client library was configured --with-openssl.


How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

The hint of the error message is right on target:

  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

Bold emphasis mine. Per documentation:

  RETURNING queries on the view will be rejected if there is no
  RETURNING clause in any available rule.

Don't confuse this with the RETURNS clause of your function. Not the same thing.

Postgres looks for an actual RETURNING clause of an INSERT, UPDATE or DELETE statement. It has to be a plain SQL statement. There can only be one RETURNING clause, even if there are multiple statements in the rule:

  furthermore there can be at most one RETURNING clause among all the rules for the same event.

It will only be executed if the outer call has a RETURNING clause, too.

Since you are inserting into two tables, it gets slightly complicated. Either one of the two INSERT statements in the rule only has the columns of one target table at it's disposal. You would need to select from the other table (or from the view itself) in the RETURNING clause to provide a complete row.

Also, the returned value from the serial column cannot be stored in a variable, since there are no variables in plain SQL. I solved it with lastval() instead.

And NEW is not visible in a CTE, so you can't chain data-modifying CTE either.

But it can be done anyway:

CREATE OR REPLACE RULE custom_insert_gi_joe AS
ON INSERT TO gi_joe DO INSTEAD (

 INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
 VALUES (NEW.weapon_type, NEW.weapon_fire_power, NEW.created_at, NEW.updated_at);

 INSERT INTO joe(first_name, last_name, city, country
               , gi_id, external_id, created_at, updated_at)
 VALUES(NEW.first_name, NEW.last_name, NEW.city, NEW.country, lastval()
      ,(SELECT joe_id FROM external WHERE id = NEW.external_id LIMIT 1)
      , NEW.created_at, NEW.updated_at)
 RETURNING
   joe.gi_id,
   NULL::int,
   NULL::int,
   joe.first_name,
   joe.last_name,
   joe.city,
   joe.country,
   joe.external_id,
   NULL::timestamp,
   NULL::timestamp
);

SQL Fiddle.

I didn't bother to provide the complete row and just filled in NULL values for missing columns from gi, cast to respective types. This could be solved, too, but since you are only interested in the id column, it would be cost for no gain.

Now, this works as expected:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at)
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW())
RETURNING id;


MySQL replication: slave is not getting data from master

From your Show slave status \G output, the following things can be understood:

Slave_IO_Running: Yes It means, Input Output thread is running - this thread is responsible for downloading data (binlog) from master server to slave server.
Slave_SQL_Running: Yes It means, SQL thread is running - this thread is responsible for executing SQL from relay log (from binlog).
Read_Master_Log_Pos: 402, Exec_Master_Log_Pos = 402, It means, the IO thread reading from the binary log position 402 and the SQL thread executing the same query.
Seconds_Behind_Master: 0 It means, both the master and slave is in sync and slave is in up-to the point, no delay.
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates. It means slave is up to the point.

It seems, slave is getting data from master and there is no issues in your settings.

FYI:

Run the Show Slave Status \G command continuously, you will see the following variable values will change, every time.

Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 402
Relay_Log_File: VoltyLinux-relay-bin.000046
Relay_Log_Pos: 317
Exec_Master_Log_Pos: 402
Relay_Log_Space: 692

In case, if your slave stopped getting data from master server, you will see the No value for Slave_IO Running variable in your Show Slave Status \G command output as follows.

Slave_IO_Running: NO

I hope this answer will help you. Thanks.


You don't need window functions:

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

Please check if you want >= or > in the conditions. It's better (in my opinion) to have date and time checks as ">= start_date" and "< end_date", in other words working with closed-open datetime intervals.


SQL Server: can dynamic data masking be safe when providing a database backup?

No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.

I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.


Role or permission to view but not create SQL Agent jobs

Seems like this should work:

  The "reader" name is definitely misleading. This may be stating the
  obvious, but to get around this I've granted the SQLAgentReaderRole
  role and explicitly denied EXECUTE on add job sprocs in msdb for the
  logins with the role.

DENY EXECUTE ON [dbo].[sp_add_job] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobschedule] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobserver] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobstep] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobstep_internal] TO [YourLogin] GO


How can I insert records in a table that are dependent on other tables?

Since the LOCATIONS and employee CLASSES are quite important for your database, you should probably give them separate tables.  Here some suggestions - which may serve as a starting point (including some test data, using Oracle 12c).

create table locations ( id, description )
as
select 100, 'Executive Lounge' from dual union all
select 200, 'Executive Washroom' from dual union all
select 300, 'Supply Closet' from dual union all
select 400, 'Employee Lounge' from dual union all
select 500, 'Server Room' from dual union all
select 600, 'Server Closet' from dual;

create table employeeclasses ( id, description )
as
select 1, 'class 1' from dual union all
select 2, 'class 2' from dual union all
select 3, 'class 3' from dual union all
select 4, 'class 4' from dual union all
select 5, 'class 5' from dual union all
select 6, 'class 6' from dual;

create table employees ( id, fname, lname, employeeclass )
as
select 5000, 'fname5000', 'lname5000', 1 from dual union
select 5001, 'fname5001', 'lname5000', 2 from dual union
select 5002, 'fname5002', 'lname5000', 1 from dual union
select 5003, 'fname5003', 'lname5000', 2 from dual union
select 5004, 'fname5004', 'lname5000', 6 from dual ;

alter table locations
add constraint pkey_locations primary key ( id ) ;

alter table employeeclasses
add constraint pkey_eclasses primary key ( id );

alter table employees
add constraint pkey_employees primary key ( id ) ;

alter table employees
add constraint fkey_empclass
foreign key ( employeeclass ) references employeeclasses ( id ) ;

When it comes to permissions, things get a bit more complicated :-).  Apparently, you want to be able to assign access permissions to GROUPS (classes of employees).  However, in the "access log" (your table3), you are recording events that are associated with SINGLE users.  Thus, (just as an idea) we could create a PERMISSIONS table, where we record all permissions (per employee).  You see that any of the valid combinations of EMPLOYEEID and LOCATIONID can be recorded in the access log table (code below), whose foreign key constraint references the permissions table.

create table permissions (
  employeeid number references employees( id )
, locationid number references locations( id )
, constraint pkey_permissions
  primary key ( employeeid, locationid )
);

create table accesslog (
  id number generated always as identity start with 2000
, employeeid number
, locationid number
, access_time date default sysdate
, constraint fkey_accesslog
  foreign key ( employeeid, locationid )
  references permissions ( employeeid, locationid )
);

Testing

-- Grant all members of class 1 access to locations 100 and 200.
insert into permissions ( employeeid, locationid )
select id, 100
from employees
where employeeclass in ( 1, 2 )
union
select id, 200
from employees
where employeeclass in ( 1, 2 ) ;

-- Employee 5000 accesses location 100 -> access granted, row inserted.
insert into accesslog ( employeeid, locationid )
values ( 5000, 100 ) ;
-- 1 row inserted.

-- Is employee 5000 allowed access to location 500? No.
insert into accesslog ( employeeid, locationid )
values ( 5000, 500 ) ;
ORA-02291: integrity constraint (...FKEY_ACCESSLOG) violated - parent key not found

-- Employees 5000, 5001, 5002, and 5003 want to access location 200.
-- They have permission to do so.
declare
  location constant number := 200 ;
begin
  for i in 5000 .. 5003
  loop
    insert into accesslog ( employeeid, locationid )
    values ( i, location ) ;
  end loop;
end;
/
-- PL/SQL procedure successfully completed.

Our "access log" now contains:

select id, employeeid, locationid
, to_char( access_time, 'YYYY-MM-DD HH24:MI:SS' )
from accesslog ;

        ID EMPLOYEEID LOCATIONID TO_CHAR(ACCESS_TIME
---------- ---------- ---------- -------------------
      2000       5000        100 2018-10-08 04:46:35
      2002       5000        200 2018-10-08 04:50:20
      2003       5001        200 2018-10-08 04:50:20
      2004       5002        200 2018-10-08 04:50:20
      2005       5003        200 2018-10-08 04:50:20

Now - if you also want to record "access denied" events, and want to use GROUP/class level permissions, too, then we need triggers (frowned upon...) or procedures (the bee's knees...).  The main reason for this being: CHECK constraints only work within a single table.  Hence, we cannot check whether a particular employee belongs to a particular group AND whether this group is allowed to access a particular location (even in your original layout, the class data and employee data are located in 2 tables ...).

For recording all valid combinations of classes and locations, we should probably add another table (your original design will contain loads of NULLs in table1).  Also, the accesslog gets an additional column, that allows us to record "access denied" events.

alter table accesslog
add allowdeny varchar2(128) default 'access granted' ;

SQL> select * from accesslog;
ID    EMPLOYEEID  LOCATIONID  ACCESS_TIME  ALLOWDENY
2000  5000        100         08-OCT-18    access granted
2002  5000        200         08-OCT-18    access granted
2003  5001        200         08-OCT-18    access granted
2004  5002        200         08-OCT-18    access granted
2005  5003        200         08-OCT-18    access granted

-- just an example!
create table locations_classes ( locationid, classid )
as
select 100, 1 from dual union all
select 200, 2 from dual union all
select 300, 3 from dual union all
select 400, 4 from dual ;

The procedure code should go into a package (which is omitted here, for clarity).  The code given below is far from perfect, but it should give you something to work with - if you have done some PL/SQL before.

create or replace procedure getAccess (
  employeeid_ number
, locationid_ number
)
is
  employeeclass_ number := 0 ;
  classfound_ number := 0 ;
begin
  select employeeclass into employeeclass_
  from employees where id = employeeid_ ;

  select count(*) into classfound_
  from locations_classes
  where rownum = 1 and locationid = locationid_ and classid = employeeclass_ ;

  if classfound_ > 0 then
    insert into accesslog ( employeeid, locationid )
    values ( employeeid_, locationid_ ) ;
  else
    insert into accesslog ( employeeid, locationid, allowdeny )
    values ( employeeid_, locationid_, 'ACCESS DENIED' ) ;
  end if;
end;
/

When testing the procedure, you may find that some calls will cause an ORA-02991.  You can disable the accesslog's FK constraint in order to allow the logging of "access denied" events.

-- parameters: employee id, location id
begin
  getAccess( 5000, 100 ) ;
  getAccess( 5003, 100 ) ;
  getAccess( 5002, 100 ) ;
  getAccess( 5004, 300 ) ;
  commit ;
end ;
/

-- ORA-02291: integrity constraint (...FKEY_ACCESSLOG) violated - parent key not found

alter table accesslog disable constraint fkey_accesslog ;

SQL> select * from accesslog ;
ID    EMPLOYEEID  LOCATIONID  ACCESS_TIME  ALLOWDENY
2024  5000        100         08-OCT-18    access granted
2025  5003        100         08-OCT-18    ACCESS DENIED
2026  5002        100         08-OCT-18    access granted
2027  5004        300         08-OCT-18    ACCESS DENIED

Sorry for the length of this answer!  The gist is: do whatever you can with integrity constraints!  Let the DDL code do the work.  CHECK constraints do not work across tables.  When you look at the (very crude) diagram, you will see that the LOCATIONS_CLASSES is not connected to the access log.  The procedure "enforces a constraint" - but not really, as we want to record "access denied" as well.

If you want to use a procedure, you may not need the PERMISSIONS table.  However, if you can get away WITHOUT writing a procedure, and only use integrity constraints, you'll get a more robust solution!


How to design a database where parent table has default values

It might not be the most elegant model - but your description points to 2 (independent) m:n relations between Clients and Accessories and Employees and Accessories.
The usual way to resolve those m:n relations IS to create mapping tables like you did - so nothing wrong from that view point.
Of course it needs joins to request data over the 3 tables, when you want to know what accessories are still held by a certain Client or employee.

If your clients or employees receive the same initial sets of accessories you can go like @Akina wrote in the comments. Fill a small template table with the sets you want to give new clients / employees and copy the lines over - while filling in the correct clientID / employeeID to the corresponding mapping table.


Moving Transaction Data To Another Database For Reporting Purpose

Since you have this requirement,

  They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database

AlwaysON gets eliminated.

I would suggest you to setup Transactional replication.

  Frequently (every 30mins) move the transaction data out of live database to another database

You can schedule the log reader agent frequency to 30 mins (or as per your needs).

As a side note, I will suggest you to read this article by Kendra Little. It explains T-Rep and compares it with other technologies (AlwaysON, CDC, etc).


MySQL uses RAM in many ways...

For caching data and indexes.  In some sense, it does not matter how much RAM, since it is cache.  This may be the biggest use, but it is a "shared" use.
A complex SELECT may need a temporary table (eg, for GROUP BY and/or ORDER BY and/or subquery).  This may be done in RAM, or it may not even attempt to do it in RAM, but instead do it on disk.  There is one setting that puts a limit on the size of each in-memory temp table.
The parsing of the statement takes a little bit of RAM.
The binlog is buffered through RAM.
"Group commit" is buffered in RAM.
Galera does its "will this commit with a deadlock" logic in RAM.
InnoDB uses bits to keep track of what is locked.

Shall I go on?  Your question is not easy to answer.  Furthermore, I would argue that it is mostly irrelevant.  Would you care to explain why you asked?


Assuming Postgres 9.4 or later:

SELECT id, t2.table2value0, t2.table2value1, t2.table2othervalues
FROM   table1 t1
LEFT   JOIN (
   SELECT table1_id AS id
        , min(value) FILTER (WHERE type = 0) AS table2value0
        , min(value) FILTER (WHERE type = 1) AS table2value1
        , array_agg(value) FILTER (WHERE type IN (1,2) IS NOT TRUE) AS table2othervalues
   FROM   table2
   GROUP  BY table1_id
   ) t2 USING (id);

The aggregate FILTER clause was introduced with pg 9.4. There are various less elegant alternatives for older versions:

Postgres Nested WHEN Aggregate Function

This returns one row for every row in table1, missing types in table2 result in NULL values.

  If there are more than one value for table2value0 and table2value1, I only want the first.

You seem to be under the impression that there is a natural order of rows, but there is not:

Does Postgres preserve insertion order of records?

I picked the minimum value instead, since your requirement is effectively undefined.

The best query depends on your actual table definition (including all constraints).

In particular, the expression type IN (1,2) IS NOT TRUE also catches type IS NULL, which may not be necessary, depending on your missing table definition.

Related:

Select column to be inserted based on input column value
How to pass a parameter into a function

Aside:
Your desire to return a single (scalar) value for types 1 and 2, but an array for the rest makes it harder to solve with crosstab() (but still possible). Basics:

PostgreSQL Crosstab Query


DECLARE @x TABLE(StrippedLogin VARCHAR(255));

INSERT @x(StrippedLogin)
  SELECT LEFT([Login], CHARINDEX(':', [Login])-1)
  FROM dbo.OriginalTable
  WHERE CHARINDEX(':', [Login]) > 1;


Use MDX to select Dimension-Members instead of Measure Values

You can achieve your desired results by using a crossjoin (the * notation) and putting the dimensions on the columns axis. Here's an example based upon the Adventure Works SSAS DB.

select {} on 0,
{
[Customer].[Gender].[Gender].members
*
[Date].[Calendar Year].[Calendar Year].members
}  on 1
from [Adventure Works]


Can I be automatically notified of prolonged blocking in SQL server?

Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:

alter database [...] set read_committed_snapshot on;

No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of applications don't notice any difference, they just get a free performance boost.

Anyway, I though to answer also the original question: how to detect (and possibly kill) a long running query. Actually, the engine already does that for you. There is an event raised when a threshold is passed: Blocked Process Report Event Class. The threshold is configured via the blocked process threshold Option. Any trace event can be turned into an Event Notification and event notifications can activate procedures. Connect the dots and you have on-demand activated code that runs when the engine detects a query that has crossed an execution time threshold. No polling, no monitoring. Note though that the notification is asynchronous, by the time you process it the query may had completed so that has to be taken into account.

Here is an example:

use msdb;
go

create queue blocked_process_report_queue;
go

create service blocked_process_report_service
    on queue blocked_process_report_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification blocked_process_report_notification
    on server
    for BLOCKED_PROCESS_REPORT
    to service N'blocked_process_report_service',
          N'current database';
go

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;

Now in a new query set up a WAITFOR expecting a notification:

use msdb;
waitfor(
   receive cast(message_body as xml), *
   from  blocked_process_report_queue);

And go ahead and cause some blockage. I used a process that created a table and didn't commit, and from another query windows I tried to select from the table. In 20 seconds (my configured threshold above) I got the blocking report:

<blocked-process-report>
  <blocked-process>
    <process id="process1b5a24ca8" ...>
      <executionStack>
        <frame line="1" stmtstart="-1"... />
      </executionStack>
      <inputbuf>
          select * from t   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="sleeping" spid="51" ...>
      <executionStack />
      <inputbuf>
         begin transaction
         create table t (a int)   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

I'll leave the task of wrapping this up into an automated process as an exercise to the reader. And yes, the queue/service/activated procedure must be in [msdb].


How to select nodes where all children is satisfied?

I would first rethink the design. You only need one table:

CREATE TABLE c (
  id integer PRIMARY KEY
 ,parent_id integer REFERENCES c(id)
 ,state boolean
);

With the layout as presented in the question, the query could be:

SELECT DISTINCT a.*
FROM   a
JOIN   b ON a.node_id = b.id
LEFT   JOIN a ca ON ca.child_node_id = a.node_id
LEFT   JOIN b cb ON cb.id = ca.node_id AND cb.state = FALSE
WHERE  b.state = FALSE
AND    cb.id IS NULL

This includes nodes that are turned off and have no children at all.
To exclude nodes without children, replace the first LEFT JOIN with a plain JOIN.

Or, may be faster:

SELECT a.*
FROM   a
JOIN   b ON a.node_id = b.id
WHERE  NOT EXISTS (
   SELECT 1
   FROM   a ca
   JOIN   b cb ON cb.id = ca.node_id
   WHERE  ca.child_node_id = a.node_id
   AND    cb.state = FALSE
   )


Managed to find the error source.

First, check the log file (eg. [Path to log)/mongod.log)

Turns out that the authentication has failed on one of the config servers. This is fixed by including " --auth --keyFile [Path to Key]" when starting up the config server mongod.


Can't create new tablespace for PostgreSQL data. Permissions denied

For some reason I don't seem to be allowed to change my PostgreSQL data folder to a drive other than the C drive.

This is not the correct way to do that anyway.  Creating a tablespace and moving the whole cluster are different things.

  I am an admin on that machine and I have made sure that I have 'full control' on the D drive.

It doesn't matter who you are, what matters is who is the service account running PostgreSQL, and what permissions that account has.


AFTER UPDATE trigger that modifies the table on which it is defined

You don't need, neither you can have (as the error message suggests), the UPDATE statement against the table on which the trigger fires. You can, however, directly set the value any NEW column in a BEFORE trigger:

CREATE TRIGGER `Metrosky_spotters_2020_au`
BEFORE UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
SET NEW.trnmswid = 5000


w/o any secondary index I can think of PIVOT. (Data in Key-Value pair way)

Depending on the usage and query you are expecting you need to decided if key "LogText" needs to go in to another table or ok to put in Table-2.

You can have key on both columns and that will give you option of defining local secondary index to improve performance for complex queries like

ALL DATA where (TimeStamps is between x and y) and User = "ABC"

this can be done in two steps,

select ALL data where KeyIDs are IN
( get unique KeyIDs for that satisfy this WHERE conditions.)


What is the most efficient way to update a product table with 1 million rows in MySQL

What alterable properties of the product table e.g. Indexes might slow down
  updates?

What do you care? It is totally irrelevant. See, by your own words:

  For each row, I need to perform an expensive http operation and then update 1
  field in the row based on a returned header.

So, all the MySql performance wont matter - unless you run that on a mobile phone - because you are going to be controleld in speed by "an expenive HTTP operation".

Make a queue (can bedb based), request the http stuff one by one, run updates. THe updates wont take long. Get into multi threaded programming and use some hundred threads - each running in parallel - to get some http speed.

But the update performance in MySQL will not matter unless you want to wait untill you got all data, which may work or not - normally I would say it is not an optimal way.


SQL aggregate function performance in a table with many records that is well-indexed

Is it common to spread data of a single user operation across multiple rows?

Think of the "entities":  User, Review, Rating.  A "user" may go to the restaurant many times, and give a "review" each time.  The "review" may give ratings for multiple things, such as 'food' and 'location'.

These are "Many-to-one" relationships, so Ratings has a review_id and Reviews has a user_id.  ("Many-to-many" relationships need an extra table.)

Yes, database optimizer can usually use indexes as needed.  We need to look at the specific SELECTs to say further.

KEY `nid` (`nid`), -- Redundant with the next; DROP this.
KEY `nid_uid` (`nid`,`uid`),

It looks like you did a shotgun approach to adding INDEXes.  Do EXPLAIN SELECT ... on your various SELECTs -- This is to see which indexes are actually used.  Then DROP the unused queries.

Every table should (must?) have a PRIMARY KEY.  For Ratings, it should probably be PRIMARY KEY(review_id, type).

Your "reports" are likely to do JOINs and may end up COUNTing and SUMming things extra times, so beware.  Do a sanity check on the results.  The overages can be fixed, but I'll let you get to that point before trying to explain the problem.


How to separate values from one column into two columns in SQL Server

Here's an example which does what you want and will get you started with using PIVOT. Sorry it is a bit rushed, but hopefully it will get you started and show you how it can be done fairly easily.

There will be limitations and I would fully advise researching and playing with the pivot functions because they are really powerful.

-- Some sample data to work with
DECLARE @SampleData AS TABLE (
    NAME VARCHAR(20)
    ,contactnumber BIGINT
    )

INSERT INTO @SampleData
VALUES (
    'Deepika'
    ,75865558
    )
    ,(
    'Deepika'
    ,5181568
    )
    ,(
    'Deepika'
    ,56954121
    )
    ,(
    'Rnveer'
    ,4561896
    )
    ,(
    'Rnveer'
    ,48640655
    )
    ,(
    'vaibhav'
    ,48640655
    );

--The pivot
WITH PivotData
AS (
    SELECT NAME --The row group
        ,'Contact ' + CAST(ROW_NUMBER() OVER (
                PARTITION BY NAME ORDER BY contactnumber ASC
                ) AS VARCHAR(1)) AS TheRow --The value that will form columns
        ,contactnumber
    FROM @SampleData
    )
SELECT *
FROM PivotData
PIVOT(AVG(contactnumber) FOR TheRow IN (
            [Contact 1]
            ,[Contact 2]
            ,[Contact 3]
            ,[Contact 4]
            )) AS ThePivot1


Best Database for range queries on a single column

I'll raise a_horse_with_no_name's ante, and tell you I've personally worked with multi-terabyte databases in Microsoft SQL Server that had single tables in the hundreds of billions of rows without any form of Sharding or Partitioning setup, and ran just fine. I've even discussed the topic with Microsoft representatives who've claimed the system can handle tables into the trillions of rows.
Specifically I've worked with range queries (including on fields like a CreatedDate) on tables in the tens of billions of rows, and they were efficient and performed index seeks when appropriate (based on the cardinality of my predicates). The hardware behind the servers these databases ran on were as modest as an average laptop, in some cases.
As far as any modern database system goes (relational or not), you shouldn't choose one based solely on the amount of data you plan to support. Rather you should choose one based on the type of data (inclusive of it being structured, semi-structured, unstructured, or highly variable), and your use-cases for that data such as the type of querying you plan to do.
With such a skinny and structured table of only three columns with lightweight data types, I see no reason why any modern relational database management system couldn't handle your requirements. Proper indexing and architecting will go the longest of ways towards your performance and scalability, as opposed to which database system you actually choose.


For discovering performance issues and getting started on them, my favorite tool is the SlowLog.  By keeping it turned on and a low value for long_query_time, I can look back in time to see what might have caused a slowdown in the past.  Plus it will point to the specific query (or queries) that should be investigated.

I feel that most non-crash issues will be reflected in some way in the slowlog.

Sometimes there are things that are challenging to investigate.  One Slave was running significantly slower than other Slaves, even though nothing indicated any spike in traffic, etc.  Even the slowlog was more confusing than helpful.  After a while, the problem went away.  Several people were looking into it.  The problem turned out to be the RAID controller 'cycling' its battery, during which it disabled the its "Battery Backed Write Cache".

Monyog and MEB are a tools ($$) that provide a lot of continual monitoring of a lot of metrics.  Then the problem becomes knowing what to look at.

Many times, I have encountered clients that happily make lots of connections to MySQL.  (Eg: Apache with a high value of MaxClients.)  Usually this is not a problem because MySQL finishes things rapidly.  But sometimes MySQL gets overwhelmed.  The cure is (in my opinion) to throttle how many connections the client will make to MySQL.  After only a few dozen active connections, MySQL starts stumbling over itself (locks, mutexes, I/O, CPU, etc).  The solution may be to speed up the queries (see SlowLog), or it may be to throttle the clients.  Threads_running (at the moment) and Max_used_connections (after the fact, but with no time given) are the clues that this scenario may be happening.

In that case, the SlowLog would show slow queries that are normally fast.  And it would show a lot of slow queries at a specific time -- coming from a lot of different connections (clients).

I have a couple hundred formulas (of STATUS and VARIABLES) with recommended ranges.  This comes in handy once in a while.  Some of them say whether caches (Query, Buffer_pool, Key_buffer, Table_open, etc) are being effective or inefficient.  Unfortunately, they rarely say "change xx to yy".

One project had spikes at the start of the hour.  Well, the obvious answer was 'cron'.  Sure enough, there were over a hundred cron jobs, each hitting at the top of the your, and possibly at other times.  Solution:  Instead of

*/5 * * * * do_something.sh

do

(3-59)/5 * * * * do_something.sh

(and change the '3' to various values for different cron jobs.)  MySQL EVENTs can also be a spike-maker.

Many monitoring tools can discover such an hourly spike.  Even an alert human can notice such.  But few, if any, monitoring tools will tell you what to do.

The real problem is that the tools give you clues, but not answers.  That is where a human needs to step in.  That human needs a knowledge of the 'system', the tunables, the queries, the schema, how to create good indexes, etc.


Best Format to Bulk INSERT/UPDATE Data to SQL SERVER using C#

What about "NO FORMAT"? Use SqlBulkCopy to dump the data into a temp table. No need to put a file in a place the server can read at all.


sqlnet.ora or tnsnames.ora changes require reboot?

In general, no.  A tnsnames.ora change shouldn't require a reboot but some applications will read and parse the tnsnames.ora at startup to be able to present a drop-down list of servers to the user, for example, and will cache whatever was read when the application started up rather than re-reading the file.  Depending on the situation, it might be easiest to reboot a Windows client rather than figuring out how to kill and restart any applications that might have the data cached.

A sqlnet.ora change might benefit from a reboot for the sake of consistency though it is not required.  If you are doing something like enabling dead connection detection on a server by setting sqlnet.expire_time, for example, it probably makes sense to reboot the server to make sure that the setting applies to all connections rather than just new connections-- if you're trying to debug why a particular dead connection is still hanging around, knowing that the server was restarted and that you're not looking at some artifact of a connection that was opened prior to the setting being made would generally be helpful.


Disable indexes on Amazon RDS PostgreSQL instance

As @pietrop already said: it's a good pratice drop de index and recreate it after the data load.

If you are loading a lots of data, use COPY[1] instead of INSERTs. Consider using PGLoader[2] on huge data load environments.

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/sql-copy.html
http://pgloader.io


I can't tell you the best structure for your data because I don't know all your needs, however, I can tell you how I structured my tables for a similar project that had to store a scalable amount of email messages in a searchable form that may or may not be geographically distributed. This is what we did:

There was a messages table consisting of a unique message_id as primary key, as well as some fields to store information that was not contained in the message itself (data from the SMTP envelope) such as dates and times, sender, sending server, a few performance indicators, etc. Additionally, there was a storage_id, to indicate which storage structure contained the parts/attachments (usually a folder path, but could be a schema-based address, such as sftp://user@host/path/to/folder).

Next was the headers table which stored one message header line per row. This contained a unique header_id as primary key, as well as a message_id (indexed) that linked back to a record in the messages table. There was a header_name field (indexed), which contained the header name (eg: "Subject", "From", etc), and a header_text field, which contained the text of the header. There was also a sequence number (indexed) that was used to indicate the order of the headers. Due to our need to search these headers, we used fulltext indexing on header_text.

Then there was a parts_text table which stored only the text parts of a message. This table had a unique part_id as primary key, a message_id, a sequence and some fields for storing mime types and part-markers, and the part_body field, which stored the entire text. This field was fulltext indexed, however in the long term we planned to run the message's text parts through a lucene indexing process and just store the results in the db, with the actual text part stored as a file in the area designated by the message's storage_id.

Then there was a parts_binary table which stored the same things as the parts_text table but without the part_body. That was stored as a file in the area designated by the message's storage_id. Note that the sequence in this table would be interspersed with the sequence from the parts_text table.

I know this is not a direct answer to your question, but this structure worked for us so maybe it can be of some help to you.


Database design for school management to keep track of current and previous school years

The Student table is a master table; generally speaking, information about the student is static and would not change. I would recommend adding children tables to track the student activities/matriculation in or out of classes. All this detail data would be inserted or updated as time advances.
From the hip, I'd start with something like this:
/* Student demographic data */
CREATE TABLE Student
(
    StudentID  INT IDENTITY(1, 1) PRIMARY KEY
    ,FirstName VARCHAR(100)
    ,LastName  VARCHAR(100)
    ,DOB       DATE
    ,Address   VARCHAR(150)
    ,/* etc */
);

/* Information on classes */
CREATE TABLE Class
(
    ClassID       INT IDENTITY(1, 1) PRIMARY KEY
    ,AcademicYear VARCHAR(25)
    ,StartDate    DATE
    ,EndDate      DATE
    ,CourseNumber VARCHAR(50)
    ,/* etc */
);

/* What action was taken with the class */
CREATE TABLE ClassActivity
(
    ClassActivityID INT IDENTITY(1, 1) PRIMARY KEY
    ,Activity       VARCHAR(50)  /*Enroll, Drop, etc*/
    ,/* etc */
);

/* Classes students interacted with (took action in) */
CREATE TABLE StudentClass
(
    StudentClassID   INT IDENTITY(1, 1) PRIMARY KEY
    ,StudentID       INT
    ,ClassID         INT
    ,ClassActivityID INT
    ,EffectiveDate   DATE
    ,/* etc */
);

/* How students performed in class */
CREATE TABLE StudentClassGrade
(
    StudetnClassGradeID INT IDENTITY(1, 1) PRIMARY KEY
    ,StudentClassID     INT
    ,Grade              VARCHAR(10)
    ,EffectiveDate      DATE
    ,/* etc */
);

It would also be useful to create views to display the student's current/past enrollment as separate queries instead of trying to handle these questions in the data tables. Such as:
dbo.v_StudentCurrentEnrollment
dbo.v_StudentHistoricalEnrollment
dbo.v_StudentCurrentClassLevel

The logic for which class the student is in would be contained in the view, not in the data tables. Structure your tables with solid relationships, then you can ask well formed questions to answer myriad questions.


To what extent do the "non-durability features" affect COPY's performance?

The answer you refer to is talking about bulkloading a DB, not restoring a dump.

synchronous_commit will have minimal to no effect on COPY, since there's only one commit at the end.

COPY can only reduce WAL, not ignore it, and only if wal_level = minimal. Most production systems run with wal_level = hot_standby (in pg10, wal_level = replica) or higher. UNLOGGED tables absolutely do benefit COPY at higher wal_level, but only if you do not later plan on making the table logged. If you do, you're just deferring the WAL cost, since it'll be paid when you convert the UNLOGGED table to LOGGED instead of at COPY time. Use UNLOGGED tables if you can regenerate and reload the data later in case of crash and you don't need it to replicate. pg_restore will restore unlogged tables as unlogged, and logged tables as logged, so it's kind of a moot point.

Don't turn fsync off unless you really know what you're doing, or you don't care about your data. For bulk loading a new blank PostgreSQL instance, wal_level = minimal and synchronous_commit = off are probably going to be just as good. Better, even, since you avoid a whole bunch of WAL writing.

max_wal_size won't make much difference with bulk loading if wal_level = minimal. If you're using a higher WAL level you do need to tune checkpoints for best results. checkpoint_timeout doesn't really matter since it generally only comes into play on idle-ish systems.

So if you're using pg_restore to a system configured for physical replication (hot standby/warm standby/archive PITR) you've got to pay the costs for that, and there's not much you can do to speed things up on top of what COPY already does. pg_restore uses COPY.