59
It very much depends on details of your setup and requirements.
Note that since Postgres 11, only adding a column with a volatile DEFAULT still triggers a table rewrite. Unfortunately, this is your case.
If you have sufficient free space on disk - at least 110 % of pg_size_pretty((pg_total_relation_size(tbl)) - and can afford a share lock for some time and ...
answered Oct 30 '13 at 19:44
Erwin Brandstetter
140k17 gold badges331 silver badges475 bronze badges
31
This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN?
If you go to your SAN team and start talking about the disks being slow, they're gonna show you a fancy graph with 0 millisecond latency on it and then point a stapler at you.
Instead, ask them about the network path to the SAN. Get speeds, if it's ...
29
No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.
Extension pguint
But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution:
In addition to various unsigned integer types,...
22
Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved.
You also do not need, nor want, to specify a column length larger than the maximum actual size that will be stored for a variety of reasons: query memory ...
19
We have a similar setup and recently encountered these messages in the logs. We are using a DELL Compellent SAN. Here are some things to check when receiving these messages that helped us find a solution
Review your windows performance counters for your disks that the warning messages are pointing to, specifically:
Disk avg. read time
Disk avg. write time
...
18
I don't have a "best" answer, but I have a "least bad" answer that might let you get things done reasonably fast.
My table had 2MM rows and the update performance was chugging when I tried to add a secondary timestamp column that defaulted to the first.
ALTER TABLE mytable ADD new_timestamp TIMESTAMP ;
UPDATE mytable SET new_timestamp = old_timestamp ;
...
17
Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL.
NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly affects performance. Effective storage requirements depend on whether the null bitmap for each row already exists and has room for 3 more bits.
Tables with up to ...
answered Feb 12 '15 at 21:56
Erwin Brandstetter
140k17 gold badges331 silver badges475 bronze badges
16
If your table doesn't have a clustered index, then deletes don't deallocate empty pages by default.
Your options are:
ALTER TABLE dbo.MyTable REBUILD - which will take your table offline in Standard Edition, building a new copy of it with everything packed in nicely like sardines
Do your deletes with the TABLOCK hint - which can prove problematic for ...
14
Each row has a minimum of 7 bytes of overhead when stored in FixedVar format (the default). There will also be a (typically relatively small) number of pages used for the upper levels of the clustered index. Optimally stored, and disregarding the upper index levels, 2 million rows would require just over:
(7 + 8 bytes) * 2,000,000 = 28.61MB.
More ...
14
Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.
other queries using a PK of table (let say id_foo)
That ...
13
It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, maybe no gains. If the LUNS are all under 1 controller...you see how many variables are at play. There's no one answer.
To determine the configuration of the mount ...
12
Absolutely possible, because someone with the resources to store
6021188640340442162025691220451771208370039202613309330051794368412 Terabyte
definitely has enough money to get a custom made database system for his purposes as well.
I might be available as a contractor, for only 0.01€ per Terabyte.
12
Size can differ due to several reasons:
indexes take some disk space,
there can be several copies of the same record on disk,
slack space in pages.
Indexes take up disk space in order to facilitate faster lookup. The more indexes you have, the more disk space your DB will take up. GIN indexes are usually smaller, but aren't useful if you use range queries. ...
11
My field avgObjSize was around 6442 bytes. I chose a random document in my collection, I typed :
Object.bsonsize(db.collectionName.find({"_id" : ObjectId("5508497c51a990da07b07106")}))
And I obtained 810 bytes.
Why do I have this huge difference ?
The db.collection.find() method returns a cursor, so what you have calculated is the ...
11
1. chr(10)
... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there.
2. & 3. ascii() produces 128 or 192?
It starts with a mistake I made. I carelessly assumed "char" would cover the range of an unsigned 1-byte integer (0 to 255) in the referenced answer (...
11
is there any performance benefits to keep the data files split across different logical drives?
If all the volumes map to the same set of physical disks on the SAN there's normally no difference.
However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes. For instance, they can be ...
answered Nov 24 '20 at 21:47
David Browne - Microsoft
36k3 gold badges38 silver badges79 bronze badges
10
I'd run DBCC UPDATEUSAGE against the table as a first step, since the symptoms show inconsistent space usage.
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found ...
10
Create a clustered index so that your table is not a heap. When a row is deleted from a heap, the space will not be reused. Even empty pages don’t get cleared up. This problem won’t happen if your table is a clustered index.
9
Keep in mind that there are 4 "row formats". A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few months before DYNAMIC and COMPRESSED were introduced in the "InnoDB plugin".
So, I claim that that other Q&A is out of date! That is the 754 Upvotes ...
8
You have to use compression (columnstores should do it). I suggest RLE (Run Length Encoding), you can store (5HpHagT65TZzG1PH3CSu63k8DbpvD8s5ip4nEB3kEsreAbuatmU ,1MsHWS1BnwMc3tLE8G35UXsS58fKipzB7a ,1Q1pE5vPGEEMqRcVRMbtBK842Y6Pzo6nK9) as the value and 115792089237316195423570985008687907853269984665640564039457584007913129639936 as the run length. Document ...
8
Based on the results of numerous internet searches I can't find any (post-SQL Server 2000) reason to not use mount points.
The main reason is someone had a bad experience with them (or, conversely, no experience with them) and has completely ridden them off... forever. This is otherwise known as personal preference.
Now, there are some reasons that you ...
answered Aug 4 '17 at 19:19
Sean Gallardy - Retired User
21.1k2 gold badges34 silver badges65 bronze badges
8
In addition to CM_Dayton's answer and Sean Gallardy's answer, one issue not yet identified with Mount Points is related to security. To quote Guidelines for Setting SQL Permissions on Mount Point Folders:
Although the mount-point root folders may look like regular folders and are accessed in the same way folders are accessed, they are not regular folders....
8
Why storing the data on a SAN? What's the point? All database performance is tied to Disk I/O and you are using 3 servers with only one device for the I/O behind them. That makes no sense... and unfortunately so common.
A SQL server software is a very advanced piece of software, it is designed to take advantage of any bits of hardware, CPU cores, CPU cache, ...
7
SQL Server 2008 R2 installed on a virtual server
IMO, the SQL Server VM should live with the rest of the VMs. If this device goes kaput (the Cybernetics website says nothing about the number of controllers in this model range, so presumably there's only one), you don't want all your eggs in one basket. Hopefully there are other, better protected devices ...
7
There is a bit of advice on the internet that suggests for Server 2012 and SQL 2012 I should stick to NTFS e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.
This is no longer true with SQL Server 2014 and is fully documented in KB 2974455
This problem is resolved in Microsoft SQL Server 2014. Beginning in this version of SQL ...
7
Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.
Sounds good. Have you collected and cut up the minifilter and storport tracing, yet? If so, what did it show?
At first glance it looks to be clearly an I/O subsystem error and the ...
answered Apr 2 '19 at 21:30
Sean Gallardy - Retired User
21.1k2 gold badges34 silver badges65 bronze badges
7
Ok, for anyone interested,
We solved issue in Question couple months ago simply by installing directly attached SSD drives into each of 3 servers,
and moving DB data and log files from SAN to those SSD drives
Here summary on what I did to research on this issue (using recommendations from all the posts it this question), before we decided to install SSD ...
7
Nothing has changed since that article. You should still use 64KB as your unit size.
That is how the default storage in SQL Server VMs in Azure are configured:
6
You didn't mention the Database platform, I can give insight on SQL Server.
SQL Server ensures durability with a concept called WAL. Write ahead logging. This means that all changes are first written to log before the are applied tot the data files.
When a row needs to be altered. The corresponding data (and possibly index) pages get fetched from disk into ...
6
Unfortunately there are only 1080 atoms in the visible universe. But with only one atom given, it could be possible to describe the data purely by its position in the universe.
The database has 2266 records with 52 bytes each, that is a database size of 52⋅2266 bytes. That means there are 25652⋅2266 ≈ 101082 possibe states the database could adopt.
Using ...
Only top voted, non community-wiki answers of a minimum length are eligible