Skip to main content
Share Your Experience: Take the 2024 Developer Survey
7 votes

Efficient key value store in Postgres

Number one: PostgreSQL will never be great as a key-value store if you have many UPDATEs. Workloads with many UPDATEs are just hard for PostgreSQL's architecture. Make sure that you create your table ...
Laurenz Albe's user avatar
6 votes
Accepted

Set column to randomly chosen value from another table

The subquery is evaluated only once because it has no dependency on the row to update. You may add one, just to force the engine to evaluate the subquery for each row. For instance if dest_col is a ...
Daniel Vérité's user avatar
6 votes

Galera cluster into Google cloud platform

but I have difficulties to see the added value compared to Cloud SQL instances with replication and backup. I am not very familiar in scaling heavy load systems. Galera-based clusters like ...
MarcosAlbe's user avatar
5 votes

Mysql query works in 200ms with 33594 parameters in SELECT IN (xx,xxx,xx) but runs hours with 33595 or more parameters, why?

I'm more curious about why this happens to understand what's the limit... The why is because it's an overly complicated predicate. The IN clause is syntactical sugar for a bunch of OR clauses. Too ...
J.D.'s user avatar
  • 37.8k
4 votes

PgAdmin 4.14 connection error in cloudSQL : could not send data to server, socket is not connected, could not send ssl negotiation packet

Switching from localhost to 127.0.0.1 allowed the connection successfully.
Jason Tarr's user avatar
4 votes
Accepted

MySQL slow_log table: querying takes forever

Answer originally left as a comment by Michael - sqlbot: Mostly it's a MySQL thing. The slow query log "table" -- by default -- is actually just a CSV file read by the "CSV storage engine," which ...
4 votes
Accepted

UNION Queries run in Parallel form or not

MySQL does not use multiple CPUs for any query, not even with UNION or PARTITION (the likely candidates). Multiple connections (not just multiple threads) could do things in parallel. But the ...
Rick James's user avatar
  • 78.4k
3 votes

ERROR: role "user1" cannot be dropped because some objects depend on it

06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL: privileges for database mydb 24 objects in database mydb Crossdatabase queries in PostgreSQL are not implemented. PostgreSQL ...
Melkij's user avatar
  • 3,189
3 votes
Accepted

How do I see where postgres privileges are coming from for a role?

The permissions are stored on the individual objects, and there is no central view to see them all. You could use the access privilege inquiry functions for each type of object to get that information....
Laurenz Albe's user avatar
3 votes
Accepted

IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)

The problem is that when you connect locally, PostgreSQL does not demand a password. So while the user mapping might define a password, that password can't be used as it is never asked for. If it ...
jjanes's user avatar
  • 40.1k
2 votes

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xx.xx' (10060)

I had a similar issue. I believe there are two ways to resolve it (though only one worked for me). The first way (that did not work for me) would be to create a client certificate via the Cloud ...
Chris Reyes - he-him's user avatar
2 votes
Accepted

GCP MySQL query logging

It's all documented here and here. Relevant part: To make your general or slow query logs available, enable the corresponding flag and set the log_output flag to FILE. This makes the log ...
Philᵀᴹ's user avatar
  • 31.8k
2 votes
Accepted

Half of the partitions are empty on a KEY partition

You have 100 partitions. So it's likely that your hash values modulus 100 are all even numbers. This is a general risk of hashing. If the hashing function produces only values with a pattern like this,...
Bill Karwin's user avatar
  • 15.1k
2 votes

Efficient key value store in Postgres

Your benchmarking program creates two hash indexes, one to support the constraint, and another free standing one which seems to serve no purpose. Also, 100 iterations is by no means sufficient to ...
jjanes's user avatar
  • 40.1k
2 votes

UPDATE incredibly slow on small PostgreSQL query - EXPLAIN ANALYZE causes it to hang

1+GB size for a table with 2k rows means either it has huge text columns or it is highly bloated. Why don't you try running vacuum (analyze, full) against the database in question (if you haven't ...
Sajith P Shetty's user avatar
2 votes

How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow?

Unfortunately this is not a simple matter. There is much black art and experience involved because you are dealing with a few different but strongly coupled matters. First you are trying to decide if ...
David Spillett's user avatar
2 votes
Accepted

Postgres allows to UPDATE however only SELECT is granted

=arwdDxt/dave means all privileges are granted to the pseudo-role PUBLIC, of which anton is obviously a member. The documentation states that any particular role will have the sum of privileges ...
mustaccio's user avatar
  • 26.1k
2 votes

Why does the page cleaner in MySQL 8 increase the number of active connections to the database?

This depends on what the OS has configured In the MySQL 8.0 Documentation on innodb_page_cleaners, it says in part If your workload is write-IO bound when flushing dirty pages from buffer pool ...
RolandoMySQLDBA's user avatar
1 vote

Installing pglogical: Find postgres include folder, add all include folders to C_INCLUDE_PATH

pglogical is a third party postgreSQL extension provided by 2ndQuadrant and is not part of PostgreSQL. Some of it is written in C++, so must be compiled and built on the platform that it runs on. ...
Tom Harrison Jr's user avatar
1 vote

Am I missing an index for these slow queries?

The Optimizer is between a rock and a hard place. Using last_seen_index avoids the need for a sort, but might have to scan the entire table without finding even 1 row. If most of the rows have very ...
Rick James's user avatar
  • 78.4k
1 vote

Creating manual superuser in cloudsql for postgres

Yeah, cloudsqlsuperuser is not a real superuser. If you want your new role to be as super as it gets, then grant it cloudsqlsuperuser.
jjanes's user avatar
  • 40.1k
1 vote
Accepted

migrate users from mariadb10.1 to mysql5.7

Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility ...
jynus's user avatar
  • 14.9k
1 vote

Speed up 180M+ rows returned by simple SELECT with PostgreSQL

As you mentioned you are trying to get 80% of the rows of your table and it is normal that postgreSQL optimizer decide to scan the table rather than get data from the index. Unfortunately postgres ...
Artashes  Khachatryan's user avatar
1 vote

Removing MySQL Orphan Tables in Google's Managed MySQL

Your theory is correct about your cloudSQL since it is a Google managed service. If you would like to have full access over your mysql database, you can use it through a Compute Engine instance in ...
Noohone's user avatar
  • 111
1 vote

Why is an instance forced to reboot?

Are your instances 1st or 2nd generation? The activation policy configuration for 1st generation instances can cause your instance to automatically shut itself off after 15 minutes of inactivity. ...
alextru's user avatar
  • 111
1 vote

Improve speed of GCP SQL Failover

There isn't anything you can do to improve the failover time, but you can add a read replica to have read access to your data while your instance is recovering. If you aren't locked down to MySQL, ...
kurtisvg's user avatar
  • 111
1 vote
Accepted

Google Cloud SQL lacks raster2pgsql

raster2pgsql This a helper to make the job easy. It's not strictly speaking required. raster2pgsql is a raster loader executable that loads GDAL supported raster formats into sql suitable for ...
Evan Carroll's user avatar
  • 63.5k
1 vote

How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow?

Indicators of something needed: If you are more than 2 versions behind the latest, it is time to upgrade. (When you get too many versions behind, the upgrading becomes more difficult.) For MySQL, ...
Rick James's user avatar
  • 78.4k
1 vote
Accepted

How to configure PG text search dictionary through sql

By design (it's hardcoded) tsearch_data directory is in SHAREDIR. If you want to change SHAREDIR you should use your own compiled version of PostgreSQL. If you are interested, you can read the ...
Sahap Asci's user avatar
  • 2,969
1 vote

Google Cloud SQL Replication Delay

I have seen this happens a lot in Amazon RDS. This occurs with row-based replication when a Master get hammered with lots of writes and they get serialized to one I/O thread. What you should do is ...
RolandoMySQLDBA's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible