How can I time-stamp data in MongoDB? I'm responsible for writing an app to juggle a huge amount of client and business intelligence information. This information involves records for various businesses and metrics like revenue, their web platform, etc. For reporting purposes, it has been decided that the data now must be time-stamped so that we may construct reports like "Foo Inc.'s revenue increased by 200% when they switched to digital television in 2010."

Or something to that effect. We don't really deal with clients that are that far behind the times.

As it stands, the data is stored in MongoDB and its schema (or lack thereof) exactly represents how it's displayed. A document has a name, maybe embedded documents for address information or operations-related stats, etc. I somehow need to add the concept of time-stamping, or versioning to this. The most obvious way to model this would be like so:

key: [{ value: "bar", time: "2012" }, { value: "baz", time: "2011" }]

Where every property is timestamped. Obviously, this breaks the schema, and adds maintenance issues. I've also toyed with using conventions (naming property keys like "Foo (2012-09-31)", and keeping copies of entire collections with a timestamp.

Obviously, this is a very big feature to implement and I was wondering what the best way to implement this would be. What is the most effective and performance-friendly way to add timestamps to data in MongoDB?

Edit:
Yes, I'm looking at versioning, although with all likelihood new data sets will come in large batches and not as often (for instance, every quarter). This is why I was considering using separate collections for each set and timestamping each collection, because the data is lots and far between, but I'd love to know if there's a better, more maintainable way.

Assuming you are using ObjectIDs for your _id column, then you will already have an insert based timestamp.  You can even extract it from the shell directly if you wish.  Of course, you may want to leave this as-is to permanently store your original insertion timestamp, but adding another would be relatively trivial.  Because most drivers have the ability to generate their own ObjectID and you have the published spec you can easily insert your own with a little research, and in the language of your choice.

Since ObjecIDs are the default _id value, they are also going to be a good choice for indexing, well supported in the drivers etc.

Alternatively, in terms of implementing pure timestamps in MongoDB, there is also a BSON Timestamp data type.

Storing this (or the aforementioned ObjectID) in a standard field (say "ts" for example) across documents/values would add overhead to a field/doc as you mention, but you should be able to standardize it easily (field.ts would always have a timestamp value) as well as predict the overhead for each field with a timestamp (8 bytes for a timestamp, or 12 bytes for an ObjectID - for more info see bsonspec.org).

Given that you have two native, known datatypes that provide timestamp functionality, they would be my recommendation as a way forward here.

In terms of adding them to existing data, you can choose to "lazily" add them whenever the data is next touched or issue a batch update, depending on your needs - the benefit of a flexible schema.


I changed a bit

What you want has to be a BEFORE INSERT TRIGGER

The weight i couldn't find in your trigger

Because every variable has to come from somewhere you can define them, get them from the new row, or by a selectl.

You can't access them by writing tabl1.abc or table2.xyz

DROP TRIGGER IF EXISTS calcEGFR;
delimiter //
CREATE TRIGGER calcEGFR BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
DECLARE _age_at_visit INTEGER;
DECLARE _sex VARCHAR(10);
    SELECT age_at_visit, sex INTO _age_at_visit, _sex FROM Table1 WHERE (Table1.patient_wpid = NEW.patient_wpid AND Table1.patient_id = NEW.patient_id);
    SET @creatinine_power := POWER(NEW.creatinine,-1.154);
    SET @age_power :=  POWER(_age_at_visit,-0.203);
    IF _sex = 'female' THEN
        SET @sex_addition := 0.742;
    END IF;
    IF _sex = 'male' THEN
        SET @sex_addition := 1;
    END IF;

    SET @eGFR_value := (175 * @creatinine_power * @age_power * @sex_addition);
    SET NEW.eGFR = @eGFR_value;
END;//
delimiter ;


Is it possible for a trigger to update a row in a table using a value that was deleted? I have a trigger which is updating a column in a different table after the row is inserted:

mysql>delimiter $$

mysql> create trigger status

 after insert on table2

 FOR EACH ROW

 BEGIN update

 table1 inner join table2

 ON table1.id=table2.id

 set table1.state=0 ;

Now I want to reset the table1.state=1 whenever the same value gets deleted. Is this possible?

If I understand your question right, what you want is an after delete trigger:

DELIMITER $$
CREATE TRIGGER ad_foo AFTER DELETE on foo
FOR EACH ROW
BEGIN
  update table1 SET table1.state=1 WHERE id=OLD.id;
END$$
DELIMITER ;

Example with just the insert: http://sqlfiddle.com/#!2/974b4/1

Example with insert and delete: http://sqlfiddle.com/#!2/0bf72/1


What model design supports both data store backed and free form entries? I have an application where users are tracking a workflow around producing a derivative work from an existing Book. The derivative work, we'll call "BookDerivative" must have a book that it relates to from a data model perspective. The existing system which stores the Book info (Title, Author, etc) must be considered an "optional third party" system that can be interchanged with another system in the future (which I've abstracted via IBookService).

My application must provide two options for users when they create a BookDerivative:

Select an existing book from the IBookService provider (via lookup); OR
Create a "free form" entry for the Book which would NOT be linked to the IBookService provider. This allows the user to create a BookDerivative for a Book title not found by the IBookService lookup.

My solution to support this is to include the needed columns from Book on my BookDerivative table/entity (BookTitle, BookAuthor, BookId) where BookId will only be present IF the book comes from the third party system. This way if the book is "free form" I would only populate BookTitle and BookAuthor.

Future accesses of BookDerivative do not need to look to the IBookService. The essential book info should never (or rarely) change in the third party system and I'm prepared to except the fact that it is theoretically possible for information to be out of sync.

There will be no data-integrity between the two systems and no FK. My application cannot assume the underlying data model of the Book system but instead must rely on a defined model and IBookService lookup.

My question remains, is this a good way to solve this problem? What recommendations or pitfalls might exist? I would think this is a common requirement, have you used a different or better solution in the past?

Based on the details you've provided I'd say it sounds fine.

Things that may or may not be a problem based on further information.

(1) Even though you say there is no relationship between the two systems and no foreign key, if you were to switch third part systems, you would be left with a bunch of meaningless ids for books that were added from the previous 3rd part system.  Is that okay?  If not, you may need to add a lookup table of third party systems that can related to the IDs in the Book table so you can later tell which system they came from.

(2) If the only reason for the IDs from the third party system to be there is to check for their existence, maybe a simply IsFrom3rdParty or IsFreeForm boolean column would be sufficient.  It would seem to meet all the requirements you lay out here and would be simpler than the ID to implement and would better convey the actual information you have (which is simply that it came from a 3rd party system.  You can't guarantee that the ID will relate to anything especially if the backend system has changed)

Otherwise, yes it sounds good.


Index with high fragmentation percentage I have a very large table with enormous percentage of index fragmentation( almost 98 % !). Obviously, it needs index rebuild. Main problem is my SQL Server is in Standard edition, thats why I cannot perform online index rebuild. The table has very high production priority and I cant afford to get it offline. I want to try index reorganize several times, as it can be done online. My question is: do you think several reorganizes will be efficient way in order to low the fragmentation percentage ?

Short definition of Index Reorganize, made by Kendra Little:

  Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Even single reorganize should be efficient in Your case, but remember that it depends on Your data (how well pages get filled with row data).

Another advantage of reorganize in Your situation, other than being online operation, is that it can be stopped anytime (while index rebuild would have to rollback), so it's great to combine with Ola Hallengren's Maintenance Solution options: TimeLimit and WaitAtLowPriorityMaxDuration with WaitAtLowPriorityAbortAfterWait.

One disadvantage of reorganize I could think of, is that it can't be used to rebalance data between db files (for which index rebuild is often used).

As for fragmentation itself, even that high level shouldn't have much of impact on performance. It should free up a lot of space in data files, though.
Still, the best approach is to have index maintenance scheduled regularly, just don't stress over it.

Kendra Little wrote another article about dealing with fragmentation recently, it's worth reading.


Adding multiple constraints I am trying to add constraints and I think i have an error in the code as its saying i cant add mutlple constrints. What is best way to add mutlple constraints

I am using MSQL Server 2008 r2

CREATE TABLE [erp].[table](
    [SysRevID] [timestamp] NOT NULL,
    [SysRowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),(0))+CONVERT([binary](6),getutcdate(),(0)),(0))),
    [Company] [nvarchar] (8) NOT NULL DEFAULT (''),
    --THE ABOVE ARE DEFAULT FIELDS.  LEAVE THEM ALONE.  ADD FIELDS BELOW
    [AffilationNum] [int] NOT NULL DEFAULT ((0)),
    [VendorNum] [int] NOT NULL ,
    [CreationClass]   [nvarchar] (10),
    FOREIGN KEY ( VendorNum ) REFERENCES Vendor (VendorNum),
    PRIMARY KEY (AffilationNum),
CONSTRAINT [idxEvAffiliation] PRIMARY KEY CLUSTERED
(
    [Company] ASC,
    [AffilationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

go

 CREATE NONCLUSTERED INDEX [idxAffilationInterestedParty] ON [erp].[EvAffiliation]
 (
 [Company] ASC,
 [VendorNum] ASC
 )

GO

ALTER TABLE [erp].[EvAffiliation] SET (LOCK_ESCALATION = DISABLE)
GO

Edit
 I am not sure how to create an additional index i dont want the second one to be on primary key its just an additional non clusted index i want for the second one.

This is the errror i am getting i am trying to create one pimary index and one non clustered index

  Msg 8110, Level 16, State 0, Line 2
  Cannot add multiple PRIMARY KEY constraints to table 'erp.EvAffiliation'.

The problem is with the erp.table, you are defining primary keys multiple times.

Try the following code:

CREATE TABLE [erp].[table](
    [SysRevID] [timestamp] NOT NULL,
    [SysRowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),(0))+CONVERT([binary](6),getutcdate(),(0)),(0))),
    [Company] [nvarchar] (8) NOT NULL DEFAULT (''),
    --THE ABOVE ARE DEFAULT FIELDS.  LEAVE THEM ALONE.  ADD FIELDS BELOW
    [AffilationNum] [int] NOT NULL DEFAULT ((0)),
    [VendorNum] [int] NOT NULL ,
    [CreationClass]   [nvarchar] (10),
    FOREIGN KEY ( VendorNum ) REFERENCES Vendor (VendorNum),
CONSTRAINT [idxEvAffiliation] PRIMARY KEY CLUSTERED
(   [Company] ASC,
    [AffilationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

Refer to Primary Key Constraints and Composite Primary Keys.


How do I copy databases from Postgres 8.4 to Postgres 9.1? My trac server recently started having issues. It was running a very outdated version of Ubuntu, and I was unable to properly use apt-get to correct the issues I was having. So instead I backed up the machine and did a clean install of Ubuntu 12.04.

Now I have a new server with Postgres 9.1 properly installed and running. I want to get my old Postgres 8.4 database from the backup copied over and setup on the new install. How do I go about doing this?

What I have done so far is to copy the old Postgres 8.4 data and bin directories from the backup (/var/lib/postgresql/8.4/main and /usr/lib/postgresql/8.4/bin) to /tmp/postgres.old and /tmp/postgres-bin.old on the new install. I stopped Postgres 9.1 using service postgres stop and ran the command:

sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_upgrade \
  --old-datadir=/tmp/postgres.old/main/ \
  --new-datadir=/var/lib/postgresql/9.1/main/ \
  --old-bindir=/tmp/postgres-bin.old/postgresql/8.4/bin \
  --new-bindir=/usr/lib/postgresql/9.1/bin

As described in the Postgres documentation. This seemed like it would work great, except I received the error:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

unable to connect to old postmaster started with the command: "/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl" -w -l "/dev/null" -D "/tmp/postgres.old/main" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1
Failure, exiting

The issue seems to be that it's trying to start the old server, and that doesn't work because it's so very old and not installed properly. Trying to start it manually reveals the problem:

root@mission:/tmp# sudo -u postgres /tmp/postgres-bin.old/postgresql/8.4/bin/postgres
/tmp/postgres-bin.old/postgresql/8.4/bin/postgres: error while loading shared libraries: libssl.so.0.9.8: cannot open shared object file: No such file or directory

So, How can I restore old Postgres 8.4 databases into a new Postgres 9.1 install, and update them?

It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default data directory is). From there, I'd do a pg_dump on the database(s) in question and then restore them to the new server. That would probably give you the cleanest environment.

So, here are what I see as your options:

Using pg_upgrade

On The New Server (pg_upgrade)

Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt.
Attempt to start the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/
If that works, stop the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/
Now try your pg_upgrade script

Without Using pg_upgrade

Create a VM or use an old, decommed server and install postgresql 8.4
Initialize your server using initdb (or pg_createcluster)
Copy your backed up data over that which was just created
Start postgresql
Backup the data using pg_dump
Copy the dump file to the "new server"
Restore to a new database on the new server


Postgres LIKE query using a GiST index is just as slow as a full scan What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but still it takes 5 seconds to return. Expected would be a few (like 100) milliseconds.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles  WHERE  parentpath LIKE 'somevalue'

When using %% in the query, it takes the not that long, even when using sequential scan (?!)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles  WHERE  parentpath LIKE '%a%'

I also have the same setup for the name (filename) column, when executing a similar query on that one, it only takes half of the time, even when using %%:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles  WHERE  name LIKE '%a%'

What I already tried cannot be written here in short words. Whatever I do, it gets slow starting from about 1 mio rows. As there is basically never anything deleted, of course vacuuming and reindexing does not help at all.
I cannot really use any other type of search than LIKE %% and a GIN or GiST index because I need to be able to find any character in the columns of interest, not only "words for a specific human language".
Is my expectation that this should work in around 100 milliseconds even for many million more rows that wrong?
Further information
DB create script
Re-try without any text or other index at all, 1.7 mio unique entries
EXPLAIN ANALYZE select * from residentfiles where name like '%12345%'
Seq Scan on residentfiles  (cost=0.00..78162.76 rows=33225 width=232) (actual time=0.076..3195.965 rows=45301 loops=1)
  Filter: ((name)::text ~~ '%12345%'::text)
  Rows Removed by Filter: 1604780+
Planning time: 0.596 ms
Execution time: 3318.595 ms

Try with Triagram gin index:
CREATE INDEX IF NOT EXISTS  name_gin_idx ON residentfiles USING gin (name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS  parentpath_gin_idx ON residentfiles USING gin (parentpath gin_trgm_ops);
CREATE INDEX IF NOT EXISTS  ext_gin_idx ON residentfiles USING gin (extension gin_trgm_ops);
EXPLAIN ANALYZE select * from residentfiles where name like '%12345%'

Aggregate  (cost=53717.59..53717.60 rows=1 width=0) (actual time=1694.223..1694.224 rows=1 loops=1)
  ->  Bitmap Heap Scan on residentfiles  (cost=341.89..53631.82 rows=34308 width=0) (actual time=72.010..1615.007 rows=46532 loops=1)
        Recheck Cond: ((name)::text ~~ '%12345%'::text)
        Rows Removed by Index Recheck: 111
        Heap Blocks: exact=46372
        ->  Bitmap Index Scan on name_gin_idx  (cost=0.00..333.31 rows=34308 width=0) (actual time=52.287..52.287 rows=46643 loops=1)
              Index Cond: ((name)::text ~~ '%12345%'::text)
Planning time: 10.881 ms
Execution time: 1694.755 ms

Try with varchar_pattern:
CREATE INDEX idx_varchar_pattern_parentpath ON residentfiles (parentpath varchar_pattern_ops);
CREATE INDEX idx_varchar_pattern_name ON residentfiles (name varchar_pattern_ops);
CREATE INDEX idx_varchar_pattern_extension ON residentfiles (extension varchar_pattern_ops);
EXPLAIN ANALYZE select * from residentfiles where name like '%12345%'

Aggregate  (cost=89718.74..89718.75 rows=1 width=0) (actual time=1995.206..1995.207 rows=1 loops=1)
  ->  Seq Scan on residentfiles  (cost=0.00..89574.98 rows=57507 width=0) (actual time=0.060..1913.114 rows=52232 loops=1)
        Filter: ((name)::text ~~ '%12345%'::text)
        Rows Removed by Filter: 1852103
Planning time: 8.280 ms
Execution time: 1995.255 ms

In my opinion if you do not share how you did the test, it's very hard to give you an answer. Let see an example of what I mean. Sorry for I used a postgres 11 but the conclusions are the same:

This is a new db, there is nothing running against the instance:

test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
test=# create table test_trgmidx (col1 varchar(30), col2 varchar(50));
CREATE TABLE
test=# CREATE INDEX trgm_idx_test_col2 ON test_trgmidx USING gist (col2 gist_trgm_ops);
CREATE INDEX

Inserting 500000 rows using a very simple loop.

test=# \i loop_long.sql
DO
test=# select count(1) from test_trgmidx;
 count
--------
 500000
(1 row)

test=# select * from test_trgmidx limit 20;
col1        |    col2
------------+------------
 ABCD1EFGH  | abcd1efgh
 ABCD2EFGH  | abcd2efgh
 ABCD3EFGH  | abcd3efgh
 ABCD4EFGH  | abcd4efgh
 ABCD5EFGH  | abcd5efgh
 ABCD6EFGH  | abcd6efgh
 ABCD7EFGH  | abcd7efgh
 ABCD8EFGH  | abcd8efgh
 ABCD9EFGH  | abcd9efgh
 ABCD10EFGH | abcd10efgh
 ABCD11EFGH | abcd11efgh
 ABCD12EFGH | abcd12efgh
 ABCD13EFGH | abcd13efgh
 ABCD14EFGH | abcd14efgh
 ABCD15EFGH | abcd15efgh
 ABCD16EFGH | abcd16efgh
 ABCD17EFGH | abcd17efgh
 ABCD18EFGH | abcd18efgh
 ABCD19EFGH | abcd19efgh
 ABCD20EFGH | abcd20efgh
(20 rows)

Now I restart the instance to have a clean buffer cache, then I run the explain for the first select twice, to see how cache is "disturbing" our results:

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30   rows=1 width=28) (actual time=4.586..4.912 rows=1 loops=1)
  Output: col1, col2
  Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
  Buffers: shared hit=19 read=237
 Planning Time: 0.303 ms
 Execution Time: 4.934 ms
(6 rows)

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30 rows=1 width=28) (actual time=2.096..2.298 rows=1 loops=1)
 Output: col1, col2
 Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
Buffers: shared hit=232
Planning Time: 0.072 ms
Execution Time: 2.317 ms
(6 rows)

It's clear that the first run need to retrive rows from disk (read=237) while the second one need only to access buffer cache (shared hit=232, NO reads).
Now let's do the same for the second select, restart the instance and run the explain twice:

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=0.039..89.906 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared read=3676
 Planning Time: 0.719 ms
 Execution Time: 94.942 ms
(7 rows)

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=0.015..61.741 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared hit=3676
 Planning Time: 0.081 ms
 Execution Time: 65.878 ms
(7 rows)

As you can see, now the reads come from disk the first run and from buffer the second one. Things are even more complicated because of OS buffers. Is is possible to clean OS cache and re-run everithing having back different results:

# free
              total        used        free      shared  buff/cache   available
Mem:        7914604      929920     4105056       93960     2879628     6748994
Swap:       4063228           0     4063228

# echo 3 > /proc/sys/vm/drop_caches

# free
              total        used        free      shared  buff/cache   available
Mem:        7914604      802204     6846392       93960      266008     6951156
Swap:       4063228           0     4063228
#

Look at column buff/cache as it drop down from 2879628 to 266008. Now run the explain  again (twice for any select):

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#  explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30 rows=1 width=28) (actual time=130.858..140.403 rows=1 loops=1)
   Output: col1, col2
   Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
   Buffers: shared hit=19 read=237
 Planning Time: 38.448 ms
 Execution Time: 140.466 ms
(6 rows)

test=#  explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30 rows=1 width=28) (actual time=4.386..4.759 rows=1 loops=1)
   Output: col1, col2
   Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
   Buffers: shared hit=232
 Planning Time: 0.115 ms
 Execution Time: 4.787 ms
   (6 rows)

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=9.214..161.243 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared hit=1 read=3675
 Planning Time: 0.090 ms
 Execution Time: 165.354 ms
(7 rows)

test=#  explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                          QUERY PLAN
       ----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=0.013..62.043 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared hit=3676
 Planning Time: 0.078 ms
 Execution Time: 66.101 ms
   (7 rows)

You can check how different are now the statistics, first run read form disk, second one from buffer cache. All these lines only to say that in my opinion and for my experience, it is almost impossible to have a clear understanding about what  is happening in you environment without all the information around how you did any single explain. And even having all the informations, it is sometimes very difficult to find the answer because of all the variables playing  a role.
My 2 cents


How to deal with fragmentation in PostgreSQL? What are best practices for dealing with fragmentation in PostgreSQL?

Example:
I have a multi-tenant database whose workload includes on-the-fly aggregation for reports.
CREATE TABLE account (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE widget (
    id bigserial PRIMARY KEY,
    account_id int NOT NULL REFERENCES account (id),
    length numeric NOT NULL,
    weight numeric NOT NULL,
    color text NOT NULL,
    shape text NOT NULL
);

CREATE INDEX ON widget (account_id);

I want to run account-scoped queries on widget, e.g.
SELECT color, count(*), sum(weight) AS weight
FROM widget
WHERE account_id = 42094 AND 3 < length
GROUP BY 1

Suppose there are 5,000 records in account and 100,000,000 records in widget.
Since PostgreSQL lacks clustered indices, an individual account's 20,000 widget records get scattered all over the entire table. With enough records in the table, PostgeSQL will have to read 20,000 pages for a query that under other circumstances would be very fast.

This would seem to be a common sort of situation...how do users deal with this?

CLUSTER widget USING widget_account_id_idx;

That will rewrite the table in index order, and your query will become faster.
There are down sides:

during CLUSTER, the table will be inaccessible

the order is not maintained, so you have to run CLUSTER everynow and then


How To Fix High DTU Usage on Specific Query I have a query that runs every few seconds on a Microsoft SQL Azure Database. It is from a web job that checks if there are actions that need to be done based on rows in a specific table (its a queueing table). Based on the image attached, does anyone have any suggestion of how i could reduce the DTU usage for the query in question, it is causing the database to reach 100% CPU.

Not having the full table definition, I'm going to take a stab at what it might be. Based on the screenshot in your question, the table could look something like this:

CREATE TABLE dbo.TaskSchedulerItem (
    TaskSchedulerItemID int IDENTITY(1,1),
    TaskSchedulerID int,
    ItemStatus varchar(10),
    DateLastUpdated datetime2(0),
    CONSTRAINT PK_TaskSchedulerItem PRIMARY KEY CLUSTERED (TaskSchedulerItemID)
);

And just for funsies, lets throw some random sample data in there:

INSERT INTO dbo.TaskSchedulerItem (ItemStatus, TaskSchedulerID, DateLastUpdated)
SELECT TOP 10 'Pending', column_id, GETDATE()
FROM sys.columns
UNION ALL
SELECT TOP 10000 'Complete', column_id, GETDATE()-1
FROM sys.columns;

And now we can run our query against that data:

DECLARE @CurrentDateTime datetime2(0)   = GETDATE(),
        @TaskSchedulerID int            = 4,
        @TaskSchedulerItemID int;

SELECT TOP 1 @TaskSchedulerItemID = TaskSchedulerItemID
FROM dbo.TaskSchedulerItem
WHERE (
    ItemStatus IN ('Pending','Failed')
    OR
    ItemStatus = 'IN PROGRESS' AND DateLastUpdated < DATEADD(MINUTE, -1, @CurrentDateTime)
)
AND TaskSchedulerID = @TaskSchedulerID
ORDER BY DateLastUpdated;

SELECT @TaskSchedulerItemID;

And we can see that this query does a Clustered Index Scan:

I don't know for sure that your query is doing a Clustered Index scan, but that is going to be my guess. We're only looking for one row--so ideally, we would be able to do a seek to that one row (or, at least fewer rows). The ORed criteria makes that a little bit trickier, so having a single index that always gets us the exact row is a bit challenging. Instead, we can just try to get close.

We can create an index on TaskSchedulerID (we always filter on that!), and DateLastUpdated (we always sort on that!), and ItemStatus (we filter on that, but in the OR, so it's a little trickier for SQL to search on that):

CREATE INDEX ix_TaskSchedulerId_ItemStatus
    ON dbo.TaskSchedulerItem(TaskSchedulerID, DateLastUpdated, ItemStatus);

Huzzah! It's doing a seek!

That should make the query pretty darned fast... but what's that little warning symbol?

That's the "get close" part I was talking about. In my random sampling, the index seek was able to seek to 280 rows, then did some residual I/O to find the one row it needed that fit the critera. Based on the index I created, it is going to seek to the supplied @TaskSchedulerID, then start running along the DateLastUpdated column (remember, that's the ORDER BY on the query, then do "residual I/O" to figure out that ItemStatus criteria.

Depending on your data, that might be "good enough" to fix your performance issues. Or you might need to be a bit more clever. Since I don't know your actual schema, indexing, or data distribution, I can't hazard a better guess.

But really, you just need an index that will make your query go a lot faster, so that you use fewer DTUs.


Sybase ASE find running process I run UPDATE INDEX STATISTICS on some HUGE table. And it runs already about 1 hour. Is there some procedures/ways to find the percentage of executing this command? sp_who, sp_sysmon don't help. Thanks.

There is no direct way of doing it in SybaseASE (as opposed to sql server - which exposes DMV data).

You can get close to see if your UPDATE INDEX STATISTICS is getting CPU, Physical IO and is not being blocked.

I use below query :

select l.spid
    ,p.status as status
    ,Db_name(l.dbid) as dbname
    ,p.hostname as hostname
    ,p.program_name as program_name
    ,substring(Object_name(l.id, l.dbid), 1, 30) as object
    ,p.blocked
    ,case
        when type = 1
            then "Exclusive table lock"
        when type = 2
            then "Shared table lock"
        when type = 3
            then "Exclusive intent lock"
        when type = 4
            then "Shared intent lock"
        when type = 5
            then "Exclusive page lock"
        when type = 6
            then "Shared page lock"
        when type = 7
            then "Update page lock"
        when type = 8
            then "Exclusive row lock"
        when type = 9
            then "Shared row lock"
        when type = 10
            then "Update row lock"
        when type = 11
            then "Shared next key lock"
        when type = 256
            then "Lock is blocking another process"
        when type = 512
            then "Demand lock"
        end as "TYPE_OF_LOCK"
    ,l.page "Page"
    ,Substring(p.cmd, 1, 16) as executing_command
    ,p.physical_io as "PHYSICAL_I/O"
    ,p.cpu as cpu
from master..syslocks l
    ,master..sysprocesses p
where l.spid = p.spid


Recalculate Time Dimension in BIDS I'm using BIDS to create a cube in SSAS 2008 r2.

I used the wizard to create a time dimension.  Part of that configuration is selecting a date range.

After creation, I realized that I needed a wider date range than I originally specified.

I did find that I could open the dimension, go to properties, and redefine the date range there.  I saved and reprocessed the dimension, but the actual date range in the table is not growing to include the newly-added dates.

Is there another way to get this time dimension to grow or do I need to recreate it from scratch?

Is your Time dimension connected to a source or is it a Server time dimension which has no table for it in the Data source

because usually when you generate a time dimension using BIDs it gives you these two options

and it looks like in your case you dont have a table for it in the datasource, i mean you chose a server time dimension which creates a dimension in Analysis server itself.

so you wont be able to append or add more dates, you will need to recreate it from scratch.

but if you chose the other option "Generate a time table in the data source" This option creates a time dimension table in the underlying data source and populates the same with data based on the inputs provided.

you should have access to be able to create a table in your data source

in this case you can add records to the table and reprocess it.

here is an easy script to create and fill a time dimension table
and later you can generate your time dimension in the cube from this table

http://microsoft-ssis.blogspot.com/2011/01/create-and-fill-time-dimension.html

and here is 2 articles about generating a time dimension and its options

http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/383/ssas-considerations-for-time-dimension-part1

http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/385/sql-server-ssas-considerations-for-time-dimension-part-2


Secondary data file is in 'Recovery Pending' state, but database is online I got an issue. I have a database (Say, DBASE) which have three files - Two data files (namely D:\Data1.mdf and D:\Data2.ndf) and a log file (E:\log.ldf) and all are in different file group. The secondary data file i.e., Data2.ndf is in recovery pending state. Remaining two db files are good and my database is online. When I took a look on database properties (GUI), I found there the size of Data2.ndf as 0MB where it's actual size is 10GB. We have given a try in below methods to fix the issue, but no luck.

resetdbstatus
Taking database to offline, then back to online.
Detach and attach operations
No errors from checkdb, unfortunately we don't anything in error log (as they are recycled)

Can someone help me in fixing the issue please?

FYI - Version : SQL 2012 SP1 Enterprise.

Since you mentioned earlier that your DB gets restored daily and it has different filegroups, it seems to me the case of piecemeal restore. You need to check the script used for restore and make sure it is recovering all the filegroups present in the DB backup.

It looks like currently the script is only recovering primary filegroup and skipping the others which is causing the DB to recover but without the skipped filegroups. So you can query all your data which is present in PRIMARY filegroup but not the skipped ones. Just recover all the filegroups using the example given in below link and you should be fine.

http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/sql-server-filegroup-piecemeal-restores-1/


Historical reporting from the database with Reporting Services? We run a job tracking service as part of the company software.  Reports are generated by SQL Server (Standard) Reporting Services usually daily but often as and when needed before meetings etc.

As the jobs progress their status changes and the details of the reports change.  Due to the legacy of the front end application and the database we do not store as many milestones as needed for managers to look back at historical reports.

Managers quite often ask “Can we see where XYZ was last week?” or things like “Let’s have a look how Project1 was performing last June?” but running the reports on the Project1 database backup all show 100% complete as it is now finished.

What is the best way to enable historical reporting at a daily level with SQL Server Standard?

Ideally we’d like to keep 3 months of  5 different reports stored daily if possible?

If you have lost the historical data (i.e. it gets overwritten rather than stored as a transaction history) then you can't reconstruct the historical state - full stop.  However, there are a few approaches to dealing with this.

Build an automated process that queries the system through SSRS or some other mechanism and saves the historical reports to an archive somewhere.  SSRS exports a web service API that lets you run reports (paramaterised if necessary) and then save the output to a file.  This can be operated by anything capable of consuming it.
Build an ETL process that takes a snapshot of the data and compares it with the previous snapshot position.  Where changes are detected they can be written out to a historical table.  If you have the option of putting triggers on the source database then you can make triggers that write out the audit logging information instead.
If you need something more elaborate then you could build a data mart - do a google search on 'type 2 slowly changing dimensions' for some ideas on how to implement this.  You can use a change capture mechanism like that described in (2) to source the data as necessary.  Either you can store the transaction history and reconstruct the status in the reports, or you can make a periodic snapshot of the data with its status at that point.

Note that Standard Edition doesn't support changed data capture.


config file and parameters for ndb_desc I'm trying to execute ndb_desc from API client. The config and parameters are as below:

my.cnf in mysqld:

[mysql_cluster]
ndb-connectstring=192.168.1.1,192.168.1.2

server-id=5
log-bin=mysql-bin

config.ini in mgmd: (part of it)

-- ...
[mysqld]
hostname=192.168.1.5
--- ...

Situation: there are 2x mgmd, 2x data-node, and 2x mysqld.

Parameters tried to call ndb_desc: # ndb_desc -c 192.168.1.1 --ndb-nodeid=7 -d db_name -t tb_name

Above response with error: ndb_desc: [ERROR] unknown variable 'server-id=5

On changing my.cnf, removing these two lines of server-id and log-bin, then the error response after calling ndb_desc will be as follow:

Unable to connect to management server.
NDBT_ProgramExit: 1 - Failed

There is no firewall, ping to the management is running fine.

So the question: how to exec ndb_desc (with correct parameters)? what is the correct my.cnf/config.ini for setting up the node-id?

Some links i have tried to check before posting this:
mysql-cluster-work-well-but-ndb-desc-doesnt-work, mysql:ndb_desc, mysql:connection-string, grokbase, and several hours of googling around.

update1: changed the config.ini on mgmd for emtpy mysqld section, ndb_mgm -e show output:

[mysqld(API)]   3 node(s)
id=5    @192.168.1.5  (mysql-5.7.23 ndb-7.6.7)
id=6    @192.168.1.6  (mysql-5.7.23 ndb-7.6.7)
id=7 (not connected, accepting connect from any host)

ndb_desc still has the same response: Unable to connect to management server.

update2: from mgmd node, saw this log: WARNING  -- Failed to allocate nodeid for API at 192.168.1.5. Returned error: 'Id 7 already allocated by another node.

update3: changed config.ini, added [api] nodeid=7 host=192.168.1.5. log still the same: WARNING  -- Failed to allocate nodeid for API at 192.168.11.51. Returned error: 'No free node id found for mysqld(API).

Here are the changes i made to make this work:

add empty api node at the end of mgmd config.ini: [api]
stop both of ndbd from mgmd: ndb_mgm -e "3 stop" | "4 stop"
stop both of mgmd: ndb_mgm -e "1 stop" | "2 stop"
start mgmd 1: ndb_mgmd --initial --reload --config-file=/var/lib/mysql-cluster/config.ini
start mgmd 2: ndb_mgmd --initial --config-file=/var/lib/mysql-cluster/config.ini
start ndbd manually: ssh to the machine and run ndbd
wait until all node connected
make sure my.cnf at mysql node is having: [mysql_cluster] ndb-connectstring=192.168.1.1,192.168.1.2, or can add -c on ndb_desc param later
run ndb_desc at mysql node: ndb_desc -p -d db_name -t tb_name

the important missing part after adding empty api node is restarting the data node

link to source: No free node id found


Can't connect to Azure SQL with error 18456 I'm trying to grant access to my Azure SQL database (on an elastic pool) to an external developer using SQL Authentication.
In the database (not master) I ran the following commands:
create user [UserName] with password='Password', default_schema=[dbo]
alter role db_owner add member [UserName]
He can't access the database, he gets the error:
Login failed for user 'UserName'. (.Net SqlClient Data Provider) Error Number: 18456 Severity: 14 State: 1 Line Number: 65536
Both our IPs have been whitelisted, but only I can login to the database with those credentials. Any idea where things are going wrong?

There are a bunch of reasons their attempt to log in could fail, chief among them being they are just not typing the username or password correctly
Check the sys.event_log DMV for a more detailed reason for the login failure.  You should be able to filter to connection_failed events:
SELECT *
FROM sys.event_log
WHERE event_type = 'connection_failed'
ORDER BY start_time DESC;

This will let you know if they are having firewall issues (blocked_by_firewall), timeouts (client_close), typos (invalid_login_name), etc.


Rebuild Very Large Primary Key Index I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes.

I'm able to rebuild all other indexes with online=on option and it won't affect performance. The size of one of the PK Clustered indexes is greater than 200GB, and for this one a REBUILD...WITH (ONLINE=ON) causes locking.

We do have users from all timezones accessing the site so really, I'm unable to find a time where I can rebuild the index offline.

What is the best strategy to rebuild large indexes without having a downtime in the site?

I believe reorganize won't help since fragmentation is 99%. The problem is that the table gets locked even with online. The main problem is that the index is greater than 200GB. The primary key is an integer.

Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question.

First, and I don't know if you're doing this or not, but please don't assume that high fragmentation levels on the index are always going to cause poor performance.  Stale statistics (e.g. sys.dm_db_stats_properties) and high amounts of white space per page (i.e. avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats dmv) hold more relevance regarding performance issues than fragmentation alone.  Yes, highly fragmented indexes will generate more read-aheads and you typically do see stale statistics and higher levels of white space per page coupled with fragmentation, but fragmentation isn't directly tied to query plan optimizations nor how much memory loading the index from disk will actually consume.  Query plans are affected by statistics and your memory footprint bloats with more white space.  For instance, an index that is 99% fragmented but has less than 5% avg. white space and up-to-date statistics is likely not causing you drastic performance issues as compared to either a bad execution plan as a result of stale statistics or constant paging of an index that's too big to fully fit in memory because there's a significant amount of white space present per page.

If fragmentation is truly an issue, you can reduce it, ONLINE, by issuing an ALTER INDEX ... REORGANIZE statement as identified by Dan Guzman in the comments.  This won't create as streamlined an index as a REBUILD operation will, but it will reduce your fragmentation.  The key here is to identify windows of lower usage on your database and run it then.  This could be 15 minutes or multiple hours, obviously the longer the better, but the key here is this operation doesn't rollback and retains any progress made even if you kill it mid-execution.

If, in a perfect world where your fragmentation was eliminated, would it make more sense to utilize partitioning on this table?  Azure SQL Database does allow for table partitioning and Microsoft has a great article outlining some Partitioning strategies for Azure SQL Database.  If your data is non-volitile, partitioning it may help reduce maintenance needs, and if coupled with Table Compression, you may even be able to reduce your overall storage footprint as well.  Alberto Murillo's earlier answer alludes to utilizing Horizontal Partitioning based on a data region, and this approach may help create some maintenance windows for you as your data would be more regionally specific instead of global.

Transitioning to a partitioned table won't be easy with your current absence of maintenance windows, but you may be able to utilize an approach outlined by Maria Zakourdaev which uses Partitioned Views over the top of your current table and a new partitioned table to start partitioning future data.  As time goes on (and hopefully your old data is purged), you can eventually transition fully over to the partitioned table.  Again, I don't know your data or application, but maybe this approach is something you can employ.


How can it be possible that sum operation is so much faster than count? When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below:

select sum(t.amount * t.exchange) from table t;

Finalize Aggregate  (cost=658027.39..658027.40 rows=1 width=8) (actual time=2391.248..2391.248 rows=1 loops=1)
  Buffers: shared hit=25366 read=550786 dirtied=18 written=18
  I/O Timings: read=1978.454 write=0.205
  ->  Gather  (cost=658027.17..658027.38 rows=2 width=8) (actual time=2391.100..2391.229 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=25366 read=550786 dirtied=18 written=18
        I/O Timings: read=1978.454 write=0.205
        ->  Partial Aggregate  (cost=657027.17..657027.18 rows=1 width=8) (actual time=2377.613..2377.613 rows=1 loops=3)
              Buffers: shared hit=24961 read=550759 dirtied=18 written=18
              I/O Timings: read=1977.930 write=0.205
              ->  Parallel Seq Scan on odeme_kaydi ok  (cost=0.00..622181.24 rows=4646124 width=16) (actual time=0.084..1972.061 rows=3688816 loops=3)
                    Buffers: shared hit=24961 read=550759 dirtied=18 written=18
                    I/O Timings: read=1977.930 write=0.205
Planning time: 0.279 ms
Execution time: 2408.745 ms

select count(t.id) from table t;

Aggregate  (cost=489270.14..489270.15 rows=1 width=8) (actual time=12256.560..12256.560 rows=1 loops=1)
  Buffers: shared hit=6902688 read=372054 dirtied=32
  I/O Timings: read=3067.841
  ->  Index Only Scan using pk_odeme_kaydi_id on odeme_kaydi ok  (cost=0.43..461393.25 rows=11150756 width=8) (actual time=0.169..11583.174 rows=11066478 loops=1)
        Heap Fetches: 4085161
        Buffers: shared hit=6902688 read=372054 dirtied=32
        I/O Timings: read=3067.841
Planning time: 0.110 ms
Execution time: 12256.609 ms

Note: amount field is always greater than 0 in the table.
Postgresql version is 9.5.

Is it normal or is there anything tricky?

Typically, there is not much difference between sum() and count(), the number of data pages that have to be read are the dominant factor for performance, but count() is generally faster, especially count(*):

For absolute performance, is SUM faster or COUNT?

Your sum uses Parallel Seq Scan, which turns out to be much faster. The count, on the other hand uses an Index Only Scan, which is typically the fastest way, but no Parallel support there. That's what probably makes the difference. (May be a limitation of pg 9.5, not sure.)

You are using Postgres 9.5. Parallel queries were pretty new and the cost estimation not too accurate - similar considerations apply for index-only scans, to a lesser degree. So the planner makes at least one bad cost estimate: 489270.15 for the count with and Index Only Scan, vs. 658027.40 for the sum - for which an index-only scan is probably not possible (no index covering amount and exchange). Maybe you also have cost settings that don't reflect reality well, which is typically involved in bad estimates. See:

Keep PostgreSQL from sometimes choosing a bad query plan

And I see Heap Fetches: 4085161 for the count, which strikes me as surprisingly high for an index-only scan on 11M rows. Not sure why that is, maybe a VACUUM ANALYZE on the table might change things.

All that aside, while t.id is defined NOT NULL, use count(*) instead of count(t.id), which is a bit faster, since Postgres does not have to look into the stored (index) tuple at all. The mere existence of rows is sufficient.

And upgrade to a current version of Postgres, where either variant will be substantially faster. There have been major improvements to big data and parallel queries.

BTW, if the count does not have to be exact, there are much faster options:

Fast way to discover the row count of a table in PostgreSQL


Can you create a new filegroup for a table in a database that is live? I have a SQL Server 12.0.5223.6 instance with a database on it.

I'm wondering if I can create a filegroup for one (or more) tables while it's running? Or does it have to have no data in the tables?

I could take the DB offline, but I can't take it offline for long. Right now all the data is inside Data.mdf. I'm hoping to split out some data from maybe three or four tables into separate filegroups.

Could this be done?

You can definitely add a filegroup to a database while it's running, but it will have no data until you create a table in it, or rebuild a table onto the new filegroup.

This would mean dropping the clustered key on the table, and re-creating it on the filegroup:

CREATE CLUSTERED INDEX IK_Example ON Example ( id ) ON NewFileGroup;

OR

ALTER TABLE Example ADD CONSTRAINT PK_Example PRIMARY KEY CLUSTERED (id) ON NewFileGroup;

The Microsoft Docs on this one is pretty good.


Optimizing a user retention query I'm trying to automatically calculate a table for user retention, so that the end result is something like:

Week | %
0    | 100
1    | 50
2    | 35

For now I'm not bothering with the % and just trying to get a count of users, i.e. users that came back on week 1, week 2, etc.

I have an accounts table and an action table. Currently I don't have indexes in either of these. Both have a user_email that stores the associated user, and both have a timestamp field that's a TIMESTAMP WITH TIME ZONE - for accounts this is the account creation date and for action it's the timestamp of the action.

The two tables are currently super simple:

accounts Table:
timestamp  | timestamp with time zone
user_email |character varying

action Table:
timestamp  | timestamp with time zone
user_email |character varying

That's it, no keys or anything else.

I managed to piece together a query that seems to do this calculation:

WITH weeks AS (
    SELECT (n || 'week')::interval as week from generate_series(0, 3) AS n
)
SELECT

    week,

    (SELECT count(*) FROM accounts WHERE user_email IN
        (SELECT user_email FROM action
            WHERE age(action.timestamp, accounts.timestamp) >= week
            AND age(action.timestamp, accounts.timestamp) < week + '1 week'::interval))
            AS "Returning Users"

FROM weeks;

The output here is something like:

week    | Returning Users
0 days  |  100
7 days  |  50
14 days |  35
21 days |  25

The thing is, this takes super long to calculate (with 1300 users and about 100000 actions it takes a few minutes). I'm also convinced there's a more efficient way to calculate this, or that I can at least add indexes somewhere.

Unfortunately my SQL knowledge is quite limited and after looking around for a while I'm still not sure where to start. I think I can add indexes to the timestamp columns? And I'm not sure my way of checking if timestamps fall in certain ranges is correct.

It's a bit hard to tell how your tables are linked together, but I think the following should be more efficient:

WITH weeks AS (
  SELECT make_interval(weeks => n) as week
  from generate_series(0, 3) AS n
)
SELECT week,
       count(x.user_email) as "Returning Users"
FROM weeks
  LEFT JOIN (
     SELECT act.user_email, age(act.timestamp, acc.timestamp) as diff
     FROM accounts acc
       JOIN action act on act.user_email = acc.user_email
  ) x ON x.diff >=  week and diff < week + '1 week'::interval
FROM weeks
GROUP BY week;


How to check which users have been created by specific stored procedure in sql server? I have created a stored procedure, which creates different username and password.But i have condition that for next time when stored procedure is called, it should delete previous user created by same stored procedure.And we are not supposed to store user details in table.
So could anyone can suggest me how to know those users which are created by only this stored  procedure?
Thanks.

You can't without storing something uniquely identifying, particularly if (when) users/logins are dropped and recreated by other processes than the stored procedure.

Perhaps you could store just the principal_id and create_date from sys.server_principals, and the same from sys.database_principals; nothing identifying in particular, but the pair together should be a good indication that these are the right rows.


Are my Indexes Being Used? I am using SQL server, and the person creating our database added lots of indexes on our biggest table. These indexes only include one field each.

To me it seems that these indexes are mostly useless, and that this was a bad idea.

My concern is removing some or all of these indexes and affecting performance on our live system. There is no way to test this on our test system because our test system doesn't have the same load or amount of data.

Just wondering...

Is there a way to work out which indexes are never used?
Is there some kind of analysis that will show which indexes are used for what queries over a period of time for instance?

This is made very simple by the index usage DMV:  sys.dm_db_index_usage_stats

For example, this query I use very often to identify unused indexes:

SELECT o.name Object_Name,
       i.name Index_name,
       i.Type_Desc
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id
  LEFT OUTER JOIN
  sys.dm_db_index_usage_stats AS s
 ON i.object_id = s.object_id
  AND i.index_id = s.index_id
 WHERE  o.type = 'u'
-- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2)
-- Indexes without stats
  AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
      (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );

That query is taken verbatim from sql-server-performance.com but because their blogging platform puts in em-dashes and smart quotes it renders the code on their site un-copy-pasteable. (Why? Why on a code blog??) So I put it here in a form you can use without having to resolve syntax errors.

All due credit to them for this and many other useful code snippets I rely on.


Oracle SQL - find the character '&' in a string I am intending to do an update (Oracle SQL) for any customer records that includes an ampersand in their name (eg: Joe Bloggs & Co.) to replace this ampersand with the actual word 'and' (eg: Joe Bloggs and Co.).

See script below:

UPDATE acuheader
  SET apar_name = REPLACE (apar_name, '&', 'and')
where client = 'W5'
  AND apar_id = 'x'

HOWEVER, prior to doing this, I would have liked to run a select statement to identify all the customer records that have this issue.

I understand that the ampersand sign & is recognised by Oracle SQL as a regular expression & so was looking to see if someone could assist with a select statement.

I had initially thought this would work but not 100% certain:

SELECT *
from acuheader
where regexp_like (apar_name,'[&]')
  and client = 'W5'

My choice for a test would be a simple LIKE:

SELECT id
FROM acuheader
WHERE apar_name LIKE '%&%'
  AND client = 'W5'
  AND apar_id = 'x';


Postgresql index for jsonb array field I have a JSONB field called data with a few keys and there is a field like:

{school_id: [nil, 123456]}

I'm filtering all records where second element is not null using query:

"data -> 'school_id' ->> 1 IS NOT NULL"

How can I apply an index on that field ?
I've tried:

"CREATE INDEX data_school_id ON events ((data -> 'school_id'));"

but when I use EXPLAIN I do not see that this index is in use.

=> EXPLAIN for: SELECT "events".* FROM "events" WHERE (data -> 'school_id' ->> 1 IS NOT NULL)
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on events  (cost=0.00..211043.04 rows=2096733 width=555)
   Filter: (((data -> 'school_id'::text) ->> 1) IS NOT NULL)
(2 rows)

The index is only used when Postgres expects it to help performance - which is only the case if the filter is expected to be selective enough (~ 5 % or less of the rows qualify, percentage heavily depends on various details).

One problem with document types like jsonb: Postgres currently does not maintain statistics about value frequencies of embedded elements (still true in Postgres 10). So it has to base its decision whether or not to use an index on generic frequency estimations. Meaning, even if your particular filter data -> 'school_id' ->> 1 IS NOT NULL is very selective and using the index would pay, Postgres works with a generic average estimation and might miss the opportunity.

There are ways around this with expression or partial indexes, because Postgres collects separate statistics for index expressions. The best you could do for this particular query would be a partial index:

CREATE INDEX data_school_id ON events (event_id)  -- idx column largely irrelevant here
WHERE (data -> 'school_id' ->> 1) IS NOT NULL;

And VACUUM ANALYZE events; at least once (or wait until autovacuum kicks in).

Related:

Index that is not used, yet influences query
Inconsistent statistics on jsonb column with btree index


Multiple Instance with different service packs in SQL Server I would like to install a Default instance with SQL Server 2008 R2 RTM and I want to install another named instance with SQL Server 2008 R2 SP2. Is this possible?

Yes, you can choose instance to apply service pack.
Instance components along with shared components between side by side instances will be upgraded to higher service pack.

Sql server has two types of component\features.
1. Instance specific features
2. Shared Features

When you upgrade one component with a given instance name to a new version or service pack, you must upgrade all components of that instance.


Native checksum table says there's a difference, pt-table-checksum does not I was setting up a slave and doing some table checksums to check things were ok before getting them live.

I did a native mysql checksum tables on the slaves after syncing slaving to a stopped point and comparing against a preexisting slave.

Two tables differed.  To drill down I ran pt-table-checksum which reported both the old and new slave had no differences and the same row counts.

To get a third opinion I did a select * into outfile ordered by PK and ran an md5sum on the outputs.  Those too both matched.

One noticeable difference is the newer slave is running a slighter newer version (5.5.34 vs 5.5.29); both percona builds.

Is this version difference something to be expected in causing differences in checksums?  If so it seems odd it was just two tables out have a couple dozen.

Don't worry, your situation is not unique. I encountered the exact same problem with mk-table-checksum years ago (See my Nov 16, 2012 post Maatkit shows the MySQL replication error in one table, but won't fix it)

Here is something frightening from the MySQL Documentation on CHECKSUM TABLE

  The checksum value depends on the table row format. If the row format
  changes, the checksum also changes. For example, the storage format
  for VARCHAR changed between MySQL 4.1 and 5.0, so if a 4.1 table is
  upgraded to MySQL 5.0, the checksum value may change.

  Important If the checksums for two tables are different, then it is
  almost certain that the tables are different in some way. However,
  because the hashing function used by CHECKSUM TABLE is not guaranteed
  to be collision-free, there is a slight chance that two tables which
  are not identical can produce the same checksum.

I meniotned this caveat before back in January 2012 : How to check the consistency of tables in mysql

Sometimes, I just like directly using mk-table-sync (sorry Percona, I'm old fashioned)  with the --print options and tell me the SQL differences. As the MySQL Documentation says, there could be differences because of the VARCHAR alignment or ROW FORMAT. If mk-table-sync/pt-table-sync insists there are no changes, you could trust it 99.9 % of the time. If you don't trust it, mysqldump the table and reload it. Chances are the checksum may not match again.


Should I add page level compression before adding the primary key, or after? Situation

Data warehouse on Sql Server 2008 Enterprise
36+ million row heap (don't ask), with 60+ columns
750k added monthly
No primary key defined (I have now identified one)
No compression

What I'm thinking of doing (in this order)

Add page level compression
Add the PK
Add a number of non-clustered indexes
Do this as quickly as possible

Question

Ultimately: Do I add the PK or the page compression first? (Does it matter?)
If I add the compression first to the table, will any indexes then inherit the table level compression settings? The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange

What I'm leaning towards at the moment
-- Add page level compression
alter table     dbo.TableName
rebuild with    (data_compression = page)
;
go

-- Add primary key
alter table             dbo.TableName
add constraint          PK_TableName
primary key clustered   (<Columns>)
;
go

-- Add NC_IXs here
...
...

Ive looked here (PK creation documentation) and here (ALTER TABLE documentation), but can't see anything definitive about whether or not any indexes inherit table compression settings. The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange

The clustered index is in fact the table.  On the assumption that your primary key is clustered then I would create a clustered primary key with page level compression rather than trying to do it in two steps.

-- Add primary key
ALTER TABLE             dbo.TableName
ADD CONSTRAINT          PK_TableName
PRIMARY KEY CLUSTERED   (<Columns>)
WITH (DATA_COMPRESSION = PAGE)
;

I would also copy about 100k rows to a temporary (temporary physical not #temporary) table and run some tests.  Try running compression first, clustered key first, try doing them as one step.  See what runs fastest.  I would guess it will be one step personally :).


Why can an Oracle SET UNUSED COLUMN cause internal UPDATE SET X=X and huge log generation? On Oracle 11.2.0.4.1, we tried to run a simple ALTER TABLE PARTITIONED_LARGE SET UNUSED COLUMN XXXXX; on a large ref-partitioned child table to decommission a column.

The table has hundreds of millions of rows across thousands of partitions. Most partitions have been truncated and no segments left; probably about 220 of them have data. As per standard practice the point of the SET UNUSED COLUMN was to avoid redo generation and long downtime, while allowing us to regain space moving forward.

Something seems to have gone horribly awry; it started generating dozens of GB of redo logs; eventually filling up UNDOTBS; then failed with the below and started rolling back.

Fri Apr 24 23:48:13 2015
ORA-01555 caused by SQL statement below (SQL ID: 1ksq8yfdxv3ys, Query Duration=13500 sec, SCN: 0x0b5b.6392295a):
update "SCHEMA"."PARTITIONED_LARGE" set "XXXXX" = "XXXXX"

Seeing the statement above it's pretty obvious why it's generating undo/redo, but why did Oracle decide internally to do such an update during a SET UNUSED COLUMN?!

Other points that may or may not be relevant

at the time the command was started, a restore point had been created for flashback purposes. This restore point was dropped part way through when flashback log size became a problem
the column is defined as BINARY_DOUBLE DEFAULT 0 CONSTRAINT XXXXX_NN NOT NULL
the SET UNUSED had been earlier tested on a test DB with about 75% of the data of the DB in question (but not exactly the same data) and had no issues; ran within seconds.

Can anyone explain this behaviour, either in terms of expected behaviour or known issues?

Oracle said it wasn't a known issue, and basically asked us to re-run with tracing on. Unfortunately on a large production database, and given large downtime required to rollback last time, this wasn't really an option.

We had suspicion that it is related to this bug fixed in 11.2.0.4.2

Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption (Doc ID 17325413.8)

This problem is introduced in 11.2.0.4 and 12.1.0.1 by the fix for bug 14222244

Drop of a column with a DEFAULT value and a NOT NULL definition ends up with dropped column
data being written to disk leading to block corruptions.
This causes problems for generating undo which cannot be applied; a ROLLBACK fails.

Rediscovery Notes
 If a NOT NULLable column added via "add column optimization" is writing to disk, then it
 is likely you have run into the bug.

 This can result in block corruption causing ORA-600 block check errors with check code
 errors 6264, 6110, or 6103 when db_block_checking is enabled:
  ORA-600 [kdBlkCheckError] [file#] [block#] [6264] --> needs _db_block_check_for_debug to catch it.  This is a ZLC logical corruption (Zero Length Column instead of NULL)
  ORA-600 [kdBlkCheckError] [file#] [block#] [6110]
  ORA-600 [kdBlkCheckError] [file#] [block#] [6103]

or other internal errors when db_block_checking is not enabled like ORA-600 [13013], ORA-600 [17182] or external errors like ORA-12899.

Workaround

 Note that once error occurs, table has to be rebuilt:
   Note:1527738.1 may skip the rows affected and can be used with _disable_block_checking=false as:
      alter session set "_disable_block_checking"=true;
       and in the same session use the PLSQL in Note:1527738.1 section "SKIP ORA-600 in a Table"

After patch to 11.2.0.4.6 the SET UNUSED ran fine in production without the unexpected log production. Might be worth a go if anyone else stumbles into this problem; although I suspect Oracle will eventually have to raise a proper bug for this issue once someone helps them narrow it down properly.


Best way to automate sql server backup & restore from prod to dev? I have a development server, and a production server. The dev server is inside our office at work inside our LAN.

The production server is a remotely hosted windows pc, that is connected to our web server, which is a seperate pc.

Now what I want is to have a batch file or some other tool to automatically download a recent backup, for which there are weekly backups done, and then restore that to the current copy of the database on dev server.

I know, I can create a scheduled task in windows to copy n zip a backup that the weekly backup takes, and I would need any suggestions or help for that, since the name is different because it is date/time stamp in the filename. I want to create a task to zip the backup and copy it to a folder that is accessible on the web server, they are both on the same network.

Then, I want the dev server to check for the existance of this latest backup zip file, and if exists, download it, unzip to a temp folder, then restore that database to the current one.

Since I may or may not be hear in the far future the more I can make this easy to use, and as automatic as possible, the better they will be.

Since there is no dba we can use to do this for this manaully.

Any suggestions?

I do not mind using software for this, but it has to be freeware, since there is no budget for this project.

Your solution is pretty much the way to go.

Personally I'd consider using SSIS to coordinate the tasks as well as sending emails on failure etc.


Do I need to install SQL Server service packs to continue receiving security patches It's always been my belief that SQL Server needs to be updated to the latest service pack in a timely (and tested) manner to continue receiving security/bug patches.

I'm now partially responsible for a 2008 R2 DB that has not had any service packs installed. The external IT company mainly responsible for maintaining it has told me that they don't install service packs unless there is a specific business need, and that it would automatically recieve security patches any way. Is this the case, or do you need to keep up-to-date (once tested) with the latest service pack?

It's always been my belief that SQL Server needs to be updated to the latest service pack in a timely (and tested) manner to continue receiving security/bug patches.

You are correct. Some service packs will continue to have new CUs until their lifetime runs out.

  ... and that it would automatically recieve security patches any way.

They either didn't understand how this worked or flat out lied to you.

  Is this the case, or do you need to keep up-to-date (once tested) with the latest service pack?

You'll need the latest SP + Patches. For example, since you're on RTM... you're missing quite a few different security patches because they weren't made for the RTM branch after it went out of support*.

*Note that the links above show the same security updates released for supported service packs at the time. However, RTM was not one of them.


How to add data to sql server using MS access front end, when user will not always have sql connection I have a SQL Server backend and an Access Front end database which is working fine.

However, I now have a user that needs to use this database (on a tablet) for some equipment checks out in the production plant that does not have a wifi connection and of course he gets an error since he cannot connect to the SQL server.

What is the best way to handle this?  I've never run into this before.  (My other databases are used by Office staff only)  The user does need to have some information that is on the SQL server for drop down selections, etc.

Do I create a local copy on the tablet and then have him press a "submit" button to commit the record to the server (append query) when he is back in the office with wifi range?

I'll run into issues if others are entering data that is using autonum, etc.  Also, any changes made to the dropdowns by office staff would not be updated to him if it is a local copy.  Any help would be appreciated.

This is the typical scenario for replication. You could set up a second copy of the SQLServer database on his machine (remember that SQLExpress has limitations on the size of the database - 10 GB - and does not have SQLAgent) using merge replication.

The SQL Server instance you are using now could act as a publisher and the laptop could act as a subscriber. The Access frontend should point to the instance of SQL Server on the laptop instead of pointing to the publisher. When the user gest out of the wifi range, he would still be able to work using the local SQL Server instance. When he gets back to office, the subscriber could sync with the publisher.

Since SQLExpress does not have a task scheduler (no SQL Agent), you would have to use the Windows task scheduler for running the merge agent. In case you need very frequent sync, you could use the continuous switch in the merge agent.

Keep in mind that merge publishing a database is not totally painless: all published tables (articles) will need to have a uniqueidentifier UNIQUE column decorated with the rowguidcol flag. If you don't have such column, it will be added automatically, which will likely break all the insert statements that don't specify the column list. Moreover, all articles will get triggers for insert/update/delete operations and your workload might suffer a noticeable slowdown during write operations, depending on the shape of the workload itself.


Table naming and Database normalization I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in.

My current structure is like this:

Experiment

Primary key: SimulationID

Tables:

measurement
sampling_rate
first_draft_flow_rate
second_draft_flow_rate
final_flow_rate

Foreign key relating these tables: cycle_count

Each table contains the primary key, foreign key, and values for the variables.

So for a given experiment, it runs for so many cycles (the number of cycles it runs for varies from simulation to simulation). We log data at each cycle of the simulation.

I've made a table called measurement, sampling_rate, etc, but am not sure what to name the columns. Should the also be called measurement, sampling_rate, etc? Or should I just use Value as the column name?

Here's a spreadsheet with a sample log to demonstrate what I'm working with. All of this data would be filed under a single SimulationID.

Also, any tips on how best to design a DB to be normal/best practices would be greatly appreciated.

This is not database administration, this is data modeling. Very different disciplines.

I'm guessing your main entity is Simulation and the tables you list describe it. You don't show the structure or content of these tables so the following is based on conjecture.

Measurement looks like it could be a list of measurement types: temperature, flow, particles per unit volume, etc. SamplingRates also looks like a list of valid rates: 1/sec, 10/sec, 100/sec, etc.

Finally there are three table that look like they should be one, FlowRates, that is also a lookup table.

This would mean a Simulation is the recorded results of, say, a temperature reading at a rate of 10 times per second of a 30 ml/sec flow.

Is that accurate? If so, here would be an example:

Measurements
 ID  Name
  1  Temperature
  2  Particles per ml

SamplingRates
 ID  Name  Period
  1     1     sec
  2    10     sec

FlowRates
 ID  Rate  Unit Period
  1    10    ML    sec
  2    20    ML    sec
  2    30    ML    sec

So the example Simulation entry would show a Measurement of 1, SamplingRate of 2 and FlowRate of 3 -- along with the results of the measurement, of course, and probably a timestamp of when the simulation was performed.

It would help a lot if you would give a plain language description of an experiment: "An experiment consists of any number of simulations. A simulation is made up of various readings of...made at a certain frequency based on ..." Don't think in terms of tables and columns. Pretend you're talking to a lab technician.

Update: When designing a table (and this pertains to naming the fields) it is generally a good idea to isolate the entity from all other entities -- that is, the naming should be done as context-free as possible. What that means is if you have a field that represents, say, the name or description of the entity, then by all means call those fields "Name" and "Description". No matter that you have dozens of other fields with identical names in tables scattered around the database.

A table has no context. It is the query that establishes a context.

select  s.Name as NewSite, u1.Name as Owner, u2.Name as Manager
from    Site s
join    Users u1
    on  u1.ID = s.OwnerID
join    Users u1
    on  u2.ID = s.MgrID
where   s.Created > '2015-01-01';

Here are three tables each with the field Name -- it really doesn't matter that one table is used twice. Within each table, Name means "this is the name of the entity represented by this row."

The context established by this query is easily identified. It is looking at the owner and manager of all newly created sites and renames each Name field to suit the context. Different queries can and do use the fields in completely different contexts. As it is the query which sets the context, let the query rename the fields to whatever best suits that context.

Don't try to force a context with a table by naming the fields User_ID or User_Name and so forth. In a query, fields should be prefixed with the table name or alias anyway, so there's never any confusion.

where  User.Name = 'John Smith'

Compare with

where  User.User_Name = 'John Smith'

The extra "User_" adds no useful information. Besides, you're bound to hit something like this:

where ExtremelyLongTableName.ExtremelyLongTableName_SomewhatLongFieldName = ...

I'm exhausted just typing it the one time. Besides, some DBMSs limit the length of object names. Oracle, iirc, only considers the first 32 characters of an object name. I've hit that limit more than once in shops that use the tablename_fieldname convention. At that point you have to use abbreviations which is really messy.

Anyway, "best practices" is a fairly subjective concept. Opinions will vary. Choose what is most comfortable for you.


Postgres, return 0 rows if any row matches condition, otherwise return all rows I have a table with ten rows. One of the columns is a boolean, let's just say for argument's sake:

email text
is_activated boolean

I'd like to write a query that returns zero rows if is_activated is true for any of the rows. If none of the rows have is_activated set to true, I'd like to return all of the rows in the table.

How can I do that?

SELECT * FROM table_name
WHERE NOT EXISTS
(
  SELECT 1 FROM table_name WHERE is_activated IS TRUE
);


Redundancy vs chained join I'm encountering issues checking ownership and accessibility of entities in my application.

Here is a simplified summary of my problem. Consider the following diagram (I don't have a precise DDL, this is a simplified example to illustrate my issue: access to an entity efficiently in my app depending on the properties of its parents [of parents ...]):

A writer can write many books, add chapters to it, then attach notes
to chapters (a book may have co-authors, but not at the the moment).
He is the owner of the book thus no other writer can update it, same
for its belonging chapters and notes.
He can archive his books, this imply can he no more edit books chapter
and notes.

I have an API that performs CRUD operations on books, chapters, and notes.

Foreach each operation I have to verify if the current user is the owner of that ressource and if the book it belongs to is not archived.

I'm using an ORM and currently on a "edit notes" api call, I populate objects by making a select that join the chapter then the book, then the owner.
Then i can perform the ownership check and the archiving check.

Is it wrong doing big queries with chaining join to prevent
subsequent database calls ?  I feel it makes these calls more and
more complex and heavy as the application grows.
Should I make more simplest request for each ressource?
Should I use another database design to make it easier/faster
checking ownership and parents (of parents ...) properties (e.g redundancy, something else...)?

No, I think you are doing the Right ThingTM.

Performing a bigger query to get all the data you need in one round trip almost always beats fetching the data points one by one (“nested join implemented in the application”).

Your normalized database design is perfect for a transactional application that performs data modifications and small queries. If that is your workload, keep it like that. Such a layout avoids storing information redundantly, which is good for consistency and saves space.

If you have a database for reporting or analytical processing, the many large joins required by the typical queries will often be too inefficient In that case judicious de-normalization (using fewer tables with potentially redundant data) is often more efficient.


Several variants of a string in tables I need:

a table containing keyword phrases;
a table containing URLs;
a table (or maybe a view) with keyword-URL pairs (referring to IDs of the two previous tables);
tables holding keywords/URLs for particular users.

The trouble is that both URLs and keywords may have several different forms. For example, both keywords and domains in URLs may be lowercase of uppercase, or http://example.com is the same as http://example.com/ (with trailing slash).

The question is how to manage user input when several possible variants are possible.

For now I can't formulate this question more exactly, as I have not formulated the exact specification of what I need. You are free to interpret my question in any way you may do it.

The sub-questions of this question:

Should I store both uppercase/lowercase variants or just the exact user input, generating my upper case version on the fly? (I need these "standardized" lowercase strings when doing actual URL queries from our server. It looks like for me that it's better to store them in the DB rather than to generate on the fly, for performance reason).
Should I use stored procedures to overcome such a deceiving DB layout, with that mess of lowercase and uppercase strings?

It is OK to use transactions. The DB is in MySQL.

I decided to do it in the following way:

Make a table strings with an ID and a VARCHAR, which will store both URLs and keywords, both normalized forms and unmodified user input.
Make a table seo which will hold (in addition to the row ID) four numeric fields referring to strings: unmodified user input for URLs and keywords, normalized URLs and keywords.

The list of normalized URL/keyword pair will be generated on the fly using DISTINCT.


What to do when a field in a table approaches the max signed or unsigned 32 bit integer? In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the max signed or unsigned number of the current datatype? (A 32-bit INT)? I'm guessing that the database server would overflow when it tries to assign the (2∧32)-1 number to the next entry, so, how to avoid that happening (without changing the datatype, for the sake of the question) and keep adding records? What would you do?

Why would I use INT's and not, for example, VARCHARS?

It has been several days since I've asked myself this hypothetical question and I would like to know what a professional would do.

You would generally use integers rather than varchars because they consume less space, have well understood sorting pattern are fast to index etc. Integers are natural data types of a CPU, and hence performance is generally optimal. Typically an integer is 4 bytes, equivalent to just 4 characters in a (non-unicode) varchar.

If you were worried about running out of space with an INT type, then try BIGINT, which gives you 8-byte numbers. The limit on this is pretty huge, and you'd probably run out of disk space before you reached that limit of records :-)  The performance of BIGINT is also going to be very good, especially as many servers are now 64-bit too.

The answer to the first part of your question about what happens when you run out in INTs is not simple, especially as you said without changing the datatype to BIGINT. Basically there isn't much you can do, and what you may be able to do is limited very much by the nature of the data in your database.  What records are foreign-keyed to this data? Do you still need all the data in that table and the related records?  On the assumption that you could archive off a lot of the initial data (and its related data), then the only thing I can suggest is moving the data out of the table (lets say the first 1 to X million records), and then resetting the identity seed to 1.  There are all sorts of reasons though I wouldn't recommend it - for example there are many bits of code I have seen that do things like check the maximum value of an id field, to see what has just been added, and that would not work (and shouldn't be done). Also, people assume that record N was created before N+1.  No easy answer I think.

Finally, I don't know about MySQL, but SQL Server would give an overflow error if you reached the limit.


Why does SQL Server 2012 Express use 9.5GB of RAM on my server? I am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore.  When testing on my development machine (Win7-32 with 3GB RAM), I never observed the sqlservr.exe process to use more than 1GB of RAM as I would expect from the published hardware scaling limits for the Express edition of SQL Server.

I then moved my application to a server-grade machine (Win Server 2008R2 64-bit with 16GB RAM) to evaluate its performance there and was surprised to find that the sqlservr.exe process rapidly expanded to approximately 9.5GB of RAM and stayed there.

I restarted it a couple times to see if that would have any effect, but each time, the process rapidly returned to ~9.5GB.  Now I am certainly pleased to have SQL Server Express make use of my RAM, but I would like to know if this is expected behavior so that I do not come to rely on performance levels that are based on RAM usage that is incorrect.

FYI, the version of SQL Server on my server machine, according to SELECT @@VERSION, is:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

My 9.5GB number came from the "Private Working Set" number in Task Manager.  The first table of output from DBCC memorystatus (albeit on a now idle server) is below:

PROCESS/SYSTEM COUNTS
Available Physical Memory   5543616512
Available Virtual Memory    8734902411264
Available Paging File       22471094272
Working Set                 9664200704
Percent of Committed Memory in WS   99
Page Faults                 2627510
System physical memory high 1
System physical memory low  0
Process physical memory low 0
Process virtual memory low  0

Output from spaghettidba's suggested query on sys.dm_os_memory_clerks:

MEMORYCLERK_SQLBUFFERPOOL   1410
OBJECTSTORE_LOCK_MANAGER    256
MEMORYCLERK_SQLCLR          38
MEMORYCLERK_SOSNODE         26
CACHESTORE_SQLCP            11

plus ~10 more smaller entries that sum to less than 30MB.

SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools.
To find out memory usage per memory clerk, run this:

SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

Hope this helps


DBEAVER: SQL error or missing database (near ",": syntax error) I'm using DBeaver (because Base only works on OS X) to work with an SQLite database. When I attempt to execute the following query, I get the error message 'SQL error or missing database (near ",": syntax error)', even though I've specified the database and table:

INSERT INTO `contacts`.`contacts` (`fname`, `sname`, `dob`) VALUES ('MTN', 'Account', NULL), ('A', 'Person', -20456000), ('Local', 'Police', NULL), ...;

contacts is the database, containing a table named contacts with the following structure:

CREATE TABLE `contacts` (
    `cid` INTEGER NOT NULL AUTO_INCREMENT,
    `fname` TEXT NULL,
    `sname` TEXT NOT NULL,
    `dob` INTEGER NULL
);

I can use the + button to insert rows, but I'd prefer to perform bulk inserts.

I've already looked at the SQLite INSERT documentation and it looks exactly the same as MySQL's, so I don't see why it's having a hissy fit.

SQLite allows to insert multiple rows with one statment, but only in version 3.7.11 or later.

Apparently, your JDBC SQLite driver is several years out of date.


Dealing with varying fill factors in a SQL Server 2014 environment I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor. I found a YouTube video Brent Ozar did on defragmenting & fill factor of indexes which was most insightful. All my fill factors are currently 100%, but I know that this isn't good for all my indexes. I found this article/script about determining a better fill factor for certain indexes. If this article is still applicable, this brings me to my question:

I'm using the Ola Hallengren scripts to help keep my indexes tidy, but I don't see a way to leave the current fill factor alone. It will rebuild/etc with either the system setting or the specified one but not the current one if it is different.

Does anyone have suggestions/practices they use if an index needs to be rebuild with a specific/different fill factor?

I'm not sure why you think those are the only options.

If you look in IndexOptimize, the @FillFactor variable is passed in as NULL by default.

The command that appends Fill Factor looks like this:

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4)
SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = '
+ CAST(@FillFactor AS nvarchar)

If you leave it NULL, the index will get rebuilt at its current Fill Factor.

Your only task would be to remove any Fill Factor hints from the Agent jobs, etc.


Postgres list constraints that use a stored procedure I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.

something like:

CREATE TABLE payment (
  amount bigint,
  CONSTRAINT amount_gt_0 CHECK (round_amount(amount) > 0)
);

where round_amount is user-defined function.

Is it possible to get a list of all constraint or at least tables that use such constraints?

postgresql version is 9.6

It looks like that there is already a view called information_schema.check_constraint_routine_usage that provide this information.

The view originally only shows routines owned by the current role but you can find the original query by typing \d+ information_schema.check_constraint_routine_usage and tweak it.


Users groups/permissions when working with PostgreSQL While I've been working with PostgreSQL for approximately 1 year, I still feel like a bit of a Newbie.  I feel like I'm constantly fighting with psql and pg_dump when it comes to files and permissions.  For example, just trying to do a "COPY FROM" and not having permissions to access the file.

How do dbas normally set up their users, etc to make life "easy" (or at least a little less painful).

Also, to "come clean", I have spent most of my career working on Windows.  I now primarily work on Linux.  I would probably give my self a D+ or a C- (always dangerous to try and self grade) when it comes to understand Linux user/groups/permissions.  I'm not completely in the dark and can usually make things work (or at least understand a blog post that I find by Googling a problem) - but I'm certainly not a pro.

I'm currently using PostgreSQL 9.1.5 on Ubuntu 12.04

There are two basic approaches to users and groups.  I will also describe what approach I favor here and why.

The first approach is to use login roles to describe what software is logging on and restrict rights accordingly.  This has the advantage of being relatively simple to maintain and a number of disadvantages as well, but it is probably the most common approach because of its administrative simplicity.

The second approach, which I favor, is to give every user his or her own role.  DB permissions are then attached to groups (i.e. nologin inherit roles) which are granted to individual users.  This is more complex to administer.  Keep in mind that unlike Linux, PostgreSQL groups can be nested and unlike Windows, there is no deny acl, so permissions are always fully cumulative.  In some cases where someone needs a restricted account we can create a separate user.  This adds complexity to the administration process but it also helps mitigate security issues in the application because something like a sql injection attack is less likely to have privilege escalation possibilities, and it essentially allows outsourcing of authentication to the database, and PostgreSQL can outsource this to a variety of sources (RADIUS, PAM, KRB5, GSSAPI, SSPI, etc).


Polybase with Cloudera 5.9 We are working on a Proof Of Concept using Polybase with Cloudera.

In the polybase documentation it says we can connect to Cloudera 5.5:
https://msdn.microsoft.com/en-us/library/mt143174.aspx

We are looking at Cloudera 5.9 - does polybase work with cloudera versions over 5.5?

MSDN has updated the documentation

  Option 6: Cloudera 5.1, 5.2, 5.3, 5.4, 5.5, and 5.9 on Linux

https://msdn.microsoft.com/en-us/library/mt143174.aspx