konnosDashboard
database.tsx443 lines · main
1import { PermissionAction } from '@supabase/shared-types/out/constants'
2import { useQueryClient } from '@tanstack/react-query'
3import { useFlag, useParams } from 'common'
4import dayjs from 'dayjs'
5import { ArrowRight, ExternalLink, RefreshCw } from 'lucide-react'
6import Link from 'next/link'
7import { useEffect, useRef, useState } from 'react'
8import { toast } from 'sonner'
9import { Alert, AlertDescription, Button } from 'ui'
10
11import ReportHeader from '@/components/interfaces/Reports/ReportHeader'
12import ReportPadding from '@/components/interfaces/Reports/ReportPadding'
13import { REPORT_DATERANGE_HELPER_LABELS } from '@/components/interfaces/Reports/Reports.constants'
14import ReportStickyNav from '@/components/interfaces/Reports/ReportStickyNav'
15import ReportWidget from '@/components/interfaces/Reports/ReportWidget'
16import { ReportChartUpsell } from '@/components/interfaces/Reports/v2/ReportChartUpsell'
17import { POOLING_OPTIMIZATIONS } from '@/components/interfaces/Settings/Database/ConnectionPooling/ConnectionPooling.constants'
18import DiskSizeConfigurationModal from '@/components/interfaces/Settings/Database/DiskSizeConfigurationModal'
19import { LogsDatePicker } from '@/components/interfaces/Settings/Logs/Logs.DatePickers'
20import UpgradePrompt from '@/components/interfaces/Settings/Logs/UpgradePrompt'
21import DefaultLayout from '@/components/layouts/DefaultLayout'
22import ObservabilityLayout from '@/components/layouts/ObservabilityLayout/ObservabilityLayout'
23import Table from '@/components/to-be-cleaned/Table'
24import { ButtonTooltip } from '@/components/ui/ButtonTooltip'
25import type { MultiAttribute } from '@/components/ui/Charts/ComposedChart.utils'
26import { LazyComposedChartHandler } from '@/components/ui/Charts/ComposedChartHandler'
27import { ReportSettings } from '@/components/ui/Charts/ReportSettings'
28import { ObservabilityLink } from '@/components/ui/ObservabilityLink'
29import { analyticsKeys } from '@/data/analytics/keys'
30import { useDiskAttributesQuery } from '@/data/config/disk-attributes-query'
31import { useProjectDiskResizeMutation } from '@/data/config/project-disk-resize-mutation'
32import { useDatabaseSizeQuery } from '@/data/database/database-size-query'
33import { useMaxConnectionsQuery } from '@/data/database/max-connections-query'
34import { usePgbouncerConfigQuery } from '@/data/database/pgbouncer-config-query'
35import { getReportAttributesV2 } from '@/data/reports/database-charts'
36import { useDatabaseReport } from '@/data/reports/database-report-query'
37import { useProjectAddonsQuery } from '@/data/subscriptions/project-addons-query'
38import { useCheckEntitlements } from '@/hooks/misc/useCheckEntitlements'
39import { useAsyncCheckPermissions } from '@/hooks/misc/useCheckPermissions'
40import { useRefreshHandler, useReportDateRange } from '@/hooks/misc/useReportDateRange'
41import { useSelectedOrganizationQuery } from '@/hooks/misc/useSelectedOrganization'
42import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
43import { DOCS_URL } from '@/lib/constants'
44import { formatBytes } from '@/lib/helpers'
45import { useDatabaseSelectorStateSnapshot } from '@/state/database-selector'
46import type { NextPageWithLayout } from '@/types'
47
48const DatabaseReport: NextPageWithLayout = () => {
49 return (
50 <ReportPadding>
51 <DatabaseUsage />
52 </ReportPadding>
53 )
54}
55
56DatabaseReport.getLayout = (page) => (
57 <DefaultLayout>
58 <ObservabilityLayout title="Database">{page}</ObservabilityLayout>
59 </DefaultLayout>
60)
61
62export type UpdateDateRange = (from: string, to: string) => void
63export default DatabaseReport
64
65const DatabaseUsage = () => {
66 const { db, chart, ref } = useParams()
67 const { data: project } = useSelectedProjectQuery()
68 const { data: org } = useSelectedOrganizationQuery()
69
70 const {
71 selectedDateRange,
72 updateDateRange,
73 datePickerValue,
74 datePickerHelpers,
75 showUpgradePrompt,
76 setShowUpgradePrompt,
77 handleDatePickerChange,
78 } = useReportDateRange(REPORT_DATERANGE_HELPER_LABELS.LAST_60_MINUTES)
79
80 const state = useDatabaseSelectorStateSnapshot()
81 const queryClient = useQueryClient()
82
83 const [isRefreshing, setIsRefreshing] = useState(false)
84 const [showIncreaseDiskSizeModal, setshowIncreaseDiskSizeModal] = useState(false)
85
86 const isReplicaSelected = state.selectedDatabaseId !== project?.ref
87
88 const report = useDatabaseReport()
89 const { data, params, largeObjectsSql, isPending: isLoading, refresh } = report
90
91 const { data: databaseSizeData } = useDatabaseSizeQuery({
92 projectRef: project?.ref,
93 connectionString: project?.connectionString || undefined,
94 })
95 const databaseSizeBytes = databaseSizeData ?? 0
96 const currentDiskSize = project?.volumeSizeGb ?? 0
97
98 const { data: diskConfig } = useDiskAttributesQuery({ projectRef: project?.ref })
99 const { data: maxConnections } = useMaxConnectionsQuery({
100 projectRef: project?.ref,
101 connectionString: project?.connectionString,
102 })
103 usePgbouncerConfigQuery({ projectRef: project?.ref })
104
105 // PGBouncer connections
106 const { data: addons } = useProjectAddonsQuery({ projectRef: project?.ref })
107 const computeInstance = addons?.selected_addons.find((addon) => addon.type === 'compute_instance')
108 const poolingOptimizations =
109 POOLING_OPTIMIZATIONS[
110 (computeInstance?.variant.identifier as keyof typeof POOLING_OPTIMIZATIONS) ??
111 (project?.infra_compute_size === 'nano' ? 'ci_nano' : 'ci_micro')
112 ]
113 const defaultMaxClientConn = poolingOptimizations.maxClientConn ?? 200
114
115 const { can: canUpdateDiskSizeConfig } = useAsyncCheckPermissions(
116 PermissionAction.UPDATE,
117 'projects',
118 {
119 resource: {
120 project_id: project?.id,
121 },
122 }
123 )
124
125 const { getEntitlementSetValues, isLoading: isEntitlementLoading } = useCheckEntitlements(
126 'observability.dashboard_advanced_metrics'
127 )
128 const entitledFeatures = getEntitlementSetValues()
129
130 const isSpendCapEnabled =
131 entitledFeatures.includes('database') &&
132 !org?.usage_billing_enabled &&
133 project?.cloud_provider !== 'FLY'
134
135 const showDiskIOBurstBalanceChart = useFlag('showDiskIOBurstBalanceChart')
136
137 const REPORT_ATTRIBUTES = getReportAttributesV2(
138 entitledFeatures,
139 project!,
140 diskConfig,
141 maxConnections,
142 defaultMaxClientConn,
143 isSpendCapEnabled,
144 showDiskIOBurstBalanceChart
145 )
146
147 const { isPending: isUpdatingDiskSize } = useProjectDiskResizeMutation({
148 onSuccess: (_, variables) => {
149 toast.success(`Successfully updated disk size to ${variables.volumeSize} GB`)
150 setshowIncreaseDiskSizeModal(false)
151 },
152 })
153
154 const onRefreshReport = useRefreshHandler(
155 datePickerValue,
156 datePickerHelpers,
157 handleDatePickerChange,
158 async () => {
159 if (!selectedDateRange) return
160
161 setIsRefreshing(true)
162 refresh()
163 const { period_start, period_end, interval } = selectedDateRange
164
165 REPORT_ATTRIBUTES.flatMap((chart) => chart.attributes || [])
166 .filter((attr): attr is MultiAttribute => attr !== false)
167 .forEach((attr) => {
168 queryClient.invalidateQueries({
169 queryKey: analyticsKeys.infraMonitoring(ref, {
170 attribute: attr.attribute,
171 startDate: period_start.date,
172 endDate: period_end.date,
173 interval,
174 databaseIdentifier: state.selectedDatabaseId,
175 }),
176 })
177 })
178
179 if (isReplicaSelected) {
180 queryClient.invalidateQueries({
181 queryKey: analyticsKeys.infraMonitoring(ref, {
182 attribute: 'physical_replication_lag_physical_replication_lag_seconds',
183 startDate: period_start.date,
184 endDate: period_end.date,
185 interval,
186 databaseIdentifier: state.selectedDatabaseId,
187 }),
188 })
189 }
190 setTimeout(() => setIsRefreshing(false), 1000)
191 }
192 )
193
194 const stateSyncedFromUrlRef = useRef(false)
195 useEffect(() => {
196 if (stateSyncedFromUrlRef.current) return
197 stateSyncedFromUrlRef.current = true
198
199 if (db !== undefined) {
200 setTimeout(() => {
201 // [Joshen] Adding a timeout here to support navigation from settings to reports
202 // Both are rendering different instances of ProjectLayout which is where the
203 // DatabaseSelectorContextProvider lies in (unless we reckon shifting the provider up one more level is better)
204 state.setSelectedDatabaseId(db)
205 }, 100)
206 }
207 if (chart !== undefined) {
208 setTimeout(() => {
209 const el = document.getElementById(chart)
210 if (el) el.scrollIntoView({ behavior: 'smooth', block: 'center' })
211 }, 200)
212 }
213 }, [db, chart, state])
214
215 return (
216 <>
217 <ReportHeader showDatabaseSelector title="Database" />
218 <ReportStickyNav
219 content={
220 <>
221 <ButtonTooltip
222 type="default"
223 disabled={isRefreshing}
224 icon={<RefreshCw className={isRefreshing ? 'animate-spin' : ''} />}
225 className="w-7"
226 tooltip={{ content: { side: 'bottom', text: 'Refresh report' } }}
227 onClick={onRefreshReport}
228 />
229 <ReportSettings chartId="database-charts" />
230 <div className="flex items-center gap-3">
231 <LogsDatePicker
232 onSubmit={handleDatePickerChange}
233 value={datePickerValue}
234 helpers={datePickerHelpers}
235 />
236 <UpgradePrompt
237 show={showUpgradePrompt}
238 setShowUpgradePrompt={setShowUpgradePrompt}
239 title="Report date range"
240 description="Report data can be stored for a maximum of 3 months depending on the plan that your project is on."
241 source="databaseReportDateRange"
242 />
243 {selectedDateRange && (
244 <div className="flex items-center gap-x-2 text-xs">
245 <p className="text-foreground-light">
246 {dayjs(selectedDateRange.period_start.date).format('MMM D, h:mma')}
247 </p>
248 <p className="text-foreground-light">
249 <ArrowRight size={12} />
250 </p>
251 <p className="text-foreground-light">
252 {dayjs(selectedDateRange.period_end.date).format('MMM D, h:mma')}
253 </p>
254 </div>
255 )}
256 </div>
257 </>
258 }
259 >
260 {selectedDateRange &&
261 REPORT_ATTRIBUTES.filter((chart) => !chart.hide).map((chart) => {
262 const chartAvailable =
263 !chart.entitlement ||
264 isEntitlementLoading ||
265 entitledFeatures.includes(chart.entitlement)
266 return chartAvailable ? (
267 <LazyComposedChartHandler
268 key={chart.id}
269 {...chart}
270 attributes={chart.attributes as MultiAttribute[]}
271 interval={selectedDateRange.interval}
272 startDate={selectedDateRange?.period_start?.date}
273 endDate={selectedDateRange?.period_end?.date}
274 updateDateRange={updateDateRange}
275 defaultChartStyle={chart.defaultChartStyle as 'line' | 'bar' | 'stackedAreaLine'}
276 syncId="database-charts"
277 showMaxValue={
278 chart.id === 'client-connections' ||
279 chart.id === 'client-connections-basic' ||
280 chart.id === 'pgbouncer-connections'
281 ? true
282 : chart.showMaxValue
283 }
284 />
285 ) : (
286 <ReportChartUpsell
287 key={chart.id}
288 report={{ label: chart.label, requiredPlan: chart.requiredPlan }}
289 orgSlug={org?.slug ?? ''}
290 />
291 )
292 })}
293 {selectedDateRange && isReplicaSelected && (
294 <LazyComposedChartHandler
295 id="replication-lag"
296 label="Replication lag"
297 format="s"
298 valuePrecision={2}
299 showTooltip
300 YAxisProps={{
301 width: 50,
302 tickFormatter: (value: number) => `${value}s`,
303 }}
304 attributes={[
305 {
306 attribute: 'physical_replication_lag_physical_replication_lag_seconds',
307 provider: 'infra-monitoring',
308 label: 'Replication lag',
309 tooltip:
310 'Seconds the read replica is behind its primary. Sustained or growing lag may indicate the replica cannot keep up with write throughput',
311 },
312 ]}
313 interval={selectedDateRange.interval}
314 startDate={selectedDateRange?.period_start?.date}
315 endDate={selectedDateRange?.period_end?.date}
316 updateDateRange={updateDateRange}
317 defaultChartStyle="line"
318 syncId="database-charts"
319 />
320 )}
321 </ReportStickyNav>
322 <section id="database-size-report">
323 <ReportWidget
324 isLoading={isLoading}
325 params={params.largeObjects}
326 title="Database Size"
327 data={data.largeObjects || []}
328 queryType={'db'}
329 resolvedSql={largeObjectsSql}
330 renderer={(props) => {
331 return (
332 <div>
333 <div className="col-span-4 inline-grid grid-cols-12 gap-12 w-full mt-5">
334 <div className="grid gap-2 col-span-4 xl:col-span-2">
335 <h5>Space used</h5>
336 <span className="text-lg">{formatBytes(databaseSizeBytes, 2, 'GB')}</span>
337 </div>
338 <div className="grid gap-2 col-span-4 xl:col-span-3">
339 <h5>Provisioned disk size</h5>
340 <span className="text-lg">{currentDiskSize} GB</span>
341 </div>
342
343 <div className="col-span-full lg:col-span-4 xl:col-span-7 lg:text-right">
344 {project?.cloud_provider === 'AWS' ? (
345 <Button asChild type="default">
346 <Link href={`/project/${ref}/settings/compute-and-disk`}>
347 Increase disk size
348 </Link>
349 </Button>
350 ) : (
351 <ButtonTooltip
352 type="default"
353 disabled={!canUpdateDiskSizeConfig}
354 onClick={() => setshowIncreaseDiskSizeModal(true)}
355 tooltip={{
356 content: {
357 side: 'bottom',
358 text: !canUpdateDiskSizeConfig
359 ? 'You need additional permissions to increase the disk size'
360 : undefined,
361 },
362 }}
363 >
364 Increase disk size
365 </ButtonTooltip>
366 )}
367 </div>
368 </div>
369
370 <h3 className="mt-8 text-sm">Large Objects</h3>
371 {!props.isLoading && props.data.length === 0 && <span>No large objects found</span>}
372 {!props.isLoading && props.data.length > 0 && (
373 <Table
374 className="space-y-3 mt-4"
375 head={[
376 <Table.th key="object" className="py-2">
377 Object
378 </Table.th>,
379 <Table.th key="size" className="py-2">
380 Size
381 </Table.th>,
382 ]}
383 body={props.data?.map((object) => {
384 const percentage = (
385 ((object.table_size as number) / databaseSizeBytes) *
386 100
387 ).toFixed(2)
388
389 return (
390 <Table.tr key={`${object.schema_name}.${object.relname}`}>
391 <Table.td>
392 {object.schema_name}.{object.relname}
393 </Table.td>
394 <Table.td>
395 {formatBytes(object.table_size)} ({percentage}%)
396 </Table.td>
397 </Table.tr>
398 )
399 })}
400 />
401 )}
402 </div>
403 )
404 }}
405 append={() => (
406 <div className="px-6 pb-6">
407 <Alert variant="default" className="mt-4">
408 <AlertDescription>
409 <div className="space-y-2">
410 <p>
411 New Briven projects have a database size of ~40-60mb. This space includes
412 pre-installed extensions, schemas, and default Postgres data. Additional
413 database size is used when installing extensions, even if those extensions are
414 inactive.
415 </p>
416
417 <Button asChild type="default" icon={<ExternalLink />}>
418 <Link
419 href={`${DOCS_URL}/guides/platform/database-size#disk-space-usage`}
420 target="_blank"
421 rel="noreferrer"
422 >
423 Read about database size
424 </Link>
425 </Button>
426 </div>
427 </AlertDescription>
428 </Alert>
429 </div>
430 )}
431 />
432 <DiskSizeConfigurationModal
433 visible={showIncreaseDiskSizeModal}
434 loading={isUpdatingDiskSize}
435 hideModal={setshowIncreaseDiskSizeModal}
436 />
437 </section>
438 <div className="py-8">
439 <ObservabilityLink />
440 </div>
441 </>
442 )
443}