Tag: tables

How to get count of an object, through 3 different tables in postgres with ID’s stored in each table

I’m currently using Postgres 9.6.16.

I am currently using 3 different tables to store a hypothetical users details.

The first table, called contact, this contains:

ID, Preferred_Contact_Method

The second table, called orders, This contains:

ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order)

The Third Table, Called order_details

ID, Orders_ID (the id in the orders table that relates to this order details)

The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.

I am trying to return some data so that i can generate a graph, in this hypothetical store, There’s only three ways we can contact a user: Email, SMS, or Physical Mail.

The graph is supposed to be 3 numbers, how many mails, emails, and SMS we’ve sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there’s 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.

To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i’d like to query all those rows and obtain:

SMS, 1 EMAIL, 1 MAIL, 1

Here’s a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0

the response with the above dataset should look like this:

method | count SMS,     4 EMAIL,   4 MAIL,    4

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it’s reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn’t utilize any of the indexes.

All four fields that are being filtered on are indexed on their respective tables.

Fast query plan where I filter on 3 fields from one table: https://www.brentozar.com/pastetheplan/?id=Hym_4PRSO

Slow query plan where I filter on four fields…three from one table and one from another table: https://www.brentozar.com/pastetheplan/?id=r1dVNDRHO

Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.

Count deep nested tables

since this SQL schemas I want to count the number of times a user is in a contest.

schema SQL user has trials

SELECT * FROM users u LEFT JOIN trials_has_users tu ON (tu.users_id = '1') LEFT JOIN trials AS t ON (t.id = tu.trials_id) WHERE u.id = '1';

Previously, I have the expected number of lines, but I want to make a count

SELECT contest_total FROM users u LEFT JOIN trials_has_users tu ON (tu.users_id = '1') LEFT JOIN (     SELECT          id,         COUNT(*) AS contest_total     FROM         trials     WHERE deleted_at IS NULL     GROUP BY          id ) AS t ON (t.id = tu.trials_id) WHERE u.id = '1';

Previously, have 6 rows want 1 (current user id) need LIMIT 1 ?

SELECT contest_total FROM users u LEFT JOIN trials_has_users tu ON (tu.users_id = '1') LEFT JOIN (     SELECT          id,         COUNT(*) AS contest_total     FROM         trials     WHERE deleted_at IS NULL     GROUP BY          id ) AS t ON (t.id = tu.trials_id) WHERE u.id = '1' LIMIT 1;

I would like to receive the number of contests in which the user has participated. My query is right ?

Unable to replicate Postgres tables using logical replication in RDS

Tables in the destination database are empty even after subscription is successful. I am extremely new to logical replication within Postgres. I am trying to replicate three tables from the source database to the destination database. These two databases are running on difference RDS instances

I followed the steps laid out on this link https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html

I have enabled replication across both the database instances by changing the parameter rds.logical_replication to 1 (enabled). The parameter groups are in sync after the restart.

On the source database, I created a publication

create publication master_data_publication for table tbl1, tbl2, tbl3;

On the destination I created a table with the same name and same columns and then created a subscription

create subscription master_data_sub CONNECTION 'host=sourcedbhost.some-code.some-region.rds.amazonaws.com port=5432 dbname=sourcedb user=sourceuser password=userpassword' publication master_data_publication;

if i run select * from pg_catalog.pg_stat_subscription;, it shows the following. enter image description here With this, we would assume that the replication should be working.

But the tables are shown to be completely empty.

On Source

select count(*) from tbl_1;  count -------      332

On Destination

select count(*) from tbl_1;  count -------      0

I am now stuck on how to proceed from here.

How to structure MySQL database tables for users and how to handle adding/removing permissions?

I am making a database structure for users, who can become part of a group, and within that group the admin of the group can assign as many permissions to a user as they would like.
This part seems easy. I have 4 tables:

 USERS           GROUPS             PERMISSIONS      PERMISSIONS_GROUPS -------  ---------------------    --------------    ------------------- | UID |  | GID | UID(creator) |   | PID | NAME |    | UID | GID | PID |

I think this is the best way to handle this. I have a few questions about how to handle permissions for the creator of the group, and how to handle permissions if I were to delete a permission or add a permission.
For instance, when a user makes a group. Do I query all of the permissions and in a while loop insert a PERMISSIONS_GROUPS record for every row in the permissions table? If I add a new permission, do I have to query every UID from the GROUPS table and run a while loop to insert the new permission into PERMISSIONS_GROUPS? I just want to make sure I am not missing something simple here. For structure/scripting I am using MySQL and PHP.

Is CHARSET important for tables with INT only?

My server default CHARSET is UTF-8 (set in my.cnf). I create tables like

CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, Title varchar(255), PRIMARY KEY(ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=2

but some tables have only int columns. Is it beneficial to use latin charset for them? Or is it better to have all tables with UTF-8 charset for consistency?

CREATE TABLE t2 ( ID int(11) unsigned NOT NULL, TagID int(11) unsigned NOT NULL, PRIMARY KEY(ID,TagID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

vs.

CREATE TABLE t2 ( ID int(11) unsigned NOT NULL, TagID int(11) unsigned NOT NULL, PRIMARY KEY(ID,TagID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_swedish_ci KEY_BLOCK_SIZE=1

What are wsm tables for?

So I needed to migrate to a different hosting, I downloaded the database, import it to the new hosting database, and it would produce an error at

CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_bounceVisits`  AS  select `5toy_wsm_pageViews`.`visitId` AS `visitId`,`5toy_wsm_pageViews`.`visitLastActionTime` AS `visitLastActionTime` from `5toy_wsm_pageViews` group by `5toy_wsm_pageViews`.`visitId` having (count(`5toy_wsm_pageViews`.`URLId`) = 1) ;

This was the error

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

So in the sql file I deleted all of this:

-- Structure for view `5toy_wsm_bounceVisits` -- DROP TABLE IF EXISTS `5toy_wsm_bounceVisits`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_bounceVisits`  AS  select `5toy_wsm_pageViews`.`visitId` AS `visitId`,`5toy_wsm_pageViews`.`visitLastActionTime` AS `visitLastActionTime` from `5toy_wsm_pageViews` group by `5toy_wsm_pageViews`.`visitId` having (count(`5toy_wsm_pageViews`.`URLId`) = 1) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_dateWiseBounce` -- DROP TABLE IF EXISTS `5toy_wsm_dateWiseBounce`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_dateWiseBounce`  AS  select date_format(convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-%d') AS `recordDate`,count(0) AS `bounce` from `5toy_wsm_bounceVisits` group by date_format(convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-%d') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_dateWiseBounceRate` -- DROP TABLE IF EXISTS `5toy_wsm_dateWiseBounceRate`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_dateWiseBounceRate`  AS  select `dwb`.`recordDate` AS `recordDate`,`dwb`.`bounce` AS `bounce`,`dwp`.`pageViews` AS `pageViews`,`dwv`.`visitors` AS `visitors`,((`dwb`.`bounce` / `dwp`.`pageViews`) * 100) AS `bRatePageViews`,((`dwb`.`bounce` / `dwv`.`visitors`) * 100) AS `bRateVisitors` from ((`5toy_wsm_dateWiseBounce` `dwb` left join `5toy_wsm_dateWisePageViews` `dwp` on((`dwb`.`recordDate` = `dwp`.`recordDate`))) left join `5toy_wsm_dateWiseVisitors` `dwv` on((`dwb`.`recordDate` = `dwv`.`recordDate`))) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_dateWiseFirstVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_dateWiseFirstVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_dateWiseFirstVisitors`  AS  select date_format(convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00'),'%Y-%m-%d') AS `recordDate`,count(`5toy_wsm_uniqueVisitors`.`visitorId`) AS `visitors` from `5toy_wsm_uniqueVisitors` group by date_format(convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00'),'%Y-%m-%d') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_dateWisePageViews` -- DROP TABLE IF EXISTS `5toy_wsm_dateWisePageViews`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_dateWisePageViews`  AS  select date_format(convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-%d') AS `recordDate`,sum(`5toy_wsm_pageViews`.`totalViews`) AS `pageViews` from `5toy_wsm_pageViews` group by date_format(convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-%d') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_dateWiseVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_dateWiseVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_dateWiseVisitors`  AS  select date_format(convert_tz(`5toy_wsm_logUniqueVisit`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-%d') AS `recordDate`,count(0) AS `visitors` from `5toy_wsm_logUniqueVisit` group by date_format(convert_tz(`5toy_wsm_logUniqueVisit`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-%d') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_hourWiseBounce` -- DROP TABLE IF EXISTS `5toy_wsm_hourWiseBounce`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_hourWiseBounce`  AS  select hour(convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00')) AS `hour`,count(0) AS `bounce` from `5toy_wsm_bounceVisits` where (convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00') >= '2021-01-22 00:00:00') group by hour(convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00')) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_hourWiseBounceRate` -- DROP TABLE IF EXISTS `5toy_wsm_hourWiseBounceRate`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_hourWiseBounceRate`  AS  select `hwb`.`hour` AS `hour`,`hwb`.`bounce` AS `bounce`,`hwp`.`pageViews` AS `pageViews`,`hwv`.`visitors` AS `visitors`,((`hwb`.`bounce` / `hwp`.`pageViews`) * 100) AS `bRatePageViews`,((`hwb`.`bounce` / `hwv`.`visitors`) * 100) AS `bRateVisitors` from ((`5toy_wsm_hourWiseBounce` `hwb` left join `5toy_wsm_hourWisePageViews` `hwp` on((`hwb`.`hour` = `hwp`.`hour`))) left join `5toy_wsm_hourWiseVisitors` `hwv` on((`hwb`.`hour` = `hwv`.`hour`))) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_hourWiseFirstVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_hourWiseFirstVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_hourWiseFirstVisitors`  AS  select hour(convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00')) AS `hour`,count(0) AS `visitors` from `5toy_wsm_uniqueVisitors` where (convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00') >= '2021-01-22 00:00:00') group by hour(convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00')) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_hourWisePageViews` -- DROP TABLE IF EXISTS `5toy_wsm_hourWisePageViews`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_hourWisePageViews`  AS  select hour(convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00')) AS `hour`,sum(`5toy_wsm_pageViews`.`totalViews`) AS `pageViews` from `5toy_wsm_pageViews` where (convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00') >= '2021-01-22 00:00:00') group by hour(convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00')) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_hourWiseVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_hourWiseVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_hourWiseVisitors`  AS  select hour(convert_tz(`5toy_wsm_logUniqueVisit`.`firstActionVisitTime`,'+00:00','+00:00')) AS `hour`,count(0) AS `visitors` from `5toy_wsm_logUniqueVisit` where (convert_tz(`5toy_wsm_logUniqueVisit`.`firstActionVisitTime`,'+00:00','+00:00') >= '2021-01-22 00:00:00') group by hour(convert_tz(`5toy_wsm_logUniqueVisit`.`firstActionVisitTime`,'+00:00','+00:00')) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_monthWiseBounce` -- DROP TABLE IF EXISTS `5toy_wsm_monthWiseBounce`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_monthWiseBounce`  AS  select date_format(convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m') AS `recordMonth`,count(0) AS `bounce` from `5toy_wsm_bounceVisits` group by date_format(convert_tz(`5toy_wsm_bounceVisits`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m-') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_monthWiseBounceRate` -- DROP TABLE IF EXISTS `5toy_wsm_monthWiseBounceRate`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_monthWiseBounceRate`  AS  select `mwb`.`recordMonth` AS `recordMonth`,`mwb`.`bounce` AS `bounce`,`mwp`.`pageViews` AS `pageViews`,`mwv`.`visitors` AS `visitors`,((`mwb`.`bounce` / `mwp`.`pageViews`) * 100) AS `bRatePageViews`,((`mwb`.`bounce` / `mwv`.`visitors`) * 100) AS `bRateVisitors` from ((`5toy_wsm_monthWiseBounce` `mwb` left join `5toy_wsm_monthWisePageViews` `mwp` on((`mwb`.`recordMonth` = `mwp`.`recordMonth`))) left join `5toy_wsm_monthWiseVisitors` `mwv` on((`mwb`.`recordMonth` = `mwv`.`recordMonth`))) ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_monthWiseFirstVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_monthWiseFirstVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_monthWiseFirstVisitors`  AS  select date_format(convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00'),'%Y-%m') AS `recordMonth`,count(`5toy_wsm_uniqueVisitors`.`visitorId`) AS `visitors` from `5toy_wsm_uniqueVisitors` group by date_format(convert_tz(`5toy_wsm_uniqueVisitors`.`firstVisitTime`,'+00:00','+00:00'),'%Y-%m') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_monthWisePageViews` -- DROP TABLE IF EXISTS `5toy_wsm_monthWisePageViews`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_monthWisePageViews`  AS  select date_format(convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m') AS `recordMonth`,sum(`5toy_wsm_pageViews`.`totalViews`) AS `pageViews` from `5toy_wsm_pageViews` group by date_format(convert_tz(`5toy_wsm_pageViews`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_monthWiseVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_monthWiseVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_monthWiseVisitors`  AS  select date_format(convert_tz(`5toy_wsm_logUniqueVisit`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m') AS `recordMonth`,count(0) AS `visitors` from `5toy_wsm_logUniqueVisit` group by date_format(convert_tz(`5toy_wsm_logUniqueVisit`.`visitLastActionTime`,'+00:00','+00:00'),'%Y-%m') ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_pageViews` -- DROP TABLE IF EXISTS `5toy_wsm_pageViews`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_pageViews`  AS  select `LV`.`visitId` AS `visitId`,`LV`.`URLId` AS `URLId`,`LV`.`keyword` AS `keyword`,`LV`.`refererUrlId` AS `refererUrlId`,`LU`.`countryId` AS `countryId`,`LU`.`regionId` AS `regionId`,count(0) AS `totalViews`,max(`LV`.`serverTime`) AS `visitLastActionTime` from (`5toy_wsm_logVisit` `LV` left join `5toy_wsm_logUniqueVisit` `LU` on((`LV`.`visitId` = `LU`.`id`))) group by `LV`.`visitId`,`LV`.`URLId` ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_uniqueVisitors` -- DROP TABLE IF EXISTS `5toy_wsm_uniqueVisitors`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_uniqueVisitors`  AS  select `LU`.`id` AS `id`,`LU`.`visitorId` AS `visitorId`,sum(`LU`.`totalTimeVisit`) AS `totalTimeVisit`,min(`LV`.`serverTime`) AS `firstVisitTime`,`LU`.`refererUrlId` AS `refererUrlId` from (`5toy_wsm_logUniqueVisit` `LU` left join `5toy_wsm_logVisit` `LV` on((`LV`.`visitId` = `LU`.`id`))) group by `LU`.`visitorId` ;  -- --------------------------------------------------------  -- -- Structure for view `5toy_wsm_visitorInfo` -- DROP TABLE IF EXISTS `5toy_wsm_visitorInfo`;  CREATE ALGORITHM=UNDEFINED DEFINER=`mbluhiqcygcjvfgk`@`10.%` SQL SECURITY DEFINER VIEW `5toy_wsm_visitorInfo`  AS  select `LV`.`

And it worked, it imported succesfully but im worried that it might broke something, I had a traffic/visitors plugin my best bet is that it was the data from that plugin, if it is, it doesnt matter. But if wsm means woocommerce stock manager that seems important

Drop tables but space not claimed in postgres 12

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option.

So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4).

Yesterday I have dropped two tables of size 700MB and 300MB.

After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few MBs have been freed from storage partition.

I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none.

Looking for the solution.